グラフの参照範囲を変更する

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

グラフの操作に関しては、いずれまとめようと思っていましたが、とりあえずこれだけ。既存グラフの参照範囲(元データ)を、マクロで変更するにはどうするかって話です。なお、ここではExcel 2010の画面で解説していますが、Excel 2003でも動作確認をしています。

なお、マクロを使わないで、ワークシート関数だけで自動的に変更するやり方は、下記ページをご覧ください。

グラフの参照範囲を自動的に変更する

下のような表からグラフを作ります。

たとえば、こんなグラフを作りました。

この表に「10月」のデータが追加されました。

当然、グラフで参照している元データも変更しなければなりません。これをマクロでやってみましょう。

方法1：SetSourceDataメソッドで指定する

グラフの参照元を指定するには、2つの方法があります。1つは、ChartオブジェクトのSetSourceDataメソッドで元データの範囲を指定するやり方です。

グラフを作成する操作をマクロ記録すると、たとえば次のようなコードが記録されます。

【Excel 2003まで】
Sub Macro()
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D10")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub
【Excel 2007以降】
Sub Macro()
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$D$10")
End Sub

Excel 2007からのグラフは、Excel 2003までのグラフから大きく変わりました。しかし、いずれにしても上のように、ChartオブジェクトのSetSourceDataメソッドで元データのセル範囲を指定することは同じです。

それまでは、グラフの参照範囲が「Range("A1:D10")」でした。このデータ範囲を「Range("A1:D11")」にしてやればいいです。「Range("A1:D11")」というのは、セルA1を含む連続したデータ領域ですから、CurrentRegionプロパティで取得できます。

Sub Sample()
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=Range("A1").CurrentRegion
End Sub

ここでのポイントは、いかに新しい元データのセル範囲を取得するかです。今回は、連続したひとかたまりのデータ(ここでは、セル範囲A1:D11)でしたからCurrentRegionプロパティで一発でしたが、現実はそんなに簡単ではありませんよね。

たとえば、次のようなグラフだったらどうでしょう。

こんなときは、SetSourceDataの引数に、項目軸ラベルのセル範囲と、グラフデータのセル範囲を指定します。

新しいデータ範囲は、

  • 項目軸ラベル：A1:A10 → A1:A11
  • グラフデータ：C1:D10 → C1:D11

です。

まず、項目軸ラベルの方から考えましょう。新しいセル範囲A1:A11のうち、先頭の「A1」は固定です。最後の「A11」は、A列の最終セルです。これは、Endメソッドで取得できます。

ということで、新しい項目軸ラベルのセル範囲は

Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))

となります。これは「Range(始点セル, 終点セル)」という指定方法です。

同じように、グラフデータのセル範囲も次のように取得できます。

新しいグラフデータのセル範囲は

Range(Range("C1"), Cells(Rows.Count, 4).End(xlUp))

です。

さて、ここでは連続していない2つのセル範囲を指定しなければなりません。

こんなとき、それぞれのセル範囲のアドレスと、カンマを文字列結合する・・・なんて下品なことはしないでくださいね。こうした非連続のセル範囲を扱うときは、Unionメソッドを使います。

たとえば、下図のような非連続範囲を、オブジェクト型変数Targetに格納するとしたら

Unionメソッドを使って、次のようにします。

Set Target = Union(範囲A, 範囲B)

セルのアドレスで書くと

Set Target = Union(Range("B3:B7"), Range("B10:D12"))
です。

つまり今回は

Set Target = Union(項目軸ラベルのセル範囲, グラフデータのセル範囲)

となります。このオブジェクト型変数Targetを、SetSourceDataメソッドの引数に指定してやればいいです。

Sub Sample()
    Dim Target As Range
    Set Target = Union(Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp)), _
                       Range(Range("C1"), Cells(Rows.Count, 4).End(xlUp)))
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=Target
End Sub

もちろん、オブジェクト型変数など使わずに、「Source:=」の後ろで直接UnionしてもOKです。

方法2：Formulaプロパティを指定する

SetSourceDataメソッドで新しいデータ範囲を指定するのは、いわばグラフを作り直すようなものです。そうではなく、グラフの系列が参照している範囲を変更するには、系列のFormulaプロパティで指定されているアドレスを変更します。

まず、各系列のFormulaプロパティに、どんな値が設定されているかを調べてみましょう。これは、マクロを使うまでもありません。系列を選択すると、その系列のFormulaプロパティに設定されている数式が、数式バーに表示されます。

念のために、Formulaプロパティで確認してみましょう。

Sub Sample()
    Dim msg As String, i As Long
    With ActiveSheet.ChartObjects(1).Chart
        For i = 1 To .SeriesCollection.Count
            msg = msg & .SeriesCollection(i).Formula & vbCrLf
        Next i
    End With
    MsgBox msg
End Sub

FormulaプロパティにはSERIES関数が指定されています。SERIES関数の書式は次の通りです。

引数 必須/省略 指定内容
name 省略可 凡例に表示される名前
category_labels 省略可 項目軸に表示されるラベル
values 必須 プロットされる値
order 必須 系列のプロット順

現在の系列Aには、次の数式が設定されています。

見にくいので、シート名(Sheet1)と、絶対参照を表す「$」記号を消してみます。それぞれ、次のセルやセル範囲を参照していることが分かります。

もし、セル範囲A11:D11に「10月」のデータが追記されたら、系列AのSERIES関数を次のように変更すればいいです。

もちろん、ほかの系列も、考え方は同じです。

新しく設定するセルA11とセルB11は、それぞれ、A列とB列の最終セルですから、Endプロパティで取得できます。たとえばB列だったら

ですね。もし、新しく追加されるデータが必ず1ヶ月分(1行分)であると決まっているのなら話は簡単です。置換するアドレスは、次のように考えられます。

系列Aだけを更新するなら、次のようになります。

Sub Sample()
    Dim 系列Aの数式 As String
    Dim A列の最終セル As String
    Dim A列の最終セルの1つ上 As String
    Dim B列の最終セル As String
    Dim B列の最終セルの1つ上 As String
    
    With ActiveSheet.ChartObjects(1).Chart
        系列Aの数式 = .SeriesCollection(1).Formula
        
        A列の最終セル = Cells(Rows.Count, 1).End(xlUp).Address
        A列の最終セルの1つ上 = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Address
        
        B列の最終セル = Cells(Rows.Count, 2).End(xlUp).Address
        B列の最終セルの1つ上 = Cells(Rows.Count, 2).End(xlUp).Offset(-1, 0).Address
        
        系列Aの数式 = Replace(系列Aの数式, A列の最終セルの1つ上, A列の最終セル)
        系列Aの数式 = Replace(系列Aの数式, B列の最終セルの1つ上, B列の最終セル)
        
        .SeriesCollection(1).Formula = 系列Aの数式
    End With
End Sub

分かりやすくするために、日本語の変数名を使ってみました。

あとは、同じ考え方で、ほかの系列も操作してあげればいいです。自信のない方は、上の操作を系列の数だけ記述してください。VBAの読解力に自信のある方は、次のようにループで回しましょう。

Sub Sample()
    Dim i As Long, F As String, LastCell As Range
    Set LastCell = Cells(Rows.Count, 1).End(xlUp)
    With ActiveSheet.ChartObjects(1).Chart
        For i = 1 To .SeriesCollection.Count
            F = .SeriesCollection(i).Formula
            F = Replace(F, LastCell.Offset(-1, 0).Address, LastCell.Address)
            F = Replace(F, LastCell.Offset(-1, i).Address, LastCell.Offset(0, i).Address)
            .SeriesCollection(i).Formula = F
        Next i
    End With
End Sub

最後に、何ヶ月分(何行分)のデータが追記されたかわからない場合を考えてみましょう。

さっきは、Replace関数で置換する「元のアドレス」をOffsetプロパティで取得できました。それは、追加されたのが1行だと分かっていたからです。もし、何行追加されたのが分からないのでしたら、Offsetプロパティで取得することはできません。

つまり、新しい最終セルのアドレスを置換する方法では対応できない、ということです。こんなときは、しかたないですから、SERIES関数自体を再定義してやります。

SERIES関数の書式を、おさらいしておきましょう。

引数 必須/省略 指定内容
name 省略可 凡例に表示される名前
category_labels 省略可 項目軸に表示されるラベル
values 必須 プロットされる値
order 必須 系列のプロット順

現在の系列Aは、次のように設定されているんでしたね。

これを、次のように再定義してやります。

これには、本コンテンツの上の方でやったRange(始点セル, 終点セル)を使います。たとえば項目軸ラベルだったら

Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp))

したがって、系列Aだけを変更するのなら、次のように考えられます。

Sub Sample()
    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
        .Formula = "=SERIES(" & Range("B1").Address & "," & _
                   Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp)).Address & "," & _
                   Range(Range("B2"), Cells(Rows.Count, 2).End(xlUp)).Address & "," & _
                   1 & ")"
    End With
End Sub

考えられますが、これでは失敗します。なぜなら、SERIES関数の引数では、シート名も指定しなければならないからです。ただ普通にAddressプロパティで取得したアドレスだけを指定するとエラーになります。

エラーメッセージが分かりにくいですね。

それはさておき、こんなときは、Addressプロパティの引数ExternalにTrueを指定します。引数ExternalにTrueを指定すると、Addressプロパティは、[ブック名]シート名！アドレス という形式を返します。

Sub Sample()
    Dim LastCell As Range
    Set LastCell = Cells(Rows.Count, 1).End(xlUp)
    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
        .Formula = "=SERIES(" & Range("B1").Address(External:=True) & "," & _
                   Range(Range("A2"), LastCell.Offset(0, 0)).Address(External:=True) & "," & _
                   Range(Range("B2"), LastCell.Offset(0, 1)).Address(External:=True) & "," & _
                   1 & ")"
    End With
End Sub

全部の系列を更新するのなら、次のようになりますね。

Sub Sample()
    Dim LastCell As Range, i As Long
    Set LastCell = Cells(Rows.Count, 1).End(xlUp)
    With ActiveSheet.ChartObjects(1).Chart
        For i = 1 To .SeriesCollection.Count
            .SeriesCollection(i).Formula = _
                "=SERIES(" & Cells(1, i + 1).Address(External:=True) & "," & _
                 Range(Cells(2, 1), LastCell.Offset(0, 0)).Address(External:=True) & "," & _
                 Range(Cells(2, i + 1), LastCell.Offset(0, i)).Address(External:=True) & "," & _
                 i & ")"
        Next i
    End With
End Sub

ふぅ・・・長かったｗ