こんにちは、hiroです。
今回は、コンサルで使うエクセルについての第1回目、vlookup関数です。
必要なエクセルスキルとしては、大きく次の3つでしょうか。
- 関数
- ピボット
- ショートカット
関数やピボットを使ってデータ分析をしつつ、ショートカットを身に着けていく。
私の場合、そんな感じでエクセルと格闘してきました。
vlookupはこのページに限らず色々なところで紹介されていますが、実際にどう使っているか？を踏まえて書いていきます。
vlookup(検索値, 範囲, 列番号, [検索方法])
データ分析する上で、vlookup関数は必須です。
コンサルファームに入社するのであれば、事前に使えるようにしておきましょう。
業務で確実に使います。この関数が使えないとデータ分析できないレベルです。
vlookup関数について簡単に文書で説明すると、
- 指定した「範囲」の一番左の列から、「検索値」を上から探して最初に一致する行を取得する
- その行における、指定した「範囲」内の一番左の列から数えた「列番号」のセルの値を出力する
- 検索方法は基本的に完全一致の「FALSE」としておく
という感じで、検索値をキーとして指定範囲内の情報を紐付けることができる関数です。
この関数の説明は検索すれば山ほど出てくるので、詳細な説明は割愛しますが、
注意点として指定する範囲の一番左の列にキーとなるデータを用意する必要があります。
ちなみに下記で紹介するINDEX関数とMATCH関数を組み合わせれば、一番左にキーがなくても紐づけできますが、
データ量が少ないのであれば、単純にキーとなる列を一番左にコピーして対応する方が簡単です。
INDEX+MATCHが使いこなせないと困る場面はそうはないので、まずはvlookupを使いこなせるようにしましょう。
INDEX+MATCHを覚えるよりも、ショートカットをマスターする方が重要だったりします。
範囲は絶対参照にしよう
有名な関数なので色々と解説ページがありますが、絶対参照に触れていないページが多いです。
実務上、この関数を入力したセルを縦横にコピペし、情報を紐付けていくことが多いのですが、
絶対参照にしていないとコピペで範囲がずれます。
そうならないために、範囲を選択したら、カンマを打つ前に「F4キー」を1回押しましょう。
範囲が絶対指定に変わります。
もう一度F4を押すと行のみ絶対指定、さらにもう一度押すと列のみ絶対指定することができます。
参考
=VLOOKUP($A2,$H$2:$L$400,3,FALSE)
上の式で、$が絶対参照を示しています。
検索値（$A2）は列(A)だけ絶対参照しています。
1列のデータだけ抽出したいのであれば範囲だけ絶対指定すればよいのですが、
同一の範囲から複数列のデータを抽出したい場合は、検索値の列も絶対指定しておきましょう。
これを忘れると横にコピペした際に検索値がずれます。
ただし、検索値は列だけ絶対指定することに注意してください。
行も絶対指定してしまうと、全て最初の検索値に紐づく値が抽出されてしまいます。
IFERROR関数もセットで使おう
vlookup関数を使う場合、検索値がヒットしないとエラー値の「#N/A」が出力されてしまいます。
それを回避するために、IFERROR関数とセットで使いましょう。
エラー値の場合に代わりに出力する値を指定することができます。
=IFERROR(vlookup(検索値,範囲,列番号,FALSE),""))
エラーの場合に表示させたい文字を""に入れればOKです。
空欄にしたいのであれば、""と入力します。
実際に使用する場合は、フラグ情報をvlookupで紐づける際に、エラー値を"0"や""として変換することが多いです。
データ量が多い場合は、INDEX＋MATCH関数で
範囲の列数にもよりますが、1万行くらいを検索する場合、vlookupだと処理に時間がかかりすぎます。
そんな時は、INDEX+MATCHでデータの紐づけを行います。
INDEX関数 = INDEX(配列,行番号,[列番号])
配列の(行番号,列番号)にある値を取得
MATCH関数 = MATCH(検査値,検査範囲,[照合の種類])
検査値の検査範囲における位置を取得
INDEX関数とMATCH関数を組み合わせて、
INDEX(配列,MATCH(検査値,検査範囲,[照合の種類]),[列番号]) のように使います。
MATCHで検索値に一致する行位置を取得し、その行にあるデータをINDEX関数で取得するイメージです。
何だこれ？って感じですよね。
結論としては、まずはvlookup関数を使えばよいと思います。
はっきり言って、それだけでも十分戦えます。あとはどんなロジックを組むか、です。
INDEX+MATCH関数の使い方については、別に記事を書きたいと思います。
最後は実践あるのみ！
参考となる書籍を紹介すると、有名どころですが、
「エクセル最強の教科書」
「たった1日で即戦力になるExcelの教科書」
のどちらか気に入った方を参照すればよいと思います。
エクセルは実際に自分で使ってみないと分からない部分がありますので、是非実践してみてください。