エクセルの曜日毎の平均値を出すのに未入力の分まで計算されてしまい困ってます

エクセルで曜日毎の平均値を出したいのですが、


①未入力の曜日も計算してしまって実績の平均値が出ない（例：月曜が5回ある月で月曜の欄に3週分しか入力していないのに5で割られてしまう）

　上記の計算式は、

=SUMIF(B4:B34,"月",C4:C34)/COUNTIF(B4:B34,"月")

　以上で回答できる説明になっているか分かりませんが、必要であれば回答に必要な私が提供すべき情報も含めてご教示頂ければ幸甚に存じます。

質問者からの補足コメント

• すみません！上記公式はコピペしたら変わってます。
  
  　正確には、
  
  　=sumif(b4:b34,"月",c4:c34)/countif(b4:b34,"月")
  
  上記の計算式の補足ですが、aの列に日付、bの列に曜日、cの列に総売上が入力になります。

    補足日時：2015/08/04 21:49

• 

  もう大丈夫と思っていたところに思わぬ伏兵が潜んでいました（泣）
  
  曜日毎の平均値を出す欄に例えば、
  
  =averageif(b4:b34,"火",c4:c34)
  
  上記の式が入っているのですが、曜日毎の平均値を出す計算式の上に日毎の数値を入力する部分が、入力されていないと#div/0!　と出てしまいます。
  
  この場合下記の式をどのように修正したら#div/0!が出なくなるか教えて頂ければ幸いです。
  =averageif(b4:b34,"火",c4:c34)

    補足日時：2015/08/05 02:56

No.11 回答者： Saturn5 回答日時： 2015/08/05 17:24

ここは、目的に合った計算式を他人に聞いていては力がつきません。

質問者様はこれからも仕事でいろんな表を作る必要があるはずです。
それに、今回の例ではほぼ官制に近くなっているし、エラーの原因も
明白です。

Ｎｏ．８の回答者様のいうとおり、ＤＩＶ／０！ は０除算エラーです。
割り算を含む式またはaverage系などの内部の割り算を含む関数で見られます。
対応法は、先に分母となる部分を計算して、それが０になると割り算を
含む関数を実行しないというｉｆ文を付けるだけです。

そもそもが、私が最初に提示した
if(c25-o25-p25=0,"",c25-o25-p25)　が通らなくて、
if(iserror(c-25-o25-p25),"",(c25-o25-p25)) の式に変更する必要が
あるのは、セルの入力形式がおかしいのです。
Ｃ列、Ｏ列，P列が全てが数値であれば問題ないはずです。
全角数字入力またはスペースキーによる消去が行われているはずです。

No.10 回答者： esupuresso 回答日時： 2015/08/05 12:38

No5・６のesupuresso です。

次の式で軽く対処できます。
=IFERROR(AVERAGEIF(B4:B34,"火",C4:C34),"")

曜日毎の平均値を出す方法として、一々数式内に「月・日・水・・・」と入力するよりもっと楽な方法もあります。
また画像を添付しますが、セルE4～E10に「月、火、水、木、金、土、日」と入力しておきます。
セルF4に次の式を　=INT(IFERROR(AVERAGEIFS($C$4:$C$34,$B$4:$B$34,$E4,$C$4:$C$34,">=1"),""))　入力しておきセルF10まで数式をオートフィルコピーします。
これで曜日毎の平均値が出ます。

No.9 回答者： tom04 回答日時： 2015/08/05 11:01

No.8です。

C列の数式をみると、エラーが出る要素はないですね。
ただ、検索条件すべてが空白の場合にエラーが出るようですので

=IF(SUMIF(B4:B34,"火",C4:C34),AVERAGEIF(B4:B34,"火",C4:C34),"")

としてみてはどうでしょうか？m(_ _)m

No.8 回答者： tom04 回答日時： 2015/08/05 08:30

こんにちは！

横からお邪魔します。

直接の回答ではないのですが・・・
＞日毎の数値を入力する部分が、入力されていないと#div/0!　と出てしまいます。

「#DIV/0！」というエラーは「0」で割った時に出るエラーですので、
C列には計算式（割り算）が入っていて、「0」で割るような行が含まれていないでしょうか？

もしC列にエラーセルが存在するとそのような結果になることがあります。

そうであれば、IF関数などを使用しC列にエラーそのものを表示させないようにすれば
すでに回答されている数式で大丈夫だと思います。

※　C列の具体的な式が判らないのでこの程度でごめんなさい。m(_ _)m

この回答へのお礼

tom04様

朝からありがとう御座います（＾－＾）

当方全くのど素人でお手を煩わせ申し訳ありません。

c列には例えば、

=if(d8+e8+f8=0,"",d8+e8+f8)

上記のような日毎の総売上を求める式が入っており、下に曜日毎の平均を自動計算で出す計算式の欄を設けています。

上のc列が入力されていれば自動計算で出ますが、エラーを出さないようにするには具体的にはどのような計算式を入れれば良いでしょうか？

ひょっとしたら答えが既に出ているかも知れませんが、皆様からご教示頂いたのをほぼそのまま入力して、数学白雉とエクセル初心者故、失礼をお許し下さいませm(__)m

お礼日時：2015/08/05 10:50

No.7 回答者： Saturn5 回答日時： 2015/08/05 07:21

if(sum(c4:c34)=0,0,averageif(b4:b34,"火",c4:c34))

この回答へのお礼

朝早くから早速のご対応ありがとう御座いますm(__)m

上記の式を入力してみたのですが、恐らく私の方の他の式や設定が間違っていると思うのですが、#div/0! が消えません。

因みに、エクセルの2010です。

お手を煩わせて恐縮です(T_T)

お礼日時：2015/08/05 07:56

No.6 回答者： esupuresso 回答日時： 2015/08/05 01:45

NO5です。

　失礼しました！　　彡Ｏ┓ﾍﾟｺﾘ
添付画像にて、最後の月曜日に値が（セルC34)入っていたようだったので、こちらの添付画像の方が正確でした。
申し訳ないです　<(_ _)>

この回答へのお礼

いえいえご丁寧に補足までして頂きありがとう御座いますm(__)m

お礼日時：2015/08/05 02:46

No.5 回答者： esupuresso 回答日時： 2015/08/05 01:41

一応画像を添付しますので、そこから説明していきます。

要するに「月曜日」の隣の列（C列）には計算式が入っているけど、計算式による空白として返ってきてるセルを数えない（今のところは3週の月曜日だけ）の平均を出したいという事ですね？
お使いのexcelバージョンが2007以上の場合は「AVERAGEIFS」関数を使う事で思った事が再現できます。

セルC4以降にはどのような式が入力されているのか分かりませんが、一応添付画像からの説明です。
今月8月は月曜日が5週ありますので8月を引用。
B列には　=TEXT(A4,"aaa")　の式が入っております。（式を下方向へオートフィルコピー）
C列4行目からですが、ここは　=IF(K4="","",K4*L4)　の式を入れて下方向へ式をコピーしています。（K列の月曜日のセルの最期の2週の月曜日の値が空白を返す式です。
要は、C列にはすべて数式が入っているという事です。

で、肝心の3週分の平均の出し方ですが、次の関数を使います。
セルE4に　=AVERAGEIFS(C4:C34,B4:B34,"月",C4:C34,">=1")　の式を入れてます。

セルE5には　=INT(AVERAGEIFS(C4:C34,B4:B34,"月",C4:C34,">=1"))　の式を入れ「INT」関数で小数点以下を切り捨ててます。

セルE6は　=ROUNDDOWN(AVERAGEIFS(C4:C34,B4:B34,"月",C4:C34,">=1"),0)　の式で、「ROUNDDOWN」関数にて少数点以下を切り捨ててます。

セルE7の式は一番楽な方法で、　=INT(AVERAGEIFS(C:C,B:B,"月",C:C,">=1"))　の式を入れ、列単位で範囲を設定し、外側の「INT」関数で小数点以下を切り捨ててます。

セルE4～E6までの計算式はセル範囲を指定していますが、セルE7の式は列単位での計算式となっています。
度の式を入れても帰ってくる値は同じですのでお試しください。

この回答へのお礼

夜分に画像付きの分かりやすい説明ありがとう御座います（＾－＾）

お礼日時：2015/08/05 02:45

No.4 回答者： Saturn5 回答日時： 2015/08/05 01:39

数値入力または未入力ならば以下の式でＯＫです。

if(c25-o25-p25=0,"",c25-o25-p25)

#value!　というのは数値入力を前提としたセルに文字入力されて
いる場合に表示されます。

未入力”” ではなくて空白”　"が入力されているのではないですか？
数字を消去するときはＤＥＬキーで消去してください。

この回答へのお礼

取り急ぎ、

=if(iserror(c-25-o25-p25),"",(c25-o25-p25))

上記の式で解決したようです。

夜分にありがとう御座いましたm(__)m

お礼日時：2015/08/05 02:42

No.3 回答者： Saturn5 回答日時： 2015/08/05 00:37

if(d27＋e27＋f27=0,"",d27＋e27＋f27)

の式に変更してはどうでしょうか？
合計が０の場合は空白が入ります。

この回答へのお礼

ありがとう御座います！

概ね解決しました。

一点、最後の損益の列に例えば

=if(c25-o25-p25=0,"",c25-o25-p25)

とq列に式が入っているのですが、入力されていない行は#value!

と出てしまいます。

恐らくこれが解決すれば完成ですので、宜しければ重ね重ね恐縮ですがご教示頂ければ幸甚です。

お礼日時：2015/08/05 01:18

No.2 回答者： Saturn5 回答日時： 2015/08/04 23:09

average関数は空白セルを無視します。

残り２週というのは本当に空白でしょうか？
システムで[０]を空白で表示するようになってませんか？
残り２週のところで、ＤＥＬキーを押してみてください。
（式などが入力されていても、ctrl＋ｒで元に戻る）

この回答へのお礼

重ねてありがとう御座います。

仰るとおり式が入力され、空白で表示されるようになっていました。

ですが、そこに足し算の式が入力されていないと、手計算で数値を入力せねばなりません。

例えば月曜日のc列には自動計算で総売上、d列は現金売上を手入力、e列にはカード売上を手入力、f列には会社請求を手入力で、c列の4週目の月曜には、

d27＋e27＋f27

上記の計算式が入っていないと、総売上も手計算での入力となってしまいます。

これを回避する方法を合わせてご教示頂ければ助かりますm(__)m

お礼日時：2015/08/05 00:02