【エクセル】下からn番目までの合計値を計算する方法!

エクセル
この記事は約2分で読めます。
スポンサーリンク
スポンサーリンク

【エクセル】最下位n番目までの合計値を計算する方法!

・SMALL関数 と
・SUMPRODUCT関数
を組み合わせて、最下位N人の点数の合計を求める関数を紹介します。

出力結果としては下記の通り、
点数の低い5人の合計値を出しています。
※ランダムで点数を作成したためわかりにくいですが、
「8+20+23+28+21=100」となっています。

使用した数式

使用した数式は、下記のとおりです。

=SUMPRODUCT ( SMALL( 範囲、{ 1,2,3,4,5} ))

数式の説明

SMALLは、範囲内の「n番目に小さい」値を返す関数です。

例えば、

= SMALL( range 、1 ) //最小
= SMALL( range 、2 ) // 2番目に小さい
= SMALL( range 、3 ) // 3番目に小さい

となります。

しかし、SMALL関数はこのような使い方だけでなく、
配列を指定してあげ、

= SMALL( A1:A10 、{ 1 、2 、3 } )

と記述することで、

{ 最小値1 ,最小値2 ,最小値3 }

と、指定した番号の配列を返してくれる関数となっています。

この結果を、SUMPODUCTと組み合わせると、

SUMPRODUCT ( SMALL( A1:A10 、{ 1 、2 、3 } ))
=SUMPRODUCT({ 最小値1 ,最小値2 ,最小値3 })

と、小さい順に1~3番目の数値を合計することができます。

n数が大きい場合

5個くらいまでは、手書きで対応できますが、
n数が大きくなると、手間がかかります。

その場合は、ROW関数とINDIRECT関数を使用する配列定数を作成することで対処できます。

例えば

= SUMPRODUCT(SMALL( 範囲 、ROW(INDIRECT("1:20" ))))

とすると、20番目までの合計値を出すことができます。

これを応用し、A1セルの文字に対応させると、

= SUMPRODUCT(SMALL( 範囲 、ROW(INDIRECT("1:"& A1 ))))

と記述することで、n数をA1セルで切り替えることができます。

まとめ

SMALLの配列の使い方は難しいですが、
このようなに使うと非常に便利な使い方ができます。

ぜひ、利用してみてください!

コメント欄

タイトルとURLをコピーしました