今回のまとめ
・VLOOKUP関数は、「①検索値、②範囲、③列番号、④検索の型」という４つの「引数」を設定する必要がある。・VLOOKUP関数では、「見つけ出したい項目」は必ず引数①「検索値」と「突合される列を含む右側」に引数②「範囲」を設定する必要がある。
・今後はVLOOKUP関数とHLOOKUP関数の両方の機能を併せ持つXLOOKUP関数が主流となるだろう。
【第2回】監査や経理担当者が知っておくべき！『モダンExcel』データ分析超入門（全10回）
第2回 レガシーエクセルによる基本的な「データ突合」
・VLOOKUP関数では、「見つけ出したい項目」は必ず引数①「検索値」と「突合される列を含む右側」に引数②「範囲」を設定する必要がある。
・今後はVLOOKUP関数とHLOOKUP関数の両方の機能を併せ持つXLOOKUP関数が主流となるだろう。
監査技術の一つに「突合」があります。「突合」とは、ＡとＢを突き合わせ、正確な記録が行われているか等を確かめる、基本的な監査技術です。
たとえば、実地棚卸の結果と在庫在高帳を「突合」して、不一致が判明すれば、商品や製品などの棚卸資産が横領されている可能性があることなどを把握できます。言い換えれば、あるべき「入・出・残」から異常点を把握する必要があるわけです。
図1 あるべき「入・出・残」から考える、これがデータ分析の基本。
（拙著『よくわかる「自治体監査」の実務入門』（日本実業出版社）より転載）
この「突合」が、データ分析の基本動作の一つです。データ分析の現場では「データ突合」として活用されます。
なお、数字は「P×Q」、つまり「Price（単価）」と「Quantity（数量）」に分けて考えると良いでしょう。詳細は、拙著『CAATで粉飾・横領はこう見抜く』（中央経済社）などに委ねます。
■伝統的な「データ突合」の技法として用いられる「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
- 執筆者プロフィール
-
村井 直志（むらい ただし）
公認会計士 / データアナリスト日本公認会計士協会東京会コンピュータ委員会委員長、経営・税務・業務各委員会委員、第三者委員会委員などを歴任。研究テーマ『CAAT（コンピュータ利用監査技法）で不正会計に対処する、エクセルを用いた異常点監査技法』で日本公認会計士協会研究大会に選抜。
- 著書
-
『3000社の決算を分析してきた会計士が教える 経営を強くする 会計7つのルール』（ダイヤモンド社）
『Excelによる不正発見法 CAATで粉飾・横領はこう見抜く』（中央経済社）
『会計ドレッシング10episodes』（東洋経済新報社）
『モダンExcel入門 データ分析＆可視化の新しい教科書』（日経BP）など
