エクセル 平日と土日祝の時間計算表示

質問

下記のようなエクセルを作りたいのですが、
祝日の7月15日の土日祝残業が、平日の方に表示されてしまいます。

休日表は、別シートでＳｅｅｔ２のＢ２～Ｅ２１に作成しています。
Ａセルに、日付
Ｂセルに、始業時間
Ｃセルに、終業時間　
Ｄセルに、休憩時間として、

Ｅセルに、実労時間
　=C2-B2-D2
Ｆセルに、平日労働時間
　=IF(OR(WEEKDAY(A2,2)>5,COUNTIF(ｓｈｅｅｔ2!B$2:E$21,A2)),"",(E2))
Ｇセルに、土日祝労働時間
　=IF((F2<>""),"",(E2))
Ｈセルに、平日残業時間
　=IF(OR(WEEKDAY(A2,2)>5,COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)),"",(E2-TIME(8,0,0)))
Ｉセルに、土日祝残業時間
　=IF(OR(WEEKDAY(A2,2)<6,COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)),"",(E2-TIME(8,0,0)))

を入れてみたのですが・・・
祝日の残業時間のところがうまく表示されません。
(WEEKDAY(A2,2)<6　から　COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)　除いた日を""にすれば
よいのかなぁと試行錯誤しましたがうまくできませんでした。

そのような式を教えて頂けますか？
もしくは他に適切な式があったら教えてください。

宜しくお願い申し上げます。

回答No.4

tom04

こんばんは！
前回
http://okwave.jp/qa/q8241711.html
で回答した者です。

Sheet2の祝日データは前回同様の配置とします。
D列の「休憩時間」がない場合は空白のままではなく、「0」を入力してください。
そうしたうえでの一例です。

まずD～I列すべてを範囲指定　→　右クリック　→　セルの書式設定　→　表示形式　→　ユーザー定義から
G/標準　となっていればそれを消して
[h]:mm
としておきます。

E2セルに
=IF(COUNTBLANK(B2:D2),"",C2-B2-D2)
（意味：B～D列に一つでも空白セルがあれば空白に、そうでない場合は　終業時刻-始業時刻-休憩時間）

F2セルに
=IF(OR(E2="",WEEKDAY(A2,2)>5,COUNTIF(Sheet2!B$2:E$21,A2)),"",E2)
（意味：E列が空白またはA列日付が土・日曜またはA列が祝日の場合は空白に、そうでない場合は実労時間を！）

G2セルに
=IF(OR(E2="",F2<>""),"",E2)
（意味：E列が空白またはF列にデータが表示されている場合は空白に、そうでない場合は実労時間を！）

H2セルに
=IF(AND(F2<>"",F2>"8:00"*1),F2-"8:00","")
（意味：F列にデータが表示されている、なおかつF列時間が8時間より大きい場合はF列から8時間マイナスした時間を！、
そうでない場合は空白に！）
これを右へ1列オートフィルでコピーしているので相対参照で1列ずれるのがI列の数式になります。

という数式を入れ隣のI2セルまでコピー！

最後にE2～I2セルを範囲指定　→　I2セルのフィルハンドルで下へコピー！

これで何とかお望み通りにならないでしょうか？m(＿ ＿)m

お礼

ふたたびありがとうございます！！！

教えて頂いたとおりに設定して、表示できました！！！
ご丁寧に本当にありがとうございます。
とても助かりました。

あと・・・
すみません、またもう一つ（これで最後のハズ）うまくいかないところがありまして、、、
再度、新しく質問させていただきますので、
重ね重ね申し訳ありませんが、教えて頂けると大変大変ありがたいです。

「エクセルで22時以降を平日・土日・研修各々表示させたい」
m(＿ ＿)m

回答No.3

bunjii

質問内容の用語で誤りがありますので訂正させていただきます。

始業時間　→　始業時刻
終業時間　→　就業時刻
時間は時刻と時刻の間の長さです。
時刻はその時々の瞬時です。

E2=C2-B2-D2
勤務していない日は0:00と表示されて良ければこれで良いでしょう。

F2=IF(OR(WEEKDAY(A2,2)>5,COUNTIF(ｓｈｅｅｔ2!B$2:E$21,A2)),"",(E2))
IF関数の最後の引数はE2のみで良く括弧の1組を外せます。

G2=IF((F2<>""),"",(E2))
勤務の無い日は0:00と表示されて良ければこれでも良いでしょう。
他にはF2のIF関数の第2引数と第3引数を入れ替えれば同じ結果になります。

H2=IF(OR(WEEKDAY(A2,2)>5,COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)),"",(E2-TIME(8,0,0)))
タイプミスがあります。COUNTIF関数の範囲は$B$2:$E$21でなければなりません。
IF関数の第3引数（E2-TIME(8,0,0))は括弧が1組無駄です。
また、状況によって時間の計算で負数が発生しエラーになります。
原因はE2-TIME(8,0,0)です、

I2=IF(OR(WEEKDAY(A2,2)<6,COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)),"",(E2-TIME(8,0,0)))
H2の式と同じタイプミスがあります。
更に、IF関数の論理が正しくありません。
H2の関数式を使って、第2引数と第3引数を入れ替えれば正しい論理になるはずです。

時間の計算では負数が許されません。
従って、E2がTIME(8,0,0)より大きい場合のみ減算するように修正すると良いでしょう。

お礼

ご丁寧に教えて頂きありがとうございます。
とても助かりました。
試行錯誤しながら、やっと表示できました。

あと・・・
すみません、またもう一つ（これで最後のハズ）うまくいかないところがありまして、、、
再度、新しく質問させていただきますので、
重ね重ね申し訳ありませんが、教えて頂けると大変大変ありがたいです。

「エクセルで22時以降を平日・土日・研修各々表示させたい」
m(＿ ＿)m

回答No.2

KURUMITO

次のようにすればよいでしょう。
Ｅセルに、実労時間
　=C2-B2-D2
Ｆセルに、平日労働時間
　=IF(OR(WEEKDAY(A2,2)>5,COUNTIF(ｓｈｅｅｔ2!B$2:E$21,A2)),"",E2)
Ｇセルに、土日祝労働時間
　=IF(F2<>"","",E2)
Ｈセルに、平日残業時間
　=IF(OR(WEEKDAY(A2,2)>5,COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)>0),"",(E2-TIME(8,0,0)))
Ｉセルに、土日祝残業時間
　=IF(AND(WEEKDAY(A2,2)<6,COUNTIF(Sheet2!$B$2:$E$21,A2)=0),"",(E2-TIME(8,0,0)))

なお、上の式でSheet2!＄B$2:E$21の場合とSheet2!＄B$2:$E$2の場合が有りますが後者は間違っているように思いますね。
セル番地に（）は必要ないですね。
土日の残業ではORとするのは間違いですね。また、COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)=0とすることが必要ですね。
平日でしかも祝日に無い場合にはということですからANDになります。

お礼

ご丁寧に教えて頂きありがとうございます。
とても助かりました。
試行錯誤しながら、やっと表示できました。

あと・・・
すみません、またもう一つ（これで最後のハズ）うまくいかないところがありまして、、、
再度、新しく質問させていただきますので、
重ね重ね申し訳ありませんが、教えて頂けると大変大変ありがたいです。

「エクセルで22時以降を平日・土日・研修各々表示させたい」
m(＿ ＿)m

.

回答No.1

mshr1962

Ｉセルに、土日祝残業時間
　=IF(OR(WEEKDAY(A2,2)<6,COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)),"",(E2-TIME(8,0,0)))

のOR(WEEKDAY(A2,2)<6,COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2))の条件がおかしいです。

COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)の結果が1になる場合、祝日になるため除外する必要が出てきます。

　=IF(OR(WEEKDAY(A2,2)<6,COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)=0),"",(E2-TIME(8,0,0)))

としてもいいですが、単純にHセルの式の条件で

　=IF(OR(WEEKDAY(A2,2)>5,COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)),(E2-TIME(8,0,0)),"")

またはGセルのように

　=IF((H2<>""),"",(E2-TIME(8,0,0)))

とすればよいのでは？

お礼

ご丁寧に教えて頂きありがとうございます。
とても助かりました。
試行錯誤しながら、やっと表示できました。

あと・・・
すみません、またもう一つ（これで最後のハズ）うまくいかないところがありまして、、、
再度、新しく質問させていただきますので、
重ね重ね申し訳ありませんが、教えて頂けると大変大変ありがたいです。

「エクセルで22時以降を平日・土日・研修各々表示させたい」
m(＿ ＿)m