「Excelって数字がいっぱいで、必要な情報が探しづらい…
結局、データを一覧でまとめてみたけど自分で探す手間がかかるから時間短縮につながってない。」
これだと、Excelの機能が使いこなせているとは言えません。Excelはデータ集計・分析ソフトであり、分析に時間が掛かっていればソフトの価値は下がります。
今回は検索の機能であるLOOKUP関数を使い、データ分析や情報収集を簡単に出来る方法を見ていきましょう。
LOOKUP関数では、主にVLOOKUPとHLOOKUPを使うケースが多いです。
以下、関数の意味です(口語訳)
VLOOKUP
V→バーティカル(vertical):垂直の
HLOOKUP
H→ホライゾンタル(horizontal):水平の
今日はこのVLOOKUPとHLOOKUPについてお話ししてまいります。使いこなしていくことで、出来ることの幅が大きく増える重要な関数です。じっくり読み進めながら、確実に習得していきましょう。
また、VLOOKUP関数は求職などで「Excelを使える」という求人内容ではほぼ必須になってくるケースが多いので、仕事に使えるレベルの登竜門とも言えます。1つずつ内容を細かく確認していきましょう。
式の形と使い方
使用目的
注意事項
LOOKUP関数を使いこなす為に、今回も具体例などで使うExcelファイルを貼付けておくので必要に応じて使ってみてください。
式の形と使い方
使われる頻度が高いのはおそらくVLOOKUPなので、例などはVLOOKUPに統一します。式の形はHLOOKUPでもほぼ変わりませんのでご安心ください。
=VLOOKUP(検索値,範囲,列,検索方法)
これがVLOOKUPの式の形となります。分解して各項目ごとの意味を見ていきます。
今回の例として、八百屋さんの損益を単品で見てみることにします。
八百屋さんから「商品ごとの販売動向や損益が知りたい」と相談がありました。
野菜の種類ごとに見やすい形にまとめつつ、欲しいデータをすぐ見れるようにして、八百屋さんの仕入効率の改善につなげてみましょう。
=VLOOKUP(検索値,範囲,列,検索方法)
◼️検索値
検索値はそもそも何のデータを抽出したいのかを決める部分になります。
今回は『ニンジン』について検索をしていきます。この何を検索するかの部分が検索値に該当します。今回は検索値をB4セルに設定しました。
◼️範囲
範囲は一覧表の全体を指します。範囲としてはD5:L18セル(要は表全体を覆う)としてください。
なお、必ず検索値と同じワードが含まれる部分までカバーしてください。(今回でいうとD列が含まれていないと、検索値の単語を範囲で探しても該当するものが無いためエラーになってしまいます)
◼️列(行)
列は縦軸、行は横軸を指しています。先程、D列の話が出ましたが、D列には野菜の品目が書かれています。列と行はLOOKUPで非常に重要な概念であり、他の関数でもよく使用されます。間違えないようにしましょう。
◼️検索方法
検索方法とありますが、2つしかありません。
近似一致(TRUE・1)
完全一致(FALSE・0)
結論から言ってしまえば、完全一致(FALSE)を使用してください。
TRUEでも使用自体は出来るのですが、複雑な検索方法になってしまい思った形にしようとするとエラーまたは誤った検索結果になってしまう事が多々あります。(二分探索)
改めて野菜販売動向表を見てみましょう。
仕入数(B5セル)の結果を自動で出す式は
=VLOOKUP(B$4,$D$5:$L$18,2,FALSE)
となります。
①B4セルの野菜と一致する野菜を
②D5～L18セルの範囲で
③表の2列目の情報について
④完全に一致する情報を調べる。
こんな形で訳すとわかりやすいです。
すると、ニンジンの仕入数が自動で算出されます。
あとはオートフィルでB12まで引っ張り、VLOOKUPの列に該当する部分だけ変更(原価であれば3、売価であれば4といった形に)すれば完了ですね。(下表参照)
ニンジンのところを表の中にある別の野菜に変えても動くか確認してみます。
レタスに変えてみましょう。（下表参照) 
仕入数や原価、売価など他の項目も自動でレタスのものに自動で置き換わっているのがわかりますね。このように検索ツールとして使う事が出来ます。
◆HLOOKUPについて
HLOOKUPでも基本的な考え方はほぼ変わりません。
変わるのは軸の部分だけになります。
表の違いで考えるとシンプルです。
・野菜の一覧
⇒仕入などの項目より、品目が増えるケースが多いので縦長の表になります。
・テストの一覧
⇒科目数の項目より、生徒の数が増えるケースが多いので横長の表になります。
要は表の形式によって使い分けるぐらいの認識で大丈夫です。
HLOOKUP関数に関しても、例題ファイルの中に入っているので確認してみてください。
使用目的
今回の表はあくまで数式説明なので20品目に満たないものでした。しかし実際には表自体がもっと膨大なデータが入っているケースは多々あります。たとえば、
- 住所録
- 商品一覧表
- 貸借対象表や損益計算書等の項目
私が例を挙げるよりも皆さんのほうが様々な使途が見えるかと思います。
例で挙げた住所録も100,200…などでは足りない場合もあります。
その中から探すのも大変ですし、行の見間違えなどのミスも起こりやすくなります。
あとは検索値の部分をドロップダウンリスト形式にすることで入力ミスの防止や、あいまいな記憶を補うことも可能になり結果としてより使い勝手が増します。
組み合わせでもっと便利になるのです。
また、私がSVとして勤めていたころに50項目のデータが毎週更新されて、全体の中でどの項目がどれぐらい売上・利益を稼いでいているのかトップ10のデータだけ拾うという事をしていました。
毎週続けるにあたり、自動化する仕組みにVLOOKUPやLARGE・SMALLといった関数も組み込むことで1時間の作業を5分も掛からないコピペだけで済むようになった事もあります。
注意事項
ここまでLOOKUP関数が万能のように話してきましたが、弱点があります。特に問題なのが『複数の条件一致』です。具体例でみましょう。
東京都出身のひとはAさんとEさんの2人います。しかしながら、
E2セルに
=vlookup(D2,A2:B7,2,false)
E3セルに
=vlookup(D2,A2:B7,2,false)
これで東京都出身の2人を検索しようとすると、Aさんが2回出てきてしまいます。これは仕様になっていて、簡単に言うと「条件に一致するもので一番上に来る解を選択」する仕様です。
これを解決するには、COUNTIF関数があれば解決できるようになるのでVLOOKUP関数を習得したら併せてご確認ください。
