見積書と請求書を1行でつなぐExcel。120件で試すと請求漏れが8件出た

見積書は見積のフォルダ、請求書は請求のフォルダ。書類の種類ごとに台帳が分かれていると、納品したのに請求書を出していない案件が、どの台帳にも出てこない。

1案件を1行にして、見積日・受注日・納品日・請求日を横に並べた表を作った。架空の120件を入れて動かしたら、納品済みで請求していない案件が8件、金額にして866万円あった。

以下の計算は、2026年9月21日にMicrosoft 365のExcel（バージョン16）で行った。120件は式を確かめるために作った架空の取引で、消費税とインボイスの要件は扱っていない。Googleスプレッドシートでの動作は確認していない。

請求が遅いのではなく、出していないことに気づいていない

8件が納品からどれだけ止まっているかを数えた。平均66.9日。いちばん古いものは147日で、5か月近く放ってある。

請求済みの61件のほうは、納品から請求まで平均10.5日で出せていた。

6.4倍の開きがある。これは請求の作業が遅いという話ではない。普段は10日で出しているのだから、出す気になれば出せるのだ。8件は単に、担当者の視界から落ちている。

8件のうち6件は30日を超えていた。残りの2件は1日と9日なので、こちらは普通の処理の途中だ。納品から30日たって未請求、という線を引けば、拾うべきものだけが残る。

受注した見積金額の合計に対して、866万円は6.3%にあたる。受注額の6%が請求されないまま置いてあると考えると、探しにいく価値はある。

見積のまま止まっている19件のほうが、金額は大きい

未請求の8件ばかり見ていたが、見積を出したきり受注日が入っていない19件も数えた。見積日からの平均は88.3日。最長は175日で、半年近く返事がない。

60日を超えたものが13件あり、見積金額の合計は1,435万円になる。未請求の866万円より大きい。これを「まだ生きている見積」として受注見込みに入れたままだと、見込みの数字が実態から離れていく。

失注の列は、この表に作らなかった。日付を入れた分だけ状態が進む、という決め方が崩れるからだ。代わりに、60日を超えた見積は取引先に一度聞いて、続かないものは行ごと別のシートに移している。判断を人がするところと、式が出すところを混ぜない。

受注したのに納品が済んでいない32件も、同じ見方で数えられる。受注日からの平均は45.2日、最長146日、60日超が11件だった。こちらは納期が先の案件も混ざるので、赤くはしていない。

4つの日付が入っているかどうかで、状態が決まる

状態の列は、右から順に日付が入っているかを見るだけでいい。請求日があれば請求済み、なければ納品日を見る、という順で降りていく。

=IF($A3="","",IF($H3<>"","請求済み",
  IF($G3<>"","納品済み・未請求",
  IF($F3<>"","受注・納品待ち","見積のまま"))))

見積のまま、受注・納品待ち、納品済み・未請求、請求済み。この4つしか出ない。日付を入れた分だけ状態が進むので、進捗を手で打ち替える欄は作らなかった。打ち替える欄があると、打ち替え忘れた行が必ず出る。

500行すべてが赤くなった。空のセルと数値を比べたせい

止まっている日数は、請求済みでない行だけに出す。

=IF($A3="","",IF($J3="請求済み","",設定!$B$3-MAX($D3,$F3,$G3)))

最後に日付が動いた日をMAXで取って、今日からの差を出す。受注日が空でも見積日が入っていれば、そこから数えられる。

行を赤くする条件付き書式を、はじめは$L3>設定!$B$5とだけ書いた。500行すべてが赤くなった。日数の列は、案件が入っていない行では空文字を返している。Excelは文字列と数値を比べると文字列のほうを大きいと判定するので、空文字>14が真になっていた。

状態の判定を前に置いて逃げた。

=AND($J3="納品済み・未請求",$L3>設定!$B$5)

条件付き書式が思ったところに出ないときは、その式を空いたセルにそのまま貼るのが早い。TRUEかFALSEが返ってくるので、式が悪いのか範囲が悪いのかがすぐ分かれる。ガントチャートの帯が消える話でも同じ追い方をした。

見積と請求で金額が違う14件を、その場で見る

差額の列は請求金額から見積金額を引くだけ。

=IF(OR($I3="",$E3=""),"",$I3-$E3)

120件のうち、金額が一致しなかったのは14件だった。

差引の18万円は、請求済み合計7,616万円の0.2%にすぎない。合計で見ると小さい。

ただ、少ない側の30万円は取りこぼしのほうに寄っている。仕様が減ったから安くしたのか、転記を間違えたのかは、行を開かないと分からない。多い側の4件も同じで、追加分を正しく乗せたのか二重に乗ったのかは、記録がなければ判断できない。

差額が出ること自体は悪くない。むしろ差額ゼロの行しかない表のほうが怪しい。見積をそのまま請求に写しているだけで、途中の変更を誰も拾っていない可能性がある。

件数の集計はSUMPRODUCTで出した。COUNTIFSに"<>0"を渡すと、式が返した空文字まで数に入る。

=SUMPRODUCT(($I$3:$I$502<>"")*($K$3:$K$502<>0))

番号を書類ごとに振り直すと、線が切れる

この表が成り立つのは、1行が1案件で、見積から請求まで同じ番号を使っているからだ。

見積書に見積番号、請求書に請求番号を別々に振ると、どの請求がどの見積から来たのかは、取引先と件名の組み合わせで推測するしかなくなる。同じ取引先に似た件名の案件が2つあれば、そこで推測は外れる。

番号は見積を出すときに1つ振って、受注書にも納品書にも請求書にも同じものを載せる。書類の種類を区別したいなら、番号の頭に記号を足すのではなく、書類名を横に書けばいい。番号そのものは動かさない。

うちで使っている形はMと西暦下2桁と連番で、M2601なら2026年の1件目になる。年が変わると連番が戻るので、過去の番号とぶつからない。

請求済みの行は消さずに残す

毎週見るときは、状態の列でオートフィルタをかけて納品済み・未請求だけを出し、止まっている日数の降順に並べている。列の並びをこの順にしたのは、絞ってから並べ替えるまでの操作を2回で終わらせたかったからだ。

済んだ行を消したくなるが、消すと差額の集計が消える。番号が重複していないかの確認もできなくなる。行数は500行まで用意してあるので、月に20件なら2年分は足りる。あふれたら年ごとにファイルを分けて、前の年のファイルはそのまま置いておく。

使った関数はIF、OR、MAX、COUNTIF、SUMIF、SUMPRODUCTだけ。どれも古いExcelにもある。新しい関数を混ぜると、社内の別の端末で開いたときに#NAME?が並ぶ。見栄えより、開けることを取った。

請求したあとは、別の表に渡す

この管理表は請求書を出すところで終わる。入金の確認は入れていない。請求日から先は売掛金の年齢表のほうで、支払期日からの経過日数で追っている。

払う側の予定は支払予定表に分けた。1つの表に請求と支払を混ぜると列が倍になって、どちらの立場で見ている数字なのか分からなくなる。

作っていて意外だったのは、未請求の8件のうち3件が同じ880,000円だったことだ。金額の同じ定例の保守で、1件出したところで「出した」と記憶が上書きされる筋書きになっていた。架空のデータではあるが、似たことは起きる。金額ではなく番号で数えるほうが安全だと思う。