Excel で学ぶ 多変量データ処理入門

Excel で学ぶ
多変量データ処理入門
講義補助テキスト
太成学院大学
坂
元
保
経営学部
秀
著
ま
え が き
本テキストは，種々の分野で収集された多変量データを Microsoft Excel を用いて処理す
る方法を述べたものである．特に，収集した多変量データを処理するために Software がな
く断念した，また Software を購入するまでに至らなかった等，初期の目的を達成できなか
ったとの意見を聞いたことがあり Excel の基本関数を用いて解析を試みた．
多変量統計解析の理論を理解するためには，かなり統計学の基礎知識や数学的基礎知識
を理解しておく必要があるが，それぞれのデータ処理法が何を目的としたデータ処理なの
かを理解して解析の方法論を修得して欲しいと願っている．
本テキストでは，時間さえ十分にかければ Microsoft Excel を用いて多変量データの処理
は可能であることをポイントとしている．各章に Excel 演習問題ワークシートおよび Excel
演習問題解答を配置し，
また固有方程式の解法には Excel VBA を用いてプログラムを Excel
シートに作成した．いくつかの章に対しては，Excel 演習問題ワークシート No.1 と No.2
または No.3 の三つを提示しているので，応用として No.2 または No.3 のワークシートにも
挑戦していただきたいと願っている．演習問題で扱ったデータは，表示用として少ない変
数およびサンプルとして提示してあるが，本来の多変量データは多変数および多サンプル
となるので，本書では処理方法に重点をおいている．さらには統計的な解析については言
及していない．
本テキストで示した Excel 演習問題ワークシートおよび Excel 演習問題解答については
ホームページで公開した．公開先のＵＲＬは，
http://page.sannet.ne.jp/yo-skmt/demuva.html
である．
また本書で扱っている
（１）重回帰分析
（２）主成分分析
（３）因子分析
（４）判別分析
（５）正準相関分析
（６）クラスター分析
（７）数量化理論Ⅰ類
（８）数量化理論Ⅱ類
（９）数量化理論Ⅲ類
（10）数量化理論Ⅳ類
の処理方法については，Windows 用アプリケーションソフトとして開発作成しているので
公開しインストールを WindwsXP/7/8/8.1 まで対応させた．本書については大学の講義・演
習，社内教育等でご使用いただきご意見を賜れば幸いである．
2015 年 2 月 12 日
坂 元 保 秀
E-mail：[email protected]（学外用）
[email protected]（学内用）
目
次
第１章 行列の基礎
１．１ ベクトル表現・・・・・・・・・・・・・・・・・・・・ 1
１．２ 行列表現・・・・・・・・・・・・・・・・・・・・・・ 1
１．３ ベクトルの演算・・・・・・・・・・・・・・・・・・・ 2
１．４ 行列の演算・・・・・・・・・・・・・・・・・・・・・ 2
１．５ 逆行列・・・・・・・・・・・・・・・・・・・・・・・ 3
第１章 Excel 演習問題・・・・・・・・・・・・・・・・・・・・ 4
第１章 Excel 演習問題ワークシート・・・・・・・・・・・・・・ 5
第２章 統計的処理の基礎
２．１ データの分布・・・・・・・・・・・・・・・・・・・・
２．１．１ ヒストグラムの作成法・・・・・・・・・・・・・・
２．１．２ 散布図の作成法・・・・・・・・・・・・・・・・・
２．２ データの基本統計量・・・・・・・・・・・・・・・・・
２．２．１ 分布の中心的傾向・・・・・・・・・・・・・・・・
２．２．２ 分布の散らばり的傾向・・・・・・・・・・・・・・
２．２．３ 二つの特性間の関係・・・・・・・・・・・・・・・
２．３ 正規分布・・・・・・・・・・・・・・・・・・・・・・
２．３．１ 正規分布の確率・・・・・・・・・・・・・・・・・
２．３．２ 多変量正規分布・・・・・・・・・・・・・・・・・
第２章 Excel 演習問題・・・・・・・・・・・・・・・・・・・・
第２章 Excel 演習問題ワークシート・・・・・・・・・・・・・・
7
7
8
9
9
9
10
11
11
12
14
16
第３章 多変量データとは
３．１ 多変量データの行列表示・・・・・・・・・・・・・・・ 19
３．２ データの種類・・・・・・・・・・・・・・・・・・・・ 20
３．３ 多変量データの基本統計量・・・・・・・・・・・・・・ 21
３．３．１ 全変数が定量的データのとき・・・・・・・・・・・ 21
３．３．２ 変数に定性的データが含まれるとき・・・・・・・・ 22
第３章 Excel 演習問題・・・・・・・・・・・・・・・・・・・・ 23
第３章 Excel 演習問題ワークシート・・・・・・・・・・・・・・ 24
第３章 Excel 演習問題ワークシート・・・・・・・・・・・・・・別 24
第４章 重回帰分析
４．１ 重回帰分析とは・・・・・・・・・・・・・・・・・・・
４．２ 重回帰モデルと偏回帰係数の推定・・・・・・・・・・・
４．３ 重回帰式の妥当性・・・・・・・・・・・・・・・・・・
４．４ 推定した回帰式による予測・・・・・・・・・・・・・・
４．５ 変数間の分散共分散の推定・・・・・・・・・・・・・・
４．６ 変数間の相関行列の推定・・・・・・・・・・・・・・・
４．７ 変数間の偏相関行列の推定・・・・・・・・・・・・・・
第４章 Excel 演習問題・・・・・・・・・・・・・・・・・・・・
第４章 Excel 演習問題ワークシート・・・・・・・・・・・・・・
−i−
27
27
30
30
30
31
31
33
34
第５章 主成分分析
５．１ 主成分分析とは・・・・・・・・・・・・・・・・・・・ 36
５．２ 主成分の導出・・・・・・・・・・・・・・・・・・・・ 36
５．２．１ 変数が２個の場合の主成分の導出・・・・・・・・・ 37
５．２．２ 変数がｐ個の場合の主成分の導出・・・・・・・・・ 39
５．２．３ 寄与率・・・・・・・・・・・・・・・・・・・・・ 40
５．２．４ 因子負荷量・・・・・・・・・・・・・・・・・・・ 40
５．２．５ 主成分得点・・・・・・・・・・・・・・・・・・・ 40
５．３ 標準化したデータを用いる主成分分析・・・・・・・・・ 41
５．３．１ 主成分の導出・・・・・・・・・・・・・・・・・・ 41
５．３．２ 寄与率・・・・・・・・・・・・・・・・・・・・・ 43
５．３．３ 因子負荷量・・・・・・・・・・・・・・・・・・・ 43
５．３．４ 主成分得点・・・・・・・・・・・・・・・・・・・ 43
５．４ ラグランジュの未定乗数法・・・・・・・・・・・・・・ 44
５．５ 固有方程式・・・・・・・・・・・・・・・・・・・・・ 44
５．６ 固有値，固有ベクトルを求めるプログラム使用手順・・・ 44
第５章 Excel 演習問題・・・・・・・・・・・・・・・・・・・・ 47
第５章 Excel 演習問題ワークシート・・・・・・・・・・・・・・ 49
第５章 Excel 演習問題ワークシート・・・・・・・・・・・・・・別 49
第６章 判別分析
６．１ 判別分析とは・・・・・・・・・・・・・・・・・・・・ 54
６．２ 説明変数１個の場合の判別分析・・・・・・・・・・・・ 54
６．２．１ 線型判別関数による判別・・・・・・・・・・・・・ 54
６．２．２ 分散の推定・・・・・・・・・・・・・・・・・・・ 55
６．２．３ 誤判別の確率・・・・・・・・・・・・・・・・・・ 55
６．３ 説明変数２個の場合の判別分析・・・・・・・・・・・・ 56
６．３．１ 線型判別関数による判別・・・・・・・・・・・・・ 56
６．３．２ 分散共分散行列の推定・・・・・・・・・・・・・・ 58
６．３．３ 誤判別の確率・・・・・・・・・・・・・・・・・・ 59
６．４ 説明変数ｐ個の場合の判別分析・・・・・・・・・・・・ 59
６．４．１ ｐ変数ｋ群による線型判別関数による判別・・・・・ 59
６．４．２ 誤判別の確率・・・・・・・・・・・・・・・・・・ 61
６．５ 行列表示・・・・・・・・・・・・・・・・・・・・・・ 61
６．５．１ 基本統計量・・・・・・・・・・・・・・・・・・・ 61
６．５．２ 線型判別関数・・・・・・・・・・・・・・・・・・ 62
６．５．３ 誤判別の確率・・・・・・・・・・・・・・・・・・ 62
第６章 Excel 演習問題・・・・・・・・・・・・・・・・・・・・ 63
第６章 Excel 演習問題ワークシート・・・・・・・・・・・・・・ 64
第６章 Excel 演習問題ワークシート・・・・・・・・・・・・・・別 64
第７章 正準相関分析
７．１ 正準相関分析とは・・・・・・・・・・・・・・・・・・ 67
７．２ 正準変量および正準相関係数の求め方・・・・・・・・・ 67
− ii −
７．３ 第２番目以降の正準変量および正準相関係数・・・・・・ 72
第７章 Excel 演習問題・・・・・・・・・・・・・・・・・・・・ 74
第７章 Excel 演習問題ワークシート・・・・・・・・・・・・・・ 75
第７章 Excel 演習問題ワークシート・・・・・・・・・・・・・・別 75
第８章 因子分析
８．１ 因子分析とは・・・・・・・・・・・・・・・・・・・・ 78
８．２ 因子分析モデルと因子行列・・・・・・・・・・・・・・ 79
８．３ 共通性の推定・・・・・・・・・・・・・・・・・・・・ 82
８．４ 非反復法による因子行列の推定・・・・・・・・・・・・ 82
８．５ 反復法による因子行列の推定・・・・・・・・・・・・・ 84
８．６ 因子得点の推定・・・・・・・・・・・・・・・・・・・ 85
８．７ 固有方程式・・・・・・・・・・・・・・・・・・・・・ 87
【参考】因子分析による因子得点の推定法（回帰推定法）
・・・・ 87-1
第８章 Excel 演習問題・・・・・・・・・・・・・・・・・・・・ 88
第８章 Excel 演習問題ワークシート・・・・・・・・・・・・・・ 90
第８章 Excel 演習問題ワークシート・・・・・・・・・・・・・・別 90
第８章 Excel 演習問題ワークシート（ＳＭＣ規準）・・・・・・・ 別 90
第９章 クラスター分析
９．１ クラスター分析とは・・・・・・・・・・・・・・・・・ 95
９．２ クラスター構成法・・・・・・・・・・・・・・・・・・ 95
９．３ 類似性を測る統計量・・・・・・・・・・・・・・・・・ 95
９．４ クラスター間の類似性を測る統計量・・・・・・・・・・ 96
９．４．１ クラスター間の類似性を測る最短距離法・・・・・・ 96
９．４．２ クラスター間の類似性を測るウォード法・・・・・・ 98
９．５ クラスターのデンドログラム・・・・・・・・・・・・・100
第９章 Excel 演習問題・・・・・・・・・・・・・・・・・・・・101
第９章 Excel 演習問題ワークシート・・・・・・・・・・・・・・103
第 10 章 数量化理論Ⅰ類
10．１ 数量化理論Ⅰ類とは・・・・・・・・・・・・・・・・・108
10．２ ダミー法・・・・・・・・・・・・・・・・・・・・・・108
10．３ 数量化理論Ⅰ類回帰モデルとカテゴリ数量の推定・・・・110
10．４ カテゴリ数量の基準化・・・・・・・・・・・・・・・・113
10．５ 回帰式の妥当性・・・・・・・・・・・・・・・・・・・114
10．６ 推定した回帰式による予測・・・・・・・・・・・・・・114
10．７ アイテム間の分散共分散の推定・・・・・・・・・・・・115
10．８ アイテム間の相関行列の推定・・・・・・・・・・・・・115
10．９ アイテム間の偏相関行列の推定・・・・・・・・・・・・115
第 10 章 Excel 演習問題 ・・・・・・・・・・・・・・・・・・・117
第 10 章 Excel 演習問題ワークシート ・・・・・・・・・・・・・118
第 11 章 数量化理論Ⅱ類
11．１ 数量化理論Ⅱ類とは・・・・・・・・・・・・・・・・・122
−iii−
11．２ ダミー法・・・・・・・・・・・・・・・・・・・・・・122
11．３ アイテム数２個の場合の数量化理論Ⅱ類・・・・・・・・122
11．３．１ 線型判別関数による判別・・・・・・・・・・・・・122
11．３．２ 分散共分散行列の推定・・・・・・・・・・・・・・124
11．３．３ 誤判別の確率・・・・・・・・・・・・・・・・・・126
11．４ アイテム数ｐ個の場合の数量化理論Ⅱ類・・・・・・・・126
第 11 章 Excel 演習問題 ・・・・・・・・・・・・・・・・・・・127
第 11 章 Excel 演習問題ワークシート ・・・・・・・・・・・・・128
第 12 章 数量化理論Ⅲ類
12．１ 数量化理論Ⅲ類とは・・・・・・・・・・・・・・・・・132
12．２ ダミー法・・・・・・・・・・・・・・・・・・・・・・132
12．３ 数量化変数の推定・・・・・・・・・・・・・・・・・・132
12．４ 一般的なデータ表に基づく数量化変数の推定・・・・・・136
第 12 章 Excel 演習問題 ・・・・・・・・・・・・・・・・・・・139
第 12 章 Excel 演習問題ワークシート ・・・・・・・・・・・・・140
第 13 章 数量化理論Ⅳ類
13．１ 数量化理論Ⅳ類とは・・・・・・・・・・・・・・・・・146
13．２ 数量化変数の推定・・・・・・・・・・・・・・・・・・147
13．３ 分析対象物の数量化・・・・・・・・・・・・・・・・・149
13．４ 行列 H の求め方 ・・・・・・・・・・・・・・・・・・150
13．５ 固有値と固有ベクトル・・・・・・・・・・・・・・・・153
第 13 章 Excel 演習問題 ・・・・・・・・・・・・・・・・・・・157
第 13 章 Excel 演習問題ワークシート ・・・・・・・・・・・・・159
第 14 章 クロス表を応用した数量化理論Ⅰ類
14．１ クロス集計表・・・・・・・・・・・・・・・・・・・・162
14．２ 偏差積和行列・・・・・・・・・・・・・・・・・・・・163
14．３ カテゴリ数量の推定・・・・・・・・・・・・・・・・・164
第 14 章 Excel 演習問題・・・・・・・・・・・・・・・・・・・ 165
第 14 章 Excel 演習問題ワークシート・・・・・・・・・・・・・ 166
第 15 章 クロス表を応用した数量化理論Ⅱ類
15．１ クロス集計表・・・・・・・・・・・・・・・・・・・・173
15．２ 偏差積和行列・・・・・・・・・・・・・・・・・・・・173
15．３ 線型判別関数の推定・・・・・・・・・・・・・・・・・175
15．４ 誤判別の確率・・・・・・・・・・・・・・・・・・・・176
第 15 章 Excel 演習問題・・・・・・・・・・・・・・・・・・・ 177
第 15 章 Excel 演習問題ワークシート・・・・・・・・・・・・・ 178
【付録】Excel 関数を用いた基本統計量の求め方・・・・・・・・・・ 187
∼関数の使用法いろいろ データ表から直接求める方法を規準として∼
− iv −
Excel 演習問題解答
第１章 Excel 演習問題解答・・・・・・・・・・・・・・・・・解 1
第２章 Excel 演習問題解答・・・・・・・・・・・・・・・・・解 3
第３章 Excel 演習問題解答・・・・・・・・・・・・・・・・・解 6
Excel 演習問題解答・・・・・・・・・・・・・・・・・別解 6
第４章 Excel 演習問題解答・・・・・・・・・・・・・・・・・解 9
Excel 演習問題解答・・・・・・・・・・・・・・・・・別解 9
第５章 Excel 演習問題解答・・・・・・・・・・・・・・・・・解 11
Excel 演習問題解答・・・・・・・・・・・・・・・・・別解 11
第６章 Excel 演習問題解答・・・・・・・・・・・・・・・・・解 16
Excel 演習問題解答・・・・・・・・・・・・・・・・・別解 16
第７章 Excel 演習問題解答・・・・・・・・・・・・・・・・・解 19
Excel 演習問題解答・・・・・・・・・・・・・・・・・別解 19
第８章 Excel 演習問題解答・・・・・・・・・・・・・・・・・解 22
Excel 演習問題解答・・・・・・・・・・・・・・・・・別解 22
Excel 演習問題解答(ＳＭＣ規準)・・・・・・・・・・・別解 22
第９章 Excel 演習問題解答・・・・・・・・・・・・・・・・・解 27
第 10 章 Excel 演習問題解答 ・・・・・・・・・・・・・・・・解 33
第 11 章 Excel 演習問題解答 ・・・・・・・・・・・・・・・・解 37
第 12 章 Excel 演習問題解答 ・・・・・・・・・・・・・・・・解 41
第 13 章 Excel 演習問題解答 ・・・・・・・・・・・・・・・・解 47
第 14 章 Excel 演習問題解答 ・・・・・・・・・・・・・・・・解 50
第 15 章 Excel 演習問題解答 ・・・・・・・・・・・・・・・・解 57
【付録】Excel 関数を用いた基本統計量の求め方・・・・・・・・解 66
∼関数の使用法いろいろ データ表から直接求める方法を規準として∼
参考文献
−v−