Excel平均数式でゼロ除外+結果ゼロ表示

質問

会社でチームの４月～来年３月までの残業時間の平均をExcelで取っています。
数式は私が組んだのではないのですが、変更を求められ色々調べてみたのですがどうやっても上手くいかないので皆様の知恵をお貸しください。
(1)指定範囲内にゼロが含まれている場合、平均計算にゼロは入れず、割る数は入力されている数だけにする
（例）計算範囲は１２月までの１２セル分指定でも、８月までしか時間が入力されていない（９月～１２月はゼロ）場合は÷５（５ヶ月）として計算する
(2)平均の結果がゼロである場合、「0」と表示すること
この２点を組み込むように指示され、最初から組み込まれている平均数式は(1)の数式が入れられています。

(1)の数式
=AVERAGE(IF(セル範囲<>0,セル範囲,""))

しかしこのままでは平均結果がゼロの場合「DIV/0!」と表示されてしまいます。
チームの中には残業していなくて４月からずっとゼロの人も居ます。
その場合(1)の計算では数式エラーが発生します。

(2)の「結果がゼロの場合0と表示する」という条件に合う数式を自分なりに調べてみました。
=IF((COUNT(セル範囲)=0),"",AVERAGE(セル範囲))

この場合、ずっとゼロの人の場合でも結果はキチンと「0」と表示させることができます。
しかしこの数式にしてしまうと(1)の条件にある「割る数は入力されている数だけにする」の設定に反しすべて÷１２にしてしまうのです。

じゃあ元々入力されていた(1)の数式と、自分で調べた(2)の数式を上手く組合せばいいんじゃないか？と色々試行錯誤してみたのですが、Excelの数式はちょびっとかじった程度なので、どう組み替えていけばいいのか分からずエラーばかり出ます。

(1)と(2)の条件をすべて呑んだ数式があれば教えてください！
お願いします！

※ちなみに※
文中に「ずっと残業時間ゼロの人が・・・」と書いていますが、来年の３月までゼロというワケではありません。
いつ残業が発生するのか分かりませんので、その人だけ別の数式、とかではなく全員同じ平均数式で(1)(2)の条件に合うものをお願いします！

回答No.2

dodemoii · ベストアンサー

こんにちは

先ずゼロ除外については下記URL参照
http://www.geocities.jp/chiquilin_site/data/070612_zero-less_average.html
で例として=SUM(A2:A11)/COUNTIF(A2:A11,"<>0")
としますと「DIV/0!」を消すためにISERRを使って
=IF(ISERR(SUM(A2:A11)/COUNTIF(A2:A11,"<>0")),0,SUM(A2:A11)/COUNTIF(A2:A11,"<>0"))
でどうでしょうか?
でも何か面倒ですね。
簡単なのは、条件付書式で「DIV/0!」になったら、白い字に
して見えなくしてしまう方法かと思います。

では。

お礼

貼り付けて頂いた参照ページはまさに知りたい内容のことが書かれていたので大変勉強になりました！

=IF(ISERR(SUM(A2:A11)/COUNTIF(A2:A11,"<>0")),0,SUM(A2:A11)/COUNTIF(A2:A11,"<>0"))
教えていただいたこちらの式で見事に解決できました！

「DIV/0!」表示の場合、空白ではなくゼロと表示するように。という指示だったので条件付書式は使わず、教えていただいた式を使っていこうと思います。

こんなすぐに解決するとは思わなかったので本当にビックリです！
有難うございました！
とても感謝しています！！！

回答No.3

piro19820122

すいません。投稿後に気付いたのですが、Excelのバージョンはいくつでしょうか？
AVERAGEIF関数は2007以降でしか使えない関数でした。失礼しました。

ただ、内容としてはSUMIF/COUNTIFで代用できると思いますので、置き換えてください。

お礼

肝心な情報を、質問されるまで書き忘れていたことに気づきませんでした！
会社のＰＣなのでExcel2003です。

一番最初に(2)の条件に合う数式を調べた時
=IF((COUNT(セル範囲)=0),"",AVERAGE(セル範囲))
という数式だったので、COUNTとAVERAGEをどう組合すか？ということばかり考えてました。
SUMIF/COUNTIFで考えていく方法があったのですね。。。

2003と2007で使える関数に差があるみたいで、業務上これから先もちょくちょく関数とぶつかることになりそうです。
今回はとても勉強になりました！
ありがとうございます！

回答No.1

piro19820122

残業時間がゼロの場合に、空白なのかゼロなのかによって変わってくると思います。
最初に挙げられている数式では、空白だとご要望の機能になりますが、ゼロが入力されていると平均に加えられてしまいます。

=AVERAGEIF(範囲,">0")
これだと空白もゼロもカウントに入りません。

これに、「平均がゼロの場合」の処理を加えます。
平均がゼロということは、合計もゼロ（残業時間に負数はないという前提で）ですので、SUM(範囲)がゼロかどうかを判定します。

=IF(SUM(範囲)>0,AVERAGEIF(範囲,">0"),0)
という式でいかがでしょうか？

お礼

残業時間がゼロの場合、空白ではなく「0.00」と表示しています。（小数第二位まで表示）

=IF(SUM(範囲)>0,AVERAGEIF(範囲,">0"),0)
の場合、範囲がすべてゼロなら「0.00」と表示してくれますが、ゼロ以外の数字が入ると「#NAME?」という表示になりました。

">0"　で空白もゼロもカウントに入らない指定になるとは知りませんでした。
一つ勉強になりました！
有難うございます！！