#div/0!と出てしまうセルを除いた、平均値を出す計算方法が知りたい

[to]

何も入ってないセルが計算され、#div/0!と出てしまう。そのセルを除いた、平均値を出す計算方法が知りたい

[sugasawa]

※OSとエクセルのバージョンを明記しましょう。


まず、あなたが作成した計算式を提示しましょう。
例えば、セルＡ１～Ａ３に入力された３つのデータの
平均を出すとき、

１．　＝（Ａ１＋Ａ２＋Ａ３）／３
２．　＝Ａ１／３＋Ａ２／３＋Ａ３／３
３．　＝ＳＵＭ（Ａ１：Ａ３）／３
４．　＝ＡＶＥＲＡＧＥ（Ａ１：Ａ３）

などなど、様々な方法で平均を求めることが出来ます。

何故このようなことを言うかというと、通常は、上に示した全ての
計算式において、Ａ１～Ａ３の間に空のセルが含まれていても、
エラーメッセージではなく平均値を返してくれるからです。

（ゼロを含めた全てのデータの個数による平均なのか、
ゼロを省いたデータの個数による平均なのか、という
違いはありますので、求める平均値と合致しているか
どうかは別問題です。）

「＃ＤＩＶ／０！」というエラーメッセージは、簡単に言えば
「ゼロで割っても答えはゼロにしかならないよ！」という
エラーですので、どこかでゼロで割るような格好に
なってしまっているのでしょう。

例えば、数式が
「＝（データの合計を求める数式）／（データの個数を求める数式）」
となっている場合、「データーの個数を求める数式」自体が間違って
いる、などが考えられます。

すがさわ
（ＷｉｎＸＰＨｏｍｅＳＰ２＆Ｅｘｃｅｌ２００２ＳＰ３）



"to" <***@discussions.microsoft.com> wrote in message news:296626FC-C756-405E-BDF7-***@microsoft.com...

[FCfun]

FCfunです。
横から失礼します。

Post by sugasawa
何故このようなことを言うかというと、通常は、上に示した全ての
計算式において、Ａ１～Ａ３の間に空のセルが含まれていても、
エラーメッセージではなく平均値を返してくれるからです。

本題とは関係ありませんが、確かに空のセルを含む場合には、計算されますが、
「#DIV/0!」が含まれている場合には、計算結果も「#DIV/0!」になりませんか？
ＯＦＦＩＣＥ２０００ ＳＲ１ＳＰ３で、AVERAGE関数を使用した場合には、
エラーになりますが…何か設定があるのでしょうか？

すがさわさんがかかれている様にこのエラーはゼロで除算しているために、
発生しているエラーですので、ゼロで除算しないように算式を工夫するか、
どうしてもゼロなる場合には、結果を空白やゼロににするなどで、
対応されてはいかがでしょうか？
私はあまり詳しくないのですが、if文とかを使用すると出来ませんか？

[kounoike]

茶々。

Post by sugasawa
「＃ＤＩＶ／０！」というエラーメッセージは、簡単に言えば
「ゼロで割っても答えはゼロにしかならないよ！」という

ちょっと，違うような気が・・・。

Post by sugasawa
エラーですので

[はぐれねこ]

たしかに分母が0の数字は0でなく、存在しませんね。
はぐれねこ

Post by kounoike
茶々。

Post by sugasawa
「＃ＤＩＶ／０！」というエラーメッセージは、簡単に言えば
「ゼロで割っても答えはゼロにしかならないよ！」という

ちょっと，違うような気が・・・。

Post by sugasawa
エラーですので

[sugasawa]

皆様、フォローありがとうございます。
要は、元投稿者さんが詳細な情報を提示してくれさえすれば
このグループの皆さんであれば簡単にかたがつく内容ぽいなと
思ったもので。
とはいえ元の投稿から推し量る限り、闇雲に「詳細を提示しろ」
と言っても、どこをどう提示してよいのやらわからないかな、とも
思い。
大雑把な考え方をつかんでもらうためにああいう書き方をして
みたのですが、効果があったのやら、なかったのやら。
元投稿者さんの反応待ちですね。

すがさわ

[to]

sugasawa様　ありがとうございます。
投稿が初めてなもので、自分が必要な部分のみ、勝手に書いてしまいました。
OSはｘｐとエクセルのバージョンは２００２です。
＝ＡＶＥＲＡＧＥ（Ａ１：Ａ３）のような式 で入力しています。
#div/0!の出ているセルの平均をだしたいのですが、どうすればいいのですか。

[sugasawa]

すがさわです。
失礼しました、質問の意味を理解できていなかったようです。

「#div/0!と出てしまうセルを除いた、平均値を出す計算方法が知りたい」
ということですので、FCfunさんがお書きになったように、例に挙げた
＝ＡＶＥＲＡＧＥ（Ａ１：Ａ３）で言うところのＡ１～Ａ３に該当する部分に、
計算式によって得られたデータがはいっており、そのデータのうち
いくつかはゼロで割られているために、”#div/0!”エラーが出ている
ということですね。

ということなので、ＦＣｆｕｎさんがフォローしてくださったように
ＩＦ関数を使って”#div/0!”エラーを回避する（空白にする）か、
はぐれねこさんがフォローしてくださったように平均値を
出す数式をＩF(iserror(average(・・・)),"",average(・・・))と言う風に
するか、という工夫が必要です。

今回の場合、はぐれねこさんの提示してくださった数式は
データの中に一つでもエラーがあれば空白を返しますから、
平均値を求めたい元のデータ（を求める数式）を工夫するのが
良いでしょう。

どのような数式で元のデータを求めてらっしゃるのか不明なので、
非常に遠まわしな表現になりますが、
元のデータを計算する際に、ゼロもしくは空白セルで割り算を
している部分があるのならば、その結果がゼロであったときには
空白を、そうでなければ結果をそのまま表示するようなＩＦ文を
組めばよろしいでしょう。

すがさわ

[はぐれねこ]

空のセルや、文字だけのセルばかりならば、averageを使うと#div/0!になりますね。
またその範囲に、一つでもエラーがある場合もエラーが出ますね。
そういう意味でなく、#div/0!を含む範囲のaverageを使いたいならば#div/0!が
でるセルをIF(iserror(average(・・・),"",average(・・・))とでも入れて、空
白表示にしてはどうでしょうか。ちなみに・・・は平均させたい範囲
そうすれば、#div/0!が無くなるので、数値が一つでもあれば、正しく表示され
そうですが。
はぐれねこ

Post by to
何も入ってないセルが計算され、#div/0!と出てしまう。そのセルを除いた、平均値を出す計算方法が知りたい

[to]

縦計・横計の１００個のセルの平均を出したいのですが。Ｋ1のセルに１０個のセルの平均　＝ＡＶＥＲＡＧＥ（Ａ１：Ｊ１）として入力しています。そのときにＫ１のセルに#div/0!と出てしまいます。

そして、Ｋ1と同じようにＫ２・Ｋ３・・・Ｋ１０とありそのＫ1～Ｋ10までの平均をＫ11に出したいのですが。#div/0!と出てしまうため、平均がでません。

ちなみに、数値の入力されていないセル（空白セル）もランダムに存在します。１００個のセル（Ａ1：Ｊ１)×（Ａ１：Ａ１０）には数値のみ入力です。1列にまったく数値の入力がない列も存在します。

IF(iserror(average(・・・),"",average(・・・))に・・・の平均させたい範囲を入れたんですが、うまく動きませんでした。”入力した数式は正しくありません。”と表示されました。わたしの入力方法が違っていたのかもしれませんが、両方平均させたい範囲をいれましたけど。

質問の内容が自分の頭の中でのことなので、うまくまとめられませんが、よろしくお願いします。

すがさわ様　FCfun様　ｋｏｕｎｏｉｋｅ様　はぐれねこ様　ありがとうございます。

[to]

もう一度入力しましたら、できました。
IF(iserror(average(・・・),"",average(・・・))で数式を立てたらできました。
　すがさわ様　FCfun様　ｋｏｕｎｏｉｋｅ様　はぐれねこ様　本当にありがとうございました。

[kounoike]

Post by to
もう一度入力しましたら、できました。
IF(iserror(average(・・・),"",average(・・・))で数式を立てたらできま

した。

Post by to
　すがさわ様　FCfun様　ｋｏｕｎｏｉｋｅ様　はぐれねこ様　本当にありが

とうございました。
えっと，上にあるkounoikeって者なのですが，私がしたことと言えばすがさわさ
んの記事に大した内容でもない茶々を入れただけなんで，このように書かれると
ちょっと戸惑いますが。

ところで，別のやり方としてちょっと手間ですがこんなのはどうでしょうか。
オートフィルタを使用するためヘッダが必要なので，先頭の行に一列行を挿入し
て，Ｊ１に適当に平均とでも入れておく。

次に，K2に
=AVERAGE(A2:J2)
といれ，このセルをK11まで，ドラッグ等でコピーする。
次にK12に
=SUBTOTAL(1,K2:K11)
と入れる。（K2からK11までの平均を意味します。）
セルK1を選択し，オートフィルタを実行。オプションを選んで，抽出条件欄に
#DIV/0!と”と等しくない”を選択し，ＯＫボタンを押す。以上で終わり。空白
行に値を入れるときは，フィルタを解除し入力。

[kounoike]

Post by kounoike
オートフィルタを使用するためヘッダが必要なので，先頭の行に一列行を挿入

し

Post by kounoike
て，Ｊ１に適当に平均とでも入れておく。

すいません。Ｊ１はK1の書き間違いです。

[はぐれねこ]

IF(iserror(average(・・・)),"",average(・・・))
と表示されているでしょうか。かっこ一つ忘れですみません。

ただ、ふと思ったのですが、K11に、average(・・・,・・・,・・・)と私なら入
力しそうです。結果はそう変わりないように思いますが。
はぐれねこ

Post by to
もう一度入力しましたら、できました。
IF(iserror(average(・・・),"",average(・・・))で数式を立てたらできました。
　すがさわ様　FCfun様　ｋｏｕｎｏｉｋｅ様　はぐれねこ様　本当にありがとうございました。

[sugasawa]

すがさわです。
解決との事、なによりです。
結果オーライなのですが、ちょっと確認させてください。

Post by to
ちなみに、数値の入力されていないセル（空白セル）もランダムに存在します。
１００個のセル（Ａ1：Ｊ１)×（Ａ１：Ａ１０）には数値のみ入力です。
1列にまったく数値の入力がない列も存在します。

とのことなのですが、「行」の場合はどうでしょう？
ＡＢＣ・・・の横方向に増えていくのが「列」で
１２３・・・の縦方向に増えていくのが「行」なわけですが、

１行目（（Ａ１：Ｊ１）の範囲）が全て空欄なのであれば、
それが原因ということなので、しっくりくるのですが。


あとは細かいことなのですが、１００個のまとまったセル範囲を
示す場合であれば、（Ａ１：Ｊ１０）とするのが一般的です。
もうひとつ、=if(iserror(average(・・・)),"",average(・・・))が正しいです。
示していただいた数式では、閉じカッコが一つ足りませんので、
エラーが出たものと思われます。

すがさわ


"to" <***@discussions.microsoft.com> wrote in message news:611CF7B1-EA62-482B-AB3D-***@microsoft.com...

[はぐれねこ]

Post by sugasawa
もうひとつ、=if(iserror(average(・・・)),"",average(・・・))が正しいです。
示していただいた数式では、閉じカッコが一つ足りませんので、
エラーが出たものと思われます。

失礼しました。全くその通りでございます。
はぐれねこ

[T. Sugita]

$B=D7W!&2#7W$N#1#0#08D$N%;%k$NJ?6Q$r=P$7$?$$$N$G$9$,!#(B
$B#K(B1$B$N%;%k$K#1#08D$N%;%k$NJ?6Q!!!a#A#V#E#R#A#G#E!J#A#1!'#J#1!K$H$7(B
$B$FF~NO$7$F$$$^$9!#$=$N$H$-$K#K#1$N%;%k$K(B#div/0!$B$H=P$F$7$^$$$^$9!#(B
$B$=$7$F!"#K(B1$B$HF1$8$h$&$K#K#2!&#K#3!&!&!&#K#1#0$H$"$j$=$N#K(B1$B!A#K(B10
$B$^$G$NJ?6Q$r#K(B11$B$K=P$7$?$$$N$G$9$,!#(B#div/0!$B$H=P$F$7$^$&$?$a!"(B
$BJ?6Q$,$G$^$;$s!#(B

$B$A$g$C$HLLGr$=$&$@$C$?$N$G!"JL2r$r!#(B

1) 0$B0J>e!"(B0$BL$K~$r>r7o$H$7$?>l9g(B
=(SUMIF(K1:K10,">=0",K1:K10)+SUMIF(K1:K10,"<0",K1:K10))/COUNT(K1:K10)

2) $BG[Ns?t<0$r;HMQ(B
=AVERAGE(IF(ISNUMBER(K1:K10),K1:K10,"")) Ctrl+Shift+Enter