Excel2007-10: 平均・分散・標準偏差

理系のためのExcelグラフ入門 表紙 金丸隆志「理系のためのExcelグラフ入門」好評発売中です

グラフの例

本ページの目的は、平均と標準偏差の使いどころを理解することであるが、
そのために、再び「振動実験」のグラフを描くことを例にとろう。

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

その後、ファイルをダブルクリックすると以下のようなデータが現れる。
振動実験では、入力振動数を増加させたときと減少させたときで二回データを とってもらった。
下のデータは、これを5チームぶん全て集めたものである。



どのようなグラフだったか思い出すため、2回分のデータだけグラフを描いてみよう。
「入力振動数」「1回目」「2回目」のデータをマウスで選択した後、 「挿入」タブ→「散布図」→「散布図(直線とマーカー)」である。



皆さんが実験で描く図には「マーカー(データポイント)がないもの」や「マーカー(データポイント)が曲線で結ばれているもの」が多い。
しかし、データポイントは皆さんが計測したデータそのものであるから重要である。
そのため、データポイントがあるグラフの方が良い。

また、曲線でデータポイントを結ぶのが好ましくない場合がある。
なぜかというと、そもそも実験でわかるのは、データポイントにおけるデータだけである。
データポイントの間でどのようなデータが得られるかは厳密に言えばわからない。
そこを曲線で結ぶと言うのは、データに先入観を与えることになり、好ましくない場合がある。

この考え方を突き詰めれば、データを直線で結ぶのも好ましくないという考え方もでき、
実際、データポイントだけを表示して、線で結ばないグラフもしばしば見られる。
そのあたりは適材適所で、適宜判断していこう。

ここではデータポイントを直線で結ぶことにする。

現れるグラフは以下のようになる。





10 回分のグラフを描いてみる

それでは、話を先に進めよう。実際の実験では2回分のデータが得られ、
そのグラフを描くのは上で見たように簡単だった。

しかし、実験を進める上で、2回と言わず10分回くらいの実験データが 得られることもあり得る。
そのようなときにどのようにグラフを描けば良いだろうか?

まずは、シンプルに10回分のグラフを重ね書きしてみよう。
全てのデータをマウスで選択し、上でやったのと同じようにグラフを挿入すればよい。



すると、以下のように無茶苦茶なグラフになってしまう。
これは、下図において青色のデータ (10 個) を x 軸にしたかったのに対し、
赤色のデータ (11 個)が x 軸として自動選択されて しまったためである。
(どちらが x 軸として選ばれるかはデータの個数による)



こういうときは慌てずにグラフを選択した上で、 「デザイン」タブから「行/列の切り替え」を選べば良い。



そうすれば、x 軸を正しく指定できたことになり、10 系列のグラフが描ける。 (グラフの設定はまだ省略している)



しかし、(ここからがこのページの本題であるが) この10本のグラフは データとして有用であると言えるだろうか?
少なくとも、10本のデータを一つ一つ見分けることは困難であることが わかるだろう。

このような場合、平均と標準偏差をうまく利用して図示化すると、
10本のデータの性質が良く分かるようなグラフを描くことができる。



平均と標準偏差を図示してみよう

ここから本ページの本題。

まず、10 回分のデータの平均を求めよう。平均は AVERAGE 関数で求められる。

今、下図の入力周波数 500 [rpm] の10回分のデータの平均を求めるには、 下図のように「=AVERAGE(C4:L4)」と入力すれば良い。



正しく入力できれば、以下のように平均値が計算される。
残りのセルに対しても平均を求めるには、ここで記入したセルをコピーして
そのまま下の全てのセルにコピーすれば良い。



やはり、Excel 特有の相対参照の考え方により、 全ての行に対する平均が再計算され、セルに表示される。



まず、この平均をグラフ化してみよう。

まず、入力振動数の列と平均の列を同時選択する。
同時選択するには、まず「入力振動数」の列を選択した後、
Ctrl キーを押しながら「平均」の列を選択すれば良い。

そして、グラフウィザードでグラフを描けば…、



以下のように平均のグラフが得られる。
しかし、せっかく10回分実験したのに、平均のグラフしか描かないのはもったいない。
標準偏差は、10回の実験のバラツキの程度を数値化したものである。それも図に取り込んでみよう。



まず、標準偏差をを計算しよう。STDEV 関数で求められる。
(詳しく言えば、これは不偏分散の平方根である。詳細はExcel2007-13: 種々の統計量と相関係数参照)
AVERAGE と同じように、全てのセルで計算 (コピー) しよう。



全ての標準偏差を計算し終えたら、グラフをクリックして選択し、
「レイアウト」タブから「誤差範囲」→「その他の誤差オプション」 を選択しよう。





すると、以下のように、「誤差範囲の書式設定」というウィンドウが現れる。
ここで「両方向」が選択されていることを確認し、 ユーザー設定の「値の指定」ボタンをクリックしよう。



すると、以下のように誤差範囲を指定するダイアログが現れる。
ここで先程計算した標準偏差を指定することになる。
「正の誤差の値」の記入欄がマウスで選択され、下図のように色が反転した状態で…、



標準偏差の領域をマウスでドラッグして選択しよう。
すると、「正の誤差の値」の部分に領域を表す式が格納される。



同様に、「負の誤差の値」の記入欄がマウスで選択され、
下図のように色が反転した状態で同じ「標準偏差」の領域をマウスでドラッグして選択すると、



やはり「負の誤差の値」の部分に領域を表す式が格納される。 ここでOKボタンをクリックすると標準偏差がグラフ化される。



触れ幅最大付近の 800[rpm]、900[rpm] では、データのばらつきが大きいことも 見てとれるだろう。



これを、レポートに耐え得るように細かく設定することで、 最終的に以下のようなグラフにすることができる。



グラフの設定法がわからない場合は、以下の復習用ページを参考にして欲しい。

←Excel2007-09: SUM 関数と AVERAGE 関数Excel2007-11: 絶対参照とは何か〜グラフの例をもとに→

Office 2007 の基礎に戻る