【関数】「偏差値」を計算できる「AVERAGE」と「STDEVP」の組み合わせテクニック

Aさん

契約社員の業績を評価するために、単位の異なる複数の指標で多角的に評価する仕組みを考えるように上長から指示がありました。。
単位の異なる指標を横並びに扱う塩梅が難しいですが、何か良い方法はありますかね？

森田

その場合、「偏差値」を活用する方法のも一案ですね。
偏差値によって、単位の異なる指標を同じ尺度で比較でき、Excelで計算することが可能です。
では、詳細を解説していきますね。

はじめに

この記事はAVERAGEとSTDEVPの使い方を把握していることが前提です。

単位の異なる指標を「偏差値」で同じ尺度にする

実務では、個人あるいは組織を多角的な指標で総合的に業績を評価したい場合があります。

その際、採算やCS、ES、品質、勤怠等、単位の異なる複数の指標を比較するのはなかなか難しいもの。

こうした場合、学生時代の受験等で良く使われている「偏差値」がビジネスでも有効です。

偏差値は、数値が全体（母集団）の中で相対的な位置を調べるのに適しています。

具体的には、平均値に等しい「50」を基準とし、数値が高いほど全体の中で上位にいることを示します。

この偏差値を使うことで、単位の異なる複数の指標を「偏差値」という共通の尺度にすることが可能です。

AVERAGEとSTDEVPを組み合わせれば、「偏差値」を計算可能！

Excelで偏差値を求めるための専用の関数はないため、以下の数式をセットすればOKです。

なお、平均値の部分は「AVERAGE」、標準偏差の部分は「STDEVP」を使います。
標準偏差とは、「データ全体のバラツキの大きさ（=平均値から離れているデータの多さ）」を数値化したもの。

実際に数式をセットしたものが以下です。

これで、単位の異なる指標を「偏差値」という共通の尺度に変更できました。

後は、この各偏差値を使ってどう評価するかを決めればOKです。
偏差値を単純に合計する、各偏差値を一定基準でポイント化してから合計する等。

なお、G2セルの数式を他にセル（G2~I11セル）にも再利用するため、AVERAGE・STDEVPの参照範囲は複合参照（行のみ絶対参照）で指定しています。

【応用】数値が小さい方を上位にする場合の偏差値の求め方

偏差値は通常、数値の大きい方を上位として扱います。

しかし、退職率やミス率等、指標によっては数値の小さい方を上位にしたい場合があります。

この場合、「100」から偏差値を減算すればOKです。

前述のデータの退職率を対象に偏差値を求めた数式が以下です。

これで、数値が小さい方を上位にした指標も他の指標と同じ尺度にできました。

【参考】作業セルで数式をシンプル化してもOK

偏差値の数式はAVERAGE・STDEVPを含むため、長くなりがちです。

また、各レコードの数式にAVERAGE・STDEVPの同じ数式（「AVERAGE(C$2:C$11)」、「STDEVP(C$2:C$11)」等）が繰り返し入るため、作業セルでそれぞれの数式を用意し、それを各偏差値の数式で参照した方がシンプルです。

この場合も、G2セルの数式を他にセル（G2~I11セル）にも再利用するため、平均値/標準偏差のセルを複合参照（行のみ絶対参照）で指定しています。

サンプルファイルで練習しよう！

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ワークシート関数_AVERAGE+STDEVP（偏差値）
サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります（上記リンクから登録フォームへ遷移します）。

ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
以下はG列（①偏差値）のみが対象。

本記事の解説と同じ結果になればOKです！

さいごに

いかがでしたでしょうか？

単位の異なる指標を同じ尺度にしたい場合、偏差値は便利です。

実務で必要になった際は本記事の内容に沿って計算してみてください。

なお、偏差値以外にもExcelの各種テクニックを拙著で体系的に解説していますので、こちらも参考にしてみてください。

Excel関数をフルに使って データの整形・集計・可視化の自動化を極める本

created by Rinker

ソシム

¥2,640 (2024/09/21 04:05:13時点 Amazon調べ-詳細)

• Kindle
• Amazon
• 楽天市場
• Yahooショッピング

ご参考になれば幸いですm(_ _)m

森田

私の場合、コンタクトセンターの管理者としてオペレーターの業績評価をする際や、企画職として各デスクの業績を比較する際等で偏差値を使うケースがありました。
偏差値の計算式はパッと見は複雑ですが、分解するとそこまで難易度が高いわけではないため、実務で使う機会がありそうなら覚えると良いですね。