Excelのピボットテーブルでクロス集計から統計解析まで

　先日の鬼嫁/神嫁婚前判定用アンケートを実施しますから鬼嫁/神嫁婚前判定用アンケート結果に至るまでの解析の過程が誰かの役に立つだろうと考えて投稿する次第．ガチの統計手法だから一般社会人はとりあえずこれマスターしとけ．あ，大学で専門的にやってる人は除外ね．多変量解析とかはここでは扱わない．

　大まかな流れとしては次のような感じ．今回の投稿はできれば Excel のインストールしてある PC で見てくれ．スマホだと演習問題が解けない．

データの収集

　データを収集する方法については省略．Googleスプレッドシートのフォームでアンケートを作ってみたでも参考にして自分で収集するも良し，会社なら既にデータベースに登録されてるのもあるだろう．それを Excel で開く．

ピボットテーブルはクロス表だ！

　最初に大事なことを書いた．大事なことだからもう一度書く．ピボットテーブルはクロス表だ．

　ピボットテーブルでデータをクロス集計することは統計解析の基本となる．カイ二乗検定，Fisher直接確率，オッズ比，リスク比，ハザード比およびそれらの95%信頼区間を算出するための基礎だ．

　今の時代，統計ができない情弱は簡単に騙される．だからまずデータを見たらピボットテーブルを作ることを覚えてしまおう．

　データをある基準で二つに分けることは，古くはデカルトの二元論に由来する．日本人にはこういう考え方は向かないという向きもあるが，実際に世の中はそこから進歩してきたのだからグダグダ言っても仕方がない．

クロス表の書き方

　クロス表の記法には明確な決まりはない．決まりはないが，慣例的な記法ならある．表頭に結果を，表側に検査を書く．

　結果とは事実を指す．事実とは人の主観では決められない客観的なことだ．患者の生死，新薬の効果の有無，癌の診断など．ここでは鬼嫁か否かが結果にあたる．

　検査とは人の意思の介入することだ．無作為化比較試験における抗癌剤の使用の有無，腫瘍マーカーなどだ．ここでは鬼嫁の兆候の有無が検査に当たる．

	結果	結果
検査			周辺度数
検査			周辺度数
	周辺度数	周辺度数	周辺度数

　このレイアウトは学術論文でよく目にする．その理由は，恐らく結果は二つしかなくて右には伸びないが，検査はいくつもあって表が下に伸びていくという性質があるからじゃないかと思っている．要は，印刷の都合ってやつだ．例えば，新薬の効果を調べるための医学論文なんかでは結果が「死亡」か「打ち切り」かの二つしかないが，検査にはいろいろな要素があるからね．

　最も抽象度の高い記法が下表になる．表頭の左側には真の結果を，右側には偽の結果を書く．表側の上には検査陽性の項目を，下には検査陰性の項目を書く．

	真	偽
陽性			陽性数
陰性			陰性数
	真の数	偽の数	総数

　今回なら表頭の左側に「鬼嫁」，右側に「それ以外」となり，表側の上に「セックスの拒否」，下に「なし」となる．

	鬼嫁	それ以外
セックスの拒否	a	b	a+b
なし	c	d	c+d
	a+c	b+d	a+b+c+d

　この原則さえ守れば，クロス表はとても見やすいものになる．では，実際にピボットテーブルを作っていこう．

ピボットテーブルを作る

　これは実際に手を動かしてみないとよく分からない人も多いだろう．なので，今回の解析に用いたデータを一部分，切り出して置いておく．「鬼嫁判定」と「鬼嫁の兆候」の実際のデータなので，結果も合うはずだ．

鬼嫁データシート

　ダウンロードしたファイルを Excel で開くと，ワークシートが 1 枚ある．「挿入」タブの一番左に「ピボットテーブル」があるのでクリック．

　ダイアログが出るが，そのまま「OK」をクリックする．

　新規ワークシートに白紙のピボットテーブルが作られ，ウィンドウの右側に「ピボットテーブルのフィールド」が出る．「ピボットテーブルのフィールド」の上半分にはデータシートの項目名が並んでいる．下半分にはボックスが四つある．ここへいろいろ放り込んでいく．

　ピボットテーブルについて解説したサイトや書籍の殆どは「とにかく色々試してみましょう」としか書いてない．書いた奴が何にも分かってないことがこの一文で分かってしまう．

　「ピボットテーブルのフィールド」の「ボックス」はクロス表と対応している．つまり，「列」は表頭の「結果」に，「行」は表側の「検査」に，「Σ値」は集計フィールドに対応する．さっきの表をもう一度置いておくから，よく見比べてくれ．

	真	偽
陽性			陽性の数
陰性			陰性の数
	真の数	偽の数	総数

「ピボットテーブルのフィールド」の上のペインにある「鬼嫁判定」をドラッグして「列のボックス」に落とす
「ピボットテーブルのフィールド」の上のペインにある「鬼嫁判定」をもう一度ドラッグして今度は「Σ値のボックス」に落とす（Σ値のボックスには「個数/鬼嫁判定」とあるはずだ）
「ピボットテーブルのフィールド」の上のペインにある「Q61aセックスの拒否」をドラッグして「行のボックス」に落とす

　一応これでピボットテーブルはできたのだが，先程述べたクロス表の原則と違う．「セックスの拒否」と「なし」はそのままでいいが，「鬼嫁」と「それ以外」が逆になっている．

　ピボットテーブルの「列ラベル」が押せるようになっていて，コレを押すとアイテムの並べ替えができる．「降順」を選ぶと「鬼嫁」と「それ以外」が逆転していい感じになる．

　何言ってるか分からないって？じゃあ下の動画を見てくれ．これでも分からんやつは俺にはどうしようもない．情弱として毟られる人生を送ってくれ．

　ところで，集計フィールドの意味は分かるな？例えば左上のフィールドは，列ラベルが「鬼嫁」かつ，行ラベルが「セックスの拒否」，に該当するレコード数が示されている．

　ここは例によって Microsoft の MDX という特殊なクエリで書かれた処理が走っているんだが，あいにく俺は詳しくない．SQL で書くなら下記のような処理だ．

SELECT SUM(CASE WHEN 列ラベル = '鬼嫁' AND 行ラベル = 'セックスの拒否' THEN 1 ELSE 0 END) FROM table;
SELECT SUM(CASE WHEN 列ラベル = 'それ以外' AND 行ラベル = 'セックスの拒否' THEN 1 ELSE 0 END) FROM table;
SELECT SUM(CASE WHEN 列ラベル = '鬼嫁' AND 行ラベル = 'なし' THEN 1 ELSE 0 END) FROM table;
SELECT SUM(CASE WHEN 列ラベル = 'それ以外' AND 行ラベル = 'なし' THEN 1 ELSE 0 END) FROM table;

　この作業を Q61a から Q61p まで 17 回繰り返す．ピボットテーブルが 17 個できるが，できる場所は「新規ワークシート」でもいいし，「既存のワークシート」からさっきのピボットテーブルの直下に追加していってもいい．俺は既存のワークシートに追加してくほうが楽だけど．理由は後で述べる．

　でだ．実は行アイテムを手動でソートするにはなぜか A 列を空けておかないといけない．A 列を選択して列を挿入しておく．ピボットテーブルの上でマウスカーソルを動かすとポインタの形が変わる．行アイテムの場合，ポインタが右向きの矢印に変わると行アイテムを選択できる．その状態でポインタを左へ動かしていくと，あるところで上下左右全方向に矢印のついた形に変わるので，その状態でアイテムをドラッグできる．適切な順番に入れ替えておく．

　正直なところ，Excel のピボットテーブルを舐めていた．かつてのピボットテーブルはそれはそれは遅くて重たくて使い物にならなかった．マシンのパワーが上がったのもあると思うけど，内部的にブラッシュアップされたんだろうな．今回のアンケート解析で 150 個以上のピボットテーブルを一つのファイルに作ったけど，ファイルサイズが 500 KB も行かないし動作も軽い．Excel，最強やんけ．たまにフリーズすることもあるけど．

統計解析

　さて，統計解析と聞いてもビビる必要はない．基本的にはセルに数式を入れていくだけである．R や Python やってる人ならコマンド一発だけど，このブログはそこまで到達してない人向けだから．

準備

　1 行目をタイトル行にする．「表示」タブの「ウィンドウ枠の固定」から「先頭行の固定」を選んでおく．下にスクロールしてもタイトル行が隠れないための措置．

　G1 セルに a, H1 セルに b, I1 セルに c, J1 セルに d とタイプする．以下，下表のように入力して欲しい．右方向に表が伸ばせないので下方向に延ばしてある．脳内で縦横変換してくれ．

　俺は親切だから解説してやるけど，|*| ってのは * の絶対値のことだ．E(*) ってのは期待値の頭文字．! は階乗の演算子だ．意味が分からんやつは後でググってくれ．

セルアドレス	入力内容
G1	a
H1	b
I1	c
J1	d
K1	\|ad-bc\|
L1	\|ad-bc\|-1/2
M1	{\|ad-bc\|-1/2}^2
N1	a+b
O1	c+d
P1	a+c
Q1	b+d
R1	N
S1	χ²statistics
T1	p<0.05
U1	p<0.01
V1	P
W1	E(a)
X1	E(b)
Y1	E(c)
Z1	E(d)
AA1	(a+b)!
AB1	(c+d)!
AC1	(a+c)!
AD1	(b+d)!
AE1	a!
AF1	b!
AG1	c!
AH1	d!
AI1	N!
AJ1	Fisher

数式の入力

　G4 セルをクリックする．半角で = とタイプする．続けてピボットテーブルの列「鬼嫁」と行「セックスの拒否」が交差するセル（C5 セルあたりか？）をクリックする．数式ペインには何やら長ったらしい式が入力されているが気にしないこと．

　そのままタブキーをタイプしてセルを右に移動する．半角で = とタイプし，ピボットテーブルの列「それ以外」と行「セックスの拒否」が交差するセル（D5 セルあたりか？）をクリックする．

　同様にタブキーで右のセルに移動し，= とタイプしてから列「鬼嫁」と行「なし」が交差するセル（C6 セルあたりか？）をクリックする．タブキーで右のセルに移動し，= とタイプして列「それ以外」と行「なし」が交差するセル（D6 あたりか？）をクリックする．

　これでクロス表の 4 成分が取り出せた．後は先程の数式に従って関数を入力していくだけだ．面倒だから表にするが，最初の一行だけは各自で入力してくれ．一度作れば後はコピペで構わないから．

セルアドレス	数式
G5
H5
I5
J5
K5	=ABS(G5J5-H5I5)
L5	=K5-0.5
M5	=L5*L5
N5	=G5+H5
O5	=I5+J5
P5	=G5+I5
Q5	=H5+J5
R5	=SUM(G5:J5)
S5	=M5R5/(N5O5P5Q5)
T5	5.024
U5	7.879
V5	=IF(S5>U5,”p<0.01",IF(S5>T5,”p<0.05","ns"))
W5	=N5*P5/R5
X5	=N5*Q5/R5
Y5	=O5*P5/R5
Z5	=O5*Q5/R5
AA5	=FACT(N5)
AB5	=FACT(O5)
AC5	=FACT(P5)
AD5	=FACT(Q5)
AE5	=FACT(G5)
AF5	=FACT(H5)
AG5	=FACT(I5)
AH5	=FACT(J5)
AI5	=FACT(R5)
AJ5	=AA5AB5AC5AD5/(AE5AF5AG5AH5*AI5)

数式のコピー

　で，数式のコピペだが，G 列から J 列までの 4 成分だけは = とタイプして直接該当するピボットテーブルのセルをクリックしてやらないといけない．面倒だがそういう仕様なのでポチポチクリックすること．

　17 個のピボットテーブル全部に対してそれぞれ 4 成分が取り出せたか？できたらやっと数式のコピペだ．K5 セルから AJ5 セルまでを選択してコピーし，取り出した成分の右のセルにペーストする．

　見やすくするために G 列から U 列までと W 列から AI 列までを非表示にしよう．ピボットテーブルの右側には V 列と AJ 列だけが見えているはずだ．

統計的有意なのはどれだ？

　V 列が p<0.05 とか p<0.01 とか表示されているところがあるか？

　あったか？おめでとう！それが統計的有意ということだ．あなたにもクロス集計から統計解析ができるようになった．データの山から意味のある提案ができるようになった．明日から会議での発言力，爆上がりだぜ！

　…まあ，実際はそんなことはないのだけれど，最後まで解いて背筋がゾクゾクしたっていう人，あなたにはデータ解析の才能がある．

カイ二乗検定は予選

　最初のとっかかりとして演習問題を解いてもらったけど，カイ二乗検定は単変量解析の一つで，実は多変量解析の候補者の選定，つまり予選なんだ．

　なら，どの候補者が一番強いのか？二番目は？三番目は？と疑問が出る．決戦は多変量解析で決まる．なんか，天下一武道会みたいだな．

　まだあるのかよ？そう思ったかもしれない．安心してくれ，世の中にはここまで出来るやつは殆どいない．ここまで来たら，太刀打ちできるのは大学で専門的にやってる連中だけだ．

まとめ

　日本人は数学嫌いと言われてるけど，高校の数学で統計をまともに教えられていないこともあって，統計的な思考のできる人が少ない．だからこそ，統計のできる人は重宝される．

　演習問題を解いてカイ二乗検定と Fisher 直接確率に触れてもらった．鬼嫁アンケートという身近な問題から学問的な話になってしまったが，日本を少しでも良くしたいという思いでこの記事を書いている．

　…ところで，アンケートの解析結果だが，もう少し待っててくれ（大汗）．