“動的なグラフ”の作り方、関数SERIESを上手に活用

今回、紹介するのは「グラフ」を加工できる関数SERIESの使い方だ。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」のセル範囲（新宿店のデータ）を“セル参照”として取得できる。

関数OFFSETの入力

簡単に補足しておこう。基準セル（第1引数）は「C4」で、そこから行方向に「0」（第2引数）、列方向に「0」（第3引数）だけ移動する。移動量0なので、このセルは「C4」のまま変わらない。そこから高さ「4」（第4引数）、幅「1」（第5引数）のセル範囲、という意味になる。その結果、「C4:C7」のセル参照が得られることになる。

関数OFFSETの出力結果

なお、Excel 2019以前の場合は、結果をスピル出力できないため「#VALUE!」のエラーが生じてしまう。とはいえ、関数OFFSETそのものはExcel 2019以前でも使用可能だ。

続いては、このセル範囲を「データ数」に応じて可変にしてみよう。第4引数を関数COUNTに変更し、C列に入力されている「数値データの個数」を「高さ」として指定する。

「高さ」を関数COUNTで指定

現時点ではC列に「4個の数値データ」が入力されているので、関数COUNTの値（高さ）は「4」になる。よって、C4から4行分のセル参照（C4:C7）となる。つまり、先ほどと同じ結果になる。

関数OFFSETの出力結果

その後、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」と指定する。