照合一致や突合にエクセル関数は有効！？最適な方法を解説

「請求書と入金データの照合に時間がかかりすぎて、月末はいつも残業になってしまう」
「手作業でデータを突き合わせているが、見落としやミスが心配で何度も確認している」
「Excelで効率的に照合する方法を知りたいが、どの関数を使えばいいのかわからない」

経理や在庫管理、データ分析など、さまざまな業務でこうした悩みを抱えている方は少なくありません。

2つのデータを比較して一致・不一致を確認する「照合」や「突合」は、正確性が求められる重要な作業です。しかし、従来の手作業では時間がかかり、人的ミスのリスクも避けられません。そのため、Excelの関数やツールを活用した効率化が求められています。

本記事では、Excelでデータを照合する7つの方法と、それぞれの使い分けのポイントを解説します。基本的な関数から応用的な組み合わせ技、さらにAIツールを活用した自動化まで、実務で役立つ情報をわかりやすく説明します。

※ こちらの音声は、Google NotebookLM によって AI が生成したものです。そのため、発音や内容が正確でない場合があります。

照合・突合とは？業務で必要な理由

「照合」とは、複数のデータを比較して実在性や正確性を確認するプロセスを指します。
一方、「突合」は元データと加工後のデータを比較して、処理が正しく行われたかを検証する作業です。

ビジネスの現場では、以下のような場面で照合・突合が必要とされます。

• 経理業務での請求書と入金データの突合
• 在庫管理でのシステムデータと実地棚卸の照合
• 顧客データベースの重複チェック
• 発注書と納品書の内容確認

これらの作業を正確かつ効率的に行うことで、ミスの防止や業務のスピードアップが期待できます。

3点照合についてはこちらの記事にて詳しく解説しております。

エクセルによる大量データの突合

Excelには、データを照合するための方法が複数用意されています。

2つの表に記載されたデータを突合する場合、どのように処理されているのでしょうか。よく見かける方法は、以下の通りです。

• 2つの表を並べて表示し、目視で確認する。
• プリントアウトしてマーカーで色を付けるなど、目視で確認する。

数行のチェックなら目視は有効ですが、大量データになると目視では時間と精度に問題が生じます。それはチェック漏れなどのヒューマンエラーを起こしやすく、ミスの元になるためです。では、どうするのがよいのでしょうか。

1. 条件付き書式で重複を視覚的に確認

条件付き書式を使えば、重複するデータに自動的に色を付けて視覚化できます。少量のデータで重複を一目で確認したい場合に適した方法です。

操作手順

1. 照合したいセル範囲を選択
2. 「ホーム」タブから「条件付き書式」を選択
3. 「セルの強調表示ルール」→「重複する値」を選択
4. 任意の書式を選んで「OK」をクリック

関数を使わず直感的に操作できるため、Excel初心者の方でも簡単に利用できます。ただし、大量データには不向きで、詳細な条件指定も難しいといえるでしょう。

2. COUNTIF関数で存在確認

COUNTIF関数は、指定した範囲内に検索値が何個存在するかを数える関数です。他のリストに同じ値が存在するか確認したい場合に便利です。

基本構文：=COUNTIF(検索範囲, 検索値)

使用例

A列のデータがB列に存在するかを確認する場合：=COUNTIF(B:B, A2)

この数式は、A2の値がB列に存在すれば1以上、存在しなければ0を返します。

判定結果を表示する場合

=IF(COUNTIF(B:B, A2)>0, "一致", "不一致")

シンプルで理解しやすく、重複の個数も確認できます。ただし、存在確認のみで対応する値を取得することはできません。

3. VLOOKUP関数で対応データを取得

VLOOKUP関数は、検索値に対応するデータを別の表から取得できる関数です。照合作業で最も広く使われている方法といえるでしょう。

基本構文：=VLOOKUP(検索値, 範囲, 列番号, [検索方法])

使用例

A2の値をSheet2のA列から検索し、対応するC列の値を取得する場合：

=IFERROR(VLOOKUP(A2, Sheet2!A:C, 3, FALSE), "不一致")

検索値が見つからない場合は「#N/A」エラーが返されるため、IFERROR関数で「不一致」と表示するのが一般的です。

VLOOKUPは対応データを一度に取得できる便利な関数ですが、検索列は範囲の左端に限られます。また、複数の該当データがある場合は最初の値のみ取得されるという制限もあります。

4. INDEX×MATCH関数の組み合わせ

INDEX関数とMATCH関数を組み合わせると、VLOOKUPより柔軟な検索が可能です。

基本構文：=INDEX(取得範囲, MATCH(検索値, 検索範囲, 0))

使用例

A2の値をSheet2のA列から検索し、対応するC列の値を取得する場合：
=IFERROR(INDEX(Sheet2!C:C, MATCH(A2, Sheet2!A:A, 0)), “不一致”)

この組み合わせには、左方向の検索が可能、列の追加・削除に強い、VLOOKUPより処理速度が速いといったメリットがあります。数式がやや複雑になりますが、大規模なデータを扱う方にはおすすめの方法です。

5. XLOOKUP関数（Microsoft 365・Excel 2021以降）

XLOOKUP関数は、VLOOKUPとINDEX×MATCHの長所を統合した最新の検索関数です。Microsoft 365やExcel 2021以降で使用できます。

基本構文：=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])

使用例

A2の値をSheet2のA列から検索し、対応するC列の値を取得する場合：

=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "不一致", 0)

XLOOKUPは左右どちらの方向も検索可能で、見つからない場合の値を直接指定できるといった特徴があります。最も簡潔で強力な検索関数といえるでしょう。ただし、古いバージョンのExcelでは使用できない点に注意が必要です。

6. エクセルマクロを使って突合する

繰り返し発生する照合作業は、マクロやVBAで自動化することで大幅に効率化できます。

マクロを使えば、ボタン1つで複雑な処理を実行できます。処理の標準化によってミスを削減でき、作業時間も大幅に短縮できるでしょう。

ただし、マクロの作成にはプログラミング知識が必要で、メンテナンスにも専門知識が求められます。また、セキュリティ設定によってマクロが実行できない環境もあります。

そのため、自社の状況に応じて、マクロ導入の適否を判断することが重要です。

7. ジーニアルAIを使って突合する

VLOOKUP関数やCOUNTIF関数は小規模データなら有効ですが、大量データや複雑な帳票では計算が遅くなったり、設定ミスでエラーが発生することもあります。そこで、ジーニアルAIのようなエクセルアドイン型AIツールを使うと、関数やマクロを組む手間なく自動で照合・転記が可能です。

ジーニアルAIは、エクセルのデータと書類を結びつけ、一致する箇所をハイライトし、確認作業を効率化できるエクセルアドインです。エクセルやPDFにおけるチェック作業と転記作業が簡素化され、大幅に時間削減できます。

インストールすると、以下のような機能が利用可能になります。

• 証憑突合機能
  • Excelシートと書類の自動照合
  • セル選択で関連する書類、一致する箇所をハイライト表示
  • 照合結果をExcelシートへ出力
• クリップ機能
  • テキストクリップ：書類の文章をドラッグしてExcelシートへ自動転記
  • テーブルクリップ：書類の表を選択してExcelシートへ自動転記

これらの機能は、膨大なエクセルデータと合致する膨大なPDFを即座に自動マッチングすることができ、業務の自動化が可能となります。

一致を確認する際の注意点

Excelでデータを照合する際は、いくつかの注意点があります。正確な照合を行うために、以下のポイントを押さえておきましょう。

完全一致であること

Excelの照合では、わずかな違いも別のデータとして扱われます。
具体的には、以下のような違いが不一致の原因となります。

照合の精度を高めるには、以下のような前処理が効果的です。

• TRIM関数で余分なスペースを削除
• CLEAN関数で改行コードを削除
• ASC関数やJIS関数で全角・半角を統一
• VALUE関数やTEXT関数でデータ型を統一

これらの処理を行うことで、見落としやミスを防ぐことができます。

「実際に入力されている値」と「見た目の値」

エクセルでは、セルに入力した値をフォーマットや書式で変換して表示することができます。たとえば、2023/04/07とセルの値を入力した場合、書式を「標準」にすれば2023/04/07のまま表示されます。また、書式を「YYYY年M月」と設定すると、セルには2023年4月と表示されます。このように、見た目には「異なる数値」と判断できる値でも、エクセルの中では同じ値として管理されているのです。

起こりがちな問題と解決策

エクセルを使って突合を行う場合、起こり得る問題点と解決策について解説します。

エラーが出た場合の対処法がわからない

関数を正しく設定しないとエラーが発生します。単純な関数であれば順を追って確認すれば解決するものの、VLOOKUP関数のように対象範囲が広く表全体が対象範囲となるような関数はエラーが発生した場合、その原因究明と解決が複雑になるケースがあります。

• #N/A エラー
  検索値が見つからない場合に表示されます。IFERROR関数やIFNA関数で「不一致」などのメッセージを表示するとよいでしょう。
• #REF! エラー
  参照範囲が削除された場合に表示されます。数式の範囲を修正することで解決できます。
• #VALUE! エラー
  データ型が不一致の場合に表示されます。TEXT関数やVALUE関数でデータ型を変換することで対処できます。

関数が入り組み過ぎて複雑化し取り扱いが不便

関数を作成する場合、関数の中に関数を入れて精度を高めることができます。たとえば、IF関数を使う場合、理論式の答えが真の場合はこの関数を利用し、偽の場合はこの関数を利用して値を求めるなど、少々手が込んでいるように見える計算でも正しく設定すれば答えを導き出してくれます。ただし、複雑な関数に不具合が起きた場合や、新しく設定し直したい場合は困難です。
「数式の検証」などの機能で関数の構造を解析して不具合を修正することもできますが、関数はできるだけシンプルに設定することを心掛けるとよいでしょう。

エクセルで突合するメリット

多くの企業で既にExcelが導入されているため、新たなツールを購入する必要がありません。追加コストをかけずに照合作業を効率化できます。

メリット1 | スタートアップしやすい

Microsoft Officeは、ほぼすべての企業で導入されていると言われます。そのため、追加費用をかけずにはじめることが可能です。
例えば、「とりあえず一覧表にしてみよう」といったシンプルな操作でも、誰でも簡単に使えるソフトとしては、他に類を見ないでしょう。

メリット2 | 教育にかける時間を短縮できる

Excelは広く普及しており、基本的な操作を知っている方が多いでしょう。特別な学習をしなくても、すぐに照合作業に取り組めます。

メリット3 | 業務内容に合わせた作り込みができる

業務の内容に合わせて、数式や書式を自由に調整できます。自社独自の運用ルールにも対応しやすいといえるでしょう。
専用システムの構築を待つ必要がなく、今すぐ照合作業を始められます。

エクセルで突合するデメリット

一方で、Excelでの照合にはいくつかのデメリットもあります。

デメリット1 | 同時に使用できない

Excelファイルは同時に複数の人が編集すると、ファイルのロックが発生します。チームでの作業には不向きな面があります。

デメリット2 | 履歴を追うことができない

誰がいつ変更したかを追跡するのが難しく、データの信頼性を保つのに苦労することがあります。

デメリット3 | 複雑化によりメンテナンスが困難

数式が複雑になると、後から修正や変更を行うのが難しくなります。担当者の異動時に引き継ぎが困難になるケースも少なくありません。

活用方法

データを突合する場合に便利なエクセルの関数について説明します。

活用1 | VLOOKUP関数（単純に、一致するかしないかの判別）

この関数は、もともと2つのデータを統合するために作られた機能です。2つの表（表A・表B）のうち、表Aの検索値と一致する値が表Bの検索範囲内にある場合、その一致したセルがある行の指定された列にある値を抽出して表示します。
例えば、2回目の参加者リスト（表A）と1回目の参加者リスト（表B）の氏名を突合し、表Bに氏名がある人は、表Aの氏名欄の横にある「1回目参加」という項目に〇を表示するような場合に利用します。

活用2 | COUNTIF関数（何件一致するかカウントする）

この関数は、「何件一致するか」をカウントする機能をもっています。2つの表（表A・表B）を突合し、表Aの検索値が表Bにも存在するかを調べ、その結果を表示します。

例えば、2回目の参加者リスト（表A）と1回目の参加者リスト（表B）を突合し、表Aの氏名が表Bの氏名欄に何回表示されているかを調べ、結果が「1」と表示されれば1回目の参加者、「0」と表示されれば1回目の参加者でないと判断できます。

AI OCRとExcelアドイン型AIツールによる照合技術の進化

照合という技術は、年々進歩しています。文字や記号などの2次元的な照合にとどまらず、今や顔認証・指紋認証などの生体認証や3次元的な照合も可能になりました。生体認証は、端末ログインなどに広く普及しており、私たちの生活には欠かせない身近なものになっています。
また、銀行での印鑑照合においても、今ではスキャナによる照合が主流となりました。

今後についても、まだ手作業で行われているさまざまな照合の場面において、照合技術の進化により大幅な業務効率化が期待されています。