0を省いた 曜日別平均

質問

エクセルで売上管理表の作成途中なんですが、曜日別の売上＆売上平均のところで困ってます。曜日別平均=SUMIF(C2:C315,"月",D2:D315)/COUNTIF(C2:C315,"月")の関数で動いたのですが、未来の日にちのセルには”0”が入っており正しい平均計が出ていません。

色々と調べて{=AVERAGE(IF(WEEKDAY($D$6:$C$36,1)=1,$E$6:$E$36))}　Shift+Ctrl+Enterを押すものもも試してみましたが平均値が0になり正しく動きませんでした。どなたかアドバイスお願いいたします。

回答No.6

kagakusuki

過去の日付の内、月曜日の日が何日あるのかを計算するためには、現在の日付が何日であるのかが判らなければなりません。
　しかし、現在の日付が何日であるのかを決めるのに、TODAY関数やNOW関数を使用した場合、最新の売り上げのデータを入力していない場合であっても、現実の日付が変わる度に過去の日数が増え続けるため、例えば、月曜日の日に管理表のあるExcelファイルを開いた場合等には、その日の売り上げデータは未だ入力されていないにも拘らず関わらず、未来ではない月曜日の日数は増える事になります。
　更に、管理表のファイルを1月毎や1年毎に新たに作成し、それまでのデータは、過去のファイルとして保存しておく様な場合には、過去のファイルの売り上げデータは更新される事はないにも関わらず、合計値を割る方の、過去の月曜日の日数は、現実の日数が1週間経つ毎に、1日ずつ増え続けて行く事になりますから、1週間毎に表示される値が変わってしまう事になります。
　ですから、売り上げデータを最後に更新した日付を、Excelの同じBook内の何処かのセルに記載しておき、その日付に対して過去、現在、未来を決めなければなりません。

　それから、未来の日付のデータと未来ではない日付のデータを区別するためには、そのデータが何日の日付のものなのかが判らなければなりません。
　御質問文中の数式から推理すれば、C2～C315に曜日のデータが存在するらしい事は想像出来なくもありませんが、日付のデータが何処に入力されているのかが、御質問文中には記載されていません。
　又、売り上げの曜日別の平均を求めたいという事ですが、売り上げデータが何処に入力されているのかも、数式から推理出来なくもないというだけで、明記されてはいません。

　御質問されるのであれば、これらの必要となる情報を明記して頂く様御願いします。

　必要な情報が明記されていないため、何処に何のデータが存在しているのかに関しては、取り敢えず仮定の話とした上で、回答させて頂きます。
　今仮に、売り上げテータの最終更新日がA1セルに入力されていて、B2～B315に日付が入力されているものとします。
　もし、行が下へ行くに従って日付が新しくなっている場合には、次の様な関数にされると良いと思います。

=SUMIF($C$2:$C$315,"月",$D$2:$D$315)/COUNTIF($C$2:INDEX($C:$C,MATCH($A$1,$B:$B)),"月")

　又、もしも、データが日付順に並んでいない場合には、SUMPRODUCT関数を使用した次の様な数式で計算する事が出来ますが、SUMPRODUCT関数を使用した場合には、コンピューターの計算処理の負荷が若干増えてしまいます。

=SUMIF($C$2:$C$315,"月",$D$2:$D$315)/SUMPRODUCT(($B$2:$B$315<=$A$1)*($C$2:$C$315="月"))

　又、作業列を使用した次の様な方法もあります。
　今仮に、Z列を作業列として使用する事にします。
　まず、Z2セルに次の数式を入力します。

=IF(AND(ISNUMBER($B1),$B2<=$A$1),$C2,"")

　次に、Z2セルをコピーして、Z3以下に貼り付けます。
　後は、月曜日の曜日別平均値を表示させるセルに、次の数式を入力します。

=SUMIF($C$2:$C$315,"月",$D$2:$D$315)/COUNTIF($Z:$Z,"月")

　尚、御使いのExcelのバージョンがExcel2007以降である場合には、AVERAGEIFS関数が最も適しています。

=AVERAGEIFS($D:$D,$B:$B,"<="&$A$1,$C:$C,"月")

お礼

私の頭では付いていけず途中で根をあげておりました。しかしながらやっと完成いたしましたので今更ながらお礼申し上げます。ありがとうございます

回答No.5

yomyom01

COUNTIF()の代わりにCOUNTIFS()？

回答No.4

KURUMITO

例えばB2セルから下方には日付が入力されており、C列の曜日の表示はC2セルに次の式を入力して下方にオートフィルドラッグされているとします。

=IF(B2="","",TEXT(B2,"aaa"))

そこで平均を求める式ですがD列には未来の日付については0が表示されているとのことですね。過去のデータについても0のデータは記載がないとすれば次の式で求めることができます。

=SUMIF(C2:C315,"月",D2:D315)/SUMPRODUCT((C2:C315="月")*(D2:D315>0))

また、過去の日付でも0または記載がない場合でも平均に含めるのであれば次のような式になりますね。
ただし本日が月曜日で売り上げのデータが入力されていなくても本日までの平均が表示されます。

=SUMIF(C2:INDIRECT("C"&MATCH(TODAY(),B2:B315,1)),"月",D2:INDIRECT("D"&MATCH(TODAY(),B2:B315,1)))/COUNTIF(C2:INDIRECT("C"&MATCH(TODAY(),B2:B315,1)),"月")

回答No.3

imogasi

配列数式を使うことが良いかどうか別にして
又http://pc.nikkeibp.co.jp/pc21/special/hr/hr3.shtmlの「配列が使える関数」
のような記事があるので、注意して、やって見た
例　データ　A1:C23
2011/5/1 1 1
2011/5/2 2 2
2011/5/3 1 3
2011/5/4 2 4
2011/5/5 3 5
2011/5/6 4 6
2011/5/7 2 7
2011/5/8 0 1
2011/5/9 4 2
2011/5/10 1 3
2011/5/11 2 4
2011/5/12 3 5
2011/5/13 4 6
2011/5/14 5 7
2011/5/15 0 1
2011/5/16 2 2
2011/5/17 3 3
2011/5/18 4 4
2011/5/19 5 5
2011/5/20 6 6
2011/5/21 1 7
2011/5/22 2 1
C列は参考まで出した。関数で=WEEKDAY(A2)
ーー
=AVERAGE(IF((WEEKDAY($A$2:$A$23)=1)*($B$2:$B$23<>0),B$2:B$23))
でSHIFT+CTRL+ENTERで
1.5
になって、うまく行くようです。
またデータの入ってない範囲まで指定して
=AVERAGE(IF((WEEKDAY($A$2:$A$33)=1)*($B$2:$B$33<>0),B$2:B$33))
でSHIFT+CTRL+ENTERで
エラーにならず1.5
になりました。
ーーーーーーーーーーー
ＳＵＭＰＲＯＤＵＣＴ関数を使うやワーク列をつくり該当行に１を出してAVERAGEを出す方法もあると思う。

回答No.2

pai3_14

SUM関数で 合計を出し、それを　COUNT関数(数値が入力されたセルの数)
で割ればいいのでは？

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/count.htm

回答No.1

keithin

方法１：
仮にＢ列に「年/月/日」で日付が記入してあるとすると
Ｃ列には
C2:
=TEXT(B2,"aaa")
のように曜日を文字列でしっかり計算で出しておいて，
その上で「今日以前の月曜日の平均」を
=SUMPRODUCT((B2:B315<=TODAY())*(C2:C315="月"), D2:D315)/SUMPRODUCT((B2:B315<=TODAY())*(C2:C315="月"))
のように計算します。

方法２：
ご利用のエクセルのバージョンがご質問に書かれていませんので，Excel2007以降を使い
=SUMIFS(D2:D315,B2:B315,"<="&TODAY(),C2:C315,"月")/COUNTIFS(B2:B315,"<="&TODAY(),C2:C315,"月")
のように計算します。