この記事では「上位10パーセントまでの合計を求める方法」「上位10パーセントまでの平均を求める方法」について解説していきます。
これらの処理としては、主に
・PERCENTRANK関数とGESTEP関数とSUMIF関数
・PERCENTRANK関数とGESTEP関数とAVERAGEIFS関数
を使うと効率良く算出することができます。
それでは、実際のサンプルをみていきましょう。
なお、以下で足し算・合計に関する処理をまとめていますので、併せてチェックしてみてください♪
上位10パーセントまでの合計を求める方法【上位25パーセントなども】
エクセルにて上位10%までの合計を計算するには、複数の関数を組み合わせて処理しましょう。
具体的には、PERCENTRANK関数でAからJのランキングのパーセンテージを表示させた後、GESTEP関数で上位のランキングを判定し、SUMIF関数を使用するのが良いです。
複雑ですが順を追って解説します。
まずPERCENTRANK関数を使用して、ランキングのパーセンテージを表示させていきます。
任意のセルに=PERCENTRANK(C$2:C$11,C2,2)と入力するとOKです。
この関数は
=PERCENTRANK（配列、X、有効桁数）
という構成で成り立っています。
「数値」とは、ランキングを求めたい数値のことなので、今回の場合はC2セルを選択します。
「配列」とは、どこの範囲の中のランキングパーセンテージを求めたいか、という意味なので今回の場合はC2セルからC11セルを選択します。
計算式をコピーしたときに数値がずれない様に絶対参照とするのを忘れずに行いましょう。絶対参照はF４キーで設定することができます。
「X」とは、相対的な順位を調べる値なので、今回の場合はC2セルを指定します。
「有効桁数」は、結果として返される桁数なので今回の場合は２とします。
組み込むと、上述の「=PERCENTRANK(C$2:C$11,C2,2)」という数式になります。
ENTERにて処理を確定すると、D2セルにC2セルの数値に対応するランキングパーセンテージがでました。
あとは、数式のコピー＆ペースト、もしくはドラッグ＆ドロップして計算式を反映させれば完了です（上位10%の合計を出すための準備）。
見にくい場合は、書式設定でパーセント表示をします。
ここまできたら、閾値以上かどうかを返す関数のGESTEP関数で上位１０パーセントのデータを抽出します。
上位１０パーセントということは、９０パーセント以上のデータを抽出したいことになります。そのため「閾値」としてセル参照させるために、任意のセルに９０パーセントと入力しておきます。
具体的には=GESTEP(D3,$E$1)と入れましょう。
この関数は、
=GESTEP（数値、閾値）
という構成で成り立っています。
「数値」とは、閾値との大小を比較する数値のことで、今回はD2セルを指定します。
「閾値」とは、基準となる数値のことで、今回上位１０パーセントを指定したいのでE1セルを指定します。計算式をコピーしたときに数値がずれない様に絶対参照とするのを忘れずに行いましょう。
組み込むと、上述の「=GESTEP(D3,$E$1)」という数式になります。
閾値である９０％よりも下位ならば「０」、上位１０％ならば「１」と返されます。
これでE2セルにランキング判定がでました。
あとは、数式のコピー＆ペースト、もしくはドラッグ＆ドロップして計算式を反映させれば完了です。
次にSUMIF関数で上位１０パーセントの売上件数の合計を抽出していきます。
この関数は指定条件に合う合計を返す関数であり
＝SUMIF（範囲、検索条件、合計範囲）
という構成で成り立っています。
具体的には、=SUMIF(E2:E11,”=1″,C2:C11)と入れるといいです。
上の数式の意味を確認していきます。
上述の判定で１（つまり上位１０パーセント）と抽出されたデータの合計を求めたいので,「範囲」は、E2セルからE11セルを指定し「検索条件」は、「”=1”」と入力します。
「合計範囲」は、計算の対象となるセルの範囲です。今回は売上件数列（Ｃ2セルからC11セル）を指定します。
組み込むと、「=SUMIF(E2:E11,”=1″,C2:C11)」という数式になります。
これで上位１０パーセントの合計がでました。
ちなみに上位２５パーセントの場合は閾値を７５パーセントなどと、状況に応じて数値を変化させればOKです（以下のよう）。
エクセルにて上位１０パーセントの平均を求める方法【上位２５パーセントなど】
上記ではSUMIF関数を使用して上位10%の合計を算出しましたが、上位の平均を求めたいこともありうでしょう。
この場合はAVERAGEIFS関数を使用するとよく、具体的なやり方をみていきましょう。
ランキングパーセンテージを表示させるところまでは、上の上位の合計の計算方法と同じです。
ここまできたらAVERAGEIFS関数を使用し、=AVERAGEIFS(C2:C11,E2:E11,”=1″)と入れましょう。
この関数は条件に合う平均値を出力するものであり
＝AVERAGEIFS（平均対象範囲,条件範囲１,条件１,条件範囲2,条件2・・）
という構成で成り立っています。
上の数式の詳細は以下の通り。
「平均条件範囲」とは、平均を求めたい範囲なので、今回の場合は売上件数列（C2からC11セル）を指定します。
「条件範囲１」とは、特定の条件による評価の対象となるセルの範囲のことで、今回の場合は判定列（E2からE11セル）を指定します。
「条件１」とは、平均を求めるのに使用されるセルを定義する条件のことで、今回の場合は上位１０パーセントの平均を求めたいので「”=1″」とします。
組み込むと、上述のよう「=AVERAGEIFS(C2:C11,E2:E11,”=1″)」という数式になります。
ENTERにて確定させると、これで上位１０パーセントの売上件数の平均が算出されました。
上位１０パーセントが１件しかなくわかりにくいので、上位２５パーセントの平均も同様に計算してみた結果が以下の通りです。
まとめ エクセルにて上位の合計・平均を計算する方法【上位25パーセント等も】
この記事では、「上位10パーセントまでの合計を求める方法」「上位10パーセントまでの平均を求める方法」について解説しました。
慣れてくるといろいろと応用もできるので、エクセルのいろいろな機能を使いこなし、業務効率化を図っていきましょう。
