グラフのデータ範囲を可変にして自動でグラフを拡張する2つの方法

Excelでグラフを作成して、グラフの参照元の範囲を拡張すると、グラフのデータ範囲も拡張しなければなりません。

この操作を行わず、グラフの参照元にデータが追加されたら、グラフも自動的に拡張される方法を紹介します。

1つは、データ元にテーブルを使用する方法です。もう1つは、OFFSET関数を使用する方法です。

テーブルに変換するのが一番手っ取り早いですが、ブック内にテーブルがあると不都合なこともあります。

その場合は、関数を使用する方法をおすすめします。

Excel2019 バージョン2406での解説です。

データを基にグラフを作成

以下のような表があります。このデータを基に縦棒グラフを作成します。

表内のセルを選択して、［挿入］タブの［グラフ］グループにある［おすすめグラフ］をクリックします。

［グラフの挿入］ダイアログボックスが表示されますので、［集合縦棒］を選択して［OK］ボタンをクリックします。

以下のようにグラフが作成できます。

参考［挿入］タブの［グラフ］グループの［縦棒/横棒グラフの挿入］をクリックして［2-D縦棒］の［集合縦棒］を選択しても同じです。

グラフタイトルはセルを参照

グラフタイトルは、表の上に入力されているセル［A1］の文字列と同じにしたいと思います。

グラフ内の［グラフタイトル］を選択して、数式バーに［=］（半角）で入力します。

セル［A1］をクリックします。

数式バーは、以下のように［=Sheet1!$A$1］となり、グラフタイトルはセル［A1］を参照するようになります。

参考ここでは、グラフタイトルは、すでに入力したものがあったのでセルをリンクする方法を紹介していますが、グラフタイトルは直接入力しても構いません。

また、Office2016から追加された新グラフでは、タイトルのセル参照はできません。

グラフの参照元範囲にデータを入力してグラフに反映

完成したグラフの参照元範囲にデータを入力します。グラフは、そのままですね。

表の罫線も設定しなければなりません。

参考セル［A7］に文字を入力すると、塗りつぶしが自動で設定されますが、これはExcelの設定によるものです。同じ書式が3つ続くと、すぐ下のセルには書式が設定されます。

グラフのデータ範囲を変更

グラフに新しいデータを反映させます。

グラフを選択します。そうすると、グラフのデータ範囲が太い枠線で囲まれます。

青い囲み線の右下にマウスを合わせるとマウスポインタが以下のように斜めの形に変わります。

このまま、参照元範囲の最終セルの位置（ここではセル［C7］）までドラッグします。

データ範囲が拡張され、グラフにも反映されます。

参考または、［グラフのデザイン］タブの［データ］グループにある［データの選択］をクリックします。

［データソースの選択］ダイアログボックスが表示されますので、［グラフデータの範囲］を変更して［OK］ボタンをクリックします。

グラフのデータ範囲をテーブルに変更して自動拡張

通常のグラフでデータ範囲を変更するのも面倒なことはないと思われる場合はいいのですが、毎月や毎週、定期的に複数のグラフの修正を行わなければならないこともあります。

少しでも効率よくする方法です。

グラフのデータ範囲をテーブルに変更すると、データを入力するだけでグラフの修正は不要になります。

テーブルに変換

表内のセルを選択して、［挿入］タブの［テーブル］グループにある［テーブル］をクリックします。

ショートカットキーは、［Ctrl］+［T］です。

［テーブルの作成］ダイアログボックスが表示されますので、データ範囲が間違っていなければ［OK］ボタンをクリックします。

［先頭行をテーブルの見出しとして使用する］のチェックボックスはオンにしておきます。

以下のようになります。せっかくの書式設定が崩れてしまいました。

テーブルに変換すると、自動的にスタイルが設定されますが、スタイルは解除することができます。

元の書式に変更するには、［テーブルデザイン］タブの［テーブルスタイル］グループにある［クイックスタイル］をクリックします。

スタイルの一覧が表示されますので、左上の［なし］をクリックします。

または、スタイル一覧の一番下にある［クリア］をクリックします。

以下のように元の書式になります。

フィルターボタンも不要であれば、［テーブルデザイン］タブの［テーブルスタイルのオプション］グループにある［フィルターボタン］のチェックをオフにします。

フィルターボタンもオフになり、元の表と同じようになります。

ただ、表内のセルを選択すると、［テーブルデザイン］タブが現れます。

テーブルにデータを追加してみます。

テーブルが自動拡張され、グラフにも反映されます。罫線も設定されています。

データを入力するだけでOKです。

参考テーブルが自動拡張されない場合は、設定を確認してみてください。以下の記事では、テーブルを解除する方法も記しています。

テーブルはとても便利な機能です。

入力規則のリストが増減するような場合もテーブルにすると便利です。

ピボットテーブルを作成する場合も、元データにテーブルを使用すると［更新］ボタンのみでデータソースを更新できます。

印刷範囲の設定もテーブルにすると、再設定する必要がありません。

ただ、テーブルにすると、いくつかの機能が制限され不都合なこともあります。

OFFSET関数を使って自動拡張

テーブルを使用しないで、関数を使ってデータをグラフに反映させることもできます。

テーブルの場合は、縦横に拡張されてもグラフに反映されますが、以下で紹介するOFFSET関数は、既存の系列に新しいデータが追加された場合の行方向の自動拡張になります。

セル範囲に名前を付ける

表全体を範囲選択して、［数式］タブの［定義された名前］グループにある［選択範囲から作成］をクリックします。

ショートカットキーは、［Ctrl］+［Shift］+［F3］です。

［選択範囲から名前を作成］ダイアログボックスが表示されます。

［上端行］を選択して［OK］ボタンをクリックします。

セル［A3］からセル［C6］までを範囲選択して［選択範囲から名前を作成］をクリックすると、まとめて3つの名前を定義することができて効率的です。

セル範囲に名前を付ける方法と管理については、以下の記事で解説しています。

名前の管理を表示して参照範囲を修正

［数式］タブの［定義された名前］グループにある［名前の管理］をクリックします。

ショートカットキーは、［Ctrl］+［F3］です。

［名前の管理］ダイアログボックスが表示されます。以下のように表の項目名が名前になっています。

ここから、名前の参照範囲に関数を使って修正していきます。

［A店］を選択して、［編集］ボタンをクリックします。

［名前の編集］ダイアログボックスの［参照範囲］を修正します。

以下のように入力します。シート名は入力しなくても構いません。

同じように［B店］を選択して、参照範囲に以下のように入力します。

［月］の参照範囲も編集します。

［名前の管理］ダイアログボックスは、以下のようになります。

参考以下の記事でも名前の参照範囲でOFFSET関数を使用しています。

OFFSET関数

OFFSET関数は、指定した参照から指定した行数、列数の範囲への参照を返す関数です。

OFFSET(参照, 行数, 列数, [高さ], [幅]) ［高さ］と［幅］は省略可能です。

OFFSET 関数 - Microsoft サポート（Microsoft）

［A店］の参照範囲は、セル［B4］を基準にして、行数［0］、列数［0］、高さをCOUNT関数で指定しています。

参考OFFSET関数はとても便利です。縦横に拡張されるデータの場合にも使用できます。

縦横の範囲が可変する名前定義

COUNT関数とCOUNTA関数

COUNT関数は、範囲内の数値が含まれるセルの個数を返します。

COUNT 関数 - Microsoft サポート（Microsoft）

COUNTA関数は、範囲内の空白でないセルの個数を返します。空白スペースは文字としてカウントされます。

COUNTA 関数 - Microsoft サポート（Microsoft）

［A店］と［B店］のセルはすべて数値なので［COUNT関数］を使用していますが、［COUNTA関数］でも求めれれます。

［月］の範囲は数値ではないので、［COUNTA関数］を使用しています。

また、［COUNTA($A:$A)-2］で［-2］としているのは、セル［A1］とセル［A2］を省くためです。

セル［A1］に文字列を入力していない場合は、［COUNTA($A:$A)-1］としてください。

［月］のセルが［2010/04/01］のような数値で入力しているような場合は、［COUNT］関数で求められます。

参考UNIQUE関数と組み合わせて重複のないリストを作成することもできます。

データソースに名前を適用

グラフを選択して、［グラフのデザイン］タブの［データ］グループから［データの選択］をクリックします。

［データソースの選択］ダイアログボックスが表示されます。

凡例項目の［A店］を選択して、［編集］をクリックします。

［系列の編集］ダイアログボックスが表示されます。

［系列値］を名前［A店］に変更します。

［系列値］の［=Sheet1!］を残して削除します。そして、キーボードから［F3］キーを押します。

［名前の貼り付け］ウィンドウが表示されますので、［A店］を選択して［OK］ボタンをクリックします。

［系列の編集］の［系列値］は以下のように［=Sheet1!A店］となります。［OK］ボタンをクリックします。

ここでは、［名前の貼り付け］を使用しましたが、直接入力してもかまいません。

閉じて、再度 開くと、以下のようにブック名が表示されますが、問題ありません。

同じように凡例項目の［B店］を選択して、系列値に名前［B店］を指定します。

［月］は、［横（項目）軸ラベル］の［編集］をクリックします。

そして、［軸ラベル］の［軸ラベルの範囲］に名前［月］を指定します。

［データソースの選択］ダイアログボックスの［OK］ボタンをクリックして閉じます。これで完了です。

データを入力すると、すぐにグラフに反映されます。

このOFFSET関数を使用した表の場合は、表の月を先に表示していても（例えば、3月までを予め入力していても）グラフには反映されません。

A店とB店の数値を入力した時にグラフに反映されます。

参考罫線も自動で設定することができます。以下の記事で解説しています。

ここでは、3月まで拡張されることを考えてセル［A3］からセル［C15］までに条件付き書式を設定します。