Excel。手早く、上位20%の平均値を算出するには、どうすればいいの。【Top 20%】

Excel。手早く、上位20%の平均値を算出するには、どうすればいいの。

＜AVERAGEIF+PERCENTILE.INC関数＞

データの平均値を算出するには、AVERAGE関数をつかえば、算出することができます。

次の表をつかって確認します。

F2には、

=AVERAGE(B2:B11)

という数式を設定しました。

オートSUMボタンにある平均をつかってもいいですね。

では、上位20%の平均値を算出したい場合は、どのようにしたらいいのでしょうか。

考え方として、上位20%なのかを調べる必要があります。

20%圏内ならば、平均値の対象になります。

そこで、登場するのがPERCENTILE.INC関数です。

この関数は、データ全体の上位何％（今回は20%）のポジションの値を算出することができます。

F4に

=PERCENTILE.INC(B2:B11,80%)

と数式をつくりました。

これで、上位20%の数値が算出できました。

その結果、88.2

あとは、この結果以上の値のデータだけの平均値を算出すればいいというわけです。

ところで、

=PERCENTILE.INC(B2:B11,80%)

の2番目の引数の「率」に注意が必要です。

上位20%だから20%と設定しがちですが、上位20%というのは、80%の位置にあたりますので、100-上位率　の数値を設定します。

F2に上位20%の平均値を算出します。

F4の88.2以上が平均値算出の条件です。

条件付きの平均値ということで、使う関数は「AVERAGEIF関数」です。

=AVERAGEIF(B2:B11,">="&F4)

F2には、91と算出できました。

データも88.2以上のものは、89と93しかないので、その平均値は91ですので、合致しております。

今回は、説明の為、F4に一度、上位20%の数値を算出しましたが、一つの数式にしても、問題はありません。

=AVERAGEIF(B2:B11,">="&PERCENTILE.INC(B2:B11,80%))

Excel。平均値だけでなく上位3位までの平均値を算出するにはどうしたらいいの。【TOP3】

Excel。平均値だけでなく上位3位までの平均値を算出するにはどうしたらいいの。

＜AVERAGEIF+LARGE関数＞

上位グループと下位グループに分かれているようなデータの場合、単純な平均値だけではなく、上位グループの平均値も合わせて算出したい場合があります。

そこで、今回は、上位3位までの平均値を算出する方法をご紹介します。

次のデータを用意しました。

A1:C10にデータがあります。

C2:C10に数値のデータがあります。この平均値を算出したのが、F1です。

F1の数式は、

=AVERAGE(C2:C10)

AVERAGE関数では、全体の平均値を算出することはできても、条件をつけた平均値を算出することができません。

そこで、上位3位という条件を含めることができる。

つまり単一条件の平均値を算出することができる、AVERAGEIF関数を使う必要があります。

F3に上位3位までの平均値を算出していきますので、次の数式を設定しました。

=AVERAGEIF(C2:C10,">="&LARGE(C2:C10,3))

これで、上位3位までの平均値を算出することができました。

AVERAGEIF関数の引数の説明をします。

最初の引数は、「範囲」です。

この範囲とは、2つ目の引数の条件が含まれている範囲のことです。

よって、C2:C10と設定します。

2つ目の引数の条件には、上位3位と意味する数式を設定する必要があります。

「上位」という順位に該当する値を算出（抽出）する場合には、LARGE関数を使います。

ただし、3位の値ではなく、1～3位までの数値である必要がありますので、比較演算子をつかいます。

そこで、

「”>=”」と比較演算子とLARGE関数を「&（アンパサンド）」で結合します。

3つ目の引数の、平均対象範囲は、1つ目の引数の範囲と同じなので、省略できますので、省略しました。

これで、上位3位までの平均値を算出することができます。

なお、ステップを踏んで算出すると次のようになります。

D列には、LARGE関数をつかって、3位の数値。246を表示してあります。

D2の数式は、

=LARGE($C$2:$C$10,3)

D10までオートフィルで数式をコピーします。

E2には、

=IF(C2>=D2,C2,"")

という数式をコピーします。

これで、上位3位までの数値だけにすることができます。この3件の平均値を算出するというわけです。

E11に設定した数式は

=AVERAGEIF(E2:E10,">0")

E11の結果と先程紹介した

=AVERAGEIF(C2:C10,">="&LARGE(C2:C10,3))

と同じになっていることが確認できます。

Excel。上位20%までの平均値を算出するにはどうしたらいいの【Top 20%】

Excel。上位20%までの平均値を算出するにはどうしたらいいの

＜AVERAGEIF＋PERCENTILE.INC関数＞

単純に平均を算出するだけならば、オートSUMボタンの中にある、「平均」やAVERAGE関数を使えば、簡単に算出することができます。

ただ、データ量が増えたときなどに、上位20%の平均値を算出したい場合、どのようにしたら、算出することができるのでしょうか？

上位20%のデータを抽出してその平均値を算出すればいいわけですが、オートフィルターをつかったりして、抽出してから平均値を算出するというのも面倒ですね。

できれば数式一発で算出したいわけですね。

次の表をつかって確認していきます。

そこで、このデータは、データ全体の中で上位何パーセントの場所にあるのかを判断することができるのが、PERCENTILE.INC関数（パーセンタイル・インクルーシブ）です。

PERCENTILE.INC関数は、全体を100%として、「率」に相当する値を算出することができます。

なお、親戚筋というか、PERCENTILE.EXE関数（パーセンタイル・エクスクルーシブ）というのがあります。

こちらの関数は、百分位を数える時に、0%と100%を含めないで数えてしまいます。

そのため、PERCENTILE.INC関数を使用します。

E2には、次の数式を設定しました。

=AVERAGEIF(B2:B11,">="&PERCENTILE.INC(B2:B11,80%))

これで、上位20%までの平均値を算出することができます。

AVERAGEIF関数をつかいます。

2つ目の引数である条件で、PERCENTILE.INC関数を使うわけですが、「上位20%」なので、20%にしては、ダメです。

上位20%とは、全体を100%としたとき、80%の位置を指すわけですから、80%にする必要があります。

あとは、比較演算子と数式を結合するために、比較演算子の「>=」を「”（ダブルコーテーション）」で囲み、「&（アンパサンド）」で結合する必要がありますので、数式を作るときに、注意が必要ですね。

Excel。上位3位まで平均を楽に算出するには、どうしたらいいの。【higher rank】

Excel。上位3位まで平均を楽に算出するには、どうしたらいいの。

＜AVERAGEIF＋LARGE関数＞

全体の平均を算出するだけならば、オートSUMボタンに含まれている、AVERAGE関数の「平均」をつかえば、すぐに算出することができます。

ただ、そのうち、上位3位までとか、限定した範囲の平均を算出する場合、どのようにしたら、楽に、手早く算出することができるのでしょうか。

次のデータを用意しました。

E1には、データ全体の平均がすでに算出されています。

E1の数式は、

=AVERAGE(B2:B11)

上位3位までの平均ということは、どうやって、上位3位までなのかを判断させる必要があります。

上位3位までのような算出をおこなうには、「LARGE関数」をつかうことで、対応できます。

そして、上位3位までだったら、平均を算出する対象ということで、条件付きの平均で、算出する必要があります。

単一条件の平均を算出するには「AVERAGEIF関数」をつかうと解決できます。

このようなことから、今回は、AVERAGEIF関数とLARGE関数のネストで対応することができそうです。

E2に数式を設定します。

=AVERAGEIF(B2:B11,">="&LARGE(B2:B11,3),B2:B11)

これで、上位3位までの平均を算出することができました。

それでは、ダイアログボックスを表示して、数式を確認しておきましょう。

最初の範囲と、平均対象範囲は、説明は不要だと思いますので、条件について確認します。

上位3位までということで、1位2位3位を算出しようと考えてしまいがちですが、1位などのランキングを算出するのは、LARGE関数ではなくて、RANK.EQ関数です。

上位3位という意味は、上位3位の数値以上なわけですね。

そのため、LARGE関数をつかって上位3位を算出した値以上という条件にすればいいわけです。

また、比較演算子は、「&（アンパサンド）」で結合する必要があります。

「">=LARGE(B2:B11,3)”」と「”（ダブルコーテーション）」で全体を囲いがちですが、これでは、文字になってしまうので、条件が合致しませんので、比較演算子を使用する時は注意する必要があります。

今回のケースは、「上位」でしたが「下位」を条件に使う場合には、「SMALL関数」をつかうことで対応することができます。

単純な平均だけではなくて、他の関数と組み合わせることで、必要とする平均を求めることができるかもしれませんので、色々試してみるといいかもしれませんね。

Excel。AVERAGEIF関数は単一条件で数値の平均値を算出します【AVERAGEIF】

Excel。AVERAGEIF関数は単一条件で数値の平均値を算出します

＜関数辞典:AVERAGEIF関数＞

AVERAGEIF関数

読み方： アベレージ・イフ　　

分類： 統計 

AVERAGEIF(範囲,条件,[平均対象範囲])

単一条件で数値の平均値を算出します　

Excel。単一条件で平均値を算出するならAVERAGEIF関数の出番です【function:AVERAGEIF】

Excel。単一条件で平均値を算出するならAVERAGEIF関数の出番です

＜AVERAGEIF関数＞

平均値を算出するならば、「AVERAGE関数」。

そして、単一条件（条件が一つだけ）で平均値を算出したいとなれば、「AVERAGEIF関数」の登場となるわけですね。

AVERAGEIF関数も、実務でお馴染みの関数になりました。

AVERAGEIF関数の基本情報を確認しておきましょう。

AVERAGEIF関数の読み方は「アベレージイフ」です。

カテゴリーとしては、「統計」に所属しています。

AVERAGEIF関数の引数も確認しておきましょう。

AVERAGEIF (範囲,条件,[平均対象範囲])

ポイントは、最初の引数は、2番目の引数の条件が含まれている範囲となっているのが、わかりにくいところですね。

範囲ではなくて、条件が含まれている範囲としてくれるといいのですが。

では、実際に次の表を使って確認しておきましょう。

F2には、2022/1/1より前の販売日の売上高の平均値を算出しています。

F2の数式は、

=AVERAGEIF(A2:A11,"<2022/1/1",C2:C11)

2番目の引数の条件が、「”（ダブルコーテーション）」で囲われていることがポイントですね。

直接条件を設定する時は、イメージしやすいのですが、セル参照型で、比較演算子を使うときは、もう一つポイントがあります。

では、F5の数式を確認してみましょう。

=AVERAGEIF(A2:A11,"<"&E5,C2:C11)

比較演算子だけが、「”（ダブルコーテーション）」で囲っています。

そして、「&（アンパサンド）」でセル名と結合させて使用します。

AVERAGEIF関数以外にも、～IF関数や～IFS関数がありますが、比較演算子とセル参照を組み合わせる場合には、注意するポイントがありますので、注意したいところですね。

Excel。AVERAGEA関数なのかAVERAGE関数なのか？平均を算出する関数が異なってくる【Average】

Excel。AVERAGEA関数なのかAVERAGE関数なのか？平均を算出する関数が異なってくる

＜AVERAGE関数・AVERAGEA関数・AVERAGEIF関数＞

平均を算出するには、オートSUMボタンにあるAVERAGE関数を使うのが一般的ですが、範囲内にゼロがある場合や文字がある場合など、状況によって、平均を算出する関数が異なってしまうので、確認してみましょう。

下記の表を用意しました

B列の得点1は、10件のデータがあります。この列が標準として比較していきます。
C列の得点2は、空白のデータがある場合。
D列の得点3は、0（ゼロ）にした場合。
E列の得点4は、欠席と文字にした場合。

関数の説明をしておきましょう。
12行目のAVERAGE関数は、お馴染みの関数ですね。
B12に数式を作ってみましょう。

数値1には、範囲選択するだけですから、B2:B11
ということで、B12の数式は、
=AVERAGE(B2:B11)
ですね。

このAVERAGE関数に単一条件をつけたのが、13行のAVERAGEIF関数です。

複数条件の場合は、AVERAGEIFS関数ですが、今回は、AVERAGEIF関数をつかっていきます。

14行のAVERAGEA関数はあまり見慣れない関数かもしれませんね。

AVERAGE関数は数値の平均値を計算し、AVERAGEA関数は文字列などのデータも含む値の平均値を計算するということを意味しています。

つまり、AVERAGEA関数は、文字列を0（ゼロ）として算出しています。

B14にAVERAGEA関数の数式をつくってみると、
=AVERAGEA(B2:B11)
となり、AVERAGE関数とAVERAGEA関数との違いは、ほとんど感じませんが、ダイアログボックスを表示すると、違いがハッキリ異なるのが確認できます。

AVERAGE関数は、引数が「数値1」となっていますが、AVERAGEA関数では、引数が「値1」となっています。

この差が生じることになります。

【算出してみる】

それぞれの関数で算出してみると、次のような結果が表示されてきました。

B列は、とくに条件を付ける必要がないので、AVERAGEIF関数はつかっていません。

AVERAGE関数・AVERAGEA関数ともに、78.4と同じ数値を算出しました。

C列は、C7は何も入力されていない「空欄」です。

AVERAGEIF関数のC13の数式は、
=AVERAGEIF(C2:C11,">0")
とゼロより大きいという条件をつけていますが、<>””という条件でもOKです。

AVERAGE関数・AVERAGEA関数。そして、AVERAGEIF関数ともに、76と同じ数値を算出しました。

データの件数を9件としているのがわかります。

D列になると、違いは現れはじめます。
AVERAGE関数・AVERAGEA関数は、68,4と算出していますが、AVERAGEIF関数は、76と算出しています。

D13の数式は、
=AVERAGEIF(D2:D11,">0")
AVERAGEIF関数は、ゼロよりも大きいという条件をつけていますので、9件で算出していますが、AVERAGE関数・AVERAGEA関数はゼロも数値なので、それを含めた10件ということで算出しています。

AVERAGEIF関数と算出結果が異なったのはそのためです。

E列を見てみましょう。
E13のAVERAGEIF関数は、
=AVERAGEIF(E2:E11,"<>欠席")
という計算式にしています。欠席という文字でなかったらという条件にしています。

欠席というのが文字のために、AVERAGE関数では対象外になるので、AVERAGE関数とAVERAGEIF関数の算出結果が同じ、76と算出されていますが、AVERAGEA関数は、欠席という文字であっても、文字列を「０」として計算してしまうので、D列と同じ結果の68.4を算出しています。

このように、平均値を算出するのでも、使用する関数によって算出結果が変わってきますので、注意が必要です。

Excel Technique_BLOG Categoryに追加しました。2019/08/22

Excel Technique_BLOG Categoryに追加しました。

＜目次サイト＞

このBLOGの記事を、
カテゴリー分けにした【Excel Technique_BLOG Category】に追加しました。

Excel。平均値ってこれでいいのか？よくないんじゃない　その１ 平均値と中央値

平均値というのは、あくまでも、総和をその個数で割った数でしかないわけですね。そうしますと、ビジネスではこのまま、オートSUMボタンの平均を使う訳にはいかないケースというのが、あるわけですね。

＜続きはこちら＞
Excel。平均値ってこれでいいのか？よくないんじゃない　その1 平均値と中央値
https://infoyandssblog.blogspot.com/2014/10/excel.html


Excel。平均値とゼロの関係。ゼロを含める？含めない？平均アレコレ その2 AVERAGE関数とAVERAGEIF関数

平均値というのは、総和を総個数で割ったものなので、ゼロも加えて、その個数も加えて総個数とした場合、確かにその平均値では、よろしくないケースがあります。そこで、今回は、ゼロを除いて平均値を算出する方法をご紹介します。

＜続きはこちら＞
Excel。平均値とゼロの関係。ゼロを含める？含めない？平均アレコレ その2 AVERAGE関数とAVERAGEIF関数
https://infoyandssblog.blogspot.com/2014/10/excel_19.html


Excel。平均値。今回は文字列をゼロとして計算したい。平均アレコレ その3 AVERAGEA関数

欠席などの文字がデータに入っているときに、ゼロとして認識したい時に使う、VERAGEA関数をご紹介しましょう。

＜続きはこちら＞
Excel。平均値。今回は文字列をゼロとして計算したい。平均アレコレ その3 AVERAGEA関数
https://infoyandssblog.blogspot.com/2014/10/excel3.html


Excel。一定の上限と下限の数値を除外して平均値を求める方法をご紹介 TRIMMEAN関数

上限10%と下限10％に該当するものを除外するとした場合、上限10%と下限10％を算出するだけでも大変ですよね。

＜続きはこちら＞
Excel。一定の上限と下限の数値を除外して平均値を求める方法をご紹介 TRIMMEAN関数
https://infoyandssblog.blogspot.com/2014/10/excel-trimmean.html

Excel関数辞典 VOL.04。AVEDEV関数～AVERAGEIFS関数

Excel関数辞典　VOL.04。AVEDEV関数～AVERAGEIFS関数

＜Excel関数＞

今回は、AVEDEV関数～AVERAGEIFS関数までをご紹介しております。
平均関係の関数で、日ごろからお世話になっている関数ばかりですね。
平均も様々な条件で使用する関数が異なりますので、知っておきたい関数ばかりですね。

AVEDEV関数
アベレージディブ
データ全体の平均値に対する個々のデータの絶対偏差の平均をも算出します。
AVEDEV(数値1,数値2…)

AVERAGE関数
アベレージ
数値の平均値を算出。指定した数値の合計÷数値の個数で平均を算出します。
対象は数値セルだけを含めて算出。
AVERAGE(数値1,数値2…)

AVERAGEA関数
アベレージ・エー
数値・文字列・論理値を含む平均値を算出。
引数に文字列が含まれる場合も分母のデータ件数に含み、値をゼロとして平均を算出。
AVERAGEA(数値1,数値2…)

AVERAGEIF関数
アベレージ・イフ
条件付きで数値の平均値を算出
AVERAGEIF(範囲,条件,平均範囲)

AVERAGEIFS関数
アベレージ・イフズ
複数条件付きで数値の平均値を算出
AVERAGEIFS(平均範囲,検索範囲1,検索条件1[,検索範囲2,検索条件2…])

Yandsシステムズの関数一覧サイト
https://sites.google.com/view/yandsssystems/function

Excel。平均値とゼロの関係。ゼロを含める？含めない？平均アレコレその２

Excel。平均値とゼロの関係。ゼロを含める？含めない？
平均アレコレその２

AVERAGE関数とAVERAGEIF関数

企業研修でよくご紹介するケースが平均値の時にありまして、確かに、
なるほど…と現場ではあるよね。
というものがありまして、その中のひとつ。

平均値を算出するときにゼロが混ざっていることがあって、
これを除いて算出しないといけないのでは？というもの。

確かに、平均値というのは、総和を総個数で割ったものなので、ゼロも加えて、
その個数も加えて総個数とした場合、確かにその平均値では、よろしくないケースがあります。

そこで、今回は、ゼロを除いて平均値を算出する方法をご紹介します。
また、ケースによっては、職業訓練の時にもご紹介しております。

で、今回使用する関数は、AVERAGEIF関数です。

下記の表を見てみましょう。

C9には、通常の平均値を算出してあります。
作り方は、オートSUMボタンの▼にある、平均を使うと範囲選択をするだけなので、簡単ですね。

C9の数式は、=AVERAGE(C3:C8)

で、結果は、45.5。

この45.5。見た目で、すでに、なんか低くないか？と感じられると思います。そうなんでね。
品川さんの0ポイントが混ざっているからなのです。

そこで、ゼロを除いて平均値を算出すれば、いいのでは？と思った場合、
どうしたらいいのかというと、AVERAGEIF関数を使えば、

ゼロを除いて平均値を算出することが出来るのです。

それでは、C10をクリックして、品川さんのゼロを除いて平均値を算出してみましょう。
まずは、AVERAGEIF関数のダイアログボックスを表示しましょう。

まずは、範囲から。
これは、２個目のボックスの条件がある範囲になりますので、
今回は、ゼロで無いものを算出対象にしたい訳なので、この範囲は、C3:C8となりますね。

続いて、条件ですが、ゼロで無いというのが条件になります。
そこで、ゼロであるなら、"=0"でいいのですが、

今回は、ゼロで無いなので、NOTとなりますから、"<>0"と入力します。

条件式なのに、”　”ダブルコーテーションで囲むというのは、不自然に感じるかもしれませんが、
関数自体が数式ですでに=ではじまっていますから、途中で=を入れるわけにいかないので、”　”ダブルコーテーションが付くわけですね。

そして、平均対象範囲。
これは、省略すると範囲と同じ範囲指定になるのですが、きちんと入力しておきましょう。

この平均対象範囲は、C3:C8と入力します。

あとは、OKボタンをクリックしましょう。

すると、C10には、54.6と算出されましたね。このほうが、イメージに合っていますね。
ただし、今回は、ゼロを除いて平均を出したかったわけでして、

次回は、ゼロではなくて、欠席などの文字が入っている場合で、
欠席した人数も含めて個数とした平均値を算出する場合をご紹介したいと思います。

Excel。平均を求める時にゼロを除かないと…AVERAGEIF関数

Excel。平均を求める時にゼロを除かないと…

AVERAGEIF関数

Excel。平均を求めるのは、AVERAGE関数がおなじみですよね。
オートΣボタンにも収納されているぐらい、おなじみ。
ただ、求める範囲のデータにゼロが含まれていると、
平均がちょっと、異なってしまうのです。
こういうゼロを除きたい場合どうしたらいいのかというと、
AVERAGEIF関数を使うと簡単に求めることが出来ます。

それでは、やってみましょう。
下記のような表があります。
まずは、B9をクリックして、通常のAVERAGE関数で算出してみます。

平均は、55.714286になります。
ただ、0が含まれていますね。
このゼロを除いて算出しないと、おかしくなっちゃうわけですね。
そこで、AVERAGEIF関数を使ってみましょう。

B10をクリックして、AVERAGEIF関数のダイアログボックスを表示しましょう。

範囲には、B2:B8と入力します。
条件には、”<>0”と入力します。
これは、ゼロ以外という意味です。<>はこういう時に使いますね。
平均対象範囲は空白のままとします。
OKボタンをクリックして完成ですね。

ゼロを除いたら、65と算出されました。
約10ポイントも異なりますね。
このように、

ゼロを除きたい場合は、AVERAGEIF関数を使うこと

を知っておくといいですね。