第３回：表計算の利用(3)
| ■まずは準備 | ■最大値・最小値の算出 | ■ 「統計ツール」の利用 |
| ■計算式を数値に変換する | ■標準偏差の算出 | ■ ｉｆ関数の利用 |
| ■並べ替え | ■偏差をとる（絶対参照の使い方の復習） | |
| ■関数とは何か | ■散布図を描く | |
| ■平均の算出 | ■相関係数の算出 |
これまでに表計算ソフトによって四則計算や表，グラフへの加工の仕方を学んだが，今回は「関数」を用いて複雑な計算，集計の仕方を学びます．
1.まずは準備
「人口と商店数」のデータを分析してみます．今回の実習は「文科系のやさしいデータ分析」,冨樫光隆・上田 泰,1994,有斐閣,の内容を用いています．この本はDOS版のロータス123という表計算ソフトとSASという統計パッケージを用いて解説を行っています．データのダウンロードはこちらをクリックして下さい．
サンプルをダウンロードしたら，Excelで開きます．以下の図のようなデータがすでに入力されています．
人口と商店数にはどのような関係があるのか調べましょう．人口1000人当たりの商店数を算出してみよう．例えば，Ｅ６のセルは，「=Ｄ６／Ｃ６＊１０００」となります．これをコピーしてすべての市について算出しましょう．
2.計算式を数値に変換する
今回のような小さなデータであれば，問題ありませんがないが，大量のデータ（例えば何十年にも及ぶ毎日の株価のように）を対象に計算する場合，計算式をそのままにしておくと，処理が遅くなるケースが発生します．これは新しい入力がある度にコンピュータが再計算を行なっているためです．
これを避けるために，もう変更する必要のない，計算式は計算結果だけを数値で表現したほうがパソコンへの負担が少なくなります．ここではこの方法を解説します．
- 計算式を数値に変換したい範囲を選択します(今回の例では人口当たりの商店数)．
- コピーします．今回は貼り付ける位置は同じ位置にします．
- メニューの「編集」から「形式を選択して張り付け」を選びます．
- 現れたパネルから「数値」を選びます．
3.並べ替え
得られた人口当たりの商店数のランキングを付けてみます．
- 名称を含む全ての範囲（Ａ５からＥ３２まで）を選択します．
- メニューの「データ」から「並び替え」を選びます．
- 現れたパネルの下部にある「範囲の先頭行」がタイトル行になっていることを確認して下さい．
- 上部の「最優先されるキー」を商店数/人口にします．
- 並び替えの順序を「降順」にし，並べ替えを実行します．
4.関数とは何か
下の例でセルＡ５にＡ１からＡ４の合計を計算することを考えます．これまでの講義でのやり方なら，
「＝Ａ１＋Ａ２＋Ａ３＋Ａ４)」でした．
| A | B | |
| 1 | 10 | 15 |
| 2 | 12 | 10 |
| 3 | 15 | 12 |
| 4 | 12 | 15 |
| 5 |
例のように計算に含めるセルが少ないときには良いですが，大量のデータを相手にするといちいち入力が大変です．そんなとき，関数を使って表記すると，非常に簡単になります．この例では，次のように書きます．
「＝ＳＵＭ（Ａ１：Ａ４）」
かっこの中は引数(ひきすう)といい，ここではＡ１からＡ４までという意味を持っています．その他に代表的な関数として，
- AVERAGE：引数の平均値
- COUNT：引数に含まれる数値の個数
- MAX：引数のなかの最大値
- MIN：引数のなかの最小値
- STDEV：引数を母集団の標本としたときの標準偏差
- VAR：引数を母集団の標本としたときの分散
- SQRT：引数の平方根
などがあります．
ボタンを押すと関数のリストが現れます．参考にすると良いでしょう．
5.平均の算出
この後の分析に備えて，人口，商店数，人口当たりの商店数の平均を計算します．せっかくなので，２通りの計算をしてみます．
セルＣ３５では平均の計算式通りに計算してみます．
＝ＳＵＭ（Ｃ６：Ｃ３２）／２７
もしくは
＝ＳＵＭ（Ｃ６：Ｃ３２）／ＣＯＵＮＴ（Ｃ６：Ｃ３２）
になるはずですね．
セルＣ３７は平均の関数を用いてみます．
＝ＡＶＥＲＡＧＥ（Ｃ６：Ｃ３２）
両者の結果は同じになりましたか？商店数，人口当たり商店数もコピーをして計算しましょう．人口当たりの商店数の平均を見ると，東村山市が平均とほぼ等しい位置にあるようです．
6.最大値・最小値の算出
セルＣ３８，Ｃ３９にそれぞれ最大値，最小値を算出します．最大値はＭＡＸ，最小値はＭＩＮという関数名でした．
商店数，人口当たり商店数もコピーしましょう．
7.標準偏差の算出
平均と並んで良く用いる統計量です．データのちらばりが平均からどのくらい離れているかを示す指標です．受験の際に良く耳にした「偏差値」もこの考えを利用しています．
セルＣ４１に人口の標準偏差を算出してみます．関数名はＳＴＤＥＶでした．
やはりこれも商店数，人口当たり商店数の分もコピーしましょう．
8.偏差をとる（絶対参照の使い方の復習）
次に，人口と商店数の関係を分析しましょう．ここで，各市の人口，商店数がサンプル全体の平均からどれだけ離れているかを考慮にいれた形で分析を進めます．そこで，各市について「人口−人口の平均」，「商店数−商店数の平均」を計算することにします．この値を「偏差」と呼んでいます．
例えば，セルＦ６は＝Ｃ６−＄Ｃ＄３７である．
「＄」意味，覚えてますよね．絶対参照です．忘れてしまったら，表計算(1)を参照して下さい．
9.散布図を描く
人口と商店数の関係を具体的に数値で表現する前に，両者の関係をグラフに表し，イメージをつかんでみます．データの視覚化は分析の基本です．ここでは散布図を描き，両者の関係をつかんでみます．
「人口の偏差」を横軸に，「商店数偏差」を縦軸にします．グラフの描き方は前回解説したので，ここでは省略します．
人口の偏差か商店数の偏差で並べ替えし直せば，散布図の一つ一つがどの都市に当たるかが検討つくと思います．グラフから人口と商店数の間には一定の関係があることが想像できます．
10.相関係数の算出
先の散布図で，人口と商店数の間に一定の関係が想像できましたが，これを具体的な数値で表現してみます．2つのデータ（ここでは人口と商店数）の間の関係の強さを測る統計量を相関係数と呼んでいます．
Excelでは，「CORREL」という関数を用います．セルＣ４３に相関係数を計算してみましょう．
＝ＣＯＲＲＥＬ（Ｃ６：Ｃ３２，Ｄ６：Ｄ３２）
相関係数は0.942とでました．これをどのように評価すればよいのでしょう．
一つの目安として，相関係数の大きさ(絶対値)と相関の程度の表現の対応関係は以下のように考えれば良いといわれています．
| 1.0≧|R|≧0.7 | ：高い相関がある |
| 0.7≧|R|≧0.5 | ：かなり高い相関がある |
| 0.5≧|R|≧0.4 | ：中程度の相関がある |
| 0.4≧|R|≧0.3 | ：ある程度の相関がある |
| 0.3≧|R|≧0.2 | ：弱い相関がある |
| 0.2≧|R|≧0.0 | ：ほとんど相関がない |
| 出典：「社会調査の基礎」放送大学テキスト | |
上記の表を元にすれば，都市の人口と商店数には「高い相関がある」と言えます．
ここからは時間があれば触れる内容です．
11.「統計ツール」の利用
Excelには「統計ツール」と呼ばれるアドイン（オプション装備みたいなものか？）があり，これがさらに高度な分析の処理を可能にしています．ここでは，「回帰分析」を行なってみます．
11-1.回帰分析とは何か
回帰分析とは，乱暴にいってしまえば，複数の変数間の関係を一次方程式（Y=aX+bってやつ）で表現する分析方法です．
詳しい内容については，統計学のテキストに譲りますが，用途としては，制御や予測に用いることができます．例えば，
- 売上高と宣伝費の関係：目標とする売上高に対して宣伝費を決定する（制御）
- 人口と商店数の関係：あきる野市の人口から市の商店数を予測する（予測）
11-2.回帰分析を実行する
- メニューの「ツール」から「分析ツール」を選択します．
- 現れたパネルから，「回帰分析」を選択します．
- 回帰分析のパネルが現れるので，「Ｘ範囲」，「Ｙ範囲」，「出力範囲」を入力します．
- 正しく入力（マウスで指定しても良い）できたら，実行です．
12.ｉｆ関数の利用
これまでとは少し感じの異なる関数ですが，紹介します．
いわゆる条件分岐を行なうときに用いる関数です．サンプルの人口について，10万人より多いか少ないかで区別をしてみます．
例えばセルＨ６にはこんな風に入力してみましょう．
＝ｉｆ（Ｃ６＞１０００００，１，０）
Ｈ７以下もコピーしてみます．人口10万人を越える市には「１」，10万人以下には「０」がつきました．
ｉｆ関数では条件式「Ｃ６＞１０００００」が真なら「，」のすぐあとを表示（あるいは実行），偽ならさらにその後を表示（あるいは実行）する，という記述のしかたをします．
さらに複雑にすることもできます．今度はセルＩ６に，
＝ｉｆ（Ｃ６＞１５００００，２，ｉｆ（Ｃ６＞＝１０００００，１，０））
Ｉ７以下もコピーしましょう．今度は人口15万人を越える市には「２」，15万人以下10万以上には「１」，10万人未満には「０」がつきました．このように関数は組み合わせて使うこともできます．
Copyright(C) 1997-2001 by ABE Keiji
All rights reserved.