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

VBA
スポンサーリンク

VBAで散布図を作るのは簡単です。

要点さえ理解できたら初心者でも実装できますよ!

・散布図を自動作成したい

・X軸Y軸を自動選択してほしい

・マーカーとか自動で設定したい

こんな悩みを解決する内容です。

データ準備

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

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

データ例

グラフエリア作成

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

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列データが入っている例
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

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

新規系列作成

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

というわけで、新規系列としてデータを足してみましょう!

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があります。

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

vbColor(vbRed, vbBlue…)が直感的に書けるのでおススメです。

縦軸横軸の自動切換え

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

あらかじめ散布図を作成しておきましょう。

横軸と縦軸を指定する図

上図のセル(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なら完全に自動化することができます。

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

VBA
スポンサーリンク
HTOMblog

コメント

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