入金消込をExcelで整理｜一括入金を請求ごとに配賦して確認

※本記事にはPRを含む場合があります。

こんにちは。『脱会社員』脱サラを応援し続けるブログ、運営者の『なかびー』です。

請求書を二枚出した後、銀行口座に一回の振込がありました。金額は二枚の請求を足した額より少なく、明細にはどちらの請求にいくら充てたのか書かれていません。さらに後日、別の振込があった場合、入金の合計だけを見て「すべて消し込み済み」としてよいのでしょうか。

この記事では、請求AとB、入金XとYを用いた架空の例で、Excelに「請求」「入金」「配賦」という三つの表を作ります。配賦とは、確認できた一回の入金を、どの請求にいくら対応させるかを記録することです。相手からの支払通知などで内訳を確認できた部分だけ入力し、請求側と入金側の両方から合計を点検します。

Excelが振込の意味を自動で判定するわけではありません。氏名が似ている、金額が一致する、差額がゼロになったというだけでは、その対応が正しいとは限りません。実際の支払条件や税務・会計処理、債権に関する判断は、契約や証憑を確認し、必要に応じて専門家へ相談してください。

Windowsのデスクトップ版Excelを想定した作例ですが、記事作成時点で実機の動作は確認していません。セルや式は練習用ブックで試し、Excelの版・地域の設定・実際の運用に合うか確認してください。事業全体の入金予定と支払予定を整理したいときは、個人事業主の資金繰り表の作り方も参考になります。

請求・銀行入金・配賦を三つの表に分ける

最初に「請求」「入金」「配賦」の三つのシートを用意します。請求は何を請求したか、入金は銀行で何が確認できたか、配賦は確認済みの入金をどの請求へ対応させたかを記録します。三つを分けると、入金の事実と、その内訳の確認を混同しにくくなります。

架空の請求は、Aが12,000円、Bが8,000円です。支払者からの銀行振込Xは14,000円で、後日Yが6,000円入金される例を考えます。XとYは銀行が発行した番号ではなく、この練習で別々の入金を区別するために自分で付けた記号です。実際の明細番号や管理番号は、利用する金融機関や管理方法に合わせます。

重要なのは、入金Xが14,000円だったことと、請求A・Bにいくらずつ充てられるかが別の情報だという点です。最初は銀行の着金だけ確認でき、内訳が分からないこともあります。送金額がAの12,000円より大きいからといって、A全額とBの一部だと決めつけません。

表は横にスクロールできます。金額と番号はすべて説明のための架空例です。

着金前と内訳待ちを分ける

時点T0ではXの14,000円は銀行で確認済みですが、A・Bへの内訳は不明です。Yはまだ着金を確認していません。配賦表は空欄にします。Excelの集計式が空欄を含む範囲から0を返しても、それは「入力済みの配賦がない」という算数です。どちらの請求も未払いと確定したことにはなりません。

請求シートと入金シートには、金額の列に加えて確認状態の列を設けます。たとえばXには「着金確認・内訳待ち」と書きます。Yは未着の時点では行を空け、後日着金を確認したら金額とともに記録します。未確認のYを、0円の入金があったという明細に置き換えないためです。

現実には、銀行明細に振込名義や振込日、請求書に請求日や相手先が記載されます。番号と金額だけでなく、どの明細とどの通知を確認したかをたどれるようにします。氏名や口座の情報を含む実データは、共有する相手と保存場所に注意しましょう。練習に実際の口座情報を入力する必要はありません。

三つの表を見比べるときは、同じ金額に別の名前を付ける理由を意識します。請求額は相手に支払いを求めた額、銀行入金額は口座で確認した額、配賦額はその入金について確認できた対応関係です。たとえばX-Aの8,000円という行は、新たな8,000円の振込ではなく、14,000円のうちAに対応すると確認した内訳を表します。

「入金番号」は一回の明細を識別するため、同じ日・同じ金額の振込が複数ある場合にも区別できる形で付けます。請求番号も、それぞれの請求を間違えずにたどるためのものです。番号が本物の明細に印字されているかどうかと、自分の作業表に重複しない番号を付けることは別です。

確認状態は、請求先からの返信を受け取るたびに更新する運用が考えられます。着金だけ確認した時点では「内訳待ち」、通知と請求を照らした後に「通知確認済み」とします。通知を受け取っただけで内容が読み取れない場合は、受信と内容確認も別の状態です。

入力者が一人でも、後日確認するときに当時の判断を忘れることがあります。問い合わせた日、相手の回答日、確認した資料の名前を記録できる列があると、何を根拠に配賦したかを振り返りやすくなります。メール本文や個人情報をすべて表に転記する必要があるとは限りません。

確認できた一回の入金を配賦表へ分ける

時点T1では、支払通知などによって、入金Xの14,000円のうちAへの支払いが8,000円、Bへの支払いが6,000円だと確認できたと仮定します。この通知の内容を、配賦シートに二行で記録します。確認できなかった場合は以下の二行を推測で作らず、内訳待ちのままにしてください。

配賦シートのA1に「入金番号」、B1に「請求番号」、C1に「配賦額」と書きます。A2/B2/C2にはX・A・8000、A3/B3/C3にはX・B・6000を入力します。四行目と五行目は空欄です。Xという一回の振込が二つの配賦行に分かれても、銀行口座への着金が二回あったことにはなりません。

入金シートはA1が入金番号、B1が銀行入金額です。A2にX、B2に14000と入力します。C1は配賦合計、D1は「銀行額と入力済み配賦の差」、E1は確認状態とします。E2には「Xの通知確認済み」など、何を確認したかを記載します。

入金番号で入力済み配賦を合計する

入金シートのC2には、次の式を入力する案です。

=SUMIF(配賦!$A$2:$A$5,A2,配賦!$C$2:$C$5)

配賦シートのA2:A5から、入金シートのA2にある番号と一致する行を探し、その行に対応するC2:C5の金額を合計します。Xの二行が範囲に入っていれば、8,000＋6,000で14,000円を期待します。ドル記号は検索範囲と合計範囲を固定し、他の入金行へ式をコピーするときに参照範囲がずれないようにするためです。

D2には=B2-C2と入力します。Xの銀行入金額14,000円から、入力済み配賦14,000円を引いた差は0円です。ただし、差0は配賦額の合計が銀行額と一致するという意味に限ります。X-AとX-Bの対応が支払通知に合っているかは、元の通知との照合が必要です。

Excelで設定する前に、シート名が「配賦」であること、検索範囲と合計範囲の行数が一致していること、C列の金額が数値として入力されていることを確かめます。式や画面表示は利用する環境で確認してください。MicrosoftのSUMIF関数の案内で引数の意味も確認できます。

0円と未確認を読み違えない

T0で配賦表が空欄なら、式は入力済みの金額を合計するため0になり得ます。その横の差額だけを見て「Xの全額が行方不明」と決めるのではなく、「Xの内訳待ち」の状態と一緒に読みます。T1で通知を確認し、初めて二行を入力します。数字の欄だけを切り取って別の人へ伝えないことが大切です。

確認状態には、単に「済」と書くより、「Xの支払通知を確認」「Yは着金未確認」「Yの通知待ち」のように対象を含めます。確認した日や元資料の保管場所を別欄へ書く運用も考えられます。資料の保存義務や期間をこの表だけで判断せず、必要な条件を確認しましょう。

二つの配賦行を作成したら、まずXが両行に入っていることを確認します。一方にYを誤入力すると、請求ごとの合計が偶然合っていても、Xの配賦合計は14,000円になりません。元の通知から入金番号、請求番号、金額を一行ずつ照らし、転記した後で入金側の集計を見ます。

数式を入力するC2では、最初の引数が「どの行を探すか」、二つ目が「何と一致する行か」、三つ目が「一致した行のどの値を足すか」です。今回は配賦A列、入金番号X、配賦C列の金額という順です。A列とC列で開始行がずれると、意図しない行の金額を合計するおそれがあります。

二回目の入金が存在しない段階では、Yに相当する行へ0と入力する必要はありません。行が空欄であることと、銀行が0円の取引を記録したことは意味が違います。空欄をどう表示するかは表の作り方によりますが、少なくとも確認状態の説明を省かないようにします。

手数料が差し引かれていたように見える場合も、差だけから誰の負担かを確定しません。実際の契約、通知、入金内容の確認が必要です。請求額と銀行額が一致しないとき、その差を自動で特定の科目や手数料へ割り当てる処理は、この練習表には含めません。

請求側から入力済み配賦と差額を確認する

次は同じ配賦表を、請求の側から見ます。請求シートのA1に請求番号、B1に請求額、C1に配賦合計、D1に「請求額と入力済み配賦の差」、E1に確認状態を置きます。A2/B2はA・12000、A3/B3はB・8000です。

C2には次の式を入力する案です。

=SUMIF(配賦!$B$2:$B$5,A2,配賦!$C$2:$C$5)

今度は配賦シートのB列にある請求番号を条件にします。A2の請求番号Aに一致する配賦金額をC列から合計し、D2には=B2-C2を入力します。三行目へ式をコピーしたら、条件がA3、差額の参照がB3とC3になっているか確認します。配賦の検索範囲と合計範囲は固定した2〜5行目です。

T1で入力されているのはX-Aの8,000円とX-Bの6,000円です。請求Aは12,000−8,000で記録上4,000円、請求Bは8,000−6,000で記録上2,000円の差を期待します。これは入力したXの配賦を引いた算数であり、確認していない支払や契約上の調整を反映した法的な債権額ではありません。

銀行でYが見えたが、内訳はまだ不明

時点T2では、Yの6,000円が銀行に入ったものの、AとBへの内訳がまだ分からないとします。入金シートにY・6000を追加し、確認状態を「着金確認・内訳待ち」とします。配賦表にYの行はまだ追加しません。

請求AとBの差額は、入力済みXだけを引いているので、数式上は4,000円と2,000円のままです。これをYの影響まで確定した残高と呼ばず、「Y内訳待ち」と添えます。請求側の合計6,000円と銀行入金Yの6,000円がたまたま同額でも、内訳を確認する根拠にはなりません。

Yについては、相手の支払通知、請求番号、入金日などを確認し、どの請求に対応するかを確かめます。確認先への問い合わせが必要なら、何を受け取り、何が不明かを簡潔に整理します。すでに分かっているXの内容と、まだ分からないYの内訳を同じ「確認中」にまとめるより、相手も回答しやすくなります。

請求番号のAとBは練習用です。実務では同じ番号を二つの請求に付けていないか、入力時に余分な空白がないか、番号を後から変更した場合に配賦表との対応が残るかを確認します。数式が一致した文字を合計しても、入力した番号が正しい取引を表しているとは限らないからです。

請求Aの式は、配賦B列の請求番号Aを条件とし、対応するC列だけを合計します。入金Xを探す式とは、条件に使う列が異なります。どちらも同じ配賦額C列を足しますが、請求側と入金側では質問が違うため、結果を表示する表も別にします。

もしAの配賦が想定より少ないとき、最初に確認するのは「配賦表に入力されているAの行」です。請求番号が別の文字になっていないか、金額がC列にあるか、参照範囲2〜5行に入っているかを見ます。入力されていない入金まで、式が銀行口座から探して追加するわけではありません。

T2でYの着金が確認できても内訳が不明なら、請求Aの差4,000円という表示には「Xの確認済み配賦を除いた記録上の差」という説明が必要です。Yの6,000円がAに全額対応する可能性も、Bに対応する可能性も、実際の通知を見るまで判断しません。数字だけを経営資料へ転記する場合も、この前提を添えます。

差額に関する問い合わせでは、請求番号、請求日、請求額、銀行で確認した日と金額、確認したい範囲を整理します。相手に送る前に、口座情報や個人情報を必要以上に含めていないか確認します。この記事はメールを自動送信するものではなく、社内や取引先との正式な確認方法を置き換えるものでもありません。

二回目の入金を確認してから配賦を追加する

時点T3では、入金Yの6,000円について、Aが4,000円、Bが2,000円という内訳を通知で確認できたと仮定します。配賦シートのA4/B4/C4へY・A・4000、A5/B5/C5へY・B・2000を入力します。確認前のT2では空欄だった二行を、確認後に埋めるという順番です。

請求AにはXから8,000円とYから4,000円が配賦され、合計12,000円。請求BにはXから6,000円とYから2,000円が配賦され、合計8,000円です。請求側の差はどちらも0円になることを期待します。

入金側では、Xに8,000＋6,000＝14,000円、Yに4,000＋2,000＝6,000円の配賦があります。各銀行入金額との差も0円になることを期待します。入金シートのC3にC2の式をコピーする際は、条件が同じ行のA3を参照し、配賦シートの範囲は2〜5行に固定されたままか確認します。D3もB3−C3の関係です。

配賦行を銀行入金としてもう一度足さない

銀行から入った金額を集計するときは、入金シートのXとYを使います。配賦シートの四行は、その二回の入金を請求別に分けた内訳です。銀行入金の20,000円に配賦表の20,000円をさらに加えると、同じ資金の動きを二重に数えることになります。

請求側と入金側の集計は、別々の問いに答えています。請求側は「この請求に入力済みの配賦はいくらか」、入金側は「この銀行入金を入力済みの配賦がいくら使っているか」です。どちらか一方だけが合っていても、もう一方が合っていない場合があります。

たとえば請求Aに12,000円が入るよう数字を調整しても、Xに14,000円を超える額を割り当てていれば、銀行の一回の入金を使い過ぎています。反対にXの合計が14,000円でも、誤った請求番号へ配賦しているかもしれません。各行の請求番号、入金番号と金額を、確認した通知に戻って見る必要があります。

通知の内容が不明瞭なときに、残額に合う数字を自分で作ってはいけません。相手に確認できるまで、該当する入金は内訳待ちにします。状態が変わった日と、どの資料で確認したかを記録すれば、後から担当が交代しても、入力した数字の根拠をたどれます。

T3で四つの配賦行を入力した後も、XとYを混ぜず、それぞれの銀行明細へ戻ります。XはX-AとX-Bの二行、YはY-AとY-Bの二行です。番号だけ入れ替えても全体合計20,000円は変わらないため、入金ごとの配賦合計と、対応する通知の両方を確認します。

同様に請求AはX-AとY-A、請求BはX-BとY-Bを見ます。一つの請求に複数回の入金が対応する場合、一行の請求に対して配賦が複数行になることは自然です。一方、同じ振込を二回登録したのか、同じ振込を二つの請求へ正しく配分したのかは、元の銀行明細と配賦行の組合せを見なければ分かりません。

入力した表と銀行明細を比較する人が違う場合は、確認者、確認日、元資料を見た範囲を記録します。配賦表の金額を修正する人と、それを承認する人が別の場合もあります。誰が何を確認するかは業務の規模に合わせて決めますが、式が一致したことだけを相手への確認とみなさない点は共通です。

請求書を訂正する必要が生じた場合、表内の金額を直接書き換える前に、元の請求書と訂正後の書類、相手への連絡を確認します。訂正の方法や必要な書類は取引と制度により異なります。配賦表はその確認を整理する補助であり、訂正書類や税務上の保存そのものではありません。

重複と配賦超過を二方向から点検する

ここではT1へ戻り、配賦シートの二行目がX-A-8000、三行目がX-B-6000、四行目と五行目が空欄の状態を、練習用コピーに作ります。誤操作の例として、四行目にX-A-8000をもう一度入力したとします。これは実際に二度入金があったという意味ではありません。

固定した検索範囲2〜5行にこの重複が入るため、入金Xの配賦合計は8,000＋6,000＋8,000＝22,000円です。銀行で確認した14,000円から引くと、差は−8,000円になります。同時に請求Aの配賦は8,000＋8,000＝16,000円となり、請求額12,000円からの差は−4,000円です。

負の差は、入力済みの配賦合計が、その行の銀行入金額や請求額を超えていることを示す確認の入り口です。この例では重複行が原因だと自分で作ったので分かりますが、実務で負の値を見つけても、最初から原因を決めつけません。番号違い、金額の誤入力、対応の確認不足などを元資料と照らします。

テストが終わったら、練習コピーでは四行目を空欄に戻し、Xの配賦合計が14,000円、Aの配賦合計が8,000円に戻ることを確認してからYの例へ進みます。本番データを修正するときは、重複と判断した根拠、変更前の値、修正日、担当者をどのように残すかを先に決めてください。

差が0でも誤りはあり得る

合計が一致しても、AとBの内訳を取り違えている可能性があります。たとえばXの8,000円と6,000円を逆の請求番号へ入力しても、X側だけ見れば合計は14,000円です。請求側の差も確認し、なお不明なときは支払通知を見ます。

複数の誤りがたまたま相殺されることや、異なる取引に同じ管理番号を誤って付けることも考えられます。Excelの差額が0になったことを、相手の確認を受けた証拠として使うことはできません。計算結果は確認箇所を見つける補助とし、根拠となる銀行明細と支払通知を分けて保管します。

データを修正するときは、誰かが同時に同じファイルを編集していないかも確認します。ファイルの複製が複数ある場合は、それぞれの入力内容が異なるかもしれません。作業する版と更新後の保管先、他の担当者が確認できる方法を決め、古い版を誤って新しい版として扱わないようにしましょう。

重複を調べる練習では、先にT1の正しい二行を記録してから、四行目だけに誤ったX-A-8000を追加します。最初からT3の四行を入力した後に六行目へ追加すると、式の範囲外なのでこの練習の負の差を再現できません。どの時点の表で、何行目を変更したかを残します。

負の差が見えたときは、入金Xの銀行額14,000円、配賦表のXに付いた三行の金額、請求Aに付いた二行の金額をそれぞれ書き出します。銀行側だけでなく請求側にも影響があると分かれば、削除する行を選ぶ前に、その行が別の通知に基づく正当な配賦でないか確認できます。

誤入力を直した後、数式の結果が元へ戻ったことだけでなく、誤った行の記録と修正理由が必要な人に伝わるかも点検します。特に、修正前の表をすでに他の人が参照していた場合は、どちらの版を使うのか決める必要があります。複製ファイルで実験し、実データは定めた手順で更新します。

入金額と請求額が一致しない理由には、分割された支払い以外の可能性もあります。振込名義が異なる、別の取引を含む、請求書自体が訂正されたなど、確認すべき情報はケースごとに異なります。見た目の差額に合う理由を一つ選んで記入するのではなく、分かった事実と仮説を区別します。

範囲外・未確定・訂正履歴を引き継ぐ

この記事のSUMIFは配賦シートの2〜5行を固定して参照します。T3で四行すべてを使った後、六行目に新しい配賦や重複を入力しても、その式の範囲には入っていません。六行目に誤りがあっても、差が変わらないことがあります。

行を追加する前に、請求側と入金側のすべての式の範囲を確認し、必要なら同じ範囲へ広げます。範囲を広げるときは条件範囲と合計範囲を対応させ、追加行だけでなく既存行の結果も見直します。テーブルを使う方法もありますが、参照の書き方と更新時の動作は別に検証が必要です。設定しただけで無制限に正しく集計されるとは考えません。

追加前に固定範囲の末尾を調べる

配賦シートの五行目までが入力済みのとき、新しい入金を受けたら六行目に書きたくなるでしょう。しかし、式は2〜5行しか見ていません。まず請求シートC2・C3と入金シートC2・C3の式を確認し、どの範囲が固定されているか書き出します。表示される合計が変わらない理由を、着金がなかったためと誤解しないようにします。

範囲を2〜6行へ広げる例では、条件範囲と合計範囲の両方について、開始行と終了行をそろえます。請求側は配賦B列とC列、入金側は配賦A列とC列です。一方の表だけ広げると、同じ配賦行が請求側には含まれ、入金側には含まれないという不整合が生じます。

変更後には、追加した六行目を含む合計と、もとの四行からの合計を別々に電卓などで照合します。そのうえで、他の請求や入金の行へコピーした式にも同じ範囲が設定されているか確認します。Excelのバージョンやコピー方法により実際の表示は異なる場合があるため、練習用ブックで手順を確かめてください。

実務では新しい配賦が毎月発生するかもしれません。担当者が一回だけ範囲を広げても、次回追加時にまた範囲外となります。入力可能な行の末尾、範囲を広げる担当、確認を行うタイミングを運用ルールとして残します。範囲を自動的に管理する別の仕組みを導入する場合も、既存の資料で動作を確認する必要があります。

一方、配賦表の列やシート名を変更すれば、式が意図どおり参照できなくなる場合があります。変更前にコピーを取り、変更後に請求側と入金側の両方で既知の例を再計算します。担当者が「追加するだけ」と思って行った操作でも、参照先に影響する可能性があると共有しておきましょう。

この練習に使った請求A・B、入金X・Yと四つの配賦は架空のものです。同じ番号や金額が実務に現れたとしても、金額の一致だけで同じ取引とは判断できません。相手方と対象期間、請求書、通知、銀行明細という別々の資料を確かめる流れへ戻ります。

「差がマイナスにならなかった」という観察も、どの時点の、どの範囲を確認したかと一緒に残します。たとえばT1の四行目は範囲内ですが、T3の六行目は範囲外です。この違いを知らずに結果だけ共有すると、次の担当者がすべての行を点検済みだと思ってしまうかもしれません。表と説明を一緒に引き継ぎましょう。

金額の単位や入力形式も、実際の表でそろえます。数字を文字として取り込んだ行、空欄、式が入った行を混ぜると、意図した集計か確認しにくくなります。銀行明細の取込結果を加工した場合は、加工前の情報を消さずに残し、どの値を作業用の表へ移したかをたどれるようにします。

未確認を残したまま担当者へ渡す

T2のYは、銀行で6,000円を確認した一方、内訳は不明です。引継ぎメモに「Y着金確認・内訳待ち」「Xの二行は通知で確認済み」と分けて書けば、次の担当者はどちらを調べるべきか分かります。未確認を0や完了へ置き換えず、確認先と次に見る資料を残します。

確認が取れたら、以前のメモをなかったことにせず、いつ何が確認できたか追記します。誤配賦を見つけた場合は、数字を上書きする前に、どの通知を根拠にどう訂正するのか確認します。実際に必要な記録の内容は、事業のルールや会計処理に応じて決めてください。

使い始める前には、架空データでT0からT3まで順に試し、Xの配賦、請求側の差、Yの保留、二方向の合計、範囲内の重複をそれぞれ確認します。六行目を追加しても固定式に含まれないことも試し、参照範囲の限界を利用者へ伝えます。実際の画面で動作しなければ、そのまま本番へ持ち込まず、式と範囲を見直してください。

入金消込の表は、数字を合わせるために内訳を推測するものではありません。請求と銀行入金という二つの事実を残し、確認できた対応だけを配賦として入力し、その両側を検算するための道具です。まずは少ない架空データで順番を確かめ、実務に使う場合は資料の確認と会計上の判断を別に行いましょう。