excel 標準偏差の求め方

よろしくお願い致します。
標準偏差を求める関数を作製したいのですが、
STDEVP関数を使えばいいのですが、
空白のセルもあるため、使えません。

COUNTAを使って、数値が入っているセルのみで
標準偏差を求められますでしょうか？

平均と、（X-Xi)＾２は既に求めてあります。

よろしくお願い致します。

No.1 回答者： okormazd 回答日時： 2010/11/09 17:58

STDEVP関数は、空白セルがあっても、それは抜かして標準偏差を求めます。

この回答へのお礼

ありがとうございます。＆言葉足らずでした。
空白のセルではなく、計算結果0のセルでした。
計算式は入っているので、STDEVP関数は使えなかったのです。

言葉足らずですみませんでした。

お礼日時：2010/11/10 15:07

No.2 回答者： CoalTar 回答日時： 2010/11/09 18:24

ちょっと気になったので

＞COUNTAを使って、数値が入っているセルのみで
数値の入っているセルの数を数えるのはCOUNTです。

標本標準偏差をSTDEVPを使わないで求めると
=(SUMPRODUCT(((A1:A9<>"")*(A1:A9-AVERAGE(A1:A9)))^2)/COUNT(A1:A9))^0.5
といったところでしょうか(^.^)無駄な努力か。

この回答へのお礼

ありがとうございます。＆言葉足らずでした。
１さんにも書きましたとおり、計算結果が0だったわけで、計算式は入ってまして。

具体的に書くと、
C10～G10の平均がI10で、それが29行、C38まであります。
I10にはAVERAGE(C10:G10)が入っており、以下I38まであります。
C列に数字が入らなければ、I列の計算結果は必ず0です。
(C列に数字が無ければ、DEFG列にも入らないため。）
C列の空白をCOUNTを使って拾い、C列に入力されている場合のみの
I列の標準偏差を求める方法はありますでしょうか？

ご教授いただいた式を、自分なりに工夫しましたが
うまくいきません。

自分なりに並行して努力いたしますが、再度お力を拝借したく存じます。

お礼日時：2010/11/10 15:26

No.3 回答者： Wendy02 回答日時： 2010/11/10 12:26

もう、正解が出ていますから、無駄かもしれませんが、今あるものを活用するなら、

>平均と、(X-Xi)^2は既に求めてあります。

分散 =Σ(X-Xi)^2 ÷COUNT(範囲) ....データ個数
標準偏差 = 分散^(1/2)

A列にデータがあるとして、
A1 から A9 までとしたら、

B1 から、それ以下に
=IF(A1<>"",(A1-AVERAGE($A$1:$A$9))^2,"")

B10:
=SUM(B1:B9)　....標準偏差

B11:
=(B10/COUNT(A1:A9))^(1/2)

この回答へのお礼

ありがとうございます。＆言葉足らずでした。
2さんのお礼の通りです。
あと、自分の勘違いもありまして。

>平均と、(X-Xi)^2は既に求めてあります。

これを
求めてあるシートもありますが、無いシートもありまして。
(X-Xi)^2が無い場合でも関数にて計算可能か、
ご教授頂ければ幸いです。


以後、質問の仕方に細心の注意を払いたいと思います。

お礼日時：2010/11/10 15:32

No.4 回答者： Wendy02 回答日時： 2010/11/10 12:34

#3の訂正

B10:
=SUM(B1:B9)

B11:
=(B10/COUNT(A1:A9))^(1/2)　....標準偏差

No.5 ベストアンサー 回答者： CoalTar 回答日時： 2010/11/10 16:03

#2です。

返答より
=STDEVP(IF(ISNUMBER(C10:C38),I10:I38))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式です({}で囲まれる)

ただ、平均の標準偏差って計算する意味があるのか、
勉強不足でわからない(^^;)STDEVのほうがいいのかも。

この回答へのお礼

ありがとうございます。＆解決いたしました。

仕事上の提出書類で、そうのような雛形の仕様でして
恐縮ですが、数字があっていればOKなのでした。

＞平均の標準偏差って計算する意味があるのか、

こういう疑問すら抱きませんでした。
お手数お掛けいたしました。
ありがとうございます。

お礼日時：2010/11/10 17:33

No.6 回答者： Wendy02 回答日時： 2010/11/10 17:12

ご説明ではよく分かりませんでした。

>C10～G10の平均がI10で、それが29行、C38まであります。
>I10にはAVERAGE(C10:G10)が入っており、以下I38まであります。

全体の表の作りが分かりません

例：
　　　C　　 D　　　E　　 F　　　G　　　　I
10|　84　　170　　10　　117　　97　　　　95.6　

>C列に数字が入らなければ、I列の計算結果は必ず0です。

>I列の標準偏差を求める方法はありますでしょうか？

言葉を断片的に拾いあげている状態ですから、欲分かりません。C列セルに値が入らなければ、0というのですから、i10の標準偏差を求めるはずはないと思うのです。あくまでも、i38 の部分ではありませんか？

I列のi10からi38 までを演算対象にするなら、以下のようにしたらよいと思います。

=(1/COUNTIF(I10:I38,"<>0")*(SUMPRODUCT(I10:I38,I10:I38)-SUM(I10:I38)*SUM(I10:I38)/COUNTIF(I10:I38,"<>0")))^(1/2)

COUNTIFで、0を除き、カウントするようにして、STDEVP を展開してみました。

この回答へのお礼

引き続きありがとうございます。＆また、足りなかったようですね。すみません。

I10からI38には、AVERAGE関数を使用しております。AVERAGE(C10:G10)↓
そして、C列に数値が無い場合DEFGにも数値が入ることはありません。
よって必然的に、Cに数値が無い場合はI列セルの計算結果は0です。

I10からI38の範囲で、0値以外の値の標準偏差を求めたい。

計算結果0になる根拠を、C列で表現しました。



意図が伝えられてますでしょうか？
もし、配列数式を使用しなくてもいい方法があれば、引き続きご教授願います。

お礼日時：2010/11/10 17:52

No.7 回答者： Wendy02 回答日時： 2010/11/10 19:33

#5さんのところで

>解決いたしました。
えっ!確かに、C列に対して判定すればよいのですが……。

>I10からI38の範囲で、0値以外の値の標準偏差を求めたい。
あくまでも、I列を対象として、0以外なら、0を排除するという話ではないでしょうか？
私は、頭が悪いのかもしれませんね。ご質問者さんの言葉をそのまま鵜呑みにしたのがいけないようです。

I10:I38 で、文字列の(""　長さ0の文字列)にすれば、複雑な数式は必要ないはずです。
単に、STDEVP だけでよいはずです。

>もし、配列数式を使用しなくてもいい方法があれば、引き続きご教授願います。
また、以下の数式は、SUMPRODUCT(I10:I38,I10:I38)で、配列を使っていないわけではないのですが、配列の確定をするわけではありませんし、これを使わないと、かなり長くなります。ここに何か問題あるのでしょうか？むろん、全部を配列数式のほうが楽だと思いますが。

#6の回答では満たないでしょうか。きちんと確認はしていますけれどもね。

◎I列のi10からi38 までを演算対象にするなら、以下のようにしたらよいと思います。

=(1/COUNTIF(I10:I38,"<>0")*(SUMPRODUCT(I10:I38,I10:I38)-SUM(I10:I38)*SUM(I10:I38)/COUNTIF(I10:I38,"<>0")))^(1/2)

配列確定をしなくてよいなら、
=STDEVP(INDEX(LARGE(I10:I38,ROW(INDIRECT("1:"&COUNTIF(I10:I38,"<>0")))),,))
(ただし、すべてが、0以上であること）

とすればよいのですが。なんだか、狐につままれたようです。

この回答へのお礼

うーん、
質問者の立場ながら、そのような表現をお使いになられるなら、
あまり回答なさらない方がよろしいのではと思います。
貴殿にうまく伝わらなかったとしても、質問者側に非は無いと思うんですけどね。
ただ、レスを付けずスルーでいいと思うんですけどね。
事実、No.5さんにはうまく伝わっているわけですし。

>もし、配列数式を使用しなくてもいい方法があれば、引き続きご教授願います。
一度アクティブになると、もう一度[Ctrl]+[Shft]+[Enter]をしなければならないのが
忘れがちなので、配列数式でない方法があるのであればと思い書いたまでで。
そして、この表現で、No.5さんので満足良く結果が得られていると読解できるわけで。

貴殿から頂いた回答No.6・7では残念ながら、うまくできてません。
No.5さんの回答と貴殿のとでは、まったく違った値が出ます。ご確認下さい。

私には、貴殿が狐に思えてなりませんが？
お手を煩わせた事に関しては、お詫び申し上げておきますね。

お礼日時：2010/11/11 11:11