[[20141113130138]]　『複数条件のある残業時間の集計表について』（りんご）　ページの最後に飛ぶ

『複数条件のある残業時間の集計表について』（りんご）

エクセルで残業時間と休日出勤時間の集計表を作成しております。ここで質問させてください。

作りたい表は
平常日の残業日を区分0
休日出勤日を区分1
とします。

A 区分0の時は残業開始時刻

    区分1の時は休日出勤時刻

B 退社時刻

C 残業時間／休日出勤時間の集計

A.Bを入力するとCを表示させたいのですが、

条件が、

区分0の時は条件もなく、そのまま
B-Aで引いてくれればいいのですが

区分1の時は
朝8:10～18:30までの場合、休憩1時間
朝8:10～18:30を過ぎる場合は、休憩が１時間＋15分

になります。

この条件を一つのセルに関数を組合わせて
作りたいのですが、
どうかお力を貸してください。
宜しくお願いします。

＜使用 Excel：Excel2010、使用 OS：Windows2000 ＞

 区分0か区分1かをどうやって判定させたらいいですか？

（ぽち） 2014/11/13(木) 15:10

ぽちさん

説明不足ですいません。
今の段階では、
表の日付の横のセルに、
区分として、
通常時の残業日は「０」
休日の日は「１」と手入力しています。

この区分のセルも時間によって
自動判定する事は可能なのでしょうか？？

「０」を判定して欲しい時は
セルの残業開始が17:00or17:15の時。

「１」を判定して欲しい時は
セルの残業開始が朝の8:10～の時です。

今作ってる表を表すとこんな感じです。
※この表では残業扱いの時間だけを計算したいので、
(休日出勤の時間も残業と同等な扱いの為)
入力は平常日は残業開始時間からの入力。

例

Ａ日付／B区分／C残業開始／Ｄ退社時間／Ｅ残業時間

11/13(木) ／ 1／ 17:15 ／22:15／5.0

11/14(金) ／１／17:00／18:30 ／1.5

11/15(土) ／０／8:10／20:15／11.0

こんな感じに作りたいのですが、

Bを自動判定できるようにと、

Ｅの式が、難しくて。

（りんご） 2014/11/13(木) 16:53

エクセルを使える環境にないので計算ロジックを書きます。

ちなみに作りかけの表と質問本文とで区分が違ってますが、表の方を信用しますね。

区分=もし残業開始時刻が17時より前の時刻なら0、そうでないなら1
残業時間=もし区分が1ならば退社時刻引く残業開始、そうでない場合で退社時刻が18時30分以前ならば退社時刻引く残業開始引く1時間、それ以外たらば退社時刻引引く残業開始引く1.25時間

これを数式化するだけです。

りんごさんはどこでつまずき難しいと考えていらっしゃるのかを教えてくださいませんか。
何か書けていない条件があるのですか。
（ryopo^2） 2014/11/14(金) 00:14

とりあえずExcel使える環境にいるのでざくっと作ってみました。
いただいている条件で扱いをどうすればよいのかわからなかったのは、区分0の場合で退社時間が18時30分～18:45の方の休憩時間の扱いです。

18:30以降に退社した方の休憩時間を単純に1:15とすると、18:40に退社した人の残業時間が18:30に退社した人の残業時間より短くなるという現象が生じます。

運用面で18:30以降に退社する人は必ず18:46以降に退社するルールを適用していればよいのですが、そうでない場合を想定した数式にしています（開始時刻が同じで退社時間が18:30～18:45の複数の方がいた場合は、すべて同じ残業時間になるようにしました）。

数式：
B2=IF(C2<TIME(17,0,0),0,1)
E2=IF(B3=1,D3-C3,IF(AND(B3=0,D3<=TIME(18,30,0)),D3-C3-TIME(1,0,0),IF(AND(B3=0,D3<TIME(18,45,0)),TIME(18,30,0)-C3-TIME(1,0,0),D3-C3-TIME(1,15,0))))

結果：
　A B C D E
1 日付区分開始時刻退社時刻残業時間
2 11/20（木) 1 17:15 22:15 5:00
3 11/21（金) 1 17:00 18:30 1:30
4 11/22（土) 0 8:10 20:15 10:50
5 11/23（日) 0 8:10 18:30 9:20
6 11/24（月) 0 8:10 18:42 9:20

セルの書式
A列：mm"/"dd"（"aaa")"
B列：数値
C列～E列：hh:mm
（ryopo^2） 2014/11/14(金) 12:26

すみません。
E2の数式の右辺について行番号がまちがってました。
右辺の行番号を3から2に読み替えてください。
よろしくお願いします。

（ryopo^2） 2014/11/14(金) 12:38

丁寧に御回答頂き、本当にありがとうございます。
知識不足で申し訳ないです(_ _)

区分の欄なのですが、
勤務形態の都合もあり
やはり自分で手入力することになりまして、
事前に区分は入力しておく形で
作ってみたところ

区分０で、休日出勤しておらず
残業開始時間と退社時間を未入力の方がいる場合、
残業時間のセルにマイナスの数値が
出てしまいました。

✱残業時間の表示を、１時間30分の時は1.30
ではなく、1.5と表示させたいので、
それぞれの式に×24をして書式を変えてみたのですが、
それが影響しているのでしょうか？

マイナス数値でなく、
休日出勤していない日のセルに
空白、もしくは０ｈと表示させるには
どのように式を追加したらよいでしょうか？

上記の問題以外は、本当に望んでいた通りの
表になっております。

度々で申し訳ございません。
宜しくお願いします。

（りんご） 2014/11/14(金) 15:12

現行の式だと退社時刻（D列）が空白だとマイナスの表示がされてしまいます。
なので、区分欄（B列）、開始時刻(C列)、退社時刻（D列）に値が入っていない時は空白とするロジックを追加しました。

E2=IF(OR(b2="",C2="",D2=""),"",IF(B2=1,D2-C2,IF(AND(B2=0,D2<=TIME(18,30,0)),D2-C2-TIME(1,0,0),IF(AND(B2=0,D2<TIME(18,45,0)),TIME(18,30,0)-C2-TIME(1,0,0),D2-C2-TIME(1,15,0)))))

これでお試しください。

（ryopo^2） 2014/11/14(金) 16:11

 よく理解できていませんが、
 > 上記の問題以外は、本当に望んでいた通りの表になっております。 
 ということでしたら

 E2: =IF(COUNT(B2:D2)<3,"",IF(B2=1,D2-C2,MAX(0,D2-C2-"1:00"-"0:15"*(D2-"18:30">0)))*24)

 これだとどうなりますか？

 表示形式は「標準」か何か知りませんが、適切なものにしてください。

 区分が「0」の場合、出社時間に関係なく退社時間が18:30を過ぎていたら、
 休憩時間が15分加算されるんですよね？
　
（JKT） 2014/11/14(金) 17:45

 > E2: =IF(COUNT(B2:D2)<3,"",IF(B2=1,D2-C2,MAX(0,D2-C2-"1:00"-"0:15"*(D2-"18:30">0)))*24)

 MAX関数を使っていますが、休憩時間を引く・引かないという区分があるんだったら、
 たぶん要らないですね。（区分がなくても、これだけでは意味がないかもしれません）

 その他もちょっと修正しました（20:21）

 E2: =IF(COUNT(B2:D2)<3,"",IF(B2=1,D2-C2,D2-C2-IF(D2-"18:30">0,"1:15","1:00"))*24)

 これで試してみてください。
　　
（JKT） 2014/11/14(金) 19:56

ryopo^2さん
JKTさん

御回答頂き、本当にありがとうございました。
コメントがおそくなってしまい申し訳ありません。

皆さんの回答を元に、色々試してみた結果
全て上手くまとめることが出来ました。
本当に助かりました(;_;)
ありがとうございます。

質問して、回答して頂き、
それを見て利用するだけでなく
それを元に色々応用させて頂き、
自分の中で少しでも知識として吸収できればと
思います。

また質問させて頂くこともあるかと思いますが、
宜しくお願いします。

（りんご） 2014/11/17(月) 12:40

すいません(´・ω・｀)
またまた質問させて下さい。

JKTさんの最後の式を、現在
使っているのですが

休日出勤の場合、
色々な出勤パターンがあると
実際に使い出して気づき…。

先述の条件プラス
出勤が午前中だけの時や午後からだけの時もあり
困っています。

１、8:10以降に出勤し12時までに退社の場合は休憩なし(９時出勤の場合もあります)
２、午後13:00から出勤の場合も休憩なし
但し、18:30以降残業する場合は休憩15分

この条件を追加したいのですが…。
宜しくお願いします(_ _)

3、

（りんご） 2014/11/21(金) 11:21

訂正
２の場合15:00からの10分休憩はあります…。

度々すいません。
（りんご） 2014/11/21(金) 12:56

 休憩時間を「なし」にしたいんだったら、区分を「1」にすればいいと思うんですが、
 それはできないということですか？（何か他に影響するとか？）

 いちばん簡単なのは休憩時間も表に入力することなんですが、
 これも無理なんでしょうか？

 だとして

 > 8:10以降に出勤し12時までに退社 の場合は休憩なし

 12:00までの退社でも、8:10より前の出勤だったら休憩時間があるということですか？
 7:00～12:00の勤務だったら休憩時間は何分ですか？
 早朝出勤の休憩時間ということなら、どういう場合に何分の休憩時間があるのかを明確にして下さい。

 > ２の場合15:00からの10分休憩はあります…。

 15:00からの10分休憩はどういう場合に与えられるんですか？
 退社時間が15:00よりあとの人は全員ですか？

 もしそうだったら
 9:00～18:30勤務は休憩時間　1:10（1時間＋10分）
 9:00～20:00勤務は休憩時間　1:25（1時間15分＋10分）
 ということですか？

 出勤が15:00よりあとになることもありえますか？
　
　
 その他、可能性のある出勤パターンを考えて
 もう一度条件を整理してみてください。
　
（JKT） 2014/11/21(金) 15:14

区分から、平日の残業時間と休日出勤の時間の集計を出しているので
分けておきたいんです(-.-;)

私がきちんと整理出来てないまま質問してしまいました、すいません。

早朝休憩など午前中の休憩はありません。
「以降に」とつけてしまい、ややこしくなりました。

そして、15時からの10分休憩なんですが皆に与えられています。

会社の休憩は、以下の通りです。
･昼休憩→12時～50分間…昼をまたげば皆に与えられます。

･15時からの休憩→15時～10分間…15時になれば皆に与えられます。
15時までに退社の時、15時以降からの出勤の場合は与えられない。

･残業前休憩→15分間…退社が18:30を過ぎる場合に与えられる。

先述の条件の際に、
休憩時間1時間と記載していたのは、
私が昼休憩50分＋10分を足しての1時間です。

出勤時間が15時以降になる場合も稀にあります。
15時から２時間だけ…とかそんなパターンも有り得ます。

ややこしくなりまして、申し訳ないです…。

（りんご） 2014/11/21(金) 16:17

 E2セル =IF(COUNT(A2:D2)<4,"",(D2-C2-(B2=0)*(MAX(0,MIN("12:50",D2)-MAX("12:00",C2))+MAX(0,MIN("15:10",D2)-MAX("15:00",C2))+MIN("0:15",(D2-C2)*(D2-"18:30">0)*(C2-"18:30"<0))))*24)
 下にコピー

  行 _______A_______ _B_ ____C____ __D__ ____E____
   1                     残業開始  退社  残業時間 
   2 2014/11/20(木)    1 17:15     22:15        5 
   3 2014/11/21(金)    1 17:00     18:30      1.5 
   4 2014/11/22(土)    0  8:10     20:15 10.83333 
   5 2014/11/23(日)    0  8:10     18:30 9.333333 
   6 2014/11/24(月)    0  8:10     18:42 9.283333 
   7 2014/11/25(火)    0  9:00     12:00        3 
   8 2014/11/26(水)    0 13:00     18:30 5.333333 
   9 2014/11/27(木)    0 13:00     18:31      5.1

（半平太） 2014/11/21(金) 22:16

 ロジックミスがあったので修正しました。2014/11/24(月) 0:29

コメント返信：

[ 一覧(最新更新順) ]

[[20141113130138]] 『複数条件のある残業時間の集計表について』（りんご） ページの最後に飛ぶ

[[20141113130138]]　『複数条件のある残業時間の集計表について』（りんご）　ページの最後に飛ぶ