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

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

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

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

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

317 1. [ データ分析 ] ウィンドウから [ 基本統計量 ] を選択し [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] ボタンをクリックする そうすると 図 10.10 に 示す基本統計量の表が 基本統計量 ワークシートに作成される ここから 平均

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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