スプレッドシートで取引を照合する方法（手作業で行を突き合わせることなく）

2つの取引リストを照合するとは、4種類の差異を見つけ出すことです。一方のリストにない行、もう一方のリストで重複している行、金額の不一致、そして同じ決済が異なる期間に計上されているケースです。それ以外の作業はすべて、これら4つの差異を処理するための記帳作業にすぎません。

直感的には、2つのファイルを並べて行を突き合わせ始めたくなります。しかし、それが通用するのは200行程度までです。それを超えると、午後を丸々潰した挙句、誰も監査できない数字が出来上がるだけになってしまいます。

本ガイドでは、なぜスプレッドシートがこの特定のタスクで苦戦するのか、人々がよく使う3つの回避策、およびそれぞれの限界について解説します。これはデータワークフローに関するものであり、会計上のアドバイスではありません。

スプレッドシートでの照合が、見た目以上に難しい理由

スプレッドシートはセルを比較します。一方、照合はイベント（取引）を比較するものであり、同じイベントが両側でまったく同じように見えることは滅多にありません。

カード決済は、自社の元帳には1回しか表示されなくても、決済代行会社の出力データでは請求額と手数料に分割されて2回表示されることがあります。仕入先からの請求書は、一方のシステムでは参照番号がINV-0042、もう一方ではINV42となっているかもしれません。31日に開始された振込が翌月1日に決済されると、月全体の数字が狂ってしまいます。

これらはどれもデータのエラーではありません。同じ現実を記録している2つのシステムにおける通常の姿であり、検索数式を単に使うだけでは解決できません。

また、毎月のように人々が陥る端数処理の罠もあります。完全な浮動小数点精度で保存された通貨値は、小数点第4位で異なる場合があるため、画面上は1,204.50と表示されている2つの金額が、完全一致のテストで不一致と判定されてしまうのです。

データ量によっても問題の性質が変わります。50行であれば、人間が両方のリストを頭の中で把握できます。しかし、5,000行になると、このタスクは大量の一致データの中に隠された一握りの例外を探し出す作業になります。人間の注意力は、こうした作業にはあまり向いていません。

これによって生じるコスト

月末の「しっぽ（未処理タスク）」。 最初の90%の行は数分で一致します。しかし、残りのわずかな行の処理に何時間もかかります。なぜなら、それら1つひとつについて、4つの差異タイプのどれに該当するのかを人間が判断しなければならないからです。

監査不可能な結果。 目視で照合を行うと、ファイルを閉じた瞬間にその作業プロセスが失われます。6週間後には、なぜその2つの行が同じ決済として処理されたのか、誰も再現できなくなります。

見逃されるエラー。 重複データが誤った相手先と照合されてしまうと、合計値としては相殺され、きれいに照合できたように見えてしまいます。合計金額が一致していることは、行レベルで一致していることの証明にはなりません。

これら3つのコストは連鎖します。未処理タスクが長引くと疲労が生じ、疲労は手抜きを生み、手抜きによって誤った照合が正しいものとして記録されてしまうのです。

よく試される回避策

選択肢1：照合を始める前に合計値を比較する

行単位ではなく、まずはグループごとの合計値を比較することから始めます。SUMIFS を使って、月別、口座別、または取引先別に両側の合計を算出し、2つの列を並べて比較します。

これにより、時間を費やす前に差異の場所を特定できます。12ヶ月のうち11ヶ月が1セント単位まで一致していれば、1年分ではなく1ヶ月分だけを照合すればよくなります。

これは非常に有用で、手戻りを防げます。ただし、グループ合計は不一致がどこにあるかを教えてくれるだけで、どの行が原因であるかは分かりません。また、同じグループ内で2つのエラーが相殺し合っている場合、それらは見えないままになってしまいます。

選択肢2：照合キーを作成して検索する

取引を特定するフィールド（通常は日付、金額、およびクレンジング済みの参照番号）を結合して1つのキーを作成します。そして、双方向で XLOOKUP を使用し、一方にあって他方にない行を見つけ出します。

重複を検出するために、同じキーに対して COUNTIFS も追加します。検索関数は最初に見つかった一致のみを返し、2つ目以降を黙って無視してしまうためです。また、キーに組み込む前に金額を ROUND 関数で小数点以下2桁に丸めておくことで、前述した浮動小数点の不一致を解消できます。

これは最もよく使われる実用的な方法であり、ほとんどの月はこれで対応できます。しかし、その限界は構造的なものです。つまり、両側で同じ意味を持つキーが必要になります。参照番号のフォーマットが異なっていたり、手数料が2行に分割されていたりすると、この方法はすぐに破綻します。

選択肢3：4つの差異タイプを個別に処理する

1回でまとめて照合するのではなく、4つのより絞り込んだチェックを実行します。不足している行は双方向の検索から特定します。重複はキーのカウントから特定します。金額の不一致は、参照番号のみで照合した後に値を比較することで特定します。タイミングのズレは、正確な日付ではなく、一定の日付範囲内で照合することで特定します。

正しく行えば、これが最も確実な方法です。なぜなら、一致しなかったすべての行が「その他」の山に放置されることなく、名前の付いたカテゴリに分類されるからです。

同時に、最も手間がかかる方法でもあります。4回チェックを通すということは、片側につき4つの作業用列が必要になることを意味し、出力データの列順が変わるたびに仕組み全体を再構築しなければなりません。この方法が前提とする準備ステップについては、当社のデータのクレンジングと重複排除に関するガイドで解説しています。

共通する限界。 これら3つの方法はすべて、一方の1行がもう一方の1行に対応していることを前提としています。しかし、1回の決済に40件の取引が含まれていることもあります。1つの支払いが、請求額＋手数料＋返金として処理されることもあります。どちらの場合も、キー照合は機能しません。ここで午後が丸々潰れてしまうのです。

Powerdrill Bloomで取引を照合する方法

ステップ1：両方のファイルをアップロードする

元帳の出力データと相手方の明細書を一緒にアップロードします。Powerdrill Bloomが両方のプロファイルを分析するため、照合を開始する前に、一致しない列名、異なる日付フォーマット、一貫性のない参照番号のスタイルなどを視覚的に確認できます。

ステップ2：自然言語で照合ルールを指示する

4つのカテゴリを名前で指定して依頼します。一方のファイルに存在し、もう一方に存在しない行や、重複している参照番号を抽出するよう指示します。さらに、指定した許容範囲を超える金額の不一致や、日付が数日ずれているエントリの抽出を求めます。

そして、最も厄介なケースを解決するための質問を投げかけます。一方の複数行の合計が、もう一方の単一の行と一致する組み合わせを尋ねるのです。これこそが、キー照合では対応できない「複数対1」のケースです。

ステップ3：チャート、レポート、またはスライドをエクスポートする

例外リスト、カテゴリ別の未一致金額のサマリー、または決算ファイル用の簡単なメモを出力します。

毎月照合の仕組みを再構築するよりも、この方法が優れている理由

	手動で行う場合	Powerdrill Bloom
参照番号のフォーマットが異なる	まず手作業で両側をクレンジングする	違いを説明して指示するだけ
複数行対1行	手動でグループ化する	どの行の合計が相手方と一致するか尋ねる
重複データ	両側にカウント用の列を追加する	例外リストに自動で含まれる
翌月以降	すべての作業用列を再構築する	新しい出力データをアップロードするだけ

表の1行目の作業に、最も多くの時間が費やされます。2つのシステム間で一致するように参照番号をクレンジングする作業は、それ自体では何も価値を生まない準備作業です。しかも、出力フォーマットが変わるたびにやり直す必要があります。

よくある間違い

合計金額の一致をもって照合完了とみなす。 反対方向の同じ大きさの2つのエラーがあると、合計金額は完全に一致してしまいます。合計値だけでなく、行数や未一致の金額も確認してください。

金額のみで照合する。 実際の元帳では、複数の取引が同じ金額になることがよくあります。金額だけで検索を行うと、誤った組み合わせが紐付けられ、それが正しいかのように表示されてしまいます。

端数の違いを無視する。 画面上で同一に見える値であっても、一致テストで不一致となることがあります。比較する前に、両側の値を同じ精度に丸めてください。

チェックの方向を忘れる。 一方向の検索では、2つ目のファイルに不足している行は見つけられますが、1つ目のファイルに不足している行は見落とされます。必ず毎回、双方向で実行してください。

照合できた行をその都度削除する。 効率的に思えるかもしれませんが、監査証跡が失われてしまいます。代わりにステータス列を使って行にフラグを立て、元のデータはそのまま残してください。

期間がクローズする前に照合を行う。 遅れて発生した仕訳はタイミングのズレを生みますが、期間が閉まれば自然と解消されます。期中にこれらを追いかけるのは、作業の無駄になります。

結論

照合とは「分類」の問題であり、「突き合わせ」の問題ではありません。一致しないすべての行を、不足、重複、金額不一致、期間不一致のいずれかに分類すれば、残りの作業はわずかになり、説明も容易になります。

照合コストが高くなる原因は、毎月その仕組みを再構築することにあります。特に参照番号が一致しない場合や、1つの決済が複数の行に対応している場合はなおさらです。決算作業でそこに時間を取られているなら、両方の出力データで Powerdrill Bloomをお試しください。また、当社のVLOOKUPを使わずに2つのExcelファイルを結合する方法や、予算対実績レポートの作成方法に関するガイドもご覧ください。関連するワークフローについては、経費精算書（expense report）やキャッシュフロー分析（cash flow analysis）のページで解説しています。

よくある質問

取引の照合とはどういう意味ですか？

同じ活動に関する2つの記録が一致していることを確認し、残されたすべての差異について説明できるようにすることです。その説明は、不足している行、重複、金額の不一致、タイミングのズレの4つのグループに分類されます。

Excelは2つのリストを自動的に照合できますか？

単体では不可能です。Excelは、主に検索、カウント、条件付き合計などの部品を提供しますが、照合ロジック自体は自分で構築する必要があり、出力データが変更されるたびに再構築しなければなりません。

同じに見える2つの金額が一致しないのはなぜですか？

通常は、保存されている精度の問題です。小数点以下2桁で表示されている値であっても、その裏にさらに細かな数値が保持されている場合があり、そのため完全一致の比較で不一致となります。両側の値を同じ精度に丸めることで解決します。

複数の行として表示される1つの決済はどのように処理すればよいですか？

細かい行をグループ化し、そのグループ合計を単一の相手方データと比較します。行レベルのキー照合ではこのケースを表現できないため、これが手作業が発生する最も一般的な原因となっています。

一致しなかった行は削除すべきですか？

いいえ。それらは残しておき、カテゴリと理由を記録するステータス列を追加してください。削除してしまうと、後から照合の正当性を証明するための監査証跡が失われてしまいます。