今回、紹介するのは「グラフ」を加工できる関数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」と指定する。

  • 「月」のセル範囲の定義

以上で「名前の定義」は完了。あとは、これらの名前を使って関数SERIESの引数を変更していけばよい。

「新宿店」の系列を選択し、第2引数(ラベル範囲)に「月」、第3引数(データ範囲)に「新宿」のセル範囲を指定する。なお、「シート名」と「!」の記述は、そのまま残しておく必要がある。

  • 関数SERIESの変更(新宿店の系列)

続いて、「品川店」系列を選択し、第3引数(データ範囲)に「品川」のセル範囲を指定する。なお、第2引数(ラベル範囲)は「新宿店」の系列と連動しているため、すでに「月」のセル範囲に置き換えられている。

  • 関数SERIESの変更(品川店の系列)

これで「データ数」に応じて“動的に変化するグラフ”にカスタマイズできた。試しに、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引数(データ範囲)に「人口データ」のセル範囲を指定する。

  • 関数SERIESの変更

これでグラフのカスタマイズは完了だ。「Enter」キーを押して関数SERIESの修正を確定すると、グラフ表示が1950年から50年間(1950~1999年)のデータに変化するのを確認できる。

  • 1950年から50年間をグラフ化した場合

もちろん、期間を指定する数値を変更すると、それに応じてグラフ表示も自動的に変化する。以下の図は、1980年から41年間(1980~2020年)にグラフ表示を変化させた例だ。

  • 1980年から41年間をグラフ化した場合

このように関数SERIESを操作することで“動的なグラフ”を作成することも可能である。興味がある方は試してみるとよいだろう。