上位3件の注文の合計（SUMIF + LARGE）
問題
問題文
成約一覧 A2:C9 には、金額の大きく異なる8件の注文が並んでいます。ボーナスは金額の大きい3件だけで計算されるため、「上位3件の合計」を出す必要があります。作業列を足すことも、並べ替えることもできません。結果を Top 3 total のセル E2 に入力してください。
しきい値は固定の数字ではありません。テストケースによって上位3件に入る注文が変わるので、境界はデータから計算する必要があります。
問題のデータ
表でデータ全体を見る(選択・コピーできます)
| A | B | C | E | |
|---|---|---|---|---|
| 1 | Order | Rep | Amount | Top 3 total |
| 2 | ORD-2001 | Ana Li | 1250 | |
| 3 | ORD-2002 | Ben Kim | 800 | |
| 4 | ORD-2003 | Chen Wu | 960 | |
| 5 | ORD-2004 | Dana Ortiz | 600 | |
| 6 | ORD-2005 | Eve Novak | 330 | |
| 7 | ORD-2006 | Ana Li | 700 | |
| 8 | ORD-2007 | Chen Wu | 480 | |
| 9 | ORD-2008 | Ben Kim | 1200 |
ポイント解説
LARGE(範囲, 4) は4番目に大きい金額なので、SUMIF(範囲, ">"&LARGE(範囲, 4)) はそれより大きい値だけを残します。つまり上位3件です。よく教えられる書き方に ">="&LARGE(範囲, 3) があり、3番目の値をそのまま含めます。どちらも金額が重複しない前提で、3位が同額で並ぶと「より大きい」は少なく、「以上」は多く数えてしまいます。その場合は SUM(LARGE(範囲, {1,2,3})) が安全です。
解き方のヒント
回答は E2 に入力します。送信時は 3 通りの入力で数式を再実行するので、数値を直接書くと必ず失敗します。数式はセルを参照してください。
- 回答セルは E2 です
- 金額は C2:C9 にあります。順位の列はないので、それで絞ることはできません
- LARGE(C2:C9, 4) は4番目に大きい金額を返します。それより大きい値が上位3件です
- 形：=SUMIF(?, ">"&LARGE(?, ?), ?)
模範解答
模範解答を表示(2 通り・まずは自分で試してみましょう)
解法1：4番目より大きい
=SUMIF(C2:C9, ">"&LARGE(C2:C9, 4))
LARGE(C2:C9, 4) が実行時に境界（4番目に大きい金額）を計算し、条件がそれより大きい値をすべて足します。「上位 N 件」は 4 を N+1 に置き換えるだけなので、並べ替えも作業列も要りません。
解法2：3番目以上
=SUMIF(C2:C9, ">="&LARGE(C2:C9, 3), C2:C9)
同じ3件を別の道から求めます。3番目に大きい金額を境界にして、それも含めます。考えるのは短くて済みますが、3位が同額で並ぶと静かに壊れ、3件ではなく4件になります。
関数ガイド
よくある質問
並べ替えずに上位3件の合計を出すには？
=SUMIF(C2:C9, ">"&LARGE(C2:C9, 4)) と書くと、4番目に大きい金額を超える値だけが合計されます。上位 N 件なら 4 を N+1 にします。境界はデータから計算されるので、表の順序は自由です。
3位が同額で並んだ場合はどうなりますか？
上の2つの書き方はどちらも正しくありません。「より大きい」は並んだ2件を黙って落として3件未満になり、「以上」は4件に増えます。同額が起こりうるなら =SUM(LARGE(C2:C9, {1,2,3})) を使います。これはしきい値ではなく順位で取ります。
SUMPRODUCT でもできますか？
できます。=SUMPRODUCT(LARGE(C2:C9, {1,2,3})) や =SUMPRODUCT((C2:C9>=LARGE(C2:C9,3))*C2:C9) です。SUMIF のほうが短く読めますが、同じ数式で OR や大文字小文字の区別も扱うなら SUMPRODUCT が逃げ道になります。