ExcelのVLOOKUP関数の使い方|練習できる講座も紹介

Excel

今回は、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関数の活用事例

活用のイメージ

データを分析などをする際に、いくつかのデーターベースなどから出力されたデータが、別々のシートに存在していることもあるでしょう。

例えば、複数のブックやシートに、社員の所属組織や、月収データ、アンケートへの回答などの情報があったとします。

出所:yuru-unagi.com作成

詳細な説明は別の講座でまとめているので省きますが、バラバラのデータを一つのデータテーブル形式のマスターデータとすることで、データの集計ができるようになります。

これを、社員IDなど重複しない固有のコードを集計キー(VLOOKUP関数では検索値)としてマスターデータへと統合をしていきます。

出所:yuru-unagi.com作成

検索値を基に、別々のシートにある所属組織の情報や、月収データ、アンケートへの回答結果などを一つのシートに集約することで、関数などを用いたデータ分析ができるようになります。

実際の関数の活用

概念を理解したところで実際に関数を活用してみましょう。

社員ID組織従業員区分
1
2
3
4
5
集計用テーブル
社員ID組織
1人事部
2総務部
3総務部
4総務部
5国内営業部
表1 社員IDと所属組織
社員ID従業員区分
1契約社員
2正社員
3契約社員
4契約社員
5契約社員
表2 社員IDと従業員区分

シートは「集計シート」と「所属組織」「従業員区分」に分かれています。

出所:Excelにてyuru-unagi.com作成

集計シートに、所属組織のシートから社員IDが「1」の人の組織情報を取得していきます。

出所:Excelにてyuru-unagi.com作成

C4セルに「=VLOOKPU」と入力をして、VLOOKUP関数選択します。

出所:Excelにてyuru-unagi.com作成

検索値の入力

検索値は、社員IDの入力されているB4セルを選択します。

出所:Excelにてyuru-unagi.com作成

関数の入力完了後、C4セルの関数をC5セルから下の行へコピーするので、コピーした際に、参照しているセルがずれないように、列を絶対参照にします。

出所:Excelにてyuru-unagi.com作成

参照方式については以下の記事も参考にしてください。

範囲の設定

次に、範囲を設定します。

参照する範囲は、所属組織シートのデータとします。

関数を入力中に、他のシートをクリックすると、シートを移動することができますので、所属組織のシートに移動して範囲を選択します。

出所:Excelにてyuru-unagi.com作成

範囲は、C4セルの関数をC5セルから下の行へコピーするので、コピーした際に、参照しているセルがずれないように、絶対参照にします。

出所:Excelにてyuru-unagi.com作成

範囲が、所属組織シートの「$B$4:$D$104」となっていることが確認できると思います。

他のシートから参照した場合、セル番号の前に「シート名!」という名称がつきますので、こちらも覚えておきましょう。

列番号の入力

次に、列番号を入力します。

今回は、関数の中に直接数字を入れるのではなく、セルを参照する形にします。

関数の中に直接、列番号を入力すると、何列目を参照しているのかわかりにくく、メンテナンスもしづらいためです。

C2セルを参照させますが、C5以下にコピーをした際に、C2の参照がずれないように行を絶対参照にします。

出所:Excelにてyuru-unagi.com作成

検索方法の入力

最後に検索方法を入力します。

完全一致なので、FALSEを入力します。

出所:Excelにてyuru-unagi.com作成

エンターを押して、入力を完了します。

出所:Excelにてyuru-unagi.com作成

「#VALUE!」となりエラーが出ていますが、列番号の数値が入力されていないことが原因です。

C2セルに、検索する範囲の何列目の値を取得したいのか入力します。

記事中の例では3列目のデータを取得したいので、簡易な手法としては「3」と数字をベタ打ち入力をします。

出所:Excelにてyuru-unagi.com作成

あるいは、Excelの機能や関数を用いて列番号を指定することなどもできます。

例えば、テーブル化する、MATCH関数を用いたり、COLUMN関数で列番号を算出するなどができるでしょう。

いずれにせよ、関数の中に直接、列番号を入力すると、何列目を参照しているのかわかりにくく、メンテナンスもしづらいため、セル参照とする方がよいでしょう。

隣の列への関数のコピー

同じく、従業員区分を取得するために、C4セルをコピーして、D4セルに張り付けます。

出所:Excelにてyuru-unagi.com作成

検索値と列番号は、それぞれ列と行を絶対参照しているので、コピーしても正しく参照箇所が選択されています。

そのため、参照する範囲と列番号の数値を入力することで完了です。

慣れないうちは、ゼロから入力するとよいですが、行を絶対参照、列を絶対参照することで、コピペにより作業速度が格段にあがります。

残りは、取得したい分のデータまで入力をして、コピペをすれば完了です。

出所:Excelにてyuru-unagi.com作成

実際の関数の活用例 住所の取得

顧客データの管理などで、郵便番号を入力した際に住所を取得するシートを作成してみました。

練習できる演習問題の案内

ここまで解説をしてきた元データは、UdemyにてExcelによるデータ集計講座を演習問題形式で配信しており、それを使っています。

データ集計時にVLOOKUP関数などを活用しており、実際にどのようにデータ集計をしていくのか興味のある方は、ぜひご覧ください。

関連記事

Excelを独学するための書籍

筆者がこれまで読んできて役に立ったと思う書籍もまとめていますので、併せてご覧ください。

汎用的スキルとしてのExcel

汎用的スキルとしてのPowerPoint

PowerPointの学習講座もありますので、興味のある方はぜひご覧ください

学びに関する情報の全体像

参考文献

VLOOKUP 関数
https://support.microsoft.com/ja-jp/office/vlookup-%E9%96%A2%E6%95%B0-0bbc8083-26fe-4963-8ab8-93a18ad188a1?ns=excel&version=21&syslcid=1041&uilcid=1041&appver=zxl210&helpid=xlmain11.chm60150&ui=ja-jp&rs=ja-jp&ad=jp

コメント

タイトルとURLをコピーしました