エクセル大量データ突合｜別シート照合の手順と4つの方法

請求データと入金データ、基幹システムの出力と部門の管理表など、別々のシートに置かれた数万行のデータを突き合わせる作業は多くの部門で日常的に発生しています。件数が数百件なら目視でも対応できますが、数万行を超えると関数の設定ミスや処理の停止が相次ぎます。

突合がうまくいかない原因の多くは、関数の選び方よりも手前の工程にあるものです。全角と半角の混在や文字列として保存された数値、重複した値が残ったまま突き合わせると、数式が正しくても結果は信用できません。精度を決めるのは、突き合わせに使う列を整える前処理と、件数や更新頻度に合った方法の選定です。

本記事では、別シート間の突合に必要な前処理の手順、COUNTIF関数からVBAまで4つの方法の使い分け、処理を重くしない設計と失敗の防止策を解説します。業務改善の事例も参考にしながら、自社の突合作業に合う進め方を見つけてください。

エクセルにおける大量データ突合とは：別シート照合が必要になる場面

突合作業は、同じ対象を指す2つのデータを並べて、存在の有無や値の一致を確かめる工程です。言葉の使い分けを曖昧にしたまま進めると、依頼者と作業者で期待する成果物がずれてしまいます。ここでは用語の整理から始め、別シートでの突合が発生する業務シーンと、件数が増えたときに手作業が限界を迎える理由を順に確認します。

突合・照合・マッチングの違い：作業の目的による使い分け

突合・照合・マッチングは現場で混同されやすい言葉ですが、何を確かめる作業なのかで区別すると依頼内容の認識がそろいます。突合は2つのデータを並べ、件数や金額が双方で一致しているかを検証する作業を指します。照合は基準となる台帳を正として、対象データがそこに存在するか、登録内容が正しいかを確かめる行為です。マッチングは表記が完全には一致しない顧客名や住所を、類似度をもとに同一のものとして紐づける処理まで含みます。

実務で注意したいのは、依頼の段階で「突合して」と言われても、実際に求められている成果物は照合レベルの有無確認で足りる場合がある点です。有無だけ分かればよいのか、値の差異まで一覧化するのか、名寄せまで必要なのかで、選ぶ方法と工数は大きく変わります。着手前に依頼者と次の3点をすり合わせておくと、作業後の大きな手戻りを防げます。確認にかかる時間は10分から15分程度ですが、突合をやり直す数時間を節約できる工程です。

• 判定したい内容が、存在の有無、値の差異、類似判定のどれにあたるか
• 2つのシートのうち、どちらのデータを正とするか
• 成果物の形式が、フラグ列、差異の一覧、件数の集計表のどれか

別シートに分かれたデータを突き合わせる代表的な業務シーン

別シートでの突合が発生するのは、同じ取引や対象を2つのシステムや部署が別々に記録している場面です。どちらか一方だけを正として運用できれば突合は不要ですが、実際には基幹システムの出力と現場で作成した管理表が並存しているケースが多数あります。月次や四半期の締めに合わせて定期的に発生する点も共通しており、1回あたりの件数は数千行から数十万行まで大きな幅があります。部門別に代表的な業務シーンを挙げると、次のとおりです。

• 経理：請求データと入金データの消込、売掛金残高と取引先の残高確認書の照合
• 販売・購買：受注データと出荷実績、発注データと仕入先の請求明細の突き合わせ
• 情報システム：システム移行やマスタ統合の前後で、レコードの過不足と値の差分を抽出
• 人事・総務：人事マスタと勤怠システム、アカウント台帳とIT資産台帳の整合確認
• 小売・物流：在庫システムの理論在庫と実地棚卸の数量の照合

いずれのシーンにも共通するのは、2つのデータの出どころが異なるため、キーの表記や粒度がそろっていない点です。たとえば請求データは請求書単位、入金データは振込単位で記録されるため、1件の入金が複数の請求に対応するケースが生まれます。業務シーンを特定したら、どちらのデータを正とするか、何件程度を何日以内に処理する必要があるかを最初に書き出しておきましょう。この2点が、後で方法を選ぶときの判断材料になります。

データ量が増えると目視確認と手作業が破綻する理由

目視での突合は、1件あたり10秒で確認できたとしても1万件で約28時間かかる計算です。人の集中力は長時間続かないため、件数が増えるほど見落としの発生率も上がります。フィルターで絞り込みながらコピーと貼り付けを繰り返す手作業は、行のずれや貼り付け漏れが起きても後から気づきにくい点が問題です。作業者本人しか正確な手順を知らない状態になりやすく、担当者の異動や休暇で月次の突合そのものが止まるリスクも抱えます。

手作業から関数に切り替えた場合でも、件数が数万行を超えると別の壁に当たるのが実情です。COUNTIF関数を10万行のデータに対して10万行分コピーすると、比較回数は最大で100億回に達し、再計算のたびに数分単位で操作が止まることがあります。件数が増えたときに問われるのは作業者の丁寧さではなく、比較回数を減らす仕組みを選べるかどうかです。目安として、数千行までは関数、数万行を超えたら数式を使わない方法を検討するという線引きを持っておくと判断が早まります。

大量データの突合をエクセルで行うメリットと押さえるべき限界

専用ツールを導入しなくても、エクセルだけで数十万行規模の突合まで対応できます。ただし、行数の上限や履歴管理の弱さなど、エクセルの仕組みに由来する制約があるのも事実です。ここではエクセルを選ぶ利点を2つ確認したうえで、どの条件を超えたら別の手段を検討すべきかという限界の目安を示します。

追加コストなく既存の環境と人員のスキルで着手できる

エクセルで突合を行う最大の利点は、ライセンスの追加購入や稟議を待たずに、その日のうちに作業を始められることです。多くの企業ではMicrosoft 365が全社員に配布されており、XLOOKUP関数やパワークエリ（Power Query）も標準機能として使えます。専用のデータ統合ツールを導入する場合、選定から本番運用まで3〜6か月、費用も年間数百万円規模になるケースが珍しくありません。月に数回の突合のために投資判断を仰ぐより、エクセルで手順を固めるほうが現実的です。

人員のスキル面でも、エクセルは教育コストを抑えやすい環境です。VLOOKUP関数やフィルターを日常的に使っている担当者であれば、XLOOKUP関数への移行は半日程度の練習で十分に対応できます。現場でよくあるつまずきは、作業者ごとに関数の組み方が異なり、引き継いだ担当者が数式を読み解けなくなることです。回避策として、突合用のブックに「手順」シートを1枚設け、使用する関数と参照範囲、実行順を書き残す運用をおすすめします。

Excelを活用したデータ分析の方法

別シート構成のまま検証できるため元データを保全しやすい

エクセルでは、元データを受け取ったシートには手を加えず、突合用のシートから参照する構成を取れます。システムから出力したCSVを「元_請求」「元_入金」のように名前を付けたシートへ貼り付け、加工や判定は別シートで行う形です。この構成にしておけば、判定結果に疑問が出たときも、元の値を開いてすぐに確認できます。元データのシートは「校閲」タブの「シートの保護」でロックしておくと、誤って上書きする事故を防げます。

実務で扱いやすいのは、シートを役割ごとに3層へ分ける方法です。層を分けておくと、突合で差異が見つかったときに、その原因が元データにあるのか、整形の加工過程で新たに生じたのかを切り分けられます。シート名の先頭に01や02のような層を示す番号を付けておけば、第三者がブックを開いたときにも構造がすぐに伝わります。毎月繰り返す定例の突合なら、元データ層だけを差し替えれば判定層まで自動で結果が更新される状態を目指しましょう。

• 元データ層（シート名の例：01_元_請求）：出力したCSVをそのまま貼り付け、保護をかける
• 整形層（シート名の例：02_整形_請求）：表記の統一やデータ型の変換など、突合の準備を行う
• 判定層（シート名の例：03_判定）：参照関数で突き合わせ、一致と不一致の区分を出力する

限界：行数の上限・再計算の遅延・変更履歴が残らない運用

エクセルの1シートに格納できるのは最大1,048,576行で、これを超えるデータはシートに読み込んだ時点で切り捨てられます。実務で支障が出るのは、上限に達するより手前の段階です。数式を含む数十万行のシートは再計算に数十秒から数分かかり、入力や保存のたびに待ち時間が発生します。32ビット版のエクセルはアプリケーションが使用できるメモリに上限があるため、リソース不足のエラーで処理が途中で中断することもあります。

もう1つの限界は、どのセルを誰がいつ変更したかが記録として残らない点です。OneDriveやSharePointのバージョン履歴を使えばファイル単位で過去の状態に戻せるものの、特定の差異を誰がなぜ修正したかまでは追えません。監査対応や内部統制で突合の証跡を求められる業務では、この点が致命的な弱点になります。次に挙げる条件のいずれかに当てはまる場合は、データベースや専用ツールへの移行を本格的に検討する段階です。

• 突合対象のデータが50万行を超え、今後も増える見込みがある
• 1日に複数回、または他システムとの連携に合わせて頻繁な突合が必要になる
• 複数人が同時に編集し、修正の証跡を監査で提出する必要がある

突合の精度を左右する前工程：照合キーの標準化と一意性の確認

突合の結果が信用できるかどうかは、関数を入力する前の準備でほぼ決まります。2つのシートで同じ対象を特定するための列、つまり照合キーの表記や型が1文字でも異なれば、数式は一致を検出できないためです。ここでは4つのステップに分けて、前処理の具体的な手順と判断基準を解説します。

STEP1 表記ゆれの統一：全角半角・余分なスペース・改行コードの処理

表記ゆれの中でも見落としやすいのは、画面上では区別できない全角と半角の違いや、末尾に付いたスペースです。「A-001」と「Ａ－００１」、「山田商事」と末尾にスペースが付いた「山田商事 」は、人の目には同じに見えてもエクセルは別の値として扱います。基幹システムから出力したCSVには、セル内改行を示す改行コードや、Webシステム由来のノーブレークスペースが混入していることもあります。一致しない原因を数式に探す前に、キー列の文字そのものを疑うのが突合の鉄則です。

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

整形層のシートでは、キー列の隣に作業列を設け、関数で統一した値を作ってから突合に使います。英数字のコード列であれば、作業列に=TRIM(ASC(CLEAN(A2)))と入力し、最終行までコピーするのが最も手早い方法です。ASC関数は全角スペースも半角に変換するため、変換後にTRIM関数を通すと全角スペースもまとめて除去できます。ただし、顧客名などカタカナを含む列にASC関数を使うと半角カナに変わるため、コード以外の列には適用しないでください。組み合わせる関数の役割は次のとおりです。

• ASC関数：全角の英数字・記号・スペースを半角に変換する
• CLEAN関数：改行コードなど、印刷できない制御文字を削除する
• TRIM関数：前後のスペースを削除し、単語間の連続したスペースを1つにまとめる
• SUBSTITUTE関数：TRIM関数では消えないノーブレークスペース（CHAR(160)）などを個別に置換する

STEP2 データ型と表示形式の統一：日付や数値の見た目と実値のずれ

エクセルのセルには、画面に表示される見た目と、内部に保持している実際の値の2層があります。「2026/04/01」と表示されていても、一方のシートは日付のシリアル値、もう一方は文字列として保存されていれば一致判定は失敗します。取引先コードの「00123」がCSVを開いた瞬間に数値の123へ変換され、先頭のゼロが消える現象も典型的なつまずきです。見た目がそろっているかではなく、値の種類がそろっているかで確認する習慣をつけましょう。

データ型のずれは、ISTEXT関数とISNUMBER関数で列ごとに検出できます。作業列に=ISTEXT(A2)を入力し、TRUEとFALSEが混在していれば、同じ列に文字列と数値が混ざっている証拠です。修正には「データ」タブの「区切り位置」を使い、ウィザードの3画面目で列のデータ形式を「文字列」または「日付」に指定する方法が確実です。CSVを開く段階で先頭のゼロを守りたい場合は、「データ」タブの「テキストまたはCSVから」で読み込み、変換画面で型を指定してください。金額に小数の誤差が含まれる場合は、ROUND関数で桁をそろえてから比較します。

STEP3 照合キーの設計：1列で特定できない場合の複合キー作成

照合キーは、そのキーの値を見れば対象のレコードが1件に定まることが条件です。請求番号や社員番号のように一意に採番された列があれば、そのまま使えます。ところが入金データの場合、手がかりが取引先名と金額と日付だけというケースが一般的です。このように1列では対象を特定できない場合は、複数の列を連結して複合キーを作ります。取引先コードと請求月、店舗コードと商品コードを組み合わせるといった形がよく使われる代表例です。

複合キーの作成では、列同士を区切り文字で連結するのが基本です。テーブル形式にしたシートなら、=TEXTJOIN(CHAR(124),FALSE,[@取引先コード],[@請求日])と入力します。縦棒で区切られたキーが、テーブルの全行に自動で展開されます。日付の列はシリアル値の数字として連結されるため、両方のシートで日付型にそろえておくことが前提です。区切り文字を入れずに連結すると、「12」と「345」、「123」と「45」がどちらも「12345」になり、別の組み合わせが同じキーとして扱われてしまいます。複合キーに含める列は、2つのシートの両方に存在し、かつ表記を統一できる列だけに絞ってください。

STEP4 キーの重複確認：1対1と1対多のどちらで突合するかを決める

照合キーを作ったら、突合を始める前に各シートでキーが重複していないかを確認します。確認には、テーブル形式のシートに作業列を追加して=COUNTIFS([キー],[@キー])と入力する方法が手軽です。結果が2以上の行をフィルターで抽出すれば、重複しているキーの一覧が得られます。Microsoft 365であれば、=ROWS(UNIQUE(請求[キー]))で求めた一意の件数と全行数を比べるだけでも、重複の有無を判断できます。全行数と一意の件数が一致していれば、重複はゼロです。

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

重複が見つかった場合は、その重複が誤りなのか、業務上あり得る構造なのかを見極めます。同じ請求番号が2行ある場合は二重登録の可能性が高く、元データの修正を依頼すべき対象です。1つの取引先コードに複数の請求が紐づくのは正常な構造であり、この場合は1対多の突合として設計し直す必要があります。多い側を集計して1対1にそろえるか、明細単位のまま照合するかは、確認したい粒度で決めるのが判断基準です。キーの状態ごとの対応は次の表のとおりです。

別シート間で大量データを突合する4つの方法と使い分け

前処理でキーが整ったら、次の工程は目的とデータ量に合った突合方法の選定です。存在の有無だけを判定するのか、対応する値まで取得して比較するのか、定例作業として自動化するのかによって、適した手段は変わります。ここでは代表的な4つの方法の操作手順と注意点を取り上げ、最後に3つの軸で方法を選ぶ基準を示します。

COUNTIF関数：別シートに存在するかどうかの有無だけを判定する

COUNTIF関数は、指定した範囲に条件と一致するセルが何件あるかを数える関数です。突合では、判定したいシートの作業列に=COUNTIF(入金[請求番号],[@請求番号])と入力します。結果が0なら相手のシートに存在せず、1以上なら存在するという判定です。IF関数で文字に置き換えずに件数のまま残しておくと、2以上の値から相手側の重複にも気づけます。数式が1つで済み、VLOOKUP関数に慣れていない担当者でも扱えるため、有無の確認には最も手軽な方法です。

手軽な反面、COUNTIF関数には現場でよくある2つのつまずきがあります。1つ目は、数字だけで構成された16桁以上のコードを数値として扱うため、15桁目までが同じ別のコードを一致と数えてしまう点です。2つ目は、キーに「*」や「?」が含まれるとワイルドカードとして解釈され、意図しない行まで一致と判定される点です。有無の判定に違和感があれば、数式を疑う前にキーの桁数と記号の混入を確認してください。処理速度の面では、両シートが5万行を超えると再計算に時間がかかり始めるため、それ以上の件数ではほかの方法を検討しましょう。

XLOOKUP関数とINDEX＋MATCH：対応する値を取得して差異を比較する

存在の有無に加えて金額や数量の差異まで確認したい場合は、相手のシートから対応する値を取得する関数を使います。Microsoft 365やExcel 2021以降であれば、XLOOKUP関数が第一候補です。=XLOOKUP([@請求番号],入金[請求番号],入金[入金額])と入力すると、請求番号が一致する行の入金額が返されます。取得した値の隣に請求額との差額を計算する列を作り、0以外の行を抽出すれば差異の一覧が完成する流れです。

XLOOKUP関数は既定で完全一致になるうえ、第4引数に「未入金」などの文字列を指定すれば、一致しない行をエラー表示以外で示せます。Excel 2019以前の環境や、取引先に共有するブックで互換性を保つ必要がある場合は、INDEX関数とMATCH関数の組み合わせを使います。MATCH関数の第3引数は省略すると近似一致になるため、必ず0を指定するのが鉄則です。10万行規模になる場合は、キーで昇順に並べ替えたうえで第6引数に2を指定し、二分探索で検索時間を短縮する方法もあります。

パワークエリのマージ：数式を使わず数十万行を安定して突き合わせる

パワークエリのマージは、2つのテーブルを共通のキーで結合する機能です。数式をセルに入力しないため、数十万行を突き合わせてもシートの再計算は発生せず、ブックの動作が重くなりにくい特徴があります。シートに読み込まずに「接続の作成のみ」で保持すれば、行数上限を超えるデータも処理対象にできるのが強みです。処理手順はクエリとして保存されるので、翌月は元データを差し替えて「すべて更新」を押すだけで同じ突合を再現できます。操作の流れは次のとおりです。

• 突合する2つの範囲をそれぞれ選択し、「データ」タブの「テーブルまたは範囲から」でクエリに取り込む
• 取り込んだクエリを「閉じて次に読み込む」から「接続の作成のみ」で保存する
• 「データ」タブの「データの取得」→「クエリの結合」→「マージ」を選び、2つのクエリとキー列を指定する
• 結合の種類を選んで「OK」を押し、展開ボタンで必要な列だけを追加する
• 差額が必要なら「列の追加」タブの「カスタム列」で差額列を作り、シートに読み込む

マージの結果を左右するのは、6種類ある結合の種類の選び方です。請求側を基準に入金の有無を見るなら「左外部」、どちらか一方にしかない行を漏れなく洗い出すなら「完全外部」を選ぶのが基本になります。「左反」を選ぶと請求側にだけ存在する行が抽出されるため、未入金のリストをそのまま作成できます。現場でよくあるつまずきは、キー列の型が片方だけ「テキスト」、もう片方が「整数」になっていて、一致する行が0件になるケースです。マージの前に、両方のクエリでキー列の型アイコンが同じ表示になっているかを確認してください。

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

VBAと連想配列：定例化した突合作業を自動化し処理時間を短縮する

毎月同じ形式のデータを突き合わせ、結果を決まった帳票に出力する作業であれば、VBAで自動化すると効果が大きくなります。速度の鍵を握るのが連想配列（Dictionary）で、キーと値の組をメモリ上に保持し、キーを指定するだけで値を即座に取り出せる仕組みです。セルを1つずつ読みながら行を探す処理と比べると、10万行同士の突合で数分かかっていた処理が数秒から十数秒に収まるケースが多くあります。関数と違って結果を値として書き出すため、処理後のブックが重くならない点もメリットです。

開発工数の目安は、仕様が固まっていれば経験者で1〜2日、コードは100〜200行程度に収まります。現場でよくあるつまずきは、セルの値を1行ずつ読み書きするコードを書いてしまい、連想配列を使っても処理が遅いままになることです。シートとのやり取りは、範囲の値を配列にまとめて読み込み、処理後にまとめて書き出す形にしてください。キーの末尾にスペースが残っていると別のキーとして登録されるため、読み込み時にTrim関数を通しておくと安全です。実装の手順は次のとおりです。

• 「開発」タブの「Visual Basic」でエディターを開き、「ツール」の「参照設定」からMicrosoft Scripting Runtimeを有効にする
• 相手シートのキー列と値の列を配列に読み込み、ループで連想配列に登録する
• 判定シートのキーを配列で読み込み、Existsメソッドで存在を判定して結果を配列に格納する
• ScreenUpdatingをFalseにして描画を止めたうえで、結果の配列を判定列へ一括で書き出す

選定基準：データ件数・更新頻度・共有範囲の3軸で方法を決める

4つの方法に絶対的な優劣はなく、選ぶ際に見るべき軸は3つに絞れます。データ件数の軸では、両シートとも1万行未満なら関数、数万行から数十万行ならパワークエリかVBAが目安です。更新頻度の軸では、年に数回しか発生しないスポット作業に仕組みづくりの工数をかける必要はなく、使い慣れた関数で十分に対応できます。月次以上の頻度で繰り返す突合は、手順をクエリやマクロとして残せる方法を選ぶと、2回目以降の工数が大きく下がります。

共有範囲の軸は見落とされがちですが、運用が止まる原因になりやすい要素です。パワークエリは元データのファイルパスをクエリ内に保持するため、別の担当者のPCで開くとファイルが見つからないというエラーになります。VBAを含むマクロ有効ブックは、メールやインターネット経由で受け取るとセキュリティ設定によってマクロがブロックされる場合があります。部署をまたいで使う突合なら、共有フォルダーの固定パスを参照するなど、誰が開いても動く前提で設計するのが確実です。

処理が重くならない突合シートの設計ノウハウ

数万行を超える突合シートで処理速度を左右するのは、関数の種類よりもシートの作り方です。同じXLOOKUP関数でも、参照範囲の指定や計算方法の設定次第で、再計算にかかる時間は数倍以上変わります。ここでは再計算と描画の負荷を抑える設定と、突合結果を後工程で使いやすい形で出力する方法を解説します。

列全体参照を避けて範囲を限定する：再計算の負荷を抑える基本動作

数式でA列全体を指定する列全体参照は、入力の手間が少ない反面、最大1,048,576行が計算対象の候補になります。COUNTIF関数などは使用済みの範囲だけを計算しますが、SUMPRODUCT関数や配列数式に列全体を渡すと、100万行分の計算がそのまま発生します。参照範囲はデータの実際の行数に合わせて限定するのが、突合シートを軽く保つ基本動作です。最も手軽な方法は、元データを「挿入」タブの「テーブル」でテーブル化し、構造化参照で指定することです。

テーブル化した範囲は行を追加すると参照範囲も自動で広がるため、データが増えるたびに数式の範囲を直す作業が不要になります。OFFSET関数やINDIRECT関数で可変の範囲を作る方法は、避けたほうが無難です。これらは揮発性関数と呼ばれ、無関係なセルを1つ編集しただけでもブック全体の再計算を引き起こします。TODAY関数やNOW関数も同じ性質を持つため、突合シートでは基準日を数式ではなく値で入力しておきましょう。

計算方法を手動に切り替え、確定した結果は値として固定する

数万行の数式を含むシートでは、セルを1つ編集するたびに再計算が走り、作業が数秒ずつ止まります。この待ち時間を解消するには、「数式」タブの「計算方法の設定」を「手動」に切り替えてください。手動にした後は、元データを貼り付け終えた段階でF9キーを押し、必要なタイミングでだけ再計算を実行します。計算方法の設定はアプリケーション全体に適用されるため、同時に開いている他のブックも手動になる点に注意が必要です。

突合の結果が確定したら、数式を値に置き換えてブックを軽くします。判定列を選択してCtrl+Cでコピーし、Ctrl+Alt+Vで「形式を選択して貼り付け」を開いて「値」を選ぶ手順が最短です。値に固定した後は元データを差し替えても結果が変わらないため、固定する前に件数の検算を済ませておく必要があります。現場では、数式が残ったシートと値に固定したシートを「判定_数式版」「判定_確定版」のように別名で残す運用がよく採られます。

条件付き書式への依存を減らす：描画処理が速度低下を招く仕組み

不一致の行に色を付けるために条件付き書式を使うと、スクロールや再計算のたびに書式の判定と描画が繰り返されます。数万行に数式ベースのルールを設定すると、スクロールするだけで画面がカクつき、フィルター操作にも数秒の遅れが出ます。厄介なのは、行のコピーや挿入を繰り返すうちに同じルールが断片化し、管理画面に数百件のルールが並ぶ状態です。動作が重いと感じたら、「条件付き書式」の「ルールの管理」で対象を「このワークシート」に切り替え、件数を確認してください。

大量データの突合では、色で差異を示すのをやめ、判定結果を文字列の区分列として出力する設計に切り替えるのが効果的です。区分列があれば、オートフィルターで確認したい対象行をすぐに絞り込めます。色と違ってCOUNTIF関数やピボットテーブルで件数を集計できるため、報告資料への転記も容易です。どうしても色で示したい場合は、結果を値に固定した後に、差異のある行だけへ手動で塗りつぶしを適用すると描画の負荷を抑えられます。

突合結果は「一方のみ・両方にあるが値が異なる」で区分して出力する

突合の結果を「一致」と「不一致」の2つだけで出力すると、不一致の中身が混在し、原因の調査に余計な時間がかかります。不一致の中には、相手のシートにキー自体がないケースと、キーはあるが値が異なるケースが含まれるためです。前者は計上漏れや取り込み漏れ、後者は金額の誤入力や計上時期のずれが主な原因で、確認する相手もそれぞれ異なります。結果は最初から原因の種類ごとに区分して出力し、そのまま担当者へ振り分けられる形にしておくのが実務的です。

区分列は、XLOOKUP関数で取得した値と差額をもとに、IFS関数で判定を自動化できます。条件には取得結果がエラーかどうかと差額が0かどうかを順に指定し、それぞれに区分名を返す形が基本です。ただし、相手側にだけ存在する行は、片方のシートからの参照では検出できません。反対方向の突合も実行するか、パワークエリの完全外部結合で両側をまとめて抽出してください。区分ごとの件数を別シートに集計しておけば、月ごとの差異の推移も追えます。

大量データ突合でよくある失敗パターンと防止策

突合の失敗は、エラー表示ではなく、もっともらしい誤った結果として現れるのが厄介な点です。数式にエラーがなく不一致が少ないほど担当者は安心してしまい、誤りに気づくのは後工程で数字が合わなくなってからになります。ここでは現場で繰り返し起きている4つの失敗パターンを取り上げ、それぞれの原因と防止策を示します。

不一致ゼロを成功と判断する：突合前後で件数の検算を行う

不一致が0件という結果は、突合が成功した証拠とは限りません。参照範囲が途中の行で切れていると、範囲外の行は判定の対象にすら入らないためです。参照範囲を固定する$記号を付け忘れ、コピーした先で範囲が1行ずつずれていくのもよくある原因です。フィルターで一部の行を非表示にしたまま数式を貼り付けた場合も、非表示の行に数式が入らない、または値がずれて貼り付けられる事故が起こります。突合の成否は不一致の件数ではなく、全件が判定の対象になったかどうかで判断すべきです。

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

防止策として、突合の前後で件数と合計額が一致するかを検算する手順を必ず組み込んでください。元データの行数はCOUNTA関数、区分ごとの件数はCOUNTIF関数で求め、検算表として判定シートの上部に並べておくと確認が一目で済みます。金額を扱う突合では、件数だけでなく元データの合計額と区分ごとの合計額の一致もあわせて確認します。検算表の差が0でない限り結果を報告しないというルールを、チーム内で徹底するのが確実です。検算表で確認する観点は次のとおりです。

• 元データの行数と、判定シートで判定値が入った行数が一致しているか
• 区分ごとの件数の合計が、元データの行数と一致しているか
• 「一致」と「値の差異」の件数が、両方のシートから見て同じ数になっているか
• 元データの合計額が、区分ごとの合計額の和と一致しているか

検索方法を近似一致のまま実行し、誤った行に紐づけてしまう

VLOOKUP関数の第4引数とMATCH関数の第3引数は、省略すると近似一致で検索されます。近似一致は、昇順に並んだデータから検索値以下で最も近い値を探す仕組みです。そのため一致するキーが存在しなくてもエラーにならず、近い別の行の値を返してしまいます。エラーが出ないまま誤った金額が紐づくため、結果を見ただけでは誤りに気づけない点が最大の危険です。請求番号のように連番で並ぶキーでは、1つ手前の番号の金額が入っても違和感を持ちにくくなります。

防止策は、完全一致を既定とする関数へ切り替えることです。XLOOKUP関数やXMATCH関数は引数を省略しても完全一致で検索するため、指定漏れによる事故が起きません。VLOOKUP関数を使い続ける場合は、第4引数に必ずFALSEまたは0を入力し、作業を引き継ぐ際のチェック項目に含めてください。既存のブックを点検するときは、Ctrl+Fの検索画面で検索対象を「数式」にしてVLOOKUPを検索し、引数が3つしかない数式を洗い出す方法が効率的です。

重複キーによって1対多となり、結果の件数が水増しされる

重複の確認を省いたまま突合すると、結果の件数や金額が実際より多く集計される事故が起こります。典型的なのは、パワークエリのマージで相手側のキーが重複しているケースです。マージは一致する行をすべて結合するため、請求1件に同じ請求番号の入金行が2件あれば、請求が2行に増えて金額も二重に計上されます。XLOOKUP関数の場合は逆に最初に見つかった1件だけを返すため、2件目以降の入金が結果から消えてしまいます。

防止策は、前処理の段階で行ったキーの重複確認を、突合の直前にもう一度実行することです。マージの後は、結合後の行数が基準側のテーブルの行数を超えていないかを確認してください。行数が増えていれば相手側に重複があるため、「ホーム」タブの「グループ化」でキーごとに金額を合計してから結合し直すと1対1の突合に戻せます。件数が合わないときは突合の方法ではなく、キーの粒度が両側でそろっているかを最初に疑うのが近道です。

元データを直接上書きし、差異の原因をさかのぼれなくなる

差異が見つかったときに、元データのセルを直接書き換えて一致させてしまうのは、突合作業で最も後悔の大きい失敗です。修正した理由や元の値が残らないため、翌月に同じ差異が再発しても、前回どのように対応したかをチームの誰も説明できません。監査で修正の根拠を問われた際にも証跡を示せず、突合作業そのものの信頼性が損なわれます。「重複の削除」機能を元データに直接実行し、必要な行まで消してしまうケースも同じ系統の失敗です。

防止策は、元データを読み取り専用として扱い、修正はすべて別の場所に記録するルールを定めることです。具体的には、判定シートの右側に対応記録用の列を追加し、元データの値は変えずに記録だけを残します。受け取ったファイルは「受領日_元ファイル名」の形式で別フォルダーに保管し、作業用のコピーだけを編集してください。こうしておけば、差異の原因が元データにあったのか、処理の過程で生じたのかを後からでもたどれます。追加する列の例は次のとおりです。

• 対応内容（例：取引先へ確認中、振込手数料として処理）
• 修正後の値
• 対応者と対応日

別シートの突合作業を効率化した業務改善の事例

前処理と方法選定の考え方は、部門や業種を問わず共通のものです。一方で、つまずく箇所や効果の出る方法には、業務の性質による違いがあります。ここでは部門の異なる3つの典型的なケースをもとに、改善前の課題と取り組みの内容、効果の目安を紹介します。

経理部門：請求データと入金データの消込を月次で定型化した例

取引先の多い企業の経理部門では、月末に数千件の請求データと銀行の入金明細をエクセルで照らし合わせる消込作業が発生します。典型的な課題は、入金明細の振込名義がカタカナの略称で記録され、請求データの取引先名と一致しないことです。振込手数料が差し引かれて入金額が請求額と合わない行も多く、担当者が1件ずつ目視で判断するために月末の3営業日を費やしていた例もあります。作業の手順が担当者の頭の中にしかなく、休暇の時期には締めが遅れるという属人化の問題も抱えていました。

改善例で最初に着手したのは、取引先マスタに振込名義のカナ表記を登録する列を追加し、名義をキーとして照合できる状態を整えることです。そのうえでパワークエリのマージを使い、差額が数百円以内の行を振込手数料の差し引き候補として自動で区分する流れです。判断に迷う行だけを人が確認する形に変えたことで、消込にかかる時間は3営業日から半日程度まで短縮されています。翌月からは入金明細のファイルを差し替えて更新するだけで済むため、担当者が替わっても同じ品質で締めを迎えられます。

経理DXの実践ガイド｜推進ステップから活用ツール、成功のポイントまで徹底解説

情報システム部門：マスタ統合前の差分抽出を非属人化した例

基幹システムの刷新や企業統合に伴うシステム統合では、新旧2つの顧客マスタや品目マスタの差分を抽出する作業が発生します。数万件から十数万件のマスタを対象に、どちらか一方にしかないレコードと、項目の値が食い違うレコードを洗い出す必要があります。よくある課題は、特定の担当者が作ったVBAのマクロでしか差分抽出ができず、担当者の異動後に誰も修正できなくなることです。移行リハーサルのたびに1回2〜3日の抽出作業が発生し、プロジェクト全体の遅延要因にもなります。

改善例では、差分抽出の処理をマクロからパワークエリへ置き換え、各ステップを「適用したステップ」の一覧で誰でも確認できる状態にしました。新旧マスタのキーは全角半角とデータ型を統一してから完全外部結合で結合し、区分列で差分の種類を分類する構成です。処理の中身が画面上で追える形に変わったことで、別のメンバーへの引き継ぎが可能になり、1回あたりの作業も半日程度に収まるようになりました。統合後のマスタ管理ルールを定める際にも、このクエリを差分確認の標準手順として流用できます。

マスタデータ管理（MDM）とは？適切に運用する重要性とその手法を解説

小売業：在庫システムと実地棚卸データの照合時間を短縮した例

多店舗展開する小売業では、在庫システム上の理論在庫と、実地棚卸で数えた実在庫を突き合わせる作業が期末ごとに発生します。1店舗あたり数千SKU、20店舗で10万行を超えるデータを、本部の担当者がVLOOKUP関数で照合しているケースも少なくありません。課題になりやすいのは、ハンディターミナルから出力した商品コードの先頭ゼロが欠落し、在庫システムのコードと一致しない行が大量に発生することです。VLOOKUP関数を10万行に展開したブックは重くなり、保存のたびに数分待つ状態に陥ります。

改善例では、商品コードを取り込む段階でパワークエリの型を「テキスト」に固定し、桁数が足りないコードは先頭にゼロを補って13桁にそろえています。突合は店舗コードと商品コードの複合キーで行い、差異のある行だけを数量差の大きい順に並べて店舗へ再カウントを依頼する運用です。差異の大きい順に確認するよう変えたことで、全件を見直していた再カウントの対象を1割程度に絞り込めました。照合にかかる本部の作業時間も、2日から半日程度に短縮されています。

小売業のデータ分析で何がわかる？目的・やり方・費用をわかりやすく解説

まとめ：前処理と方法選定が大量データ突合の精度を決める

エクセルでの大量データ突合は、関数の知識だけでは精度が上がりません。キー列の表記ゆれとデータ型を統一し、重複を確認してから突合に入るという順番を守ることが、結果を信頼できるものにする前提です。方法は件数と更新頻度と共有範囲をもとに選び、突合の前後には必ず件数と合計額の検算を行いましょう。自社の突合作業を見直す際は、次の観点から着手すると改善の効果を早く確かめられます。

• 突合の目的が有無の確認なのか、値の差異の特定なのかを依頼者と合意したか
• 照合キーの全角半角、スペース、データ型を統一したか
• キーの重複を確認し、1対1と1対多のどちらで突合するかを決めたか
• 件数、更新頻度、共有範囲に合った方法を選んだか
• 突合の前後で件数と合計額を検算したか
• 元データを上書きせず、対応の記録を別に残しているか

「エクセルでの大量データ突合を効率化したいけれど、何から手をつけたらいいかわからない」「突合作業の自動化やデータ整備に専門家の知見を取り入れたい」という方は、データ領域の実績豊富な弊社、データビズラボにお気軽にご相談ください。

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

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