休業日を除いて1日平均を計算する関数

質問

お世話になります。日計表の作り方を教えてください。

・日付はあらかじめ31日まで入力しておくものとします。
・曜日は入力されていません。
　　ついたちの曜日を入力すると、以下繰り返して自動入力できたりしないでしょうか？
・本日の売上個数を手入力します。
・累計個数を計算しています。
・１日平均の関数を入れました。「=SUM(D3) / COUNTIF(D3, "<>0")」
・営業日数をカウントしたい。

■土日祝は営業していないということです。
■1日平均を計算したいのですが、休業日（個数がゼロ）もカウントしてしまうため、
1日平均の数値が少なくなってしまいます。休業日は1日平均から除外したい。
■営業日をカウントしたいのですが「個数が入力されている日は営業日として加算する」
ということはできないでしょうか。

回答No.1

mshr1962 · ベストアンサー

>ついたちの曜日を入力すると、以下繰り返して自動入力できたりしないでしょうか？
単純に言えば年月が分かれば曜日は簡単に表示できます。
B3 =TEXT(DATE(年,月,$A3,"aaa")

>累計個数を計算しています。
D3 =SUM($C$3:$C3)

>１日平均の関数を入れました。
E3 =IF($C3=0,0,$D3/$F3)

>営業日数をカウントしたい。
F3 =COUNTIF($C$3:$C3,"<>0")
※非営業日は、0と表示されます。ブランク表示にする場合はIF文にするか、表示形式で0をブランク表示にしてください。

補足

ありがとうございます。もう少しよろしいでしょうか。

=TEXT(DATE(年,月,$A3,"aaa")

これについて「この関数に対して、多すぎる引数が入力されています」と出ます。
"aaa"が反転表示されます。

曜日列の「セルの書式設定」の「ユーザー設定」にaaaを登録しましたが、
それでもダメなようでした。

これ以外ばバッチリできました！

回答No.5

bunjii

>次回は、未入力のCOUNTIF関数で考えてみます。
申し訳ありません。
関数名を誤っていました。
正しくはCOUNT関数でした。

回答No.4

mshr1962

>=TEXT(DATE(年,月,$A3,"aaa")
すみません。)が一つ抜けてました。
=TEXT(DATE(年,月,$A3),"aaa")
が正解です。

お礼

ありがとうございます。カッコが抜けていたのですね。
No.3の回答者様にお礼したように、
日付欄を正しく作り、＝TEXT以下のみで曜日を表示させました。
ダミーの数値を入力して検算も済ませました。

この度は大変助かりました。ありがとうございました。

回答No.3

imogasi

よくあるタイプの質問で、初歩的な関数の質問だ。
付帯的知識の勉強が不足しているように思う。関数の解説書を１冊ぐらいで、独学で始めるとそうなりがちだ。市販書は表面的なことしか載ってないから。
（Ａ）曜日をシートの列に表示するにしても、
（Ｂ）日付シリアル値からＷＥＥＫＤＡＹ関数で曜日を判別するにしても、
日付シリアル値を作らないといけない。
Ｇｏｏｇｌｅででも「日付シリアル値」で照会して、どういうことか、よく勉強のこと。
そのためには、年、月、日の数字が必要で、質問の表だけからは、年、月がどうなっているのかわからない。年月日を2017/4/1のような形で入力すれば、自動で「日付シリアル値」
の値に変換してくれるのだが。
もう一つ、「セルの値」と「書式設定後のセルに見えて居る日付など」の区別を意識しているか？
エクセルのバージョンも書け。使える関数に影響する。ＳＵＭＩＦＳ、ＡＶＥＲＡＧＥＩＦＳなどの関数が新設された。
http://www.excel-list.com/averageifs.html
＞COUNTIF(D3, "<>・・を使わないやり方もできるかもしれない。
-
面倒なのは、関数で祝日を判別する方法だ。国際的に国で祝日が違うので、ＭＳも手を出さなかったと思われる。日本では、年によってもちがうしね。
＞祝日。あるいは土日以外の休日に関しては自分で登録しないと運用できません。
＜ーＷＥＢ記事から
ーー
の後半のＷＯＲＫＤＡＹ、ＮＥＴＷＯＲＫＤＡＹ　ただし
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1278199062
しかし、Ｇｏｏｇｌｅで「エクセル関数 祝日判定」などで照会して、よさそうな記事を数個当たって読めば、わかるだろう。
http://d.hatena.ne.jp/yshgt/20110127/１２９６０８６３６９にあるようなｈ付けを必要な部分をシートの余白列に貼り付け、内容をよくチェックして、ＷＯＲＫＤＡＹ関数で使う。
こういうこともせずに「まず聞いてみよう」になっちゃっているが進歩が遅いよ。
http://www.kenzo30.com/ex_kisotyu/ex_ks_tyukyu9_9_3.htm
ＶＬＯＯＫＵＰ関数でも祝日判定はできる
http://kokoro.kir.jp/know/calendar3.html

お礼

ありがとうございます。
＞よくあるタイプの質問で、初歩的な関数の質問だ。

仰る通りです。

＞「日付シリアル値」で照会

必要かと思ったのですが、Windowsタスクからタイマー起動で毎日、毎月、
使いまわしするなら、曜日を一つ手入力した方がいいかと思いまして。
今回、"aaa"がエラーになったので日付セルを正しく作りました。

＞「セルの値」と「書式設定後のセルに見えて居る日付など」の区別を意識しているか？

あーそれは考えてなかったです。

＞エクセルのバージョンも書け。

すいません必要でしたね。2010です。

＞面倒なのは、関数で祝日を判別する方法だ。

祝日は無理と思っていました。
ただこれも別件のOKWAVEで教えて頂いて作ったことがあります。すいません。

No.2の回答者様が仰っているように、
やりたいことを深く考えずに作り始めてしまいました。

回答No.2

bunjii

>ついたちの曜日を入力すると、以下繰り返して自動入力できたりしないでしょうか？
曜日の算出はできますが、祝日は対象期間（年月）が分からないと判定できません。
曜日のみでしたら次の数式で算出できます。
B4=INDEX({"日";"月";"火";"水";"木";"金";"土"},MOD(MATCH(B3,{"日";"月";"火";"水";"木";"金";"土"},0),7)+1)
B4を下へコピーします。
但し、B3には曜日の1文字のみ入力するものとし、空白を前後に含まないものとします。

>本日の売上個数を手入力します。
休日は未入力（BLANK）にすることをお薦めします。
0を入力すると平均の算出時に分母の個数に算入されますが、未入力または文字列は分母の個数に加算されませんので、数式を簡素化できます。
休日を未入力にすればAVERAGE関数のみで算出できます。

>営業日数をカウントしたい。
休日を未入力にすればCOUNTIF関数で計数できます。
F3=IF(C3,COUNT(C$3:C3),"")
COUNTIF関数は数値が入力されているセルの数を計数しますので、文字列や未入力のセルを除外した結果が得られます。
営業日でも売り上げ個数が0のときは営業日の日数に加算する必要があるでしょう。
また、1日平均の売り上げ個数に売り上げ個数0も含めるべきではないでしょうか？

やりたいことをもう少し深く考えた方が良いでしょう。

お礼

ありがとうございます。

＞B4=INDEX({"日";"月";"火";"水";"木";"金";"土"},MOD(MATCH(B3,{"日";"月";"火";"水";"木";"金";"土"},0),7)+1)

日付に対応する曜日の文字だけ表示させるのはこれがいいですね。

＞休日は未入力（BLANK）にすることをお薦めします。

今回は、No.1の回答者様の式を利用して「ゼロを非表示」などでやってみました。

＞COUNTIF関数は数値が入力されているセルの数を計数しますので、文字列や未入力のセルを除外した結果が得られます。

次回は、未入力のCOUNTIF関数で考えてみます。

＞営業日でも売り上げ個数が0のときは営業日の日数に加算する必要があるでしょう。

なるほど。それは想定外でした。

＞1日平均の売り上げ個数に売り上げ個数0も含めるべき

売上ゼロ個の日の「営業日数」と「個数」も1日平均に含めるべき、という意味ですね？