【簡単マクロVBA術】複数のグラフ範囲を一発で変更する方法

プログラミング

エクセルで、既存の複数のグラフのデータ範囲を一発で変更する方法を紹介します。

グラフを複製して、そのグラフのデータ範囲を変更する場合にも使えます。

ほぼコピペで使えるので、マクロに抵抗がある人も使えますよ。

想定する状況

会社の業務で作成したデータでいくつかのグラフを作成。

見やすくするため元データのシートとグラフのシートを別にしていたとします。

年度が変わったので、新しいグラフにまとめようと考えグラフをシートごとコピー。

しかし、グラフのデータ範囲を変えるにはグラフ一つ一つのデータ範囲を変更する必要があります。

ブックごとコピーしてデータを全て新しくすれば、データ範囲を変更せずに変えられますが、通算のデータや記録も残したいなど、ブックを分けたくない場合もありますよね。

例として、散布図で測定値を時系列で記録するエクセルを作ってみました。A、Bの2つの項目を時系列で見ます。例としては2つのデータ項目ですが、項目やグラフが大量にある状況を想定しています。

シート1 「データ」
シート2 「2018」
シート3 「2019」

マクロを使わない方法

この状態からグラフ範囲を変更しようとすると、主に以下の方法が考えられます。

  1. データの選択で範囲を選び直す
  2. データ系列を左クリックし、範囲をドラッグ&ドロップして変更する
  3. データ系列を左クリックし、数式バーの数式を編集する

1. データの選択で範囲を選び直す

最も基本的な方法です。そして最も面倒くさい方法です。

1つのデータ範囲を変えるのに7,8回クリックしないといけないのと、範囲を選び直すのも面倒です。

2. データ系列を左クリックし、範囲をドラッグ&ドロップして変更する

一見簡単な方法です。しかし今回のケースのようにグラフとデータが違うシート、ブックにあると使えません。

一旦グラフをデータのシートに持ってくると使えますが手間です。

また、1つのデータ範囲を変えるのに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に入れ替える方法も思いつきますが、グラフのデータ系列は検索を行うことができません。

文字の置換(ちかん)を一瞬で行うショートカットキー | VAIO | ソニー
「置換」のショートカットキーを使って、一瞬で文字の置き換えを行う方法をご説明します。

複数のグラフ範囲を一発で変更するVBAコード

VBAの初期設定はこちらの記事を読んでください。

また、こちらのコードは以下の知恵袋を参考にしています。

エクセルでグラフの参照先を一括で変更する方法は? - 仕事で、エクセルのグラフを使っているのですが、例えば、グラフの参照先... - Yahoo!知恵袋
エクセルでグラフの参照先を一括で変更する方法は? 仕事で、エクセルのグラフを使っているのですが、例えば、グラフの参照先が下記のようだったとして、=SERIES(D:11\残業管理月'!$P:$P,3)〇〇は作業者の名前が入ります。「4月」は翌年の3月まであります。年度が変わったため、2011を201...

以下が今回紹介するVBAコードです。

このVBAコードでシート上の全グラフのデータ範囲が変わります。

このプラグラムのやっていることは

シート上の全グラフに対し、B→N と M→S の入れ替えを行っています。

今回のケース以外にも応用可能です。列が変わればそれによって「”$B$”, “$N$”」の文字列を変更します。A→Gに入れ替えたければ「”$A$”, “$G$”」に、F→BCに入れ替えたければ「”$F$”, “$BC$”」に、といった具合です。

この方法は3番目の方法をマクロを用いてシート上全グラフに対して行っています。

手動でやると1つのグラフにつき20秒くらいかかるので、3つグラフがあれば1分、10個グラフがあれば200秒、3分以上節約できます!

コードの解説

For eachを使わずに書いてみる

まずFor eachを使わずにコードを書くと以下のようになります。

まず以下の1文を見ていきます。

この一文は以下の動作を意味しています。

アクティブシート(ActiveSheet)のグラフ1というグラフ(ChartObjects(“グラフ 1”))のチャート(Chart)のデータ系列1(SeriesCollection(1))の数式(Formula)の$B$を$N$を置き換える。

.Formula = _ の「_」は改行のときにつける記号です。それ自体に意味はありません。

VBAでは1行で書かなければならない記述は「_」無しで改行するとエラーになります。

ChartObjects(“グラフ 1”)のグラフ 1は、グラフをクリックしたときに名前ボックスに表示されます。

ReplaceはAの中のbという文字列をcに置換するという関数です。

この表現はAに(bという文字列をcに置換したA)を代入する。という意味です。

プログラミングにおいて=は等しいではなく右辺を左辺に代入するという意味です。

 x = x + 1 で xに(xに1を足したもの)を代入する すなわち xに1を足す となります。

A = Replace(A, b, c) は Aのbという文字列をcに置換する となります。

ここで1つポイントは“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)

$をつけずに以下のコードにしたとします。

すると数式は以下のようになります。

=TERIET(,データ!$T$2:$V$2,データ!$T$3:$V$3,1)

SERIESのSまでTに変わってしまいます。今回のコードはデータ範囲を変更するというよりも、データ範囲の数式の文字を置き換えるというコードのため、このようになってしまいます。(実際にはエラーで止まります)

データ範囲のSだけを置き換えるため、文字列の前後に$をつけて置換します。

これでグラフ1のBをNに置き換えることは完了しました。

しかし、まだMをSに置き換えなければなりませんし、グラフ2についても置換を行う必要があります。グラフ1つにつき2回コードを書かなればならず面倒です。

このままではコードを再利用する際に、コード中の文字列の変更をグラフ数×2回行わなければならず、3番目の方法とあまり変わらなくなってしまいます。

そこでFor eachを使います。

For eachでコードをより完結に

Dimは変数を定義するときに使います。VBAでは変数を使用する際に、Dimを使って定義します。ここについてはこういうものだと覚えるしかないです。

グループXの全要素をaに入れて処理を繰り返します。

1週目はグループXの1つ目の要素をaに入れて処理を実行

2週目はグループXの2つ目の要素をaに入れて処理を実行

・・・

という感じです。

今回のケースではこのFor Eachをネスト(入れ子)にして使用しています。

グループXの全要素をaに入れ、グループYの全要素をbに入れ処理を繰り返します。

グループXに2つの要素、グループYに3つの要素があった場合以下のように進みます。

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に入れて処理を実行

となり、2✕3=6回の処理を繰り返します。

これにより、より完結にコードを書けます。

シート上にいくつグラフがあってもこの文章のみで置き換えが可能です。

そして、別の置き換えを行う場合も”$B$”, “$N$” 、”$M$”, “$S$”の2箇所を書き換えるだけで利用可能です。

Excel VBAの教科書 (Informatics & IDEA) | 古川 順平 |本 | 通販 | Amazon
Amazonで古川 順平のExcel VBAの教科書 (Informatics & IDEA)。アマゾンならポイント還元本が多数。古川 順平作品ほか、お急ぎ便対象商品は当日お届けも可能。またExcel VBAの教科書 (Informatics & IDEA)もアマゾン配送商品なら通常配送無料。

コメント

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