入金済みか未収か（SUMIFS + IF）
問題
問題文
月末の消込です。請求書一覧は A2:B5、入金ログは D2:E8 にあり、1つの請求書が分割で入金されることもあります。A2 の請求書について「Paid」か「Outstanding」を Status セル G2 に入れてください。
「入金の行があるか」ではなく「その請求書の入金合計」で判断します。1回の分割入金では 500 の請求書は消えません。
問題のデータ
表でデータ全体を見る(選択・コピーできます)
| A | B | D | E | G | |
|---|---|---|---|---|---|
| 1 | Invoice | Invoiced | Invoice | Paid | Status |
| 2 | INV-501 | 500 | INV-501 | 200 | |
| 3 | INV-502 | 320 | INV-501 | 300 | |
| 4 | INV-503 | 180 | INV-502 | 120 | |
| 5 | INV-504 | 900 | INV-503 | 180 | |
| 6 | INV-504 | 400 | |||
| 7 | INV-504 | 400 | |||
| 8 | INV-502 | 200 |
ポイント解説
入金ログは1つの請求書に複数行あるため、VLOOKUP では最初の1件しか拾えません。SUMIFS で請求書ごとの合計に畳んでから IF で状態に変えます。一部入金は Outstanding にする必要があるので、比較は >= にします。
解き方のヒント
回答は G2 に入力します。送信時は 3 通りの入力で数式を再実行するので、数値を直接書くと必ず失敗します。数式はセルを参照してください。
- 対象セルは G2
- まず A2 の入金を合計：SUMIFS
- その合計と B2 を比べる
- 形：=IF(?, ?, ?)——条件は比較で、検索ではない
模範解答
模範解答を表示(2 通り・まずは自分で試してみましょう)
方法1：IF の中に SUMIFS
=IF(SUMIFS($E$2:$E$8, $D$2:$D$8, A2) >= B2, "Paid", "Outstanding")
INV-501 は 200 + 300 = 500 の入金で、請求額 500 と一致するため Paid。片方を減らせば同じ式が Outstanding を返します。
方法2：合計範囲が最後の SUMIF
=IF(SUMIF($D$2:$D$8, A2, $E$2:$E$8) >= B2, "Paid", "Outstanding")
SUMIF は条件範囲が先、合計範囲が最後です。この2つを入れ替えるのが典型的な SUMIF/SUMIFS の取り違えです。
関数ガイド
よくある質問
請求書が入金済みかどうかはどう判定しますか？
その請求書の入金合計と請求額を比べます：=IF(SUMIFS(入金額, 請求書列, 請求書番号) >= 請求額, "Paid", "Outstanding")。
入金ログに VLOOKUP を使わないのはなぜ？
VLOOKUP は最初の1行しか返さないため、分割入金の顧客を未収と誤判定します。SUMIFS で合計する必要があります。