Excel関数：「0」を除いた標準偏差の計算方法2

質問

前に質問したのですが条件を書いていなかったので改めて質問します。回答してくれた方、申し訳ありませんでした。
例えば、下のようにセル「A1」が「1」、「A2」が「0」というようにデータがあったとします。これらのデータの中から「0」を除いた標準偏差（母集団、データ総数の）を求める計算方法があったら教えてください。
条件は、
1.一つのセルで計算する
2.「0」を変えない
3.データ範囲「A1:A5」を変えない
4.純粋に関数のみで計算する
です。
あまり、関数を知らないので解説もよろしくお願いします。

　　A
1　1
2　0
3　2
4　5
5　4

回答よろしくお願いします。

回答No.1

grumpy_the_dwarf · ベストアンサー

0だって立派なデータなんだけどなぁ。

stdev(A1:A5)は、その定義より
=sqrt(sumproduct((A1:A5-(sum(A1:A5)/count(A1:A5)))^2)/(count(A1:A5)-1))
と書くことが出来ます。helpでstdevを参照するとこの式を普通に
書いたのが載ってますから見てください。

後はこの式から0の寄与分を除外していくわけです。まず、count
(A1:A5)はcountif(A1:A5,"<>0")と「0でない数」を数えます。sum
(A1:A5)は0がいくつあっても変わらないので放置していいですね。
問題はこうして修正した平均値と各データの偏差ですが、0を除外
した配列を生成することは出来ないので、とりあえず平方和まで計
算してから平均の2乗を0の数だけ引いてやることにします。そうし
て出来上がった式は、

=sqrt((sumproduct((A1:A5-(sum(A1:A5)/countif(A1:A5,"<>0")))^2)-(sum(A1:A5)/countif(A1:A5,"<>0"))^2*countif(A1:A5,"=0"))/(countif(A1:A5,"<>0")-1))

お礼

「0」の値は正確には測定不可という意味なのですが「0」はそのままで使いたかったので。

非常に分かりやすい説明ありがとうございます。
なるほど。最初に、(データ-平均値)^2の合計を出してから、「0」のデータのみの(データ-平均値)^2の合計を引けば、「0」を除いた(データ-平均値)^2が出るわけですね。

大変参考になりました。
ありがとうございました。

回答No.3

Wendy02

こんにちは。

ちょっと試しに作ってみました。
数式の中で、0を省きました。

=STDEV(INDEX(LARGE(A1:A5,ROW(INDIRECT("A1:A"&COUNTIF(A1:A5,"<>0")))),,))

こちらは、0　と空白の共存は出来ませんから、
空白の場合は、
COUNTIF(A1:A5,"<>")　としてください。

実務上は、わたし的には、補助列を使って、単に、=IF(AND(A1<>"",A1<>0),A1,"") と入れて、計算値として省いてから、STDEV 関数を使いますね。

提示された条件付けで、ご自身で出来ればよいけれども、テクニックが先行してしまって、ちょっと難しすぎますね。

お礼

なるほど。ほかの関数を使っても計算式ができるんですね。

参考になりました。
ありがとうございました。

回答No.2

noname#204879

{=STDEVP(IF(A1:A5,A1:A5,""))}
(配列数式)

お礼

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