Excel（エクセル）での偏差値の求め方｜使用する計算式や関数を説明

Introduction

Ver.　all　2013　2016　2019　2021　365

Excel（エクセル）で偏差値を求める時に使用する計算式や関数を説明します。偏差値を求めるには、平均や標準偏差などを組み合わせる必要があります。

残念ながら、関数一発で簡単にという訳にはいきません・・・。

できるだけ公式や難しい話は抜きにして、文系の方でも大丈夫なように簡単に説明します。

 

 

この計算式で偏差値を出すことができます。

頑張れば、「平均」や「標準偏差」を表示する場所（セル）を用意せずに終わらせることも可能ですが、計算式が長くなり難しくなります。

ここでは、

のように、「平均」や「標準偏差」の項目を用意した状態で説明していきます。

 

偏差値を出してみる

まず、下の表を見てください。H列の「偏差値」を出してみようと思います。

偏差値を出す計算式は先ほど書いたように

偏差値＝（偏差値を求めるセル－偏差値を求める全データの平均）÷標準偏差×10+50

ですが、今回の表の項目名で置き換えると、

偏差値＝（合計－平均）÷標準偏差×10+50

になります。

まず、「平均」「標準偏差」などの場所を表に用意して・・・。

では、やってみましょう。

平均と標準偏差を求める

最初に合計と平均を出しておきましょう。SUM関数とAVERAGE関数ですね。

※関数の使い方が不安な方は、Excel（エクセル）での関数の使い方　②｜関数の入力方法をご覧ください。

では、次に標準偏差を出します。

• 標準偏差・・・それぞれのデータの散らばりの分布を示す値

標準偏差を出すには面倒な計算が必要なのですが、エクセルでは関数が用意されています。

---

STDEVP関数・・・引数を母集団全体であると見なして、母集団の標準偏差を計算する

STDEVP（数値1,［数値2］,…）

• 数値1・・・標準偏差を出したい場所を指定。
• 数値2・・・［省略可能］母集団全体に対応する数値引数を 2 ～ 255 個まで指定できる。

---

似た関数にSTDEV関数があります。

違いは

• STDEVP関数・・・母集団全体から標準偏差を計算する
• STDEV関数・・・母集団を標本として予測した標準偏差を計算する

 

 

簡単にいうと、STDEV関数は、選択した範囲のデータをサンプルとして扱い、予測も含めた標準偏差を計算するという事。

テストの点数のように、偏差値を出すデータが全て揃って予測が必要無い場合は、STDEVP関数を使用しましょう。

 

では、関数を入力してみます。

「G12」をクリックして、［関数の挿入］画面を表示します。

［関数の検索］画面に「STDEVP」と入力して検索するか、［関数の分類］を［すべて表示］に切り替えて、「STDEVP」を選択して［OK］をクリックします。

ここでポイント。エクセル2010から、内容は同じなのですが関数の名称が変わっています。

STDEVP　→　STDEV.P

つい新しい、「.」が付いている方を選びたくなりますが、2007以前のエクセルとの互換性も考えて「STDEVP」を使用する方が良いでしょう。

ちなみに、2010以降の［関数の分類］の［統計］から選ぼうとすると「STDEVP」が表示されないようなので注意してください。

さて、話を戻しましょう。

引数の入力は簡単です。標準偏差を計算したい範囲を選択するだけ。

［OK］をクリックすると計算結果が表示されます。

やっと下準備完了です。

偏差値を求める

ここからが偏差値を出す計算式を作る作業になります。もう一度計算式を確認しましょう。

偏差値＝（合計－平均）÷標準偏差×10+50

まずは、（合計－平均）が必要ですね。

偏差値を表示したいセル「H3」に「＝」を自分で入力。

括弧を入力したら、マウスを持って、「G3」をクリックして「－」は自分で入力、「G11」をクリックして、すぐに【F4】キーを押して「G11」を絶対参照にします。そして括弧閉じ。

「G11」の「平均」を絶対参照にする理由は、後でオートフィル機能を使用して下にコピーする時に、固定しておきたいからです。

※絶対参照の説明が必要な方は、Excel（エクセル）で計算する方法　③｜絶対参照の使い方をご覧ください。

次に

÷標準偏差

です。「/」スラッシュを自分で入力して「G12]をクリック。すぐに【F4】キーを押して絶対参照にするのを忘れずに。理由は先ほどと同じです。

最後に

×10+50

これは、特に問題ありませんね。掛け算の「＊」アスタリスクを入力して、「10」続いて「+」「50」を入力。【Enter】を押すと・・・

結果が表示されます。

オートフィル機能を使用して、下にコピーすると全員の偏差値が表示されます。

数値がおかしい場合やエラー表示になる場合は、絶対参照の設定忘れが多いと思います。

 

今回は説明のために、「平均」「標準偏差」の場所を用意しましたが、それらの数式を偏差値を算出するセルに入れ込むことで計算式を作ることもできます。

しかし、計算式は長く複雑になります。今回の場合だと、「H3」に入れる式は、

=(G3-AVERAGE($G$3:$G$10))/STDEVP($G$3:$G$10)*10+50

になります。

うーーーん、何をやっているか分からない。という方は下のリンク先を見ていただくと参考になると思います。

Excel（エクセル）での関数の使い方　⑤｜複数の関数を入れ子にして使う方法（ネストの使い方）

Excel（エクセル）で関数の中に関数を使う（入れ子にする）ことをネストとよびます。このネストの使い方を説明します。IF関数と組み合わせて使用することが多いですね。

prau-pc.jp

まとめ

偏差値の出し方でした。関数1つで「ぱっ」と表示されるわけではないので、ちょっと大変ですね。

また、標準偏差という聞きなれない言葉も出てきます。

エクセルに用意されている標準偏差の出し方には幾つかありますので、興味のある方は調べてみるといいかもしれません。