Excelで勤怠管理表作成で困っています

質問

初心者です。使い勝手が悪く、困っています。
8：30～17：00　休憩60分　休憩は手入力します。
勤務区分があります。2種に分類。それぞれの時間累計を求めます。
区分は1～5をa 10～15をb の分類とします。関数で上手く分類できない為、下記のようにa始a終b始b終と入力しています。
A1　　 B1　 C1　 D1　 E1 F1 G1 H1
区分 a始 a終 b始 b終 休 計a 計b
1a 830 900 － － － － 0.50 　
12b － － 900 1400 60 － 4.0 　 　
3a 1400 1500 － － － － 　1.00 　
15b － － 1500 1700 － － 2.0　　　　　　
G1=(TEXT(C2,"0!:00")-TEXT(B2,"0!:00")-TEXT(F2,"0!:00"))*24
あとはそれぞれ計G1、H1で累計を出しています。

始終はab（区分関係なく）同じ欄に入力し、累計でa.bがそれぞれ求められる方法はないでしょうか。
シンプルに入力し、分類a.bで集計したいのです。
区分 　開始 終了 休憩
1 　 830 900
12 　900 1400 60 　 　
3 　 1400 1500
15 　 1500 1700 　
計a 1.50
計b 6.00

回答No.2

ana_777

区分を入力されるのであれば事は簡単だと思います。各行に小計欄を二つ作成し、その小計欄に次の関数を記載します。区部の列をＡ列として記載しています。
Ａ区分小計列　=IF(1<=$A1<=10,$C1-$B1-$D1,"")
Ｂ区分小計列　=IF(10<$A1<=20,$C1-$B1-$D1,"")
それぞれの列を集計すれば、ａ区分ｂ区分の合計がでます。
なお、ｂ列は開始時間　ｃ列は終了時間　ｄ列は休憩時間　a区分の区分入力は１から１０まで、b区分の区分入力は１１から２０までの整数です。。
記入に際しては、開始時間　１３：００などのように記入。休憩時間も１時間であれば１：００のように記入してください。

お礼

ありがとうございます。
頑張ってみます。
何かまたありましたら、宜しくお願いいたします。

回答No.1

uruz2

開始時間、終了時間と区分の関係を見ると次の6種類です
(1)区分の前に終了
(2)区分の前に開始、区分中に終了
(3)区分の前に開始、区分後に終了
(4)区分中に開始、区分中に終了
(5)区分中に開始、区分後に終了
(6)区分後に開始

開始時間をB、終了時間をC、区分の開始をS1終了をE1、区分の中で実際の開始時間をS2、実際の終了時間をE2とすると
(1)S2←S1,E2←S1
(2)S2←S1,E2←C
(3)S2←S1,E2←E1
(4)S2←B,E2←C
(5)S2←B,E2←E1
(6)S2←C,E2←C

これを見てもわかると思いますが
if B < S1 then S2←S1
else if B < E1 then S2←B
else S2←C

if C < S1 then E2←S1
else C < E1 then E2←C
else E2←E1
です。
これをExcelの関数IFで示すと
S2=IF(B<S1,S1,IF(B<E1,B,C))
E2=IF(C<S1,S1,IF(C<E1,C,E1))
で、この区分の勤務時間はE2-S2です。
これを15区分について各々計算しab各々累計すれば算出できます。

実はExcel使ったこと無いので、おかしいところは遠慮なく指摘してください。

お礼

丁寧にご回答をありがとうございます。
勉強になります。
またなにかありましたら、宜しくお願いいたします。