Excelノート
統計検定データサイエンス基礎のためのExcelの使い方

1-1. Excelで基本統計量を計算しよう

2021年7月から、統計検定の新しい試験である「データサイエンス基礎」が始まりました。「データサイエンス基礎」は、Excelを使いながら実際のデータを解析する能力を評価するための試験です。このExcelノートではデータ解析を行う際によく使うエクセル関数やエクセルの機能について説明していきます。

■基本統計量に関するExcel関数一覧

関数	使い方（A1:A3のデータを用いる場合）	説明
SUM	SUM(A1:A3)	合計値
AVERAGE	AVERAGE(A1:A3)	平均値
VAR.P VARP	VAR.P(A1:A3) VARP(A1:A3)	母集団における分散標本分散
VAR.S VAR	VAR.S(A1:A3) VAR(A1:A3)	母集団から抽出した標本における分散不偏分散
STDEV.P STDEVP	STDEV.P(A1:A3) STDEVP(A1:A3)	標本分散の平方根
STDEV.S STDEV	STDEV.S(A1:A3) STDEV(A1:A3)	不偏分散の平方根
STANDARDIZE	STANDARDIZE(値,平均値,標準偏差)	標準化平均が0、分散が1となるようにデータを変換
MAX	MAX(A1:A3)	最大
MIN	MIN(A1:A3)	最小
MEDIAN	MEDIAN(A1:A3)	中央値
QUARTILE.INC QUARTILE	QUARTILE.INC(A1:A3,戻り値)	四分位数・戻り値の設定　0：最小値　1：第1四分位数　2：中央値　3：第3四分位数　4：最大値

■Excel関数の使い方

次のデータはあるクラスの生徒30人分の数学の試験結果（100点満点）です。このデータを使って、成績が一番良かった生徒の偏差値を求めてみます。

データの平均値と標準偏差をを求める

30人分のデータの下に「平均」と「標準偏差」という項目を作っておきます。

平均値を出力させたいセルを選択し、「=AVERAGE(B2:B31)」と入力します。もしくは「=AVERAGE(」と打ったあとに、データの範囲をドラッグで選択してもOKです。

Enterを押すと、結果が表示されます。

同様にして、STDEV.P関数もしくはSTDEVP関数を使って標準偏差を算出します。このデータは標本データではないので、標本分散（母集団の分散）の平方根として算出します。

すべてのデータを標準化する

STANDARDIZE関数を使って、B2の値を標準化します。標準化するときには1で算出した平均値（B33）と標準偏差の値（B34）を使います。平均値と標準偏差の値は固定しておきたいので、「$」マークを付けてそれぞれ「$B$33」「$B$34」と記入します。

C2のセルの右下部分にカーソルを持っていくと「+」マークが現れるので、ダブルクリックします。すると、30個すべてのデータに対して標準化した値が自動で入力されます（オートフィル機能といいます）。このときに、常に同じ平均値（B33）と標準偏差（B34）を使いたかったため（固定しておきたかったため）、上で「$」マークを付けるという作業を行いました。

標準化した値に10をかけて50を足して偏差値を算出する

掛け算は「*」、足し算は「+」で表します。D2のセルだけ偏差値を算出し、オートフィル機能を使って残りの値も自動で入力します。

偏差値の最大値を求める

MAX関数を使って最大値を求めます。最大値を出力させたいセルを選択し、「=MAX(D2:D31)」と入力します。

ここまでの作業で、成績が一番良かった生徒の偏差値が「65.3」であることが分かりました。

「思ったより偏差値が低いな」と思った方は鋭いです。この例題では30人分のデータを使って偏差値を算出しています。普段、偏差値を算出するときは、学校全体や日本全体のデータを使うためサンプルサイズがかなり大きいです。中心極限定理によりサンプルサイズが大きいほど分散は小さくなるので、結果として得られる偏差値がこの例題で算出した値よりももっとばらつくことが予想されます（もちろん試験の難易度や点数のばらつきによっても異なりますが）。