上位N位(下位N位)までの値を合計する【Googleスプレッドシート】

統計, 関数

概説

スプレッドシートを操作していると、上位○位(下位○位)までの値の合計が欲しい場合がよくあると思います。

例えば上位3支店の売上合計を求めたりなどです。

そのような場合、2つの関数を組み合わせ応用することで、合計値を求めることが出来ます。

具体的には SUMIF 関数と LARGE 関数を組み合わせます。

SUMIF 関数は「条件に合致する値の合計を取得する」関数です。(詳しくは「条件に合致する値の合計を取得する」の記事を参照下さい)

LARGE 関数は「上位または下位N位の値を抽出する」関数です。(詳しくは「上位または下位N位の値を抽出する」の記事を参照下さい)

これらの関数を組み合わせることで、上位○位(下位○位)までの合計値を求めようと思うのですが、どのようにすればいいか想像が付きますでしょうか?

特定のセル範囲のなかで上位○位までの合計値を求めるということは、”そのセル範囲で○番目に大きい値以上だけを合計していく” ということとイコールです。

“〜の値以上だけを合計する” というのを SUMIF 関数で計算、”セル範囲で○番目に大きい値” を LARGE 関数で抽出するという訳です。

以下に実際の計算式(関数)を記します。

方法

上位○位までの値を合計するには「SUMIF」関数と「LARGE」関数を、下位○位までの値を合計するには「SUMIF」関数と「SMALL」関数を組み合わせます。

上位○位までの値を合計する
=SUMIF(セル範囲,“>=”&LARGE(セル範囲,順位))
入力例:上位3位までの合計値を求める場合は =SUMIF(B2:B6,”>=”&LARGE(B2:B6,3))
下位○位までの値を合計する
=SUMIF(セル範囲,“>=”&SMALL(セル範囲,順位))
入力例:下位2位までの合計値を求める場合は =SUMIF(B2:B6,”>=”&SMALL(B2:B6,2))
考え方
上位3位までの合計値を求めたい

3番目に大きい値である300以上だけを合計すれば良い

計算式にすると =SUMIF(B2:B6,”>=300″)

データ変更の可能性や保守性のことを考慮し、3番目に大きい値(300)の抽出を自動にしたい

LARGE(B2:B6,3)で3番目に大きい値の抽出を自動にできる

組み合わせると =SUMIF(B2:B6,”>=”&LARGE(B2:B6,3)) となる。(※&記号は文字列を結合する記号)

図説

<図1>
=SUMIF(セル範囲,">="&LARGE(セル範囲,順位))を入力し、 エンターキーを押下

<図2>
上位3位までの合計値を求めることが出来た