| |
よろしくお願いします。
10万行以上あるため、データを別シートに
それぞれインポートしました。
インポートするデータ数はその都度変わるので
シート数も変化します。
インポートした各データはグラフ化できるように
整えるマクロは作成しました。
そのデータを元に散布グラフを自動作成する
マクロを作りたいのですが、シート指定と
範囲指定のところで躓いています。
現在記録マクロで作成したものを以下に
表示します。
アドバイス頂けると大変助かります。
------------------------------------------記録マクロここから
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("完成").Range("U9")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='SrcP=104610'!R1C2:R29506C2"
ActiveChart.SeriesCollection(1).Values = "='SrcP=104610'!R1C1:R29506C1"
ActiveChart.SeriesCollection(1).Name = "=完成!R1C1"
ActiveChart.SeriesCollection(2).XValues = "='SrcP=10469'!R2C2:R29506C2"
ActiveChart.SeriesCollection(2).Values = "='SrcP=10469'!R2C1:R29506C1"
ActiveChart.SeriesCollection(2).Name = "=完成!R1C1"
ActiveChart.SeriesCollection(3).XValues = "='SrcP=10468'!R2C3:R29506C3"
ActiveChart.SeriesCollection(3).Values = "='SrcP=10468'!R2C1:R29506C1"
ActiveChart.SeriesCollection(3).Name = "=完成!R1C1"
ActiveChart.SeriesCollection(4).XValues = "='SrcP=10467'!R2C2:R29472C2"
ActiveChart.SeriesCollection(4).Values = "='SrcP=10467'!R2C1:R29472C1"
ActiveChart.SeriesCollection(4).Name = "=完成!R1C1"
ActiveChart.SeriesCollection(5).XValues = "='SrcP=104611'!R2C2:R29506C2"
ActiveChart.SeriesCollection(5).Values = "='SrcP=104611'!R2C1:R29506C1"
ActiveChart.SeriesCollection(5).Name = "='SrcP=104611'!R1C12"
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "テスト"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "軸1"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "軸2"
End With
ActiveChart.HasLegend = False
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlHundredMillions
.HasDisplayUnitLabel = False
End With
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlHundredThousands
.HasDisplayUnitLabel = False
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlHundredMillions
.HasDisplayUnitLabel = False
End With
ActiveChart.SeriesCollection(3).Select
Selection.Delete
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 11
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollection(2).Points(29492).Select
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 11
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.ColorIndex = 11
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.ChartArea.Select
----------------------------------------------ここまで
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
- 06/2/20(月) 11:29 -
