集計表からグラフを自動作成・更新する（月別売上の棒＋利益率の折れ線）

どんな場面で使うか

月次の会議資料で、売上の推移と利益率を1つのグラフにまとめて見せることはよくあります。毎月データが1行ずつ増えるたびにグラフの範囲を手で直していると、範囲の指定漏れで最新月がグラフに出ていない、といったミスが起きがちです。

このマクロは、集計表の最終行を毎回調べて、グラフがなければ新しく作り、あれば同じグラフのデータ範囲だけを付け替えます。何度実行してもグラフが増え続けることはありません。

前提とする集計表

「月別集計」シートに、次のような表があるものとします（説明用の例です）。1行目が見出し、2行目から月ごとのデータです。利益率は 0.25 のような小数で入れ、表示形式でパーセントにしておきます。

• A列：年月（「2026/04」などの文字列、または日付）
• B列：売上
• C列：利益率

コード

グラフには名前を付けておき、その名前で既存のグラフを探します。見つからなければ ChartObjects.Add で作成します。データ範囲は SetSourceData で売上と利益率の2列を指定し、横軸の項目（年月）は各系列の XValues に明示的に設定しています。

Sub UpdateSalesChart()
    Const CHART_NAME As String = "月別売上グラフ"
    Dim ws As Worksheet, co As ChartObject, ch As Chart
    Dim lastRow As Long, i As Long

    Set ws = ThisWorkbook.Worksheets("月別集計")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then MsgBox "集計データがありません": Exit Sub

    ' 既存のグラフを名前で探す（なければ Nothing のまま）
    On Error Resume Next
    Set co = ws.ChartObjects(CHART_NAME)
    On Error GoTo 0

    If co Is Nothing Then
        With ws.Range("E2")
            Set co = ws.ChartObjects.Add(Left:=.Left, Top:=.Top, Width:=520, Height:=300)
        End With
        co.Name = CHART_NAME
    End If

    Set ch = co.Chart
    ch.ChartType = xlColumnClustered
    ch.SetSourceData Source:=ws.Range("B1:C" & lastRow), PlotBy:=xlColumns

    ' 横軸の項目を年月の列にそろえる
    For i = 1 To ch.SeriesCollection.Count
        ch.SeriesCollection(i).XValues = ws.Range("A2:A" & lastRow)
    Next i

    ' 2つ目の系列（利益率）を第2軸の折れ線にする
    With ch.SeriesCollection(2)
        .ChartType = xlLineMarkers
        .AxisGroup = xlSecondary
    End With

    ch.HasTitle = True
    ch.ChartTitle.Text = "月別売上と利益率"
    ch.HasLegend = True
    ch.Legend.Position = xlLegendPositionBottom
    ch.Axes(xlValue, xlPrimary).TickLabels.NumberFormatLocal = "#,##0"
    ch.Axes(xlValue, xlSecondary).TickLabels.NumberFormatLocal = "0%"
End Sub

コードのポイント

ChartObjects.Add の位置と大きさはポイント単位で指定します。セルの Left と Top を使うと「E2 セルの位置に置く」という指定ができ、列幅を変えてもおおよその位置がずれにくくなります。

SetSourceData に年月の列まで含めると、年月が数値や日付で入っている場合に、Excel がそれを「系列」の1つとして扱ってしまうことがあります。そこで、数値の列だけを SetSourceData に渡し、横軸の項目は XValues で別に指定しています。こうしておけば、年月の入れ方に関係なく同じグラフになります。

既存のグラフを名前で探して使い回すため、2回目以降はデータ範囲と書式だけが更新されます。グラフの位置や大きさを手で調整した場合も、その調整は保たれます。

よくある失敗と注意点

On Error Resume Next でグラフを探したあと、On Error GoTo 0 に戻し忘れると、その後のエラーがすべて無視され、グラフが崩れていても気付けません。エラーを無視するのは、存在確認の1行だけにします。

• グラフ名を変えると「見つからない」と判定され、新しいグラフが追加される
• 系列の順番は SetSourceData に渡した列の順なので、列を入れ替えると折れ線になる系列も変わる
• 利益率をパーセントの数字（25 など）で入れると、第2軸の表示が 2500% のようになる
• 日付の軸は、間のない月があると自動で日付軸になり、棒の間隔が空くことがある（その場合は年月を文字列にする）

応用

部門別に同じ形式のシートがある場合は、このマクロのシート名を引数にして、シートごとに呼び出せばまとめてグラフを更新できます。作成したグラフを報告書に貼るときは、co.Chart.Export でファイル名を指定すると PNG 画像として保存することもできます。

ほかの記事