Office 2019 Excel 2019

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

投稿日:2021年11月8日 更新日:

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

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

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

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

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

縦棒グラフとグラフのデータ範囲

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

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

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

グラフの元データ

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

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

[挿入]タブの[グラフ]グループにある[おすすめグラフ]

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

グラフの完成

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

[縦棒/横棒グラフの挿入]の[集合縦棒]

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

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

グラフ内の[グラフタイトル]を選択して、数式バーに[=](半角)で入力します。

グラフタイトル

セル[A1]をクリックします。

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

グラフタイトルのセル参照

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

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

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

グラフのデータ範囲にデータを追加

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

セルに書式設定が勝手に拡張される場合の確認方法

なぜかセルに書式される場合、いくつかの原因が考えられます。 書式設定が自動で拡張されると知っていて使うのであればいいのですが、意図せずに拡張 ...

続きを見る

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

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

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

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

このまま、参照元範囲の最終セルの位置(ここではセル[C7])までドラッグします。

マウスでデータ範囲を拡張

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

データ範囲が拡張されたグラフ

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

データ選択

グラフの対象データ範囲を変更します。

[グラフデザイン]タブの[データ]グループにある[データの選択]

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

[データソースの選択]ダイアログボックスの[グラフデータの範囲]を変更

グラフのデータ範囲をテーブルに変更

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

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

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

テーブルに変換

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

ショートカットキーは、[Ctrl]+[T]です。

[挿入]タブの[テーブル]グループにある[テーブル]

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

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

[テーブルの作成]ダイアログボックス

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

テーブル設定後

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

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

[テーブルデザイン]タブの[テーブルスタイル]グループにある[その他]

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

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

スタイルの一覧の[なし]

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

スタイル[なし]のテーブル

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

[テーブルデザイン]の[フィルターボタン]

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

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

フィルターをオフにしたテーブル

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

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

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

テーブルにデータを追加したグラフ

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

テーブルのサイズ変更と解除(自動拡張の設定はオートコレクトで)

Excelでテーブルを作成した後、テーブルのサイズを変更する方法です。 また、テーブルを通常の表に変換する方法と、自動拡張の設定についても解 ...

続きを見る

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

テーブルの便利機能(テーブルに変換して作業効率アップ)

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

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

入力規則のリストが可変(増減)するならテーブルに変換して自動修正

Excelの入力規則でドロップダウンリストから選択できるように設定することは多いですね。 でも、そのリストが増減する場合、その度に入力規則の ...

続きを見る

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

表をテーブルに変換したら制限されること

Excelのテーブルのメリットは多いのですが、テーブルに変換すると、普通の表なら便利に使っていた機能が使えないということがあります。 テーブ ...

続きを見る

OFFSET関数を使って自動拡張

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

[月]の範囲(セル[A3]からセル[A6])を選択して、[数式]タブの[定義された名前]グループの[選択範囲から作成]をクリックします。

選択範囲から作成

選択したセルから名前を自動的に生成します。

通常は、最上部の行または左端の列を選択します。

[月]の範囲を選択して[数式]タブの[選択範囲から作成]

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

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

[選択範囲から名前を作成]ダイアログボックス

[A店]のセル範囲(セル[B3]からセル[B6])、[B店]のセル範囲(セル[C3]からセル[C6])も同じように名前を作成します。

[A店]の範囲を選択して[選択範囲から名前を作成]ダイアログボックス

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

ショートカットキーは、[Ctrl]+[F3]です。

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

[名前の管理]ダイアログボックス

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

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

[A店]を選択して[編集]ボタンをクリック

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

[名前の編集]ダイアログボックス

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

=OFFSET($B$4,0,0,COUNT($B:$B))

[参照範囲]を修正

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

=OFFSET($C$4,0,0,COUNT($C:$C))

[名前の編集]ダイアログボックスの[参照範囲]を修正

[月]の参照範囲も編集します。

=OFFSET($A$4,0,0,COUNTA($A:$A)-2)

[名前の編集]ダイアログボックスで[参照範囲]を修正

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

[名前の管理]ダイアログボックス

OFFSET関数

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

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

OFFSET 関数(Microsoft)

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

OFFSET関数

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

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

COUNT関数とCOUNTA関数

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

COUNT 関数(Microsoft)

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

COUNTA 関数(Microsoft)

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

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

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

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

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

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

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

[グラフのデザイン]タブの[データの選択]

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

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

[データソースの選択]ダイアログボックス

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

[系列値]を名前[A店]に変更します。

[系列の編集]ダイアログボックス

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

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

[名前の貼り付け]ウィンドウ

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

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

[系列の編集]の[系列値]

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

再表示した時の[系列の編集]の[系列値]

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

B店の[系列の編集]の[系列値]

[月]は、[横(項目)軸ラベル]の[編集]をクリックします。

[横(項目)軸ラベル]の[編集]

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

[軸ラベル]の[軸ラベルの範囲]を編集

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

[データソースの選択]ダイアログボックスの[OK]ボタン

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

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

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

グラフのデータ範囲にデータを追加した時のグラフ

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

新規データを追加したら自動的に罫線が追加される表にするには

Excelで表を作成する時、罫線を引いたりして書式を整えますね。 データが増えることがない表であれば、それで終わりかもしれませんが、新規デー ...

続きを見る

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

条件付き書式ルールの管理

検索

ほかの記事も検索してみませんか?

おすすめ記事と広告

Profile

執筆者/はま

-Office 2019 Excel 2019

Copyright © 2001-2021 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2021 AllRights Reserved Powered by AFFINGER4.