エクセルで、既存の複数のグラフのデータ範囲を一発で変更する方法を紹介します。
グラフを複製して、そのグラフのデータ範囲を変更する場合にも使えます。
ほぼコピペで使えるので、マクロに抵抗がある人も使えますよ。
想定する状況
会社の業務で作成したデータでいくつかのグラフを作成。
見やすくするため元データのシートとグラフのシートを別にしていたとします。
年度が変わったので、新しいグラフにまとめようと考えグラフをシートごとコピー。
しかし、グラフのデータ範囲を変えるにはグラフ一つ一つのデータ範囲を変更する必要があります。
ブックごとコピーしてデータを全て新しくすれば、データ範囲を変更せずに変えられますが、通算のデータや記録も残したいなど、ブックを分けたくない場合もありますよね。
例として、散布図で測定値を時系列で記録するエクセルを作ってみました。A、Bの２つの項目を時系列で見ます。例としては２つのデータ項目ですが、項目やグラフが大量にある状況を想定しています。
マクロを使わない方法
この状態からグラフ範囲を変更しようとすると、主に以下の方法が考えられます。
- データの選択で範囲を選び直す
- データ系列を左クリックし、範囲をドラッグ＆ドロップして変更する
- データ系列を左クリックし、数式バーの数式を編集する
1. データの選択で範囲を選び直す
最も基本的な方法です。そして最も面倒くさい方法です。
１つのデータ範囲を変えるのに7,8回クリックしないといけないのと、範囲を選び直すのも面倒です。
2. データ系列を左クリックし、範囲をドラッグ＆ドロップして変更する
一見簡単な方法です。しかし今回のケースのようにグラフとデータが違うシート、ブックにあると使えません。
一旦グラフをデータのシートに持ってくると使えますが手間です。
また、１つのデータ範囲を変えるのに3回程度クリックしないといけないです。
これもグラフが大量にあると面倒です。
3. データ系列を左クリックし、数式バーの数式を編集する
「A 2019」のグラフのデータ系列を左クリックすると、数式にバーに以下の数式が表示されます。
=SERIES(,データ!$B$2:$M$2,データ!$B$3:$M$3,1)
これは横軸のデータ範囲がシート「データ」の$B$2:$M$2、縦軸のデータ範囲がシート「データ」の$B$3:$M$3であることを表しています。
この数式を直接修正することでもデータの範囲は変更できます。
2019年のデータに変更するには、横軸のデータ範囲をシート「データ」の$N$2:$S$2、縦軸のデータ範囲をシート「データ」の$N$3:$S$3に変更します。
=SERIES(,データ!$N$2:$S$2,データ!$N$3:$S$3,1)
B → N と M → S の入れ替えを行います。
この方法ならグラフとデータが違うシート、ブックにあっても使うことができます。
しかし、やっぱり大量にグラフがある場合は面倒です。
置換を使ってB→N、M→Sに入れ替える方法も思いつきますが、グラフのデータ系列は検索を行うことができません。
複数のグラフ範囲を一発で変更するVBAコード
VBAの初期設定はこちらの記事を読んでください。
また、こちらのコードは以下の知恵袋を参考にしています。
以下が今回紹介するVBAコードです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub グラフ範囲変更() Dim chartObj As ChartObject Dim srs As Series For Each chartObj In ActiveSheet.ChartObjects For Each srs In chartObj.Chart.SeriesCollection srs.Formula = Replace(srs.Formula, "$B$", "$N$") srs.Formula = Replace(srs.Formula, "$M$", "$S$") Next Next End Sub |
このVBAコードでシート上の全グラフのデータ範囲が変わります。
このプラグラムのやっていることは
シート上の全グラフに対し、B→N と M→S の入れ替えを行っています。
今回のケース以外にも応用可能です。列が変わればそれによって「”$B$”, “$N$”」の文字列を変更します。A→Gに入れ替えたければ「”$A$”, “$G$”」に、F→BCに入れ替えたければ「”$F$”, “$BC$”」に、といった具合です。
この方法は３番目の方法をマクロを用いてシート上全グラフに対して行っています。
手動でやると１つのグラフにつき20秒くらいかかるので、３つグラフがあれば1分、10個グラフがあれば200秒、3分以上節約できます！
コードの解説
For eachを使わずに書いてみる
まずFor eachを使わずにコードを書くと以下のようになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub グラフ範囲変更() ActiveSheet.ChartObjects("グラフ 1").Chart.SeriesCollection(1).Formula = _ Replace(ActiveSheet.ChartObjects("グラフ 1").Chart.SeriesCollection(1).Formula, "$B$", "$N$") ActiveSheet.ChartObjects("グラフ 1").Chart.SeriesCollection(1).Formula = _ Replace(ActiveSheet.ChartObjects("グラフ 1").Chart.SeriesCollection(1).Formula, "$M$", "$S$") ActiveSheet.ChartObjects("グラフ 2").Chart.SeriesCollection(1).Formula = _ Replace(ActiveSheet.ChartObjects("グラフ 2").Chart.SeriesCollection(1).Formula, "$B$", "$N$") ActiveSheet.ChartObjects("グラフ 2").Chart.SeriesCollection(1).Formula = _ Replace(ActiveSheet.ChartObjects("グラフ 2").Chart.SeriesCollection(1).Formula, "$M$", "$S$") End Sub |
まず以下の１文を見ていきます。
|
1 2 |
ActiveSheet.ChartObjects("グラフ 1").Chart.SeriesCollection(1).Formula = _ Replace(ActiveSheet.ChartObjects("グラフ 1").Chart.SeriesCollection(1).Formula, "$B$", "$N$") |
この一文は以下の動作を意味しています。
アクティブシート（ActiveSheet）のグラフ１というグラフ（ChartObjects(“グラフ 1”)）のチャート（Chart）のデータ系列1（SeriesCollection(1)）の数式（Formula）の$B$を$N$を置き換える。
.Formula = _ の「_」は改行のときにつける記号です。それ自体に意味はありません。
VBAでは1行で書かなければならない記述は「_」無しで改行するとエラーになります。
ChartObjects(“グラフ 1”)のグラフ 1は、グラフをクリックしたときに名前ボックスに表示されます。
|
1 |
Replace(A, b, c) |
ReplaceはAの中のbという文字列をcに置換するという関数です。
|
1 |
A = Replace(A, b, c) |
この表現はAに（bという文字列をcに置換したA）を代入する。という意味です。
プログラミングにおいて=は等しいではなく右辺を左辺に代入するという意味です。
x = x + 1 で xに（xに１を足したもの）を代入する すなわち xに1を足す となります。
A = Replace(A, b, c) は Aのbという文字列をcに置換する となります。
ここで１つポイントは“B”, “N”ではなく”$B$”, “$N$”というふうにB、Nのそれぞれの前後に$マークをつけていることです。
これは文字列の前後に$マークをつけるというルールがあるわけではありません。
=SERIES(,データ!$B$2:$M$2,データ!$B$3:$M$3,1)
データ系列の数式のB単体ではなく、$B$のセットで$N$に置き換えるという意味です。
例えば以下の数式の場合
=SERIES(,データ!$S$2:$V$2,データ!$S$3:$V$3,1)
$をつけずに以下のコードにしたとします。
|
1 |
Replace(ActiveSheet.ChartObjects("グラフ 1").Chart.SeriesCollection(1).Formula, "S", "T") |
すると数式は以下のようになります。
=TERIET(,データ!$T$2:$V$2,データ!$T$3:$V$3,1)
SERIESのSまでTに変わってしまいます。今回のコードはデータ範囲を変更するというよりも、データ範囲の数式の文字を置き換えるというコードのため、このようになってしまいます。（実際にはエラーで止まります）
データ範囲のSだけを置き換えるため、文字列の前後に$をつけて置換します。
これでグラフ1のBをNに置き換えることは完了しました。
しかし、まだMをSに置き換えなければなりませんし、グラフ２についても置換を行う必要があります。グラフ１つにつき２回コードを書かなればならず面倒です。
このままではコードを再利用する際に、コード中の文字列の変更をグラフ数×２回行わなければならず、３番目の方法とあまり変わらなくなってしまいます。
そこでFor eachを使います。
For eachでコードをより完結に
|
1 2 3 4 |
Dim a For Each a In X 処理（変数a) Next |
Dimは変数を定義するときに使います。VBAでは変数を使用する際に、Dimを使って定義します。ここについてはこういうものだと覚えるしかないです。
グループXの全要素をaに入れて処理を繰り返します。
１週目はグループXの１つ目の要素をaに入れて処理を実行
２週目はグループXの２つ目の要素をaに入れて処理を実行
・・・
という感じです。
今回のケースではこのFor Eachをネスト（入れ子）にして使用しています。
|
1 2 3 4 5 6 7 8 |
Dim a Dim b For Each a In X For Each b In Y 処理（変数a,b) Next Next |
グループXの全要素をaに入れ、グループYの全要素をbに入れ処理を繰り返します。
グループXに２つの要素、グループYに３つの要素があった場合以下のように進みます。
1週目はグループXの1つ目の要素をaに、グループYの1つ目の要素をbに入れて処理を実行
2週目はグループXの1つ目の要素をaに、グループYの2つ目の要素をbに入れて処理を実行
3週目はグループXの1つ目の要素をaに、グループYの3つ目の要素をbに入れて処理を実行
4週目はグループXの2つ目の要素をaに、グループYの1つ目の要素をbに入れて処理を実行
5週目はグループXの2つ目の要素をaに、グループYの2つ目の要素をbに入れて処理を実行
6週目はグループXの2つ目の要素をaに、グループYの3つ目の要素をbに入れて処理を実行
となり、２✕３＝6回の処理を繰り返します。
これにより、より完結にコードを書けます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub グラフ範囲変更() Dim chartObj As ChartObject Dim srs As Series For Each chartObj In ActiveSheet.ChartObjects For Each srs In chartObj.Chart.SeriesCollection srs.Formula = Replace(srs.Formula, "$B$", "$N$") srs.Formula = Replace(srs.Formula, "$M$", "$S$") Next Next End Sub |
シート上にいくつグラフがあってもこの文章のみで置き換えが可能です。
そして、別の置き換えを行う場合も”$B$”, “$N$” 、”$M$”, “$S$”の2箇所を書き換えるだけで利用可能です。
コメント