Excelでグラフを一括生成するスクリプト
1529 ワード
多くのデータに基づいて様々なバグ曲線のレポートを生成することが多いので、時間を割いてこのスクリプトを完成し、必要なものは自分で修正することができます.
Function getcolname(ByVal intcol As Long)
intcol = intcol - 1
getcolname = IIf(intcol \ 26, Chr(64 + intcol \ 26), "") & Chr(65 + intcol Mod 26)
End Function
Sub ()
'
'
' by marty
' sheet1
x = 0
y = 0
Sheets.Add After:=Sheets(Sheets.Count)
'ActiveSheet.Name = "Bug "
' sheet1
intcol = Sheets("Sheet1").Range("IV1").End(xlToLeft).Column
strCol = getcolname(intcol)
'
For i = 2 To 6
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C" & i & ":" & strCol & i)
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$C$1:$" & strCol & "$1"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!$B$" & i
' x ,
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
Next i
'
For Each Chart In ActiveSheet.ChartObjects
Chart.Activate
ActiveChart.ChartArea.Select
Chart.Top = y * 206
Chart.Left = x * 606
Chart.Height = 200
Chart.Width = 600
x = x + 1
If x >= 1 Then
x = 0
y = y + 1
End If
Next Chart
End Sub