Chia sẻ công thức Excel tổng hợp ký hiệu chấm công tay

 Nhiều doanh nghiệp hiện nay vẫn sử dụng hình thức chấm công tay thông qua các ký hiệu. Ví dụ: x là 1 công, p là 1 ngày phép,...khi đó người dùng thường sử dụng hàm Countif để tổng hợp công

=COUNTIF(B1:Z1,"x") => để đếm số ký hiệu x trong bảng chấm công

Trong thực tế dữ liệu chấm công theo chỉ tiêu của doanh nghiệp sẽ có nhiều quy định về ký hiệu tùy theo nhu cầu quản lý và theo dõi Ví dụ như tình huống ký hiệu tính công đi làm: x là 1 công, x/2 là nửa công, 1.5x là 1.5 công, 2x là 2 công,...Khi đó người dùng sẽ phải dùng từng hàm COUNTIF lại với nhau để tính ra tổng công trong tháng

=COUNTIF(B1:Z1,"x")+COUNTIF(B1:Z1,"x/2")*0.5+COUNTIF(B1:Z1,"1.5x")*1.5+COUNTIF(B1:Z1,"2x")*2

Và kết quả là càng có nhiều ký hiệu chấm công thì công thức sẽ càng dài.

Hướng dẫn dùng công thức mảng kết hơp SUMPRODUCT và COUNTIF để rút ngăn công thức chấm công tay theo ký hiệu

Cùng với ví dụ của công thức bên trên là sử dung ký hiệu x là 1 công, x/2 là nửa công, 1.5x là 1.5 công, 2x là 2 công thì công thức mảng khi kết hợp giữa SUMPRODUCT và COUNTIF như sau:

=SUMPRODUCT(COUNTIF(B1:Z1,{"X","X/2","1.5X","2X"})*{1,0.5,1.5,2})

Trong đó: 

- B1:Z1: Là dòng đầu tiên của lưới dữ liệu chấm công

- {"X","X/2","1.5X","2X"} là danh sách các ký hiệu chấm công được đặt trong dấu {...}. Ký hiệu chấm công là text nên đặt trong dấu nháy kép cho từng ký hiệu và ngăn cách nhau bằng dấu phẩy. Trường hợp máy tính sử dụng dấu ; ngăn cách các tham số thì thay bằng dấu ;

- {1,0.5,1.5,2} là danh sách con số quy đổi công tương ứng với các ký hiệu ở trên. Danh sách ký hiệu có bao nhiêu phần tử thì danh sách con số quy đổi công cũng tương ứng bằng đó phần tử

=> Khi đó, nếu dùng theo phương pháp này mà có nhiều ký hiệu chấm công thì công thức sẽ được rút ngắn đi rất nhiều

Ví dụ công thức tổng hợp chấm công theo ký hiệu sử dụng hơn 26 ký hiệu. Công thức sẽ ngắn hơn nhiều so với sử dụng 26 hàm COUNTIFS cộng lại với nhau

=SUMPRODUCT(COUNTIFS(AP8:BT8,{"1","0.5","S","C","D","G","PX","CX","BX","KX","CL","CX2","CT","1.5","2","CN","L","L2","OL","OL2","CD","CD2","CX","B","KL3"},$AP$7:$BT$7,"<"&H8)*{1,0.5,1,1,1,0.5,0.5,0.5,0.5,0.5,0.5,0.5,1,1.5,2,1,1,0.5,1,0.5,1,1,0.5,1,0.5})

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334