入金消込を自動化する4つの方法｜Excel関数・Power Query・専用ツールを比較

請求番号が両方のCSVにあり、1つの請求に1回の入金が対応する。こうした単純な照合なら、Excel関数でも十分に効率化できます。

しかし、同じ金額の請求が複数ある、振込名義が請求先名と違う、一部入金や合算振込が混ざる。ここまで来ると、関数を増やすほど「なぜこの結果になったのか」が分かりにくくなります。

入金消込の自動化は、行数だけで方法を決めると失敗しやすい作業です。

見るべきなのは、次の3点です。

- 一意に照合できる番号があるか

- 例外処理が毎月どの程度発生するか

- 担当者以外でも結果を確認できるか

この記事では、Excel関数、Power Query、VBA・スクリプト、専用ツールの4つを比較します。

■ 1. Excel関数が向くケース

請求CSVと入金CSVの両方に同じ請求番号があり、完全一致で照合できるなら、XLOOKUPやCOUNTIFを使う方法が最も手軽です。

例えば、請求番号を検索して入金額を返し、請求額との差額を別列で計算します。重複件数をCOUNTIFで確認すれば、同じ請求番号が複数ある行も見つけられます。

Excel関数が向くのは、次のような場合です。

- 照合キーが欠けていない

- 1請求に1入金が基本

- 表の列構成が毎月ほぼ変わらない

- 数式を確認できる担当者がいる

注意したいのは、XLOOKUPは通常、最初に見つかった一致を返すことです。同じ番号や名義が複数ある場合、値が返っただけでは一意に照合できたとは限りません。

■ 2. Power Queryが向くケース

毎月、同じ列構成のCSVを読み込み、不要列の削除、型の調整、表の結合まで行うなら、Power Queryが向いています。

一度手順を作れば、次月は元ファイルを差し替えて更新できます。複数CSVの取り込みや、請求データと入金データの結合も画面操作で組み立てられます。

Power Queryが向くのは、次のような場合です。

- 毎月同じ形式のCSVを使う

- データの整形や結合が中心

- Excel内で作業を完結したい

- 更新手順を社内で管理できる

ただし、振込名義の揺れ、同額請求の重複、一部入金などを細かく判定する場合は、変換手順が複雑になります。更新できることと、照合結果が正しいことは別なので、件数・金額・例外理由の確認が必要です。

■ 3. VBA・スクリプトが向くケース

CSV選択から整形、照合、結果保存までをボタン操作にまとめたい場合は、VBAやPythonなどのスクリプトが候補になります。

ルールをコードにできるため、関数やPower Queryだけでは扱いにくい条件も組み込みやすくなります。

向いているのは、次のような場合です。

- 毎月の手順と判定ルールが固まっている

- 複数の処理を一度に実行したい

- 行数が増えてExcel操作が重くなっている

- 変更時に保守できる担当者または依頼先がいる

導入前には、マクロ実行の可否、利用するPC、Excelのバージョン、引き継ぎ方法を確認します。作った人しか直せない状態を避けるため、入力条件と判定ルールを手順書に残すことも大切です。

■ 4. 専用ツールが向くケース

「一致した行」よりも「人が確認すべき行」を早く見つけたい場合は、業務ルールに合わせた専用ツールが向いています。

単に一致・不一致を出すだけではなく、次のように理由を分けて出力します。

- 入金未確認

- 一部入金・過入金

- 金額差

- 重複候補

- 曖昧一致

- 入力エラー

例えば、「振込名義は近いが同額請求が2件ある」「請求額より330円少ない」と理由が残れば、担当者は確認する行と順番を判断できます。

全件を無理に自動確定するのではなく、明確な一致だけを分け、例外を人へ戻す設計です。

■ 迷ったら、3つの質問で決める

● 質問1. 両方のCSVに同じ請求番号がありますか

あり、重複もないなら、まずExcel関数を試せます。

● 質問2. 作業の中心は整形ですか、照合判断ですか

列の削除や結合が中心ならPower Query、名義や金額差の判定が中心なら専用ルールが必要です。

● 質問3. 要確認の理由を他の担当者へ説明できますか

説明できない結果は、確認や引き継ぎで再び手作業になります。判定区分、理由、元データの行番号を残せる方法を選びます。

■ 自動化する前に用意するもの

いきなり実データ全体を渡す必要はありません。まずは個人情報をダミー値へ置き換えた5〜20行で、次を確認します。

- 請求CSVと入金CSVの列名

- 現在使っている照合キー

- 一部入金、合算振込、手数料差引の有無

- 最終的に確認したい区分

この4点が分かれば、Excel関数で足りるのか、専用化した方がよいのかを判断しやすくなります。

私は、請求CSVと入金CSVを選ぶと、入金未確認・金額差・重複・曖昧一致などを理由付きで一覧化する専用ツールを作成しています。

基本範囲は、請求CSV1形式＋入金CSV1形式、2ファイル合計5,000行まで。WindowsのEdgeまたはChromeで動き、Excelの追加設定や外部通信は不要です。

照合結果は、入力データと事前に決めたルールに基づく確認候補です。会計・税務判断、監査、仕訳、送金、銀行や会計サービスへのログインは行いません。

毎月の全件確認を「要確認だけを見る作業」へ変えたい場合は、サービス内容をご確認ください。

https://coconala.com/services/4319297