エクセルの関数について教えてください。

質問

標準偏差を求めるのですが
その表が４月から翌年３月になっていて
ひと月のデータ数は最大で50くらいですが決まっていません。
空白の場所には
計算式が入っていて、データが入力されていないと
「0」が表示されています。
この「0」は標準偏差を計算するときも計算されてしまのですが
「0」は計算しないで標準偏差をだしたのですが
方法を教えてください。

宜しくお願い致します。

回答No.2

keithin · ベストアンサー

Ｂ１からＢ５０に数字やゼロが計算されているとして。

とりあえず手っ取り早く
=STDEV(IF(B1:B50<>0,B1:B50))
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力します。

＃別の方法
Ｂ１からＢ５０に数字やゼロが計算されているとして。
標準偏差は
=SQRT(N*SUMSQ(B1:B50)-SUM(B1:B50)^2)/N/(N-1))
で計算できます。ここでＮは関数じゃなく、対象範囲に入っている「（ゼロは除いた）計算したいデータの個数」です。
正確に、Ｂ１：Ｂ５０にもれなく数式が記入してあるならＮの部分にはCOUNTIF(B1:B50,"<>0")が入ります。
もし計算したい「数字」が例えば「プラスの数」とかなら、Ｎの部分にはCOUNTIF(B1:B50,">0")としてもＯＫです。

回答No.3

ryo_ky

標準偏差を求める時はおそらく範囲を設定して値を出しますよね？
その中に0があれば確かに偏差は小さくなるので、0のデータを無視するというのは分かります。

この問題を解決する方法は２つあります。
１つは空白のデータを0にしない方法です。
『空白の場所には計算式が入っていて』と質問にありますが、この計算式は"あるセルにデータが入っているとその値を参照して計算"するものだと思いますが、この計算式をいじるのが一番簡単です。
IF関数を用い、
条件：参照セルの値が0でない場合
真の値：計算式
偽の値：空白
とすれば解決できます。具体的には参照セルをA2とすると
=IF(A2<>0,計算式,"")
※ここで""は空白になります。
空白であれば、0ではないので標準偏差の母集団にふくまれないので、0を除外した計算結果になると思います。

もうひとつの方法は作業行を追加する方法です。
この方法のメリットは元々のセルのデータをいじらないという事です。
例えば計算式の入っているデータがA列とすると、標準偏差を求めるための列にB列を使います。
B列には=IF(A2<>0,A2,"")と入力し、データの入力しているセルの行までコピーします（質問から50程度入力されているとの事なのでB53までコピーします）。そうするとB列には値もしくは空白が入力されます。このB列を標準偏差を求める範囲に使えば0のデータを無視した標準偏差が求められます。

回答No.1

gn_drive

もう１行セルを追加して、
=if(A1=0,"",A1)
(A1は本当のセルにする)
それをコピーする。
そしてそこの標準偏差(=stdev())をとればいかが。

0
3 3
4 4
5 5
0
2 2
0
2.081 1.291