承認額を超えた経費精算の合計（SUMIF）
問題
問題文
経理から出てきた一覧 A2:C8 には、金額の大小にかかわらず経費精算が並んでいます。監査で見るのは承認額を超えた分だけで、その承認額は D2 に入っています（四半期ごとに変わるため）。承認額を「超える」精算だけの合計を Total above limit のセル E2 に入力してください。
検索条件は D2 から組み立てます。承認額はテストケースごとに変わるので、500 も答えも数式に直接書けません。
問題のデータ
表でデータ全体を見る(選択・コピーできます)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Claim | Requester | Amount | Limit | Total above limit |
| 2 | EXP-101 | Ana Li | 420 | 500 | |
| 3 | EXP-102 | Ben Kim | 500 | ||
| 4 | EXP-103 | Chen Wu | 1250 | ||
| 5 | EXP-104 | Dana Ortiz | 80 | ||
| 6 | EXP-105 | Ana Li | 900 | ||
| 7 | EXP-106 | Eve Novak | 610 | ||
| 8 | EXP-107 | Ana Li | 1500 |
ポイント解説
SUMIF(範囲, 検索条件, [合計範囲]) は、範囲の各セルを検索条件と比べ、条件に合う行の合計範囲の値を足します。条件を調べる列と合計する列が同じとき（「X より大きい値だけを合計」）は、3番目の引数を省略すると SUMIF は自分で調べたセルをそのまま合計します。演算子は & でセルとつなぎ、">"&D2 のように実行時に1つの文字列を作ります。
解き方のヒント
回答は E2 に入力します。送信時は 3 通りの入力で数式を再実行するので、数値を直接書くと必ず失敗します。数式はセルを参照してください。
- 回答セルは E2 です
- 承認額は D2 にあります。条件は ">"&D2 の形で組み立て、">500" のように書いてはいけません
- 金額は C2:C8 にあります。条件を調べる範囲がそのまま合計する範囲なので、3番目の引数は省略できます
- 形：=SUMIF(?, ">"&?, ?)
模範解答
模範解答を表示(2 通り・まずは自分で試してみましょう)
解法1：合計範囲を省略した SUMIF
=SUMIF(C2:C8, ">"&D2)
SUMIF は ">"&D2 で作った文字列を C2:C8 と比べ、条件に合うセルを足します。合計範囲を指定していないので、調べたセルそのものを合計します。これが「X より大きい値だけを合計する」の基本形です。
解法2：合計範囲を明示した同じ数式
=SUMIF(C2:C8, ">"&D2, C2:C8)
結果は同じで、条件範囲と合計範囲が同じ列だと明示した書き方です。あとで調べる列と合計する列を分けるときにも、そのまま書き換えて使えます。
関数ガイド
よくある質問
しきい値を超える金額だけを合計するにはどうしますか？
=SUMIF(C2:C8, ">500") と書くと、500 より大きい値だけが合計されます。しきい値がセルにあるときは & で演算子とセルをつなぎ、=SUMIF(C2:C8, ">"&D2) とします。しきい値が変わっても数式はそのまま使えます。
しきい値が最大金額と同じとき、合計が 0 になるのはなぜですか？
> は「より大きい」という意味だからです。最大金額がしきい値と同じなら条件に合う行が1つもなく、SUMIF はエラーではなく 0 を返します。同じ値も含めたいときは ">=" を使ってください。
SUMIF の3番目の引数は省略できますか？
できます。省略すると、条件範囲そのものを合計します。「X より大きい値だけを合計する」のように、調べる列と合計する列が同じ場合に便利です。