定時で上がろう! Excel関数の底力 第58回 「有意義な平均値」の求め方‐関数TRIMMEAN

平均値は、データの傾向を探る重要な指標の一つといえる。ただし、データの中に「突出した数値」が含まれていると、平均値が“意図していた数値"になっていない可能性もある。ということで、今回は「関数TRIMMEAN」の使い方、ならびに「どのデータをもとに平均値を算出すべきか？」について検討していこう。
上位、下位のデータを除外した平均値

TRIMMEANは「上下XX%を除外した平均値」を算出できる関数で、データの中に「突出した数値」が含まれている場合に活用できる関数となる。ただし、本当に知りたい“有意義な平均値"を求めるには、その計算方法を十分に把握しておく必要がある。具体的な例を使って紹介していこう。

以下の図は、あるWebサイトにおける11月前半のアクセス数をまとめたものだ。関数AVERAGEにより「アクセス数(PV)の平均値」も算出されている。

この結果に従うと「1日あたりの平均PV数は約1.68万である」ということになる。でも、果たして本当にそういえるだろうか？　データをよく見ると、11月6日だけ「突出して大きな数値」が記録されていることに気付くと思う。

実は、この日、偶然にもSNSでバズった記事があり、通常よりも格段に多いアクセスが記録されていた。このような状況になることは滅多にないため、11月6日の76,944というPV数は実状にそぐわない、イレギュラーな数値と考えるのが順当だ。

このようにデータの中に「突出した数値」が含まれている場合は、その数値を除外して平均値を求めと、より実状に近い平均値を得ることができる。このような場合に活用できる関数がTRIMMEANだ。

◆関数TRIMMEANの書式
=TRIMMEAN(セル範囲,除外割合)

関数TRIMMEANを使って「平均値」を求めるときは、第1引数に「数値データが入力されているセル範囲」、第2引数に「除外する割合」を0～1未満の数値で指定する。

たとえば、「上下20%のデータを除いた平均値」を求めるときは、以下の図のように関数TRIMMEANを入力すればよい。

すると、「上から10%、下から10%のデータを除外した平均値」が算出される。今回の例では、約1.29万という結果になった。

この数値は、通常の方法で求めた平均値(約1.68万)より小さい値になっている。全データのPV数を個別に見ていくと、関数TRIMMEANで算出した平均値のほうが「実状に近い数値」と考えられるのではないだろうか？

参考までに、関数TRIMMEANで平均値を算出する際に除外されたデータを示しておこう。今回の例は全部で15個のデータがあり、そのうち上下20%のデータが除外されるので、15個×20%＝3個のデータが除外されることになる。これを上下に振り分けると、「上から1.5個、下から1.5個のデータを除外」となる。とはいえ、1.5個では切りが悪いので、これを整数に切り捨てて「上下とも1個のデータを除外する」という仕組みになる。

つまり、76,944(最大値)と7,668(最小値)を除外した形で平均値が算出されることになる(上図で色を付けたデータが除外される)。このように計算することで「突出した数値」を除外した平均値を求めることが可能となる。
数値データのセル範囲が離れている場合

続いては、先ほどの例に11月後半のデータを追加した例を紹介していこう。11月後半のデータは、以下の図のように入力されている。このように「離れたセル範囲」を対象にして関数AVERAGEで平均値を求めるときは、それぞれのセル範囲をカンマで区切って記述すればよい。

同様の手順で「上下20%を除いた平均値」を関数TRIMMEANで求めてみよう。以下の図のように関数を入力する。

その後、「Enter」キーを押して関数の入力を確定しようとすると、「この関数に対して、多すぎる引数が入力されています。」という警告画面が表示された。

それもそのはず。関数TRIMMEANに指定できる引数は2つで、第1引数に「数値データのセル範囲」、第2引数に「除外する割合」を指定する仕様になっているからだ。先ほど示した例の場合、セル範囲は「C5:C19」、除外する割合は「F5:F19」、さらに「0.2」という余計な引数がある、と解釈されてしまう。

では、どうすればよいのか？　このような場合は、それぞれのセル範囲をカッコで囲み、以下の図のように関数TRIMMEANを記述すると、正しく計算を実行できるようになる。

「Enter」キーを押して関数を実行すると、「上下20%を除いた平均値」は約1.21万という結果になった。

その平均値は本当に適切か？

念のため、先ほどの例についても除外されたデータを示しておこう。この例には全部で30個のデータがあるので、30個×20%＝6個のデータが除外されることになる。つまり、上から3個、下から3個のデータが除外される訳だ。

上から3個のデータは「11月6日～11月8日のデータ」になる。11月6日は偶然にもSNSでバズった日、以降の2日間も「その余波により通常よりアクセスが多かった日」と考えられる。よって、これらのデータを除外して平均値を計算するのは“意味のある計算方法"といえる。

一方、下から3個のデータとして除外された11月5日、11月18日、11月23日のデータはどうであろうか？　これらのデータはSNSでバズったことに関係なく、「単純にアクセス数が少なかった日」と捉えられるべきだ。にもかかわらず、これらのデータを除外して平均値を計算するのは“意味のある計算方法"とはいえない。

このように考えると、「11月6日～11月8日のデータ」だけを除外して平均値を計算するのが“最も実状に近い平均値"といえるかもしれない。ただし、このような計算を関数TRIMMEANで実行することはできない。関数TRIMMEANは、必ず上下XX%のデータを除外して平均値を計算する仕組みになっている。

このような場合は、関数AVERAGEIFで「条件付きの平均値」を求めるのも効果的な手法といえる。「11月6日～11月8日のデータ」だけを除外したい場合は、条件に「18,000未満」を指定して平均値を算出すればよい。そこで、以下の図のように関数AVERAGEIFを入力してみた。

その後、「Enter」キーを押して関数を実行すると「#VALUE!」のエラーが表示された。

関数TRIMMEANの場合はカッコで囲むことで「離れたセル範囲」を指定できたが、関数AVERAGEIFの場合は上手くいかない。これは関数の仕様上、どうしようもない問題といえる。関数AVERAGEIFは「離れたセル範囲」に対応していないため、上記のように関数を記述するとエラーが発生してしまう。

この問題を解決するには、

(A)1つのセル範囲で指定できるようにデータ表を作り直す
(B)セル範囲を拡大して指定する
(C)SUMIFとCOUNTIFを組み合わせて平均値(合計／データの個数)を計算する

のいずれかで対応しなければならない。ここでは、最も手軽な(B)の方法を採用してみよう。以下の図のように、すべてのデータ(PV数)を含むようにセル範囲を指定する。

この場合、セル範囲内にある「空白セル」や「文字列データ」を無視して「条件付きの平均値」が算出される。その結果は、11,656という値になった。

このように余計なセルを含めてセル範囲を指定することも可能だ。ただし、「日付データ」や「時刻データ」も数値として扱われることに注意しておく必要がある。

今回の例には「2023年の日付データ」が入力されている。これらの日付データをシリアル値に変換すると40,000以上の数値になる。この数値は「18,000未満」の条件に合致していないため、日付データは平均値の計算対象にならない。よって、「18,000未満のPV数」だけを対象に平均値を算出できる、というのが今回のテクニックの概要になる。

つまり、今回の例は「18,000未満」を条件にすることで「たまたま日付データを除外できた」というだけで、必ずしもこの方法が上手くいくとは限らない。セル範囲に日時データが含まれるときは、こういった点にも十分に注意しておく必要がある。

そのほかの方法として、セル範囲の選択により「指定したデータだけの平均値」を調べる方法もある。たとえば、「Ctrl」キーを押しながらマウスをドラッグして以下の図のようにセル範囲を選択していくと、「11月6日～11月8日のデータ」だけを除外した平均値を調べることができる。

データ数が少ない場合は、これが最も手軽な平均値の算出方法といえるだろう。

参考までに、ウィンドウ下部に表示される「平均」と「合計」の数値についても補足しておこう。これらの数値は、選択したセル範囲と同じ「表示形式」で表示される仕組みになっている。今回の例では、F5セルに「数値」(小数点以下0桁)の表示形式が指定されているので、ウィンドウ下部の「平均」と「合計」も小数点以下0桁で表示されている(※1)。

(※1)選択したセル範囲の中で「白く表示されているセル」(F5セル)の表示形式が適用される。

試しに、すべてのセルの表示形式を「標準」に変更してみると、「平均」の値が小数点以下を含む数値になるのを確認できる。

つまり、本当の平均値は11,656.03704…であり、それを四捨五入すると11,656になる、という訳だ。ウィンドウ下部で「平均」と「合計」を確認するときは、その数値が「四捨五入されたものでないか？」にも注意しておく必要があるだろう。不用意なミスを回避するためにも、念のため覚えておくとよい。