今回は、Excelのデータ分析のためにデータを整理するなどの際に利用するVLOOKUP関数の説明をします。
関数そのものの説明よりも実際にどのように活用するのか、また、練習できる演習講座も紹介しています。
VLOOKUP関数とは？
VLOOKUP関数の概要
VLOOKUP関数は、テーブルまたは範囲から、行ごとに値を検索する際に使用する関数です。
類似の機能を持つ関数に、HLOOKUP関数、XLOOKUP関数などがあります。
VLOOKUPは、Verticalすなわち縦方向に、HLOOKUPはHorizontalすなわち横方向に値を検索する関数です。
XLOOKUP関数は、新しく実装された関数で、VLOOKUPとXLOOKUPを兼ね備えた関数ですが、Excelのバージョンによっては使えないなどがあるので、まずはVLOOKUPを覚えるとよいでしょう。
VLOOKUP関数の使い方
関数の構文は、以下です。
＝VLOOKUP(検索値, 範囲, 列番号,検索方法）
検索値：
検索する値です。値や文字列、またセル参照も可能です。
範囲：
検索値を含むセル範囲を指定します。
検索値は範囲の最初の列に位置している必要があります。この点が不便なこともあり、INDEX関数とMATCH関数を組み合わせた「INDEX・MATCH」を代用することがあります。
列番号：
範囲の列番号を指定します。範囲の先頭行が1です。
ここで指定された列の値が表示されます。
セル参照も可能です。
検索方法：
完全一致する値を検索するか、近似値を検索するかを指定します。
完全一致の検索＝FALSE、近似値を含めて検索＝TRUE
基本的には、完全一致を指定しておくことがよいです。
使用方法については、Micrsoft社のサポートページに説明があるので、そちらも参照ください。
VLOOKUP関数の活用事例
活用のイメージ
データを分析などをする際に、いくつかのデーターベースなどから出力されたデータが、別々のシートに存在していることもあるでしょう。
例えば、複数のブックやシートに、社員の所属組織や、月収データ、アンケートへの回答などの情報があったとします。
詳細な説明は別の講座でまとめているので省きますが、バラバラのデータを一つのデータテーブル形式のマスターデータとすることで、データの集計ができるようになります。
これを、社員IDなど重複しない固有のコードを集計キー（VLOOKUP関数では検索値）としてマスターデータへと統合をしていきます。
検索値を基に、別々のシートにある所属組織の情報や、月収データ、アンケートへの回答結果などを一つのシートに集約することで、関数などを用いたデータ分析ができるようになります。
実際の関数の活用
概念を理解したところで実際に関数を活用してみましょう。
| 社員ID | 組織 | 従業員区分 |
|---|---|---|
| 1 | ||
| 2 | ||
| 3 | ||
| 4 | ||
| 5 |
| 社員ID | 組織 |
|---|---|
| 1 | 人事部 |
| 2 | 総務部 |
| 3 | 総務部 |
| 4 | 総務部 |
| 5 | 国内営業部 |
| 社員ID | 従業員区分 |
|---|---|
| 1 | 契約社員 |
| 2 | 正社員 |
| 3 | 契約社員 |
| 4 | 契約社員 |
| 5 | 契約社員 |
シートは「集計シート」と「所属組織」「従業員区分」に分かれています。
集計シートに、所属組織のシートから社員IDが「1」の人の組織情報を取得していきます。
C4セルに「＝VLOOKPU」と入力をして、VLOOKUP関数選択します。
検索値の入力
検索値は、社員IDの入力されているB4セルを選択します。
関数の入力完了後、C4セルの関数をC5セルから下の行へコピーするので、コピーした際に、参照しているセルがずれないように、列を絶対参照にします。
参照方式については以下の記事も参考にしてください。
範囲の設定
次に、範囲を設定します。
参照する範囲は、所属組織シートのデータとします。
関数を入力中に、他のシートをクリックすると、シートを移動することができますので、所属組織のシートに移動して範囲を選択します。
範囲は、C4セルの関数をC5セルから下の行へコピーするので、コピーした際に、参照しているセルがずれないように、絶対参照にします。
範囲が、所属組織シートの「$B$4:$D$104」となっていることが確認できると思います。
他のシートから参照した場合、セル番号の前に「シート名!」という名称がつきますので、こちらも覚えておきましょう。
列番号の入力
次に、列番号を入力します。
今回は、関数の中に直接数字を入れるのではなく、セルを参照する形にします。
関数の中に直接、列番号を入力すると、何列目を参照しているのかわかりにくく、メンテナンスもしづらいためです。
C2セルを参照させますが、C5以下にコピーをした際に、C2の参照がずれないように行を絶対参照にします。
検索方法の入力
最後に検索方法を入力します。
完全一致なので、FALSEを入力します。
エンターを押して、入力を完了します。
「#VALUE!」となりエラーが出ていますが、列番号の数値が入力されていないことが原因です。
C2セルに、検索する範囲の何列目の値を取得したいのか入力します。
記事中の例では3列目のデータを取得したいので、簡易な手法としては「3」と数字をベタ打ち入力をします。
あるいは、Excelの機能や関数を用いて列番号を指定することなどもできます。
例えば、テーブル化する、MATCH関数を用いたり、COLUMN関数で列番号を算出するなどができるでしょう。
いずれにせよ、関数の中に直接、列番号を入力すると、何列目を参照しているのかわかりにくく、メンテナンスもしづらいため、セル参照とする方がよいでしょう。
隣の列への関数のコピー
同じく、従業員区分を取得するために、C4セルをコピーして、D4セルに張り付けます。
検索値と列番号は、それぞれ列と行を絶対参照しているので、コピーしても正しく参照箇所が選択されています。
そのため、参照する範囲と列番号の数値を入力することで完了です。
慣れないうちは、ゼロから入力するとよいですが、行を絶対参照、列を絶対参照することで、コピペにより作業速度が格段にあがります。
残りは、取得したい分のデータまで入力をして、コピペをすれば完了です。
実際の関数の活用例 住所の取得
顧客データの管理などで、郵便番号を入力した際に住所を取得するシートを作成してみました。
練習できる演習問題の案内
ここまで解説をしてきた元データは、UdemyにてExcelによるデータ集計講座を演習問題形式で配信しており、それを使っています。
クーポンコードも用意しておりますので、こちらをお使いください。
5A1A768C07F0CD5F2B10
データ集計時にVLOOKUP関数などを活用しており、実際にどのようにデータ集計をしていくのか興味のある方は、ぜひご覧ください。
関連記事
Excelを独学するための書籍
筆者がこれまで読んできて役に立ったと思う書籍もまとめていますので、併せてご覧ください。
汎用的スキルとしてのExcel
汎用的スキルとしてのPowerPoint
PowerPointの学習講座もありますので、興味のある方はぜひご覧ください
コメント