Excel kế toán: Ứng dụng hàm Sumproduct – Phần 4
Hôm nay hocexcel.online gửi tới các bạn seri bài viết về chủ đề Excel với kế toán: “Ứng dụng hàm SUMPRODUCT”. Đây là một hàm có sức mạnh rất lớn, có thể thay thế hầu hết mọi hàm khi làm kế toán trên excel, hay nói cách khác khi nghĩ tới làm kế toán trên excel là chúng ta phải nghĩ ngay tới hàm SUMPRODUCT. Tại sao nó lại quan trọng như vậy? Sau đây chúng ta cùng tìm hiểu cách ứng dụng hàm SUMPRODUCT sẽ rõ.
Nội dung Seri bài viết:
Phần 2: Tính tổng phát sinh của tài khoản tổng hợp dựa trên ghi sổ của tài khoản chi tiết
Phần 3: Ứng dụng hàm SUMPRODUCT trong kế toán kho
Phần 4: Ứng dụng hàm SUMPRODUCT trong kế toán tiền lương: xử lý dữ liệu máy chấm công
….
Ứng dụng 4: Ứng dụng trong kế toán tiền lương: Xử lý dữ liệu máy chấm công vào bảng chấm công
Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:
Bài tập:
Cho bảng dữ liệu trích xuất từ máy chấm công (phần bên phải hình, từ cột AI đến AN)
Yêu cầu: tự động đưa dữ liệu cột chấm công (AN) vào bảng chấm công tháng 10 tương ứng với từng nhân viên theo từng ngày trong tháng.
Cách làm:
Bước 1: Phân tích đề bài
Ở bảng dữ liệu trích xuất từ máy chấm công chúng ta có các đối tượng sau:
- Mã NV (cột AI) tương ứng với mã NV (cột B) trong Bảng chấm công
- Ngày (Cột AJ) tương ứng với dòng 4 trong Bảng chấm công (ngày trong tháng, vùng C4:AG4)
- Kết quả chấm công (cột AN) là kết quả chúng ta cần điền từ bảng dữ liệu máy chấm công vào Bảng chấm công tương ứng với 2 điều kiện: Mã NV và Ngày.
Ở đây cột chấm công có 2 loại công (hoặc nhiều hơn, chúng ta ví dụ với 2), và kết quả yêu cầu không phải tính tổng, mà là đối chiếu dữ liệu, nên chúng ta không thể dùng được các hàm như SUMIFS mà phải sử dụng hàm COUNTIFS hoặc SUMPRODUCT. Trong seri bài viết này chúng ta sẽ ưu tiên việc xử lý bằng hàm SUMPRODUCT
Xem ngay: học Excel kế toán ở Hà Nội
Bước 2: Xây dựng nội dung hàm SUMPRODUCT
* Xét loại công là X
Hàm Sumproduct 1 = SUMPRODUCT((Cot_Ngay=C$4)*(Cot_Ma_NV=$B6)*(Cot_Cham_cong=”X”)*1)
Cot_Ngay = cột AJ
Cot_Ma_NV = cột AI
Cot_Cham_cong = cột AN
Hàm này cho kết quả >0 hoặc <0. Nếu <0 thì không có số công là X, còn nếu >0 thì có số công là X
* Xét loại công là A1
Hàm Sumproduct 2 = SUMPRODUCT((Cot_Ngay=C$4)*(Cot_Ma_NV=$B6)*(Cot_Cham_cong=”A1″)*1)
Hàm này cho kết quả >0 hoặc <0. Nếu <0 thì không có số công là A1, còn nếu >0 thì có số công là A1
Lưu ý:
- Các giá trị C4 được cố định phần hàng để khi copy công thức xuống dưới không thay đổi nội dung Ngày ở dòng 4 của Bảng chấm công
- Các giá trị B6 được cố dịnh phần cột để khi copy công thức sang bên phải không bị thay đổi nội dung Mã NV ở cột B của Bảng chấm công
- Các giá trị X hay A1 đặt trong dấu nháy kép vì là dạng ký tự cố định.
Vì 2 loại công này có thể xuất hiện cùng trên kết quả, nên ta sẽ sử dụng hàm IF để biện luận tính logic của thứ tự các nội dung:
- Nếu hàm Sumproduct 1 cho kết quả >0 thì sẽ lấy kết quả chấm công là X
- Nếu hàm Sumproduct 1 cho kết quả = 0 thì sẽ xét sang hàm Sumproduct 2
- Nếu hàm Sumproduct 2 cho kết quả > 0 thì sẽ lấy kết quả chấm công là A1
- Nếu hàm Sumproduct 2 cho kết quả = 0 thì sẽ lấy kết quả chấm công là rỗng
Nội dung hàm biểu diễn như sau:
C6 = IF(SUMPRODUCT((Cot_Ngay=C$4)*(Cot_Ma_NV=$B6)*(Cot_Cham_cong=”X”)*1)>0,”X”,IF(SUMPRODUCT((Cot_Ngay=C$4)*(Cot_Ma_NV=$B6)*(Cot_Cham_cong=”A1″)*1)>0,”A1″,””))
Chúng ta có thể copy công thức từ C6 sang các ô ngày công còn lại cho các nhân viên khác trong bảng chấm công
Kết quả cuối cùng như sau:
Cảm ơn các bạn đã luôn đồng hành cùng Học Excel Online
Khóa học mới xuất bản
