Excelで祝休日判定

質問

実働時間として
G6に=IF(AND(D6<>"",E6<>"",F6<>""),E6-D6-F6,"")の式があり、
I6に祝休日判定。Iの祝休日に「1」を入力し
H6平均時間
=IF(G6="","",SUMIF($I$6:I6,"",$G$6:G6)/COUNTIF($I$6:I6,"")) にしましたが巧く出来ません何処か違うのでしょうか。
休日の出勤時間は翌日に加算し、休日を除いたその日現在の平均時間を出したいのですが。

WIN XP or 2000
自宅は　XP 会社が2000の為
Excel バージョンは分かりません。

説明が巧く出来ませがご理解出来ますでしょうか。
よろしくお願いします。

回答No.7

taisuke555 · ベストアンサー

＞月日 曜日 開始 終了 休憩 実動時間　平均

・年は入っていないのですね？
・この表は、１月分で１シートになっていますか？

　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ　　Ｆ　　Ｇ　　　　Ｈ　　Ｉ　　←列
１　月　　日　　曜日　開始　終了　　休憩　実働　平均　休日
２　３　　１　　月　　8:30　18:00　1:30　8:00　8:00
３　３　　２　　火　　8:30　19:45　1:30　9:45　8:52
４　３　　３　　水　　8:30　18:00　1:30　8:00　　 1←休日欄
５　３　　４　　木　　　　　　　　　　　　　　　　　　1←休日欄
６　３　　５　　金　　8:30　18:00　1:30　8:00　11:15
↑
行

という表でしょうか？
それとも　月はＡ２セルのみに入っているのでしょうか？

（表の都合上、水・木の祝休日判定に１が入っています。）

上記の表であれば、
Ｈ２セルに
　=IF(OR(G2="",I2=1,TODAY()<DATE(2004,A2,B2)),"",SUM($G$2:G2)/COUNTIF($I$2:I2,""))
月がＡ２セルのみに入っていれば、
　=IF(OR(G2="",I2=1,TODAY()<DATE(2004,$A$2,B2)),"",SUM($G$2:G2)/COUNTIF($I$2:I2,""))
としてみてください。

６行目から始まるのなら
　=IF(OR(G6="",I6=1,TODAY()<DATE(2004,A6,B6)),"",SUM($G$6:G6)/COUNTIF($I$6:I6,""))
です。（Ａ６セルのみ月が入っていれば、Ａ６→＄Ａ＄６）

年が入っていれば、２００４→年のセル（＄Ａ＄１等）

１月分１シートで入っていないと、１月毎の平均の式にしないといけないので
面倒です。（１年毎の平均なら問題ないですが・・・）

もう少し、具体的に分かりやすく表を載せていただかないと、
こちらは、推測での回答になってしまうので
いろいろな場合に合わせて、回答しないといけなくなってしまいます。

まだ、おかしいようなら補足してください。

お礼

有難う御座いました。
お蔭様でなんとか出来ました、
素人の私に本当に丁寧なアドバイスをして頂き大変助かりました。

補足

有難う御座います。

日付はA4に4/21、B4に水曜と入っています 　　あとAB2（セル結合）に平成５年と入っています。
A B C D E F G H I
平成１６年５月就業時刻 　　　実 働 休日
月 日 曜日 開始 終了 時間 時間 平均
4/21 水 4/22 木 4/23 金 4/24 土 1
4/25 日 1 4/26 月 　　補足不十分で申し訳ありませんでした、これでお解かり頂けますでしょうか。

回答No.6

taisuke555

たびたびすみません。

＞その日現在までではなく１日から３０日まで全ての平均が出て来てしまいます。

もしかして、今日が、26日だとして、
27日,28日の欄にも、平均が表示されてしまう。
という事でしょうか？

もしそうなら、日付の入っている欄はありますか？
あればその列と、どの様に入力されているか
（年／月／日で入力されていて、セルの書式を[dd]にしているとか
　数字で日のみ入力しているとか）
数字で日のみ入力されている場合、年や月はどこかに入力されているか
補足してください。

なければ、作らないと、そのデータが何日の物か分からないので無理です。

補足

有難う御座います。
もしかして、今日が、26日だとして、
27日,28日の欄にも、平均が表示されてしまう。
そうです。
日付が影響するとは思いませんでしたので記載しませんでした。どうもすみません。

下記の順に作成していますので日付はあります。
月日 曜日 開始 終了 休憩 実動時間　平均

回答No.5

taisuke555

＃１，２，３です。

＞土曜日曜に平均が出てしまい

IF文を入れてませんので、出てしまいます。
（これは、試しに入れていただいたものです。）

＞その日現在までではなく１日から３０日まで全ての平均が出て来てしまいます。

今までの式では、そんな事にはならないはずですが。

＃４さんも言っていますが、＃１の補足のデータで、木曜日は２行目ですか？

もしそうなら
H2セルはどうなっているのでしょう？
=IF(OR(G2="",I2=1),"",SUM($G$2:G2)/COUNTIF($I$2:I2,""))
を入力してみてください。

H3セルにH2をコピー貼り付けすると、
=IF(OR(G3="",I3=1),"",SUM($G$2:G3)/COUNTIF($I$2:I3,""))
となっていると思います。
で、６行目は＃４さんも仰る通り、
=IF(OR(G6="",I6=1),"",SUM($G$2:G6)/COUNTIF($I$2:I6,""))

SUM(),COUNTIF()の()内が変化しているのに注意してください。

２行目の式　SUM($G$2:G2)/COUNTIF($I$2:I2,"")
はG2～G2（G2のみ）を加算し、I2～I2（I2のみ）の何も入力されていないセルをカウントし、割っています。

６行目はG2～G6までを加算し、以下略

２行目と、６行目では若干式に違いがありますので、注意してください。

SUM()関数とCOUNTIF()関数の意味と
$G$2,G2の表記の違いを勉強してみてください。

まだ、私の勘違い等ありましたら、私のわかる範囲で回答しますので、
遠慮無く、補足してください。

補足

補足は一回の回答に１回しか出来ないのでしょうか。
補足するが無くなってしまったので、別のところから補足させて頂きます。
先程の補足が、左詰めになってしまいました。

A4=4/21 B4=木C4=8:30の順になっております。

平成１６年５月 就業時刻 実 働 (平均8:00) 　休日
月日　曜日　開始　終了　休憩　時間 　平均
4/21　水 　　8:30 　18:00 　1:30 　　8:00 　8:00
4/22　木 　　8:30 　19:15 　1:30 　　9:15 　8:30
4/23　金 　　8:30 　18:15 　1:30 　　8:15 　8:30
4/24　土 　　8:30 　12:00 　0:00 　　3:30 　　　　　1
4/25　日 　　　　　　　　　　　　　　　　　　　　　　　1
4/26　月 　　8:30 　18:00 　1:30 　　8:00 　9:15
よろしくお願いします。

回答No.4

mshr1962

計算式の参照が間違っていませんか？
木曜日が2行目だとすると
H6に入るのは
=IF(G6="","",SUMIF($I$2:I6,"",$G$2:G6)/COUNTIF($I$2:I6,""))
だと思うのですが

>休日の出勤時間は翌日に加算し
と有りますが、それならSUMIFは使う必要がないのでは？
=IF(G6="","",SUM($G$2:G6)/COUNTIF($I$2:I6,""))

回答No.3

taisuke555

補足どうもです。
・・・がちょっとよくわかりません。

＞木8:30 18:00 1:30 8:00 8:15
＞金8:30 19:45 1:30 9:45 8:33

まず、金曜日の[8:33]は木・金２日間の平均ですか？
平均は[8:52]になると思うのですが・・

＃１・＃２とも、実働時間を足して、祝休日フラグが1の入っていない日を
カウントして平均を出していますので、できると思うのですが・・・

試しに
=SUM($G$6:G6)/COUNTIF($I$6:I6,"<>1")
だとどうなりますか？

実際にこの関数を入力した場合の平均時間と、
haregooさんが求めたい平均時間を補足してみてください。

補足

すみません。間違えてました、平均は8:52です。
=SUM($G$6:G6)/COUNTIF($I$6:I6,"<>1") でためしましたが、土曜日曜に平均が出てしまい、
その日現在までではなく１日から３０日まで全ての平均が出て来てしまいます。

解かり難い補足で申し訳ありません。

回答No.2

taisuke555

＃１です。
２度書きすみません。

もし、祝休日は平均時間を表示しないなら、

=IF(OR(G6="",I6=1),"",SUM($G$6:G6)/COUNTIF($I$6:I6,""))
に変更してください。

セルの書式は、[時刻]にして下さい。

補足

有難う御座います。
祝祭日は平均時間を表示しないのですが、
翌月曜日に加算して平均を出したいのです、
現在Iに祝祭日判定を設けていますが機能してないようです。

回答No.1

taisuke555

http://oshiete1.goo.ne.jp/kotaeru.php3?q=814192
の続きですか？
前回の質問のURLを載せると、質問の内容も分かり易いかもしれません。

＞巧く出来ません何処か違うのでしょうか。
どの様に巧く出来ないのか、具体的に載せた方が回答もしやすいと思います。

エラーが出る、思っている数字にならない等。

＞休日の出勤時間は翌日に加算し
どこかのセルで行っているのですか？
行っていないようなら、
=IF(G6="","",SUM($G$6:G6)/COUNTIF($I$6:I6,""))
ならどうでしょう？

そういう事ではない場合、具体的に何が巧くいかないのか補足してみてください。

補足

早速の回答有難うございます。
はい＝814192の続きの勤怠表作成です。
では、早速補足なのですが、下記のIに休日判定欄を設け土日の判定をしたのですが出来ませんでした。
土曜日に出勤していますが、平均は土日を含まず計算したいので翌月曜日の平均HにGの木金土月４日分を３日間平均で出せる様にしたいのですが、勿論、毎日の平均は表示する前提で。これでお解かり頂けますでしょうか。
D E F G H I
　開始 終了 休憩　時間　平均 休日判定
木8:30 18:00 1:30 8:00 8:15
金8:30 19:45 1:30 9:45 8:33
土8:30 18:00 1:30 8:00 [1」
日 [1」 月8:30 18:00 1:30 8:00 9:47

よろしくお願いします。