Aさん 契約社員の業績を評価するために、単位の異なる複数の指標で多角的に評価する仕組みを考えるように上長から指示がありました。。
単位の異なる指標を横並びに扱う塩梅が難しいですが、何か良い方法はありますかね？
森田 その場合、「偏差値」を活用する方法のも一案ですね。
偏差値によって、単位の異なる指標を同じ尺度で比較でき、Excelで計算することが可能です。
では、詳細を解説していきますね。
はじめに
この記事はAVERAGEとSTDEVPの使い方を把握していることが前提です。
関数のAVERAGEとSTDEVPの詳細は、以下の記事をご参照ください。
はじめに この記事はSUMの使い方を把握していることが前提です。 参考記事 SUMの詳細は以下の記事をご参照ください。 数値データの「平均値」を自動計算したい場合は「AVERAGE」が有効 Excelで最も手軽に数値デー …
はじめに この記事は関数の概要を把握していることが前提です。 参考記事 関数の概要については以下の記事をご参照ください。 数値データの「バラツキの大きさ」を定量化したい場合は「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セル）にも再利用するため、平均値/標準偏差のセルを複合参照（行のみ絶対参照）で指定しています。
複数関数の組み合わせや作業セルの詳細は、以下の記事をご参照ください。
はじめに この記事は関数の概要を把握していることが前提です。 参考記事 関数の概要については以下の記事をご参照ください。 単独の関数では機能が不足することがある Excelの関数は、さまざまなケースを想定して多種多様な関 …
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_ワークシート関数_AVERAGE+STDEVP（偏差値）
サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります（上記リンクから登録フォームへ遷移します）。
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
以下はG列（①偏差値）のみが対象。
- 関数を挿入するセルを選択
※今回はG2セル - 「=(」と入力
- 個別の数値を選択
※今回はC2セル - 「-」と入力
- 「av」等と入力
- サジェストから「AVERAGE」を選択し、「Tab」キーで確定
- 計算対象のセル範囲を選択
※今回はC2~C11セル（行のみ絶対参照） - 「))/」と入力
- 「std」等と入力
- サジェストから「STDEVP」を選択し、「Tab」キーで確定
- 母集団にしたいセル範囲を選択
※今回はC2~C11セル（行のみ絶対参照） - 「)*10+50」と入力
- 「Enter」キーで確定
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
単位の異なる指標を同じ尺度にしたい場合、偏差値は便利です。
実務で必要になった際は本記事の内容に沿って計算してみてください。
なお、偏差値以外にもExcelの各種テクニックを拙著で体系的に解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 私の場合、コンタクトセンターの管理者としてオペレーターの業績評価をする際や、企画職として各デスクの業績を比較する際等で偏差値を使うケースがありました。
偏差値の計算式はパッと見は複雑ですが、分解するとそこまで難易度が高いわけではないため、実務で使う機会がありそうなら覚えると良いですね。
