COUNTIF関数で0以外の平均値

質問

COUNTIF関数で0を除く平均値を出したいと考えています。例えば下の平均値は300ということになります。
　　　A
１　　 600
２　　 400
３　 　　0
４　　-100
５　　 900←合計

A5/COUNTIF（A1:A4,">0")
↑
これなら0以上の数値の平均となり、-100まで除かれてしまいます。
0を除いたものとするにはどうすればよいのですか？

回答No.5

134 · ベストアンサー

=a5/(countif(a1:a4,"<>")-countblank(a1:a4"))

とすると、空白セルは省けそうに思います。

お礼

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

回答No.7

maruru01

こんにちは。maruru01です。

配列数式の別解。

=SUM(A1:A4)/COUNT(IF(A1:A4*1<>0,))

と入力して、[Ctrl]と[Shift]を押しながら[Enter]で確定。
（数式の両端に「{}」が付く）

範囲に数値以外は入力しないなら、

=SUM(A1:A6)/SUMPRODUCT((A1:A6*1<>0)*1)

で、普通に[Enter]で確定、でもOK。

お礼

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

回答No.6

imogasi

空白行に対する対処法。
COUNTIFは２条件に対処できない。別の方法を考える。
（１）件数から=COUNTBLANK(A1:A8)を引く方法（＃５のご回答）
（２）配列数式　これも「判らない」といわず、勉強して見て下さい。応用が広がります。
http://www.kste.co.jp/pc21/pc_10/hr11.htm　など。
=AVERAGE(IF((A2:A9<>0)*(A2:A9<>""),A2:A9,""))
といれて、SHIFT+CRTL+ENTER
（３）DAVERAGEを使う
（例データ）A1:A9に
計数
10
20
40
50

0
-15
20
(条件）
D1:E2
計数 計数
<>0 <>""
合計を出したいセルに
=DAVERAGE(A1:A9,"計数",D1:E2)
「計数」は「人員数」などなんでも内容を表す見出しで良い。しかし必須。

お礼

アドバイスありがとうございました。配列数式も勉強してみます。

回答No.4

imogasi

#3です。配列数式を使う、条件付き平均は
=AVERAGE(IF(C1:C8<>0,C1:C8,""))で
SHIFT＋CTRL+ENTERを押す、
でした。
（例）C1:C8に
200
100
0
-50
200
0
300
0
で結果は150です。

補足

配列数式というのはわからないので、使わない場合でうまくいきました。今度は空白のセルまで合計されてしまいます。空白と0以外の平均を出したいのですが、できますか？

回答No.3

imogasi

（データ例）
200
100
0
50
200
0
300
0
（関数式）
Ｃ９に=SUM(IF(C1:C8<>0,C1:C8,""))/COUNTIF(C1:C8,"<>0")といれてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ（３キーを一緒に押す）配列数式。
（結果）１７０
配列数式を使わないなら
＝SUM(C1:C8)／COUNTIF(C1:C8,"<>0")

回答No.2

Ultramanvaio

A5/(COUNTIF(A1:A4,">0")+COUNTIF(A1:A4,"<0"))
これでどうでしょう？

お礼

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

回答No.1

134

～　"<>0"

では、だめですか？

お礼

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

補足

ありがとうございます。それでできたのですが、今度は空白のセルまでカウントされてしまいました。空白と0以外というのはできるのでしょうか？