Ngay bây giờ, bạn hãy cùng Kitudacbiet.co tìm hiểu về quy trình lập bảng chấm công Excel chi tiết nhất. Qua đó, đảm bảo hiệu quả, thể hiện tính chuyên nghiệp hoá đối với hoạt động quản trị nhân lực. Đồng thời, hệ thống giúp lưu trữ dữ liệu tốt, linh hoạt hơn so với kiểu chấm công truyền thống.

Phân chia bố cục bảng chấm công Excel

Thông thường, mô hình bảng chấm công Excel sẽ bao gồm 13 sheet. Song thực tế trong đó phân chia thành nội dung thành 2 sheet với 1 sheet làm danh sách nhân viên và 1 sheet làm 1 tháng chấm công. Hoàn thành tháng 1 sẽ copy cho các tháng tiếp theo.

Về sheet danh sách nhân viên

Nội dung ghi tên và mã của nhân viên, yêu cầu tạo mã riêng để tránh sự trùng lặp với những người tên giống nhau. Bên cạnh đó còn có ngày sinh, quê quán, số CMND/CCCD, ngày vào làm việc…

Tạo sheet nhân viên đơn giản

Tạo sheet nhân viên đơn giản

Quá trình tạo sheet nhân viên, bạn hãy để trống 2 – 3 dòng trên cùng để liên kết tới các sheet khác dễ dàng. Ngoài ra, để 1 cột dự phòng bên trái giúp bổ sung dữ liệu khi cần thiết.

Sheet tháng 1

Bạn tạo khung bảng chấm công, bao gồm các nội dung tiêu đề, tháng, bộ phận chấm công, định mức với ngày công trong tháng. Về hệ thống cột có mã nhân viên, tên nhân viên, các ngày trong tháng, 4-5 cột tính quy ra công, dành ra 1 cột để ghi chú.

 

Tạo sheet chấm công tháng 1

Tạo sheet chấm công tháng 1

Tạo ngày tháng trên bảng chấm công Exel

+ Trước tiên, bạn xác định năm sử dụng của bảng chấm công Excel rồi nhập vào ô D1.

+ Ở ô B4, xác định tháng chấm công bạn nhập hàm =date($D$1;1;1) tương ứng giá trị năm, tháng, ngày. Dấu ngăn cách trong hàm có thể sử dụng dấu ; hay dấu , tuỳ theo thiết lập máy tính.

Xác định ngày, tháng, năm bảng chấm công

Xác định ngày, tháng, năm bảng chấm công

+ Tiếp theo, tại ô B4, bạn thao tác: Format Cell -> Custom rồi nhập giá trị vào ô Type bên phải với [“tháng “mm” năm “yyyy]. Sau đó nhấn OK.

Nhập ngày, tháng, năm

Nhập ngày, tháng, năm

+ Để hiển thị đầy đủ nội dung và dễ nhìn, bạn Marge Cell từ ô B4 đến D4.

+ Tại ô ngày 1 (E9), bạn nhập =b4 xác định ngày đầu tiên của tháng.

+ Sang ô F9, bạn nhập =e9+1 dùng cho ngày tiếp theo trong tháng.

+ Copy ô F9  sang các ô bên cạnh bằng cách bôi đen hết các ô ngày rồi bấm Ctrl + R. Hoặc thực hiện theo hình:

 

+ Tiếp tục, bôi đen ô E9 đến AI9, chọn Format Cells -> Custom, tại mục Type bạn gõ chữ dd, sau đó nhấn OK.

Thao tác chỉ hiển thị ngày

Thao tác chỉ hiển thị ngày

+ Tại ô E10 của bảng chấm công Excel, bạn nhập hàm =CHOOSE(WEEKDAY(E9);”Chủ nhật”;”T. hai”;”T. ba”;”T. tư”;”T. năm”;”T. sáu”;”T. bảy”).

Nhập hàm trả giá trị ngày chấm công

Nhập hàm trả giá trị ngày chấm công

Trong đó:

  • Choose: Hàm chọn giá trị với giá trị đầu tiên chọn làm căn cứ xác định.
  • Weekday(E9): Lấy giá trị ngày trong tuần của ô E9, thường bắt đầu tư Chủ nhật, kết thúc là thứ 7 với giá trị số trả về từ 1 – 8.

+ Copy công thức ô E10 vừa hoàn thành sang các ô kế tiếp chỉ ngày cho đến hết.

Copy giá trị hàm cho tất cả các ngày trong tháng

Copy giá trị hàm cho tất cả các ngày trong tháng

+ Thứ 7 và chủ nhật là những ngày thường được tự động đổi màu so với các thứ khác trong tuần. Bạn thao tác theo hướng dẫn:

Bôi đen toàn bộ nội dung ngày tháng, chọn Conditional Formatting -> Condittional Formatting, chọn New Rule

Bôi đen toàn bộ nội dung ngày tháng, chọn Conditional Formatting -> Condittional Formatting, chọn New Rule

Tại New Formatting Rule, chọn dòng cuối của mục Select a Rule Type

Tại New Formatting Rule, chọn dòng cuối của mục Select a Rule Type

Nhập hàm =if(weekday(e$9)=1;true;false) vào Format values where this formula is true, nhấn Format chọn định dạng

Nhập hàm =if(weekday(e$9)=1;true;false) vào Format values where this formula is true, nhấn Format chọn định dạng

Kết quả Conditional formatting cho ngày chủ nhật

Kết quả Conditional formatting cho ngày chủ nhật

Thực hiện tương tự đối với ngày thứ 7 theo hàm  =if(weekday(e$9)=7;true;false).

Kết quả định dạng tự động đổi màu cho ngày thứ 7 và chủ nhật

Kết quả định dạng tự động đổi màu cho ngày thứ 7 và chủ nhật

+ Định dạng các cột không chứa ngày, phổ biến nhất đối với tháng 2 trong năm. Bảng chấm công sẽ tự động bôi đen ngày để trống khi bạn thực hiện:

  • Tại ô B4, sửa tạm giá trị tháng là 2.
  • Bôi đen vùng trong bảng rồi tương tự thao tác như đổi màu ngày thứ 7, chủ nhật với hàm được nhập là =if(e$9=””;true;false). Sau đó, bạn chọn định dạng là màu xám cho chữ và nền.

Kết quả thu được sau khi định dạng tháng 2

Kết quả thu được sau khi định dạng tháng 2

Đặt ký hiệu chấm công

Lựa chọn một số loại chấm công tương ứng hình thức mà đơn vị, doanh nghiệp áp dụng.

Ký hiệu chấm công

Ký hiệu chấm công

Hàm và công thức tính công cho bảng chấm công Excel

Tại cột AJ – Ngày công thực tế, đặt hàm =COUNTIF($E11:$AI11;$G$34) ở ô AJ11. Nội dung của hàm này là đếm số lần xuất hiện giá trị ô G34 (ký hiệu chấm công ngày cộng đủ) thuộc vùng E11 – AI11 (số ngày công trong tháng của nhân viên đầu tiên – hàng 11.

Lưu ý cố định các cột E, AI để lúc copy công thức không làm ảnh hưởng vùng chấm công. Thao tác tương tự các cột theo công thức:

+ Ô AK11 (chấm nửa công): =COUNTIF($E11:$AI11;$G$35).

+ Ô AL11 (chấm ngày nghỉ nhân viên vẫn được hưởng lương): =COUNTIF($E11:$AI11;$G$36).

+ Ô AM11 (ngày nghỉ không lương): = COUNTIF($E11:$AI11;$G$37).

+ Ô AN11 (nhân viên nghỉ ốm đau, thai sản): =COUNTIF($E11:$AI11;$G$38).

Theo yêu cầu tính cụ thể mà tổng số ngày công tính có sự khác nhau. Ở ví dụ này sẽ áp dụng:

Tổng ngày công = Ngày công thực tế (AO11) + Nửa công (AJ11+AK11) x 0,5 + Nghỉ hưởng lương (AL11) + Ốm đau, thai sản (AN11).

Hoàn thành công thức, copy xuống đối với các nhân viên khác

Hoàn thành công thức, copy xuống đối với các nhân viên khác

Như vậy, từ hướng dẫn lập bảng chấm công Excel trên đây bạn sẽ rất nhanh chóng hoàn thành nhiệm vụ. Chúc bạn thành công!