エクセルで複数の数値を比較するとき、点数や売上額そのものではなく、平均からどれほど離れているかを基準に判断したい場面があります。
このときに役立つのが標準化です。
標準化得点を使うと、単位や平均値が異なるデータでも、同じものさしで位置づけを確認できます。
たとえばテストの得点、店舗別の売上、作業時間、アンケート結果などを比較するときに便利でしょう。
標準化得点は、元の値から平均値を引き、標準偏差で割って求めます。
エクセルではSTANDARDIZE関数、AVERAGE関数、STDEV.P関数またはSTDEV.S関数を組み合わせることで計算できます。
この記事では、1行目に見出しがある表を例にして、エクセルで標準化する方法、関数の入力手順、標準偏差の選び方、結果の読み取り方までを解説します。
数式を入力する前に、標準化は順位を並べるだけの操作ではなく、平均を基準にデータの距離を数値化する考え方であることを押さえておきましょう。
エクセルで標準化得点を計算する方法
それではまず、エクセルで標準化得点を関数で求める基本手順について解説していきます。
| 氏名 | テスト得点 | 標準化得点 |
|---|---|---|
| 佐藤 | 52 | |
| 鈴木 | 68 | |
| 高橋 | 75 | |
| 田中 | 83 |
STANDARDIZE関数の入力
まず、標準化得点を表示したいC2セルを選択します。
この例ではB列に元のテスト得点があり、B2からB11までに対象データが入力されているものとします。
STANDARDIZE関数は、値、平均、標準偏差の順番で指定する関数です。
=STANDARDIZE(B2,AVERAGE($B$2:$B$11),STDEV.P($B$2:$B$11))
上の数式をC2セルに入力してEnterキーを押すと、B2セルの得点を標準化した値が表示されます。
B2だけは相対参照にし、平均値と標準偏差を求める範囲はドル記号付きの絶対参照にすることが大切です。
絶対参照にしておけば、数式を下方向へコピーしても計算対象の全体範囲がずれません。
標準化得点が正の値なら平均より上、負の値なら平均より下という見方になります。
数値が0に近い場合は、全体の平均付近に位置している状態です。
【操作のポイント】STANDARDIZE関数では、元データのセルだけを行ごとに変化させ、平均と標準偏差の範囲は固定します。
オートフィルによる数式のコピー
C2セルに数式を入力できたら、セル右下に表示される小さな四角形を使って下の行へコピーしましょう。
この四角形はフィルハンドルと呼ばれます。
C2セルを選択し、右下のフィルハンドルを最終行までドラッグすると、各行の得点に対応した標準化得点が自動で計算されます。
データが連続している場合は、フィルハンドルをダブルクリックする方法でも構いません。
コピー後のC3セルではB3、C4セルではB4というように、先頭の値だけが自動的に切り替わります。
一方で、AVERAGE関数とSTDEV.P関数の範囲は$B$2:$B$11のまま維持されます。
数式をコピーした後は、先頭行と途中の行をクリックして、参照範囲が変わっていないか確認する習慣をつけると安心です。
数式が表示されず数値だけを確認したい場合は、ホームタブの表示形式で小数点以下の桁数を調整できます。
標準化得点は小数になることが多いため、小数第2位または小数第3位まで表示すると比較しやすくなります。
【操作のポイント】オートフィルの前に、平均と標準偏差の参照範囲にドル記号が付いているか数式バーで確認しましょう。
計算結果の基本的な見方
標準化得点は、平均との差を標準偏差というばらつきの尺度で割った値です。
たとえば結果が1.20なら、平均より標準偏差1.20個分だけ高い位置にあることを示します。
結果が-0.80なら、平均より標準偏差0.80個分だけ低い位置です。
標準化得点の符号は平均より上か下かを表し、絶対値は平均との差の大きさを表します。
同じ表の中では、標準化得点が大きい人ほど平均から上に離れています。
ただし、標準化得点が1.00であることは得点が100点であることを意味しません。
元の得点とは別の尺度に変換された結果である点に注意が必要です。
【操作のポイント】0、1、-1を基準に結果を見ると、平均付近か、平均との差が大きいかを判断しやすくなります。
標準化得点の計算式と意味
続いては、標準化得点の計算式と各要素の意味を確認していきます。
| 項目 | 例 | 役割 |
|---|---|---|
| 元の値 | 75 | 比較したい個別データ |
| 平均値 | 68 | 全体の中心 |
| 標準偏差 | 10 | データのばらつき |
平均値との差を使う理由
標準化の出発点は、各データが平均値からどれだけ離れているかを求めることです。
75点で平均が68点なら、平均との差は7点になります。
しかし、7点差だけでは、その差が大きいのか小さいのかを正しく判断できません。
全員の得点が65点から70点の範囲に集まるテストなら7点差は大きく、40点から100点まで広がるテストなら相対的に小さい可能性があります。
そこで、平均との差を標準偏差で割ります。
標準化得点 ＝ 元の値 − 平均値 を標準偏差で割った値
75、68、10の例では、75から68を引いた7を10で割るため、標準化得点は0.7です。
平均との差だけではなく、データ全体の散らばりも同時に反映できる点が標準化の重要な特徴です。
【操作のポイント】標準化は元の点差を消す処理ではなく、全体のばらつきに対する相対的な距離へ変換する処理です。
平均がゼロになる性質
同じ範囲のデータをすべて標準化すると、標準化得点の平均は原則として0になります。
これは、平均より上の値と平均より下の値が釣り合うためです。
また、母集団標準偏差を使って標準化した場合、標準化後のデータの標準偏差は1になります。
この性質があるため、売上金額と作業時間のように単位が異なる数値でも、相対的な位置を比較できます。
たとえば売上が平均より0.9標準偏差高く、作業時間が平均より1.3標準偏差短いなら、後者のほうが平均との差は大きいと読めます。
標準化後の数値は元の単位を持たないため、円や分という単位をまたいだ比較が可能になります。
ただし、比較するデータの意味や集計条件が極端に異なる場合は、数値だけで評価を決めないことも重要です。
【操作のポイント】標準化後は平均が0付近になるため、正負と絶対値を中心に読み取ります。
偏差値との違い
学校の成績などでよく使われる偏差値は、標準化得点を見やすい尺度に変換した数値です。
一般的な偏差値は、標準化得点に10を掛けて50を足して求めます。
偏差値 ＝ 標準化得点 × 10 ＋ 50
標準化得点が0なら偏差値は50です。
標準化得点が1なら偏差値は60、-1なら偏差値は40になります。
標準化得点は平均0、標準偏差1を基準にし、偏差値は平均50、標準偏差10を基準にする違いがあります。
社内データの分析では標準化得点のまま扱っても問題ありません。
一方で、統計に慣れていない人へ結果を共有するなら、偏差値に変換したほうが直感的に伝わる場合もあります。
【操作のポイント】標準化得点と偏差値は比較の考え方が同じであり、表示する尺度だけが異なります。
STDEV.P関数とSTDEV.S関数の選び方
続いては、標準偏差を求める関数の選び方について確認していきます。
| 状況 | 使用する関数 | 考え方 |
|---|---|---|
| 対象者全員のデータ | STDEV.P | 母集団として扱う |
| 一部を抽出したデータ | STDEV.S | 標本として扱う |
全件データに適したSTDEV.P関数
STDEV.P関数は、手元にあるデータが対象全体を表しているときに使います。
たとえばクラス全員のテスト得点、全店舗の月次売上、所属部門の全従業員の処理件数などが該当します。
今回のように、表にある全員について相対的な位置を計算したい場合は、STDEV.P関数を使うのが自然です。
=STDEV.P($B$2:$B$11)
この関数は、指定範囲に含まれる全データを母集団として標準偏差を計算します。
表に載っている全件を評価対象にするなら、原則としてSTDEV.P関数を検討すると理解しておくと選びやすくなります。
以前のエクセルではSTDEVP関数が使われることもありましたが、現在はSTDEV.P関数を使う方法がわかりやすいでしょう。
【操作のポイント】全員分や全店舗分など、比較対象の全件が表にある場合はSTDEV.P関数を使用します。
抽出データに適したSTDEV.S関数
STDEV.S関数は、より大きな集団の一部だけを抜き出して調査した場合に使います。
たとえば全国の購入者全体を推定するために、100人だけを無作為に抽出したアンケート結果などが該当します。
抽出した値だけで母集団のばらつきを推定するため、STDEV.P関数とは計算上の補正が異なります。
=STANDARDIZE(B2,AVERAGE($B$2:$B$11),STDEV.S($B$2:$B$11))
STDEV.S関数を使った標準化得点も、同一の表における比較には利用できます。
ただし、STDEV.P関数を使った場合と数値が少し変わることがあります。
どちらの関数が正しいかは数式の形ではなく、集めたデータを全体とみなすか、一部の標本とみなすかで決まります。
レポートや資料では、使用した標準偏差の関数名も記載しておくと、後から検証しやすくなります。
【操作のポイント】データの抽出条件が不明なときは、集計対象が全件かサンプルかを先に確認してから関数を決めます。
空白セルと文字列の扱い
AVERAGE関数、STDEV.P関数、STDEV.S関数では、参照範囲にある空白セルや文字列の扱いにも注意が必要です。
通常、空白セルや文字列は計算対象から除外されます。
そのため、未入力の行が混ざっていても数式が直ちにエラーになるとは限りません。
しかし、本来は0であるべき値を空白にしている場合、平均値や標準偏差の意味が変わってしまいます。
空白が未回答なのか、0件なのか、集計対象外なのかを区別しないまま標準化すると結果の解釈を誤る可能性があります。
また、エラー値が範囲内に含まれていると、STANDARDIZE関数もエラーになる場合があります。
計算前にはフィルターを使い、空欄や文字列、異常値が混在していないかを確認しましょう。
【操作のポイント】標準化の前に元データを整え、空白やエラー値の意味を統一しておくことが重要です。
平均値と標準偏差を別セルに置く手順
続いては、平均値と標準偏差を別セルに表示してから標準化する手順を確認していきます。
| A列 | B列 | C列 | E列 |
|---|---|---|---|
| 氏名 | 得点 | 標準化得点 | 平均値 |
| 佐藤 | 52 | 計算式 | 計算式 |
| 鈴木 | 68 | 計算式 | 標準偏差 |
集計セルを作るメリット
STANDARDIZE関数の中にAVERAGE関数とSTDEV.P関数を直接書く方法は、1つのセルで完結するため便利です。
ただし、数式が長くなり、平均値や標準偏差そのものを別途確認したいときには見づらくなる場合があります。
そこで、E2セルに平均値、E3セルに標準偏差を表示し、標準化得点の数式から参照する構成にすると管理しやすくなります。
E2セルに =AVERAGE($B$2:$B$11)
E3セルに =STDEV.P($B$2:$B$11)
C2セルに =(B2-$E$2)/$E$3
この方法では、標準化の計算過程を表の上で確認できます。
平均値と標準偏差を見える場所に置くと、数式の監査、資料の説明、データ更新後の確認がしやすくなります。
共同で使うブックや、後日見直す可能性がある集計表では特に便利な構成です。
【操作のポイント】計算式を短く保ちたい場合や数値を説明したい場合は、平均値と標準偏差を補助セルに分けます。
セル参照で計算する操作画面
ここでは、E2セルとE3セルを参照する数式をC2セルへ入力し、オートフィルで下へコピーするイメージを確認していきます。
数式バーには、C2セルで入力する=(B2-$E$2)/$E$3を表示します。
赤枠のC2セルが計算結果の入力先であり、E2とE3は平均値と標準偏差を固定参照するセルです。
数式入力後はC2セル右下のフィルハンドルを下へドラッグして、各行の計算結果を表示します。
補助セルを使う場合でも、E2とE3には必ずドル記号を付けて絶対参照にすることが重要です。
【操作のポイント】数式バーで参照を確認し、B2だけが行ごとに変わり、E2とE3が固定される状態を作ります。
ゼロ除算エラーの確認
標準偏差が0の場合、標準化得点は計算できません。
標準偏差が0とは、対象範囲の数値がすべて同じで、ばらつきがない状態です。
たとえば全員が80点なら、平均との差もばらつきも比較できないため、標準化の分母が0になります。
このとき、エクセルでは#DIV/0!エラーが表示されます。
=IF($E$3=0,””,(B2-$E$2)/$E$3)
上のようにIF関数を使うと、標準偏差が0のときは空白を表示できます。
ただし、エラーを隠すだけでなく、なぜ全データが同じ値になっているかを確認することが大切です。
標準偏差が0の表では優劣や平均との差を標準化で表せないため、別の評価指標を検討しましょう。
【操作のポイント】#DIV/0!が出たら、まず標準偏差セルが0になっていないかを確認します。
標準化得点を比較と分析に使う場面
続いては、標準化得点を比較や分析に活用する場面を確認していきます。
| 指標 | 元の値 | 標準化後の比較 |
|---|---|---|
| 営業売上 | 円 | 平均からの相対的位置 |
| 処理時間 | 分 | 平均からの相対的位置 |
| 満足度 | 点 | 平均からの相対的位置 |
異なる単位の指標を比べる方法
売上は円、訪問件数は件、作業時間は分というように、実務のデータには異なる単位が混在します。
元の値だけでは、どの指標で平均から大きく離れているのかを一目で比較しにくいでしょう。
各指標を標準化すると、すべて平均0、標準偏差1を基準とした数値になります。
これにより、たとえば売上の標準化得点が0.4、顧客満足度の標準化得点が1.1であれば、満足度のほうが平均から上に離れていると把握できます。
ただし、作業時間のように小さいほうが望ましい指標では、標準化得点が低いことを悪い結果と単純にはいえません。
指標の向きが異なる場合は、高いほどよいのか、低いほどよいのかをそろえてから総合評価する必要があります。
必要なら、作業時間などの逆方向の指標は-1を掛けて符号を反転させる方法もあります。
【操作のポイント】異なる単位を比較する前に、各指標で数値が大きいほど望ましいかを確認します。
条件付き書式による見える化
標準化得点の列には、条件付き書式のカラースケールを設定すると傾向を見つけやすくなります。
標準化得点の範囲を選択し、ホームタブから条件付き書式を選び、カラースケールを適用します。
0を中間色に設定し、正の値を緑系、負の値を赤系にすると、平均より上か下かを直感的に確認できます。
ただし、標準のカラースケールは範囲内の最小値と最大値を基準にすることがあります。
複数の表で色の意味を統一したい場合は、ルールの管理から最小値、中間値、最大値を数値で指定するとよいでしょう。
表示色は分析結果を補助するためのものであり、数値そのものとセットで確認することが大切です。
【操作のポイント】複数の表を比較する資料では、条件付き書式の基準値をそろえて色の意味を統一します。
外れ値を扱うときの注意点
標準化得点の絶対値が大きいデータは、平均から大きく離れた値として確認できます。
一般に、絶対値が2や3を超える値は、外れ値の候補として注目されることがあります。
ただし、これは直ちに入力ミスや異常を意味するものではありません。
大型案件による高額売上、繁忙期の処理件数、特別な事情による長時間作業など、業務上は正しい値である可能性もあります。
標準化得点で候補を抽出した後は、元データ、対象期間、担当者、入力履歴を確認しましょう。
外れ値の発見は削除の判断ではなく、原因を調べるための入り口です。
極端な値が多いデータでは、平均と標準偏差が影響を受けやすいため、中央値や四分位数を併用する分析も有効です。
【操作のポイント】大きな標準化得点を見つけたら、数式の誤りではなく、データの背景を確認して判断します。
まとめ エクセルで標準化得点を関数で計算する手順
エクセルで標準化するには、STANDARDIZE関数を使う方法がもっとも手軽です。
基本となる数式は、=STANDARDIZE(B2,AVERAGE($B$2:$B$11),STDEV.P($B$2:$B$11))です。
表にある全件を対象にする場合はSTDEV.P関数、一部を抽出して全体を推定する場合はSTDEV.S関数を使います。
平均値と標準偏差を別セルへ置き、=(B2-$E$2)/$E$3のように計算する方法も、数式を確認しやすく実務向きです。
標準化得点が0なら平均付近、正なら平均より上、負なら平均より下と読み取れます。
また、標準化後の数値は単位を持たないため、円、件、分、点など、異なる尺度のデータを比較するときにも活用できます。
空白、エラー値、標準偏差が0になるケースを事前に確認し、元データの意味を整えてから計算しましょう。
標準化得点を使いこなせるようになると、単純な合計や平均だけでは見えにくい、全体の中での位置やばらつきが把握しやすくなります。
