Excel-13: 種々の統計量と相関係数

種々の統計量

Excel-10: 平均・分散・標準偏差で平均と標準偏差について学んだ。これらは統計量と呼ばれる量の二例であるが、
本ページではその他の統計量について学び、それを Excel でどのように計算するかを習得する。

まず、excel_data01.xlsx というファイルをダウンロードし、各自のマイドキュメントに保存してほしい。
(マウス右クリックから「対象をファイルに保存」が確実。)

保存後、ファイルをダブルクリックして Excel を起動すると、以下のように
60歳以上の男性の「身長」、「体重」、「手の長さ」の３つのデータが記入されたシートが開く。

人数は100人程度分であり統計データとしては必ずしも多くないが、練習用としてはこの程度の人数で十分であろう。
(なお、このデータは現実の計測データを参考に、人工的に生成されたデータである)



このファイルをスクロールさせて下まで見てみると、以下のように、空欄の項目がある。
この空欄を各自記述して埋めてもらう。



いくつか例を示そう。例えば「データ数」の欄は、数値が書き込まれた欄 (空欄を除く) の数を数える
COUNT 関数が使える。

今、身長に対してデータ数を求めるには以下のように行う。
まず身長のデータがセル B4 から B103 まで書き込まれていることに注意して、
空欄に「=COUNT(B4:B103)」と記入して Enter キーを入力する。すなわち、以下の通り。



関数が正しく計算されれば、セル B104 にデータ数が現れるはずである。

この演習を解く上で利用できる Excel 関数のリストを以下に挙げる。
「分散」と「標準偏差」の欄に不偏分散／分散、標準偏差(不偏分散の平方根)／標準偏差(分散の平方根)の どちらを用いるかは自由とするが、
関数の名前の付け方から判断すると Excel では不偏分散を優先させているようである。

COUNT(…)データの中に数値がいくつあるかを計算する
SUM(…)データの和を計算する
AVERAGE(…)データの平均値を計算する
VAR(…)データの不偏分散を計算する
VARP(…)データの分散を計算する
STDEV(…)データの標準偏差(不偏分散の平方根)を計算する
STDEVP(…)データの標準偏差(分散の平方根)を計算する
MAX(…)データの最大値を求める
MIN(…)データの最小値を求める
MEDIAN(…)データの中央値を求める


気真面目にやろうとすれば、８×３＝２４のセルを全て入力しなければならず、面倒である。

そこで、少し楽をする方法を考えよう。とは言っても、まず第 B 列の８個のデータは真面目に一つ一つ入力する必要がある。
そこまで入力が終わったら、８個分のセルを隣の第 C 列にコピーしよう。(イメージ図は下図の通り)
(Excel のセルのコピーの仕方を覚えているだろうか？)



そうすると、Excel の相対参照という機能により、先程まで第 B 列 (上図青色のデータ) に対して計算されていた式が、
第 C 列 (上図緑色のデータ) のデータに対して再計算され、 C104～C111 のセルに表示されるはずである。

相関係数

二つの系列に相関があるかどうかは相関係数によって計算される。
相関係数は Excel の CORREL 関数を用いて計算される。

CORREL(系列１, 系列２)系列１と系列２の相関係数を求める


ただし、今までの関数と異なり、系列が２つあるので、データの指定法に若干注意が必要である。
下図のように「=CORREL(B4:B103, C4:C103)」によって２つの系列を指定する。 正しく記入すれば、相関係数が計算されて値が表示される。



なお、上の種々の統計量の場合と異なり、C 列と D 列をコピーで済まそうとすると (D 列で) 失敗する。
相対参照の考え方に従えば、CORREL 関数を E 列にコピーした際、この関数は D 列と E 列から相関係数を計算しようとしてしまうからである。

面倒でも、手で B 列と D 列を指定しよう。

ここで計算される相関係数は、 「身長の高さと体重の重さには相関があるかどうか」
「身長が高さと手の長さとは相関があるかどうか」を数値化したものである。

相関係数が１に近い程「身長が高い人の体重は重い傾向がある」、
相関係数が０ならば「身長の高さと体重は無関係 (無相関)である」、
相関係数が－１に近い程「身長が高い人の体重は軽い傾向がある」などということに対応する。

実際には－１と１の間の実数値が得られるはずである。得られた結果を考察してみよ。


←Excel-12: 絶対参照の応用～三次元グラフ／ Excel-14: ヒストグラムの作成→

Microsoft Office の基礎に戻る