管理費・会費の「未収」を毎月ひと目で見る表の作り方｜Excelで「済・一部・未」と未収額を出す

管理組合の管理費・修繕積立金や、自治会・サークルの会費を Excel で管理していると、月末に「誰が、何月分を、まだ払っていないか」を通帳と見比べて数え直すことがあります。遅れて入った入金や、2か月分まとめての入金があると、なおさら手間がかかります。

この記事では、入金を1件1行で入れるだけで、部屋（会員）ごと・月ごとに「済／一部／未」と未収額が出る表の作り方を紹介します。例の部屋番号・金額はすべて架空です。

入金は「1件1行」で、何月分かを別の列に入れる

まず、入金を記録するシート「入金」を作り、1回の入金を1行にします。

入金日／部屋／何月分／金額

ポイントは「何月分」の列です。入金日とは別に、その入金がどの月の分なのかを入れます。たとえば9月20日に8月分が入金されたら、入金日は9月20日、何月分は8月です。何月分は「2026/8/1」のように月の1日の日付で入れ、表示の形式を「m"月分"」にしておくと、画面には「8月分」と出ます。

2か月分をまとめて振り込まれたときは、同じ入金日で2行に分けて入れます。1行に2か月分の金額を入れてしまうと、どちらの月の分かがわからなくなるためです。

収納状況のシートに、部屋と月の表を作る

別のシート「収納状況」の上に、開始月（B1）と、今月まで見たい対象月（B2）を入れます。その下に、縦に部屋番号（A列）と月額（B列）、横に月（C列から右へ12か月）を並べます。月の見出しは、開始月から1か月ずつずらす式にしておくと、年度が変わっても開始月を直すだけで済みます。

C5：=EDATE($B$1,0)、D5：=EDATE($B$1,1)、……

各マスに「済／一部／未」を出す

部屋と月が交わるマスには、その部屋・その月分の入金の合計を SUMIFS で出し、月額と比べます（101号室の4月のマスの例）。

=IF(C$5>$B$2,"",IF(SUMIFS(入金!$D:$D,入金!$B:$B,$A6,入金!$C:$C,C$5)>=$B6,"済",IF(SUMIFS(入金!$D:$D,入金!$B:$B,$A6,入金!$C:$C,C$5)>0,"一部","未")))

入金日ではなく「何月分」で集計しているので、遅れて入った入金も、本来の月のマスが「済」になります。例では、202号室の8月分が9月20日に入金されていましたが、8月のマスはきちんと「済」になりました。対象月より後の月は空欄のままにしているので、まだ来ていない月が「未」と表示されることもありません。

「未」と「一部」のマスに条件付き書式で色をつけておくと、表を開いたときに、どこが足りないかがひと目でわかります。

未収額は「月額×月数 − 入金の合計」で出す

部屋ごとの未収額は、開始月から対象月までの月数を出しておき（B3）、月額×月数から、その期間分の入金の合計を引いて出します。

月数：=(YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1

未収額：=MAX(0,B6*$B$3-SUMIFS(入金!$D:$D,入金!$B:$B,A6,入金!$C:$C,">="&$B$1,入金!$C:$C,"<="&$B$2))

その下に、未収額の合計と、COUNTIF で「未収額が0より大きい部屋の数」を出せば、月次の報告に書く数字がそろいます。例の8戸・4〜9月では、9月時点で未収は3戸・62,000円（102号室の9月分、203号室の7〜9月分、301号室の9月分の不足）と出ました。対象月を8月に変えると、1戸・28,000円に変わります。

なお、この未収額は期間の合計で見ているため、ある月に多く払った分は、ほかの月の不足と相殺されます。月ごとのマスの「済／一部／未」と合わせて見ると、どの月の分が足りないかがわかります。

入れまちがいに印をつける

入金のシートの右に、入れまちがいに気づくための列を1つ足しておきます。

=IF(B2="","",IF(COUNTIF(収納状況!$A$6:$A$13,B2)=0,"部屋番号を確認",IF(C2="","何月分を入力","")))

部屋の一覧にない番号（打ちまちがい）や、何月分を入れ忘れた行は、収納状況のどのマスにも数えられません。印をつけておけば、「入金はあるのに未のまま」という食いちがいの原因をすぐに見つけられます。例でも、一覧にない「105」と入れた行と、何月分が空の行には印がつき、どちらも収納状況には入りませんでした。

まとめ

入金は1件1行にして、入金日とは別に「何月分」を入れる。部屋×月のマスは SUMIFS で「済／一部／未」を出す。未収額は月額×月数から入金の合計を引く。入れまちがいには印をつける。この形にしておくと、通帳と見比べて数え直す時間を減らしやすくなり、遅れて入った入金も本来の月に正しく反映されます。

管理費・会費の管理表を作ることもできます

今回の表に、会計ごとの月次の収支報告（前月繰越・収入・支出・次月繰越）を組み合わせた、管理組合向けの見本も作っています。お手元の部屋の一覧・月額・入金の記録に合わせた表を作りたい、今の表の数字が合わない原因を調べたい、といった場合は、下のサービスからご相談ください。マクロを使わず、関数だけで作ることもできます。