Hướng dẫn cách tính công theo ký hiệu kép trong bảng chấm công Excel

Trong bài viết Hướng dẫn cách lập bảng chấm công theo ca và làm thêm giờ trên Excel chúng ta đã biết cách tạo bảng chấm công trong những đơn vị làm việc 3 ca với làm thêm theo từng ca. Nhưng không phải trường hợp nào chúng ta cũng chỉ chấm công theo 1 ký hiệu đơn (chỉ 1 ký hiệu thể hiện 1 loại công). Có những lúc chúng ta cần tới ký hiệu kép, trong đó kết hợp nhiều ký hiệu đơn để thể hiện nhiều loại công trong cùng 1 ca. Trong bài viết này chúng ta cùng tìm hiểu cách tính công theo ký kép trong bảng chấm công Excel.

Xem nhanh

Chấm công bằng ký hiệu kép (công kép)

Nếu trong 1 ca làm việc, người lao động nghỉ nửa ca và làm nửa ca thì chúng ta chấm công thế nào?

Nếu trong 1 ca làm việc, người lao động được đào tạo chuyên môn nghiệp vụ nửa ca, còn lại nửa ca sẽ quay trở lại làm việc, vậy chấm công thế nào?

Trên đây là 2 ví dụ tiêu biểu cho trường hợp 1 ca làm việc có tới 2 loại công khác nhau. Trong những trường hợp này chúng ta thường sử dụng chấm công bằng ký hiệu kép.

Ví dụ như sau:

Ký hiệu chấm công:

HC : Làm đủ 1 ca
HC/P: Làm nửa ca, còn nửa ca là nghỉ phép
HC/K: Làm nửa ca, còn nửa ca là nghỉ có phép
HC/H: Làm nửa ca, còn nửa ca là đi học

Trên bảng chấm công ta có như sau:

Công thức tính công theo ký hiệu kép

Xét các tiêu chí xác định công làm việc:

Với công làm việc là HC, ta thấy có 2 cách tính:

Ô chỉ có đúng ký hiệu là HC (không có ký hiệu nào khác) thì được tính đủ 1 công
Ô có ký hiệu kép gồm ký tự HC với ký tự khác sẽ được tính là nửa công

Như vậy chúng ta phải tách riêng 2 loại công này để tính, sau đó cộng lại để ra tổng số công làm việc trong ca.

Công thức tính làm đủ ca bằng hàm COUNTIF

Để tính riêng những ô chỉ có ký tự HC, chúng ta có thể sử dụng hàm đếm theo điều kiện như sau:

Vùng cần đếm: từ cột D tới cột AH trên cùng dòng chấm công của từng người, từng ca
Điều kiện đếm: ký tự HC (trong ô B23)

Ta có công thức tính công cho NV001, ca 1 như sau:

=COUNTIF($D5:$AH5,$B$23)

Trong đó:

D5:AH5 có thể cố định các cột D, AH (hoặc không cần cố định cũng không sao)
B23: phải cố định ô B23 để tránh tham chiếu tới vị trí ký tự chấm công HC trong ô B23 bị thay đổi khi sao chép công thức

Kết quả chấm công bằng hàm COUNTIF ta có:

Công thức tính làm nửa ca bằng hàm SUMPRODUCT

Để tính công làm nửa ca, chúng ta sẽ phải tính dựa vào ký hiệu kép. Nguyên tắc tính như sau:

Chỉ xét những ô có chấm công ký hiệu kép là ô có số ký tự >2
Xét phần bên trái, trong khoảng 2 ký tự đầu, phải là ký tự HC

Để đáp ứng cả 2 yêu cầu trên, chúng ta có thể sử dụng hàm SUMPRODUCT để đếm theo nhiều điều kiện như sau:

=SUMPRODUCT((LEFT($D5:$AH5,2)=$B$23)*(LEN($D5:$AH5)>2)*1)

Trong đó:

LEFT($D5:$AH5,2)=$B$23 là xét phần bên trái, 2 ký tự đầu trong vùng chấm công có bằng ký tự HC tại ô B23 hay không
LEN($D5:$AH5)>2 là xét chỉ những ô có trên 2 ký tự (từ 3 ký tự trở lên)

Tham khảo:

Hàm LEFT, RIGHT và cách sử dụng hàm LEFT, RIGHT trong Excel

Hàm LEN, hàm đếm số ký tự trong chuỗi

Vì những kết quả thu được của hàm SUMPRODUCT này chỉ tương đương với 0,5 công bình thường. Do đó chúng ta có thể nhân với 0,5 để tính gộp vào tổng số công

Tổng số công = Đủ công + Nửa công / 2

AK5 = AI5 + Ạ5/2

Kết quả hoàn chỉnh bảng chấm công như sau: