VLOOKUP関数を駆使して定型レポートを超効率的に完成させる方法

こんにちは。Excelを使いこなす会社員、ユージーン(@Eugene_no2)です!

VLOOKUP関数といえば、数あるExcel関数の中でもかなりメジャーな関数で、使っている人も多いでしょう。

でも、本当に使いこなせていますか?

この記事では、VLOOKUP関数を使って、定型レポートを高速・時短で効率的に作成する方法を解説します。

これを作るだけで多少なりともお金を取れる内容だと思うので、関数に苦手意識がある人は覚悟してお読みください。

でもなるべく丁寧に、分かりやすく解説を心がけています。

VLOOKUP関数を使いこなすと定型レポートはこうなる!

まず手始めに、VLOOKUP関数を使いこなせるようになると、定型レポートがどう変わるのか、について説明します。

この記事でいう、定型レポートとは、毎週、毎月、毎年など、定期的に作成する類のデータ・表・グラフなどを指します。

例えば、自社の売上データや出荷データから、必要な部分を抜粋してレポーティングするイメージです。

特に会社員であれば、そのような仕事が必要になる人も少なくないはず。

このように、決まったデータの形から、必要な部分を抜き出してまとめる場面では、VLOOKUP関数がめちゃくちゃ役に立つんです。

コピペやフィルタ機能をオススメしない理由

そのようなレポートを作るとき、必要な部分を探してコピペしている人もいれば、フィルタ機能を駆使して、数表を作っている人もいるでしょう。

ですが、そのような方法はオススメしません。なぜなら、ミスが起こりやすく、また毎回時間のかかる作業だからです。

毎月作成するレポートであれば、毎月同じだけの作成時間がかかります。

ところが、この記事で解説するVLOOKUP関数を使う方法であれば、初回は少し時間がかかりますが、一度レポート作成用のExcelファイルを作ってしまえば、2回目以降は、元となるデータを貼りかえるだけでレポートを完成させることが出来るようになるんです。

では、具体的な方法を解説していきましょう。

VLOOKUP関数で定型レポートを効率的に仕上げる方法

この記事で紹介する、VLOOKUP関数を駆使して、定型レポートを仕上げるための方法とは、概ねこのような感じです。

  1. 「元データ」を貼り付けるシートと、「レポート用」のシートを分けて作る。
  2. 「元データ」と「レポート用」データ、それぞれのシートに共通した「検索値」を作る。
  3. VLOOKUP関数で、「元データ」の中から必要な数字を、「レポート用」シートに引っ張ってくる。

順番に、丁寧に、解説していきます。

「元データ」と「レポート用」のシートを分けておく

まず、売上や出荷のローデータなど、元となるExcelデータを用意します。このシートを、ここでは「元データ」と名づけておきます。

のちのち、VLOOKUP関数で、このシート名も使用することになるので「元データ」でも「data」でも何でも良いですが、シートに名前をつけておくことをオススメします。

次に、別のシートを「レポート用」として用意します。数字が埋まっていない状態の、数表はこの時点で作成してしまって構いません。

これからやろうとしていることは、数表の空白部分に、VLOOKUP関数を駆使して、「元データ」のシートから、必要な数字だけを引っ張ってくるというわけです。

もし「元データ」の数字だけではなく、そこから計算させることが必要なら、「レポート用」シートの中で計算させましょう。

「元データ」のシートはあくまでローデータを貼り付けるだけ、の状態にしておくことがポイントです。

「元データ」シートのA列に「検索値」を作る

次に行うのは、「元データ」のシートに「検索値」の列を作るという作業。

VLOOKUP関数というのは、指定した「範囲」の中から「検索値」を使って必要なデータを探し出す関数です。

そのために、「元データ」シートのA列には「検索値」になるものが入ってる必要があります。

元々のデータの先頭列(A列)がすでに、各行固有の文字列が入っているのであれば、この作業は不要です。しかし実際には、元データも様々な形式になっており、一筋縄ではいかないことのほうが多いでしょう。

よくあるパターンを例に、「検索値」列の作り方を説明します。

例:「繋ぎの&」で「検索値」を作る

例えば、データの形式が次のようになっている場合は、「繋ぎの&」を使って、各行固有の「検索値」を作成することができます。

繋ぎの&」の便利な活用はこちら。

合わせて読みたい Excel繋ぎの【&】を覚えるだけで仕事は劇的に変わる!
関数ではないものの、関数と同じくExcelの便利機能「&」(わたしは「繋ぎの&」と呼んでいます)。資料作成にも使える「繋ぎの&」を解説

なお「検索値」は先頭列(A列)に列挿入をして作成するようにしましょう。

例:空白がある場合はIF関数を活用する

繋ぎの&」だけでは検索値を作れない場合があります。例えば、こんなケース。

この場合、空白があるため、「繋ぎの&」で繋ぐだけでは、各行固有の「検索値」を作ることができません。

でも大丈夫。こういう場合はまず、空白のない列を作ってあげましょう。

実はこれ、IF関数を使えば一発でできるんです。

ちょっとややこしいですが、理解できますか?

C9セルが空白なら、一つ上のB8セルの値を。C9セルが空白でないなら、C9セルの値を。

と命令しているのです。こうすれば、うまい具合に空白を埋めることができます。

間違っても、コピペなど手作業でこれをやってはダメです。次回も同じ作業をしなければいけなくなってしまいますから。

あとは先ほどと同じように「繋ぎの&」を使えば、各行固有の「検索値」の完成です。

「レポート用」シートにも「検索値」を作る

さて、続いては、「レポート用」のシートにも同様の「検索値」を作るという作業です。

VLOOKUP関数は「検索値」を手がかりにデータを引っ張ってくるので、「元データ」と「レポート用」両方のシートに「検索値」が必要なのです。

レポート用」の検索値は当然ながら、「元データ」側の検索値と同じものが出来上がるようにする必要があります。

必要なデータが、「元データ」ではどのような「検索値」になっているのかを踏まえ、「検索値」の列を作りましょう。

必要であれば、先ほど同様に「繋ぎの&」などを活用しましょう。

「レポート用」シートにVLOOKUP関数用の範囲・列番号を用意する

そろそろVLOOKUP関数を…、と言いたいところですが、もう二つ、準備するべきものがあります。それが、VLOOKUP関数に使用する「範囲」と「列番号」。

=VLOOKUP(検索値,範囲,列番号,検索方法)
(訳:範囲の中から検索値を探して、その列番号番目の数字を返せ)

VLOOKUP関数は、指定した範囲の中から、検索値を探して、その列番号目のデータを引っ張る関数なので、「範囲」と「列番号」についても準備をしておく必要があります。

VLOOKUP関数用の「範囲」を記載する

範囲」を指定するのは簡単です。「レポート用」シートのA1セルあたりに、「元データ!A:Z」などと書いておけばOKです。

これが意味しているのは、「元データ」シートのA列~Z列のこと。

別のシート名にしているのであれば、「」の前のシート名を変更すればOKです。

A:Z」はA列~Z列までを指しますが、「元データ」がZ列まで入っていなくても構いません。VLOOKUP関数は、大は小を兼ねるのです。

逆に、元データがZ列を超えるような場合には、もっと余裕をみて「A:BZ」とか「A:ZZ」とかしておけばOKです。

なお、この記事では「レポート用」シートのA列を「検索値」にする前提で書いていますので「範囲」の開始列はA列ですが、もし「検索値」がB列にある場合は、「元データ!B:Z」などとする必要がありますので、ご注意ください。

VLOOKUP関数用の「列番号」を記載する

続いて「列番号」です。わたしがよくやる方法は、「レポート用」シートの1行目を列番号専用にして、各列に必要な列番号を入力しておきます。

ここでいう「列番号」とはつまり、「元データ」シートの「検索値」の列から数えて何列目のデータを引っ張ってきたいのか、ということです。

例えば、この例では、1月のデータは元データの5列目にありますから、「列番号」として入れるべき数字は、ということになりますね。

同じように2月は6列目、3月は7列目となるわけです。

VLOOKUP関数を駆使して必要なデータをレポートへ

さて、初めての人はここまでも大分時間がかかったかも知れませんが、これでVLOOKUP関数を使う準備が全て整いました。

これまでに用意した「範囲」「検索値」「列番号」をそれぞれ指定すれば、レポートの各セルに欲しいデータを引っ張ってくることができるようになっています。

例えば、C5セルに入れる数式は、次のようになります。

=VLOOKUP($A5,INDIRECT($A$1),C$1,FALSE)

ちょっと長いですが、一つ一つ見れば難しくないはずです。

INDIRECT関数とは?

なお、「範囲」の指定にINDIRECT関数が登場しています。知らない人も多いかも知れません。

INDIRECT関数は()で囲った中に、数式などを使って、直接セルの住所(例:元データ!A:Z)を入力できる関数・・・なのですが、言葉での理解が非常に難しい関数です。

ここでは、説明は割愛しますが、「元データ!A:Z」のように、セルの住所を参照するときに必要になる、という理解でひとまずOKとしましょう。

絶対参照にご注意!

上の数式では絶対参照相対参照(複合参照)が組み合わさっています。($A$1などの「$」マークのことです。)

その理由は、1セルだけVLOOKUP関数を使った数式を入力できたら、あとはセルをコピーするだけでよくなるからです。

  • 「範囲」($A$1)は、どのセルでも固定になるので、行列ともに絶対参照。
  • 「検索値」($A5)は、列のみを固定(縦には動いてOK)なので、列のみ絶対参照。
  • 「列番号」(C$1)は、行のみを固定(横には動いてOK)なので、行のみ絶対参照。

という参照の仕方になります。

そもそも絶対参照って何?という方のために、分かりやすい解説を書いてみましたので、ぜひこちらをどうぞ。

*関連記事

また、ここではVLOOKUPで複数列を一気に取得していますが、この方法についての詳しい解説はこちら。

合わせて読みたい VLOOKUP関数で複数列のデータを一気に取得する方法
VLOOKUP関数の応用活用術。列番号を記載することで、複数列を一気に取得する方法を解説。

元データを貼り替えれば自動的にレポートが完成!

というわけで、VLOOKUP関数を駆使して、元データから必要なデータを引っ張ってくる作業が完了しました。

正しくデータを引っ張ってこれているかどうか、いくつかチェックをすることをオススメします。

さて、このVLOOKUP関数を使った方法、おそらく地道にコピペしたり、フィルタ機能を使うよりも、時間がかかったことでしょう。

でも、この方法が本領を発揮するのは、冒頭でも触れた通り、2回目以降なのです。

翌月、新しい元データ(売上データや出荷データ)が更新されたとしましょう。

あなたがやるべき作業は、Excelファイルの「元データ」のシートに、ずれないように新しいデータを貼り付けるだけ!

データを貼り替えれば、その瞬間に、VLOOKUP関数が新しいデータを、「レポート用」シートに運んでくれるのです。

つまり、新しいデータを貼った瞬間に、すでに「レポート」が完成しているわけですから、作業時間はめちゃくちゃ短くなります。

コピペやフィルタ機能、ピボットテーブル機能を使わずに、なるべく関数を使うべき理由は、ここにあります。

関数を使えば、レポート作成を半自動化できてしまうのです!

「検索値」「範囲」「列番号」を変えれば、レポートの編集も簡単!

また、VLOOKUP関数を使って、レポートを作成するメリットは、2回目以降の時短だけではありません。

一度この方法を理解してしまえば、応用が効くのです。

例えば、レポートに必要なデータが変わった、増えたなどが起きた場合、「レポート用」ファイルある「範囲」「検索値」「列番号」を書き換えてあげれば、どこにデータを引っ張ってくるのか、自由自在に操ることができるようになります。

元データが複数のシートに分かれたって、「範囲」の部分を工夫すれば怖くありませんよね。

というわけで、この記事では、VLOOKUP関数を駆使して定型レポートを超効率的に完成させる方法について解説してきました。

関数に苦手意識のある方には難しかったかも知れません。

でも、もし定型レポートを手がけているのであれば、このやり方を体得する価値は計り知れませんよ。

レポートを作ることに時間をかけるより、レポートから何が起きているのかを読み取ること、どうアクションすべきかを考えることに、時間を割いたほうが良いに決まっています。

他にも、作業効率化、時短に繋がるExcelテクニックを色々とまとめていますので、興味がある方はぜひ、こちらもどうぞ。

合わせて読みたい Excelの便利な活用術・実践的な工夫・小技まとめ
当ブログではたびたび、他のサイトには載っていないような、Excelの便利な活用方法や、実践的な工夫・小技などを紹介しています。