給与計算 一日数回の入出時間ある時の計算式

質問

タイムカードのデータをCVS出力したものを、エクセルの関数設定してあるものに張り付けて、給与計算をすることが目的です。

過去ログなどをみて、なんとかもう少し？というところまでできたのですが、どうしても式がうまくできないところがありますので、どうかご教示お願いいたします。

CVSデータは

1 2013/5/31 9:50
3 2013/5/31 12:46
4 2013/5/31 12:55
3 2013/5/31 14:24
4 2013/5/31 15:58
2 2013/5/31 18:17
1 2013/6/3 9:47
2 2013/6/3 11:25

となっております。　一日に１、2回の休憩がある日もあります。１列目は1＝出勤、2＝退勤、3＝休憩入り、4＝休憩戻りです。

「Kで勤務時間（休憩控除前）を計算」し、Lで休憩時間を１回ごと5分単位でまるめて、後に勤務時間や残業など計算しようと思っています。

できないところは「K行」です。

今の表は （添付あり）

A B C D E F G H 　 I J K L
1 2013/5/31 9:25　 9:30 1 10:00 10:00
3 2013/5/31 12:46　 12:46
4 2013/5/31 12:55 　 12:55 0:10
3 2013/5/31 14:24 14:24
4 2013/5/31 15:58 　 15:58 1:35
2 2013/5/31 18:17 18:30 ??
1 2013/6/3 9:47 10:00 2 11:00 11:00
2 2013/6/3 19:25 19:15 ??

A～C・・張り付け　　D・・出勤丸め後（15分切り上げ）、　E・・シフト種類、
F・・シフト時間、　G・・DorFの大きい方　　H・・退勤丸め後（15分切り捨て）、　I・・休憩入り、　J・・休憩戻り、　K・・勤務時間（休憩控除前）
L・・休憩時間（5分丸め後）

要望としましては、K勤務時間＝Aが2の場合、（Hから、Bで同じ日のうち、Aが1のGをひく）Aが2以外は空欄）です。
色々ためして=IF(A10=2,H10-(MATCH(B10,B5:B21,0)+(VLOOKUP("1",A5:A21,7),"")))
にたどりつきましたが、「正しくありません」がでます。

どうぞご返答おまちしております！宜しくお願いいたします！

バージョン　Excel2010　OSwindous7

回答No.1

KURUMITO · ベストアンサー

データが５行目から５０行目までに入力されているとしたらＫ５セルには次の式を入力して下方にドラッグコピーします。

=IF(A5=2,INDEX(H$5:H$50,MATCH(B5,B$5:B$50,0)+COUNTIF(B$5:B$50,B5)-1)-INDEX(D$5:D$50,MATCH(B3,B$5:B$50,0)),"")

セルの表示形式は時刻から設定します。

お礼

早速のご回答を有難うございました！

計算出来ました！

勉強になります、貴重なお時間を有難うございました！