【Excel】エクセルのグラフを自動更新する方法

エクセルで売上や件数を毎月追記しているのに、グラフの範囲が増えず、手作業でデータ範囲を指定し直している人は少なくありません。

グラフを自動更新できる状態にしておけば、新しい行や列を追加した際にも系列、凡例、軸ラベルが連動し、集計資料を作る時間を抑えられます。

最も扱いやすい方法は、元データをテーブルに変換してからグラフを作成する方法です。

データの入力方法や更新頻度に合う仕組みを選ぶと、見やすいグラフを長く維持しやすくなります。

それでは詳しく確認していきましょう。

エクセルのグラフを自動更新する方法1【テーブル機能の活用】

それではまず、データをテーブルに変換してグラフを自動更新する方法について解説していきます。

上のように1行目を見出しにしたデータ表を用意し、入力する範囲をテーブルとして管理します。

元データをテーブルに変換する操作

テーブルは、データの最終行に新しいレコードを入力したとき、表の範囲を自動的に広げるエクセルの機能です。

通常のセル範囲から作ったグラフは、元の指定範囲の外側に入力した値を自動では読み込みません。

一方でテーブルをグラフの参照元にすると、追加した行も系列の一部として扱われます。

まずA1からC4までのように、見出しを含めたデータ全体を選択します。

続いてリボンの挿入タブを開き、テーブルを選択するか、キーボードでCtrlキーとTキーを同時に押します。

テーブルの作成画面では範囲を確認し、先頭行をテーブルの見出しとして使用するにチェックが入っていることを確かめます。

OKを選ぶと、見出し行にフィルターボタンが付き、表に交互の色が表示されます。

表示色は変更できますが、重要なのは見た目ではなく、範囲がテーブルとして認識されたことです。

データの途中に空白行を作ると、追加データが別の表として扱われる場合があります。

毎月のデータは既存テーブルの直下へ、連続して入力することが基本です。

テーブルからグラフを挿入する手順

テーブル内の任意のセルを選択してからグラフを挿入すると、テーブル全体がデータ範囲として自動認識されます。

月別の売上推移を確認したい場合は、挿入タブのグラフから折れ線グラフを選びましょう。

売上額の大小を月ごとに比べるなら、集合縦棒グラフも分かりやすい形式です。

グラフを選択すると、横軸には月、凡例には商品A売上と商品B売上が設定されます。

もし月が凡例に表示されるなど意図と異なる場合は、グラフデザインタブの行と列の切り替えを使用します。

グラフはテーブルの列見出しを系列名として利用するため、見出しを具体的で重複しない名前にしておくことが大切です。

数値の単位が円なのか千円なのかも、見出しまたは縦軸の表示形式で明示しておくと誤読を防げます。

テーブルのフィルターで一部の月を絞り込むと、既定ではグラフにも表示状態が反映されます。

非表示の行もグラフに残したい場合は、グラフのデータの選択画面から非表示および空白のセルを設定します。

追加データが反映される仕組み

テーブルの最終行のすぐ下に7月と売上額を入力すると、テーブルの枠線と書式が下へ伸びます。

同時に、テーブルを参照するグラフの横軸と系列も7月分まで拡張されます。

このときグラフを作り直したり、データ範囲の選択画面を開いたりする必要はありません。

右隣に新しい商品列を追加した場合も、グラフに系列として加わることがあります。

ただし、既存グラフの種類や設定によっては、新列を自動で系列化しないケースもあります。

列追加後はグラフを一度確認し、必要に応じてデータの選択から系列を追加してください。

グラフのデータ範囲を自動拡張する設定

続いては、すでに作成済みのグラフをテーブル参照へ変更し、自動拡張させる設定を確認していきます。

固定範囲を指定したグラフでも、元のセル範囲をテーブル化すれば更新作業を減らせます。

既存グラフの参照先を確認する画面

グラフをクリックし、グラフデザインタブからデータの選択を開くと、現在の参照範囲を確認できます。

グラフデータの範囲には、通常のセル範囲であればSheet1!$A$1:$B$31のような表示が出ます。

この指定は31行目までで止まるため、32行目以降の入力はグラフに入りません。

先に元データをテーブルへ変換すると、参照範囲はテーブル名と列名を使った構造化参照へ置き換わります。

構造化参照では、行の増減に合わせて対象範囲そのものが変わります。

グラフのデータ範囲を直接手入力で直すより、元表をテーブル化するほうが入力ミスを避けやすいでしょう。

グラフが別シートにある場合も、同じブック内のテーブルなら問題なく参照できます。

新しい行と列を追加する入力ルール

日別データの場合は、テーブルの最終行の次に日付と件数を入力します。

日付列に文字列を混ぜず、日付形式としてそろえると、横軸の並び順が正しく保たれます。

数値が文字列として入力されていると、グラフでは値として扱われず、系列が不自然になることがあります。

セルの左上に緑色の三角が見える場合は、数値が文字列として保存されていないか確認しましょう。

商品名や担当者名など新しい列を増やすときは、テーブルの右端の次列に見出しを入力します。

見出しが空白のままではグラフ系列名が分かりにくくなるため、入力と同時に名称も決めます。

表示されないデータを見直す方法

行を追加してもグラフに反映されないときは、まず新しい行がテーブル内に含まれているかを確認します。

テーブルのデザインタブでテーブルのサイズ変更を選び、参照範囲の末尾が新しい行まで届いているかを見ます。

次に、フィルターで新しい項目が非表示になっていないか、値が空白ではないかも確認しましょう。

グラフ側では、グラフフィルターのボタンから系列やカテゴリが外れていないかを調べます。

自動更新が働かない原因の多くは、グラフではなく元データの範囲や形式にあります。

参照元を整えてからグラフを更新すると、不要な作り直しを避けられます。

名前の定義とOFFSET関数による可変範囲

続いては、テーブルを使いにくい帳票形式でも利用できる、名前の定義とOFFSET関数による可変範囲を確認していきます。

この方法は、データの開始位置を固定しながら、入力件数に応じてグラフ範囲だけを伸縮させたい場合に役立ちます。

OFFSET関数で縦方向の可変範囲を作る数式

OFFSET関数は、基準セルから指定した行数と列数だけずらした位置を起点にし、指定した高さと幅の範囲を返す関数です。

たとえばA列に月、B列に受注数があり、1行目がヘッダーの場合、月の可変範囲には次の数式を使えます。

基準となるA2は最初の月が入力されるセルです。

COUNTA関数でA列の空白でないセルを数え、ヘッダー1件を引くことで、データ行だけの高さを求めます。

A列に空白がないことが、この数式を正しく動かす前提になります。

同様に受注数の範囲を作る場合は、基準セルをB2へ変更します。

高さの判定をA列にそろえることで、月と受注数の行数が常に一致します。

名前の管理で数式を登録する手順

数式タブから名前の管理を開き、新規作成を選択します。

名前には月範囲、参照範囲には先ほどのA列用のOFFSET関数を入力します。

次に受注範囲という名前を作り、B列用の数式を登録しましょう。

グラフのデータの選択で系列を編集し、系列値にブック名を含む受注範囲、横軸ラベルに月範囲を指定します。

入力行が増えるとCOUNTA関数の結果も増えるため、グラフが参照する範囲も自動で拡張されます。

COUNT関数とINDEX関数を使う場合

数値だけが入る列を対象にするなら、COUNTA関数の代わりにCOUNT関数を使う選択肢もあります。

ただしCOUNT関数は文字列を数えないため、月の列のように文字を含むデータには適しません。

また、OFFSET関数は再計算が多い大規模ブックでは処理負荷につながることがあります。

その場合はINDEX関数を利用して、開始セルから最終データセルまでの範囲を作る方法も検討できます。

小規模な管理表ならOFFSET関数でも十分ですが、継続利用する大きなファイルではテーブル機能のほうが保守しやすい場合があります。

ピボットグラフとデータ更新の連携

続いては、月別、担当者別、商品別などの集計を同時に行いたい場合に便利なピボットグラフについて確認していきます。

ピボットグラフは、ピボットテーブルの集計結果を視覚化するグラフです。

ピボットテーブルを作成する準備

元データは1行目にヘッダーを置き、途中に空白行や小計行を入れない一覧形式にします。

データ内のセルを選択し、挿入タブからピボットテーブルを選びます。

集計先を新規ワークシートにすると、元データと集計表を分けて管理しやすくなります。

フィールド一覧では、受注日を行、担当者を列、売上を値に配置すると、担当者別の売上集計を作れます。

ピボットテーブルの元データもテーブルにしておくと、追加行を集計対象へ含めやすくなります。

通常範囲のまま作る場合は、元データの範囲不足に注意が必要です。

ピボットグラフを追加する操作

ピボットテーブル内を選択し、ピボットテーブル分析タブからピボットグラフを選びます。

担当者別の比較には集合縦棒、月ごとの増減には折れ線グラフが向いています。

ピボットグラフにはフィールドボタンが表示され、担当者や期間を画面上で絞り込めます。

元表を直接グラフ化するより、条件を変えた分析を短時間で行える点がピボットグラフの強みです。

不要なフィールドボタンは、右クリックメニューからグラフ上のすべてのフィールドボタンを非表示にできます。

ただし、分析中はボタンを残しておくと条件の変更が分かりやすくなります。

更新ボタンとファイルを開くときの更新設定

元データへ新しい受注行を追加しても、ピボットテーブルはただちに集計を変えないことがあります。

ピボットテーブル分析タブの更新を選ぶか、ピボットテーブル内を右クリックして更新を実行します。

複数のピボットテーブルを使う場合は、すべて更新を選択するとまとめて反映できます。

外部データ接続を利用している場合は、接続先の更新可否や認証情報も確認しましょう。

更新前後で集計値が大きく変わったときは、日付フィルターや重複データを見直す習慣が大切です。

VBAによるグラフ更新処理の自動化

続いては、複数のグラフやピボットテーブルを扱うブックで、更新作業をVBAに任せる方法について確認していきます。

VBAは便利ですが、共有先のセキュリティ設定やマクロ有効形式にも配慮して導入します。

更新マクロの基本構成

数式、グラフ、ピボットテーブルをまとめて更新したい場合は、標準モジュールに更新用のマクロを作成します。

Sub グラフを更新する()
    ThisWorkbook.RefreshAll
    Application.CalculateFull
    MsgBox "データとグラフを更新しました。"
End Sub

ThisWorkbook.RefreshAllは、ブック内のデータ接続やピボットテーブルなどを更新する命令です。

Application.CalculateFullは数式を再計算し、計算結果を使うグラフの表示を新しい状態へ整えます。

テーブルを元にした通常グラフは行追加時に自動反映されるため、VBAは特にピボットや外部接続の更新に有効です。

ファイルはマクロ有効ブックの形式であるxlsmとして保存します。

ボタンから更新を実行する設定

利用者にVBAエディターを開かせたくない場合は、シート上にボタンを置いてマクロを割り当てます。

開発タブの挿入からフォームコントロールのボタンを選び、更新用のマクロを指定します。

ボタンの表示文字は、グラフ更新や集計更新のように、実行内容が伝わる言葉にします。

更新に時間がかかるブックでは、完了メッセージを表示すると利用者が状況を判断しやすくなります。

マクロを実行できない環境では、データタブのすべて更新という通常操作も案内しておくと安心です。

自動実行時の注意点

ブックを開いた直後に自動更新する処理は便利ですが、外部接続がある場合は開く時間が長くなる可能性があります。

共有フォルダ上のデータを更新する場合、接続できないとエラーや古い値の表示につながることもあります。

実行前に元ファイルをバックアップし、更新結果が想定どおりかをテスト環境で確かめましょう。

信頼できない送信元のマクロは有効化せず、社内ルールに沿った保管場所と配布方法を選びます。

まとめ エクセルのグラフを自動更新する方法

エクセルのグラフを自動更新するには、まず元データをテーブルに変換し、そのテーブルからグラフを作成する方法が基本です。

テーブルでは最終行へデータを追加するだけで、グラフのデータ範囲が自動的に広がります。

帳票の都合でテーブルを使えない場合は、名前の定義とOFFSET関数、またはINDEX関数で可変範囲を設定できます。

集計軸を切り替えながら分析するなら、テーブルを元にしたピボットテーブルとピボットグラフが便利です。

ピボットグラフでは更新操作が必要になることがあるため、更新ボタンやファイルを開くときの更新設定を活用しましょう。

複数の集計や外部データ接続を一括で扱う場合には、VBAによる更新処理の自動化も選択肢になります。

データのヘッダー、入力形式、空白行の有無を整え、手作業での範囲変更から解放されましょう。