定時で上がろう! Excel関数の底力 第71回 “動的なグラフ”の作り方、関数SERIESを上手に活用

今回、紹介するのは「グラフ」を加工できる関数SERIESの使い方だ。SERIESは他の関数とは一線を画す存在で、厳密には「関数」に分類すべきものではないかもしれない。とはいえ、“カッコ内に引数を指定して処理をコントロールする”という点は他の関数と同じ。少し特殊な存在であるが、その使い方を学んでおいても損はないだろう。
関数SERIESの基本的な使い方
一般的な関数は、数値や文字列といった「データ」を処理する機能を有している。一方、今回紹介する関数SERIESは「グラフ」をコントロールする関数となる。この関数SERIESを上手に活用すると、データに応じて動的に変化するグラフを作成できるようになる。その具体例をいくつか紹介していこう。
関数SERIESは自分で入力して使用する関数ではなく、「グラフ」を作成すると自動的に使用可能になる関数だ。
以下の図は、各店舗の売上データをもと「縦棒グラフ」を作成した例だ。このグラフ内にある系列をクリックして選択すると、数式バーに「関数SERIES」が表示されるのを確認できる。
カッコ内の記述が見やすくなるように、シート名を「Sheet1」から「売上」に変更した例を紹介しておこう。
これで少しは関数の記述を見やすくなったはずだ。まずは、関数SERIESの書式から紹介していこう。
◆関数SERIESの書式
=SERIES(系列名, ラベル範囲, データ範囲, 順序)
先ほど示した図の場合、各引数の内容は以下のようになる。
・第1引数（系列名）
「売上」シートのC3セルを系列名にする。
・第2引数（ラベル範囲）
「売上」シートのB4:B7をラベルとして使用する。
・第3引数（データ範囲）
「売上」シートのC4:C7の数値を使ってグラフを描画する。
・第4引数（順序）
この系列を「1番目の系列」として扱う。
「!」や「$」を含む形でセル参照が記述されているだけで、各引数の内容は特に難しいものではない。念のため、各記号の意味を補足しておこう。
「!」……… それより前の記述が「シート名」であることを示す記号
「$」……… セルを「絶対参照」で指定するときに使用する記号
もちろん、関数SERIESの引数を書き換えると、それに応じて「グラフ」の描画も変更される。たとえば、第3引数（データ範囲）をC4:C6に変更すると、C7セル（4月の新宿店）を除外した形にグラフを加工できる。
さらに、第4引数（順序）を2に変更すると、「新宿店」が2番目の系列として扱われるようになり、「新宿店」と「品川店」の並び順を入れ替えることができる。
このように、関数SERIESの記述を変更して「グラフ」をカスタマイズすることも可能である。そこで、この仕組みを効果的に活用していこう、というのが今回の連載の主旨だ。
なお、関数SERIESは各系列に1つずつ自動作成される仕組みになっている。先の例は「新宿店」について関数SERIESの記述を確認・変更したものだ。「品川店」の系列について関数SERIESの記述を確認・変更したいときは、「品川店」の系列を選択した状態で数式バーを操作すればよい。
データ数に合わせてグラフ化する範囲を自動変更する
続いては、関数SERIESを操作して“動的なグラフ”を作成する方法を紹介していこう。最初に紹介するのは、データ数に応じて「グラフ化するセル範囲」が自動的に変化するグラフだ。
セル範囲をコントロールしたいときは、関数OFFSETを利用するとよい。
◆関数OFFSETの書式
=OFFSET(基準セル, 行の移動, 列の移動, [高さ], [幅])
関数OFFSETの基本的な使い方から紹介していこう。たとえば、以下の図のように関数OFFSETを入力すると、「C4:C7」のセル範囲（新宿店のデータ）を“セル参照”として取得できる。
簡単に補足しておこう。基準セル（第1引数）は「C4」で、そこから行方向に「0」（第2引数）、列方向に「0」（第3引数）だけ移動する。移動量0なので、このセルは「C4」のまま変わらない。そこから高さ「4」（第4引数）、幅「1」（第5引数）のセル範囲、という意味になる。その結果、「C4:C7」のセル参照が得られることになる。
なお、Excel 2019以前の場合は、結果をスピル出力できないため「#VALUE!」のエラーが生じてしまう。とはいえ、関数OFFSETそのものはExcel 2019以前でも使用可能だ。
続いては、このセル範囲を「データ数」に応じて可変にしてみよう。第4引数を関数COUNTに変更し、C列に入力されている「数値データの個数」を「高さ」として指定する。
現時点ではC列に「4個の数値データ」が入力されているので、関数COUNTの値（高さ）は「4」になる。よって、C4から4行分のセル参照（C4:C7）となる。つまり、先ほどと同じ結果になる。
その後、C列に数値データを追加入力した例を見ていこう。下図のように数値データを追加すると、関数COUNTの値は「7」になり、C4から7行分のセル参照（C4:C10）を得ることができる。
このような仕組みで「データ数」に応じてセル範囲を変化させることで“動的なグラフ”を実現する。
ここで問題となるのが、関数SERIESの中に“別の関数”を記述できないことだ。よって、「名前の定義」を使ってセル範囲を指定する必要がある。順番に解説していこう。
「数式」タブを選択し、「名前の定義」コマンドをクリックする。
すると、「新しい名前」というダイアログが表示され、セル範囲に「好きな名前」を付けられるようになる。この時点で、あらかじめダイアログの幅を広くしておくとよいだろう（ウィンドウの右下をドラッグする）。
まずは「新宿店の数値データ」のセル範囲から定義していこう。今回は「新宿」という名前を付けた。続いて、範囲を「ブック」から「現在のシート名」（売上）に変更する。
あとは、参照範囲を関数OFFSETで指定するだけ。この指定には絶対参照を使用するのが基本だ。C4セルを基準に「C列にある数値データの個数」だけ「高さ」を確保すればよいので、関数の記述は「=OFFSET($C$4,0,0,COUNT($C:$C),1)」となる。
次は「品川店の数値データ」のセル範囲を定義していこう。こちらは「品川」という名前を付けた。以降の手順は先ほどと同じ。D4セルを基準に「D列にある数値データの個数」だけ「高さ」を確保すればよい。よって、関数の記述は「=OFFSET($D$4,0,0,COUNT($D:$D),1)」となる。
最後に「横軸のラベル」となるセル範囲を定義する。こちらは「月」という名前を付けた。考え方は先ほどと同じであるが、今回はデータが「文字列」になることに注意する必要がある。よって、関数COUNTAを使用する。また、B1セルに「表のタイトル」が入力されていることも考慮しなければならない。よって、「高さ」の部分は「COUNTA($B:$B)-1」と指定する。
以上で「名前の定義」は完了。あとは、これらの名前を使って関数SERIESの引数を変更していけばよい。
「新宿店」の系列を選択し、第2引数（ラベル範囲）に「月」、第3引数（データ範囲）に「新宿」のセル範囲を指定する。なお、「シート名」と「!」の記述は、そのまま残しておく必要がある。
続いて、「品川店」系列を選択し、第3引数（データ範囲）に「品川」のセル範囲を指定する。なお、第2引数（ラベル範囲）は「新宿店」の系列と連動しているため、すでに「月」のセル範囲に置き換えられている。
これで「データ数」に応じて“動的に変化するグラフ”にカスタマイズできた。試しに、5月、6月のデータを表に追加してみると……、それらのデータもグラフに自動追加されるのを確認できる。
このように、関数OFFSETで「グラフ化するセル範囲」を「名前」として定義しておき、その「名前」を関数SERIESの引数に指定すると、“動的なグラフ”を実現できるようになる。
指定した範囲だけをグラフ化する方法
関数SERIESで“動的なグラフ”を作成した例をもう一つ紹介しておこう。今度は、好きな範囲だけを切り出して表示するグラフだ。
以下の図は、1920〜2020年における「日本の人口推移」をグラフ化したものだ。このグラフを「指定した期間」だけ表示するグラフにカスタマイズしていこう。
まずは「期間」を指定するためのセルを用意する。ここでは「開始年の西暦」と「そこからX年間」を指定するセルを以下のように作成した。それぞれの値には、とりあえず適当な数値を指定しておけばよい。
続いて「名前の定義」を行う。まずは“グラフ化する数値データ”のセル範囲を「人口データ」という名前で定義する。関数OFFSETは、以下のように考えて記述すればよい。
（1）基準セルはC4セル
（2）そこから「F22 - 1920」だけ行方向へ移動する
（3）列方向には移動しない（移動量0）
（4）「高さ」はF23セルの値と同じ
（5）「幅」は1列
これらを絶対参照で記述すると、「=OFFSET($C$4,$F$22-1920,0,$F$23,1)」となる。
同様の手順で“横軸のラベル”となるセル範囲を「西暦」という名前で定義する。対象にする列がB列になるだけで、基本的な考え方は先ほどと同じだ。よって、関数の記述は「=OFFSET($B$4,$F$22-1920,0,$F$23,1)」となる。
あとは、これらの名前を使って関数SERIESの引数を変更するだけ。第2引数（ラベル範囲）に「西暦」、第3引数（データ範囲）に「人口データ」のセル範囲を指定する。
これでグラフのカスタマイズは完了だ。「Enter」キーを押して関数SERIESの修正を確定すると、グラフ表示が1950年から50年間（1950〜1999年）のデータに変化するのを確認できる。
もちろん、期間を指定する数値を変更すると、それに応じてグラフ表示も自動的に変化する。以下の図は、1980年から41年間（1980〜2020年）にグラフ表示を変化させた例だ。
このように関数SERIESを操作することで“動的なグラフ”を作成することも可能である。興味がある方は試してみるとよいだろう。