エクセルで０を除く平均値と０の平均値を同時に表示させる方法について

エクセルで、０を除いた平均を、
｛=AVERAGE(IF(A1:A10<>0,A1:A10,""))｝の数式を使って求めた場合、
データがすべて０の列の平均はエラー値になってしまいます。

すべてが０の場合は０と表示させたいのですが、どのような方法がありますでしょうか？

１つの計算式を使って、上記の２つの処理は可能でしょうか？

No.5 ベストアンサー 回答者： losedog 回答日時： 2008/10/28 12:39

エラー値の場合に，０と表示する計算式ですが，いかがでしょうか？

=IF(ISERROR(AVERAGE(IF(A1:A10<>0,A1:A10,""))),0,AVERAGE(IF(A1:A10<>0,A1:A10,"")))

この回答へのお礼

ありがとうございます！エラー値にならず、目からうろこです。配列数式を使うべきかsumproduct関数を使うべきか、どちらが効率がよいか悩みます。
同じような内容で最大値最小値の新たな質問をしておりますので、よろしければ、ご指導ください。ありがとうございました。

お礼日時：2008/10/29 22:30

No.4 回答者： grumpy_the_dwarf 回答日時： 2008/10/28 10:25

平均ってのはデータの個数で割り算をするので、「範囲内のすべて

のセルが空白」の場合はデータの個数が0になり、「ゼロで割り算す
るんじゃねぇ」って怒られますね。

「文字列や空白が混在した範囲内の特定の欠損値以外の数値の数」
を数えることができれば、そしてそれがゼロでない時だけ平均値を
計算するようにすれば、問題は解決します。

=sumproduct(isnumber(A1:A10)*(A1:A10<>欠損値))
これが配列数式を使わずに上記の条件で数値データの数を数えてい
るところ。欠損値は0でも-99999でもお好きに。同様に合計は
=sumproduct((A1:A10<>欠損値)*1,A1:A10)

で、有効なデータの個数が0のときに割り算しないために、たとえば
if(sumproduct(isnumber(A1:A10)*(A1:A10<>欠損値)),
　sumproduct((A1:A10<>欠損値)*1,A1:A10)/sumproduct(isnumber(A1:A10)*(A1:A10<>欠損値)),
　0)
というふうにやったり、もっとシンプルに
=sumproduct((A1:A10<>欠損値)*1,A1:A10)/max(sumproduct(isnumber(A1:A10)*(A1:A10<>欠損値)),1)
とやるわけです。

この回答へのお礼

非常にわかりやすい説明をいただき、ありがとうございます！使用したことがない関数で理解するのに時間がかかりお礼が遅くなりすみません。配列数式を使わなくてもできるのですね。
同じような内容で最大値最小値に関する質問を新たにしておりますので、よろしければ、ご指導ください。ありがとうございました。

お礼日時：2008/10/29 22:24

No.3 回答者： Hachi5592 回答日時： 2008/10/27 23:29

=IF(SUM(A1:A10)=0,0,AVERAGE(A1:A10))

=IF(SUM(A1:A10)=0,0,AVERAGE(A1,A2,A3,A4,A5,A6,A7,A8,A9,A19))
データのすべてがゼロの時というのであれば、上記IFとSUM関数を使います。上側の式はA1～A10に空白がある場合に平均に含めない場合で、下側は空白をゼロとして平均に含める場合です。
EXCELでは空白の扱いがセルの指定方法で変わってきます。

この回答へのお礼

ありがとうございます。空白がある場合は計算式が変わるのですね、参考になりました。

お礼日時：2008/10/28 00:18

No.2 回答者： hallo-2007 回答日時： 2008/10/27 23:29

全て　0　のときは上記の式では全て空白ですのでエラーになりますよね。

全て　0　のときの平均値は　0　ですよね。
=IF(SUM(A1:A10)=0,0,式)
Ctrl+Shift+Enterではダメだったでしょうか。

この回答へのお礼

ありがとうございます。=IF(SUM(A1:A10)=0,0,AVERAGE(IF(A1:A10<>0,A1:A10,""))でできました。０が特定のマイナス値になった場合ではエラー値が出てしまいます。マイナス値でもエラーが出ない式がありましたら、教えていただけるとうれしいです。

お礼日時：2008/10/28 00:11

No.1 回答者： xls88 回答日時： 2008/10/27 23:23

下記でどうでしょうか。

=IF(SUM(A1:A10)=0,0,SUMIF(A1:A10,"<>0")/COUNTIF(A1:A10,"<>0"))

この回答へのお礼

ありがとうございましたｍ－－ｍ。できました。教えていただいた式を、特定のマイナス値（-99999）を除くよう応用してみたところ、すべてが-99999の場合にエラー値になってしまいました。もし、マイナス値でもエラーにならないような処理がありましたら、教えていただけると嬉しいです。

お礼日時：2008/10/28 00:05