【VBA】Excelマクロによるグラフ（散布図）自動作成

VBA
2020.11.08

マクロを使えば散布図を自動作成したり、軸をリアルタイムで変換できます。

散布図の例

一見難しそうですが、１度作ってみれば簡単に感じると思います。

データ準備

以下のデータを散布図にします。
自分で使えるデータがあるなら、それを使ってください。

データの入力例

グラフエリア作成

まずはグラフを描画するエリアを作ります。

Sub test()

Dim ch As Chart
Set ch = ActiveSheet.ChartObjects.Add(10, 10, 400, 300).Chart

End Sub
グラフエリアを挿入した図

実行すると、こんな感じでグラフを描画するエリアができるはずです。
シートを指定してから、ChartObjects.Addでエリアを追加します。

グラフエリアを挿入するコード

【関連記事】Excelマクロでセル,シート,ファイルを指定する方法

DimとSetは、グラフエリアを変数chとして定義するために書いています。

グラフ種類を散布図に設定

ChartTypeでグラフの種類を設定します。
散布図ならxlXYScatterです。

Sub test()

Dim ch As Chart
Set ch = ActiveSheet.ChartObjects.Add(10, 10, 400, 300).Chart
ch.ChartType = xlXYScatter

End Sub

Setでグラフエリアを変数chとして設定したので、ch.ChartTypeで指定できますね。
Addを実行するたびにグラフが追加されるので、邪魔なら都度消してください。

データ指定

今回のデータは以下の通りです。
A列を横軸、B列を縦軸にしてみましょう。

データの入力例

SetSourceDataでデータを入れます。

Sub test()

Dim ch As Chart
Set ch = ActiveSheet.ChartObjects.Add(10, 10, 400, 300).Chart
ch.ChartType = xlXYScatter
ch.SetSourceData Source:=Range(Columns(1), Columns(2))

End Sub
データソースを設定するコード

「Source := データの範囲」でデータを指定しましょう。
このとき、Range(Columns(1), Columns(2))で１,２列を合わせて指定できます。

散布図作成例

このように散布図が作成できればOK！！

指定する列が離れている場合

以下のように３列にデータが入っているとします。
A列とC列で散布図を作成しましょう。

データの入力例

RangeではなくUnionにしてデータを指定すればOKです。

Sub test()

Dim ch As Chart
Set ch = ActiveSheet.ChartObjects.Add(10, 10, 400, 300).Chart
ch.ChartType = xlXYScatter
ch.SetSourceData Source:=Union(Columns(1), Columns(3))

End Sub

新規系列作成

マーカーの形とか系列名とか決めたくない？？

というわけで、新規系列としてデータを足してみましょう！
SeriesCollection.NweSeriesで新規に系列を作成します。

Sub test()

Dim ch As Chart
Set ch = ActiveSheet.ChartObjects.Add(10, 10, 400, 300).Chart
ch.ChartType = xlXYScatter

With ch.SeriesCollection.NewSeries
    .XValues = Range(Cells(2, 1), Cells(Rows.Count, 1))
    .Values = Range(Cells(2, 2), Cells(Rows.Count, 2))
End With

End Sub
Withでコードをまとめた例

XvaluesがX軸のデータ、ValuesがY軸のデータです。
範囲の指定では、RangeとCellsを組み合わせています。

セルを選択するコード例

この方法については、こちらの記事で詳しく解説しています。
Cells(1, 1)を含めると、項目名も入ってしまうので注意してください。

また、Withはオブジェクトの指定を省略するコードです。

Withでコードを省略した例

“With ～ End With”の間では、
” . “で始まるコードは”ch.SeriesCollection.NewSeries”の続きとなります。
こうすると長いコードを何度も書かなくていいので楽です。

系列名

系列名はNameで指定します。

Sub test()

Dim ch As Chart
Set ch = ActiveSheet.ChartObjects.Add(10, 10, 400, 300).Chart
ch.ChartType = xlXYScatter

With ch.SeriesCollection.NewSeries
    .XValues = Range(Cells(2, 1), Cells(Rows.Count, 1))
    .Values = Range(Cells(2, 2), Cells(Rows.Count, 2))
    .Name = "散布図１"
End With

End Sub

実行すると系列名が変更されているはずです。

マーカーの種類

マーカーの種類はMarkerStyleで指定します。

Sub test()

Dim ch As Chart
Set ch = ActiveSheet.ChartObjects.Add(10, 10, 400, 300).Chart
ch.ChartType = xlXYScatter

With ch.SeriesCollection.NewSeries
    .XValues = Range(Cells(2, 1), Cells(Rows.Count, 1))
    .Values = Range(Cells(2, 2), Cells(Rows.Count, 2))
    .Name = "散布図１"
    .MarkerStyle = xlMarkerStyleCircle
End With

End Sub
形状コード
■xlMarkerStyleSquare
〇xlMarkerStyleCircle
△xlMarkerStyleTriangle
◇xlMarkerStyleDiamond

上図では〇にしています。他にはXとかーとかいろいろあります。

マーカーの色

枠線の色：.MarkerForegroundColor
内部の色：.MarkerBackgroundColor
Sub test()

Dim ch As Chart
Set ch = ActiveSheet.ChartObjects.Add(10, 10, 400, 300).Chart
ch.ChartType = xlXYScatter

With ch.SeriesCollection.NewSeries
    .XValues = Range(Cells(2, 1), Cells(Rows.Count, 1))
    .Values = Range(Cells(2, 2), Cells(Rows.Count, 2))
    .Name = "散布図１"
    .MarkerStyle = xlMarkerStyleCircle
    .MarkerForegroundColor = vbRed
    .MarkerBackgroundColor = vbRed
End With

End Sub

色の指定方法にはvbColorやRGBがあります。
【関連記事】Excelマクロで書式設定をする方法

縦軸横軸の自動切換え

下図のセル(F2, F3)を変えたときに、グラフの軸が変わるようにしてみましょう。

データ入力欄の例

ドロップダウンの作成

まずは横軸と縦軸を設定するセルに入力規制を加えます。

データの入力規則の例

対象は１行目の項目にしましょう。
するとドロップダウンで項目名が選択できるようになります。

プログラム上で散布図を取得する

今回は散布図が作成済みとしているので、グラフを指定しましょう。
作成済みのグラフを取得するには、ChartObjects(数字).Chartと書きます。

Sub test()

Dim ch As Object
Set ch = ActiveSheet.ChartObjects(1).Chart
MsgBox ch.Name

End Sub

ActiveSheetは現在編集対象のシートのことです。

オブジェクトの複数指定の例

もし他にグラフを作成している場合は、ChartObjects(2)だったり(3)だったりします。

グラフ１を指定する例

実行すると取得したグラフの名前を表示します。
設定したいグラフを選べているか確認しておきましょう。

横軸と縦軸の列を取得する

F2,3のセルに設定した項目名に対応するセルの番号を取得します。

Sub test()

Dim ch As Object
Set ch = ActiveSheet.ChartObjects(1).Chart

x = Range("F2")
y = Range("F3")
xCol = Rows(1).Find(x).Column
yCol = Rows(1).Find(y).Column
MsgBox xCol
MsgBox yCol

End Sub
XYの入力を受け取った図

選択した項目名と列番号が一致してたらOKです。
【関連記事】ExcelマクロのFind(FindNext)でセルを検索する方法

縦軸と横軸のデータを変更する

系列のデータ範囲を指定しましょう。

Sub test()

Dim ch As Object
Set ch = ActiveSheet.ChartObjects(1).Chart

x = Range("F2")
y = Range("F3")
xCol = Rows(1).Find(x).Column
yCol = Rows(1).Find(y).Column

endRow = Cells(Rows.Count, 1).End(xlUp).Row
ch.SeriesCollection(1).XValues = Range(Cells(2, xCol), Cells(endRow, xCol))
ch.SeriesCollection(1).Values = Range(Cells(2, yCol), Cells(endRow, yCol))

End Sub

endRowではデータが入っている最終行を取得しています。
Rows.Countで最下行まで移動しEnd(xlUp)でCtrl + ↑を実行するコードです。

RangeとCellsで範囲指定する例

Findで取得したxColとyColを使って範囲を設定しましょう。
Range(上のセル, 下のセル)で指定します。

軸の項目を変更した図

このように項目名を変えて実行すると、縦軸横軸が変わります。

イベントプロシージャで自動起動させる

イベントプロシージャを使えば、セルを編集したときにプログラムを実行できます。
“VisualBasic”の左にあるリストから、散布図を作成したシートをダブルクリックしましょう。

シートにイベントプロシージャを登録する図

左上を”Worksheet”右上を”Change”にすると、セルが編集されたときに実行されます。

セルを編集したら起動するコードの例
Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox Target.Row
MsgBox Target.Column

End Sub

このコードを書くと、編集されたセルの位置が表示されます。

セルの変更をtargetで受け取る例

編集されたセルが変数Targetとして受け取られるので、Target.Rowで行番号になります。
試しに適当なセルを編集して、行と列が表示されれば成功しています。
これを利用して、編集したセルのデータを取っていきましょう。

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Row = 2 Or Target.Row = 3) And Target.Column = 6 Then
    MsgBox Target.Value
End If

End Sub

もしTargetが2,3行でF列ならその値を表示するようにしました。

セルを編集したら値を受け取る図

横軸または縦軸の項目を変えると、その値が表示されるはずです！
あとはさっきのプログラムを追加するだけですね。↓

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Row = 2 Or Target.Row = 3) And Target.Column = 6 Then
    Dim ch As Object
    Set ch = ActiveSheet.ChartObjects(1).Chart
    x = Range("F2")
    y = Range("F3")
    xCol = Rows(1).Find(x).Column
    yCol = Rows(1).Find(y).Column
    endRow = Cells(Rows.Count, 1).End(xlUp).Row
    ch.SeriesCollection(1).XValues = Range(Cells(2, xCol), Cells(endRow, xCol))
    ch.SeriesCollection(1).Values = Range(Cells(2, yCol), Cells(endRow, yCol))
End If

End Sub

if文の中に軸を切り替えるプログラムをそのまま入れました。
これでもしF2,3の項目が変わると勝手に散布図が切り替わります。

散布図の例

まとめ

今回はVBAで散布図を作成する方法について紹介しました。
単純な散布図作成なら手動で間に合いますが、VBAなら完全に自動化することができます。

>>Excelマクロでセル,シート,ファイルを指定する方法
>>Excelマクロで書式を変える方法

コメント

タイトルとURLをコピーしました