Excel。データはあるので、いつもの資料にプラスしたい値アレコレ
＜TRIMMEAN・MEDIAN・MODE.SNGL&MODE.MULT関数＞
データはあるけど、マンネリの資料に何かプラスしたいという話を耳にしたので、次の表を使って、こんな値を追加したらどうかなぁ～という紹介をしていきます。
ほぼ毎日の参加人数を管理しています。
最低限、合計・平均・最大値・最小値・データの件数は、算出したいところですね。
この5種類の計算は、オートSUMボタンにすべて準備されているので、簡単に算出できます。
E1の合計は、=SUM(B2:B128)
SUM関数で算出します。
E2の平均は、=AVERAGE(B2:B128)
AVERAGE関数ですね。
E3の最大値は、=MAX(B2:B128)
MAX関数で、最大値を算出できますね。
MIN関数で最小値でした。
E4の最小値は、=MIN(B2:B128)
件数ですが、検索対象が数値なので、数値の個数。
すなわちCOUNT関数で算出できました。
=COUNT(B2:B128)
これだけでは、データあるのにもったいないですね。
プラスする値ですが、平均に着目したいですね。
AVERAGE関数は、算術平均、または相加平均と呼ばれています。
平均は算出しやすいのですが、対象の数値に幅があると、適切な平均値でない場合があります。
いわゆる、「異常値」が含まれているとおかしな数値になってしまうわけです。
そこで、異常値を除外して平均を算出した値も表示したいところです。
この時に使う関数が、TRIMMEAN関数です。
H1につくった数式は、
=TRIMMEAN(B2:B128,0.1)
TRIMMEAN関数の引数は、配列=範囲選択と割合で構成されていますが、割合はほぼ0.1だと思います。
異常値の上位5%下位5%を除くといいといわれています。
この5%+5%で10%なので、割合は、0.1として算出します。
結果は、48.2087。
AVERAGE関数で算出した結果とは変わりましたね。
次に追加したいのは、中央値です。
データを昇順にしたときに、ちょうど中央にある値が中央値です。
Excelでは、MEDIAN関数で簡単に算出できます。
H2の数式は、
=MEDIAN(B2:B128)
結果が31。最大値が209なので、全体的に、小さい数値が多いイメージがしますね。
もう少し追加したいですね。
追加するのは、最頻値と呼ばれている数値です。
データの中で一番多く登場する数値のことですね。
数値がどのあたりに、「群れているか？」を確認することができます。
Excelでは、MODE.SNGL関数で算出することができるのですが、この関数、ちょっと問題があって、一位が複数ある場合、最初に見つけた数値だけを表示しちゃう。
複数ある場合、ほかの一位がわからないので、まず、最頻値の件数を算出するといいですね。
H4の数式は、
=COUNT(MODE.MULT(B2:B128))
MODE.MULT関数は、複数の最頻値を算出する関数です。
その数を知りたいので、COUNT関数とネストして算出します。
今回は、1種類しかないので、最頻値はMODE.SNGL関数をつかって算出したのが、H5で、結果が8。
オートSUMボタンの関数以外でも、少しプラスするだけで、データを深くみることができるようになりますので、機会がありましたら追加してみませんか？
