【Excelマクロ】グラフの参照範囲を自動変更するマクロを作ったよ！

自称Excel通のmasa（＠ masa）です。

 

日々であったり、月々であったり定形的に入力するデータがあって、そのグラフの参照範囲を常に手動で変更するのってめんどくさくないですか？

 

…僕はめんどうだと感じました。

 

グラフの参照範囲の変更時の手順は①最新のデータ範囲はどこまでか？、②そのデータを対象として範囲を変更するという判断基準が非常に明確なので『マクロを使って自動化しよう』と思い立ちコードを作ってみました。

 

STEP1.最深データ行を取得する。

参照範囲の先頭は一番最初の行として、参照範囲の最後は一番最後の行を取得します。

一番最後の行（最深行）は上から探していくと未入力のセルを検知してしまうので「セルの一番下から上に向かって入力済のセルを検索するマクロを使います。」

例として以下のデータを準備しました。

図1　6/28まで入力したデータ

このときの最深データ行はNo.6(2017/6/28)となっていることが分かります。

 Code1

 最深行 = Cells(Rows.Count, 3).End(xlUp).Row

ここで『3』はC列を指します。C列の最も下の行を取得します。この場合は『最深行=8（数値）』を得ることができます。

STEP2.参照範囲の自動変更

図1から入力範囲を7/1までに更新した図2を用意しました。

図2　7/1までのデータを追加したデータ

図2のグラフを見るとわかるように表は7/1まで記載があるのに対し、グラフは6/28のままになっています。

これを7/1まで自動的に変更するコードが以下です。

Code2

 最深行 = Cells(Rows.Count, 3).End(xlUp).Row

ActiveSheet.ChartObjects("グラフ 1").Activate

'体重
ActiveChart.SeriesCollection(1).Name = "体重(kg)"
ActiveChart.SeriesCollection(1).XValues = Range(Cells(3, 3), Cells(最深行, 3))
ActiveChart.SeriesCollection(1).Values = Range(Cells(3 4), Cells(最深行, 4))

'体脂肪
ActiveChart.SeriesCollection(2).Name = "体脂肪(%)"
ActiveChart.SeriesCollection(2).XValues = Range(Cells(3, 3), Cells(最深行, 3))
ActiveChart.SeriesCollection(2).Values = Range(Cells(3 5), Cells(最深行, 5))

'歩数
ActiveChart.SeriesCollection(3).Name = "歩数(千歩)"
ActiveChart.SeriesCollection(3).XValues = Range(Cells(3, 3), Cells(最深行, 3))
ActiveChart.SeriesCollection(3).Values = Range(Cells(3, 6), Cells(最深行, 6))

Code2解説

コードとして処理していることとして系列1～系列3までに対して①系列名、②横軸、③縦軸の範囲を指定しています。

黒：対象とするグラフ名

グラフを選択すると左上にグラフ名が出てくるのでその名前としましょう。

青：系列

▼抜粋版Code2

'体重
ActiveChart.SeriesCollection(1).Name = "体重(kg)"
ActiveChart.SeriesCollection(1).XValues = Range(Cells(3, 3), Cells(最深行, 3))
ActiveChart.SeriesCollection(1).Values = Range(Cells(3 4), Cells(最深行, 4))

(1)～(3)とはそれぞれ系列1～系列4までを表しています。4種類のデータがある場合は(4)を追加してください。

橙：系列、軸

Name：系列名、XValues：横軸、Values：縦軸を表しています。

紫：参照範囲

▼抜粋版Code2

'体重
ActiveChart.SeriesCollection(1).Name = "体重(kg)"
ActiveChart.SeriesCollection(1).XValues = Range(Cells(3, 3), Cells(最深行, 3))
ActiveChart.SeriesCollection(1).Values = Range(Cells(3 4), Cells(最深行, 4))

紫は噛み砕いて説明していきます。

  Range(Cells(○), Cells(□))

Rangeの中に座標を2つ指定することで範囲を設定することができます。例えばA4～B10であれば次の通りです。

Range(Range("A4"), Range("B10"))

しかしRangeで指定できる座標は与えたい座標があらかじめ決まっている場合に対してのみ有効なので、変化するものに対してはCellsを使います。

  Cells(×,△) 

 ×：行、△：列を意味します。C5であれば、×：5、△：3となります。

日付を例にするとC3【Cells(4,3)】～C11（最深行）【Cells(最深行,3)】となるので、 Range(Cells(3, 3), Cells(最深行, 3))となります。

実行結果

▼こんな感じでグラフ範囲が自動変更できました。

▼Code2を再び貼っておきます。色付き、太字は適宜修正する必要があります。

 最深行 = Cells(Rows.Count, 3).End(xlUp).Row

ActiveSheet.ChartObjects("グラフ 1").Activate

'体重
ActiveChart.SeriesCollection(1).Name = "体重(kg)"
ActiveChart.SeriesCollection(1).XValues = Range(Cells(3, 3), Cells(最深行, 3))
ActiveChart.SeriesCollection(1).Values = Range(Cells(3 4), Cells(最深行, 4))

'体脂肪
ActiveChart.SeriesCollection(2).Name = "体脂肪(%)"
ActiveChart.SeriesCollection(2).XValues = Range(Cells(3, 3), Cells(最深行, 3))
ActiveChart.SeriesCollection(2).Values = Range(Cells(3 5), Cells(最深行, 5))

'歩数
ActiveChart.SeriesCollection(3).Name = "歩数(千歩)"
ActiveChart.SeriesCollection(3).XValues = Range(Cells(3, 3), Cells(最深行, 3))
ActiveChart.SeriesCollection(3).Values = Range(Cells(3, 6), Cells(最深行, 6))

おまけ

マクロを使うときの便利なショートカットキー

Alt + F11
エディターの起動

F5(Sub～End Subの間にカーソルがあるとき)
コードの実行

 参考文献

Office TANAKAさんさすがでした。これを参考に自分なりのコードにしました。

Office TANAKA - Excel VBA Tips[グラフの参照範囲を変更する]

オススメ

この書籍もかなり分かりやすかったです。マクロなのに小説仕立てという摩訶不思議…。良かったら是非。

Excel VBAの神様 ボクの人生を変えてくれた人

posted with ヨメレバ

大村 あつし 秀和システム 2015-09-25れて

Amazon

Kindle

楽天ブックス

楽天kobo

 

こんな記事も書いています。

www.think-and-try.xyz