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

　　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 回答日時： 2008/09/24 16:42

#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列の空白を除去できます。

この回答への補足

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

補足日時：2008/09/25 13:10

この回答へのお礼

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

お礼日時：2008/09/24 18:32

No.4 回答者： Wendy02 回答日時： 2008/09/24 20:41

こんばんは。

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

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

この回答への補足

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

補足日時：2008/09/25 11:24

この回答へのお礼

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

お礼日時：2008/09/25 09:07

No.2 回答者： mu2011 回答日時： 2008/09/24 16:24

一例です。

{=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))

この回答への補足

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

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

補足日時：2008/09/25 13:08

この回答へのお礼

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

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

お礼日時：2008/09/24 16:35

No.1 回答者： mshr1962 回答日時： 2008/09/24 16:04

その数式だと結果は３ではないのですか？

式の後半がB$1:B$4でなくA$1:A$4になってませんか？

この回答への補足

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

補足日時：2008/09/24 16:06

この回答へのお礼

補足を間違えていました。
少々焦っております。
　　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の結果を「２」になるようにしたいんです。

お礼日時：2008/09/24 16:13