Excel。STANDARDIZE関数は標準化得点を算出します。
＜関数辞典：STANDARDIZE関数＞
STANDARDIZE関数
読み方： スダンダーダイズ
分類： 統計
STANDARDIZE(x,平均,標準偏差)
標準化得点を算出する
11/25/2022
Excelの様々な関数の読み方や引数などを紹介。今回は、SQRT関数～STANDARDIZE関数です。【dictionary】
Excelの様々な関数の読み方や引数などを紹介。今回は、SQRT関数～STANDARDIZE関数です。
＜Excel関数辞典：VOL.74＞
今回は、SQRT関数～STANDARDIZE関数までをご紹介しております。
SQRT関数
読み方： スクエアルート
分類： 数学/三角
SQRT(数値)
平方根を求める
SQRTPI関数
読み方： スクエアルート パイ
分類： 数学/三角
SQRTPI(数値)
円周率と数値の積の平方根を算出する
STANDARDIZE関数
読み方： スダンダーダイズ
分類： 統計
STANDARDIZE(x,平均,標準偏差)
標準化得点を算出する
2/26/2022
Excel。AとB。得点も平均も同じだけど、どっちのほうに価値があるのかしりたい【Standardization】
Excel。AとB。得点も平均も同じだけど、どっちのほうに価値があるのかしりたい
＜STANDARDIZE・AVERAGE・STDEV.P関数＞
同じ数字には、同じ価値があるはずですが、全体のデータの中でどのような位置にその数値があるのかによって、価値が変わってきます。
例えば、次の表を見てみましょう。
B列には、Leadingの得点。C列には、Writingの得点の表です。
LeadingとWritingは、当然テスト内容も異なりますので、難易度も異なっています。
それを踏まえたうえで、数式を確認します。
B13:C13には、それぞれの平均値が算出されています。
B13の数式は、
=AVERAGE(B3:B12)
B14:C14には、標本標準偏差を算出しています。
B14の数式は、
=STDEV.P(B3:B12)
標準偏差とは、データのバラツキ度合いを数値として算出したものです。
B列のLeadingのほうが、Writingよりも数値が大きいので、Leadingはバラツキが大きいということがわかるというわけです。
なお、STDEV.P関数は、全データをサンプル（母集団）として算出したものです。
一部のサンプルから全体とみなす場合は、STDEV.S関数をつかいます。
さて、ここからが本題。
番号2番は、両方とも、85点ですが、同じ価値なのでしょうか。
要するに、平均が90点なのに、85点は、平均以下になってしまいますが、逆に平均が30点ならば、圧倒的な数値です。
このように、データによって、価値が変動します。
では、2番の人は、どちらのほうが、価値があるのでしょうか。
そこで、使う関数が、「STANDARDIZE関数」です。
STANDARDIZE関数は、標準化得点であるZ得点を算出する関数です。
D3の数式を確認してみましょう。
=STANDARDIZE(B3,B$13,B$14)
最初の引数は、データの数値なので、B3。
2番目の引数は、平均なので、B13。
オートフィルで数式をコピーするので、行固定の複合参照にしますので、B$13としています。
最後の引数は、標準偏差なので、B14。こちらも、行固定の複合参照にしますから、B$14と設定します。
あとは、オートフィルで数式をコピーしたのが、上記の表です。
2番のデータを見てみましょう。
Leadingが、0.681で、Writingが、1.664 と算出されました。
この数値が大きいほど、価値があることがわかるので、Writingの85点の方が、価値が高いことが判明しました。
確かに、2番のWritingの85点は、全体でトップの成績です。
5番目データは、LeadingのほうがWritingよりも点数自体は78点と高いのですが、Writingの標準化得点が高いので、点数こそ71点と低いですが、価値が高いと判断できるわけです。
このように、単純にその数値が高い、低いだけではなく、周りから見て、どうなのかという判断できるデータも、日頃使っている資料に追加してみると、違ったことが見えてくるかもしれませんね。
4/10/2020
Excel。国語・英語・数学。標準化すれば、どこを強化すればいいか見えてきます。【STANDARDIZE関数】
Excel。国語・英語・数学。標準化すれば、どこを強化すればいいか見えてきます。
＜AVERAGE関数・STDEV.S関数・STANDARDIZE関数＞
会議の資料によくある、数字ばかりの資料。売上高や得点など実際の数値の方に目が向きやすい傾向があります。単純にいえば、教科の得点など、数値が大きい方が、いいのに決まっていますから、そこに着目するわけですね。
例えば、次の教科ごとのテスト得点表があります。
国語と英語と数学のテスト結果の一覧ですが、これを見て、Aさんは数学を重点的に強化したほうがいい感じを受けますよね。
しかし、教科ごとによって、テスト内容は違いますし、難易度も変わりますよね。
これは、売り場ごとの商材によっても変わるのと同じですね。
このように条件が異なる場合では、平均と標準偏差を算出し、その後、平均と標準偏差を使って『標準化』した値を算出することで、状況が見えてきます。
では、平均と標準偏差を算出していきます。
お馴染みの平均ですが、B6の数式は、
=AVERAGE(B2:B5)
続いて標準偏差ですが、標準偏差を算出する関数は、STDEV.S関数です。
STDEV.S関数ダイアログボックスを表示してもいいですが、簡単なので、直接入力してもつくれます。
標準偏差を算出しているB7の数式は、
=STDEV.S(B2:B5)
数式自体はあっさりしていますが、これで標準偏差を算出することができます。
あとは、オートフィルで数式をコピーします。
この表だけではまだ『標準化』を算出したわけではありません。
今回は下側に同じフレームの表を用意します。
B10に標準化した結果を表示していきます。
標準化するための関数も用意されています。
それが、『STANDARDIZE関数』です。
平均と標準偏差を算出したのは、このSTANDARDIZE関数の引数で必要になるのも理由の一つです。
では、B10をクリックしてSTANDARDIZE関数ダイアログボックスを表示します。
Xには、B2を設定します。
平均には、B$6を設定します。
複合参照にしているのは、このあと、オートフィルで数式をコピーするからですね。
行番号を固定しておきます。
標準偏差には、B$7を設定します。平均と同じように、複合参照にしておきます。
あとは、オートフィルで数式をコピーします。
そして、平均と標準偏差も改めて算出しましょう。
これで、標準化を算出することができました。
標準正規分布グラフを思い出せばわかってきますが、0に近い方が平均に寄っているわけです。
要するに0から遠ざかると、平均値から外れる。
さらに、マイナスの場合は平均値以下ということなので、今回のような成績の場合、より注視されるのはマイナスの数値で、０より遠い数値ということになります。
例えばBさんの場合、英語が60点といい感じがしますが、国語の50点よりも、英語全体から見れば、よくないことがわかります。
なので、まずは英語をより注視して対応するほうがいいということが、見えてきたりします。
このように、数字の大小だけを判断材料にするのは、ちょっとどうなのかな？という時には、いろんな角度からみられる資料を作ってみるというのもいいのかもしれませんね。
11/17/2017
Excel関数一覧表にアイテムを追加しました。【function:2017/11/17】
Excel関数一覧表にアイテムを追加しました。【function:2017/11/17】
＜Excel関数一覧＞
「Excelテクニック and MS-Office recommended by PC training」の目次サイト「Officeソフトのスキルアップサイト」のExcel関数一覧表に次のアイテムを追加しました。
GAUSS ガウス
指定した標準偏差の範囲になる確率を算出
NORMDIST ノーマルディスト
平均と標準偏差に対する正規分布の確立を算出
NORM.DIST ノーマル・ディスト
平均と標準偏差に対する正規分布の確立を算出 Excel2010以降
NORMSDIST ノーマルスタンダードディスト
標準正規分布の累積確立を算出
NORM.S.DIST ノーマル・スタンダード・ディスト
標準正規分布の累積確立を算出 Excel2010以降
NORMINV ノーマルインヴァース
累積確立から正規分布の数値を逆算する
NORM.INV ノーマル・インヴァース
累積確立から正規分布の数値を逆算する Excel2010以降
NORMSINV ノーマルスタンダードインヴァース
累積確立から標準正規の数値を逆算する
NORM.S.INV ノーマル・スタンダード・インヴァース
累積確立から標準正規の数値を逆算する Excel2010以降
POISSON ポワソン
ポワソン分布の確立を算出
POISSON.DIST ポワソン・ディスト
ポワソン分布の確立を算出 Excel2010以降
STANDARDIZE スダンダーダイズ
標準化得点を算出する
今回は統計関係の関数を12個追加しました。
Officeソフトのスキルアップサイト
https://sites.google.com/view/infoyandss/ホーム
8/14/2017
Excel。事務職のデータ分析その8。標準化してみると見えてきます【Standardization】
Excel。事務職のデータ分析その8。標準化してみると見えてきます
＜標準化&STDEV.S関数&STANDARDIZE関数＞
最近は、事務職でも資料作りの一環で、データ分析系の資料作成をする人が増えてきたそうですので、
あまり馴染みがないものも少しずつ慣れていくようにしましょう。
ということで、
店舗ごとに、どのジャンルに力を入れていけばいいのか？
改善するジャンルはどれ？
ということが見えてくる【標準化】について紹介します。
このような売上表があります。
数字だけ見ると、
【コーン菓子】を全店舗で力をいれて改善する必要があるようにみえますよね。
これは、数字の大小だけをみてしまうからですが、
それぞれ、単価が違うとか、売上個数が違うとかあるわけですね。
要するに、コーン菓子とアイスとケーキは、
別な条件で販売されているということを忘れてはいけません。
では、どうしたらいいのか？というと、
すべてある基準、すなわち統計学的に見やすくしてあげればいいわけです。
それが【標準化】です。
標準化を算出するには、
「平均」と「標準偏差」を算出する必要があります。
6行目に「平均」。
7行目に「標準偏差」を算出していきます。
B6には、=AVERAGE(B2:B5)という数式を設定します。
オートフィルでD6まで数式をコピーしましょう。
続いて、B7に「標準偏差」を算出しますので、
標準偏差を算出する関数である、
【STDEV.S関数】を使いますので、
STDEV.S関数ダイアログボックスを表示します。
数値1には、B2:B5を設定して、OKボタンをクリックします。
平均と同じように数式をコピーしましょう。
ここまで出来ました。
これらの数値を使って【標準化】を算出しますので、
A1:D7を範囲選択して、フレームだけ下方向にコピーします。
【標準化】を算出するには、
STANDARDIZE関数（スタンダーダイズ関数）
を使うと算出することが出来ます。
STANDARDIZE関数は、
平均と標準偏差をもとに値の変量を算出してくれる関数です。
なかなか、使う機会は少ない関数なので難しいように感じますが、
いたって簡単。
では、STANDARDIZE関数ダイアログボックスを
B11をクリックして表示しましょう。
Xには、B2を設定します。
平均には、B6を設定しますが、
このあとオーフィルで数式をコピーするので、
複合参照で行を止めておきますので、B$6
標準偏差には、B7でこちらも、複合参照にしますので、B$7
あとはOKボタンをクリックします。
出来た数式を、D14までコピーしましょう。
この値が、標準化された値です。
平均と標準偏差も先ほどと同様に算出してあげると、平均は0。
標準偏差は1と算出されます。
標準化では、
平均値を0・標準偏差を1としてデータを変換した値ということになります。
標準化する事によって、
例えば、新宿店では、ケーキの数値が悪いことがわかりますので、
改善をするのは、ケーキということがわかるのです。
売上金額だけを見ていたら、コーン菓子の数値が一番悪いので、
そこを改善対象にしていたはずですね。
このように【標準化】という方法があることを知っている、
また使った表を作成できるというのは、
今後、事務職であっても、知っておいたほうがいい事なのかもしれませんね。
登録:
投稿 (Atom)