314 図 10.1 分析ツールの起動図 10.2 データ分析ウィンドウ [ データ ] タブに [ 分析 ] がないときは以下の手順で表示させる 1. Office ボタンをクリックし Excel のオプションをクリックする ( 図 10.3) 図 10.3 Excel のオプション

Size: px

Start display at page:

Download "314 図 10.1 分析ツールの起動図 10.2 データ分析ウィンドウ [ データ ] タブに [ 分析 ] がないときは以下の手順で表示させる 1. Office ボタンをクリックし Excel のオプションをクリックする ( 図 10.3) 図 10.3 Excel のオプション"

ひでかさだい
7 years ago
Views:

1 313 第 10 章 Excel を用いた統計処理 10.1 Excel の統計処理レポートや卒業研究などでは大量のデータを処理分析し報告しなければならない場面が数多く登場するこのような場合手計算では多くの時間を要するため現在では計算機を用いて一括処理することが一般的であるこれにより時間短縮だけでなく手軽に詳細な分析を行うことができる Excel ではこのような大量のデータに対する分析を容易に行えるよう分析ツールと呼ばれる機能を追加することができる分析ツールとは統計処理を中心とするデータ分析を一括に行うことができる機能である本章ではこの分析ツールの使い方を例題を用いて解説する多くの解析機能があるため本章では部分的にしか解説できない残りの機能についてはヘルプを参照されたいこの章では Excel の分析ツールのうち以下のデータ分析機能を用いる 1. 相関分析ツール相関係数を求める 2. 基本統計量分析ツール対象となるデータの主要な傾向と変動を求める 3. ヒストグラム分析ツール度数分布累積頻度を求める 4. 移動平均分析ツール不規則変動を取り除いてなめらかなデータ列を作成する 5. 回帰分析ツール線形回帰分析を行う長期的な傾向を分析するため過去の観測値に基づき将来の値を予測するまた Excel はグラフ形式の変更に際しデータを再度計算する必要がない本章ではグラフ形式の変更などの表現操作についても解説していくグラフの表現操作については以下の機能について手順を説明する 1. 誤差入りグラフの作成方法 2. 近似曲線とその数式の求め方 3. 片 ( 半 ) 対数グラフの作成方法分析ツールのセットアップ分析ツールを起動するにはリボンの [ データ ] タブをクリックし [ 分析 ] 内の [ データ分析 ] をクリックする ( 図 10.1) すると図 10.2 の [ データ分析 ] ウィンドウが開く

2 314 図 10.1 分析ツールの起動図 10.2 データ分析ウィンドウ [ データ ] タブに [ 分析 ] がないときは以下の手順で表示させる 1. Office ボタンをクリックし Excel のオプションをクリックする ( 図 10.3) 図 10.3 Excel のオプション

3 アドインを選択し管理タブを Excel アドインにし設定をクリックする ( 図 10.4) 図 10.4 アドインの設定 3. アドインウィンドウが表示されるので分析ツールにチェックを入れて OK をクリックする ( 図 10.5) 図 10.5 分析ツールの追加

4 時系列データの基本統計量移動平均回帰分析例題 : オゾン量の分析図 10.6 オゾン量測定値図 10.6 は南極昭和基地札幌つくばの各地点上空における年度ごとの 10 月のオゾン量計測値を示しているこのデータについて 1 各地点における 1974 年から 1996 年までの平均分散標準偏差を求めよ 2 昭和基地におけるデータについて移動平均を求め横軸に西暦縦軸にオゾン量をプロットした滑らかなグラフを作成せよ 3 昭和基地のデータについて回帰直線を求めその X 軸交点について議論せよただしこの例題の Excel ファイルは問題用と解答例それぞれがに用意してあるので自由にダウンロードして用いてよい ( 各地点データは 1999 年度版理科年表より抜粋 ) 平均分散標準偏差基本統計量データの平均分散標準偏差は分析ツールの基本統計量分析ツールを用いると容易に求めることができる課題 1はこのツールにより得られた結果から必要な項目を抜き出せばよいそこで分析ツールを起動し基本統計量を求める手順を以下に示す

5 [ データ分析 ] ウィンドウから [ 基本統計量 ] を選択し [OK] ボタンをクリックする ( 図 10.7) そうすると図 10.8 に示す [ 基本統計量 ] ウィンドウが開く図 10.7 基本統計量分析ツールの起動図 10.8 基本統計量ウィンドウ 2. [ 入力範囲 ] には各地点の名前の行を含む 3 地点のすべてのデータ範囲を入力する ( 図 10.9(1)) 先頭行が項目軸ラベルであることを示すために[ 先頭行をラベルとして使用 ] のチェックボックスをオンにする ( 図 10.9(2)) また結果の出力先として[ 新規ワークシート ] を選択し基本統計量と入力する ( 図 10.9(3)) 出力オプションとして基本統計量の表を出力する [ 統計情報 ] のチェックボックスをオンにする ( 図 10.9(4)) ここで例として 1 番目に大きい値小さい値を求めてみる ( 図 10.9(5)) (2) (1) (3) (4) (5) 図 10.9 基本統計量設定入力 3. すべての項目が設定できたら [OK] ボタンをクリックするそうすると図に示す基本統計量の表が基本統計量ワークシートに作成されるここから平均

6 318 分散標準偏差を抜き出せば課題 1の解答となるここで手順 2 で指定した [k 番目に大きい値小さい値 ] の結果として最大値 (1) と最小値(1) が出力されていることがわかるこのように k の値を変えることにより任意の順位の値を得ることができる図三地点の基本統計量滑らかなグラフの作成 : 移動平均実験や実地試験により得られた時系列データを横軸に時間をとったグラフにしたときいびつが目立つことがよくあるこのようなグラフを平滑化し滑らかな変化を見せたい場合には実験データより移動平均を求めグラフにプロットする課題 2では年ごとのデータの移動平均を求めることにより昭和基地のデータのグラフを平滑化すればよいここで移動平均の代表例として 3 項移動平均と 5 項移動平均についてそれぞれのグラフを求める手順を以下に示す 1. [ データ分析 ] ウィンドウから [ 移動平均 ] を選択し [OK] ボタンをクリックする ( 図 10.11) そうすると図に示す [ 移動平均 ] ウィンドウが開く図移動平均分析ツールの起動図移動平均ウィンドウ 2. [ 入力範囲 ] には昭和基地の名前の行を含むデータ範囲を入力する ( 図 10.13(1)) 先頭行が項目軸ラベルであることを示すために [ 先頭行をラベルとして使用 ] のチェックボックスをオンにする ( 図 10.13(2)) 入力データに関して 3 項移動平均を得たい

7 319 ので [ 区間 ] に 3 と入力する( 図 (3)) また結果の出力先として[ 出力先 ] に $E$5 と入力し観測データのあるワークシート内の E5 のセルを基準にして作成するように指定している ( 図 (4)) ここではデータのみを得たいのでその他のオプションは指定していない (1) (2) (3) (4) 図移動平均設定入力 3. すべての項目が設定できたら [OK] ボタンをクリックするそうすると図に示す 3 項移動平均の表が E5 から E27 に作成されるただし先頭行のラベル ( 昭和基地 3 項平均昭和基地 5 項平均 ) は入力する必要がある 1 2 の操作を [ 区間 ] を 5 にし出力先を $F$5 にして同様に行うと 5 項移動平均が F5 から F27 に作成されるなおデータ中の #N/A とは Not Available の意でデータが存在しないことを意味している

8 320 図項移動平均と 5 項移動平均 4. 昭和基地の元データ 3 項移動平均 5 項移動平均のそれぞれについてグラフにすると図のようになる ( 詳しくは本書第 9 章を参照されたい ) 元データに比べ移動平均を求めたもののほうが平滑化されているのがわかるまた [ 区間 ] の項目の値を増やすことによりより平滑化されたグラフが得られるが一方で元データからの誤差が大きくなることがわかる図平滑化したグラフ

9 回帰直線 : 回帰分析時系列データに関する回帰直線を求めると将来の傾向がつかめるようになりその直線から予測値を得られる課題 3では横軸に西暦縦軸にオゾン量をプロットしたグラフについて回帰分析を行えばよいこれによりオゾン層が将来どうなるかを予測できる回帰直線を求める手順を以下に示す 1. [ データ分析 ] ウィンドウから [ 回帰分析 ] を選択し [OK] ボタンをクリックする ( 図 10.16) そうすると図の [ 回帰分析 ] ウィンドウが開く図回帰分析ツールの起動図回帰分析ウィンドウ 2. [ 回帰分析 ] ウィンドウに設定項目を入力する [ 入力 Y 範囲 ] にはオゾン量のデータ範囲を先頭行のラベルも含めて指定する ( 図 10.18(1)) [ 入力 X 範囲 ] には西暦のデータ範囲を同じくラベルも含めて指定する ( 図 10.18(2)) X Y の範囲の先頭行にラベルを含むので [ ラベル ] のチェックボックスをオンにする ( 図 (3)) 回帰分析の結果得られる回帰直線の原点を X=0 Y=0 に設定したいときには [ 定数に 0 を使用 ] のチェックボックスをオンにする有意水準の検定率を指定するときには [ 有意水準 ] のチェックボックスをオンにし検定率を入力する結果の出力先として [ 新規ワークシート ] を選択し回帰分析と入力する ( 図 10.18(4)) また残差表と観測値グラフも出力するように指定している ( 図 (5))

10 322 (1) (3) (2) (4) (5) 図回帰分析条件入力 3. すべての設定が完了したら [OK] ボタンをクリックするそうすると図に示す回帰分析結果が表示される表およびグラフ中に表示された予測値が回帰直線の離散データである図では予測値グラフの種類を直線に変更しているまた残差は観測値と予測値の差をあらわしている図回帰分析結果ここで課題 3で指定された X 軸交点すなわちオゾン量が 0 になる西暦を求める準備として D25 と D26 のセルに各々の西暦 (1974 と 1975) を入力しておく予測値は直線をなすので以下の式で与えられる

11 323 y = ax + b 直線の傾きは直線上の 2 つの点を (x 1 y 1 ) (x 2 y 2 とすると a = y 1 y 2 x 1 x 2 であるので図に示すように E26 のセルに = (B26-B25) / (D26-D25) と入力すると直線の傾きが求められる一方 Y 軸の交点 b は以下の式で求められる b = y ax この式に直線上の値 ( 予測値のひとつ ) と上記の傾きを当てはめると b が求まるしたがって F26 のセルに = B26-E26*D26 と入力すると b が計算される以上から直線の式 y = x が求まるここで y を 0 とすると F26/E26 の演算により X 軸の交点が求まる 0 = x x = これによって求まった値がオゾン量 0 になる西暦であるしたがってこのまま人類が何の環境対策も行わずにいると西暦 2021 年前後に昭和基地上空の 10 月のオゾン量は 0 になり紫外線が直接地表に降り注ぐかもしれない図回帰直線の X 軸 Y 軸交点 ( 実際は E26 F26 G26 には計算された結果が表示される )

12 ヒストグラム相関分析例題 :2000 年セリーグの上位打者の統計処理図は 2000 年プロ野球セリーグの上位 30 位までの打者成績を示しているこの打者成績について以下の分析を行え 1 平均打率のヒストグラム ( 度数分布 ) を求めよ 2 各成績項目について相関を求め長打率と 3B ヒット長打率とホームランの相関関係について議論せよ図年セリーグ上位打者成績ただしこの例題の Excel ファイルは問題用と解答例それぞれがに用意してあるので自由にダウンロードして用いてよいヒストグラム対象となるデータデータ区間のセル範囲の個別頻度および累積頻度を求めるデータグループに含まれる特定の値の出現頻度を求めるにはヒストグラム分析ツールを用いる課題 1はヒストグラムを作成すればよいそこで分析ツールを起動しヒストグラムを作成する手順を以下に示す 1. ヒストグラム分析ツールを起動する前にデータ区間を定義するために図のような各区間の値を打者成績のあるワークシートの余白に用意するデータ区間はヒストグラムの横軸の値となるので分布がわかる程度細かい値を作成する

13 325 図データ区間の定義 2. [ データ分析 ] ウィンドウから [ ヒストグラム ] を選択し [OK] ボタンをクリックする ( 図 10.23) そうすると図に示す [ ヒストグラム ] ウィンドウが開く図ヒストグラム分析ツールの起動図ヒストグラムウィンドウ 3. 入力範囲には平均打率のデータ範囲を入力する ( 図 10.25(1)) データ区間には平均打率の度数を表す区間のデータ範囲を入力する ( 図 10.25(2)) ヒストグラムの出力先として [ 新規ワークシート ] を選択し 0.05 刻み打率ヒストグラムと入力する ( 図 10.25(3)) また累積度数分布も求めるようにしグラフも作成するように指定する ( 図 10.25(4)) ここでパレート図とは各区間の度数が大きい順に表示されるグラフを作成するオプションであるここの例ではこれは指定しない

14 326 (1) (2) (3) (4) 図ヒストグラム設定入力 4. 設定が終了したら [OK] ボタンをクリックするそうすると図に示すヒストグラムの表とグラフが 0.05 刻み打率ヒストグラムワークシートに作成される図刻みデータ区間の場合のヒストグラム (1) 5. ここで作成したヒストグラム ( 図 10.26) はグラフの棒の幅とデータ区間の幅が異なっているヒストグラムならば棒の幅は対応する区間の幅に等しくする必要がある 4 で作成したグラフの頻度のデータ系列をクリックしリボンの [ グラフツール ] の [ レイアウト ] タブをクリックする [ レイアウト ] タブの [ 現在の選択範囲 ] の [ 選択対象の書式設定 ] をクリックする ( 図 10.27)

15 327 頻度のいずれかのデータマーカーをクリックすることでデータ系列を選択する図レイアウトタブ 6. [ 系列のオプション ] を選択し [ 要素の間隔 ] に 0 と入力する ( 図 10.28) 7. 設定が終了したら [ 閉じる ] ボタンをクリックするそうすると図に示すヒストグラムのグラフに変更される図選択範囲の書式設定図刻みデータ区間の場合のヒストグラム (2) ここの操作例ではデータ区間を 0.05 刻みに設定したがデータ区間を 0.01 刻みに設定するとより詳しいヒストグラムを作成することができる 0.01 刻みのデータ区間を指定した場合のヒストグラムを図に示す

16 328 図刻みデータ区間の場合のヒストグラム相関相関分析ツールを使うと 2 つのデータグループが一緒に変化するかどうかを調べることができる相関分析ツールによって求められる相関係数の特性として次の点があげられる相関係数は-1 と 1 の間の値を取る相関係数が正のとき正の相関があるといい横軸の変量の値が増加すると縦軸の変量の値も増加する関係にある相関係数が負のとき負の相関があるといい横軸の変量の値が増加すると縦軸の変量の値は減少する関係にある相関係数の絶対値が 1 のとき完全相関であるといい 2 つの変量は同じものをあらわしていることになる例題の課題 2では長打率に対する 3B ヒットの相関長打率に対するホームランの相関を議論すればよいここではすべての項目に対して相関係数を求め対象となる相関に焦点を当てる 1. [ データ分析 ] ウィンドウから [ 相関 ] を選択し [OK] ボタンをクリックする ( 図 10.31) そうすると図に示す [ 相関 ] ウィンドウが開く

17 329 図相関分析ツールの起動図相関分析ウィンドウ 2. [ 相関 ] ウィンドウの設定項目に値を入力する ( 図 10.33) [ 入力範囲 ] には相関を求めるデータの範囲を入力する ( 図 10.33(1)) 打者成績のすべての項目について相関を求めるので D2 から R31 までの範囲を選択するデータの方向は列方向なので [ 列 ] をチェックする ( 図 10.33(2)) ここの例では入力範囲の先頭行にデータラベルを含む範囲を入力するので [ 先頭行をラベルとして使用 ] チェックボックスをオンにする ( 図 10.33(3)) また結果の出力先として[ 新規ワークシート ] を選択し相関と入力する ( 図 10.33(4)) (1) (3) (2) (4) 図相関分析設定入力 3. 設定が完了したら [OK] ボタンをクリックするそうすると図に示す表が相関分析結果として表示される相関係数の特性として A に対する B の相関は B に対する A の相関と同じなのでこの表での上半分のデータは省略されている

18 330 (1) (2) 図相関解析結果従って課題 2の解答は長打率と 3B ヒットは相関係数が 0 に近いため ( 図 10.34(1)) 関係がほとんどないすなわち長打率が高くても 3B ヒットを多く打つ可能性は低い一方長打率とホームランに関しては相関係数が高いため ( 図 (2)) 長打率が高ければ高いほどホームラン数は多くなることを示しているしたがって長打率が高い打者はホームランになる確率が高いため 3B ヒットは少ないと結論付けることができるとすればよい 10.4 誤差入りグラフ近似曲線半 ( 片 ) 対数グラフ例題 : ウランの放射能測定ウラン 237 の半減期は約 7 日間であるウラン 237 の数 A(t) は経過日数 tとt = 0の時のウラン 237 の数 A 0 を用いた関係式 A(t) = A 0 e λt で表される図はウラン 237 の経過日数 tが増加したときのウラン 237 の数 A(t) を実験から求めた値を示しているこのデータを元に 1 測定誤差入りのグラフを示せ 2 近似曲線を求めよこのとき近似式は指数近似を求めその式を示しウランの崩壊定数 λを求めよ 3 求めた近似曲線を片 ( 半 ) 対数グラフで示せただしこの例題の Excel ファイルは問題用と解答例それぞれがに用意してあるので自由にダウンロードして用いてよい

19 331 図ウランの放射能の強さのデータ誤差入りグラフまず課題 1の測定誤差入りグラフを作成する最初に Excel の機能であるグラフウィザードの散布図を用いてグラフを作成するその後にデータ系列に対し誤差を追加するその手順を以下に示す 1. グラフを作成する ( グラフの作成方法については本書第 9 章を参照されたい ) 図に示すような散布図のグラフを作成したことを前提に説明を続ける図ウランの放射能の測定グラフの作成 2. 図のグラフをクリックした後にリボンの [ グラフツール ] の [ レイアウト ] タブをクリックする ( 図 10.37)

20 332 図レイアウトタブ 3. [ レイアウト ] タブの [ 分析 ] で [ 誤差範囲 ] をクリックしドロップダウンボックスの [ その他の誤差範囲オプション ] をクリックする ( 図 10.38) 図誤差範囲 4. [ 誤差範囲の書式設定 ] ウィンドウの [ 縦軸誤差範囲 ] タブをクリックする [ 表示 ] の方向を [ 両方向 ] 終点のスタイルを[ ギャップあり ] に [ 誤差範囲 ] を [ ユーザー設定 ] に指定する ( 図 10.39) その後 [ 値の指定 ] ボタンをクリックする [ ユーザー設定の誤差範囲 ] ウィンドウが開くので [ 正の誤差の値 ] および [ 負の誤差の値 ] に測定誤差のデータ範囲 (C3:C11) を入力する ( 図 10.40) 設定が完了したら [OK] ボタンをクリックする

21 333 図ユーザー設定の誤差範囲ウィンドウ図誤差範囲の設定 5. 設定が終了したら [ 閉じる ] ボタンをクリックするそうすると図に示す測定誤差入りグラフに変更される

22 334 図測定誤差入りグラフ (1) 6. ここで作成した測定誤差入りグラフ ( 図 10.41) は X 軸 Y 軸の両軸に誤差範囲が表示されている課題 1では Y 軸つまりウランの数の誤差範囲のみが表示されればよいので X 軸の誤差範囲を削除する必要がある 5 で作成したグラフ ( 図 10.41) をクリックした後にリボンの [ グラフツール ] の [ レイアウト ] タブをクリックする 7. [ レイアウト ] タブの [ 現在の選択範囲 ] で [ グラフの要素 ] ボックスの [ 系列 X 軸誤差範囲 ] を選択するその後 [ 分析 ] タブの [ 誤差範囲 ] をクリックしドロップダウンボックスの [ なし ] をクリックする ( 図 10.42) 図グラフツール誤差範囲

23 そうすると図に示す測定誤差入りグラフに変更されるこれで課題 1 は完了である図測定誤差入りグラフ (2) 近似曲線データ系列を指定し近似曲線を求める手順を示していく 1. 図のグラフをクリックした後にリボンの [ グラフツール ] の [ レイアウト ] タブをクリックする 2. [ レイアウト ] タブの [ 分析 ] で [ 近似曲線 ] をクリックしドロップダウンボックスの [ その他の近似曲線オプション ] をクリックする ( 図 10.44) 図近似曲線

24 [ 近似曲線の書式設定 ] ウィンドウの [ 近似曲線のオプション ] タブをクリックする [ 近似または回帰の種類 ] で [ 指数近似 ] を指定する ( 図 10.45(1)) さらに近似式も表示したいので [ グラフに数式を表示する ] チェックボックスをオンにする ( 図 (2)) (1) (2) 図近似曲線の書式設定 4. すべての必要項目の設定が完了したら [ 閉じる ] ボタンをクリックするそうすると図に示す指数近似された近似曲線と近似式を含んだグラフが表示される課題 2の近似曲線については図のグラフでよい λについてはグラフ中に示されている近似式のeの指数であるから 0.10 と求まるしたがって実験値から求めた近似式は A(t) = e 0.10t であるこの式のtの係数よりウランの崩壊定数 λは 0.10 であるとすればよい

25 337 図近似曲線が追加されたグラフ片 ( 半 ) 対数グラフ片 ( 半 ) 対数グラフとは一方の軸が対数値になっているグラフのことである Excel で何も指定せずにグラフを作成すると軸は対数表示にならない軸を対数表示にするには特別な指定が必要である本節では Y 軸を対数表示にする手順について説明していく 1. 図のグラフをクリックした後にリボンの [ グラフツール ] の [ レイアウト ] タブをクリックする [ レイアウト ] タブの [ 現在の選択範囲 ] で [ グラフの要素 ] ボックスの [ 縦 ( 値 ) 軸 ] を選択し [ 選択対象の書式設定 ] をクリックする ( 図 10.47) 図グラフツールレイアウト 2. [ 軸の書式設定 ] ウィンドウが開くので [ 軸のオプション ] タブをクリックし [ 対数目盛を表示する ] のチェックボックスをオンにし [ 基数 ] として 10 を入力する( 図 10.48) 設定が完了したら[ 閉じる ] ボタンをクリックする

26 338 図軸の書式設定 3. 図に示すように Y 軸が対数のグラフが作成される課題 2で作成された近似曲線は指数関数なので Y 軸を対数表示させることによりグラフが曲線から直線になっている図 Y 軸を対数表示した片 ( 半 ) 対数グラフ以上 Y 軸に関して対数表示したグラフを示すことができたので課題 3 は完了する

タイトルを修正軸ラベルを挿入グラフツールデザイングラフ要素を追加軸ラベル第 1 横 ( 縦 ) 軸凡例は削除横軸は, 軸の目盛範囲の最小値最大値を手動で設定して調整図 2 散布図の仕上げ見本相関係数の計算散布図を見ると, 因果関係はともかく, 人口と輸送量の間には相関関係があ

タイトルを修正軸ラベルを挿入グラフツールデザイングラフ要素を追加軸ラベル第 1 横 ( 縦 ) 軸凡例は削除横軸は, 軸の目盛範囲の最小値最大値を手動で設定して調整図 2 散布図の仕上げ見本相関係数の計算散布図を見ると, 因果関係はともかく, 人口と輸送量の間には相関関係があ Excel を使った相関係数の計算回帰分析準備データは授業のホームページ上に Excel ブックの状態 ( ファイル名 pop_traffic.xlsx) で用意してあるので, これをダウンロードして保存しておくダウンロードされたファイルを開いたら,DATA シート中の空欄 (POP,TK の列 ) をそれぞれの合計値 (POP の場合は,POP1~POP3) で埋めるように,SUM 関数あるいは和の式を使って処理しておく

314 図 10.1 分析ツールの起動 図 10.2 データ分析ウィンドウ [ データ ] タブに [ 分析 ] がないときは 以下の手順で表示させる 1. Office ボタン をクリックし Excel のオプション をクリックする ( 図 10.3) 図 10.3 Excel のオプション

314 図 10.1 分析ツールの起動図 10.2 データ分析ウィンドウ [ データ ] タブに [ 分析 ] がないときは以下の手順で表示させる 1. Office ボタンをクリックし Excel のオプションをクリックする ( 図 10.3) 図 10.3 Excel のオプション