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

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

■Excel関数の使い方

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

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

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



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



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



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


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

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



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


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

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



4. 偏差値の最大値を求める

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




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

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