一部のデータを除外して平均値を計算する(TRIMMEAN)

平均値を取る場合に、あまり極端なデータが含まれていると正しい値と隔離してしまうことがあります。そういった場合に平均を取りたいデータを並び替えて上と下の極端なデータを除外して平均を取る事で、より正確な平均値を取る方法を見ていきます。

=TRIMMEAN(セル範囲, 除外割合)

引数に指定したセル範囲の中で、指定した割合のデータを除外して平均を計算します。割合には0.1や0.2といった値を指定します。

例えば0.2を指定した場合上下合わせて20％のデータを削除することになりますので、上位の10％のデータと下位の10％のデータを除外します。

例えば次のような記述となります。

=TRIMMEAN(A1:A4, 0.2)

この例の場合、セルA1からセルA4までのセルの中で、上下とも10％のデータを除外して平均値を計算します。

除外する計算方法ですが、例えば全体の個数が15個だった場合に除外割合を0.15とすると、上下とも0.075の値が除外されますのでそれぞれ15×0.075＝1.125個のデータが除外されます。小数点以下は切り捨てで計算するため、結果として上下とも1個ずつのデータが除外されて全部で13個のデータで平均を取ることになります。

では実際に試して見ます。

1.元となるExcelを用意する

下記のようなExcelシートを用意します。

店舗別の売上平均を計算します。AVERAGE関数を使って全店舗の平均は既に計算していますが、臨時収入があったD店の売上が大きいため全体の平均が実態よりも高くなっているため、20％除外した平均を計算します。

2.結果を表示するセルを選択

平均点の結果を表示するセルを選択します。

結果を表示するセルは「C13」です。

3.関数の設定

挿入する関数を選択します。メニューの「挿入」から「関数」を選択します。

「関数の検索」で「trimmean」を入力し、「関数名」で「TRIMMEAN」が選択されていることを確認してから「OK」ボタンをクリックします。

「配列」に平均の対象となるセル範囲を入力します。

「割合」に除外対象となるパーセンテージを入力します。

最後に「OKボタンをクリックして下さい。

4.結果の表示

指定したセル範囲の中で、上限合計で30％(上下それぞれ15％ずつ)のデータを除外したセルのデータを平均して表示します。

平均点は5416となり、より実態に近い値となりました。

また、セルに直接次のように入力しても結構です。

=TRIMMEAN(C3:C10,0.3)

除外されるデータはセル単位となりますので、集計するセルの個数が少なかったり割合が低かったりして除外されるセルの個数が1未満となると小数点以下は切り捨てのためデータが結局除外されないことにもなりますので注意して下さい。

OfficePro