無料で試せるクラウド会計ソフト

新規登録
  1. クラウド会計ソフト「マネーフォワード クラウド会計」
  2. 会計の基礎知識
  3. VLOOKUP関数で突合する方法は？活用例や手順をわかりやすく解説
  • 作成日 : 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回以上の人にお礼状を出すことにしました。お礼状を出す人の住所と名前を抽出・検索したい。顧客コードで検索するものとし、これらのリストは同じエクセルブック内にあるものとします。

顧客リスト                                                                                              お礼状リスト

VLOOKUP関数の突合具体例1

お礼状リストのC2セルに下記のように入力し、下方向にコピーします。

顧客コードは検索範囲である顧客リストの一番左側にあり、VLOOKUP関数が使えます。

C2に入れる関数　=　VLOOKUP(B2,顧客リスト!$A$2:$D$14,2,FALSE)

注意点として、コピーしても検索範囲が変らないように検索範囲には絶対セル指定にすることと、氏名を抽出する場合は検索範囲内の左から何列目かを確認することです。

VLOOKUP関数の突合具体例2

同様にして、年齢や住所も関数を使ってお礼状リストに表示します。

VLOOKUP関数の突合具体例3

VLOOKUP関数によるデータ突合を会計処理にどう利用する？

VLOOKUP関数について、具体的にはどのように会計処理に利用するかを考えてみます。

会計ソフトを利用していると想定した場合、会計ソフトから得られる帳票やレポート等は最大限に利用しましょう。その上で、データを取得して会計処理に役立つ資料を考えてみてください。

一般にエクセルで独自の資料が必要となるのは、手作業が関係する場合や、現システムにフォーマットがない場合などです。以下に、いくつかの例を挙げてみます。

  • 仕訳入力時のコード一覧表作成

会計ソフトでは、一般に勘定科目にコードが割り当てられています。仕訳入力や帳簿作成時にそれらのコードがわかる一覧表があれば便利です。

会計ソフトから勘定科目マスターのデータをエクスポートします。VLOOKUP関数を使用して勘定科目コードから勘定科目名を自動的に取得して、勘定科目コードの一覧表を作ることができます。

  • 別フォーマットによる損益管理表作成

一般に、会計ソフトでは仕訳時に部門コードを入力していれば、部門別の損益計算書が得られます。しかし、思うような部門損益の管理表が得られないとき、独自のフォーマットを作成できます。

部門コードが付された損益計算書をエクスポートし、VLOOKUP関数により部門コードに対応する各損益項目を表示させ、目的のフォーマットを得ることができます。

  • 固定資産棚卸のための実査フォーマット作成

基本的に、固定資産の現物と固定資産台帳は一致すべきです。決算準備の一環として、固定資産の棚卸をするための書式を必要とする場合があります。

管理ソフトなどから固定資産データをエクスポートして、棚卸のためのフォーマットを作成します。その際に、他のデータからVLOOKUP関数により実査に必要な情報を付加できます。

VLOOKUP関数で複数条件を設定する方法

見てきたように、VLOOKUP関数における検索値は1つです。検索値が複数ある場合には、どのようにしたらよいでしょうか？1つの方法として、VLOOKUP関数で、検索値を「&（アンパサンド）」で結合して利用できます。

（例）

下のように「&」でいくつかの検索値をつなげることができます。ただし、この場合は検索範囲内には連結後の検索値が含まれている必要があります。

VLOOKUP(検索値1&検索値2, 検索範囲, 列番号, FALSE)

下は、顧客コードの下2桁のみが入ったリストです。この場合、顧客リストからVLOOKUP関数で氏名や年齢を抜き出す場合には、「&」を使います。

顧客リスト                                                                                                                     抽出リスト

VLOOKUP関数の突合具体例4

抽出リストのB2には、1番目の引数に固定値として”C0000”を付けるという意味で「”C0000″&A2」とします。結果は次のとおりです。

VLOOKUP関数の突合具体例5

VLOOKUP関数で別ファイルを参照する方法

別のエクセルファイル内に検索範囲があった場合のVLOOKUP関数を考えてみましょう。

この場合は、特に意識しなくても別ファイルの検索範囲をVLOOKUP関数内で指定したときに、そのファイルのある場所を表示してくれます。しかしながら、検索範囲がファイルの中の中などネストが深いと関数表記が長くなり、非常に理解しづらくなります。

同様の例において、顧客マスターとなるリストが別のエクセルファイルとなった場合には次のような関数の記述になります。当ファイルとは別の「顧客マスター2024」というブック内の「最新リスト」内に検索範囲があるケースです。

VLOOKUP関数の突合具体例6

理論上、検索範囲がクラウド上にあっても検索範囲の設定は可能ですが、エクセルの動作が重くなるなどの影響も想定されます。

XLOOKUP関数とは？

XLOOKUP関数は、VLOOKUP関数の後継の関数で、より柔軟で強力な検索が可能です。Vは、縦方向（Vertical）という意味でしたが、Xは拡張（eXtended）という意味で、指定された検索範囲で、特例の列のデータを検索し、その値に対応するデータを取得します。

しかしながら、XLOOKUP関数は新たに実装された機能であるため、Excel 2016やExcel 2019などでは利用できません。

参考：XLOOKUP 関数 ｜Microsoft

VLOOKUP関数との違い

VLOOKUP関数においては、検索範囲の一番左端の列に検索値があることが条件でしたが、XLOOKUP関数は、任意の列から検索できるようになりました。さらに、1つの関数で複数の列の値を返せるほか、下から上、右から左への検索も可能となりました。

加えて、検索値が見つからなかった場合の処理も組み込まれており、IFERROR関数等との組み合わせが不要になりました。

基本式

XLOOKUP関数では引数の数は増え、全部で6つになりました。

=XLOOKUP（①検索値,②検索範囲,③戻り範囲,④見つからない場合,

⑤一致モード,⑥検索モード）

①〜③については引数が必須ですが、④〜⑥の引数も指定することで、より詳細な検索が可能となります。

①　検索値

考え方はVLOOKUP関数と同じです。「&」でつなげることも可能です。

②　検索範囲

検索値を含む列が一番左端にある必要はなく、検索範囲として1列を指定します。

検索範囲に複数の列も「&」でつなげて指定することができます。

③　戻り範囲

どの列を返すのか列を指定します。

ただし、②の検索範囲と③の戻り範囲の高さ（縦に検索する場合）または幅（横に検索　　する場合）を揃えておく必要があります。

④　見つからない場合（省略可）

検索値が見つからなかったときに表示するメッセージを指定できます。

例えば、”データなし”などと指定することが可能です。

⑤　一致モード（省略可）

VLOOKUP関数における検索方法と同じです。TUREやFALSEについても同じです。

⑥　検索モード（省略可）

検索する順序を指定します。引数の考え方は⑤と同じです。

省略した場合は、先頭→末尾の順となります。

引数が多くて複雑に見えますが、セルや数式バー入力の際は、入力補助が表示されるため、次に何を入れるかがわかりやすくなっています。下の図は最後の引数をFALSEとしており、下から検索するように指定した例です。

XLOOKUP関数

XLOOKUP関数が利用できる環境にあれば、VLOOKUP関数ではなく、XLOOKUP関数を使うほうが拡張性があるため便利でしょう。

活用例

XLOOKUP関数では複雑な処理も簡単にこなせます。会計処理においても、より柔軟なデータ検索や突合が可能になり、特に複数のマスターデータとの連携や、条件に基づいた検索が必要な場面で威力を発揮します。

会計ソフト等からのエクスポートデータを使って、「小回りの利く」レポートが作成できます。例えば次のように種々のものが考えられます。

  • データが別々にある場合の各種管理リスト
  • 特定の項目や期間を対象にした売上高や費用の分析表
  • 仕入先ごとの詳細な支払予定表
  • 全社または部門別の予算と実績の対比表

VLOOKUP関数を使いこなして、シンプルな管理を！

1つの帳票や管理表であれもこれも管理するのではなく、目的別に管理表やレポートを使い分けて管理方法をシンプルにするのも業務効率化の1つです。そのためには、VLOOKUP関数やXLOOKUP関数をスムーズに使えるようにしておきたいものです。

多くの関数を組み合わせるより、できるだけシンプルに、他の人が見てもすぐ理解が得られるようなエクセル関数ユーザーになることをおすすめします。


※ 掲載している情報は記事更新時点のものです。

  • 監修：岡 和恵　（税理士 / CFP）

    大学卒業後、２年間の教職を経て専業主婦に。システム会社に転職。 システム開発部門と経理部門を経験する中で税理士資格とフィナンシャルプランナー資格を取得。 2019年より税理士事務所を開業し、税務や相続に関するライティング業務も開始。

会計の知識をさらに深めるなら

※本サイトは、法律的またはその他のアドバイスの提供を目的としたものではありません。当社は本サイトの記載内容（テンプレートを含む）の正確性、妥当性の確保に努めておりますが、ご利用にあたっては、個別の事情を適宜専門家にご相談いただくなど、ご自身の判断でご利用ください。

関連記事

会計の注目テーマ

  1. クラウド会計ソフト「マネーフォワード クラウド会計」
  2. 会計の基礎知識
  3. VLOOKUP関数で突合する方法は？活用例や手順をわかりやすく解説