Microsoft Excelを使用してZスコアを計算する方法

Zスコアは、特定の値がデータセット全体の平均からどれだけの標準偏差があるかを示す統計値です。 AVERAGEとSTDEV.SまたはSTDEV.Pの式を使用してデータの平均と標準偏差を計算し、それらの結果を使用して各値のZスコアを決定できます。.

Zスコアとは何ですか、またAVERAGE、STDEV.S、およびSTDEV.P関数は何をしますか。?

Zスコアは、2つの異なるデータセットの値を比較する簡単な方法です。それはデータ点が存在する平均からの標準偏差の数として定義されます。一般式は次のようになります。

=（DataPoint-AVERAGE（DataSet））/ STDEV（DataSet）

これは明確にするための例です。異なる教師によって教えられた2人のAlgebraの生徒のテスト結果を比較したいとしましょう。あなたは最初の生徒があるクラスの期末試験で95％を獲得し、他のクラスの生徒が87％を獲得したことを知っています.

一見したところ、95％の成績がより印象的ですが、2番目のクラスの先生がもっと難しい試験をした場合はどうなりますか？各クラスの平均スコアと各クラスのスコアの標準偏差に基づいて、各生徒のスコアのZスコアを計算できます。 2人の学生のZスコアを比較すると、87％のスコアを持つ学生の方が、残りのクラスの98％のスコアを持つ学生よりも、他のクラスの学生と比べて優れていることがわかります。.

最初に必要な統計値は 'mean'で、Excelの "AVERAGE"関数はその値を計算します。セル範囲内のすべての値を単純に合計し、その合計を数値を含むセルの数で除算します（空白のセルは無視されます）。.

もう1つ必要な統計値は「標準偏差」です。Excelには、標準偏差をわずかに異なる方法で計算するための2つの異なる関数があります。.

以前のバージョンのExcelには「STDEV」関数しかありませんでした。これは、データを母集団の「サンプル」として扱いながら、標準偏差を計算します。 Excel 2010はこれを標準偏差を計算する2つの関数に分けました。

STDEV.S： この機能は前の“ STDEV”機能と同じです。データを母集団の「サンプル」として扱いながら、標準偏差を計算します。集団のサンプルは、研究プロジェクトのために集められた特定の蚊や衝突安全性テストのために取っておかれた車のようなものかもしれません.
STDEV.P： この関数は、データを母集団全体として扱いながら、標準偏差を計算します。全人口は地球上のすべての蚊のようなものか、または特定のモデルの生産走行におけるすべての車のようなものになるでしょう。.

どちらを選択するかは、データセットに基づいています。通常、差は小さくなりますが、 "STDEV.P"関数の結果は、同じデータセットの "STDEV.S"関数の結果よりも常に小さくなります。データの変動が大きいと仮定することは、より保守的なアプローチです。.

例を見てみましょう

この例では、 "AVERAGE"、 "STDEV.S"、および "STDEV.P"関数の結果を格納するための2つの列（ "Values"と "Z-Score"）と3つの "helper"セルがあります。「値」列には500を中心とした10個の乱数が含まれています。「Zスコア」列には、「ヘルパー」セルに格納されている結果を使用してZスコアを計算します。.

まず、 "AVERAGE"関数を使って値の平均を計算します。 "AVERAGE"機能の結果を保存するセルを選択してください.

次の式を入力してを押す入る -または - 「数式」メニューを使う.

= AVERAGE（E2：E13）

[数式]メニューから関数にアクセスするには、[その他の関数]ドロップダウンを選択し、[統計]オプションを選択してから[平均]をクリックします。

[関数の引数]ウィンドウで、[値]列のすべてのセルを[数値1]フィールドの入力として選択します。「Number2」フィールドについて心配する必要はありません。.

それでは「OK」を押してください。

次に、 "STDEV.S"または "STDEV.P"関数を使用して値の標準偏差を計算する必要があります。この例では、「STDEV.S」から始めて、両方の値を計算する方法を示します。結果が格納されるセルを選択します.

"STDEV.S"機能を使用して標準偏差を計算するには、この式を入力してEnterキーを押します（または[式]メニューからアクセスします）。.

= STDEV.S（E3：E12）

“ Formulas”メニューから関数にアクセスするには、“ More Functions”ドロップダウンを選択し、“ Statistical”オプションを選択し、少し下にスクロールしてから“ STDEV.S”コマンドをクリックしてください。.

[関数の引数]ウィンドウで、[値]列のすべてのセルを[数値1]フィールドの入力として選択します。ここで「Number2」フィールドについて心配する必要はありません。.

それでは「OK」を押してください。

次に、 "STDEV.P"関数を使って標準偏差を計算します。結果が保存されるセルを選択してください.

"STDEV.P"関数を使用して標準偏差を計算するには、この式を入力してEnterキーを押します（または[式]メニューからアクセスします）。.

= STDEV.P（E3：E12）

“ Formulas”メニューから関数にアクセスするには、“ More Functions”ドロップダウンを選択し、“ Statistical”オプションを選択し、少し下にスクロールしてから“ STDEV.P”式をクリックしてください。.

[関数の引数]ウィンドウで、[値]列のすべてのセルを[数値1]フィールドの入力として選択します。繰り返しますが、「Number2」フィールドについて心配する必要はありません。.

それでは「OK」を押してください。

データの平均と標準偏差を計算したので、Zスコアを計算するために必要な作業はすべて完了しました。 "AVERAGE"と "STDEV.S"または "STDEV.P"関数の結果を含むセルを参照する単純な式を使用できます。.

「Z-Score」列の最初のセルを選択してください。この例では "STDEV.S"関数の結果を使用しますが、 "STDEV.P"の結果も使用できます。

次の式を入力してEnterキーを押してください。

=（E3- $ G $ 3）/ $ H $ 3

代わりに、入力する代わりに次の手順を使用して数式を入力することもできます。

セルF3をクリックして入力 =（
セルE3を選択します。（あなたは押すことができます 左矢印キー 1回またはマウスを使う
マイナス記号を入力 -
セルG3を選択してを押す F4 セルへの「絶対」参照を作成するための「$」文字の追加（「G3」>「$G$3 ">“ G$3 ">"$G3 ">" G3 "を押し続ける場合 F4）
タイプ ）/
セルH3（または「STDEV.P」を使用している場合はI3）を選択してを押します。 F4 2つの「$」文字を追加する.
Enterを押す

Zスコアは最初の値に対して計算されています。それは平均以下の0.15945標準偏差です。結果を確認するには、標準偏差にこの結果（6.271629 * -0.15945）を掛けて、結果が値と平均の差（499-500）と等しいことを確認します。両方の結果は等しいので、値は意味があります.

残りの値のZスコアを計算しましょう。数式を含むセルで始まる 'Z-Score'列全体を強調表示します.

Ctrl + Dを押すと、一番上のセルの数式が他のすべての選択されたセルにコピーされます。.

今すぐ数式がすべてのセルに「塗り潰され」、そして「$」文字のため、それぞれが常に正しい「AVERAGE」と「STDEV.S」または「STDEV.P」セルを参照します。エラーが発生した場合は、戻って入力した式に「$」文字が含まれていることを確認してください。.

「ヘルパー」セルを使用せずにZスコアを計算する

「AVERAGE」、「STDEV.S」、および「STDEV.P」関数の結果を格納しているヘルパーセルと同様に、ヘルパーセルに結果が格納されます。それらは便利ですが、必ずしも必要というわけではありません。 Zスコアを計算するときは、代わりに次の一般式を使用してそれらを完全にスキップできます。.

これは“ STDEV.S”関数を使ったものです。

=（値 - 平均（値））/ STDEV.S（値）

そして「STEV.P」機能を使用するもの：

=（値 - 平均（値））/ STDEV.P（値）

関数の「値」のセル範囲を入力するときは、絶対値参照（F4を使用して「$」）を追加して、「塗りつぶし」したときに異なる範囲の平均または標準偏差が計算されないようにしてください。すべての数式のセル数.

大きなデータセットがある場合、毎回 "AVERAGE"および "STDEV.S"または "STDEV.P"関数の結果を計算しないので、ヘルパーセルを使用するほうが効率的です。結果の計算にかかる時間を短縮する.

また、“ $ G $ 3”は“ AVERAGE（$ E $ 3：$ E $ 12）”よりも格納に必要なバイト数とロードに必要なRAMが少なくて済みます。標準の32ビット版のExcelは2GBのRAMに制限されているため、これは重要です（64ビット版では使用できるRAMの量に制限はありません）。.