表示するデータを自由に変更できるグラフ

雑多なデータが含まれる表をもとに、「特定の列」だけをピックアップしたグラフを作成したい場合もあるだろう。このような場合にVLOOKUP関数を利用すると、「表示するデータを自由に変更できるグラフ」を作成できる。ということで、今回は脱初心者の代名詞としてもお馴染みのVLOOKUP関数をグラフに応用した例を紹介していこう。

スケールの異なるデータをグラフ化すると……

まずは、グラフのもとになるデータ表を紹介していこう。以下の図に示した表は、ある飲食チェーンにおける各店舗のデータを集計したものだ。具体的には、各店舗の席数、1日の売上（ランチ／ディナー／合計）、売上におけるランチの割合、客単価といったデータが集計されている。

• 

  グラフ作成用のデータ

この表をグラフ化し、状況を分析していく場合を考えてみよう。試しに、普通に「集合縦棒」のグラフを作成してみると、以下の図のような結果が得られた。

• 

  「集合縦棒」のグラフを作成した場合

「ランチ」や「ディナー」、「1日の売上」について、各店舗の状況を比較することは不可能ではないが、それ以外のデータは数値が小さすぎるため、グラフでは状況を確認できなくなってしまう。

これは各項目のスケールが大きく異なることが根本的な原因だ。「売上」のデータは数十万単位のスケールになるが、「客単価」は数千程度のスケールしかない。「ランチ割合」にいたっては0～1（0～100％）というスケールになる。このようにスケールが全く異なるデータを1つのグラフで表現しようとするのは、どだい無理な話である。

今回の例のような場合は、項目ごとにデータを切り分けてグラフを作成するのが一般的だ。たとえば、グラフ フィルターを使って「1日の売上」だけをグラフ化すると、各店舗の状況を比較可能なグラフに仕上げられる。

• 

  「1日の売上」だけを比較したグラフ

同様に「客単価」だけをグラフ化すると以下の図のような結果になり、店舗によって「客単価」が大きく異なることを把握できる。

• 

  「客単価」だけを比較したグラフ

ただし、そのつどグラフ フィルターを操作するのは少し面倒な作業になるかもしれない。項目別に何個もグラフを作成する方法もあるが、この場合は大きなスペースが必要になってしまう。

そこで今回は、「表示するデータを自由に変更できるグラフ」を作成する方法を紹介してみよう。

VLOOKUP関数でグラフ作成用の表を用意する

今回、紹介するテクニックは、VLOOKUP関数を応用することにより「グラフ化するデータ」を切り替える手法となる。順番に解説していこう。

まずは、グラフ作成用の表を新たに用意する。具体的には、「店舗名」のデータをコピー＆ペーストして、右隣に「空白の列」を配置した表を作成すればよい。

• 

  グラフ作成用の表を新たに準備

また、グラフ化する列を指定するためのセル（F20セル）も用意しておく必要がある。こちらは「N列目が何のデータを指しているか？」を把握できるように、「数値」と「項目名」の対応を追記しておいた。

あとは、VLOOKUP関数を使って数値データをピックアップするだけだ。念のため、VLOOKUP関数の書式も示しておこう。

=VLOOKUP(検索値, 範囲, 列番号, 検索方法)

• 

  VLOOKUP関数の入力

「検索値」には、データ検索に用いる文字列などを指定する。今回の例では、先ほどコピー＆ペーストした「店舗名」が入力されているセル（B14）を指定すればよい。

「範囲」には、元の表のセル範囲を指定する。今回の例では「B2:H11」となるが、VLOOKUP関数をオートフィルでコピーしたときにセル範囲が変化しないように絶対参照でセル範囲を指定している。

「列番号」には、「元の表の何列目のデータを取り出すか？」を指定する。この値はF20セルに入力した値で指定する。こちらもオートフィルでコピーしたときにセル参照が変化しないように絶対参照で指定している。

最後の「検索方法」は完全一致にするので「FLASE」を指定している。

VLOOKUP関数を正しく入力できたら、これを他のセルにオートフィルでコピーする。その後、列を選択するセル（F20セル）に適当な数値（2列目の場合は「2」など）を入力すると、元の表からデータを抽出できるようになる。

• 

  VLOOKUP関数でデータを抽出した例

なお、項目名を示すラベルも同様の手順で取得することが可能だ。今回の例の場合、C14セルに入力したVLOOKUP関数を上方向にオートフィルでコピーしても構わない。

• 

  ラベルを取得するVLOOKUP関数の入力

以上でVLOOKUP関数の入力は完了。列を選択するセル（F20セル）の数値を変更すると、それに応じて抽出されるデータが変化するのを確認できるはずだ。

• 

  VLOOKUP関数の動作確認

表示項目を自由に変更できるグラフの作成

あとは、新たに作成した表をもとにグラフを作成するだけ。この手順に特に変わった点はない。表内のセルを1つだけ選択して（または表全体を選択して）、「挿入」タブにあるコマンドでグラフの種類を指定すればよい。

• 

  グラフの作成手順

続いて、グラフ内にある文字の書式などを調整すると、以下の図のようなグラフに仕上げられる。

• 

  グラフ内にある各要素の書式を指定

このグラフは「VLOOKUP関数により取得されたデータ」をもとに作成されるため、F20セルの数値を変更すると、それに応じてグラフ表示も変化する。たとえば、F20セルの数値を「3」に変更すると、「元の表の3列目」のデータを使ってグラフが再描画されることになる。

• 

  3列目（ランチ）を選択した場合

同様に、F20セルの数値を「6」に変更すると「元の表の6列目」、F20セルの数値を「7」に変更すると「元の表の7列目」のデータでグラフが再描画される。

• 

  6列目（ランチ割合）を選択した場合

• 

  7列目（客単価）を選択した場合

グラフ タイトルの部分には「C13セルの値」が自動的に反映されるため、「何を示したグラフなのか？」を一目で判断することも可能だ。

なお、「元の表の列数」を超える数値（または文字など）を入力した場合は、VLOOKUP関数でエラーが生じるため、グラフ表示は空白になる。

• 

  「表の列数」を超える数値を入力した場合

こういったエラーを回避するには何らかの対策を施しておく必要がある。とはいえ、自分だけが使用するExcelファイルであれば、このエラー対策は放置しておいても構わまないだろう。

以上が、今回の連載で紹介したテクニックの全貌となる。VLOOKUP関数の使い方に慣れている方なら、その仕組みを容易に理解できるだろう。

もちろん、この記事の冒頭でも話したように、グラフ フィルターを使って同様の機能を実現しても構わない。むしろ、「グラフ フィルターの方が簡単なのでは？」と感じる方もいるだろう。

今回の連載で紹介したテクニックは、大きな表から必要なデータだけを抽出してグラフ化する手法の一つとして、グラフ作成時のヒントにしていただければ幸いだ。

Excelには、ピボットグラフのようにもっと使い勝手のよい機能も搭載されている。よって、今回のテクニックを実際に使用するのは機会は滅多にないかもしれない。ということで、次回はピボットグラフの使い方を詳しく紹介していこう。