今回はExcelで偏差値を算出する方法を紹介します。
尚、Excelを使用して偏差値を計算するやり方をインターネットで検索すると、「STDEVP」関数を使用して簡単に「標準偏差」を求める手順が大半ですが、敢えてこの「STDEVP」関数を使わないやり方を紹介していきます。
便利な「STDEVP」関数を使わない理由
単純に私がExcelで偏差値を算出しようとした時点では、そんな便利な関数があるのを知らなかっただけです。
ただ、関数に頼らず計算できた方が数字にも強くなり数字の成り立ちにも理解が深まるので、需要があるかは置いておいて、敢えて少しだけ面倒な手順で紹介します。
「偏差値」を算出する具体的な手順と計算式
当項では「偏差値」を算出するための具体的な手順と計算式を紹介していきます。
まずは紹介する手順で実際に偏差値を算出していただき、それをご自身の実際のExcelファイルのデータレイアウトに合わせて調整していただくと良いかと思います。
「偏差値」算出対象の値を書き出す
まずはシートのA列で偏差値の算出対象の値を書き出します。
「偏差値」算出計算式の完成形シートとその詳細説明
以下の画像が「偏差値」を算出する際に必要な計算式をすべて設定し、できあがった状態のシートです。
この画像に振ってある番号ごとに設定する計算式などの説明をしていきます。
① 「偏差値」算出方法の値を書き出す
偏差値の算出対象の値を書き込みます。
今回のサンプルではA列を使用します。
② 「値の合計値」を算出
「値の合計値」を算出します。
複数のセルの合計値を算出するには「SUM」関数を使用します。
今回のサンプルではセル”E2″を使用します。
③ 「値のデータ個数」を指定
「値のデータ個数」を指定します。
今回のサンプルではセル”H2″を使用します。
今回は手入力で 10 と入れていますが、実際に表で自動計算させる場合は「COUNT」関数などで値を自動算出させる方が望ましいです。
④ 「値の平均値」を算出
「値の平均値」を算出します。
値の平均値を算出するには、値の「合計値」を値の「個数」で割ると算出されます。
今回のサンプルではセル”F2″を使用します。
⑤ 「値と平均の差の二乗」を算出
「値と平均の差の二乗」を算出します。
この値をもとに⑥の「標準偏差」を算出します。
この値はA列の値から④の値を引き、その計算結果を二乗して算出します。
今回のサンプルではセル”B2″に計算式を埋め込み、その計算式を埋め込んだセルを”B3″から”B11″までのセルにすべてコピーします。
計算式には絶対参照($記号)が指定されており、絶対参照で指定されたセルの位置はコピーしても変更されません。
⑥ 「標準偏差」を算出
「標準偏差」を算出します。
この値はB列の値をすべて足して、その算出結果に対してセル”H2″の値の「個数」を割ります。
その算出した値に対して「SQRT」関数を使用して平方根を算出します。
今回のサンプルではセル”G2″を使用します。
⑦ 「偏差値」を算出
「偏差値」を算出します。
この値はA列の値から④の値を引き、その値を⑥の標準偏差で割ります。
更に算出された値に対して10を掛けて、更にその値に対して50を足します。
このままだと小数値が発生するので、「ROUND」関数で小数を四捨五入します。
今回のサンプルではセル”D2″に計算式を埋め込み、その計算式を埋め込んだセルを”D3″から”D11″までのセルにすべてコピーします。
計算式には④と⑥のセルへの絶対参照を指定しています。
これで完成です。
最後に
今回はExcelで「STDEVP」関数を使わずに偏差値を算出する方法を紹介しました。
記事にするにあたって「SQRT」関数も使わずに作ろうかと思いましたが、自前の計算式で算出するのは大変そうなので辞めました。
偏差値は試験の点数に対して算出する使い方が一般的ですが、社会人であれば、売り上げに対して偏差値を算出して個々の売り上げの数字の偏差具合を分かりやすく表現したり、試験の点数に対して算出する以外にもデータ集計やデータ分析の業務でも活用できます。
是非色んな集計に対して偏差値も使用してみてください。
それでは、今回も読んでいただきありがとうございました。
