Sub 数据整理()
Dim iAreaCount As Integer
Dim i As Integer
Dim sTemp, sTemp2 As String
Dim iTemp As Long
Dim bFind As Boolean
Dim ValueName() As String
Dim ValueValue() As Double
Dim ValueDate() As Date
Dim iHour, iMinute, iSecond As Integer
Dim iIndex As Integer
Worksheets("Sheet1").Activate
Range("A1").CurrentRegion.Select
iAreaCount = Selection.Rows.Count
ReDim ValueName(iAreaCount)
ReDim ValueValue(iAreaCount)
ReDim ValueDate(iAreaCount)
Sheets.Add After:=Sheets("Sheet1")
ActiveSheet.Name = "CPU"
Sheets.Add After:=Sheets("Sheet1")
ActiveSheet.Name = "MEM"
For i = 1 To iAreaCount
ValueName(i) = Worksheets("Sheet1").Cells(i, 1).Value
ValueValue(i) = Worksheets("Sheet1").Cells(i, 2).Value
If ValueName(i) Like "*cpu*" Then ValueValue(i) = ValueValue(i) / 100
If ValueName(i) Like "*mem*" Then ValueValue(i) = ValueValue(i) / 1073741824
iTemp = Worksheets("Sheet1").Cells(i, 3).Value
iHour = Int((iTemp Mod 86400) / 3600)
iMinute = Int((iTemp Mod 3600) / 60)
iSecond = Int(iTemp Mod 60)
sTemp = Str(iHour) & ":" & Str(iMinute) & ":" & Str(iSecond)
ValueDate(i) = sTemp
Next i
For i = 1 To iAreaCount
bFind = False
Dim sName As String
sName = Right(ValueName(i), 30)
For Each sht In Sheets
If sName = sht.Name Then bFind = True
Next sht
If bFind Then
Worksheets(sName).Activate
Range("A1").Select
Range("A1").CurrentRegion.Select
iTemp = Selection.Rows.Count + 1
sTemp2 = Mid(Str(iTemp), 2)
sTemp = "A" & sTemp2
Range(sTemp).Select
ActiveCell.FormulaR1C1 = ValueName(i)
sTemp = "B" & sTemp2
Range(sTemp).Select
ActiveCell.FormulaR1C1 = ValueValue(i)
sTemp = "C" & sTemp2
Range(sTemp).Select
ActiveCell.FormulaR1C1 = ValueDate(i)
Else
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = sName
Range("A1").Select
ActiveCell.FormulaR1C1 = ValueName(i)
Range("B1").Select
ActiveCell.FormulaR1C1 = ValueValue(i)
Range("C1").Select
ActiveCell.FormulaR1C1 = ValueDate(i)
End If
Next i
Dim rng1, rng2 As Range
'cpu chart
Dim ChtCPU As Chart
Worksheets("CPU").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "CPU占用"
Dim iChartIndex As Integer
iChartIndex = 1
For Each sht In Sheets
If sht.Name Like "*cpu*" Then
Worksheets(sht.Name).Activate
Range("A1").Select
Range("A1").CurrentRegion.Select
iTemp = Selection.Rows.Count
sTemp2 = Mid(Str(iTemp), 2)
sTemp = Worksheets(sht.Name).Cells(1, 1).Value
Set rng1 = Sheets(sht.Name).Range("B1", "B" & sTemp2)
Set rng2 = Sheets(sht.Name).Range("C1", "C" & sTemp2)
rng1.NumberFormat = "0.0%"
Worksheets("CPU").Activate
Set ChtCPU = ActiveSheet.ChartObjects(1).Chart
ChtCPU.SeriesCollection.NewSeries
ChtCPU.SeriesCollection(iChartIndex).Values = rng1
ChtCPU.SeriesCollection(iChartIndex).XValues = rng2
ChtCPU.SeriesCollection(iChartIndex).Name = sTemp
iChartIndex = iChartIndex + 1
End If
Next sht
'memory chart
Dim ChtMEM As Chart
Worksheets("MEM").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "内存占用(G)"
iChartIndex = 1
For Each sht In Sheets
If sht.Name Like "*mem*" Then
Worksheets(sht.Name).Activate
Range("A1").Select
Range("A1").CurrentRegion.Select
iTemp = Selection.Rows.Count
sTemp2 = Mid(Str(iTemp), 2)
sTemp = Worksheets(sht.Name).Cells(1, 1).Value
Set rng1 = Sheets(sht.Name).Range("B1", "B" & sTemp2)
rng1.NumberFormat = "0.00"
Set rng2 = Sheets(sht.Name).Range("C1", "C" & sTemp2)
Worksheets("MEM").Activate
Set ChtMEM = ActiveSheet.ChartObjects(1).Chart
ChtMEM.SeriesCollection.NewSeries
ChtMEM.SeriesCollection(iChartIndex).Values = rng1
ChtMEM.SeriesCollection(iChartIndex).XValues = rng2
ChtMEM.SeriesCollection(iChartIndex).Name = sTemp
iChartIndex = iChartIndex + 1
End If
Next sht
End Sub