思考酒後

思考酒後

自分に入ってきた情報を定着、深化するために文章化

MENU

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

自称Excel通のmasa@ masaです。
 
日々であったり、月々であったり定形的に入力するデータがあって、そのグラフの参照範囲を常に手動で変更するのってめんどくさくないですか?
 
…僕はめんどうだと感じました。
 
グラフの参照範囲の変更時の手順は①最新のデータ範囲はどこまでか?、②そのデータを対象として範囲を変更するという判断基準が非常に明確なので『マクロを使って自動化しよう』と思い立ちコードを作ってみました。

 

f:id:masa_mn:20170701113538p:plain 

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

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

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

 

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

f:id:masa_mn:20170701122440p:plain

図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を用意しました。

f:id:masa_mn:20170701122629p:plain

図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までに対して①系列名、②横軸、③縦軸の範囲を指定しています。

黒:対象とするグラフ名

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

f:id:masa_mn:20170701125519p:plain

青:系列

▼抜粋版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となります。

 

f:id:masa_mn:20170701122629p:plain

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

 

 

実行結果

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

f:id:masa_mn:20170701125644p:plain

 

▼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[グラフの参照範囲を変更する]

 

オススメ

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

 

広告を非表示にする