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

公開: 2022-01-29

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

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

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

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

明確にするための例を次に示します。異なる教師によって教えられた2人の代数の生徒のテスト結果を比較したいとします。最初の生徒が1つのクラスの最終試験で95％を取得し、他のクラスの生徒が87％を獲得したことを知っています。

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

必要な最初の統計値は「平均」であり、Excelの「平均」関数がその値を計算します。セル範囲内のすべての値を単純に合計し、その合計を数値を含むセルの数で除算します（空白のセルは無視されます）。

必要なもう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」関数の結果を保存するセルを選択します。

次の数式を入力してEnterキーを押すか、[数式]メニューを使用します。

 = AVERAGE（E2：E13）

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

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

次に「OK」を押します。

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

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

 = STDEV.S（E3：E12）

「数式」メニューから機能にアクセスするには、「その他の機能」ドロップダウンを選択し、「統計」オプションを選択し、少し下にスクロールして、「STDEV.S」コマンドをクリックします。

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

次に「OK」を押します。

次に、「STDEV.P」関数を使用して標準偏差を計算します。結果を保存するセルを選択します。

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

= STDEV.P（E3：E12）

「数式」メニューから関数にアクセスするには、「その他の関数」ドロップダウンを選択し、「統計」オプションを選択して少し下にスクロールし、「STDEV.P」数式をクリックします。

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

次に「OK」を押します。

データの平均と標準偏差を計算したので、Zスコアを計算するために必要なものはすべて揃っています。「AVERAGE」および「STDEV.S」または「STDEV.P」関数の結果を含むセルを参照する簡単な数式を使用できます。

「Zスコア」列の最初のセルを選択します。この例では「STDEV.S」関数の結果を使用しますが、「STDEV.P」の結果を使用することもできます。

次の数式を入力してEnterキーを押します。

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

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

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

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

残りの値のZスコアを計算してみましょう。数式を含むセルから始まる「Zスコア」列全体を強調表示します。

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

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

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

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

これが「STDEV.S」関数を使用したものです。

 =（Value-AVERAGE（Values））/ STDEV.S（Values）

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

 =（Value-AVERAGE（Values））/ STDEV.P（Values）

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

データセットが大きい場合は、「AVERAGE」および「STDEV.S」または「STDEV.P」関数の結果を毎回計算しないため、ヘルパーセルを使用する方が効率的であり、プロセッサリソースと結果の計算にかかる時間を短縮します。

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