【第2回】監査や経理担当者が知っておくべき！『モダンExcel』データ分析超入門（全10回）

■伝統的な「データ突合」の技法として用いられる「VLOOKUP関数」

経営管理業務で役立つレガシーエクセルの機能のひとつが、 VLOOKUP（ブイルックアップ）関数 というデータ検索の関数です。

VLOOKUP関数は、 「縦方向/垂直」（Vertical）に、検索（Look Up）する「関数」で、条件に合致したデータを抽出 できます。これは、CAAT（Computer Assisted Audit Techniques、コンピュータ利用監査技法）により異常点をつかむ基本の一つでもあります。

図2のように、Ｂ列に「得意先コード」はあっても「得意先名」がない場合、それぞれのデータ項目を持つ「得意先マスタ」のような別のデータから該当データを探し出し、Ｄ列に「得意先名」を表示できるようにするのがVLOOKUP関数です。

VLOOKUP関数では、「検索値、範囲、列番号、検索の型」という４つの「引数（ひきすう）」を設定する必要があります。

図2をご覧ください。「得意先コード」からVLOOKUP関数で紐づけ、「得意先名」が表示できるようにするには、セルD2「=VLOOKUP（①B2,②得意先マスタ!A:B,③2,④FALSE）」のように、①から④の４項目を「引数」として指定する必要があります。

• 「得意先コード」が記載された「検索値」セル（引数①「B2」）を参照します。
• これと同じものを「範囲」として指定したデータ項目の中から選び出します（引数②「得意先マスタ!A:B」）。
• 引数①「検索値」が突合するデータを、必ず引数②「範囲」の一番左の列（「得意先マスタ」のＡ列）に設定するのがポイントです。引数②「範囲」の中から引数①「検索値」と同じものを見つけ引数③「列番号」で指定された列（「2」列目）のデータ項目を、“見つけ出したい項目”（「得意先マスタ」のA列から「２」列目のＢ列）として返します。
• 検索方法には〔近似一致（TRUE）〕と〔完全一致（FALSE）〕の２種類ありますが、財務会計データの場合、検索の型は「完全一致」（引数④「FALSE」）とします。

図2 VLOOKUP関数のしくみ
（拙著『よくわかる「自治体監査」の実務入門』（日本実業出版社）に一部加筆）

■VLOOKUP関数を使うには、並べ替えした方が良い

VLOOKUP関数を適用するには、対象となる項目（ここでは「得意先コード」があるＢ列）を昇順に「並べ替え（ソートともいいます）」、連続データにする方が良いでしょう。

並べ替えをせずともVLOOKUP関数の引数④を「FALSE（完全一致）」とすれば結果は得られます。

ただし、並べ替えをしない場合、 検索に時間がかかる ことがあります。たかだか数千行程度の「ごく小さなデータ」であっても、パソコンのスペック（性能）次第では、相当難儀することもあります。したがって、VLOOKUP関数では、並べ替えも考慮して欲しいと思います。

データの並べ替え（ソート）をすると元データがバラバラになってしまうので、工夫も必要になります。元のシートをコピーするか、任意の列（作業列と呼ぶことがあります）にあらかじめ「連番（インデックス）」を付すと元データを再現でき便利です。

■VLOOKUP＋HLOOKUP＝XLOOKUP

エクセルには HLOOKUP関数 という「横方向/水平」（Horizontal）に検索（Look Up）する関数もあります。

ただし、財務会計データは「1行１列1項目のきれいなデータ」を対象にすることが多いので、このHLOOKUP関数を使う場面は限られます。したがって、今回ご紹介したVLOOKUP関数を含め、エクセル初学者は、第1回でご紹介した程度の関数や機能を理解されると良いでしょう。

ちなみに、サブスクリプション「Office365」とパッケージ版「Excel2021」以降には、「 XLOOKUP 」という新しい関数が追加されています。

XLOOKUP関数は、VLOOKUP関数とHLOOKUP関数の両方の機能を併せ持つ大変便利な関数で、操作も比較的簡単なので、 今後このXLOOKUP関数が主流 になってくるでしょう。

VLOOKUP関数、HLOOKUP関数、XLOOKUP関数、これらを「三位一体」で理解することを促す、幣ブログ後掲のリンク先「 XLOOKUP 関数（VLOOKUP関数、HLOOKUP関数、合わせて理解！） 」も参考にしてください。

■VLOOKUP関数「最大の弱点」

上述のとおり、VLOOKUP関数では、引数①「検索値」が突合するデータを必ず引数②「範囲」の一番左の列（事例では「得意先マスタ」のＡ列）に設定するのがポイントでした。

これが、VLOOKUP関数「最大の弱点」でもあります。

仮に元データで、検索値（引数①）の左側に「見つけ出したい項目」「跳ね返したいデータ」（引数②の「範囲」並びに引数③の「列番号」の組み合わせ）がある場合、「作業列」というVLOOKUP関数を動作させるため「だけ」の列をわざわざ作成する必要が生じます。

これにより、「1列」以上の「作業のためだけの列」をワークシート上に設けなければならず、その分だけデータが増え、結果としてデータ処理に負荷がかかり、フリーズ（パソコンが固まってしまう現象）などの原因にもなってしまいます。

こうしたVLOOKUP関数の弱点を克服するため、INDEX関数とMATCH関数を組み合わせVLOOKUP関数の代替とする方法なども、エクセルに詳しい人によって編み出されています。次回ご紹介します。

（参考資料等）
拙著『CAATで粉飾・横領はこう見抜く』（中央経済社）
https://amzn.to/3ATjyx2

幣ブログ「モダンExcel研究所」
「XLOOKUP 関数（VLOOKUP関数、HLOOKUP関数、合わせて理解！）」
https://excel.value.or.jp/?p=475

関連リンク