脱初学者！Excel応用編【VLOOKUP関数】
まえがき
こんにちは、分析屋二代目業務効率化隊長の野口です。
今回はExcelのVLOOKUP関数の応用についての記事です。
「VLOOKUP関数は使ったことある」
「関数の組み合わせができるらしいけどよくわからない」
「わかってるつもりだけど復習しておきたい」
そんな方に向けて記事を書きました。
基礎から解説していくので、苦手を感じている方も是非チャレンジしてみてください！
おさらい
VLOOKUP関数とは
ExcelのVLOOKUP関数は、指定された範囲の中から特定の値を検索し、その値と同じ行にある別の列の値を返す関数です。VLOOKUP関数は4つの引数を使用します。
VLOOKUP(検索値, 範囲, 列番号, 検索の型)
検索値
探したい値です。この値は、範囲の最初の列にある必要があります。範囲
検索の対象となるセル範囲です。検索値はこの範囲の最初の列にある必要があります。列番号
範囲内で出力したい値がある列の番号を指定します。範囲の左側の列から順に1から数えます。検索の型
検索の方法を指定します。TRUEまたは省略すると近似一致、FALSEにすると完全一致を意味します。（ほとんどのケースでFALSEが使用されます）
応用その１
列番号を手入力せずに参照する方法
第3引数である列番号には範囲内の左から数えた時の数字を入れなければなりません。
本来なら数字を入力するところですが、MATCH関数を使う事で文字参照できる方法です。
参照元
やりたいこと
商品コードを参照して出力先のB2セル以降に商品名を出力したい
出力先
VLOOKUP(検索値, 範囲, 列番号, 検索の型) をそのまま使うと、
＝VLOOKUP($A2, ‘参照先’!$A:$C, 2, FALSE)となります。
ここに列番号の”2”をMATCH関数で代用します。
すると、
＝VLOOKUP($A2, ‘参照先’!$A:$C, MATCH(B1,’参照先’!$A$1:$C$1,0), FALSE)となります。
これで数字を手入力で入れなくても参照することが出来ます。MATCH関数を使用するメリットは以下の通りです！
参照元のシートの列順が変わっても対応できる
出力したい項目が増えてもコピーするだけで対応できる
注意点としては
列名を参照しているため、参照元と出力先の列名は同一名称にする
表形式でMATCH関数を使う場合は必ず表の一番左（もしくは一番上）から範囲指定する
が挙げられます。
応用その２
2個以上の項目を参照する方法
検索値を２つにする場合、VLOOKUP関数では対応できないため、先ほど使ったMATCH関数とINDEX関数の組合せで対応します。
参照元
やりたいこと
日付と時間帯（AM、PM）を参照して出力先のC2セル以降に担当者名を出力したい
出力先
先に答えを出すと、この様になります。
=INDEX(参照元!$A$1:$F$11,MATCH($A2&$B2,参照元!$A$1:$A$11&参照元!$B$1:$B$11,0),MATCH(C$1,参照元!$A$1:$F$1,0))
一つずつ解説していきます。まずは、INDEX関数の解説から。
INDEX関数は
① ＝INDEX(範囲, 行番号)
② ＝INDEX(範囲, 行番号, 列番号)
の二つの使い方があり、今回は②の方を使っています。
引数ごとに分けると、
-----------------------------------
範囲 参照元!$A$1:$F$11
行番号 MATCH($A2&$B2,参照元!$A$1:$A$11&参照元!$B$1:$B$11,0)
列番号 MATCH(C$1,参照元!$A$1:$F$1,0)
-----------------------------------
となります。
範囲はVLOOKUP関数同様、参照元の範囲を取ります。
列番号はC1セルの値（買出）を検索します。
参照元では「買出」は左から3番目であるため列番号は3となります。
そして複数条件を参照する行番号は第一引数と第二引数に、”＆”を使用します。
Excel関数における”＆”は文字を結合する働きを持っています。
この場合A2に2024年7月1日、B2にAMと入っているため、
第一引数の値は「2024年7月1日AM」となります。
第二引数は第一引数と同じ値（2024年7月1日AM）を検索して完全一致する番号を持ってきます。
参照元では「2024年7月1日AM」は上から2番目であるため行番号は2となります。
よって参照元A1：F11の上から２番目、左から3番目の値が参照される仕組みとなり、
=INDEX(参照元!$A$1:$F$11,MATCH($A2&$B2,参照元!$A$1:$A$11&参照元!$B$1:$B$11,0),MATCH(C$1,参照元!$A$1:$F$1,0))
で参照することができます。
まとめ
Excelを日々使っている方でも、改めて聞かれたら答えられる自信はないという方も多いのではないでしょうか？
複数の関数の組合せがスムーズにできれば、周りからの信頼度も上がるかと思います。
他にも解説してほしい関数等ありましたらコメントください！
ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！
株式会社分析屋について
弊社が作成を行いました分析レポートを、鎌倉市観光協会様HPに掲載いただきました。
ホームページはこちら。
noteでの会社紹介記事はこちら。
【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。 「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。