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

X軸，Y軸，補助目盛線の書式設定が終わったところ
このエントリーをはてなブックマークに追加
Pocket

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

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

Contents hide
1 散布図を挿入する
1.1 散布図の種類
1.2 マクロ記録
2 データ系列の追加
2.1 散布図に最初のデータ系列を追加する
2.2 二番目のデータ系列を追加する
2.3 グラフエリア，プロットエリアの書式設定
3 データ系列の書式設定
4 軸の書式設定
4.1 X 軸の書式設定
4.2 Y 軸の書式設定
4.3 補助目盛線の追加とフォントの色の変更
5 まとめ

散布図を挿入する

散布図の種類

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

  • 散布図
  • 散布図（平滑線とマーカー）
  • 散布図（平滑線）
  • 散布図（直線とマーカー）
  • 散布図（直線）
  • バブル
  • 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．

コメントを残す

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