エクセルのデータマッチング｜突合・照合の5手順と失敗例

毎月の請求データと入金明細を1行ずつ目で追い、数字が合わない理由を探すだけで半日が過ぎることがあります。エクセルの突き合わせ作業は、関数を知っているだけでは精度が安定しない業務です。VLOOKUP関数で一致を確かめたつもりでも、翌月に計上漏れや二重計上が見つかるケースもあります。

突き合わせの結果がぶれる原因の多くは、関数そのものではなく照合を始める前の準備不足です。全角と半角の混在や末尾の空白、文字列として保存された数値が残っていると、同じ値でも不一致と判定されます。同じ取引先が複数行に登場するデータを1対1の前提で照合すると、件数や金額が二重に集計される点も見落としやすい落とし穴です。

本記事では、エクセルで突合・照合を行う5つの手順を、目的の設定から不一致データの検証まで順に解説します。代表的な関数と機能の使い分けに加え、よくある失敗パターンと業務別の活用例も紹介しています。自社の突合業務を見直す際のチェックリストとしてご活用ください。

エクセルのデータマッチングとは：突合・照合との違いを整理する

エクセルのデータマッチングは、2つ以上のデータを共通の項目で結び付け、一致・不一致を判定する作業の総称です。実務では「突合」と「照合」が混同されやすく、依頼者と担当者で作業範囲の認識がずれる原因になります。以下では2つの用語の違いを比較表で押さえたうえで、代表的な業務シーンと扱うデータ量の目安を確認します。

突合：原本データと加工後データを突き合わせて整合性を検証する

突合は、同じ取引を記録した2つのデータを突き合わせ、内容に食い違いがないかを検証する作業です。典型例は、販売管理システムから出力した売上明細と、会計システムに計上された売上仕訳の突き合わせ作業です。両者は同じ取引を別の経路で記録したものなので、すべての工程で正しく処理されていれば件数も金額も一致します。一致しない行が見つかった時点で、転記ミスや計上漏れ、システム連携の失敗がどこかの工程で起きていると判断できます。

突合の特徴は、「どちらが正しいか」ではなく「なぜ差が生じたか」まで説明を求められる点です。監査や月次決算で使う資料なら、差異の件数と金額に加えて、原因と対応状況を1行ずつ記録する必要があります。そのため、一致・不一致の判定列に加えて、差額列と原因メモ列をあらかじめ用意してから作業に入るのが実務の定石です。判定列だけで作業を始めると、差異の説明資料を別途作り直すことになり、作業時間が1.5〜2倍に膨らみます。

照合：2つのデータを照らし合わせて値の有無や正誤を確かめる

照合は、基準となるデータと確認対象のデータを照らし合わせ、値が存在するか、内容が正しいかを確かめる作業です。たとえば、展示会で集めた数百枚分の名刺リストを既存の顧客マスタと照らし合わせ、新規か既存かを判定する作業が該当します。突合が双方向で差異を追うのに対し、照合は基準側を正として片方向で確認するケースが多くなります。結果も「存在する・存在しない」「正しい・誤り」といった2値で表せる場合がほとんどです。

照合では、何を基準データとするかを最初に決めておかないと、判定結果の解釈が担当者ごとに変わります。顧客マスタと名刺リストの例なら、マスタ側を基準とし、名刺リストの各行に「既存」「新規」の判定を付けるのが基本形です。反対にマスタ側から名刺リストを照合すると、「今回接触がなかった既存顧客」の抽出という別の目的の作業になります。依頼を受けた段階で、どちらのデータに判定結果を付けるのかを依頼者と確認しておくと手戻りを防げます。

データマッチングが発生する代表的な業務シーンと扱うデータ量

データマッチングは経理部門だけの作業ではなく、営業、購買、物流、人事など、複数のシステムからデータを受け取る部署で日常的に発生します。たとえば経理なら請求と入金、購買なら発注と納品というように、突き合わせる相手のデータは業務ごとにほぼ決まっているのが実情です。担当者が扱うデータ量は業務によって大きく異なるため、手法を選ぶ前に件数の規模を把握しておく必要があります。代表的な業務シーンと、1回の作業で扱う件数の目安は次のとおりです。

• 経理：請求データと入金明細の突き合わせ（月数百〜数千件）
• 購買：発注データ、納品データ、請求書の三者照合（月数百〜数千件）
• 在庫管理：システム在庫と実地棚卸の差異確認（数千〜数万SKU）
• 営業・マーケティング：展示会リストと顧客マスタの重複確認（数百〜数万件）
• 人事：勤怠データと給与計算データの突合（従業員数×勤務日数分）

手法を選ぶ際の目安として、1万行未満で月1回程度の作業なら、関数による照合で十分に対応できます。1万〜10万行、または毎週・毎日の作業になる場合は、再計算の負荷と手作業の工数を考えるとPower Queryへの切り替えを検討すべき段階です。エクセルの上限は1シートあたり1,048,576行ですが、数十万行を関数で処理するとファイルの保存だけで数分かかる状態になります。100万行に近づく規模なら、データベースやETLツールでの処理を前提に設計を考え直すほうが現実的です。

エクセルで突合・照合を行うと解決できること

突合・照合をエクセルに置き換える効果は、「無料で使える」「導入しやすい」といった一般論にとどまりません。目視確認に頼っていた作業を数式に移すことで改善するのは、見落とし、判定のばらつき、根拠の追跡という3つの課題です。以下では、それぞれの効果を実務の数値や運用例とあわせて説明します。

目視確認による見落としとダブルチェックの工数を減らせる

目視での突き合わせは、件数が数百行を超えたあたりから集中力が続かず、見落としが急に増えます。見落としを防ぐために2人でダブルチェックを行うと、作業時間はそのまま2倍になります。1,000行の照合に1人3時間かかる業務なら、月に6時間を確認作業だけに使っている計算です。COUNTIF関数などで一致判定を自動化すると、作業は数式の入力と不一致行の確認だけになり、同じ規模でも30分〜1時間程度に収まります。

ここで押さえておきたいのは、自動化で減るのは「全件を見る工数」であり、確認そのものが不要になるわけではない点です。数式で抽出された不一致行は、原因の特定や相手先への問い合わせなど、人の判断が必要な作業として残ります。ダブルチェックの対象を全件から不一致行だけに絞れば、チェックの密度を保ったまま工数を大きく減らせます。実務では、不一致行が全体の5%を超えた場合にのみ2人目が全件を再確認するといった、発動条件付きのルールにする運用が効果的です。

一致の判定基準を数式に固定し、担当者による品質のばらつきを抑えられる

目視の照合では、「株式会社」の有無や半角・全角の違いを一致とみなすかどうかが、担当者の感覚に委ねられがちです。Aさんは一致、Bさんは不一致と判定すると、同じデータでも月によって差異件数が変わり、数字の信頼性が揺らぎます。判定基準を数式に落とし込むと、誰が作業しても同じ入力には同じ結果が返るため、品質が担当者に依存しなくなります。数式として残った基準は、判断のルールを明文化した資料としても機能するのが利点です。

数式に固定する際は、「どこまでを同じとみなすか」を判定ルールとして先に文章で決めておきます。たとえば「法人格の表記は無視する」「英数字の全角・半角は区別しない」「金額は1円単位で一致させる」といった具合です。ルールを決めずに数式を組むと、作成者の判断が数式の中に埋もれ、後任者は意図を読み解けません。判定ルールは照合シートの先頭に3〜5行程度の箇条書きで記載しておくと、数式と照らし合わせて確認しやすくなります。

照合結果を列として残し、後から差異の根拠を追跡できる

照合結果を元データの隣に判定列として残しておくと、「いつ、どの基準で、どの行が不一致だったか」を後から確認できます。目視確認でチェックマークを付けるだけの運用では、結果は残っても判定の根拠が残りません。監査対応や翌月の問い合わせで「なぜこの取引を差異として扱ったのか」と聞かれたとき、説明に使えるのは記録された判定列だけです。判定列、差額列、原因区分列、対応状況列の4列をセットで持たせる形が、実務で運用しやすい構成です。

追跡性を高めるには、月末時点の判定結果を値として確定させる手順を加えます。数式のままにしておくと、元データを更新した瞬間に過去の判定結果も書き換わり、当時の状態を再現できなくなります。確定の方法は、判定列をコピーして「値として貼り付け」を行い、シート名に「2026年9月確定」のような日付を付けて保存するだけです。ファイル名にも作業日と作業者名を入れておくと、差異の根拠を探す時間を1件あたり数分単位に短縮できます。

エクセルでデータマッチングを行う5つの手順

エクセルでの突合・照合というと、COUNTIF関数やVLOOKUP関数の書き方から覚えようとする方が少なくありません。しかし、照合精度を左右するのは関数を入力する前の目的設定、前処理、キー設計です。以下の5つの手順は、関数による照合をSTEP4に位置付け、その前後に準備と検証の工程を置いた流れになっています。

STEP1 突合の目的と対象範囲：どの項目をどこまで一致とみなすか決める

最初に決めるのは、突合によって何を明らかにしたいのかという目的です。「入金漏れを見つけたい」のか「金額の誤りを見つけたい」のかで、比較すべき項目も、許容する差の範囲も変わります。入金漏れの確認なら請求番号の有無だけを照合すれば足りますが、金額誤りの確認では金額と振込手数料の差まで比べる必要があります。目的が曖昧なまま始めると、不要な項目まで比較して不一致が大量に発生し、確認作業に追われる結果になりがちです。

目的が決まったら、対象範囲と一致の定義を依頼者と合意します。特に食い違いやすいのが対象期間で、経理は締め日基準、営業は受注日基準でデータを出力しているケースがよくあります。期間の基準がずれたまま突合すると、月末をまたぐ取引がすべて不一致として抽出され、実態のない差異の確認に数時間を奪われるのが典型的なつまずきです。合意した内容は照合シートの冒頭に記載し、依頼者の確認を得てから作業を始めると後の手戻りを防げます。作業前に合意しておく観点は、次の5つです。

• 対象期間：締め日基準か、取引日基準か
• 対象データ：どのシステムから、どの条件で出力したファイルか
• 比較項目：一致を判定する列と、参考として表示するだけの列
• 許容差：金額は1円単位で一致させるか、振込手数料分の差を許容するか
• 完了条件：不一致が何件以下になれば作業を完了とするか

STEP2 前処理：表記ゆれ・余分な空白・データ型を統一する

前処理は、2つのデータの書式をそろえ、本来一致するはずの値が正しく一致と判定される状態を作る工程です。システムから出力したデータには、全角と半角の混在、前後の空白、「株式会社」と「(株)」のような表記ゆれが必ず含まれます。見た目は同じでも、エクセルは「A001」と「Ａ００１」、「1234」という文字列と1234という数値を別の値として扱います。書式の違いだけで不一致になった行が、抽出結果の半数を超えることもあるほどです。

データクレンジングとは？意味と代表手法を解説！

前処理はエクセルの標準機能と関数で進められ、1万行程度なら作業時間は30分〜1時間が目安です。作業は元データを直接書き換えず、隣に作業列を追加して変換後の値を作る方法で進めます。元の値を残しておけば、変換の誤りに気付いたときにすぐ元の状態と見比べられるのが利点です。作業列の見出しには「顧客名_前処理後」のように加工済みであることがわかる名前を付け、元の列と取り違えないようにします。実務で確認している前処理の観点と、それぞれの操作は以下のとおりです。

• 前後の余分な空白：TRIM関数で削除し、全角スペースは置換（Ctrl＋H）で空欄に置き換える
• 全角・半角の混在：ASC関数で英数字とカタカナを半角にそろえる
• 文字列として保存された数値：VALUE関数で数値に変換するか、「データ」タブの「区切り位置」で一括変換する
• 先頭のゼロの欠落：取引先コードは両方のデータを文字列にそろえ、LEN関数で桁数を確認する
• 法人格の表記の違い：置換で「(株)」「㈱」を「株式会社」に統一するか、法人格を除いた比較用の列を作る
• 目に見えない改行や制御文字：CLEAN関数で削除する

STEP3 キー項目の設計：単一キーと複合キーを使い分ける

キー項目とは、2つのデータの行同士を「同じもの」として結び付けるための共通の列を指します。請求番号や社員番号のように、1つの値で1行を特定できる列をそのまま使う方法が単一キーです。一方、1つの列だけでは行を特定できない場合に、取引先コードと請求月のように複数の列を連結して使う方法を複合キーと呼びます。どちらを使うかは、キーの値が重複せず、両方のデータに同じ桁数・同じ形式で存在するかどうかで判断します。

マスタデータとは？具体例でクイックに解説！

キーの候補が決まったら、照合の前に重複がないかを必ず確認します。確認はキー列の隣に「=COUNTIF(請求[請求番号],[@請求番号])」と入力し、結果が2以上の行を抽出するだけで完了します。重複があるまま照合すると1行に複数行が対応し、件数や金額が二重に集計されるため、キーの見直しが必要です。複合キーは「=[@取引先コード]&[@請求月]」のように文字列を連結して作成しますが、桁数が一定でない列同士では区切り文字を挟むのが安全です。

STEP4 照合の実行：一致・不一致を判定して結果列に出力する

前処理とキー設計が済んだら、関数を使って照合を実行します。作業の前に、両方のデータ範囲を選択してCtrl＋Tでテーブルに変換しておくと、行を追加しても数式の参照範囲が自動で広がります。テーブル名は「請求」「入金」のように内容がわかる名前に変更し、数式の中で「請求[請求番号]」と列を名前で指定できる状態にするのがおすすめです。セル範囲を固定の行番号で指定する方法は、行が増えたときに参照漏れが起きる典型的な原因です。

判定は「存在確認」と「値の比較」の2段階に分けると、不一致の種類を区別しやすくなります。1段階目の存在確認に使うのは、請求テーブルに入力する「=COUNTIF(入金[請求番号],[@請求番号])」です。結果が0の行は入金データが存在しないため、この時点で「入金なし」と判定できます。2段階目では存在する行に限って「=XLOOKUP([@請求番号],入金[請求番号],入金[入金額])」で入金額を取得し、差額列で請求額と比べます。判定列には「一致」「金額差異」「入金なし」の3区分を入力し、フィルターで区分ごとの件数を確かめるのが基本の流れです。

STEP5 不一致データの検証：差異の原因を分類して処理する

照合で抽出した不一致行は、そのまま修正せず、まず原因を分類してから処理方針を決めます。不一致の原因は大きく、データの欠落、キーの重複、表記の差、計上タイミングの差という4つの型に分けられます。原因によって対応する部署も処理の方法も異なるため、分類せずに1行ずつ修正すると、同じ原因の差異を何度も調べ直すことになり非効率です。分類の判定方法と処理の詳細は、後述する実務のポイントの表にまとめたとおりです。

検証作業では、不一致行を原因区分ごとにフィルターで絞り込み、件数の多い区分から処理します。表記の差が大半を占める場合は、個別修正ではなくSTEP2の前処理ルールに戻って変換を追加し、照合をやり直すほうが早く片付きます。1件ずつ判断が必要な欠落やタイミング差は、相手部署への確認事項として一覧化し、回答期限を決めて依頼するのが確実です。不一致が最終的に0件にならない場合でも、残った差異の件数、金額、理由を記録して完了とすれば、翌月の照合で継続案件として追跡できます。

突合・照合に使うエクセル関数と機能の使い分け

突合・照合に使える関数や機能は複数ありますが、それぞれ得意な判定と苦手な条件が異なります。選ぶ基準は、確認したいのが「存在の有無」か「値の差」か、データが何行あるか、作業を繰り返す頻度の3点です。以下の比較表で全体像をつかんだうえで、各関数の具体的な使い方と判断基準を確認してください。

COUNTIF関数：相手のデータに存在するかを件数で確認する

COUNTIF関数は、指定した範囲の中に条件と一致するセルがいくつあるかを数える関数です。照合では「相手のデータにこの値が何件あるか」を数える用途で使い、結果が0なら存在しない、1なら存在する、2以上なら重複と判断できます。1つの数式で「有無」と「重複」を同時に確認できるため、照合の最初の一手として最も使い勝手のよい関数です。請求番号の有無だけを確認したい入金漏れチェックなら、COUNTIF関数だけで作業が完結します。

操作手順は、照合したい表の右端に判定列を追加し、先頭行に「=COUNTIF(入金[請求番号],[@請求番号])」と入力するだけです。テーブル形式にしておけば、Enterキーを押した時点で全行に数式が自動入力されます。結果列にフィルターをかけて「0」だけを表示すれば、相手に存在しない行の一覧がすぐに得られます。品番に「*」や「?」が含まれる場合、COUNTIF関数はワイルドカードとして解釈するため、誤った件数が返る点に注意が必要です。

VLOOKUP関数・XLOOKUP関数：対応する値を取得して差異を比較する

金額や数量の差まで確認したい場合は、キーをもとに相手データから値を取得するVLOOKUP関数やXLOOKUP関数を使います。取得した値を元データの隣に並べ、「=[@請求額]-[@入金額]」のように差額を計算すると、差異の大きさを一目で把握できます。Microsoft 365やExcel 2021以降を使っている環境なら、XLOOKUP関数を優先して選ぶのが基本です。VLOOKUP関数は列番号を数字で指定するため、元の表に列が1つ挿入されただけで、別の列の値を取得してしまうからです。

XLOOKUP関数は、検索範囲と戻り範囲を別々に指定するため、列の挿入や削除の影響を受けません。4つ目の引数に見つからなかった場合の戻り値を指定でき、#N/Aエラーの処理を別の関数で包む必要もなくなります。どちらの関数も、キーが重複していると最初に見つかった1件だけを返す仕様である点は共通です。同じ請求番号で分割入金があるデータでは2件目以降が無視されるため、事前にCOUNTIF関数で重複を確認してから使ってください。

INDEX関数＋MATCH関数：左方向の検索や列の増減に強い組み合わせ

INDEX関数とMATCH関数の組み合わせは、XLOOKUP関数が使えないExcel 2019以前の環境で、同等の検索を実現する方法です。MATCH関数でキーが何行目にあるかを求め、INDEX関数でその行にある値を取り出すという2段構えで動きます。数式は「=INDEX(マスタ[顧客名],MATCH([@顧客コード],マスタ[顧客コード],0))」の形になります。キー列より左側の値も取得でき、VLOOKUP関数のように表の列を並べ替える必要がない点が利点です。

取引先ごとに異なるシステムからデータを受け取り、列構成が毎月変わる業務では、この組み合わせの強みが生きます。MATCH関数の最後の引数には必ず0を指定し、完全一致で検索させます。省略すると近似一致になり、キーが昇順に並んでいない表では、存在しない値に対して別の行の値が返るという発見しにくい誤りが起きるためです。Microsoft 365環境へ移行済みで共有相手も同じバージョンを使っているなら、読みやすさの面からXLOOKUP関数へ置き換える判断も合理的です。

EXACT関数・条件付き書式：厳密一致の判定と視覚的な確認

通常の比較式「=A2=B2」は英字の大文字と小文字を区別せず、「abc」と「ABC」を一致と判定します。大文字と小文字を区別するシステムの会員IDや、大小で意味が変わる品番を照合する場合は、EXACT関数を使って厳密に判定します。「=EXACT([@会員ID],[@照合先ID])」の結果がFALSEの行は、見た目が似ていても文字として異なる値です。前後の空白や全角・半角の違いもFALSEとして検出されるため、前処理の漏れを見つけるチェックにも使える関数です。

条件付き書式は、判定結果を色で示し、不一致の分布を視覚的に確認したいときに役立ちます。操作は、判定列を選択して「ホーム」タブの「条件付き書式」から「セルの強調表示ルール」を開き、「指定の値に等しい」でFALSEや0に色を付けます。2つの列をまとめて比べたい場合は、「重複する値」ルールで「一意」を選び、一方にしか存在しない値を色分けする方法が手軽です。ただし数千行を超えるデータに条件付き書式を多数設定するとスクロールや保存が重くなるため、確認が済んだらルールを削除する運用が安全です。

Power Query：数万行規模のマッチングをマージ機能で処理する

Power Queryはエクセルに標準搭載されたデータの取得・変換機能で、起動場所は「データ」タブの「データの取得」です。突合に使うのは「クエリのマージ」機能で、2つのテーブルをキー列で結合し、一致した行と一致しない行を一度に抽出できます。結合の種類で「左反」を選べば左側だけに存在する行、「右反」を選べば右側だけに存在する行が得られます。関数と違って数式を1行ずつ再計算しないため、数万〜数十万行でも処理が止まりにくいのが特長です。

Power Queryの最大の利点は、前処理から照合までの手順が「適用したステップ」として記録され、翌月は「すべて更新」を押すだけで再実行できる点です。空白の削除や全角・半角の統一も「変換」タブの操作で記録できるため、STEP2の前処理ごと自動化できます。目安として、月次で同じ突合を繰り返し、データが1万行を超える業務なら、初期設定に半日〜1日かけても2〜3か月で工数を回収できます。反対に、年に数回だけ発生する数百行規模の照合なら、関数で済ませるほうが学習コストを含めて効率的です。

データプレパレーションとは？ETLとの違いから成功ポイントまで徹底解説

データマッチングの精度を高める実務のポイント

手順どおりに照合を実行しても、不一致の後処理や運用の仕組みが整っていなければ、毎月の作業は安定しません。精度を左右するのは、不一致の分類方法、突合漏れの検知、処理方法の切り替え基準、手順の記録という4つの観点です。いずれも現場で繰り返し起きるつまずきへの対策で、今月の照合からすぐに取り入れられます。

不一致リストを4分類する：欠落・重複・表記差・計上タイミング差

不一致リストは、原因ごとに4つの区分へ振り分けてから処理すると、確認の手戻りが大きく減ります。欠落、重複、表記差、計上タイミング差の4区分は、どの業務の突合でも共通して現れる不一致の型です。区分ごとに見るべき兆候と処理方法が決まっているため、担当者は新しい不一致が出るたびにゼロから原因を考える必要がありません。原因区分の列を「データの入力規則」でプルダウン化しておくと、入力内容がそろい、区分別の集計も容易です。4区分の特徴と処理方法は次の表のとおりです。

分類後は、区分ごとの件数を月次で記録しておくと、差異の傾向から業務プロセスの問題が見えてきます。たとえば表記差が毎月50件以上出るなら、入力画面の選択肢化や取引先マスタの整備を検討すべき合図です。計上タイミング差が増えている場合は、部署間で締め日の運用がずれ始めている可能性を疑います。分類の記録は照合作業の効率化だけでなく、上流のデータ品質を改善する材料としても活用できます。区分別件数の推移は、月次の照合結果と同じファイルに記録しておくと前月との比較が容易です。

データ品質とは？品質評価項目や品質を向上させるための実務的対策を解説

一致件数だけでなく未突合件数を確認し、突合漏れを検知する

照合結果の確認で見落とされやすいのが、「一致件数」と「不一致件数」を足しても元データの件数にならないケースです。空白行やフィルターで非表示になった行、判定列の数式が入っていない行は、一致にも不一致にも数えられずに残ります。この未突合の行に気付かないまま「不一致はすべて処理した」と報告すると、翌月以降に大きな差異として表面化します。件数の検算で確かめるのは、元データ件数＝一致件数＋不一致件数＋未突合件数が成り立つかどうかです。

実務では、照合シートの上部に件数の検算セルを設け、差が0でなければ作業を完了させないルールにします。テーブルの行数は「=ROWS(請求)」、判定済みの件数は「=COUNTA(請求[判定])」で求められ、2つの差が未突合件数になります。金額についても、元データの合計額と、一致分・差異分の合計額が一致するかを同じ要領で確かめるのが確実です。検算セルに条件付き書式を設定し、差が0以外なら赤く表示されるようにしておくと、完了報告前の見落としを防げます。確認セルとして設けておきたいのは次の3項目です。

• 件数の検算：元データ件数から判定済み件数を引いた値が0になっているか
• 金額の検算：元データの合計額から、一致分と差異分の合計額を引いた値が0になっているか
• 区分別件数：「一致」「金額差異」「入金なし」の件数が前月と比べて極端に増減していないか

関数の多用で再計算が重くなる前に処理方法を切り替える

関数による照合は、データ量が増えるにつれて再計算の負荷が急に重くなります。COUNTIF関数やXLOOKUP関数は数式1つにつき相手の範囲全体を検索するため、5万行同士の照合では検索回数が数十億回規模に達します。セルを1つ編集するたびに数秒〜数十秒待たされる状態になったら、それが処理方法を切り替えるべき合図です。一時的な対策として、「数式」タブの「計算方法の設定」を「手動」にし、F9キーで必要なときだけ再計算する方法があります。

切り替えの判断は、データ量と作業頻度を軸にすると迷いません。1万行未満の単発作業なら関数のままで問題ありませんが、1万行を超えて毎月繰り返す作業はPower Queryへの移行を検討する段階です。10万行を超える場合や、複数の部署が同じ突合結果を参照する場合は、BIツールやデータベースで処理する構成に移したほうが安定します。移行時には、エクセルで出していた結果と新しい仕組みの結果を1〜2か月並行して比べ、件数と金額が一致することを確かめてから切り替えます。並行期間を設けずに移行すると、集計条件の違いによる数字のずれに後から気付き、原因の特定に数週間かかる例もあるほどです。

ExcelからBIへの移管プロジェクトで、数値のズレが起こる13の理由と対応策

手順とルールを手順書に残し、翌月も同じ結果を再現できるようにする

突合作業は月次や週次で繰り返されるため、一度組み上げた照合シートを翌月も同じ手順で使えるかどうかが効率を大きく左右します。作成者の頭の中にしか手順がない状態では、担当者が休んだり異動したりした時点で作業が止まります。手順書といっても長い文書は不要で、照合シートの1枚目に「手順」シートを追加し、A4で1〜2枚分の分量にまとめれば十分です。文書を別ファイルに分けると更新が漏れやすいため、照合シートと同じファイルに同居させるのが実務的な判断です。

手順シートに書く内容は、「何を」「どの順番で」「どの基準で」行うかを後任者が読んで再現できる粒度にそろえます。「前処理を行う」とだけ書くのではなく、「顧客名列にASC関数を適用し、置換で(株)を株式会社に統一する」のようにクリックする操作の単位で記載します。判定基準を変更した場合は、変更日と理由を1行追記し、過去の照合結果と比較できるようにしておくことが肝心です。最低限記載しておきたい項目は以下のとおりです。

• 使用するデータの出力元システム、出力条件、ファイル名の規則
• 前処理の操作内容と実施する順番
• キー項目と判定ルール（許容差を含む）
• 不一致の原因区分と、区分ごとの問い合わせ先
• 完了条件と、結果を値として確定・保存する場所

エクセルの突合・照合でよくある失敗パターン

突合・照合の失敗は、関数の書き間違いよりも、確認の手順が抜けたことによって起きるものが大半です。しかも多くの失敗は作業直後には気付かれず、翌月の残高確認や監査の段階で表面化します。ここでは現場で繰り返し見られる5つの失敗パターンを取り上げ、原因と回避策を具体的に示します。

前処理を省いて照合し、不一致件数が実態より膨らむ

最も多い失敗が、システムから出力したデータをそのまま照合し、数百件に及ぶ不一致の確認に追われるパターンです。たとえば3,000件の取引先リストを取引先名で照合すると、法人格の表記や空白の違いだけで数百件が不一致になることがあります。これらを目視で1件ずつ確認すると、担当者2人で丸1〜2日を要する規模の作業量になります。実態としての不一致は数十件しかないのに、書式の違いに作業時間の大半を奪われている状態です。

回避策は、照合を実行する前に、キー列のサンプル100行を目で見て書式の傾向をつかむことです。全角・半角の混在、空白、法人格の有無、数値と文字列の混在が見つかれば、STEP2の前処理を先に行います。サンプルの確認は1人で10〜15分あれば終わるため、照合後の手戻りと比べると圧倒的に安い投資です。前処理の前後で不一致件数を比べておくと、前処理の効果を数字で説明できます。不一致件数が全体の10%を超えたら、個別確認に入る前に書式の問題を疑うというルールを決めておくと安心です。

重複キーを見落としたまま突合し、金額や件数が二重に集計される

キーの重複は、エラー表示が出ないため気付きにくい失敗です。たとえば同じ請求番号に分割入金が2回あると、XLOOKUP関数は1回目の入金額しか返さず、残りの入金は照合結果から消えます。反対にPower Queryのマージでは重複キーの行がすべて結合されるため、請求額が2倍に膨らんだ状態で集計されます。どちらの場合も表面上は正常に処理されたように見えるため、合計額を検算しない限り発見が難しい点が厄介です。

回避策は、照合の前に両方のデータでキーの重複を確認する手順を必ず挟むことです。COUNTIF関数で自分自身のキー列を数え、2以上の行があればフィルターで抽出して1件ずつ内容を確認します。分割入金のように重複が正当な場合は、ピボットテーブルでキーごとに金額を合計した表を作ってから照合します。二重登録のように重複自体が誤りである場合は、元データの修正を依頼者に求め、照合用のデータには反映しないのが原則です。

数式の参照範囲がずれ、未チェックの行が残ったまま完了と判断する

数式をセル範囲の直接指定で作ると、翌月にデータが増えたとき、追加された行が参照範囲から外れます。前月が4,800行で今月が5,200行なら、末尾の400行は照合されないまま、一見すると正常な結果が表示されます。数式をコピーする際に絶対参照の「$」を付け忘れ、1行下にコピーするたびに参照範囲も1行ずつ下にずれるケースも典型的です。範囲の先頭側が照合から外れるため、どの行が漏れたかを後から特定するのに時間がかかります。

回避策の第一は、STEP4で紹介したとおり、データをテーブルに変換して列名で参照することです。テーブルは行の追加に合わせて範囲が自動で広がるため、参照範囲のずれそのものが起きなくなります。第二の対策は、先に紹介した件数の検算セルで、元データの行数と判定済みの行数の一致を毎回確認することです。この2つを組み合わせれば、範囲ずれが起きにくい仕組みと、起きても完了前に検知できる仕組みの両方がそろいます。

エラー値を一律に置き換えて表示し、本来の不一致を見逃す

照合結果に#N/Aが並ぶと見栄えが悪いため、IFERROR関数で一律に0や空白へ置き換えてしまう例がよく見られます。しかし#N/Aは、「相手のデータに存在しない」という照合結果そのものです。0に置き換えると「入金額が0円のデータ」と区別できなくなり、入金漏れを金額差異として誤って処理する原因になります。さらにIFERROR関数は、参照先のシート削除などで生じる#REF!エラーまで隠すため、数式が壊れていても気付けません。

エラー値は隠すのではなく、意味のある判定区分に変換して残すのが正しい扱い方です。存在確認はCOUNTIF関数で先に行い、0件の行を「入金なし」と判定したうえで、存在する行だけにXLOOKUP関数を適用します。IFERROR関数ではなくIFNA関数を使えば、#N/Aだけを処理対象にし、それ以外のエラーはそのまま表示させられます。判定列に想定外のエラーが残っていないかはフィルターの一覧で数秒のうちに判断できるので、完了前の確認手順に加えておくと安心です。

作成者しか数式を理解できず、担当変更のたびに作り直しになる

IF関数を何重にも入れ子にした数式や、非表示の作業列に依存した照合シートは、作成者以外には中身を追えません。担当者が異動した翌月に、後任者が数式を読み解けずにシートを一から作り直し、判定基準が前任者と変わってしまうケースは珍しくありません。判定基準が変わると過去の結果と比較できなくなり、差異の推移を追う資料としての価値も失われる点が深刻です。属人化の影響は、作業時間の増加や引き継ぎの手間だけにとどまりません。

回避策は、1つの数式で1つの判定だけを行う設計に分解することです。存在確認、値の取得、差額計算、区分判定をそれぞれ別の列に分ければ、各列の数式は1行で読める長さに収まります。列の見出しには「①存在確認」「②入金額取得」のように処理順の番号を付け、非表示の列は作らないルールにします。完成したシートは作成者以外のメンバーに手順シートだけを見て同じ結果を出せるかを試してもらうのが、属人化を防ぐ確実な検証方法です。

業務別に見るデータマッチングの活用例

データマッチングの手順や関数は、業務の種類によって重視するポイントが変わります。以下では経理、在庫管理、顧客管理の3業務について、課題、アプローチ、成果の流れで活用例を紹介します。数値はいずれも一般的な規模の企業を想定した目安のため、自社の件数や体制に置き換えて参考にしてください。

経理：請求データと入金明細の消込で差異確認の工数を削減した例

月に約2,000件の請求を発行する卸売企業のケースを想定します。経理担当者は、銀行の入金明細と請求データを突き合わせる消込作業に毎月3日前後を費やしている状況です。振込名義は半角カナで記録される一方、請求データは漢字の正式社名で管理されているため、名義の文字列による照合がほとんど機能しません。振込手数料が差し引かれた入金や、複数の請求をまとめた一括入金も混在し、目視での判断に頼らざるを得ない状態です。

アプローチとして、取引先マスタに「振込名義（半角カナ）」の列を追加し、入金明細の名義から取引先コードを取得できるようにします。そのうえで取引先コードと入金額で照合し、一致しない入金だけを「手数料差」「一括入金」「不明」の3区分に分類する流れに変えました。手数料差は振込手数料の上限額以内を許容差として一致扱いとし、一括入金はピボットテーブルで請求を合算してから再照合する運用です。この結果、目視で確認する行は全体の1割程度に絞られ、作業は3日から1日程度まで短縮される見込みです。

経理のデータ分析は何ができる？活用方法や実施ポイントをプロが解説

在庫管理：システム在庫と実地棚卸の照合で差異要因を特定した例

3つの倉庫で約8,000SKUを管理する小売企業のケースを考えます。半期ごとの実地棚卸のたびに、システム在庫との差異確認に1週間程度を要し、原因がわからない差異は棚卸減耗として一括処理されがちです。棚卸データはハンディターミナルから倉庫ごとに出力され、商品コードの桁数が倉庫によって6桁と8桁で異なる状態です。この桁数の違いにより同じ商品でもキーが一致せず、差異の大半が実在しない不一致として抽出されてしまいます。

アプローチの起点は、商品コードを8桁の文字列に統一する前処理と、倉庫コード＋商品コードの複合キーの設計です。Power Queryで3倉庫の棚卸データを1つに結合し、システム在庫とマージして数量の差を算出する手順を記録しました。差異は「入荷の計上遅れ」「出荷の計上漏れ」「ロケーション違い」「実際の紛失・破損」の4つに分類し、倉庫別に件数を集計します。特定の倉庫で入荷の計上遅れが多いとわかれば、検品後の入力タイミングを見直すという具体策につなげられる点が、分類まで行う価値です。

データ統合とは？統合の目的や初心者向けの進め方を解説

顧客管理：複数リストの突合で重複登録を洗い出した例

営業部門がSFA、マーケティング部門がセミナーの申込リストを別々に管理している企業では、同じ顧客が複数のリストに重複登録されやすくなります。約1万件の顧客リストを統合しようとしても、会社名の書き方の違いや担当者の異動によって、会社名の単純な一致だけでは重複の多くを見逃します。重複が残ったままでは、同じ担当者へのメールの二重送信や、営業担当同士のアプローチの重複が避けられない状況です。統合の精度を左右するのは、どの列をどう加工して照合するかという設計です。

アプローチとして、会社名は法人格と空白を除いた比較用の列を作り、メールアドレスのドメイン部分を別の列に切り出しました。そのうえで「比較用会社名＋電話番号」と「メールアドレス」の2種類のキーで照合し、どちらかで一致した組み合わせを重複候補として抽出します。候補の最終判断は目視で行いますが、確認対象は抽出された候補だけなので、作業負荷は全件確認と比べて大幅に軽くなるのが特徴です。統合後の顧客リストは、どのリストのどの行を正とみなしたかを記録しておくと、次回の統合作業でも同じ判断基準を再利用できます。

名寄せとは？正確な顧客データ管理の方法と活用ポイントを徹底解説

まとめ：前処理とキー設計がエクセルの突合精度を左右する

エクセルのデータマッチングは、関数の知識以上に、照合を始める前の準備と照合後の検証で精度が決まります。目的と対象範囲を決め、前処理で書式をそろえ、重複のないキーを設計してから照合を実行する流れが基本です。5つの手順で押さえるべき要点は、次のとおりです。

• STEP1：目的、対象期間、許容差を依頼者と合意する
• STEP2：空白、全角・半角、データ型、法人格の表記をそろえる
• STEP3：キーの重複をCOUNTIF関数で確認し、必要なら複合キーにする
• STEP4：データをテーブル化し、存在確認と値の比較を2段階で行う
• STEP5：不一致を4区分に分類し、件数と金額の検算で完了を判断する

照合の仕組みは一度作って終わりではなく、手順書と検算セルを備えることで、担当者が変わっても同じ精度を保てます。データ量が1万行を超えて毎月繰り返す作業になったら、Power Queryや専用ツールへの切り替えを検討してください。

「エクセルでの突合・照合作業を効率化したいけれど、何から手をつけたらいいかわからない」「データマッチングの精度を高める専門家の知見を取り入れたい」という方は、データ領域の実績豊富な弊社、データビズラボにお気軽にご相談ください。

貴社の課題や状況に合わせて、データの取り組みをご提案させていただきます。

データビズラボの実績無料相談・お見積り