• ベストアンサー

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

エクセルで、0を除いた平均を、 {=AVERAGE(IF(A1:A10<>0,A1:A10,""))}の数式を使って求めた場合、 データがすべて0の列の平均はエラー値になってしまいます。 すべてが0の場合は0と表示させたいのですが、どのような方法がありますでしょうか? 1つの計算式を使って、上記の2つの処理は可能でしょうか?

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

  • ベストアンサー
  • losedog
  • ベストアンサー率66% (22/33)
回答No.5

エラー値の場合に,0と表示する計算式ですが,いかがでしょうか? =IF(ISERROR(AVERAGE(IF(A1:A10<>0,A1:A10,""))),0,AVERAGE(IF(A1:A10<>0,A1:A10,"")))

sanabonn
質問者

お礼

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

その他の回答 (4)

回答No.4

平均ってのはデータの個数で割り算をするので、「範囲内のすべて のセルが空白」の場合はデータの個数が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) とやるわけです。

sanabonn
質問者

お礼

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

  • Hachi5592
  • ベストアンサー率36% (252/698)
回答No.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では空白の扱いがセルの指定方法で変わってきます。

sanabonn
質問者

お礼

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

  • hallo-2007
  • ベストアンサー率41% (888/2115)
回答No.2

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

sanabonn
質問者

お礼

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

  • xls88
  • ベストアンサー率56% (669/1189)
回答No.1

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

sanabonn
質問者

お礼

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

関連するQ&A

  • 配列数式での平均値

    こんにちは。 今、エクセルで配列数式を使って平均値を出す作業をしています。 列Aには男女別を示す1、2が入力されており、 列B以降にはいろいろなデータが入っています。 それらデータにおける男女別の平均値を出したいので 配列数式を使って求めるまでは良かったのですが、 どうやら、列B以降の空欄になっているセルも 0として計算されてしまっているようなのです。 (普通のaverageと同様に)空欄を数に入れないで計算させるには どのようにしたらよいのでしょうか? ちなみに今使っている配列数式は、 {=average(if(A1:A10=1,B:B10,""))} です。

  • エクセルにて平均値を表示したいけど?

    エクセルにて平均値を表示したいけど? その方法が分かりませんので宜しくお願いします。 月別売上金の平均値を表示させたいのですが、 A列から1月B列2月順次L列12月としてM列に其の月までの平均値を表示させたいのですが 該当月までの平均値をIF関数にてできますでしょうか?  現在はAVERAGEの数式を該当月の列に置き換えて利用していますが 各月ごとに数値を入力すれば即該当月までの平均値が表示できればと思います。  尚 現在は年間分入力用書式を作成されて月末に数値を入力、数式を変える、作業をしています。 OSはオフィス2007を使用してます。 宜しくお願いします。

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

    会社でチームの4月~来年3月までの残業時間の平均をExcelで取っています。 数式は私が組んだのではないのですが、変更を求められ色々調べてみたのですがどうやっても上手くいかないので皆様の知恵をお貸しください。 (1)指定範囲内にゼロが含まれている場合、平均計算にゼロは入れず、割る数は入力されている数だけにする (例)計算範囲は12月までの12セル分指定でも、8月までしか時間が入力されていない(9月~12月はゼロ)場合は÷5(5ヶ月)として計算する (2)平均の結果がゼロである場合、「0」と表示すること この2点を組み込むように指示され、最初から組み込まれている平均数式は(1)の数式が入れられています。 (1)の数式 =AVERAGE(IF(セル範囲<>0,セル範囲,"")) しかしこのままでは平均結果がゼロの場合「DIV/0!」と表示されてしまいます。 チームの中には残業していなくて4月からずっとゼロの人も居ます。 その場合(1)の計算では数式エラーが発生します。 (2)の「結果がゼロの場合0と表示する」という条件に合う数式を自分なりに調べてみました。 =IF((COUNT(セル範囲)=0),"",AVERAGE(セル範囲)) この場合、ずっとゼロの人の場合でも結果はキチンと「0」と表示させることができます。 しかしこの数式にしてしまうと(1)の条件にある「割る数は入力されている数だけにする」の設定に反しすべて÷12にしてしまうのです。 じゃあ元々入力されていた(1)の数式と、自分で調べた(2)の数式を上手く組合せばいいんじゃないか?と色々試行錯誤してみたのですが、Excelの数式はちょびっとかじった程度なので、どう組み替えていけばいいのか分からずエラーばかり出ます。 (1)と(2)の条件をすべて呑んだ数式があれば教えてください! お願いします! ※ちなみに※ 文中に「ずっと残業時間ゼロの人が・・・」と書いていますが、来年の3月までゼロというワケではありません。 いつ残業が発生するのか分かりませんので、その人だけ別の数式、とかではなく全員同じ平均数式で(1)(2)の条件に合うものをお願いします!

  • エクセルの平均値を出す時に困ってます

    家計簿でそれぞれの平均をエクセルで出そうとしているんですが A~G列の列ごとに出したいのですが  ABCD・・・ (1)245 (2)4 7 (3) 54 (4)578 (5)68 上記のように列に空白があり、そのままaverageで平均を出すと 空白も計算してしまい、正確な平均が出ないので困っています 曜日ごとにつけていますので、どうしても空白が出来てしまうですが このような場合に平均値を正確に出す時になにか良い、計算方法は ないでしょうか?

  • EXCELの平均を出す関数について

    初歩的な質問となりますが、 どうぞよろしくお願いいたします。 sheet1に下記のようなデータがあります。   A    B    D 1 日付  売上  平均 2 7/1   200    3 7/2   100   =AVERAGE(B2:B3) 4 7/3   300   =AVERAGE(B2:B4) 5 7/4   200   =AVERAGE(B2:B5) 6 7/5         ・ 7 7/6         ・ ・  ・          ・ ・  ・          ・ ・  ・          ・ 32 7/31        =AVERAGE(B2:B5) 現在B6までデータが入力されています。 D列に今日までの平均を表示したいのですが、 上記のようなAVERAGE関数をD32まで先に埋めてしまった場合、 B6:B32のあす以降の計算結果も出てしまいます。 B列のセルに入力がない限り、 今日の日付以降を計算しない(空欄)ようにする方法はありますか? よろしくお願いいたします。

  • 配列数式? 中央値 平均値

    いつもお世話になっております。 早速ですが以前質問させていただいた内容からも発展というか 修正というかです。 http://oshiete1.goo.ne.jp/qa4561210.html 当初、配列数式にて =IF(COUNTIF($A$2:$A$33930,A2)=1,b2,AVERAGE(IF($A$2:$A$33930=a2,$B$2:$B$33930)))} を使用しておりましたが、 = SUMIF($A$2:$A$33930,A2,$B$2:$B$33930)/COUNTIF($A$2:$A$33930,A2) でも、同一の回答が得られまた処理も大変早く行われるため 非常に助かっておりました。 しかし、最近になって元データ側に不具合あり 平均値ではターゲットとする値から離れていることが発覚しました。 そのため、平均値から中央値 最頻値 のどちらかと思っているのですが 同一データが2つ無い場合もあるため最頻値ではエラーになると思い 中央値で求めたいと思っています。 データ量が6000行を超える場合もあり 配列数式を使って計算するべきなのか、他に良い方法があるのか教えて頂きたく よろしくお願いします。 エクセル2003です

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

      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のセルを空欄にすると、空欄を0としてしまい、 D1の計算結果が1となってしまいます。 D1の数式を=AVERAGE(B1:B2)としますと、空欄は空欄として扱い、 計算結果は2となります。 配列数式を使った場合にも、空欄を空欄として扱い、 計算結果が2となるような方法はありませんでしょうか。 よろしくお願いします。

  • エクセルグラフで0が表示される

    いつものことですみません。 教えてください。 エクセルで折れ線グラフを描画しましたところ、一番最後の表示が0になってしまうのです。 A列に日付(時間)、B列に数値データがリアルタイムに入ってくるので最後の行を特定できません。適当に広い範囲を指定しています。 B列の数値データの平均値(複数)を計算してC列(3平均値)・D列(5平均値)・E列(9平均値)に置いています。 ちなみにセルC3は、=IF(B3="","",AVERAGE(B1:B3))です。 見た目には、なにも表示がなく、0は入っていないのですが・・・ お忙しいとは存じますが、なにとぞよろしくお願いいたします。

  • エクセルで3列おきの平均値を

    こんにちは! エクセルで、3列おきのセルの値の平均値を出したいのですが、値がエラー(#N/Aと表示されている)場合は、平均を求める計算の中に含めないようにしたい・・・というのはどうやって設定したらよいのか、お教えください! 関数等、色々試して見たのですが、よくわかりませんでした・・・。どうぞどうぞ宜しくおねがいたします!

  • EXCELで「月平均10」と表示させる。

    まず、 =AVERAGE(B18:K18) という式をEXCELで作りました。 答えは「10」なので、セルには「10」が入りました。 これはうまくいきました。 次に「月平均10」と表示させたいので ”月平均”=AVERAGE(B18:K18) =AVERAGE(B18:K18):”月平均” など、いろいろしましたが、うまくいきません。 どのようにしたら、1つのセルに中に計算結果と文字を入力できるのでしょうか? よろしくお願いします。

専門家に質問してみよう