配列数式で平均を出すと空欄が０で計算されてしまう

質問

A　B　C　D
1　　1　2　1　{=AVERAGE(IF($A$1:$A$4=$C1,B$1:B$4))}
2　　1　4　2　{=AVERAGE(IF($A$1:$A$4=$C2,B$1:B$4))}
3　　2　6
4　　2　8

上記のような数値、数式ですと、
B列すべてに数値が入力されているため、問題なく計算するのですが、
例えば、B2のセルを空欄にすると、空欄を０としてしまい、
D1の計算結果が１となってしまいます。

D1の数式を=AVERAGE(B1:B2)としますと、空欄は空欄として扱い、
計算結果は２となります。

配列数式を使った場合にも、空欄を空欄として扱い、
計算結果が２となるような方法はありませんでしょうか。

よろしくお願いします。

回答No.3

mshr1962 · ベストアンサー

#1です。IFをネストにしないでも
{=AVERAGE(IF(($A$1:$A$4=$C1)*(B$1:B$4<>""),B$1:B$4))}
または
{=AVERAGE(IF(($A$1:$A$4=$C1)*ISNUMBER(B$1:B$4),B$1:B$4))}
でB列の空白を除去できます。

お礼

ありがとうございます。
試してみます！

補足

どちらの方法も、「０」と空欄を区別してくれます。
ありがとうございました。

回答No.4

Wendy02

こんばんは。

あえて配列数式にすることもないのでは？
実務では、配列数式でなければ解けないもの(そういう場合はVBAでも難しいことがある）の時にだけ、配列数式を使うようにしたほうがよいと思います。

=SUMIF($A$1:$A$4,C1,$B$1:$B$4)/COUNTIF($A$1:$A$4,C1)

お礼

回答ありがとうございます。
おっしゃるとおり、無理して配列数式を使うこともないのですが…。
この方法も試させていただきます。
数式的にもすっきりした感じです。

補足

試してみましたが、教えていただいた数式では、
B2が空欄の時に「０」として計算してしまい、
D1の計算結果が「１」になってしまいました。

回答No.2

mu2011

一例です。
{=AVERAGE(IF($A$1:$A$4=$C1,IF($B$1:$B$4>0,$B$1:$B$4)))}
又は、
=SUMPRODUCT(($A$1:$A$4=C1)*($B$1:$B$4))/SUMPRODUCT(($A$1:$A$4=C1)*($B$1:$B$4>0))

お礼

解決しました！ありがとうございます。
どちらの方法も確認しました。
今回は配列数式を使ってみたいので、上の式でやりたいと思います。
本当にありがとうございました。

なぜそうなるかは、落ち着いてからしっかり考えたいと思います。

補足

この数式ですと、Ｂ列が「０」だった場合も計算してくれなくなります。

「０」と空欄を区別したいという趣旨が
全く伝わらないような質問文でした。
すみませんでした。

回答No.1

mshr1962

その数式だと結果は３ではないのですか？
式の後半がB$1:B$4でなくA$1:A$4になってませんか？

お礼

補足を間違えていました。
少々焦っております。
　　A　B　　C　D
1　　1　2　　1　{=AVERAGE(IF($A$1:$A$4=$C1,B$1:B$4))}
2　　1　""　 2　{=AVERAGE(IF($A$1:$A$4=$C2,B$1:B$4))}
3　　2　6
4　　2　8

この時のD1の結果を「２」になるようにしたいんです。

補足

ご指摘ありがとうございます！
D1の計算結果は×「２」→○「３」です。
書き間違えました。