ZUGFeRD請求書と支払いをExcelで照合：AIと残額の自動更新

請求書ファイルが7個あっても、請求書が7件とは限りません。1件の請求書に複数回の支払いがあることもあります。Excelで照合する基本は、取引先と請求書番号で支払いを結び付け、合算してから残額を求めることです。

このGetSheetAI事例は、ZUGFeRDのサンプル請求書と模擬支払いデータを使った実際の操作です。拡張請求書インポートを備えたローカル開発版で撮影しており、このインポート経路は公開版にはまだ反映されていません。照合方法自体は、すでにExcelにある請求書データにも使えます。

6件の請求書を4つの支払い状態に整理

7ファイルから6件の請求書を整理し、結果を Rechnungsabgleich、元の支払いを Zahlungen に保持しました。

ある取引先の過払いで、別の取引先の未払いを相殺しないことが重要です。

請求書側には取引先、番号、日付、支払期日、支払額、通貨、出典を用意します。支払い側には支払いID、日付、受取先、請求書番号、金額、通貨が必要です。番号は文字列として保管します。

画面ではLieferant GmbHとHandwerker GmbHの両方に 471102 という番号があります。番号だけで検索すると誤結合になります。実務では安定した取引先IDを優先し、表記の違いは対応表で整理します。複数通貨なら通貨も条件に加えます。

新しい照合シートを作成してください。取引先と請求書番号で支払いを照合し、分割払いを合算して請求額、支払済額、残額、状態を表示してください。重複ファイルは一度だけ計上し、記録は残してください。過払いと請求書のない支払いを別表示し、残額は数式にして元のシートは変更しないでください。

最初の一致だけを返す検索では、2回目の支払いを見落とします。SUMIFSなどで合算し、残額を請求額から支払済額を引く数式にします。

デモでは200 EURと140.96 EURで340.96 EURの請求書を完済。別の請求書は300 EURに対し120 EURを支払い、残額は180 EURでした。465 EURに500 EURを支払った請求書は−35 EURで、過払いです。

テストで支払いを120から300に変えると、残額は 180から0、正の残額合計は 3453から3273 に更新されました。他の請求書と数式は変わらず、テスト後に元の金額へ戻しています。

同一ファイルのコピーは重複計上を避けられますが、同じキーで金額が異なるレコードは矛盾として残します。支払い状態には明確な丸め許容差も必要です。このケースでは6件と48個の出典フィールドを検証しており、あらゆる帳票形式を保証するものではありません。

既存の表から始める方法は請求書と支払いの照合、全体像はAI for Excelの使い方をご覧ください。