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

VBA
スポンサーリンク

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

実行後の画面

データ準備

以下のデータを散布図にします。

自分で使えるデータがあるなら、それを使ってください。

データ例

グラフエリア作成

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

Sub test()

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

End Sub

実行すると、こんな感じでグラフを描画するエリアができるはずです。

シートを指定してから、ChartObjects.Addでエリアを追加します。

コードの解説図

シートの指定方法については、以下の記事を参考にしてください。

()の中でサイズを指定すればOKです。

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))で1,2列を合わせて指定できます。

実行後の画面

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

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

以下のように3列にデータが入っているとします。

A列とC列で散布図を作成しましょう。

3列データが入っている例

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
系列作成するコード例

XvaluesがX軸のデータ、ValuesがY軸のデータです。

範囲の指定では、RangeとCellsを組み合わせています。

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 = "散布図1"
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 = "散布図1"
    .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 = "散布図1"
    .MarkerStyle = xlMarkerStyleCircle
    .MarkerForegroundColor = vbRed
    .MarkerBackgroundColor = vbRed
End With

End Sub

色の指定方法にはvbColorやRGBがあります。

詳しくは記事を参考にどうぞ。

縦軸横軸の自動切換え

もっと…自動化とか…カッコいいことしたい!!

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

横軸と縦軸を指定する図

ドロップダウンの作成

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

ドロップダウンの作成方法

対象は1行目の項目にしましょう。

するとドロップダウンで項目名が選択できるようになります。

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

今回は散布図が作成済みとしているので、グラフを指定しましょう。

作成済みのグラフを取得するには、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
実行後の画面

選択した項目名と列番号が一致してたらOK!!

Findの使い方は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 + ↑を実行するコードです。

詳しくはExcelマクロで最終行(列)を取得する方法をご覧ください。

コードの解説図

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.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なら完全に自動化することができます。

コピペした後にこのプログラムを入れておけば完璧ですね!!

コメント

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