分析で役立つスプレッドシートの使い方 | ヒストグラム・代表値・クロス集計・標準偏差・P値

取り上げた本のワークショップではエクセルを利用していますが、スプレッドシートでも代用できます。

今回は、ヒストグラムや標準偏差の算出など、スプレッドシートで使える具体的な分析方法についてご紹介します。

データの事前チェック・データの可視化

外れ値のやデータの傾向を把握するときに利用します。

データが信頼できるかどうか、最適な分析方法は何かを探る上でも大事なステップです。

“データの可視化と最大値・最小値の計算は、どんな場合でも必ず最初に行う”ようにと本に書かれています。

1つの指標の分布がわかります。

指標の範囲を選択し、挿入 > グラフを選択します。グラフの種類からヒストグラムを選択します。

カスタマイズのヒストグラムでは、アイテムの分割表示やバケットサイズ、異常値のパーセンタイルが設定できます。

2つの指標の関係がわかります。

指標の範囲を選択し、挿入 > グラフを選択します。グラフの種類から散布図を選択し、X軸と系列を設定します。

カスタマイズの系列では、誤差範囲やデータラベル、ドレンドラインなどの設定ができます。

種類	説明	関数
最大値	データの中で最も大きい値	=MAX(範囲)
最小値	データの中で最も小さい値	=MIN(範囲)

代表値やクロス集計を利用して、膨大なデータから特徴を見出します。

種類	説明*	有効条件*	関数
平均値	全ての値を合計して、データの数で割ったもの	・本質的に数量の情報である・分布の山が1つ・極端に非対称な分布ではない・極端な外れ値がない、または少ない	=AVERAGE(範囲)
中央値	データを大きい順（小さい順）に並べたときに、真ん中の順位に来るものの値	・ある値が真ん中より上か下かを知りたい場合・平均値より中央値の方が分布の山に近い場合	=MEDIAN(範囲)
最頻値	該当するデータ数が最も多い値	・本質的に名前や言葉を指す分類データ・数値データを等間隔に区切った分類データ	=MODE(範囲)

分布がふたこぶなど代表値が有効でない場合や、複数ある要因が数字にどう影響しているか当たりをつけるときに利用します。

データの範囲を選択し、挿入 > ピボットテーブルを選択します。テーブルの挿入先を選択して作成をクリックします。

行、列に要因を、値に指標を設定します。値の集計には目的にあうものを選択します。

クロス集計をする前に、欠損値や外れ値を確認してデータをクレンジングするステップも大切です。

標準偏差は各データと平均値の乖離からデータのばらつきを表し、平均値の信頼性の判断やリスクの可視化に役立ちます。

危険率(P値)はグループ間で本当に差があると言えるかを検証するときに利用します。

種類	説明	関数
標本標準偏差	集めたデータ自体のばらつきをみる	=STDEVP(範囲)
母標準偏差	集めたデータから全体のばらつきを推定する	=STDEV(範囲)

ピボットテーブルでも算出できます。

比較するデータの条件によって、算出方法が異なります。

	対応のあるT検定	スチューデントのT検定	ウェルチのT検定	Z検定
対応比較	対応あり同じ個体同士の比較	対応なし異なる個体同士の比較	対応なし異なる個体同士の比較	対応なし異なる個体同士の比較
母分散	ー	母分散が未知	母分散が未知	母分散が既知
分散	ー	分散が等しい	分散が等しくない	ー
例	施策 X の実施”前後”の A さんの売上の差	施策 “X と Y” の売上の差	施策 “X と Y” の売上の差	Xブログの過去平均 PV と直近1ヶ月の平均 PV の差
関数【片側検定】	=TTEST(範囲1,範囲2,1,1)	=TTEST(範囲1,範囲2,1,2)	=TTEST(範囲1,範囲2,1,3)	=ZTEST(範囲, 母集団の平均値, [標準偏差])
関数【両側検定】	=TTEST(範囲1,範囲2,2,1)	=TTEST(範囲1,範囲2,2,2)	=TTEST(範囲1,範囲2,2,3)	=2 * MIN(ZTEST(範囲, 母集団の平均値, [標準偏差]), 1 – ZTEST(範囲, 母集団の平均値, [標準偏差]))

差があるかどうかを知りたい場合は両側検定、差の大きさ（小ささ）を知りたい場合は片側検定を用います。

対応のないT検定をするときに必要な分散が等しいかどうかは、 F検定（=FTEST(範囲1, 範囲2)）で算出します。

スプレッドシートで使える分析方法をご紹介しました。関数はエクセルとほぼ同じ感覚で使えます。

この記事が分析のお役に立てたら幸いです！