入金管理をエクセルで行う方法|請求と入金を1行で管理する表と関数
「請求書は出したけれど、どれが入金済みなのか、すぐにわからない」
「通帳と請求書の控えを見比べるのに、毎月時間がかかる」
ハウスクリーニングや訪問サービスでは、個人のお客様の当日払い、管理会社への月末のまとめての請求、作業のあとの振込など、代金の受け取り方がいくつもあります。請求書の控え、通帳、売上シートと記録が分かれていると、入金の漏れに気づくのが遅れがちです。
先に結論をお伝えします。入金管理は、請求書1枚を1行にして、請求額・支払期限・入金日・入金額を同じ行に並べた表で行います。 まだ入っていない金額と、支払期限を過ぎた請求は、関数と色で自動で見えるようにします。
この記事では、入金管理表の項目とテンプレートの見出し、そのまま使える関数、期限切れに色を付ける設定、入金の確かめ方、支払期限を過ぎたときの連絡の順番と例文を解説します。
入金管理表の項目とテンプレート
シートの名前を「入金管理」にして、次の12項目をA列からL列に入れます。J列の差額は、関数で自動で出します。
| 列 | 項目 | 入れ方のポイント |
|---|---|---|
| A | 請求書番号 | 請求書に書いた番号と同じにする |
| B | 請求日 | 日付として入力する |
| C | お客様名 | 法人なら会社名。振込名義が違うときはメモに書く |
| D | 件名 | 「11月分 定期清掃」「エアコンクリーニング 11/12作業」など |
| E | 請求額(税込) | 請求書の合計と同じ金額 |
| F | 支払期限 | 請求書に書いた期限 |
| G | 支払い方法 | 振込・現金・カードから選ぶ |
| H | 入金日 | 通帳や入出金明細で確かめた日 |
| I | 入金額 | 実際に入った金額 |
| J | 差額 | 関数で自動(まだ入っていない金額) |
| K | 状態 | 未入金・一部入金・入金済みから選ぶ |
| L | メモ | 手数料の差し引き、まとめての振込、連絡した日など |
見出しの行は、次の1行をコピーしてA1のセルに貼り付けると、列に分かれて入ります。
請求書番号 請求日 お客様名 件名 請求額(税込) 支払期限 支払い方法 入金日 入金額 差額 状態 メモ請求書1枚につき1行にする
請求書を出したら1行足し、入金があったら同じ行の入金日と入金額を埋めます。請求の表と入金の表を別々に作ると、2つを見比べる作業が毎回必要になります。1行にまとめておけば、まだ入金のない請求(売掛金)が、その行を見るだけでわかります。
管理会社に1か月分をまとめて請求するときも、請求書1枚で1行です。作業ごとの明細は請求書のほうに書きます。請求書の書き方は「ハウスクリーニングの請求書の書き方」で解説しています。
状態と支払い方法はプルダウンにする
「入金済み」「入金済」「済」のように書き方がばらばらだと、関数が別のものとして数えてしまいます。状態と支払い方法は、選んで入力する形にしましょう。
エクセルでは、列を選んで「データ」タブの「データの入力規則」を開き、「入力値の種類」で「リスト」を選んで、「未入金,一部入金,入金済み」のように選択肢をカンマで区切って入れます。Googleスプレッドシートでは、セルを右クリックして「プルダウン」から設定できます。
入金管理に使う関数
ここで紹介する関数は、エクセルとGoogleスプレッドシートのどちらでも、同じ式で使えます。
差額(J列)を出す
J2のセルに次の式を入れて、下の行へコピーします。
=IF(E2="","",E2-I2)請求額から入金額を引いた、まだ入っていない金額が出ます。
- 入金がまだなら、請求額がそのまま出る
- 全額が入れば、0になる
- マイナスになったら、請求より多く振り込まれている
- 請求額が空の行は、空欄のまま
未入金(売掛金)の合計と件数を出す
集計の関数は、別のシートにまとめます。入金管理の表でフィルターを使ったときに、同じ行にある集計まで隠れてしまわないようにするためです。シートを1つ足して名前を「集計」にし、A1からA3に「未入金の合計」「未入金の件数」「期限を過ぎた未入金」と書いて、となりのB列に関数を入れます。
未入金の合計(B1のセル)は、状態が「入金済み」ではない行の差額を合計します。一部入金の行は、残りの金額だけが数えられます。
=SUMIFS(入金管理!J:J,入金管理!K:K,"<>入金済み")未入金の件数(B2のセル)は、次のとおりです。
=COUNTIFS(入金管理!E:E,">0",入金管理!K:K,"<>入金済み")支払期限を過ぎた未入金の合計(B3のセル)は、次のとおりです。TODAY()は今日の日付を表します。期限が今日の請求と、支払期限が空欄の行は数えません。
=SUMIFS(入金管理!J:J,入金管理!K:K,"<>入金済み",入金管理!F:F,"<"&TODAY())月別の入金合計を出す
集計シートのA5からC5に「月」「入金の合計」「請求の合計」と見出しを書きます。A6のセルに「2026/11/1」のように月の初日を入れ、B6のセルに次の関数を入れます。A7に次の月の初日を入れて関数をコピーすれば、月ごとの入金合計が並びます。
=SUMIFS(入金管理!I:I,入金管理!H:H,">="&A6,入金管理!H:H,"<"&EDATE(A6,1))入金日がその月の1日から月末までの入金額を合計します。EDATE(A6,1)は1か月後の日付です。「次の月の1日より前」までを数えるので、30日の月も31日の月も、同じ式で月末の入金まで入ります。
同じ月に請求した金額の合計は、C6のセルに次の関数を入れると出せます。入金の合計と並べると、その月の請求のうち、どれくらいが入金されたかの目安になります。
=SUMIFS(入金管理!E:E,入金管理!B:B,">="&A6,入金管理!B:B,"<"&EDATE(A6,1))支払期限を過ぎた請求に色を付ける
期限を過ぎてもまだ入金のない行に、色が付くようにします。入金管理のシートでA2からL1000までのセルを選び、次の数式で条件付き書式を設定します。
=AND($E2<>"",$F2<>"",$F2<TODAY(),$K2<>"入金済み")- 期限の翌日から色が付く。期限の当日はまだ付かない
- 入金済みにした行と、支払期限が空欄の行には付かない
- 一部入金の行は、期限を過ぎていれば色が付く
- 1000行目までに設定しているので、請求が増えてきたら範囲を広げる
設定の場所は、次のとおりです。
- エクセル: 「ホーム」タブの「条件付き書式」から「ルールの管理」を開き、「新規ルール」をクリックします。「数式を使用して、書式設定するセルを決定」を選んで数式を入れ、「書式」から塗りつぶしの色を選びます
- Googleスプレッドシート: 「表示形式」の「条件付き書式」を開き、「セルの書式設定の条件」で「カスタム数式」を選んで、「値または数式」に数式を入れます
毎朝ファイルを開いたら、色の付いた行がないかを見る習慣にしましょう。支払期限で並べ替えると、古い順に確かめられます。
入金の確かめ方
曜日を決めて、通帳や入出金明細と照らし合わせる
入金の確認は、「毎週月曜と木曜」のように曜日を決めて行います。月末と、支払期限の多い日の翌日も確かめましょう。
- 通帳やネットバンキングの入出金明細で、前回確かめた日からの入金を1件ずつ見る
- 金額と振込名義から、入金管理表の行を探す
- 入金日と入金額を入れ、差額を確かめてから状態を選ぶ
- 照らし合わせた入金に、通帳なら印を付ける
最後の印は、同じ入金を2回入力しないためのものです。明細を印刷して使うなら、紙に印を付けてもかまいません。
振込名義がお客様名と違う
法人の略称、管理会社が代わりに振り込んだ、ご家族の名義で振り込まれた、といった理由で、名義と請求先が一致しないことがあります。金額と時期から当たりをつけ、わからなければお客様に確かめます。
一度わかった名義は、メモの列や顧客名簿に書いておきましょう。次からは迷わずに済みます。請求書に「お振込名義がご契約者様と異なる場合は、ご一報ください」と一言書いておくのも手です。
振込手数料が差し引かれている
差額が数百円なら、振込手数料が差し引かれていないか確かめます。請求書に「振込手数料はお客様のご負担」と書いていたなら、お客様にお伝えするか、今回は自社で負担するかを決めます。
自社で負担すると決めたら、状態を「入金済み」にして、メモに「振込手数料○円差し引き」と書きます。手数料をどちらが負担するかは、取引の前に決めて請求書に書いておくと、こうした確認が減ります。
複数の請求がまとめて振り込まれた
入金額がどの請求とも合わないときは、同じお客様の請求をいくつか足した金額と比べます。合えば、それぞれの行に同じ入金日を入れ、入金額にはその行の請求額を入れます。メモには「11/30 3件まとめて入金」のように書いておきます。
足しても合わないときは、自分で決めつけずに、お客様に内訳を確かめましょう。
一部だけ入金された
入った金額を入金額に入れ、状態を「一部入金」にします。差額の列に、残りの金額が出ます。
残りが入ったら、入金額を合計の金額に直し、入金日は最後に入った日にします。1回目の日付と金額はメモに残します。1回目と2回目で月をまたいだ場合は、月別の入金合計で、2回分がまとめて後の月に数えられる点に気をつけてください。
支払期限を過ぎたときの連絡の順番と例文
期限を過ぎても、払うつもりがないとは限りません。請求書が経理の担当者に届いていない、支払いを忘れている、名義が違ってこちらが見落としている、ということもあります。最初の連絡は、責める言い方を避けて、確認のお願いにしましょう。
連絡の順番は、たとえば次のように決めておきます。日数は目安なので、自社のお客様に合わせて決めてください。
| 期限からの日数 | すること |
|---|---|
| 翌日〜3日 | 自社の見落としがないか確かめてから、メールやLINEで確認のお願いをする |
| 1週間ほど | 電話で確認し、お支払いの予定日をうかがう。法人なら経理の担当者にも確かめる |
| 約束の日を過ぎたら | 改めて期日を決めて、メールなど記録に残る形でお願いする |
| それでも入金がない | 次のご依頼をお受けする前にお支払いを確かめる。弁護士などの専門家にも相談する |
最初の確認のお願いは、たとえば次のように書きます。
○○様
いつもご利用いただき、ありがとうございます。○○クリーニングの△△です。
11月12日にお送りした請求書(請求書番号 2611-005、ご請求額 33,000円、お支払期限 11月30日)について、本日までにこちらでご入金を確認できておりません。
行き違いでお手続きいただいている場合は、失礼をお許しください。
お手数ですが、お支払いのご予定を教えていただけますでしょうか。請求書がお手元にない場合は、すぐにお送りいたします。
期日を決めて改めてお願いするときは、次のように書きます。
○○様
先日ご連絡した、請求書番号 2611-005(ご請求額 33,000円)の件で、改めてご連絡いたしました。
恐れ入りますが、12月10日(木)までにお振り込みいただけますでしょうか。
ご事情があってお支払いが難しい場合は、ご遠慮なくご相談ください。
例文の金額と番号は、説明用の架空のものです。連絡したら、日付と内容をメモの列に書いておきましょう。担当者が替わっても、どこまで連絡したかがわかります。
現金・カード払いの扱い
作業の当日に現金やカードで受け取った分も、入金管理表に1行入れておくと、「請求した金額」と「受け取った金額」を月ごとに見比べられます。当日払いの件数が多くて大変なら、入金管理表は後払いの請求だけにして、当日払いは売上シートの決済方法と入金状況の列で管理する、と分けてもかまいません。売上シートの作り方は「売上管理スプレッドシートの作り方」で解説しています。
現金で受け取ったとき
請求日、支払期限、入金日をすべて作業日にして、入金額を入れ、状態を「入金済み」にします。その日のうちに、手元の現金と、その日の現金の行の合計が合っているかを確かめましょう。日がたつほど、合わない理由がわからなくなります。
カードで受け取ったとき
お客様の支払いは当日に済んでいるので、入金日と入金額にはお客様が支払った日と金額を入れ、支払い方法を「カード」にします。カード決済のサービスから自社の口座に入る日や手数料の扱いは、サービスによって違います。口座への入金は、お使いのサービスの入金明細で別に確かめてください。
エクセルの入金管理の限界が見えてくるサイン
表を整えても、請求の件数やスタッフが増えると苦しくなる場面が出てきます。次のサインが増えてきたら、仕組みを見直す時期です。
- 請求書を作るたびに、同じ内容を入金管理表にも書き写している: 請求書、売上シート、入金管理表と、同じ金額を何度も入れている
- 現場で受け取ったお金の記録が、事務所に届くのが遅い: 現金やカードの分を、スタッフが戻ってから聞き取って入れている
- 入金の確認が、月末にまとめてになっている: 期限切れに気づくのが遅れ、連絡も遅れる
- 売上シートと入金管理表の金額が合わない: 月末に2つの表を見比べる作業が発生している
- 定期清掃など、毎月の請求が増えてきた: 請求書を作って表に書き写す作業が、月末に集中する
- 確かめられる人が1人しかいない: その人が休むと、入金の確認が止まる
入力の手間そのものを減らす考え方は「電話メモが売上シートに残らない訪問型予約管理」で解説しています。
予約革命なら、請求書づくりから入金の確認までつながる
私たち株式会社カノエが提供する「予約革命」は、ハウスクリーニング会社である私たちが自社のために作った、訪問サービス向けの予約管理システムです。
- 予約の明細から見積書を作り、見積書から請求書・領収書へワンクリックで転換できる
- 振込確認で、支払期限と入金の状況を管理できる
- スタッフが現場で決済金額や決済方法を入れて完了報告すると、現金・カード・振込などの決済チェックも一覧で確かめられる
- 定期清掃は、毎週・隔週・毎月の案件と月別の請求書まで管理できる
- 月度締めで、月ごとの売上を確認してCSVで出力できる
振込確認と月度締めは、スタンダードプランからお使いいただけます。開発元である当社の自社運用では、1件20〜30分かかっていた転記が、最短1分になりました。
料金はスタンダードが月額11,000円(税込)、Webからの予約も受けられるプロが月額38,500円(税込)で、別途、初期導入・サポート費が110,000円(税込・一括)かかります。「まず画面を見てみたい」だけでも大丈夫なので、予約革命の公式LINEからお気軽にご相談ください。くわしい機能は予約革命のトップページでご覧いただけます。
よくあるご質問
エクセルとGoogleスプレッドシート、どちらがいいですか?
この記事の関数と条件付き書式は、どちらでも同じ式で動きます。事務所と現場など、複数の人で同じ表を見るなら、ファイルを送り合わなくても全員が同じ内容を見られるGoogleスプレッドシートが向いています。入金を確かめる人が1人なら、エクセルでもかまいません。
入金管理表は、売上シートとは別に作ったほうがいいですか?
売上シートは作業ごとに1行、入金管理表は請求書ごとに1行と、行の単位が違います。管理会社への月まとめの請求があるなら、分けたほうが見やすくなります。個人のお客様の当日払いが中心なら、売上シートの入金状況の列だけでも回ります。
請求より多く振り込まれたときは、どうすればいいですか?
差額がマイナスになります。二重に振り込まれていないか、別の請求の分が含まれていないかを確かめてから、お客様に連絡して、返金するか次回の請求で調整するかを相談しましょう。どうしたかは、メモの列に残しておきます。
この表を帳簿の代わりに使えますか?
入金の確認には役立ちますが、帳簿として足りているかどうかは、事業の形によって変わります。税理士や税務署に確かめてください。
参考にした情報
- SUMIFS 関数(Microsoft サポート)
- COUNTIFS 関数(Microsoft サポート)
- EDATE 関数(Microsoft サポート)
- 条件付き書式を使用して Excel 内の情報を強調表示する(Microsoft サポート)
- ドロップダウン リストを作成する(Microsoft サポート)
- SUMIFS 関数(Google ドキュメント エディタ ヘルプ)
- COUNTIFS(Google ドキュメント エディタ ヘルプ)
- EDATE(Google ドキュメント エディタ ヘルプ)
- Google スプレッドシートで条件付き書式ルールを使用する(Google ドキュメント エディタ ヘルプ)
- セル内にプルダウン リストを作成する(Google ドキュメント エディタ ヘルプ)