エクセルで０と空白を除外した平均値を算出させたい

質問

Excel 2007 を使用している者です

添付画像のような表で・・・

１）０と空白を除外した平均値を算出して B15～E15 に表示させたい
２）#DIV/0! や #VALUE! エラーを０に置き換えて表示させたい

これらの事を行いたく、ネット検索などして試行錯誤を繰り返してますが
なかなか思い通りにいかず質問させて頂きました。
当方、SUM、AVERAGE等の基本的な使い方は理解できる程度の知識レベル・・・
ですのでどなたかご教示よろしくお願い致します。

回答No.1

bunjii · ベストアンサー

>１）０と空白を除外した平均値を算出して B15～E15 に表示させたい
下記の数式で良い。
=AVERAGEIF(範囲,条件,[平均対象範囲])
B15=AVERAGEIF(B4:B14,">0")
[平均対象範囲]は範囲と同じなので省略しました。

>２）#DIV/0! や #VALUE! エラーを０に置き換えて表示させたい
AVERAGE関数（値）の外側へIFERROR関数を被せればエラー時のとき0を代入できる。
=IFERROR(値,エラーの場合の値)
B15=IFERROR(AVERAGEIF(B4:B14,">0"),0)
B15を右へオートフィルコピーすれば良い。

お礼

ご教示の通りやってみて思い通りの形になりました。
関数式もシンプルでしたのでこちらを
ベストアンサーとさせて頂きますね！？
今回は有り難う御座いました。

回答No.5

tsubu-yuki

お答えする前に確認しておきたいこと・・
> ２）#DIV/0! や #VALUE! エラーを０に置き換えて表示させたい
コレ、どの範囲（セル）に対するものでしょうか？
まぁ、おそらくは
> １）０と空白を除外した平均値を算出して B15～E15 に表示させたい
この範囲についてのことでしょうけども。

ということで↑を前提にして、
質問文から読み取れる「やりたいこと」を実現するには
例えば
　　Ｂ１５セル：=IFERROR(AVERAGEIF(B4:B14,"<>0"),0)
で「範囲内の（空白を含む）０以外の数値」の平均を求め、
範囲内に対象となる数値が無いときには０を返します。
おそらくこれで充分行けるかと。

関数については
> ネット検索などして試行錯誤を繰り返して
くださいませ。

お礼

ご教示頂いた関数式で十分行けました。
有り難う御座いました。
他にも色々やり方があるようなので
引き続き色々試行錯誤してみます。

回答No.4

Chiquilin

> １）０と空白を除外した平均値を算出
２）の対処が済んでたら こんな計算そもそも必要ないのでは？
=SUMIF(B4:B14,"<1E15")/COUNTIFS(B4:B14,"<1E15",B4:B14,"<>0")

> ２）#DIV/0! や #VALUE! エラーを０に置き換えて表示
ジャンプ機能で エラー値を一括で選択して そこに「0」を入力すれ
ばいいと思います。

一括入力は選択した状態で数式バーに「0」と入れて Ctrl + Enter。

回答No.3

msMike

[回答No.1]へのコメント、
この場を借りて、bunjiiさんにお尋ねします。

》 ０と空白を除外した平均値を…
》 下記の数式で良い。
》 B15=AVERAGEIF(B4:B14,">0")
この式は「０と空白」だけでなく、負数も除外していると理解しています。
「０と空白を除外」して負数は含めたいときはどうすればよろしいのでしょうか？
教えてください。

回答No.2

imogasi

参考までに書いてみます。（ビミョーな点があるようです。加算器の加算合計／・件数カウントで筆算チェックも使って慎重に）
ーー
多数のセルを使って計算して、結果（合計、件数、平均、最大最少など。集合関数というタイプ）を出すには
関数で、（セルの値によって）除外するデータがある場合は
（１）関数名に「条件」を折り込み済みのもの
ＣｏｕｎｔＢｌａｎｋ、ＣＯＵＮＴＡの様な関数のようなもの。
しかしＮＯＮＢＬＡＮＫやＮＯＮＺＥＲＯは、、すべての集合関数にあるわけでない。
（１）’関数の引数に取り入れられているもの
WORKDAY(開始日, 日数, [祝日]）での「祝日」除きのようなもの。
（２）関数名に○○ＩＦがついていて、「条件の引数」を予定していて、引数で指定するので
そこに指定するもの
（３）２００７以後では「〇○ＩＦＳ」タイプの複数条件を指定できる関数
（４）比較的自由に、条件が入れられるところの、「配列数式」で、ＩＦを使い、
ＡＮＤ、ＯＲなども　使える配列数式
関数ではないが、
（５）ＶＢＡで計算するもの
（６）実質ＶＢＡだが、ユーザー定義関数を作るもの。
（７）その他　　今思い付かないが
などがある
＝＝＝
注意
http://www.moug.net/tech/exopr/0090041.html
＞エクセルにとって０は数であり（テストで０点の人は起こりえるが）、０を特別視して除外するには、
=SUM(平均対象範囲)/(COUNT(平均対象範囲)-COUNTIF(平均対象範囲,0)) を入力します。
ちなみに
配列数式でやろうとして
=AVERAGE(IF(OR(A2:A9<>0,A2:A9<>""),A2:A9))と入れて
ＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲでは、分母の件数にあたる数に０分件数が含まれてしまうようだ。
この点小生の間違いや認識不足が、あるかもしれないが。
ーー
さらに
＞）#DIV/0! や #VALUE! エラーを０に置き換えて表示させたい
http://www.cando.co.jp/column/column_13.html
上記サイト記事は空白にする例だが、””の部分を０に置き換えればよいでしょう。

お礼

色々なやり方があるんですね～
少し時間がかかりますがじっくり試してみたいと思います。
今回はご回答下さり有り難うございました。