Giúp đỡ xử lý dữ liệu chấm công tìm giờ vào/ra cho ca gãy ( ca xoay )

[kienphamiuh]

Em xin chào các anh/chị ! hiện tại em đang gặp 1 số vấn đề xử lý dữ liệu chấm công như sau mong anh/chị giúp đỡ!
- Thực trạng công ty em đang làm việc như sau :
+ Công ty làm trong lĩnh vực dịch vụ, lịch làm việc của nhân viên không cố định ( làm ca gãy ), thời gian vào/ra ca làm việc thay đổi theo ngày.
+ Quy mô công ty tầm 50 đến 200 nhân viên.
- Em gặp vấn đề về xử lý dữ liệu chấm công như sau :
+ Do thời gian làm việc các nhân viên không cố định vì thế mỗi lần chấm công em phải làm thủ công như sau: từ dữ liệu thô máy chấm công đối chiếu với lịch làm việc trong tháng để xác định giờ ra/vào của nhân viên, từ đó lấy giờ vào/ra copy vào file chấm công mới bắt đầu tính công được ạ
Khó khăn khi xác định giờ vào/ra của nhân viên như sau :
+ Phải đối chiếu thủ công dữ liệu chấm công với lịch làm việc gốc để xác định giờ vào/ra của nhân viên
+ Nhân viên bấm vân tay nhiều lần: phải copy dữ liệu chấm công của lần bấm đầu tiên đối với “vào ca” và lần bấm cuối cùng đối với “ra ca”
+ Nhân viên quên bấm vân tay : xác định giờ đã bấm vân tay thuộc giờ vào/ra của ca nào để cho vào đúng vị trí
+……….
>> Mục tiêu cuối của em là làm ra bảng giờ vào/ra chính xác nhất để tính ngày công , mong anh/chị giúp đỡ và góp ý, em làm thụ công đối chiếu hai “dữ liệu lịch làm việc” và “dữ liệu máy chấm công” lâu lắm ạ
Trong file đính kèm em lấy lịch làm việc và dữ liệu chấm công của 1 số bạn nhân viên thôi ạ!

 

[dovanhoc84]

mỗi người có max 6 thời điểm ra vào thôi hả bạn?
Nhỡ bác nào "Quá rảnh" bác ý bấm vân tay chục lần thì dữ liệu ghi kiểu gì hay vẫn chỉ có 6 lần?

 

[kienphamiuh]

mỗi người có max 6 thời điểm ra vào thôi hả bạn?
Nhỡ bác nào "Quá rảnh" bác ý bấm vân tay chục lần thì dữ liệu ghi kiểu gì hay vẫn chỉ có 6 lần?

đúng rồi anh nếu bấm chục lần là máy ghi lại chục lần anh ạ, em tính công mấy tháng nay thì em thấy cao nhất là 6 lần là nhiều lắm rồi ạ
mỗi người làm 1 ngày cao nhất là 4 lần, bấm chuẩn là bấm 2 ra 2 vào, các trường hợp 6 lần là người ta tưởng chưa bấm nên bấm thêm ạ

 

[Phan Thế Hiệp]

đúng rồi anh nếu bấm chục lần là máy ghi lại chục lần anh ạ, em tính công mấy tháng nay thì em thấy cao nhất là 6 lần là nhiều lắm rồi ạ
mỗi người làm 1 ngày cao nhất là 4 lần, bấm chuẩn là bấm 2 ra 2 vào, các trường hợp 6 lần là người ta tưởng chưa bấm nên bấm thêm ạ

Chưa vừa ý lắm, nên bạn có thể tham khảo trước xem sao:
Công thức cho ô M3 của các sheet chi tiết:

=IFERROR(IF(COUNT(1/(1/$E3:$J3)/(MATCH(CEILING($E3:$J3*24,0.5),INDEX(CEILING($E3:$J3*24,0.5),),)=COLUMN($A:$F)))=4,AGGREGATE(15,6,1/(1/$E3:$J3)/(MATCH(CEILING($E3:$J3*24,0.5),INDEX(CEILING($E3:$J3*24,0.5),),)=COLUMN($A:$F)),COLUMN(A$1)),AGGREGATE(15,6,1/(1/$E3:$J3)/(MATCH(CEILING($E3:$J3*24,0.5),INDEX(CEILING($E3:$J3*24,0.5),),)=COLUMN($A:$F))/(MATCH(1/(1/$E3:$J3)/(MATCH(CEILING($E3:$J3*24,0.5),INDEX(CEILING($E3:$J3*24,0.5),),)=COLUMN($A:$F)),1/(1/OFFSET(LICHLV!$A$4,MATCH($C3,LICHLV!$A$5:$A$35,),MATCH($B3,LICHLV!$B$2:$AZ$2,),,4) )-1.75/24)=COLUMN(A$1)),1)),"")

Kết thúc bằng Ctrl+Shift+Enter.

Bạn tự kiểm tra xem thử còn chỗ nào chưa đúng không nha!

Thân

 

[kienphamiuh]

Chưa vừa ý lắm, nên bạn có thể tham khảo trước xem sao:
Công thức cho ô M3 của các sheet chi tiết:

=IFERROR(IF(COUNT(1/(1/$E3:$J3)/(MATCH(CEILING($E3:$J3*24,0.5),INDEX(CEILING($E3:$J3*24,0.5),),)=COLUMN($A:$F)))=4,AGGREGATE(15,6,1/(1/$E3:$J3)/(MATCH(CEILING($E3:$J3*24,0.5),INDEX(CEILING($E3:$J3*24,0.5),),)=COLUMN($A:$F)),COLUMN(A$1)),AGGREGATE(15,6,1/(1/$E3:$J3)/(MATCH(CEILING($E3:$J3*24,0.5),INDEX(CEILING($E3:$J3*24,0.5),),)=COLUMN($A:$F))/(MATCH(1/(1/$E3:$J3)/(MATCH(CEILING($E3:$J3*24,0.5),INDEX(CEILING($E3:$J3*24,0.5),),)=COLUMN($A:$F)),1/(1/OFFSET(LICHLV!$A$4,MATCH($C3,LICHLV!$A$5:$A$35,),MATCH($B3,LICHLV!$B$2:$AZ$2,),,4) )-1.75/24)=COLUMN(A$1)),1)),"")

Kết thúc bằng Ctrl+Shift+Enter.

Bạn tự kiểm tra xem thử còn chỗ nào chưa đúng không nha!

Thân

hay quá anh ơi được 95% rồi ạ! nhưng còn 1 chỗ chưa đúng lắm, khung giờ ra mình sẽ lấy khung giờ ra cuối cùng đó anh. ý là lúc vào ca thì lấy thời gian sớm nhất ( trong file đang làm đúng ), lúc ra ca sẽ lấy thời gian trễ nhất ạ
vd: nv1 ra lúc 20:30 theo lịch làm việc, nv1 bấm máy 2 lần, lần 1 là chấm công lúc 20:19, do có việc phát sinh của khách hàng phải ở lại giải quyết đến 21:00 >> chấm công lần 2 là 21:00, sếp em muốn lấy giờ chấm công ra về trễ nhất để tính thêm lương cho nhân viên anh ạ

 

[Phan Thế Hiệp]

hay quá anh ơi được 95% rồi ạ! nhưng còn 1 chỗ chưa đúng lắm, khung giờ ra mình sẽ lấy khung giờ ra cuối cùng đó anh. ý là lúc vào ca thì lấy thời gian sớm nhất ( trong file đang làm đúng ), lúc ra ca sẽ lấy thời gian trễ nhất ạ
vd: nv1 ra lúc 20:30 theo lịch làm việc, nv1 bấm máy 2 lần, lần 1 là chấm công lúc 20:19, do có việc phát sinh của khách hàng phải ở lại giải quyết đến 21:00 >> chấm công lần 2 là 21:00, sếp em muốn lấy giờ chấm công ra về trễ nhất để tính thêm lương cho nhân viên anh ạ

Cái 5% còn lại mà bạn đã nói rõ ở trên là 5% "khổ ải vô biên" đó!

Để tôi xem lại hén! Nhưng huởn huởn chút nha!

Thân

 

[Phan Thế Hiệp]

hay quá anh ơi được 95% rồi ạ! nhưng còn 1 chỗ chưa đúng lắm, khung giờ ra mình sẽ lấy khung giờ ra cuối cùng đó anh. ý là lúc vào ca thì lấy thời gian sớm nhất ( trong file đang làm đúng ), lúc ra ca sẽ lấy thời gian trễ nhất ạ
vd: nv1 ra lúc 20:30 theo lịch làm việc, nv1 bấm máy 2 lần, lần 1 là chấm công lúc 20:19, do có việc phát sinh của khách hàng phải ở lại giải quyết đến 21:00 >> chấm công lần 2 là 21:00, sếp em muốn lấy giờ chấm công ra về trễ nhất để tính thêm lương cho nhân viên anh ạ

Để thực hiện một công thức dùng chung cho 4 cột thì sẽ khó trong việc phân định: "Giờ vào lấy Nhỏ, Giờ Ra lấy Lớn".
Do vậy, tôi thực hiện công thức khác nhau cho 4 cột, như sau:

M3=IFERROR(IF(MATCH(AGGREGATE(15,6,1/(1/E3:J3),1),INDEX(1/(1/OFFSET(LICHLV!$A$4,MATCH($C3,LICHLV!$A$5:$A$35,),MATCH($B3,LICHLV!$B$2:$AZ$2,),,4))-1.75/24,))=1,AGGREGATE(15,6,1/(1/E3:J3),1),""),"")
N3=IFERROR(AGGREGATE(14,6,1/(1/$E3:$J3)/(MATCH(1/(1/$E3:$J3)/(MATCH(MROUND($E3:$J3*24,2),INDEX(MROUND($E3:$J3*24,2),),)=COLUMN($A:$F))/(MROUND($E3:$J3*24,2)<>IFERROR(MROUND(M3*24,2),))/(MROUND($E3:$J3*24,2)<>IFERROR(MROUND(P3*24,2),))/(MROUND($E3:$J3*24,2)<>IFERROR(MROUND(O3*24,2),)),1/(1/OFFSET(LICHLV!$A$4,MATCH($C3,LICHLV!$A$5:$A$35,),MATCH($B3,LICHLV!$B$2:$AZ$2,),,4))-1.75/24)<3),1),"")
O3=IFERROR(AGGREGATE(15,6,1/(1/$E3:$J3)/(MATCH(1/(1/$E3:$J3)/(MATCH(MROUND($E3:$J3*24,2),INDEX(MROUND($E3:$J3*24,2),),)=COLUMN($A:$F))/(MROUND($E3:$J3*24,2)<>IFERROR(MROUND(M3*24,2),))/(MROUND($E3:$J3*24,2)<>IFERROR(MROUND(P3*24,2),)),1/(1/OFFSET(LICHLV!$A$4,MATCH($C3,LICHLV!$A$5:$A$35,),MATCH($B3,LICHLV!$B$2:$AZ$2,),,4))-1.75/24)>2-(P3<>"")*(M3="")),1),"")
P3=IFERROR(IF(MATCH(AGGREGATE(14,6,1/(1/E3:J3),1),INDEX(1/(1/OFFSET(LICHLV!$A$4,MATCH($C3,LICHLV!$A$5:$A$35,),MATCH($B3,LICHLV!$B$2:$AZ$2,),,4))-1.75/24,))>2+(COUNT(E3:J3)<2),AGGREGATE(14,6,1/(1/E3:J3),1),""),"")

Enter, rồi copy cả hàng xuống. Chép bảng cả 4 cột công thức này dán cho các sheet sau.

Xem file kèm.

Thân

 

[kienphamiuh]

Để thực hiện một công thức dùng chung cho 4 cột thì sẽ khó trong việc phân định: "Giờ vào lấy Nhỏ, Giờ Ra lấy Lớn".
Do vậy, tôi thực hiện công thức khác nhau cho 4 cột, như sau:

M3=IFERROR(IF(MATCH(AGGREGATE(15,6,1/(1/E3:J3),1),INDEX(1/(1/OFFSET(LICHLV!$A$4,MATCH($C3,LICHLV!$A$5:$A$35,),MATCH($B3,LICHLV!$B$2:$AZ$2,),,4))-1.75/24,))=1,AGGREGATE(15,6,1/(1/E3:J3),1),""),"")
N3=IFERROR(AGGREGATE(14,6,1/(1/$E3:$J3)/(MATCH(1/(1/$E3:$J3)/(MATCH(MROUND($E3:$J3*24,2),INDEX(MROUND($E3:$J3*24,2),),)=COLUMN($A:$F))/(MROUND($E3:$J3*24,2)<>IFERROR(MROUND(M3*24,2),))/(MROUND($E3:$J3*24,2)<>IFERROR(MROUND(P3*24,2),))/(MROUND($E3:$J3*24,2)<>IFERROR(MROUND(O3*24,2),)),1/(1/OFFSET(LICHLV!$A$4,MATCH($C3,LICHLV!$A$5:$A$35,),MATCH($B3,LICHLV!$B$2:$AZ$2,),,4))-1.75/24)<3),1),"")
O3=IFERROR(AGGREGATE(15,6,1/(1/$E3:$J3)/(MATCH(1/(1/$E3:$J3)/(MATCH(MROUND($E3:$J3*24,2),INDEX(MROUND($E3:$J3*24,2),),)=COLUMN($A:$F))/(MROUND($E3:$J3*24,2)<>IFERROR(MROUND(M3*24,2),))/(MROUND($E3:$J3*24,2)<>IFERROR(MROUND(P3*24,2),)),1/(1/OFFSET(LICHLV!$A$4,MATCH($C3,LICHLV!$A$5:$A$35,),MATCH($B3,LICHLV!$B$2:$AZ$2,),,4))-1.75/24)>2-(P3<>"")*(M3="")),1),"")
P3=IFERROR(IF(MATCH(AGGREGATE(14,6,1/(1/E3:J3),1),INDEX(1/(1/OFFSET(LICHLV!$A$4,MATCH($C3,LICHLV!$A$5:$A$35,),MATCH($B3,LICHLV!$B$2:$AZ$2,),,4))-1.75/24,))>2+(COUNT(E3:J3)<2),AGGREGATE(14,6,1/(1/E3:J3),1),""),"")

Enter, rồi copy cả hàng xuống. Chép bảng cả 4 cột công thức này dán cho các sheet sau.

Xem file kèm.

Thân

Bác ơi em thấy không ổn lắm, file này tạm ổn cho đến khi nv đi vào/về với thời gian > 2 tiếng, vd 9h làm hoặc 19h30 về mà nv đi sớm 7:00 hoặc về trễ 21:00 là thua , em nghĩ chắc không có nv nào như vậy đâu bác nhỉ