Excel で条件に基づいて最小値または下位 N 個の値を合計する

以前のチュートリアルでは、データ範囲内の最小 n 個の値を合計する方法を解説しました。本記事では、さらに高度なテクニックとして、Excel で1 つまたは複数の条件に基づいて最小 n 個の値を合計する方法をご紹介します。

Excel で条件に基づいて最小値または下位 N 個の値を合計する

たとえば、次のようなスクリーンショットに示すデータ範囲があるとします。この中から、製品「Apple」の注文の中で最も小さい金額から3 件を合計したい場合です。

Excel で条件付きの範囲から下位 n 個の値を合計するには、SUM 関数、SMALL 関数、および IF 関数を組み合わせて配列数式を作成します。一般的な構文は次のとおりです。

• range=criteria 特定の条件に一致するセル範囲。
• values: 合計する対象となる下位 n 個の値を含むリスト。
• N： n 番目に小さい値。

上記の問題を解決するには、以下の配列数式を空白セルに入力してください。

次に、Ctrl + Shift + Enterキーを同時に押すと、以下スクリーンショットのような正しい結果が得られます。

数式の説明：

=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))

• IF(($A$2:$A$14=D2), $B$2:$B$14)：範囲 A2:A14 内の製品が「Apple」と一致する場合、注文リスト（B2:B14）から対応する数値を返します。一致しない場合は FALSE が表示されます。結果は次のようになります：{800;FALSE;FALSE;FALSE;1000;230;FALSE;FALSE;1600;FALSE;900;FALSE;500}。
• SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}): この SMALL 関数は FALSE 値を無視して、配列内の最小値から3 番目までの値を返します。その結果、{230,500,800}が得られます。
• SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))=SUM({230,500,800}): 最後に、SUM 関数が配列内の数値を合計し、結果として1530 が得られます。

ヒント：2 つ以上の条件を処理する場合

2 つ以上の条件に基づいて下位 n 個の値を合計する必要がある場合は、IF 関数内で「*」文字を使って他の範囲と条件を追加するだけです。例：

• Range1=criteria1 最初の条件に一致する最初のセル範囲。
• Range2=criteria22 番目の条件に一致させるための2 番目のセル範囲。
• Range3=criteria33 番目の条件に一致させるための3 番目のセル範囲。
• values: 合計したい下位 n 個の値を含むリスト。
• N: n 番目に小さい値。

たとえば、販売担当者が Kerry である製品「Apple」の注文の中から下位3 件を合計したい場合は、以下の数式をご利用ください。

次に、Ctrl + Shift + Enterキーを同時に押すと、必要な結果が得られます。

使用された関連関数：

関連記事：