こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

1つの計算式を使って、上記の2つの処理は可能でしょうか?

投稿日時 - 2008-10-27 22:55:37

QNo.4435039

困ってます

質問者が選んだベストアンサー

エラー値の場合に,0と表示する計算式ですが,いかがでしょうか?

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

投稿日時 - 2008-10-28 12:39:17

お礼

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

投稿日時 - 2008-10-29 22:30:20

ANo.5

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(5)

平均ってのはデータの個数で割り算をするので、「範囲内のすべて
のセルが空白」の場合はデータの個数が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-28 10:25:58

お礼

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

投稿日時 - 2008-10-29 22:24:25

ANo.3

=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-27 23:29:23

お礼

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

投稿日時 - 2008-10-28 00:18:13

ANo.2

全て 0 のときは上記の式では全て空白ですのでエラーになりますよね。
全て 0 のときの平均値は 0 ですよね。
=IF(SUM(A1:A10)=0,0,式)
Ctrl+Shift+Enterではダメだったでしょうか。

投稿日時 - 2008-10-27 23:29:11

お礼

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

投稿日時 - 2008-10-28 00:11:44

ANo.1

下記でどうでしょうか。
=IF(SUM(A1:A10)=0,0,SUMIF(A1:A10,"<>0")/COUNTIF(A1:A10,"<>0"))

投稿日時 - 2008-10-27 23:23:53

お礼

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

投稿日時 - 2008-10-28 00:05:22

あなたにオススメの質問