- 作成日 : 2024年11月20日
VLOOKUP関数で突合する方法は？活用例や手順をわかりやすく解説
業務において、エクセルを利用してデータの抽出や比較をする機会はよくあります。エクセルで非常によく使われる関数として挙げられるのが、VLOOKUP関数です。VLOOKUP関数は、大量のデータから特定の情報を検索して、抽出するのに適しています。この記事では、エクセルのVLOOKUP関数について実践的な解説を行います。
目次
エクセルの突合とは
「突合する」とは、複数のデータを比較し、一致や不一致を特定する作業のことです。エクセルを利用した業務において、突合処理は頻繁に行われる作業の一つです。一般に、突合の目的には次のようなものがあり、いずれもデータの正確性を向上させるものと言えます。
- 取引の正確性の確認
- 数値の整合性チェック
- 不正や誤りの抽出
- データの完全性の検証
例えば、注文書、納品書、請求書の項目について、通常は整合を行います。これらを別々に作成した場合には、データを突合させて一致を確認することがあります。また、システム間で一致すべきデータを突合して確かめるケースも考えられるでしょう。
さらにシステムに手修正を加えたような場合には、修正前後のデータを突合して、修正箇所のみが変更されているかを検証することもあります。
照合との違い
以前は銀行などで「印鑑照合」している光景をよく見かけました。持参した印鑑が登録している印鑑と一致しているかどうかを、目視で確認する作業です。
このように照合とは、主として1対1の確認が中心となる確認作業です。これに対して突合は、抽出や差異確認を含み、より多くの観点から正確性を検証する作業と言えます。
VLOOKUP関数でデータを突合する方法
エクセル関数の1つであるVLOOKUP関数は、縦方向（Vertical）に表データを検索し、指定した値と一致する行から特定の列にあるデータを返す関数です。別途、横方向（Horizontal）に表データを検索するHLOOKUP関数もあります。
データベースをエクセルで見た場合、通常、1行1レコードの形式で設計されます。したがって、レコードの検索には横より縦の検索が向いているため、VLOOKUP関数のほうがよく利用されています。
エクセル関数の動作を具体的に指示するための情報を「引数（ひきすう）」と言います。
VLOOKUP関数には次のように4つの引数があります。
【VLOOKUP関数の使い方】
セルに入れる関数 = VLOOKUP(①検索したい値, ②検索の範囲, ③範囲の中の列番号,④検索方法) |
|---|
① 検索したい値
探したい値を指定します。多くはセルを参照して指定します。
② 検索の範囲
検索の対象となる範囲を指定します。他のシートやブックでも可能です。
検索範囲の一番左端の列に検索値があることがVLOOKUP関数では重要です。
③ 検索の範囲の中の列番号
②の検索範囲内の左から何番目に返したい値が含まれているのかを指定します。
④ 検索方法
検索の方法を指定します。TUREまたは1（近似一致）、FALSEまたは0（完全一致）を 入力します。ただし、省略が可能です。
この中で入力が任意となる④の検索方法について補足します。
- TRUEとした場合
検索値に一致するデータが検索範囲になければ、検索値未満の最も大きい値を返します。
- FALSEとした場合
検索値と完全一致するデータを返し、一致しなければエラー値（#N/A）を返します。
- 省略した場合
TRUEが指定されたことになりますが、③の引数の後ろにカンマが残っていた場合には、FALSEが指定されたことになります。
【VLOOKUP関数の突合具体例】
顧客リストの中から、購入回数10回以上の人にお礼状を出すことにしました。お礼状を出す人の住所と名前を抽出・検索したい。顧客コードで検索するものとし、これらのリストは同じエクセルブック内にあるものとします。
顧客リスト お礼状リスト
お礼状リストのC2セルに下記のように入力し、下方向にコピーします。
顧客コードは検索範囲である顧客リストの一番左側にあり、VLOOKUP関数が使えます。
| C2に入れる関数 = VLOOKUP(B2,顧客リスト!$A$2:$D$14,2,FALSE) |
|---|
注意点として、コピーしても検索範囲が変らないように検索範囲には絶対セル指定にすることと、氏名を抽出する場合は検索範囲内の左から何列目かを確認することです。
同様にして、年齢や住所も関数を使ってお礼状リストに表示します。
VLOOKUP関数によるデータ突合を会計処理にどう利用する？
VLOOKUP関数について、具体的にはどのように会計処理に利用するかを考えてみます。
会計ソフトを利用していると想定した場合、会計ソフトから得られる帳票やレポート等は最大限に利用しましょう。その上で、データを取得して会計処理に役立つ資料を考えてみてください。
一般にエクセルで独自の資料が必要となるのは、手作業が関係する場合や、現システムにフォーマットがない場合などです。以下に、いくつかの例を挙げてみます。
- 仕訳入力時のコード一覧表作成
会計ソフトでは、一般に勘定科目にコードが割り当てられています。仕訳入力や帳簿作成時にそれらのコードがわかる一覧表があれば便利です。
会計ソフトから勘定科目マスターのデータをエクスポートします。VLOOKUP関数を使用して勘定科目コードから勘定科目名を自動的に取得して、勘定科目コードの一覧表を作ることができます。
- 別フォーマットによる損益管理表作成
一般に、会計ソフトでは仕訳時に部門コードを入力していれば、部門別の損益計算書が得られます。しかし、思うような部門損益の管理表が得られないとき、独自のフォーマットを作成できます。
部門コードが付された損益計算書をエクスポートし、VLOOKUP関数により部門コードに対応する各損益項目を表示させ、目的のフォーマットを得ることができます。
- 固定資産棚卸のための実査フォーマット作成
基本的に、固定資産の現物と固定資産台帳は一致すべきです。決算準備の一環として、固定資産の棚卸をするための書式を必要とする場合があります。
管理ソフトなどから固定資産データをエクスポートして、棚卸のためのフォーマットを作成します。その際に、他のデータからVLOOKUP関数により実査に必要な情報を付加できます。
VLOOKUP関数で複数条件を設定する方法
見てきたように、VLOOKUP関数における検索値は1つです。検索値が複数ある場合には、どのようにしたらよいでしょうか？1つの方法として、VLOOKUP関数で、検索値を「&（アンパサンド）」で結合して利用できます。
（例）
下のように「&」でいくつかの検索値をつなげることができます。ただし、この場合は検索範囲内には連結後の検索値が含まれている必要があります。
| VLOOKUP(検索値1&検索値2, 検索範囲, 列番号, FALSE) |
|---|
下は、顧客コードの下2桁のみが入ったリストです。この場合、顧客リストからVLOOKUP関数で氏名や年齢を抜き出す場合には、「&」を使います。
顧客リスト 抽出リスト
抽出リストのB2には、1番目の引数に固定値として”C0000”を付けるという意味で「”C0000″&A2」とします。結果は次のとおりです。
VLOOKUP関数で別ファイルを参照する方法
別のエクセルファイル内に検索範囲があった場合のVLOOKUP関数を考えてみましょう。
この場合は、特に意識しなくても別ファイルの検索範囲をVLOOKUP関数内で指定したときに、そのファイルのある場所を表示してくれます。しかしながら、検索範囲がファイルの中の中などネストが深いと関数表記が長くなり、非常に理解しづらくなります。
同様の例において、顧客マスターとなるリストが別のエクセルファイルとなった場合には次のような関数の記述になります。当ファイルとは別の「顧客マスター2024」というブック内の「最新リスト」内に検索範囲があるケースです。
理論上、検索範囲がクラウド上にあっても検索範囲の設定は可能ですが、エクセルの動作が重くなるなどの影響も想定されます。
XLOOKUP関数とは？
XLOOKUP関数は、VLOOKUP関数の後継の関数で、より柔軟で強力な検索が可能です。Vは、縦方向（Vertical）という意味でしたが、Xは拡張（eXtended）という意味で、指定された検索範囲で、特例の列のデータを検索し、その値に対応するデータを取得します。
しかしながら、XLOOKUP関数は新たに実装された機能であるため、Excel 2016やExcel 2019などでは利用できません。
VLOOKUP関数との違い
VLOOKUP関数においては、検索範囲の一番左端の列に検索値があることが条件でしたが、XLOOKUP関数は、任意の列から検索できるようになりました。さらに、1つの関数で複数の列の値を返せるほか、下から上、右から左への検索も可能となりました。
加えて、検索値が見つからなかった場合の処理も組み込まれており、IFERROR関数等との組み合わせが不要になりました。
基本式
XLOOKUP関数では引数の数は増え、全部で6つになりました。
| =XLOOKUP（①検索値,②検索範囲,③戻り範囲,④見つからない場合, ⑤一致モード,⑥検索モード） |
|---|
①〜③については引数が必須ですが、④〜⑥の引数も指定することで、より詳細な検索が可能となります。
① 検索値
考え方はVLOOKUP関数と同じです。「&」でつなげることも可能です。
② 検索範囲
検索値を含む列が一番左端にある必要はなく、検索範囲として1列を指定します。
検索範囲に複数の列も「&」でつなげて指定することができます。
③ 戻り範囲
どの列を返すのか列を指定します。
ただし、②の検索範囲と③の戻り範囲の高さ（縦に検索する場合）または幅（横に検索 する場合）を揃えておく必要があります。
④ 見つからない場合（省略可）
検索値が見つからなかったときに表示するメッセージを指定できます。
例えば、”データなし”などと指定することが可能です。
⑤ 一致モード（省略可）
VLOOKUP関数における検索方法と同じです。TUREやFALSEについても同じです。
⑥ 検索モード（省略可）
検索する順序を指定します。引数の考え方は⑤と同じです。
省略した場合は、先頭→末尾の順となります。
引数が多くて複雑に見えますが、セルや数式バー入力の際は、入力補助が表示されるため、次に何を入れるかがわかりやすくなっています。下の図は最後の引数をFALSEとしており、下から検索するように指定した例です。
XLOOKUP関数が利用できる環境にあれば、VLOOKUP関数ではなく、XLOOKUP関数を使うほうが拡張性があるため便利でしょう。
活用例
XLOOKUP関数では複雑な処理も簡単にこなせます。会計処理においても、より柔軟なデータ検索や突合が可能になり、特に複数のマスターデータとの連携や、条件に基づいた検索が必要な場面で威力を発揮します。
会計ソフト等からのエクスポートデータを使って、「小回りの利く」レポートが作成できます。例えば次のように種々のものが考えられます。
- データが別々にある場合の各種管理リスト
- 特定の項目や期間を対象にした売上高や費用の分析表
- 仕入先ごとの詳細な支払予定表
- 全社または部門別の予算と実績の対比表
VLOOKUP関数を使いこなして、シンプルな管理を！
1つの帳票や管理表であれもこれも管理するのではなく、目的別に管理表やレポートを使い分けて管理方法をシンプルにするのも業務効率化の1つです。そのためには、VLOOKUP関数やXLOOKUP関数をスムーズに使えるようにしておきたいものです。
多くの関数を組み合わせるより、できるだけシンプルに、他の人が見てもすぐ理解が得られるようなエクセル関数ユーザーになることをおすすめします。
※ 掲載している情報は記事更新時点のものです。
会計の知識をさらに深めるなら
※本サイトは、法律的またはその他のアドバイスの提供を目的としたものではありません。当社は本サイトの記載内容（テンプレートを含む）の正確性、妥当性の確保に努めておりますが、ご利用にあたっては、個別の事情を適宜専門家にご相談いただくなど、ご自身の判断でご利用ください。
関連記事
インボイス制度で小口精算はどう変わる？立替経費の精算方法や効率化のコツについて解説
インボイス制度の導入により、小口精算手続きや会計処理の複雑化が懸念されます。特に、インボイス制度ではいくつかの特例が設けられており、経営者や経理担当者に加え、経費を取り扱う従業員の理解も必要不可欠です。本記事では、インボイス制度による小口精…
詳しくみる割引手当とは？種類や不渡手形になるまでのプロセスを解説
販売代金を現金ではなく約束手形で受け取った場合に「受取手形」で仕訳を起こすことは良く知られていますが、「割引手形」や「裏書手形」、「不渡手形」がどういった種類の手形であるのか意外と知らない人が多いのではないでしょうか？ ここでは、 ・割引手…
詳しくみる立替経費精算を解説！改正電子帳簿保存法への対応まで
会社が支払うべき経費を従業員が立て替えた経費を立替経費といいます。国税関係の帳簿書類の電子保存を認める法律に電子帳簿保存法がありますが、立替経費に関わる書類も電子帳簿保存法の対象となるのでしょうか。 この記事では、立替経費の概要を踏まえ、立…
詳しくみる入金消込をエクセルで自動化！関数・マクロの活用やポイント解説
売掛金や未収入金の照合や消込を自社のエクセルのテンプレートで行っている会社も少なくありませんが、その作業には時間と労力がかかります。今回はエクセルの関数・マクロを使って債権の入金の照合・消込を行う方法についてご紹介します。入金の消込処理の自…
詳しくみる売上債権とは？種類や管理・回収方法、売上債権回転期間と回転率の計算方法も解説
企業間の取引においてしばしば登場するのが「掛取引」「手形取引」です。信用に基づき代金の決済を繰り延べる取引形態ですが、信用取引により発生した売上にかかる債権のことを「売上債権」と呼びます。 本記事では「売上債権」の種類や管理方法、回収方法に…
詳しくみる債権回収の流れは？支払督促や強制執行の方法、注意すべきポイントを解説
債権とは、特定の相手に対し、特定の行為や給付を請求できる権利のことです。分かりやすくいえば、商品やサービスを販売する側が代金を請求できる権利、買う側が商品やサービスを受け取る権利のことですが、約束通りに受け取れない場合は「債権の回収」を行う…
詳しくみる