エクセル大量データの突合マクロ｜高速化の5STEPと失敗例

請求データと入金データ、発注実績と部品マスタなど、2つの表を行単位で突き合わせる作業は、多くの部署で毎月発生する定例業務です。数千行ならVLOOKUP関数で対応できますが、数万行を超えると再計算のたびにエクセルが固まり、作業が数時間単位に膨らみます。関数で処理しきれない分を目視で補う運用が続くと、見落としと手戻りが担当者の負担として積み上がっていきます。

突合の精度が落ちる原因は、関数やマクロの書き方よりも、その手前にあるデータの状態にあることが大半です。全角と半角の混在や末尾の空白、文字列として保存された数値は、画面上では見分けがつかないまま不一致として判定されます。処理を速くする工夫と同時に、キーの設計と前処理の手順を決めておくことが、結果を信頼できる突合の前提です。

本記事では、エクセルで大量データを突合するマクロを高速化する5つのステップと、突合前の標準化の手順、よくある失敗例を解説します。月次の突合に時間がかかっている場合は、自社データのキー列と件数を手元で確認しながら読み進めてください。

エクセルの大量データ突合とは｜関数だけでは処理しきれない理由

突合の自動化を検討する前に、何と何をどの粒度で突き合わせるのかを言語化しておくと、後の設計で迷いが減ります。ここでは突合と照合の違いを整理したうえで、VLOOKUPやCOUNTIFが数万行で遅くなる仕組みと、目視チェックが残る運用で起こる問題を順に確認します。

突合と照合の違い：原本との突き合わせか、有無の確認か

突合は、2つ以上のデータを項目単位で突き合わせ、金額や数量などの値まで一致しているかを確かめる作業です。請求書と入金明細を顧客コードと請求番号で結び、請求額と入金額が同じかを見るのが典型的な例です。照合は、ある値が相手側に存在するかどうかの確認を指す場面が多く、社員番号が名簿に載っているかを調べる作業が該当します。実務では両者が混同されやすく、依頼者が「照合して」と言いながら金額の一致まで求めているケースも珍しくありません。

マクロを作る前に、依頼内容がどちらに当たるかを依頼者と確認しておくのが、判定ロジックの作り直しを防ぐ第一歩です。有無の確認だけで足りるなら出力は「あり／なし」の2区分で済みますが、突合では値の相違まで拾うため、区分は4つ以上になるのが普通です。確認の場では、キー列の名称と比較する値の列、許容差の有無を1枚のメモに書き出して合意を取っておくと、後で揉めずに済みます。経理部門からの依頼は突合、名簿チェックは照合であることが多いものの、例外もあるため思い込みで作り始めないでください。

VLOOKUP・COUNTIFが数万行で遅くなる仕組み

VLOOKUP関数で完全一致を指定すると、検索値を照合元の先頭行から順に比べていく線形検索が行われます。照合元が5万行、検索側も5万行あると、最悪で25億回の比較が発生し、再計算のたびにこの処理が走ります。COUNTIFで重複や存在を確認する場合も同じ構造で、列全体を参照すると空白行まで走査対象に含まれる仕様です。5万行同士の突合では1回の再計算に数分かかり、セルを1つ編集するたびに待たされる状態になりがちです。

対策として関数側でできる工夫もあります。照合元をキーで昇順に並べ替え、VLOOKUPの検索方法をTRUEにすると二分探索になり、5万行でも1件あたり16回程度の比較で済みます。ただし完全一致を保証しないため、見つかった値とキーが等しいかを別の式で確かめる二段構えが必要です。Microsoft 365のXLOOKUPでも検索モードで二分探索を指定できますが、並べ替えの前提は変わりません。関数の工夫で数十万行まで耐えられても、判定区分の追加や証跡の出力まで考えると、マクロへの移行を検討する段階に入ります。

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

目視チェックが残る運用で生じる見落としと手戻り

関数で一致判定を出したあと、#N/Aになった行だけを目で確認する運用は多くの現場に残っています。問題は、目視の対象が数百行を超えると集中力が続かず、同じ行を二重に確認したり飛ばしたりする見落としが起こる点です。フィルターで絞り込んだ範囲に値を貼り付けると、非表示の行まで上書きされて集計がずれる操作ミスも頻発します。こうしたミスは翌月の締めで初めて発覚することが多く、原因の特定に過去データの再突合が必要になります。

手戻りの規模を把握するには、1回の突合にかかる時間を工程別に計測してください。関数の再計算、目視確認、差異の調査に分けて記録すると、どこを自動化すべきかが数字で見えます。5万行規模の月次突合では、目視確認と差異調査だけで1回あたり半日から1日かかる例も珍しくありません。人手の工程が全体の半分を超えているなら、関数の改良よりもマクロによる判定と区分付けを優先するほうが効果的です。計測は1か月分で足りるため、次回の締め作業から記録を始めてください。

突合をマクロで自動化すると解決できること

マクロ化の効果は、処理が速くなることだけにとどまりません。判定ルールをコードとして固定できるため、誰が実行しても同じ結果が得られ、実行記録も自動で残せる点が利点です。以下では、処理時間・再現性・証跡の3つの観点から、自動化によって変わる点を具体的な数値や運用例とともに見ていきます。

処理時間の短縮：数万行規模でも短時間で完了する

マクロで高速化する基本は、セルを1つずつ読まずに、範囲をまとめて配列に取り込む方式にすることです。キーの検索に連想配列（Scripting.Dictionary）を使うと、1件あたりの検索がほぼ一定の時間で済みます。照合元と検索側がそれぞれ5万行でも、比較回数は行数に比例する程度に収まり、一般的な業務用PCで数秒程度で処理が終わるのが目安です。関数で数分かかっていた再計算がなくなるため、結果を見ながら条件を変えて再実行する試行錯誤も現実的になります。

下表の数値は、Core i5相当・メモリ16GBのPCで、2列の値を比較する突合を想定した目安です。セルを1つずつ読み書きするループは、ワークシートとの往復が増えるぶん関数より遅くなる場合もあります。マクロ化するなら、最初から配列と連想配列を前提に組むのが近道です。30万行を超えるとエクセル自体のメモリ消費が増えるため、年度単位のデータは月別に分割するか、Power QueryやAccessへの移行も視野に入ります。

再現性の確保：担当者が変わっても同じ判定結果になる

手作業の突合では、前後の空白を消すか、ハイフンの有無を同一とみなすかといった細かな判断が担当者ごとに異なります。前任者が一致としていた行を後任者が不一致として扱い、差異件数が数十件単位でぶれる現象は、引き継ぎ直後によく見られる状況です。マクロに判定ルールを書き込めば、同じ入力からは必ず同じ結果が出るため、件数の変動がデータ自体の変化によるものだと説明できます。監査や上長への報告で差異件数の推移を示す場面では、この説明力が大きな武器です。

再現性を保つには、コードだけでなく実行前後の手順もそろえる必要があります。入力ファイルの置き場所やシート名が毎回変わると、マクロが別のデータを読み込んで誤った結果を出す典型的な原因です。実務では、マクロ実行ボタンを置いた操作シートに手順を番号付きで書き、ファイル名に年月を含めるルールを決めておくと運用が安定します。操作シートに前回の実行日と実行者を表示するセルを置いておけば、実行漏れにも気づけます。引き継ぎの際は、次の項目を確認してください。

• 入力ファイルの保存フォルダとファイル名の命名規則
• 読み込むシート名と見出し行の位置
• マクロを実行する順番と所要時間の目安
• 結果シートで最初に確認する区分と件数

証跡の確保：突合条件と実行結果を記録として残せる

内部統制や監査対応が関わる経理・購買の突合では、結果だけでなく「いつ、誰が、どの条件で実行したか」を示せる状態が必要です。関数による突合はセルの式が上書きされると経緯が追えず、どの時点の判定なのかを後から証明できません。マクロなら実行のたびに、日時や実行者、入力ファイル名、区分ごとの件数をログシートへ追記できます。実行者の名前は「Application.UserName」で取得し、PCのユーザー名と対応させておくのが一般的です。

データ監査とは？基本概念と実施手順、企業での活用ポイント

ログに残す項目は、監査で質問されやすい内容から逆算して決めます。差異件数だけを記録すると、件数が前月から増えた理由を問われたときに、どのファイルを使ったのかが追えません。入力ファイルの更新日時と行数も記録しておけば、元データの取り込み漏れを疑われた場面で即座に反証できる点が利点です。ログは1年分を目安に保持し、期末監査の資料として提出できる状態にしておきます。ログシートには保護をかけて手で書き換えられないようにし、月次の結果ファイルと同じフォルダに保存してください。

• 実行日時と実行者
• 入力ファイル名、更新日時、読み込んだ行数
• 適用した判定条件のバージョン
• 区分ごとの件数、金額合計、処理時間

突合の前に行う標準化：不一致の大半は表記ゆれから生じる

突合で不一致と判定された行を1件ずつ調べると、実際に値が違うケースより、同じ値の書き方が違うだけのケースが多く見つかります。マクロの判定精度を上げる近道は、判定ロジックを複雑にすることより、突合の前にデータの形をそろえることです。ここでは文字列、数値と日付、キーの重複という3つの観点で、突合前に済ませておく標準化の手順を解説します。

文字列の標準化：全角半角・空白・記号表記の統一

文字列のゆれを整える作業はデータクレンジングの一部で、突合前にそろえておきたいのは全角半角と空白、記号の表記です。取引先コード「A-001」と「Ａ－００１」は画面上で似ていても、文字コードが異なるため別の値として扱われます。VBAでは「StrConv」関数に「vbNarrow」を指定すると英数字と記号を半角に変換でき、全角空白も半角になる仕様です。そのうえで「Trim」関数を通せば、前後の空白を除去できます。

現場でよくあるつまずきは、StrConvを取引先名の列にも適用して、全角カタカナが半角カタカナに変わってしまうケースです。半角カタカナは帳票システムへの再取り込みでエラーになることがあるため、変換はキーとなるコード列に限定してください。記号については、ハイフンの類似文字として長音記号「ー」や全角マイナス「−」が混ざりやすく、Replaceで置換表を1文字ずつ当てるのが確実です。置換表は別シートに「変換前」「変換後」の2列で持たせると、後から文字を追加しやすくなります。

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

数値と日付の型の統一：文字列型の数値と日付シリアルの扱い

エクセルの日付は内部的に1900年1月1日を1とするシリアル値で保持され、表示形式で「2026/09/16」と見せているにすぎません。基幹システムから出力したCSVでは、同じ日付が「20260916」という8桁の文字列で入っていることが多く、見た目が近くても一致しません。数値も同様で、左上に緑の三角が出ているセルや、先頭にアポストロフィが付いたセルは、文字列として格納されている状態です。キーに日付や数値を含める場合は、両方のデータを同じ型に変換してから比較します。

データフォーマットとは？種類や特徴・選び方・変換方法まで徹底解説

判断基準としては、計算に使う列は数値や日付に、コードとして扱う列は文字列にそろえるのが原則です。日付の8桁文字列は「DateSerial」関数で年・月・日に分解して日付へ変換し、先頭の0に意味がある商品コードは「Format」関数で固定桁の文字列にします。現場でよくあるつまずきは、「CLng」で数値化した結果、「00123」と「123」が同一扱いになり、別商品のコードが一致してしまうケースです。変換ルールは列ごとに1行で書き出し、判定条件シートに残しておくと引き継ぎで迷いません。

キーの重複確認：1対1か1対多かを突合前に見極める

突合を始める前に、照合元と検索側のそれぞれでキーが一意かどうかを確認します。請求番号が一意だと思っていたら、分割請求で同じ番号が2行に分かれていたというケースは、経理データでよく見られるパターンです。重複したまま連想配列に登録すると、後から入った行で値が上書きされたり、登録時にエラーで止まったりします。事前確認はピボットテーブルでキー列を行に置き、値にキーの個数を集計して、2以上の行を抽出する方法が手軽です。

重複が見つかった場合の扱いは、キーとデータの関係が1対1、1対多、多対多のどれに当たるかで決まります。1対多であれば、明細側を集計して1行にまとめてから突き合わせるのか、明細単位で1件ずつ照合するのかを先に決める必要があります。多対多の場合はキーの設計自体が不十分なことが多く、日付や枝番を加えてキーを細かくする見直しが先です。判断に迷ったら重複件数が全体の何％かを出し、1％未満なら個別確認、それ以上ならキー設計の見直しを選ぶと進めやすくなります。

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

大量データを突合するマクロの作成手順5STEP

突合マクロは、設計、読み込み、登録、判定、書き出しの5段階に分けて組むと、途中で不具合が出ても原因の場所を特定しやすくなります。ここからは各段階で書くべき処理と、画面上での確認方法を順に説明します。全体像は、ワークシートとのやり取りを最初と最後の2回に絞り、間の処理をすべてメモリ上で行う形です。

STEP1：突合キーと判定基準の設計：単一キーと複合キーの使い分け

突合キーは、2つのデータで同じ取引や同じ対象を指していると判断するための列です。請求番号のように1列で一意に決まる場合は単一キーで足りますが、取引先コードと請求月の組み合わせでなければ特定できない場合は複合キーを使います。VBAで複合キーを扱うときは、列の値を区切り文字でつないで1つの文字列にし、連想配列のキーとして登録する方法が基本です。区切り文字には、データ内に現れない縦棒「|」やタブ文字を選ぶと、連結後の衝突を防げます。

区切り文字を省くと、「A1」と「23」をつないだ結果が「A12」と「3」の組み合わせと同じ「A123」になり、別の取引が一致と判定されます。判定基準は、キーが一致するかと、値が一致するかを分けて定義してください。比較対象が金額と数量の2列なら、両方一致を「一致」、どちらか相違を「値相違」とするように、区分と条件の対応表を別シートに先に作ります。この対応表を依頼者に見せて合意してからコードを書くと、完成後の手戻りが大きく減ります。

STEP2：対象範囲の配列読み込み：セルへのアクセス回数を減らす

VBAからセルを1回読むたびにワークシートとの間で受け渡しが発生し、5万行×5列を1セルずつ読むと25万回の往復になります。これを避けるには、範囲をまとめてVariant型の変数に代入し、2次元配列としてメモリ上に展開する方法が定石です。書き方は「arr = ws.Range(開始セル, 終了セル).Value」の1行で、行数に関係なく一度の読み込みで済みます。取り込んだ配列は「arr(行, 列)」の形で参照でき、添字が1から始まる点に注意してください。

最終行の取得には「ws.Cells(ws.Rows.Count, 1).End(xlUp).Row」を使い、A列の最下行から上方向に値のある行を探します。現場でよくあるつまずきは、キー列に空白セルが混じっているデータで「End(xlDown)」を使い、途中の空白で読み込みが止まってしまうケースです。見出し行を除いて2行目から読むのか、見出しごと読んで配列の1行目を飛ばすのかは、チーム内でどちらかに統一してください。読み込み直後に「UBound(arr, 1)」で行数を確かめ、画面上の件数と一致するかをイミディエイトウィンドウで見ておくと安心です。

STEP3：連想配列へのキー登録：照合元を一度の走査で保持する

連想配列を使うには、VBE（Visual Basic Editor）の「ツール」メニューから「参照設定」を開きます。一覧から「Microsoft Scripting Runtime」を探してチェックを入れ、「OK」を押してください。これで「Dim dic As New Scripting.Dictionary」と宣言でき、入力補完も効くようになります。照合元の配列を1行目から最終行まで1回だけ走査し、キーを「dic.Add キー, 行番号」で登録する流れです。値そのものではなく行番号を登録しておくと、判定時に配列の任意の列を参照でき、比較する列が増えてもコードの修正が少なく済みます。

登録時の注意点は、重複キーの扱いを明示することです。「Add」は同じキーを2回登録しようとすると実行時エラー457で停止するため、事前に「dic.Exists(キー)」で存在を確かめる分岐を入れます。重複だった場合は別の連想配列に重複キーとして記録しておくと、後で重複区分として出力できます。大文字と小文字を区別したくない場合は、最初の登録より前に「dic.CompareMode = TextCompare」を設定しておいてください。

STEP4：一致・不一致の判定：区分コードを付与して仕分ける

検索側の配列を1行ずつ走査し、キーが連想配列に存在するかを「Exists」で確かめます。存在しなければ「検索側のみ」とし、存在すれば登録された行番号から照合元の値を取り出して金額や数量を比較する流れです。判定結果は文字列ではなく区分コードで持たせると、後工程のフィルターや件数集計が扱いやすくなります。照合元で一度も参照されなかったキーは、参照済みの印を別の配列に付けておき、判定ループの後にまとめて「照合元のみ」として拾う仕組みです。

区分コードは、十の位で大分類、一の位で詳細を表す2桁構成にしておくと、区分を後から追加しても既存コードを振り直さずに済みます。10番台を一致、20番台を値相違、30番台を片側のみ、40番台を重複とし、21を金額相違、22を数量相違と割り振る方法です。現場でよくあるつまずきは、1行の中で金額も数量も違うときに一方しか記録されず、差異調査で片方を見落とすケースです。複数の相違を同時に持てるよう、区分コードとは別に、相違した列名を連結した備考列を用意してください。

• 11：キー・値とも一致
• 21：金額のみ相違／22：数量のみ相違／23：金額・数量とも相違
• 31：照合元のみに存在／32：検索側のみに存在
• 41：キーの重複あり

STEP5：結果の一括書き出しと実行ログの記録

判定結果は、読み込み時と同じ行数の配列に格納しておきます。最後に「ws.Range(開始セル).Resize(行数, 列数).Value = 結果配列」の1行で、シートへまとめて書き出します。1セルずつ書き込むと5万行で数分かかる処理も、一括書き出しなら数秒で済む計算です。結果シートは入力データのシートとは分け、元データに区分列を直接書き込まないようにしてください。元データを上書きすると、判定を誤ったときにやり直す材料が失われるためです。

書き出しと同時に、ログシートの最終行へ実行記録を1行追記します。日時は「Now」関数、実行者は「Application.UserName」で取得できます。処理時間は、開始時と終了時の「Timer」関数の差を秒単位で記録するのが簡単です。区分ごとの件数は判定ループの中でカウントしておくと、ログ用の集計処理を別に書かずに済みます。処理時間を毎月記録しておけば、データ量の増加で分割実行が必要になるタイミングも数字で判断できます。

突合結果の精度と処理速度を保つ実務のポイント

マクロが動くようになった後に問われるのは、差異が調査しやすい形で出ているか、処理が遅くならないか、他の人が保守できるかという運用面の品質です。ここでは差分の区分方法、許容差の設定、処理速度の底上げ、属人化の回避という4つの論点について、判断基準と設定値の目安を示します。

差分の3区分：片側のみ・両方あるが値相違・重複

突合の差分は、原因と対応の担当が異なる3つの区分に分けて出力すると、調査の振り分けが速くなります。片側のみに存在する行は計上漏れや入力漏れ、値相違は誤入力や値引きの反映漏れ、重複は二重計上や分割処理が主な原因です。原因が違えば確認先も変わり、片側のみは入力部門、値相違は取引先、重複はシステム担当に回すといった分担が可能です。区分を混ぜて1つのリストにすると、担当者が自分の対応分を探す手間だけで1時間以上かかることもあります。

件数の目安も判断材料になります。差分全体が総件数の1％以内なら個別の調査で対応できますが、5％を超える場合は前処理かキー設計に問題があると考え、差分を10件ほど抜き出して傾向を確認してください。特定の取引先や月に差分が偏っていれば元データの出力条件が変わった可能性が高く、突合ロジックを直す前に出力元へ問い合わせるのが近道です。区分別の件数をログから月次でグラフ化しておくと、異常の兆候に早く気づけます。

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

許容差の設定：金額の丸め誤差や日付のズレをどこまで許容するか

金額の突合では、税込と税抜の換算や端数処理の違いで、1円から数円の差が出ることがあります。この差をすべて値相違として出力すると、調査不要な行が差分リストの大半を占める事態になりがちです。許容差は、差額の絶対値が閾値以下なら一致とみなす条件として定義し、「Abs(金額1 - 金額2) <= 許容額」の形で判定します。日付も同様で、請求日と入金日を比べる場合は、支払サイトに応じて数日程度の幅を認める設計が一般的です。月末が休日の場合は入金が翌営業日にずれるため、その日数も加味して幅を決めます。

許容差の値は、担当者の感覚ではなく根拠のある数字で決めてください。税込換算の端数なら1明細あたり最大1円の差が生じるため、明細行数を上限とする設定が理屈に合います。振込手数料を差し引いて入金する取引先が多い場合は、手数料込みで一致した行を別区分にするほうが、後の仕訳で扱いやすい設計です。許容差で一致とみなした行にも区分コードを分けて付けておくと、許容範囲が妥当だったかを後から検証できます。許容差を変更した月はログに旧値と新値を記録し、前後の件数変化を比べられるようにしておくのが安全です。

処理速度の底上げ：画面更新と自動計算の制御、件数に応じた分割実行

配列と連想配列で組んだマクロでも、書き出しの前後で画面の再描画や数式の再計算が走ると、処理時間が数倍に延びることがあります。マクロの冒頭では「Application.ScreenUpdating = False」で画面更新を止めます。あわせて「Application.Calculation = xlCalculationManual」で自動計算を止め、終了時に元へ戻すのが基本形です。現場でよくあるつまずきは、途中でエラーが発生して終了処理まで到達せず、計算方法が手動のまま残るケースです。「On Error GoTo」でエラー時にも復帰処理へ飛ばす構成にしてください。計算方法はアプリケーション全体の設定のため、手動のまま残ると別のブックの数式まで更新されなくなります。

件数による分割の判断基準は、処理時間とメモリの2つで考えます。32ビット版のエクセルは使えるメモリが約2GBに制限されるため、30万行×20列を超えるような配列ではメモリ不足のエラーが出やすい環境です。この場合は月別や拠点別にデータを分けて順に処理し、結果を最後に結合する方式に切り替えます。64ビット版でも、1回の実行が5分を超えるなら、分割して途中経過を確認できる形にするほうが安全です。自社のエクセルが何ビット版かは、「ファイル」→「アカウント」→「Excelのバージョン情報」で確認できます。

バッチ処理とは？仕組み・メリット・実装の考え方をわかりやすく解説

属人化の回避：判定条件をコード外に切り出して可読性を保つ

許容額や区分コード、比較する列番号をコードの中に直接書き込むと、条件を1つ変えるだけでもVBEを開いて該当箇所を探す必要が生じます。VBAを読める担当者が異動した途端に、許容額を1円から10円に変える程度の変更すらできなくなる事態は珍しくありません。対策は、判定条件を「設定」シートに表形式で置き、マクロの冒頭でその表を読み込む構成にすることです。設定シートにはキー列、比較列、許容差、区分名を1行1条件で並べ、変更履歴の列も設けてください。

コード側の書き方も、後任者が保守できるかを左右する要素です。処理は読み込み・登録・判定・書き出しのプロシージャに分けて作成します。各プロシージャの先頭に何を受け取り何を返すかを1〜2行のコメントで書いておくと、修正箇所を探す時間が大きく減ります。変数名は「arr」や「i」だけで済ませず、「srcData」「keyCol」のように役割がわかる名前にするのが実務の定石です。引き渡しの前には、次の観点で確認してください。

• 条件の変更が設定シートだけで完結するか
• モジュールの先頭に「Option Explicit」を宣言し、未定義の変数を防いでいるか
• テスト用の小さなデータと、期待される区分件数がブック内に保存されているか
• 実行手順と過去のログが、VBAを読めない人にも理解できる形で残っているか

大量データの突合マクロでよくある失敗パターン

突合マクロのトラブルは、コードの文法ミスよりも、データの性質を見誤ったことによる判定の誤りとして表に出ます。エラーで止まらずに誤った結果を出すため、気づくのが遅れやすい点が厄介です。ここでは実務で頻度の高い5つの失敗について、症状と原因の見分け方、再発を防ぐための対策を解説します。

データ型の不一致：見た目が同じでも一致と判定されない

症状として多いのは、画面上ではまったく同じコードなのに、マクロの結果では片側のみに分類される行が大量に出るパターンです。原因の大半は、一方のキーが数値型、もう一方が文字列型になっている型の不一致です。連想配列は数値の1001と文字列の「1001」を別のキーとして扱うため、型が混在すると一致しません。確認するには、イミディエイトウィンドウで「? TypeName(arr(2, 1))」を実行し、Double型かString型かを見ます。

対策は、連想配列に登録する直前と検索する直前の両方で、キーを「CStr」関数で文字列に変換する処理を必ず通すことです。片方だけに変換を入れると、登録側と検索側で型がずれて同じ失敗が再発します。キーの変換は「MakeKey」のような専用の関数にまとめ、登録と検索の両方から呼び出す形にしておくと、変換漏れを構造的に防げます。「1001.0」のように小数点付きで出力するシステムもあるため、テスト時に数件のキーを文字列化した結果を目で確かめてください。

不可視文字の混入：末尾の空白・改行・制御文字

型をそろえても一致しない場合、次に疑うのが画面に表示されない文字の混入です。基幹システムやWebフォームから出力したデータには、下に挙げる文字が紛れ込みやすくなります。LEN関数で文字数を数え、見た目の文字数より多ければ不可視文字が入っていると判断できます。どの文字かを特定するには、「=CODE(RIGHT(A2,1))」で末尾1文字の文字コードを調べるのが手早い方法です。VBAのTrim関数が除去するのは文字コード32の半角空白だけで、改行や160番の空白は残る点に注意が必要です。

• 半角空白（文字コード32）：末尾に付くと画面では判別できない
• セル内改行（文字コード10）：Alt＋Enterや外部データの改行が残る
• タブ文字（文字コード9）：テキストの貼り付けやCSVで混入しやすい
• 改行なしスペース（文字コード160）：Webページからのコピーで入りやすい

除去は、標準化の段階で「Replace」関数を文字コードごとに当てる方法が確実です。改行なしスペースは「Chr(160)」を半角空白に置き換えます。改行とタブは「vbCr」「vbLf」「vbTab」を空文字にし、最後にTrimを通す順番にすると取りこぼしがありません。置換の対象文字は設定シートに一覧で持たせ、新しい文字が見つかったら行を追加するだけで対応できる形にしておくのが得策です。一度この仕組みを作れば、不可視文字による不一致は月次運用の中でほぼ再発しなくなります。

二重ループによる処理時間の膨張：行数の積で計算量が増える

連想配列を使わずに、検索側の各行について照合元を先頭から探す「For」の二重ループで組むと、比較回数は両方の行数の積になります。処理の増え方を表す指標を計算量と呼び、1万行同士なら1億回、5万行同士なら25億回と、行数が5倍になると比較回数は25倍に膨らみます。テストで使った1,000行のデータでは一瞬で終わるため、本番データで初めて何十分も固まる事態に気づくのが典型的な失敗の流れです。処理中はエクセルが応答なしと表示され、強制終了すると途中の結果も消えてしまうのが実情です。

二重ループを見分けるには、コードの中で「For」が入れ子になり、内側のループが照合元の全行を回していないかを確認します。該当する場合は、照合元を連想配列に登録する形に書き換えるだけで、比較回数は行数の和に近い水準まで下がるのが一般的です。部分一致や範囲条件での照合のように、二重ループが避けにくい処理もあります。その場合は照合元を並べ替えて探索範囲を絞るか、Power Queryのマージ機能に任せる選択肢を検討してください。書き換えの前後で1万行のテストデータの処理時間を「Timer」で計測しておくと、効果を説明しやすくなります。

重複キーの見落とし：1対多の関係を1対1として処理してしまう

連想配列への登録で「Exists」による確認を省き、既存キーへの代入で上書きする書き方にすると、重複キーはエラーを出さずに最後の1行だけが残ります。同じ請求番号の明細が3行あっても照合元には1行分しか登録されず、残り2行は突合の対象外という状態です。検索側から見るとすべて一致に見えるため、合計金額を確かめるまで誰も気づかないのが怖いところです。卸売や製造の現場では、分納や分割請求の明細でこの失敗がよく起こります。

防ぐ手順は2つあります。1つ目は、STEP3で触れたとおり登録時に「Exists」で重複を検出し、重複キーの一覧を別シートに出力することです。2つ目は、突合の前後で照合元と検索側の金額合計を比べる検算を入れることです。区分ごとの金額合計が元データの合計と一致しなければ、どこかで行が消えているか二重に数えられています。検算の結果はログに残し、不一致なら結果シートを開く前に警告メッセージを出すようにしておいてください。

最終行の固定指定：データ追加時に対象範囲から漏れる

マクロを作った時点の行数に合わせて、読み込み範囲を「5000行目まで」のように固定してしまう失敗も後を絶ちません。翌月にデータが5,200行に増えると、超えた200行は何の警告もなく対象外になり、結果は正常に終わったように見える点が厄介です。範囲を名前定義で固定している場合も、名前の参照範囲は自動では広がらないため同じ問題が起こります。テーブル機能を使っていないブックでは、特に起こりやすい失敗です。毎月の件数が数％ずつ増えるデータほど、発覚までに数か月かかる傾向があります。

対策は、固定値をやめて最終行を実行のたびに取得することです。STEP2の「End(xlUp)」に加えて、元データを「挿入」タブの「テーブル」でテーブル化しておく方法も有効です。テーブル化した範囲は「ListObjects(1).DataBodyRange」で参照でき、行が増えても自動で追従します。読み込んだ行数はログに記録し、前月から大きく減っていないかを実行後のメッセージで表示すると、範囲漏れにその場で気づけます。

事例：大量データの突合を自動化した3つの取り組み

ここまでの手順とポイントが実際の業務でどう組み合わさるのかを、業種の異なる3つのモデルケースで確認します。いずれも数万行規模の突合を関数と目視で回していた状態から、マクロ化に切り替えた場面を想定した例です。前処理やキー設計のどこに手をかけたかに注目すると、自社の業務に置き換えるときの手がかりになります。

卸売業：請求データと入金データの月次突合を自動化した例

取引先が約800社、月間の請求明細が約4万行ある卸売業の経理部門を想定します。販売管理システムの請求データと銀行の入金明細を、VLOOKUPと目視で突き合わせていた状態です。入金明細の振込名義は半角と全角のカタカナが混在し、請求側の取引先名と一致しない行が毎月1,000件前後発生していました。担当者2名が締め後の3営業日を突合と消し込みに充て、繁忙月は残業で乗り切っていたという前提です。消し込み漏れが翌月に持ち越される件数も、毎月20件ほどありました。

マクロ化では、振込名義を「StrConv」の「vbWide」で全角にそろえ、「カ）」「（カ」などの法人略称を除く置換表を先に整備しました。名義だけでは同名企業を区別できないため、振込名義と取引先コードの対応マスタを作り、取引先コードと請求月の複合キーで照合する方式です。金額は振込手数料分として最大880円の差を許容し、手数料を差し引いた入金は別区分で出力する設計です。この構成なら調査が必要な差分は月50〜100件程度に絞られ、突合と消し込みの工数を3営業日から半日程度まで縮められる計算になります。

製造業：部品マスタと発注実績の照合で重複計上を発見した例

部品点数が約1万2,000点、年間の発注実績が約15万行ある製造業の購買部門を想定します。部品マスタと発注実績の品番を照合し、単価がマスタと一致しているかを確認する作業で、COUNTIFによる存在確認だけを行っていた状態です。品番は旧体系と新体系が混在しており、旧品番「AB-1234」と新品番「AB1234-00」が同じ部品を指すケースも含まれていました。15万行を関数で扱うため、ファイルを開くだけで1分近く待たされる状況でした。

マクロでは、品番からハイフンを除いて枝番を切り分けた正規化品番を作り、新旧対応表で旧品番を新品番に置き換えてから登録しました。登録時に「Exists」で重複を検出すると、正規化後に同一となる品番が約300組見つかったのが最初の発見です。そのうち一部では同じ発注が新旧両方の品番で計上されており、年間の購買金額が実態より多く集計されていたことが判明しています。単価の突合に入る前に重複を洗い出す手順を置いたことが、金額面の誤りを見つける決め手になりました。

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

人材サービス業：勤怠データと請求明細の突合手順を標準化した例

派遣スタッフ約2,000名の勤怠データと、派遣先企業ごとの請求明細を週次で突き合わせる人材サービス業の管理部門を想定します。勤怠は打刻システムから、請求は販売管理システムから出力され、スタッフ番号と勤務日の複合キーで照合する必要がありました。拠点ごとに担当者が独自のExcelファイルで確認しており、休憩時間の端数処理や深夜時間帯の扱いが拠点間でそろっていないことが課題でした。週次の締めが月曜午前に集中し、担当者の負担が偏っていたことも見直しのきっかけです。

標準化では、判定条件を設定シートに集約し、労働時間の許容差を1日あたり15分以内、深夜時間を22時から翌5時と定義しました。全拠点が同じマクロ入りブックを共有フォルダから使う運用に切り替え、実行ログは本社の管理者が週1回確認する体制です。拠点ごとにばらついていた差分の基準がそろったことで、請求誤りの傾向を拠点横断で比較できるようになりました。切り替え時は、過去3か月分のデータで旧手順とマクロの結果を並べ、区分件数の差を1件ずつ説明できる状態にしてから移行するのが安全です。

まとめ：エクセルの大量データ突合は前処理とキー設計で精度が決まる

エクセルで大量データを突合するときは、処理速度と判定精度を分けて考えると改善の順番が見えます。速度は、配列への一括読み込み、連想配列によるキー検索、結果の一括書き出しの3点を押さえれば、数万行でも数秒単位に収まる水準です。精度は、文字列・型・不可視文字の標準化と、1対1か1対多かを見極めたキー設計でほぼ決まります。

着手する際は、手元の突合業務の件数と所要時間を工程別に測り、次の観点を1つずつ確認するところから始めてください。

• 突合と照合のどちらが求められているか、依頼者と合意できているか
• キー列の型、全角半角、不可視文字をそろえる手順が決まっているか
• キーの重複件数を把握し、1対1か1対多かを判断できているか
• 判定区分と許容差が設定シートに書き出されているか
• 実行ログと金額合計の検算が毎回残る仕組みになっているか

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

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

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