『Excelの統計関数(平均・偏差・標本・相関)』を叩き込もう

『Excelの統計関数(平均・偏差・標本・相関)』を叩き込もう

まもなく新生活が始まろうとしている方々も大勢いらっしゃるのではないでしょうか?

期待に胸を膨らませている方には「どうぞお楽しみに」としか言えませんが、今現在不安にかられている方も少なからずいらっしゃるかと思います。

不安になる要因はいくらでもあるのでしょうが、私がよく聞くのは「履歴書を誇張して書いちゃった、即戦力と見なされたらどうしよう……」といったものです。

間もなくエイプリルフールも近いですが、さすがに企業も採用活動にお金がかけているので「冗談でした…」では済まされません。

一般職であれば、誇張してしまいがちな部分としてPCスキル、特にExcelの習熟度についてではないでしょうか。

入社早々に「コイツ使えねぇ~」なんて思われないように、Excel学習の鬼門とも言える関数について、最低限覚えておきたいものをご紹介します。

一言に関数と言っても関数はたくさんあるので、今回は『統計関数』に絞らせていただきます。

と、偉そうに言ってはみたものの、実は私もまったく使いこなせていません(笑)

ぜひ、みなさんも私と一緒にExcelを使いこなせるようになりましょう!

平均値を自動計算させよう

これは基本中の基本、すべての合計を個数で割った値が「平均」ということになります。

<例>

5人の生徒のテストの点数は82点、15点、78点、70点、85点という結果。

この場合の平均点は「(82+15+78+70+85)÷5=66」。よって平均点は66点。

これをいちいち計算せずにExcelに自動計算させるには、以下の文字列を関数入力欄に入れればあっという間です。

=AVERAGE(開始:終了)

とある6人の平均身長を求めようと思ったら、以下のようなイメージになりますね。

平均身長は174.33…。この場合は4人が平均以上、2人が平均以下であることが分かります。
※名前はあくまでも架空の人物です

中央値を自動計算させよう

平均値の場合、1つ極端に上振れ、または下振れした数値が存在した場合、その数値に引きずられる形の平均が算出されてしまいます。そんな時に有効なのは「中央値」。

「中央値」はデータを大きい順、または小さい順に並べたとき、ちょうど中央にくる値になります。

<例>

5人の生徒のテストの点数は82点、15点、78点、70点、85点という結果。

これを小さい順に並べると15、70、78、82、85となり、中央値は78点となる。

これを先ほど同様にExcelに算出させるには以下のように入力すれば良いのです。

=MEDIAN(開始:終了)

とある6人の身長の中央値を求めようと思ったら、以下のようなイメージになりますね。

平均値では約174.33となっていましたが、今回の中央値では176。

一人160センチ台の人が混ざっていたため「平均以上:平均以下」が「4:2」の割合となっていましたが、中央値は純粋な中間点であることを意味しているんですね。

※名前はやはり架空の人物です

ちなみにExcelとは関連しないかもしれませんが、中央値に関する余談を2つほど併せてご紹介します。

<余談1>
母数が偶数個ある場合、中間の二つの値を足して2で割った値が中央値になる。

例えば15、70、78、80、82、85とあった場合、「(78 + 80)÷2」=79」で「79」が中央値になる。

<余談2>
上の平均の例で70点をとった生徒は「やったー!平均点超えたー!!」と喜ぶかもしれないが、実際には5人中では下から2番目。あんまり喜んでもいられませんね……。

標本標準偏差を自動計算させよう

まずは「標本標準偏差?」となるかもしれませんね。まずは偏差について説明します。

偏差とはデータのバラツキ度合いを表すもので、偏差自体に「標本標準偏差」と「母集団標準偏差」の二種類あります。データ全体を母集団と言い、ある程度サンプルデータとして抜き取ったものを標本と言います。

「標本標準偏差」は母集団の数が分からない場合、そして母集団が不明瞭であるが故に平均値が分からないといったケースで有効になります。標本データの平均値や「標本標準偏差」の値をもって、母集団の分布を推測するのに役立つということなんですね。

<例>
20歳男性の身長のバラツキを調べたい。

しかし、世界中の20歳男性の身長を測定することはできないため、無作為に30人の20歳男性を集め、標本データとした。

実際の計算式は馴染みのないものかもしれません。

「平均と各値の差の2乗の合計を個数 – 1で割った値の平方根」ということになるわけですが、こんなのExcelに任せたくもなりますよね……。

はい、こんなのもExcelにかかれば、たった一行の関数で済んでしまうのです。

=STDEV(開始:終了)

400人前後いる所属人数のうち、無作為に選出した6人の身長データから「標本標準偏差」を算出した場合は、以下のような感じになります。

標本標準偏差は「約5.82」。仮に6人とも同じ身長だった場合、ゼロとなります。この5.82はバラツキ度合いを示すものなんですね。つまり偏差とはバラツキが大きいか小さいかを表しているのです。

※名前は架空の人物です。くれぐれも誤解のないように

母集団標準偏差を自動計算させよう

先ほどの標本データのバラツキとはうって変わって、今度は母集団データのバラツキを示す値になります。

それでも計算式は「平均と各値の差の2乗の合計を個数で割った値の平方根」と、いまいちピンと来ないですよね……。

<例>

5人の生徒のテストの点数は82点、15点、78点、70点、85点という結果。平均点は前記のとおり66点。しかし、クラスの総数が不明瞭だった場合は標本データとなるため標準偏差は約29.06となりバラツキが大きい。

バラツキが大きいということは一部の点数が平均を下げているだけで、平均点を取るのは難しくなかったと言えます。

そしてたった5人しかいない別クラスの点数が55点、70点、68点、66点、75点という結果だった場合、平均は66.8点と上記の不特定多数クラスと近い数字にはなっているが、標準偏差(母集団)は約6.62となる。不特定多数クラスよりもバラツキが小さく、つまり高得点の生徒が少ないものの低得点の人も少ないということが分かります。

いずれにしても複雑ですよね。しかしこれをExcelで求めれば…

=STDEVP(開始,終了)

これをたった6人のグループ内でのバラツキを調べるとすれば、こんな感じになります。

先ほどの400人近い所属人数の中での標本データ6人の標準偏差は約5.82でしたが、母集団として標準偏差を算出すると「約5.31」という結果になりました。つまり、若干ではあるもののバラツキが少ない可能性があるということなんですね。

※クドいようですが、名前は架空の人物です。

相関係数を自動計算させよう

「相関係数」とはもはや統計学的な数式。データyとデータxの相関関係を示す値が「相関係数」になります。“風が吹けば桶屋が儲かる” のように “風が吹く” ことと “桶屋が儲かる” ことの因果関係を数値がしようというものなのです。

ちなみに「相関係数」は-1から+1の間で計測され、中間のゼロの場合は2つのデータ群に関連性がないとされ、無相関と呼ばれています。なお+1に近いほど正の相関関係(正相関)であり、-1に近いほど負の相関関係(逆相関)にあります。一般的には0.7以上と算出された場合、2つのデータ群に強い結びつきがあるとされているようですね。

<例>

ある夏の一定期間の気温と飲料水の販売数の相関関係を調べたい。

気温:38℃、36℃、32℃、37℃、42℃
販売:120本、111本、96本、102本、156本

相関係数は0.9となり、非常に強い結びつきにあると言える。

2つのデータ群を用いるような統計関数においても、Excelに任せれば一瞬です。

=CORREL(データy開始:データy終了,データx開始:データx終了)

とある6人グループの健康管理を行います。「高身長ほど高体重」であれば問題ないですが、「高体重なのに低体重」や「低身長なのに高体重」だと危険です。

そこでグループ内の身長と体重の相関関係を調べてみると……

算出された相関係数は「約0.68」。結びつきが強いとされる0.7に限りなく近い数値ですね。つまりこのグループは「高身長ほど高体重」「低身長ほど低体重」の傾向が強いグループであると言えます。

ただ、加齢による体重増減が考えられ、相関係数の減少もあり得ますので、コントロールを怠ってはいけないですね。今後の活躍に期待です(何の?)。

新しい環境でも恥をかかないようにと始めた「Excelの相関係数」レクチャーでしたが、いくらExcelの習熟度が高いとされる人でもここまで使いこなせる人はいないでしょう。

もう不安が解消されたのではないでしょうか?

(※新環境早々に複雑な統計関数を使うシチュエーションもないでしょうが……)

それでも「数学が苦手だなぁ~」という方であっても、簡単に計算してくれるExcelはやはり便利。もちろん、『統計関数』以外にもExcelにはたくさんの関数(全476種!)があり、環境次第ではピッタリとマッチする関数もあるハズです。

数学は苦手であっても、Excelにおける関数を必要な分だけ覚えておければ、何とかなりそうですね!

コメント