Excelで売掛金エイジングレポート（30日、60日、90日）を作成する方法

エイジングレポートは、未払いの請求書を延滞期間（通常は0〜30日、31〜60日、61〜90日、90日超）ごとに分類するものです。レポートが正確かどうかは、2つの決定によって左右されます。1つ目は、支払期日と請求日のどちらを基準に起算（エイジング）するか。2つ目は、一部支払い済みの請求書について、全額を表示するか、それとも残高を表示するかです。

この2つを間違えると、すべての分類（バケット）の合計金額が狂ってしまい、レポートが全くない状態よりも悪化してしまいます。

本ガイドでは、レポート作成が破綻する理由、よく使われる3つのアプローチ、そしてそれぞれのアプローチが機能しなくなる限界について解説します。これはデータワークフローに関するものであり、会計上のアドバイスではないため、具体的な処理方法については総勘定元帳の管理者に確認してください。

なぜエイジングレポートはスプレッドシートを破綻させるのか

第1の問題は、日付の基準です。請求日から起算すると、その書類が作成されてからどれだけ経過したかがわかります。支払期日から起算すると、顧客の支払いがどれだけ遅れているかがわかり、債権回収において必要となるのは後者の数値です。

どちらの基準にも合理性があり、それぞれ異なるレポートが作成されます。よくある失敗パターンは、どちらの基準が使われたのかがスプレッドシートのどこにも記録されていないケースです。

第2の問題は、一部支払いです。10,000ドルの請求に対して7,000ドルを受領している場合、売掛金は3,000ドルであり、いずれか1つのバケットに正確に3,000ドルとして表示されなければなりません。未決済明細（オープンアイテム）リストではなく、単なる請求書リストに基づいて作成されたエイジングレポートは、気づかないうちにすべての数値を過大評価してしまいます。

第3の問題は、レポートがスナップショットである点です。バケットは「今日」を基準に計算されるため、昨日のファイルはすでに古くなっており、再構築するたびにすべての行が再計算されます。

さらに、処理の難しいデータ行もあります。クレジットノート（赤伝票）、前受金、係争中の請求書、複数通貨の残高など、それぞれにルールが必要です。そして、そのルールは、次にファイルを開く人によって壊されないように維持されなければなりません。

これらは個々に見れば難しいことではありません。難しいのは、これらすべてが月に一度、締め切りのプレッシャーの中で同時に押し寄せてくるからです。

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

行動につながらない回収リスト。 バケット分類の目的は、誰に最初に連絡すべきかを把握することです。残高が過大評価されたレポートは、すでに回収済みの資金を追跡させるという無駄な作業を生み出します。

毎月発生する手戻り作業。 バケットは「今日」を基準にするため、エイジングレポートに終わりはありません。毎サイクル、同じ結合処理、同じ数式、同じ手動チェックが繰り返されます。

元帳と一致しない合計金額。 バケットの合計が売掛金残高と一致しない場合、レポートの信頼性は失われます。その原因を突き止めるには、通常、最初にレポートを作成するよりも長い時間がかかります。

エイジングレポートが信頼されるのは、その合計金額が元帳と一致しているからです。それが一致しなければ、他のどんな要素も意味をなしません。

よく試される回避策

オプション1：数式を触る前に定義を確定させる

シートの上部に4つの事項を書き出します。どの日付を基準に起算するか、バケットの境界（区切り）はどうするか、金額は支払前の総額（グロス）か支払後の純額（ネット）か、そして基準日はいつか、です。

これには10分もかかりませんが、最もよくある意見の食い違いを防ぐことができます。Journal of Accountancyでも、最初に設定を正しく行うことを同様に重視しながら、同じ作成プロセスを解説しています。

また、これによりデータソースも決まります。必要なのは、これまでに発行されたすべての請求書のリストではなく、残高が含まれる未決済明細（オープンアイテム）の抽出データです。

限界としては、定義を定めただけでは何も計算されないという点です。定義は、間違った計算をしてしまうのを防ぐだけにすぎません。

オプション2：バケット列を作成し、合計をピボットテーブルで集計する

延滞日数を「基準日マイナス支払期日」として計算し、その数値をバケットのラベルにマッピングします。TODAY関数を使えばリアルタイムの基準日を取得でき、DATEDIF関数は2つの日付の間の日数を返します。

ラベル自体の設定には、半年後に見直したときの可読性を考慮すると、ネストされたIF文よりもIFS関数の方が適しています。その後、SUMIFS関数を使って顧客およびバケットごとに集計することで、行ごとの計算プロセスを監査可能な状態に保つことができます。

レポートを配布する場合は、TODAY関数ではなく、ハードコードされた（固定の）基準日を使用してください。来週になってファイルを開いたときに自動で再計算されてしまうと、すでに相手の受信トレイにあるバージョンと数値が矛盾してしまいます。

この方法の限界は、データ量と例外処理（エッジケース）です。数式自体は機能しますが、クレジットノート、一部支払い、係争中の案件などは、依然として手動で処理する必要があります。

オプション3：数値データの横にルール用のタブを用意する

複雑な判断基準を一箇所にまとめます。クレジットノートをどのように相殺するか、係争中の請求書を除外するかフラグを立てるか、外貨建て残高をどのレートでどのように換算するか、といったルールです。

これにより、他の人がレポートを実行しても破綻しなくなります。ただし、月末の締め切りが迫っているときには、このタブの更新がスキップされがちです。

限界は、ルールタブは判断基準を文書化するだけで、それを自動で適用してくれるわけではないという点です。毎サイクル、誰かが各ルールを手動で適用しなければなりません。スプレッドシートでの取引照合に関するガイドでは、この作業のベースとなるマッチング業務について解説しています。

共通する限界。 これら3つのアプローチはすべて、整理された未決済明細の抽出データがあることを前提としています。データソースが未加工の請求書エクスポートデータと、それとは別の支払ファイルである場合、バケット分類を始める前にそれらを結合する作業こそが、本当の重労働になります。

Powerdrill Bloomでエイジングレポートを作成する方法

ステップ1：請求書と支払データをアップロードする

未決済明細のエクスポートデータ、または請求書ファイルと支払ファイルを一緒にアップロードします。Powerdrill Bloomはアップロード時に対象列のプロファイリングを行うため、バケット計算が始まる前に、支払期日の漏れ、金額の空白、請求書番号の重複などを検出できます。

ステップ2：バケット分類ルールを自然言語で記述する

ルールを構築するのではなく、言葉で指示します。特定の基準日における支払期日をベースに起算すること、バケットの境界を指定すること、そして金額は受領済みの支払額を差し引いた純額にすることを伝えます。

次に、同じプロセスの中でチェックも依頼します。請求額を超える支払いがある請求書や、支払期日が請求日よりも前になっている請求書がないか尋ねます。そして、バケットの合計金額が売掛金残高と一致しているか確認します。

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

顧客別のエイジング表、バケット分布のチャート、または最も古い残高順に並べ替えた回収リストを出力します。

毎月レポートを再構築するよりも優れている理由

	手動プロセス	Powerdrill Bloom
請求書と支払データの結合	ファイルごとの参照数式（VLOOKUPなど）	両方をアップロードして指示するだけ
基準日の変更	再計算と再検証	新しい基準日を伝えるだけ
一部支払いの相殺	手動での残高列作成	支払額を差し引いた残高を指示するだけ
元帳との合計金額の一致確認	毎サイクル手動でチェック	合計が一致しているか尋ねるだけ

表の中間にある作業こそが、毎月の時間を奪っている原因です。バケット分類自体は単なる算数にすぎず、整理された未決済明細リストを用意することこそが、本来の業務なのです。

よくある間違い

支払期日のつもりで請求日から起算してしまう。 回収業務においては、ほぼ常に支払期日を基準にするのが正解です。どちらを選択する場合でも、レポート上に明記してください。

残高ではなく請求金額を表示してしまう。 一部支払い済みの請求書は、未払いの残高に基づいてバケットに分類されるべきです。全額を表示すると、すべての合計金額が過大になってしまいます。

配布済みのファイルでTODAY関数による再計算を許してしまう。 レポートを送信する前に基準日を固定してください。そうしないと、同じファイルを見ている2人が異なる数値を読み取ることになります。

クレジットノートを無視してしまう。 未適用のクレジットは顧客の貸方に残り、債務額を減らす効果があります。これを除外すると、実際の状況よりも売掛残高が悪化しているように見えてしまいます。

請求書単位ではなく顧客単位でバケット分類してしまう。 バケット分類は請求書ごとに行い、その後に顧客ごとに合算します。顧客の平均滞留日数を使ってしまうと、最も古い請求書が見えなくなってしまいます。

元帳との照合を怠る。 バケットの合計は、売掛金統制勘定の残高と一致しなければなりません。このチェックを怠ると、レポートはただの飾りにすぎなくなります。

毎サイクル、ゼロから再構築してしまう。 ルールは毎月変わるものではなく、変わるのはデータだけです。ルールは維持したまま、エクスポートデータだけを差し替えるようにします。これは、予算対実績レポートと同じアプローチです。

結論

起算日を決定し、残高を使用し、基準日を固定し、合計を元帳と一致させる。この4つのポイントを押さえることで、人々が行動を起こせるレポートになるか、それとも数値の妥当性を巡って議論が紛糾する表になるかの違いが生まれます。

この作業のコストを押し上げているのは、すべてが「今日」を基準にしているため、決して終わらないという点です。データの結合やチェック作業は、毎サイクル必ず発生します。

月末の時間がそのような作業に奪われているなら、請求書や支払のエクスポートデータを使ってPowerdrill Bloomをお試しください。また、PDFの財務諸表をチャートに変換する方法に関するガイドや、AIキャッシュフロー分析のページもあわせてご覧ください。

よくある質問

売掛金年齢分析表（エイジングレポート）の標準的なバケット（分類）は何ですか？

多くのレポートでは、0〜30日、31〜60日、61〜90日、90日超が使用され、多くの場合「当月（未到来）」の列も設けられます。これらの境界はルールではなく慣行であるため、どの区分を使用したかを明記してください。

請求書は請求日と支払期日のどちらから起算すべきですか？

顧客の支払いがどれだけ遅れているかを知りたい場合（回収業務の通常の目的）は、支払期日を使用します。書類が作成されてからどれだけ経過したかを知りたい場合は、請求日を使用します。

一部支払いにはどのように対応すればよいですか？

当初の請求金額ではなく残高を表示し、その残高を1つのバケットに配置します。請求書リストではなく、未決済明細（オープンアイテム）の抽出データから作業を行うことで、これが自動的に処理されます。

どのExcel関数が必要ですか？

基準日にはTODAY関数または固定の日付を使用し、延滞日数にはDATEDIF関数を使用します。IFS関数でバケットのラベルを割り当て、SUMIFS関数で顧客およびバケットごとに集計します。どれも複雑な関数ではありません。難しいのは定義の決定です。

レポートはどのくらいの頻度で再構築すべきですか？

すべてのバケットは基準日に依存するため、少なくとも毎月、回収業務が活発な場合は毎週再構築する必要があります。配布する各バージョンで、その基準日を必ず固定してください。