【Excel】データを追加してもグラフの範囲が自動で更新される方法

2019年4月23日

こんにちは。行未です。

皆さんいかがお過ごしでしょうか。例によって、Excelの備忘録です。

背景

「データを追加してもグラフの範囲が自動で更新される方法」の情報についてGoogle先生にお聞きすると、

「元のデータを追加するとグラフも自動的に更新する」という説明をみるサイトは多く存在しました。

ですが、今回は更に一歩踏み込んで、「グラフの横軸の基準(左端)を任意で指定できないか」というところでこれを作成しました。

問題点

まず、以下の図のグラフに注目していただければと思います。

(とりあえずグラフの上の2×2の表は無視して見て下さい)

グラフ例

何の変哲もないただのグラフです。グラフのデータはグラフ左のデータ列から取得しています。

このグラフについて何が言いたいかというと、この状態のグラフでは、

  1. グラフの基準の年度(横軸1つめの値)を変更したい場合
  2. データの追加

等に柔軟に対応しにくくなります。

一つ一つ手作業で丁寧にやるとするならば、

  • 「1.グラフの基準の年の変更」はグラフのフィルター機能を使う
  • 「2.データの追加」はデータをテーブル化する

が妥当なところでしょうか。

「2.データの追加」は一度テーブルとして設定してしまえばOKです。

なので、今回は「1.グラフの基準の年の変更」について考えてみましょう。

上のグラフは横軸が「年」です。つまりフィルターをかける作業は、「大体1年ごとだな…」と想像できます。

ですが、これが例えば、「日」となると、グラフに対して毎日フィルターをかける作業が必要になり、結構な労力となりそうです。

頭の中で上司を殴っても解決しません。

これらを改良したエクセルファイルが以下の図です。

「グラフの基準年産」の横のセル(F3セル)を変更すると、それに対応してグラフも変化しています。

また、2017年のデータを追加も行い、これもグラフを見ると同様に対応していることが分かります。

今からやる作業を纏めると、「データの取得を、基準年から最後に入力された年までを自動的に参照するようなグラフに改造する」ってことですね。

グラフ完成例

それでは手順について書いていきます。

手順

今回の手順で網羅するところといえばこの辺りですね。

  • 名前付き範囲
  • OFFSET関数
  • MATCH関数
  • INDIRECT関数

グラフを作成

まず、適当なデータを選んでグラフを作ります。

範囲を選択して【挿入】タブのグラフから【折れ線】を選択してください。

グラフ範囲設定画面


グラフ挿入画像

すると、このようにグラフが挿入されます。

作成したグラフの折れ線グラフをクリックすると、グラフの参照範囲が現れ、左のデータ全てが全て参照されていることが分かります。

このグラフを、 「データの取得を基準年から最後の年までを自動的に参照するようなグラフ」となるように改造していきます。

名前付き範囲の設定

【数式】タブの「名前の管理」にて、名前付き範囲を2つ新規に作成します。

下の画像で言うと、網付き2つの箇所ですね。

名前の管理設定画面

名前は自分(と他人)が分かりやすければ何でもいいです。今回は年産としました。

重要なのがここからです。

年産の参照範囲を以下のように設定してください。

OFFSET関数記述例1

消費地市場価格は上とほとんど一緒です。隣り合う列なので。引数の3つめを1にするくらいですね。この操作により、基準セルが1つ右に移動にします。

OFFSET関数記述例2

いずれにしても長いですね。たぶん本職の方からするとしょっぱい数式だと思います。たぶん。

各関数の番号が振ってある箇所について、1つずつ説明していきましょう。

面倒くさい方は飛ばしてもらっても全然問題ありません。

OFFSET関数について

OFFSET関数の特徴は以下の通りです。

セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。返されるセル参照は、単一のセル、セル範囲のいずれかの参照です。また、返されるセル参照の行数と列数を指定することもできます。

https://support.office.com/

私は、セルの参照範囲を可変にして設定したいときに使います。

先程の参照範囲の記述と書式を対応させると以下のようになります。(列は省略しています)

OFFSET関数書式

この式で、セルの範囲を取得するわけです。

INDIRECT関数について

INDIRECT関数は参照するセル番地を文字列で指定できます。

例えば、見たいセル番地が常に「A1」だったらそのまま「A1」と入力すればよいですね。

しかし、場合によっては見たいセル番地が「A2」かもしれません。

このように、参照したいセルについて、その数式の中身を「1」⇒「2」と手作業で設定するのは面倒です。

今回の場合でいうと、基準年は可変です。なので基準年を設定し、そのインデックス(番号)を「データ・グラフ!$F$4」で取得しています。

INDIRECT関数記述例

上記の数式により、データ列の基準セルが取得できるわけです。

尚、F4セルの中身は基準年産の行番号を取得するために、「=MATCH(F3,B:B,0)」としています。

COUNTA関数

COUNTA 関数は、範囲内の空白ではないセルの個数を返します。

こちらの数式により、基準セルからデータ列の終わりまでの行数を取得します。

まず、前半の「COUNTA(データ・グラフ!$B:$B)」でB列全体のデータが格納されているセルの個数を数えます。

そして、前半の個数に対し、「COUNTA(INDIRECT(“データ・グラフ!B1:B"&データ・グラフ!$F$4))+1」を引くことにより、基準セルからデータが格納されている最後の行数を取得します。

最後に「+1」としている理由は、「 COUNTA(INDIRECT(“データ・グラフ!B1:B"&データ・グラフ!$F$4))」だと「B1セルから基準セルまでの個数」となり、基準セル部分まで引くので、数合わせのために「+1」を最後に加えています。

グラフの参照範囲の設定

以上までの操作で、アレンジ前のグラフと名前付きの範囲を設定しました。

実際にグラフの参照範囲を設定します。

SERIES関数キャプチャ画像

グラフの折れ線をクリックすると、数式バーに数式が現れるのでこれを書き直します。

今回は以下のように書き換えてください。

SERIES関数記述例

以上で操作は終わりです。

グラフの基準年産(F3セル)を「2008」に変更し、データを新たに加えましたが、グラフは自動的に変更されます。

完成例

終わりに

以下サイトを参考にさせていただきました。

グラフの範囲を自動更新:https://www.ipentec.com/document/office-excel-graph-set-auto-range

グラフの参照範囲について:http://officetanaka.net/excel/function/tips/tips71.htm

リンゴ価格:https://www.ringodaigaku.com/study/statistics/price.html

IT, ExcelIT, Excel

Posted by 行未