Excel 2010で複数のグラフのデータ範囲を自動的に更新する方法

こんにちは！冬真っ只中で寒い日が続いてますね

さて今回は、毎月増えるアクセスログを集計する際、毎度グラフの範囲を手動で変更するのが面倒！という方もいると思うので、私の作成したデータ範囲を自動的に更新するエクセルを公開します。グラフのデータ範囲の更新自体はググれば色々と出てくるのですが、私の場合は数十件のグラフを一度に更新する必要があったため、グラフ一つ一つにあれこれ設定するのが大変でした。
そこで、やや強引ではありますが、ちょっとした工夫を入れて複数のグラフをより簡単に作成できる補助機能（VBA）も作成しました。

仕様

• ある会員制通販サイトにアクセスした、会員ごとの月間アクセスログ、及び購入数をグラフで表示する。
• 翌月分のデータを入力すると、自動でグラフのデータ範囲が変わる。
• VBAを使って、複数のグラフを作成する際の手間を省く。

手順

１．データの準備

まずはデータセットを準備します。今回は「月間アクセス数」と「月間購入数」を用意しました。なお、各データセットの項目は以下の様です。

　No.：会員番号
　名前：会員の名前
　年月：その年月の月間アクセス数、または購入数（"N"と記載しているのは、まだ該当年月において会員になっていないことを示す）

続いて、有効データ数を計算するための[計算用シート1]を作成します。なお、各データセットの項目は以下の様です。

　C列以降のデータ数：[月間アクセス数]シートのC列以降に文字が入力されているセルの総数
　Nの数：[月間アクセス数]シートのC列以降に"N"が入力されているセルの総数
　有効データ数：グラフ化の対象となる、セルの列数

次に、C4セル、D4セル、E4セルに以下の式を埋めます。

　C4：=COUNTA(月間アクセス数!C4:月間アクセス数!XFD4)
　D4：=COUNTIF(月間アクセス数!C4:XFD4, "N")
　E4：=C4-D4

その後、C4からE4セルまでを選択し、13列まで連続データのコピーをします。すると、次の様になります。

これでデータの準備は終了です。

２．動的グラフの作成

さて、いよいよ動的グラフの作成に移ります。
[グラフ]シートを追加し、[挿入]タブ＞「折れ線」からマーカー付き折れ線を挿入します。ここで、普通の折れ線を選んでしまうと、データが1カ月分しかない時にグラフ上のデータが見えなくなってしまうため、注意。
表示された空白のグラフ上で右クリック＞「データの選択」を押下し、凡例項目（系列）の「追加」から以下を入力し、「OK」を押します。

再度、凡例項目（系列）の「追加」を押し、以下を入力して「OK」を押します。

続いて、横（項目）軸ラベルの「編集」を押下し、以下を入力して「OK」を押します。「データソースの編集」画面に戻るので、再度「OK」を押します。するとグラフが描写されますが、タイトルがないので、グラフ選択をした状態で、[レイアウト]タブ＞「グラフタイトル」＞「グラフの上」を選択します。表示されたグラフのタイトルを選択し、数式バーに以下の数式を埋め込みます。

=月間アクセス数!\$B\$4

すると、以下のグラフが描写されます。

次に、[数式]タブ＞「名前の管理」＞「新規作成」をクリックし、以下を入力して、「OK」を押します。ポイントは、名前にNo001を付けるなどし、後に連番で名前管理できるようにすることです。なお、OFFSET関数の使い方に関しましては、こちらのサイトが大変参考になりますので、ご参照ください。

　名前：月間アクセス数_No001
　範囲：ブック
　参照範囲：=OFFSET(月間アクセス数!\$C\$4, 0, 計算用シート1!\$D\$4, 1, 計算用シート1!\$E\$4)

同様に以下の2つの名前も定義します。

　名前：月間購入数_No001
　範囲：ブック
　参照範囲：=OFFSET(月間購入数!\$C\$4, 0, 計算用シート1!\$D\$4, 1, 計算用シート1!\$E\$4)

　名前：年月_No001
　範囲：ブック
　参照範囲：=OFFSET(月間アクセス数!\$C\$3, 0, 計算用シート1!\$D\$4, 1, 計算用シート1!\$E\$4)

続いてグラフに戻り、「月間アクセス数」の折れ線をクリックします。すると数式バーに以下の文字が出力されます。

=SERIES(月間アクセス数!\$A\$1,月間アクセス数!\$C\$3:\$E\$3,月間アクセス数!\$C\$4:\$E\$4,1)

SERIESの各引数の意味は次の通りです。
　第一引数：系列名
　第二引数：横軸ラベル
　第三引数：系列値（縦軸の値）
　第四引数：棒グラフの順序

これを先程定義した名前を用いて、以下の様に書き換えます。（このエクセルは「ログ分析.xlsm」と名前を付けて保存しています）

=SERIES(月間アクセス数!\$A\$1,ログ分析.xlsm!年月_No001,ログ分析.xlsm!月間アクセス数_No001,1)

同様に、「月間購入数」の折れ線に関しても以下の数式に書き換えます。
※第二引数は、「月間アクセス数」を変更した時点で「月間購入数」にもその変更が適応されます。

=SERIES(月間購入数!\$A\$1,ログ分析.xlsm!年月_No001,ログ分析.xlsm!月間購入数_No001,2)

これで、動的グラフが完成しました。試しに[月間アクセス数]と[月間購入数]に2017年7月のデータを作ってみてください。するとグラフが動的に変化します。

３．複数の動的グラフの作成

さて、一つのグラフを作るために名前を3つ定義しました。これを10個作るとなると30個の名前定義が必要です。10個程度ならまだいいですが100個とかになると涙目ですそこで、名前定義をより簡便にするVBAを作成します。
私はこちらのサイトを参考にしました。

まず、[計算用シート2]を作成し、[開発]タブ＞「挿入」＞ActiveX コントロール＞「コマンドボタン」を選択し、シートに貼り付けます。ボタンを2つ用意したいので、貼り付けたボタンをコピペして2つにします。
続いて、ボタン上で右クリック＞「プロパティ」を押下し、Captionで以下の表示名にします。（オブジェクト名：Captionの組み合わせです）
　CommandButton1：名前定義出力
　CommandButton2：名前定義登録

以上が完了すると、次のようなボタンが作成されます。

続いて、[開発]タブ＞「デザインモード」が選択されている状態で「名前定義出力」ボタンをダブルクリックし、以下のコードを記入します。

ログ分析.xlsm

Option Explicit

Private Sub CommandButton1_Click()
    Call NamesListsOut
    MsgBox ("名前定義出力終了")
End Sub

Private Sub CommandButton2_Click()
    Call NamesListsIn
    MsgBox ("名前定義登録完了")
End Sub

Sub NamesListsIn()
    '名前定義登録
    Dim n As Name
    Dim i As Integer

    With ActiveWorkbook
        '一旦名前定義を削除
        For Each n In .Names
          n.Delete
        Next n
       '登録
        On Error Resume Next
            For i = 1 To Range("A65536").End(xlUp).Row
               .Names.Add Cells(i, 1).Value, Cells(i, 2).Value
            Next i
        On Error GoTo 0
    End With
End Sub

Sub NamesListsOut()
    '名前定義書き出し
    Dim i As Integer
    With ActiveWorkbook
        For i = 1 To .Names.Count
          Cells(i, 1).Value = .Names(i).Name
          Cells(i, 2).Value = "'" & .Names(i).RefersToLocal
        Next i
    End With
End Sub

その後、エクセルシートに戻り、「デザインシート」の選択状態を解除し、「名前定義出力」をクリックします。そうすると名前定義したものが出力されます。

今回はグラフを10個作成するため、A2,B2セルのデータをA11,B11セルへ、A3,B3セルのデータをA21,B21セルへ移行し、連続データのコピーを使って次の様な表を作成します。

C1セルにB1セルに記載してある最後の数字（ここでは4）を入力し、「月間アクセス数」分連続データのコピーします。次に、適当な空いているセル（今回はD13, D16, D19を選択した）に以下を入力します。

　D13：'="=OFFSET(月間アクセス数!\$C\$" & C1 & ", 0, 計算用シート1!\$D\$" & C1& ", 1, 計算用シート1!\$E\$" & C1 & ")"
　D16：'="=OFFSET(月間購入数!\$C\$" & C1 & ", 0, 計算用シート1!\$D\$" & C1& ", 1, 計算用シート1!\$E\$" & C1 & ")"
　D19：'="=OFFSET(月間アクセス数!\$C\$3, 0, 計算用シート1!\$D\$" & C1& ", 1, 計算用シート1!\$E\$" & C1 & ")"

D13セル、D16セル、D19セルのシングルクォーテーションの後からの文字をそれぞれB1セル、B11セル、B21セルにコピーをし、その後、連続データのコピーを使って次のような表を作成します。

「名前定義登録」ボタンをクリックすると、以前までの名前の定義が削除され、この表に従って、新規に名前の定義が行われます。（[数式]タブ＞「名前の管理」から名前が増えていることが確認できる）

これでようやく名前の定義まで終えました。あとはグラフに描いてみるだけです。
[グラフ]シートのAのグラフをコピーし、次のように数式を書き換えます。

　タイトル：=月間アクセス数!\$B\$5
　月間アクセス数の折れ線：=SERIES(月間アクセス数!\$A\$1,ログ分析.xlsm!年月_No002,ログ分析.xlsm!月間アクセス数_No002,1)
　月間購入数の折れ線：=SERIES(月間購入数!\$A\$1,ログ分析.xlsm!年月_No002,ログ分析.xlsm!月間購入数_No002,2)

すると、Bのグラフが作成されます。

もう名前は定義されているので、グラフのコピー以降をC～Jのグラフに対して行えば、全てのグラフを出力できます。例えば、Jのグラフを出力すると、2017年6月のデータのみプロットされているのが確認できます。

以上で自動的にデータの範囲が更新される複数のグラフを作成することができました。

最後に

一度作ってしまえば、自動化の恩恵を結構受けられるんじゃないかなーと思います。作り込む時間もなかったので、私はマクロに関しては半ばエクセルの力を借りながら力技でやってしまいましたが、本プログラムを通じてかなりエクセル操作には慣れました。
何かしら皆様のお役に立てる部分があれば幸いです。

では、失礼します。