1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125
| Option Base 1
'Option Explicit
'---------------------------------------------------------------------------------------------------------------
'Classe les serveurs par nombre d'alarmes
'---------------------------------------------------------------------------------------------------------------
Sub Calcul_Stats()
DerligServeur = Sheets("Incidents mensuels").Range("F65536").End(xlUp).Row
Sheets("Stats").Select
Range("E2:G" & DerligServeur).Select
Selection.ClearContents
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=IF('Incidents mensuels'!RC[1]="""",""NA"",'Incidents mensuels'!RC[1])"
Selection.AutoFill Destination:=Range("E2:E" & DerligServeur), Type:=xlFillDefault
Range("E2:E" & DerligServeur).Select
Selection.Copy
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Range("$F$1:$F$" & DerligServeur).RemoveDuplicates Columns:=1, Header:= _
xlYes
DerLigServeur2 = Sheets("Stats").Range("F65536").End(xlUp).Row
Range("G2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF('Incidents mensuels'!C[-1],RC[-1])"
Selection.AutoFill Destination:=Range("G2:G" & DerLigServeur2), Type:=xlFillDefault
Columns("F:G").Select
ActiveWorkbook.Worksheets("Stats").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Stats").Sort.SortFields.Add Key:=Range("G2:G" & DerLigServeur2) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Stats").Sort
.SetRange Range("F1:G" & DerLigServeur2)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
'---------------------------------------------------------------------------------------------------------------
'Liste les alarmes par serveur
'---------------------------------------------------------------------------------------------------------------
Dim Tabl1, Tabl2, Equips, Res() As String, Ctr As Long
Dim Tabl, Dico As Object 'table double entrée
Dim ResEquip(), ResOccur() As Long, Plage As Range
Set Dico = CreateObject("Scripting.Dictionary")
Ctr = 0
ReDim Res(3, 1)
ReDim ResEquip(1)
ReDim ResOccur(1)
With Sheets("Incidents mensuels")
Tabl1 = Application.Transpose(.Range(.[D2], .Cells(.Rows.Count, 4).End(xlUp)))
Tabl2 = Application.Transpose(.Range(.[F2], .Cells(.Rows.Count, 6).End(xlUp)))
For i = 1 To UBound(Tabl2)
If Len(Tabl2(i)) > 248 Then Tabl2(i) = Left(Tabl2(i), 248)
Next i
For i = 1 To UBound(Tabl1)
If Len(Tabl1(i)) > (252 - Len(Tabl2(i))) Then Tabl1(i) = Left(Tabl1(i), 252 - Len(Tabl2(i)))
Next i
For i = 1 To UBound(Tabl1)
If Not Dico.exists(Tabl1(i) & "***" & Tabl2(i)) Then
Dico.Add Tabl1(i) & "***" & Tabl2(i), Tabl1(i) & "***" & Tabl2(i)
End If
Next i
For Each Item In Dico.items
tablo = Split(Item, "***")
Ctr = Ctr + 1
ReDim Preserve Res(3, Ctr)
Res(1, Ctr) = Item
Res(2, Ctr) = tablo(1)
Res(3, Ctr) = tablo(0)
Next Item
End With
With Sheets("Stats")
.Range("H2:K65536").ClearContents
.[H2].Resize(UBound(Res, 2), 3).NumberFormat = "@"
.[H2].Resize(UBound(Res, 2), 3) = Application.Transpose(Res)
Set Plage = .Range(.[H2], .Cells(.Rows.Count, 10).End(xlUp))
Plage.Sort key1:=.[I2], order1:=xlAscending, key2:=.[J2], order2:=xlAscending, Header:=xlNo
Tabl = Application.Transpose(.Range(.[H2], .Cells(.Rows.Count, 8).End(xlUp)))
ReDim ResOccur(Dico.Count)
For i = 1 To UBound(Tabl1)
Ctr = Application.Match(Tabl1(i) & "***" & Tabl2(i), Tabl, 0)
ResOccur(Ctr) = ResOccur(Ctr) + 1
Next
.[K2].Resize(UBound(ResOccur)) = Application.Transpose(ResOccur)
.[H:H].ClearContents
Dim Final()
ReDim Final(3, 1)
Ctr = 0
Tabl = Application.Transpose(.Range(.[I2], .Cells(.Rows.Count, 11).End(xlUp)))
For i = 1 To UBound(Tabl, 2)
Ctr = Ctr + 1
ReDim Preserve Final(3, Ctr)
Final(1, Ctr) = Tabl(1, i)
Final(2, Ctr) = Tabl(2, i)
Final(3, Ctr) = Tabl(3, i)
If i < UBound(Tabl, 2) Then
If Tabl(1, i) <> Tabl(1, i + 1) Then
Ctr = Ctr + 1
ReDim Preserve Final(3, Ctr)
End If
End If
Next
For i = UBound(Final, 2) To 2 Step -1
If Final(1, i) = Final(1, i - 1) Then Final(1, i) = ""
Next
.Range("H2:K65536").Clear
.[I2].Resize(UBound(Final, 2), 2).NumberFormat = "@"
.[I2].Resize(UBound(Final, 2), 3) = Application.Transpose(Final)
.Columns(10).Cut .[L1]
.Columns(10).Delete
.Columns(11).AutoFit
End With
End Sub |
Partager