VBAでグラフのデータ範囲を変更する方法を使用して、選択されている行のデータを次々とグラフに反映させる方法をご説明します。
何十行もあるデータをまとめグラフに反映させると、系列が多すぎてよくわからないグラフが出来上がってしまう事がよくあります。
そんなグラフを見たい行のデータだけを次々グラフに反映することで、シンプルで且つ迅速にデータを可視化することが可能です。
さらにグラフはスクロールすると一緒に動いてしまうため、グラフを固定する方法も併せてご説明します。
その他のグラフの操作については下記記事をご覧ください。
1.グラフとデータを用意する
まずはグラフと、グラフに反映したいデータを用意します。
VBAでグラフを作成しても構いませんが、今回の目的は次々と選択セルを変えながら、グラフの範囲を変更する方法ですので予め用意したグラフを使用します。
今回作成する動作イメージの動画です。
グラフに反映させるデータの準備
それでは次のようにA列の15行目から商品名、15行目を横に日付、数字は日次の商品の販売個数としたいと思います。
見切れてしまっていますが、31日分のデータが存在します。
15行目から始めているのはこの上部にグラフを挿入します。
今回はA16〜A最終行の選択が変更されるとグラフのデータ範囲が書き換わるようにします。
グラフの準備
次は作成したデータを反映させるグラフを用意します。
グラフのデータ範囲は現時点では適当でも構いません。
単純にA15～AF16の2行をデータ範囲としたグラフを作成しました。
ついでに線形の近似曲線も追加しています。
もしVBAでグラフを作成する場合は「グラフ(Chart)操作 グラフの作成と詳細設定をする方法」をご覧下さい。
近似曲線の追加方法は、追加したい系列を右クリックして「近似曲線の追加」を選択して、曲線のタイプを指定するだけです。
とりあえずこれでデータとグラフが出来上がりました。
このデータの商品名の選択を変更するだけで、グラフが選択されている商品に変更されるVBAを作成します。
2.グラフを固定する
グラフを固定する工夫です。
グラフはExcelをスクロールすると一緒に移動してしまいますので、表示しきれないデータを選択するとグラフが消えて見えなくなります。
そこで、グラフを常に見えるように固定します。
グラフを一番上に設置したのには理由があります。
表示しているところで固定させるには少し複雑になるため、今回はExcelの機能の「ウィンドウ枠の固定」を利用してグラフを固定させます。
そのために一番上に設置しています。
今回は15行目でウィンドウを固定します。
3.データ範囲を変更するコード
VBAで作成済みのグラフのデータ範囲を変更する方法です。
埋め込み済みのグラフを変更する場合は、方法が2つあります。
「SetSourceData」メソッドでグラフの参照データを変更する方法と、「Formula」プロパティで参照データの数式を変更する方法です。
個人的には「SetSourceData」メソッドで操作する方がシンプルで簡単なため、こちらを使用する事をおすすめします。
今回は「SetSourceData」メソッドで変更する方法を使用します。
グラフデータの変更は「作成済みのグラフ(Chart)のデータ範囲を変更する」をご覧ください。
また、15行目と16行目以外の場合は離れたデータを反映させる必要があるため、「Union」メソッドでデータを指定しています。
離れたデータをグラフに反映させる方法については「離れたデータでグラフ(Chart)を作成する」をご覧ください。
Sub Sample1()
Dim ChartObj As Object
Dim TargetRng As Range
Dim TargetRow As Long
Dim MaxCol As Long
'15行目の最終列を取得
MaxCol = Cells(15, Columns.Count).End(xlToLeft).Column
'選択している行を取得
TargetRow = ActiveCell.Row
'データが連続していない事を想定して離れたデータを反映
Set TargetRng = Union(Range(Cells(15, 1), Cells(15, MaxCol)), _
Range(Cells(TargetRow, 1), Cells(TargetRow, MaxCol)))
'グラフを変数に格納
Set ChartObj = ActiveSheet.ChartObjects(1)
With ChartObj.Chart
'グラフにデータをセット
.SetSourceData TargetRng
End With
End Subコードの説明
基本的にはコメントに記述した通りです。
「MaxCol = Cells(15, Columns.Count).End(xlToLeft).Column」でデータ範囲の列を最終列まで指定するために、最終列を取得しています。
「TargetRow = ActiveCell.Row」で現在選択しているアクティブセルの行を取得しています。
次がポイントになりますが、
「Set TargetRng = Union(Range(Cells(15, 1), Cells(15, MaxCol)), _
Range(Cells(TargetRow, 1), Cells(TargetRow, MaxCol)))」は長いですが、15行目(固定)と選択している行のデータを指定してUnionでまとめて変数にセットしています。
「Set ChartObj = ActiveSheet.ChartObjects(1)」でシート上に埋め込まれたグラフを指定します。
もし複数のグラフが存在した場合はインデックス番号もしくはグラフ名で指定してください。
「.SetSourceData TargetRng」で先ほどのセットしたデータ範囲をグラフに反映させています。
予めグラフを用意する事で非常にシンプルなコードになります。
4.選択を変更した時にデータ範囲を変更するコード
上記のサンプルコードでグラフのデータ範囲を変更する事が出来ました。
ですが、これでは毎回実行しなければグラフにデータが変更されませんので「Worksheet_SelectionChange」イベントで選択が変更された場合にデータ範囲を反映させるコードを呼び出します。
ただ呼び出すだけでは常に選択が変わると呼び出されてしまうため、A15～A最終行までが変更された場合のみイベントが実行されるようにします。
Worksheet_SelectionChangeの作り方
「Worksheet_SelectionChange」イベントはVBEを開いてグラフの埋め込まれているシートの「シートモジュール」で作ります。
今回はSheet1に作成したので「Sheet1(Sheet1)」モジュールをWクリックで開きます。
下記図の青く選択されている箇所が「Sheet1(Sheet1)」モジュールです。
Wクリックすると下記図のように表示されるの「General」を「Worksheet」に変更します。
「Wprksheet」に変更すると自動的に「Worksheet_SelectionChange」イベントが作成されます。
これでイベントの準備が出来ました。
特定の範囲が変更された時のみ実行させる
次はA16～A最終行の範囲が変更された場合のみ実行されるようにコードを書きます。
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MaxRow As Long
MaxRow = Cells(Rows.Count, 1).End(xlUp).Row
If (Target.Row >= 16 And Target.Row <= MaxRow) And Target.Column = 1 Then
Call Sample1
End If
End Sub
コードの説明
「MaxRow = Cells(Rows.Count, 1).End(xlUp).Row」でA列の最終行を取得しています。
次がポイントです。
「If (Target.Row >= 16 And Target.Row <= MaxRow) And Target.Column = 1 Then」で選択している行が16以上、最終行以下、そして1列目の場合のみデータ範囲を変更する「Sample1」を呼び出す分岐条件を記述しています。
