【Excel】データを追加してもグラフの範囲が自動で更新される方法
こんにちは。行未です。
皆さんいかがお過ごしでしょうか。例によって、Excelの備忘録です。
背景
「データを追加してもグラフの範囲が自動で更新される方法」の情報についてGoogle先生にお聞きすると、
「元のデータを追加するとグラフも自動的に更新する」という説明をみるサイトは多く存在しました。
ですが、今回は更に一歩踏み込んで、「グラフの横軸の基準（左端）を任意で指定できないか」というところでこれを作成しました。
問題点
まず、以下の図のグラフに注目していただければと思います。
（とりあえずグラフの上の2×2の表は無視して見て下さい）
何の変哲もないただのグラフです。グラフのデータはグラフ左のデータ列から取得しています。
このグラフについて何が言いたいかというと、この状態のグラフでは、
- グラフの基準の年度（横軸1つめの値）を変更したい場合
- データの追加
等に柔軟に対応しにくくなります。
一つ一つ手作業で丁寧にやるとするならば、
- 「1.グラフの基準の年の変更」はグラフのフィルター機能を使う
- 「2.データの追加」はデータをテーブル化する
が妥当なところでしょうか。
「2.データの追加」は一度テーブルとして設定してしまえばOKです。
なので、今回は「1.グラフの基準の年の変更」について考えてみましょう。
上のグラフは横軸が「年」です。つまりフィルターをかける作業は、「大体１年ごとだな…」と想像できます。
ですが、これが例えば、「日」となると、グラフに対して毎日フィルターをかける作業が必要になり、結構な労力となりそうです。
頭の中で上司を殴っても解決しません。
これらを改良したエクセルファイルが以下の図です。
「グラフの基準年産」の横のセル（F3セル）を変更すると、それに対応してグラフも変化しています。
また、2017年のデータを追加も行い、これもグラフを見ると同様に対応していることが分かります。
今からやる作業を纏めると、「データの取得を、基準年から最後に入力された年までを自動的に参照するようなグラフに改造する」ってことですね。
それでは手順について書いていきます。
手順
今回の手順で網羅するところといえばこの辺りですね。
- 名前付き範囲
- OFFSET関数
- MATCH関数
- INDIRECT関数
グラフを作成
まず、適当なデータを選んでグラフを作ります。
範囲を選択して【挿入】タブのグラフから【折れ線】を選択してください。
すると、このようにグラフが挿入されます。
作成したグラフの折れ線グラフをクリックすると、グラフの参照範囲が現れ、左のデータ全てが全て参照されていることが分かります。
このグラフを、 「データの取得を基準年から最後の年までを自動的に参照するようなグラフ」となるように改造していきます。
名前付き範囲の設定
【数式】タブの「名前の管理」にて、名前付き範囲を2つ新規に作成します。
下の画像で言うと、網付き2つの箇所ですね。
名前は自分（と他人）が分かりやすければ何でもいいです。今回は年産としました。
重要なのがここからです。
年産の参照範囲を以下のように設定してください。
消費地市場価格は上とほとんど一緒です。隣り合う列なので。引数の3つめを1にするくらいですね。この操作により、基準セルが1つ右に移動にします。
いずれにしても長いですね。たぶん本職の方からするとしょっぱい数式だと思います。たぶん。
各関数の番号が振ってある箇所について、1つずつ説明していきましょう。
面倒くさい方は飛ばしてもらっても全然問題ありません。
OFFSET関数について
OFFSET関数の特徴は以下の通りです。
セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。返されるセル参照は、単一のセル、セル範囲のいずれかの参照です。また、返されるセル参照の行数と列数を指定することもできます。
https://support.office.com/
私は、セルの参照範囲を可変にして設定したいときに使います。
先程の参照範囲の記述と書式を対応させると以下のようになります。（列は省略しています）
この式で、セルの範囲を取得するわけです。
INDIRECT関数について
INDIRECT関数は参照するセル番地を文字列で指定できます。
例えば、見たいセル番地が常に「A1」だったらそのまま「A1」と入力すればよいですね。
しかし、場合によっては見たいセル番地が「A2」かもしれません。
このように、参照したいセルについて、その数式の中身を「1」⇒「2」と手作業で設定するのは面倒です。
今回の場合でいうと、基準年は可変です。なので基準年を設定し、そのインデックス（番号）を「データ・グラフ!$F$4」で取得しています。
上記の数式により、データ列の基準セルが取得できるわけです。
尚、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」を最後に加えています。
グラフの参照範囲の設定
以上までの操作で、アレンジ前のグラフと名前付きの範囲を設定しました。
実際にグラフの参照範囲を設定します。
グラフの折れ線をクリックすると、数式バーに数式が現れるのでこれを書き直します。
今回は以下のように書き換えてください。
以上で操作は終わりです。
グラフの基準年産（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