複数のデータ系列をもつEXCELの散布図をマクロ記録する

X軸,Y軸,補助目盛線の書式設定が終わったところ
Pocket

 EXCEL は散布図を描く際によく用いています.散布図のデータ系列の指定は奥深く,非常に難しいものがあり,少し凝ったことをしようとすると大変な目に遭います.

 手動では設定不可能なほどの数のデータ系列の設定を VBA から行えないか,試行錯誤しました.今回はマクロの記録にとどめます.

散布図を挿入する

散布図の種類

 散布図には以下の種類があります.

  • 散布図
  • 散布図(平滑線とマーカー)
  • 散布図(平滑線)
  • 散布図(直線とマーカー)
  • 散布図(直線)
  • バブル
  • 3D 効果つきバブル

マクロ記録

 それらのマクロ記録は次のようになります.

ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmooth).Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveSheet.Shapes.AddChart2(269, xlBubble).Select
ActiveSheet.Shapes.AddChart2(269, xlBubble3DEffect).Select
EXCELで散布図を挿入
EXCELで散布図を挿入

データ系列の追加

散布図に最初のデータ系列を追加する

 フラフのプロットエリアを右クリックして「データの選択」を選ぶとデータソースの選択ダイアログが現れます.「追加」ボタンを押すと「系列の編集」ダイアログに遷移し,ここで実際のデータ領域を指定します.

ActiveSheet.ChartObjects("グラフ 1").Activate
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = "=DATA!$F$2"
ActiveChart.FullSeriesCollection(1).XValues = "=DATA!$J$2:$J$5"
ActiveChart.FullSeriesCollection(1).Values = "=DATA!$G$2:$G$5"
「データソースの選択」の「系列の編集」
「データソースの選択」の「系列の編集」

二番目のデータ系列を追加する

 同様にプロットエリアを右クリックして「データの選択」から「追加」で新しいデータ系列を設定します.

Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = "=DATA!$F$6"
ActiveChart.FullSeriesCollection(2).XValues = "=DATA!$J$6:$J$9"
ActiveChart.FullSeriesCollection(2).Values = "=DATA!$G$6:$G$9"
「系列の編集」で「系列Xの値」を指定
「系列の編集」で「系列Xの値」を指定

グラフエリア,プロットエリアの書式設定

 「グラフのデザイン」タブで「グラフスタイル」を指定する箇所がありますが,なぜかブルーバックの白ポイントが選択肢に出てこなかったので,手動で設定したマクロ記録を載せます.

 散布図の初期状態はあまり見栄えがいいとは言えません.「グラフエリアの書式設定」で「塗りつぶし」を「単色」の「青」,「枠線」を「線なし」とし,「プロットエリアの書式設定」で「塗りつぶし」を「なし」,「枠線」を「単色」の「白」としたところです.

ActiveSheet.ChartObjects("グラフ 1").Activate
With ActiveSheet.Shapes("グラフ 1").Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
End With
ActiveSheet.ChartObjects("グラフ 1").Activate
ActiveChart.PlotArea.Select
ActiveSheet.Shapes("グラフ 1").Line.Visible = msoFalse
Selection.Format.Fill.Visible = msoFalse
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
End With
散布図の初期状態
散布図の初期状態
グラフエリアとプロットエリアの書式設定
グラフエリアとプロットエリアの書式設定

データ系列の書式設定

 データ系列の総数は最終的に数百にもなります.そのため,色を替えていては追いつきません.ここでは全てのデータ系列の色を白に揃えます.

 操作していて気がついたのですが,データ系列の書式設定の際,複数のデータ系列を同時に選択することはできないようです.一つずつ地道に選択しては操作を繰り返す必要があります.

 「データ系列の書式設定」で変更するのは「マーカーのオプション」「塗りつぶし」「枠線」です.

 「マーカーのオプション」では「組み込み」でサイズを 1 に変更します.

 「塗りつぶし」は「塗りつぶし(単色)」で「色」は「白」に変更します.

 「枠線」は「線なし」に変更します.

ActiveChart.FullSeriesCollection(1).Select
With Selection
    .MarkerStyle = 8
    .MarkerSize = 5
End With
Selection.MarkerSize = 4
Selection.MarkerSize = 3
Selection.MarkerSize = 2
Selection.MarkerSize = 1
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent2
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
End With
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
    .Solid
End With
Selection.Format.Line.Visible = msoFalse
ActiveChart.FullSeriesCollection(2).Select
With Selection
  .MarkerStyle = 8
  .MarkerSize = 5
End With
Selection.MarkerSize = 4
Selection.MarkerSize = 3
Selection.MarkerSize = 2
Selection.MarkerSize = 1
With Selection.Format.Fill
 .Visible = msoTrue
 .ForeColor.ObjectThemeColor = msoThemeColorAccent1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
End With
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
    .Solid
End With
Selection.Format.Line.Visible = msoFalse

 ついでにグラフのサイズも変更します.

ActiveChart.ChartArea.Select
ActiveSheet.Shapes("グラフ 1").ScaleWidth 1.0666666667, msoFalse, _
        msoScaleFromTopLeft
ActiveSheet.Shapes("グラフ 1").ScaleHeight 1.7222222222, msoFalse, _
        msoScaleFromTopLeft
グラフのサイズを変更
グラフのサイズを変更

軸の書式設定

X 軸の書式設定

 X 軸の書式設定で変更するのは「軸のオプション」の「最小値」と「最大値」,「縦軸との交点」の「軸の値」,「表示形式」の「カテゴリ」です.

 「軸のオプション」の「最小値」は -0.2, 「最大値」は 0.2 と入力します.

 「縦軸との交点」の「軸の値」は「軸のオプション」の「最小値」に入力したのと同じ値を入力します.

 「表示形式」の「カテゴリ」は「パーセンテージ」を選択します.

ActiveSheet.ChartObjects("グラフ 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinimumScale = -0.2
ActiveChart.Axes(xlCategory).MaximumScale = 0.2
ActiveChart.Axes(xlCategory).CrossesAt = 0
ActiveChart.Axes(xlCategory).CrossesAt = -0.2
Selection.TickLabels.NumberFormatLocal = "0%"

Y 軸の書式設定

 Y 軸の書式設定で変更するのは「対数目盛を表示する」,「境界値」の「最小値」と「最大値」,「表示単位」です.

 まず「対数目盛を表示する」にチェックを入れます.

 次に「境界値」の「最小値」と「最大値」にそれぞれ 100, 1.0E7 と入力します.「自動」のままにしておくとデータ系列の分布によってはレイアウトが切り替わるため,不都合です.

 さらに「表示単位」を「万」に変更します.

ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).ScaleType = xlLogarithmic
ActiveChart.Axes(xlValue).MinimumScale = 100
ActiveChart.Axes(xlValue).MaximumScale = 1000000
ActiveChart.Axes(xlValue).MaximumScale = 10000000
ActiveChart.Axes().DisplayUnit = xlThousands

補助目盛線の追加とフォントの色の変更

 Y 軸に補助目盛線を追加します.また,各軸のフォントの色,表示単位ラベルのフォントの色を全て白に変更します.

ActiveChart.Axes(xlValue).HasMinorGridlines = True
With Selection.Format.TextFrame2.TextRange.Font
    .BaselineOffset = 0
    .Fill.Visible = msoTrue
    .Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .Fill.ForeColor.TintAndShade = 0
    .Fill.ForeColor.Brightness = 0
    .Fill.Transparency = 0
    .Fill.Solid
End With
ActiveChart.Axes(xlCategory).Select
With Selection.Format.TextFrame2.TextRange.Font
    .BaselineOffset = 0
    .Fill.Visible = msoTrue
    .Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .Fill.ForeColor.TintAndShade = 0
    .Fill.ForeColor.Brightness = 0
    .Fill.Transparency = 0
    .Fill.Solid
End With
    ActiveChart.Axes(xlValue).DisplayUnitLabel.Select
With Selection.Format.TextFrame2.TextRange.Font
    .BaselineOffset = 0
    .Fill.Visible = msoTrue
    .Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .Fill.ForeColor.TintAndShade = 0
    .Fill.ForeColor.Brightness = 0
    .Fill.Transparency = 0
    .Fill.Solid
End With
X軸,Y軸,補助目盛線の書式設定が終わったところ
X軸,Y軸,補助目盛線の書式設定が終わったところ

まとめ

 EXCEL の散布図で複数のデータ系列を追加していく過程をマクロ記録しました.また,グラフの書式設定を変更していく過程もマクロ記録しました.

 散布図のデータ系列は FullSeriesCollection() コレクションに格納され,インデックス番号で管理されていることが分かりました.インデックス番号を変数化してループすれば自動で複数のデータ系列を追加できそうなめどが立ちました.

Pocket

投稿者: admin

趣味:写真撮影とデータベース. カメラ:TOYO FIELD, Hasselblad 500C/M, Leica M6. SQL Server 2008 R2, MySQL, Microsoft Access.

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です