<Excel>条件つき標準偏差の求め方

質問

初めて利用します、よろしくおねがいします。

★　49 < x にあてはまる元の標準偏差を求める。
90 68 67 54 53 52 48 47 40

この問題の通りなのですが、注意があります。今回は数が９つしかないけど、最大で１８コまで入力されるんです、実際は。つまり

90 68 67 54 53 52 48 47 40
90 68 67 64 62 59 54 53 52 48 47 40

という風に、Excelには入力されます。また必然的に一つの式にする必要があります（下の行に、下の行に、と連続してデータが入力されるので）

よろしくおねがいします。

回答No.3

nishi6 · ベストアンサー

＞ちょっと統計学習いたての僕には難しかったので・・・
逆に統計学のことなど遠い昔のことのようです・・・

回答は、数値を『母集団の標本』として、STDEV 関数と一致するように計算しています。

　従って、SQRT([nΣ(x^2)-(Σx)^2]/[n(n-1)])　を計算しています。ExcelのHelpを参照してください。

『母集団全体』としてSTDEVP 関数と同じ計算を行うなら、

　SQRT([nΣ(x^2)-(Σx)^2]/[n^2])　を計算することになりますので、算式は

=SQRT(
　　　(COUNTIF(A2:R2,">49")*SUMPRODUCT((A2:R2>49)*(A2:R2)^2)-(SUMIF(A2:R2,">49"))^2)
　　　)
　　　/COUNTIF(A2:R2,">49")

分母のSQRT(n^2) をSQRTの外に出しています。これで質問の意図と合います？

お礼

なるほど！って言っても完全には理解できていませんが...。正しい数値はしっかり出ました！

それにしてもsumproductなんていう便利な関数も知ることができて良かったです。あぁ、Excelは面白いなあ。

重ね重ねありがとうございました～。

回答No.2

nishi6

最大18個がA～R列に入力されるとして、S2に計算するための式を書いてみました。
『49 < x 』のセルのみを対象にし、条件が満たされなければ除外しています。
無理やり1セルにおさめています。少し長いです。

=SQRT(
　(COUNTIF(A2:R2,">49")*SUMPRODUCT((A2:R2>49)*(A2:R2)^2)-(SUMIF(A2:R2,">49"))^2)
　　/COUNTIF(A2:R2,">49")/(COUNTIF(A2:R2,">49")-1)
　　　)

上は連続した式です。

nΣx^2 ＝　COUNTIF(A2:R2,">49")*SUMPRODUCT((A2:R2>49)*(A2:R2)^2)
(Σx)^2＝　(SUMIF(A2:R2,">49"))^2)
n(n-1) ＝　COUNTIF(A2:R2,">49")/(COUNTIF(A2:R2,">49")-1)

SQRT([nΣx^2-(Σx)^2]/[n(n-1)]) を『>49』を満たすセルのみで計算しているはずです。

補足

回答ありがとうございます。ちょっと統計学習いたての僕には難しかったので頭をフル回転させています...。

標準偏差の定義として
・SQRT{(２乗の平均)-(平均の２乗)}
・SQRT[{Σ{(平均-ｘ)^2}}/元の個数]
と習ったのですが、nishi6さんの
SQRT([nΣx^2-(Σx)^2]/[n(n-1)])
の意味がよくわかりません。

というのも
=SQRT(
　(COUNTIF(A2:R2,">49")*SUMPRODUCT((A2:R2>49)*(A2:R2)^2)-(SUMIF(A2:R2,">49"))^2)
　　/COUNTIF(A2:R2,">49")/(COUNTIF(A2:R2,">49")-1)
　　　)

の式で算出した値が、手動で求めた場合と微妙に違っていたのです。（もちろんA2→J3などと置換して僕のファイルに対応させました)

僕の勘違いかもしれませんが、再考いただければ光栄です。

回答No.1

imogasi

統計とか苦手な私です。間違っている個所があったらごめんなさい。
（１）エクセルに＝Dstdeｖ（）（括弧内略）と言う関数があります。
（２）ただ第１行に項目見出し（例えば点数）を入れ、列方向にデータ（例えば点数）を入れないと（A1:A10とか）ダメなようです。私はやって見てダメでした。貴方の仕様ではデータは横・行方向に入れる風に取れますが、縦方向に入れることは出来ますか。行と列を入れ替えたデータは「形式を選択して貼り付け」で変換できますよね。
（２）標本を対象とする標準偏差と母集団全体を対象とする標準偏差の関数が別々にあります。前者が良いのですか。使用目的との関連がわかりません。
（３）標本（条件該当分）が１つの時は、標準偏差の公式では、分散をN-1で割ったと思いますが、分母は０になり、式の結果が０割算のエラーになります。これをどうしたら良いか経験不足で判りません。分散の方の関数は＝DVAR()とDVARPです。
（４）＝DSTDEVの例を１つやって見ます。
データとしてB3:B14に点数90,68,67,64,62,59,54,53,52,48,47
を入れます。見出しの「点数」（B3)は必須です。横に書いていますが、縦に・列方向に入れてください。最後の４７は（３）の理由から貴方の質問のデータに１つ加えて増やしました。
（４）C3:C4のセルに、「点数」、「<49」をそれぞれ入れます。
「Criteria」（条件のこと）と言います。
（５）B15に式=DSTDEV(B3:B14,B3,C3:C4)と入れます。
（６）結果数値は0.707106781となりました。
（７）検証として＜４９は４８、４７の２つで、平均４７．５。偏差平方和は(48-47.5)^2+(47-47.5)^2で
(0.5)^2+(0.5)~2=0.5です。=SQRT(0.5)は0.707106・・
なので、合っているのでしょう。
（８）エクセルの関数の本を読み、DSTDEVを調べてください。関数の書式は＝DSTDEV(データベース、フィールド、
Criteria)です。
（９）B3からデータをはじめたり、は私の勝手にしたもので意味はありません。最多１８個のデータが入ることも
考慮から外し、B15に式を入れてしまっています。
B3:B21にデータを入れB22にDSTDEVを入れることになるでしょう。また例えばB15：B20が空白でも、関数式のデータベース部はB3:B21で良いでしょう。

補足

回答ありがとうございます。

まず...失礼ですが問題を読み違えてませんか？>49です、<49ではありません^^;

僕もこれに投稿する前にdstdevを考えました。ですが、ヘルプなどを見てもフィールドの意味がつかめず、いつも"value!"と表示され諦めました。

今、時間がないのでもう一度dstdevを考えることは出来ませんが、再考次第、カキコします。