エクセルでデータ分析を行う方も多いと思いますが平均（AVERAGE）はよく使用する関数ですね。
AVERAGE関数についてはこちらから☟
【AVERAGE関数】エクセルで平均値を求める方法について説明します！ ► 独学エクセル塾 (dokugakuexcel.com)
データの種類によっては様々な要因でバラツキが発生しますが、時には異常値も紛れることがあります。
しかし収集したデータに異常値が紛れていれば正確な分析はできません。
例えば交通量の調査でも、その付近でイベントやライブが行われると
通常とは異なったデータになってしまいます。
そこで今回は異常値を取り除くのに最適であり、
指定したパーセントの上位・下位のデータを除外して平均を求める方法について紹介します。
それでは見ていきましょう。
⬛︎こんな事ができるようになる！
極端に大きい・小さいデータ・異常値を除外して平均をもとめることができるようになる。
除外の割合も変更する事が可能です。
⬛︎関数の仕組み
=TRIMMEAN(平均したい範囲,除外する割合)
平均したい範囲は配列入力となります。
複数の配列を入力する場合でセルが1つ1つ離れている場合は単一セルを選択していくしかありません。
※この記事でも紹介します。
⬛︎指定割合の数値を上位・下位から除外して平均を求める
では実際に関数を使用していきましょう。
・通常の使用方法
まずは通常の使用方法から紹介します。
下図の見本をご覧ください。
C3からC12まで1日の交通量が入力されてあります。
今回はこちらの平均を求めたいとします。
しかし5月27日と5月28日はイベントがあったため、交通量が大幅に多くなってます。
通常の平均値を見てみましょう。
平均値は「4020.8」となっており、5月27日と5月28日が平均値をあげているみたいです。
今回は通常の交通量を調査したいため異常値は除外してみましょう。
ここで使用するのがTRIMMEAN関数です。
再度関数の仕組みを見てみましょう。
=TRIMMEAN(平均したい範囲,除外する割合)
今回は上位・下位40%を除外してみたいと思います。
平均したい対象範囲は数値が入力されてあるC3からC12になります。
40%は数値になおすと0.4ですね。
では関数に当てはめましょう。
結果はこのようになります。
=TRIMMEAN(C3:C12,0.4)
では入力したいと思います。
実行した結果はこちらです。
結果は「677.5」となってますね。
通常の平均は「4020.8」なので異常値が除外された事がわかります。
10個あるデータで上位・下位40パーセントとなるのは4つなので
上位2つ・下位2つのデータが除去された平均という事になります。
上位の「15564」「19756」、下位の「245」「578」が除外され、
残った数値の平均が「677.5」ということになりますね。
・複数範囲の使用方法
先程は１つの配列の平均でしたが
今度は複数のセル・配列の平均を求めてみましょう。
先ほどのC3からC12のデータに加え、F3からF12にも新しいデータを用意しました。
通常のAVERAGE関数では「=AVERAGE(C3:C12,F3:F12)」となりますね。
しかしTRIMMEAN関数は「,」の後に割合を入力するので
「=TRIMMEAN(C3:C12,F3:F12,0.2)」なんて事ができません。
実際に入力してみてもこのようになります。
エラーが発生してしまいました。
ではどうすれば複数範囲を参照できるのでしょうか。
答えは配列をさらに「（）」で囲みます。
では先ほどの関数にカッコを加えてみましょう。
=TRIMMEAN((C3:C12,F3:F12),0.2)
結構単純ですよね。
結果はこのようになります。
無事、複数範囲でも計算することができました。
⬛︎ TRIMMEAN関数の注意点
説明を見て思った方もいると思いますが
TRIMMEAN関数の結果はあくまで目安だと思ってください。
除外したい割合が定まっている場合は正確な結果はでますが、
割合を主観で決めると結果にバラツキが生じてしまいます。
さらに異常値が上位方向または下位方向に偏ってしまったら、
対する上位・下位は正常値でも除外されてしまいます。
より正確な平均を求めたい場合はAVERAGEIF関数やAVERAGEIFS関数を
ご参照ください。
記事についてはこちらから☟
【AVERAGEIF関数】エクセルで条件に当てはまる平均値を求める ► 独学エクセル塾 (dokugakuexcel.com)
【AVERAGEIFS関数】エクセルで複数の条件に当てはまる平均を求める ► 独学エクセル塾 (dokugakuexcel.com)
⬛︎よくあるエラーと対処法
・#NUM!エラーとなる
これは除外する割合が100パーセントを超えている時に起きます
関数でいうと
=TRIMMEAN(平均したい範囲,除外する割合)
の除外する割合にあたります。1が100%なので1以下の数値でなければなりません。
では見本を見てみましょう。
=TRIMMEAN(C3:C12,1)
結果はこちらです。
NUM!のエラーとなりました。
100パーセント除外は数値がなくなるのでエラーとなります。
⬛︎公式の説明
わかりやすいように実際の内容とは異なった語句・文字で説明しています。
公式の内容をご覧になりたい方は下記リンクをご参照ください☟
⬛︎まとめ
いかがだったでしょうか。
ある程度でいいから異常値を取り除いて確認したい場合はとても便利な関数です。
さらに割合も自分で決めれるのがいいですね。
是非参考にしてみてください。
それでは次回の記事でお会いしましょう。
コメント