Đối với hoạt động của các công ty thì sự hiện diện của một bảng chấm công là điều không thể thiết. Tuy nhiên, khá nhiều người không biết được cách làm bảng chấm công hoàn chỉnh nhất. Nếu bảng chấm công không được làm đầy đủ thì sẽ khiến cho công việc quản lý nhân sự xảy ra những thiếu sót không đáng có. Sau đây chúng tôi sẽ chỉ cho bạn cách làm bảng chấm công trên excel đơn giản và hiệu quả,
Xem thêm: Máy chấm công – Máy chấm công vân tay thẻ từ giá rẻ
Trước khi tìm hiểu về cách làm bảng chấm công trong excel, điều đầu tiên bạn nên làm là tưởng tượng về bảng chấm công của mình bao gồm những yếu tố nào. Ví dụ những thành phần trong một bảng chấm công cần có bao gồm: số lượng các sheet, nội dung của bảng chấm công, cách thức hoạt động của bảng chấm công, …
Khi đã định hình được câu trả lời cho câu hỏi này thì bạn sẽ rất dễ dàng thực hiện theo cách làm bảng chấm công excel mà chúng tôi hướng dẫn ở dưới đây.
Cách làm bảng chấm công theo mô hình phổ biến nhất:
- File chấm công bao gồm 13 sheet, mỗi sheet sẽ tương ứng với 1 tháng [có tất cả 12 tháng] và sheet còn lại sẽ là danh sách của nhân viên trong bộ phận.
- Ký hiệu chấm công của mỗi người sẽ khác nhau và được đồng nhất trong suốt 12 tháng chấm công.
- Bảng chấm công cần ghi rõ và đầy đủ thứ, ngày ở trong tháng. Riêng với hai ngày nghỉ là thứ 7 và chủ nhật sẽ được đổi sang màu sắc khác.
- Mỗi ngày đều sẽ tự tính công cho nhân viên. Vào cuối tháng sẽ tính tổng số công của mỗi nhân viên trong tháng đó.
- Tên nhân viên được điền ở trong bảng chấm công sẽ được lấy ra từ danh sách nhân viên của công ty.
- Có thể thực hiện liên kết giữa các tháng với nhau để dễ dàng thao tác hơn.
Đây là nội dung chủ yếu cần có ở các sheet trong cách làm bảng chấm công.
>>>> Đọc ngay: Máy chiếu – Thiết bị máy chiếu chính hãng giá rẻ
Sau khi bạn đã xác định được bảng chấm công của mình sẽ được hoàn thiện như thế nào, vậy thì sau đây sẽ là cách làm bảng chấm công nhân viên một cách chi tiết:
Bố cục của các sheet
Đầu tiên bạn cần vẽ đầy đủ mô hình của 13 sheet trong trang tính excel. Tuy nhiên lúc đầu bạn chỉ cần thực hiện với mô hình 2 sheet dưới đây:
- Sheet 1: Danh sách nhân viên trong công ty
- Sheet 2: Sheet chấm công cho tháng đầu tiên. Sau đó chỉ cần copy bảng chấm công cho sheet này cho các tháng sau và đổi tên tháng là được.
Sheet nhân viên: Trong sheet này, nội dung chủ yếu mà bạn cần làm là họ tên và mã nhân viên của từng người. Mỗi người sẽ có một mã nhân viên tương ứng để tránh trường hợp trùng tên.
Ngoài ra, trong sheet danh sách nhân viên cũng cần có những thông tin cá nhân của nhân viên như: ngày sinh, quê quán, số CMND, ngày bắt đầu làm việc, …
Lưu ý: Bạn cần chừa ra khoảng từ 2 đến 3 dòng ở trên cùng của sheet để tạo liên kết giữa các sheet với nhau. Ở phía bên trái của bảng nhân viên nên để dư ra một cột dự phòng cho trường hợp cần bổ sung thêm nội dung nào đó.
Cách làm bảng chấm công trên excel
Với những hướng dẫn cách lập bảng chấm công khá đơn giả được thực hiện như sau:
Lập khung cho bảng chấm công: trong đó có các nội dung: Tiêu đề [tháng, bộ phận, tổng tổng ngày công trong tháng, …]. Ở các cột sẽ đề những nội dung như: mã số nhân viên, họ tên, các ngày trong tháng [số lượng ngày trong một tháng], 4 hoặc 5 cột quy ra công và 1 cột để ghi chú.
Tạo ngày tháng ở trong bảng chấm công:
Bạn cần phải xác định được năm sử dụng bảng chấm công này. Ví dụ năm 2014 ở D1.
Tiếp đến, xác định tháng sử dụng để chấm công bằng cách sử dụng hàm: =date[$D$1;1;1]. Hàm Date được dùng để xác định giá trị ngày tháng dựa theo giá trị
Đặt ký hiệu chấm công cho từng nhân viên:
Bạn tiến hành lựa chọn một số ký hiệu chấm công tương ứng với từng người:
- Ngày công tính trên thực tế [Đi làm đầy đủ với số ngày công]
- Nửa ngày công [tính làm nửa ngày hoặc nửa ca]
- Ngày nghỉ được hưởng nguyên lương [Đi họp, ngày phép, công tác, …]
- Ngày nghỉ không tính lương
Để tính công thực tế cho nhân viên, ta sử dụng hàm: =COUNTIF[$E11:$A11;$G$34]
- Hàm Countif được dùng để đếm số lần xuất hiện của giá.
Tương tự ở những cột khác ta sẽ đặt những công thức lần lượt như:
- Nửa ngày công: =COUNTIF[$E11:$A11;$G$35]
- Nghỉ tính lương: =COUNTIF[$E11:$A11;$G$36]
- Nghỉ không có lương: =COUNTIF[$E11:$A11;$G$37]
- Ốm đau hoặc thai sản: =COUNTIF[$E11:$A11;$G$38]
Tổng số ngày tính công sẽ được tính dựa trên yêu cầu tính công của từng đơn vị. Ví dụ như: Tổng số ngày công = Ngày công trên thực tế + Nửa ngày chấm công x 0.5 + Nghỉ hưởng lương + Ốm đau, thai sản.
Sau khi đã đặt xong công thức cho 1 nhân viên, ta sẽ tiến hành copy công thức này cho những nhân viên khác có trong danh sách.
Tìm hiểu ngay các mẫu máy chấm công được phân phối bởi CÔNG TY CỔ PHẦN CÔNG NGHỆ TFT để chọn cho mình sản phẩm phù hợp nhất: Máy chấm công thẻ từ
Trên đây là hướng dẫn cách làm bảng chấm công cơ bản và đơn giản nhất. Sẽ còn có những bảng chấm công với nội dung và cách thực hiện phức tạp hơn bạn có thể tìm hiểu thêm ở những bài viết sau. Hy vọng bạn có thể tự mình thực hiện một bảng chấm công dựa vào những hướng dẫn được nêu ở trên.
CEO & Founder tại Công ty TFT, là người tham vấn chuyên môn và kiểm duyệt nội dung tại website: //tft.vn/, nơi cung cấp thông tin giúp khách hàng nắm dõ hơn về sản phẩm dịch vụ tại TFT, hướng dẫn khách hàng cách sử dụng vận hành hiệu quả các giải pháp TFT cung cấp.
Về mô hình là 13 sheet, nhưng khi bắt đầu làm ta chỉ cần 2 sheet:
+ 1 sheet là Danh sách nhân viên [DSNV]
+ 1 sheet là Tháng 1 [làm hoàn chỉnh cho 1 tháng, các tháng sau có thể copy sheet này rồi đổi tên]
Nội dung chủ yếu trong sheet này là Tên và Mã nhân viên. Cần tạo mã nhân viên vì có thể có trường hợp trùng tên. Khi quản lý theo mã sẽ tránh trường hợp bị trùng.
Ngoài ra còn có các nội dung khác liên quan tới nhân viên, như: ngày sinh, quê quán, số CMT, ngày vào làm…
Bạn có thể tạo như sau:
+ Cột E [ngày sinh], bạn bôi đen cả cột [đưa con trỏ chuột vào vị trí trên cùng của cột – chữ E, bấm chọn cả cột], bạn định dạng Format cell/Number/Custome/ chọn dd-mm-yyyy . Tác dụng: khi bạn nhập bất kỳ dữ liệu kiểu ngày tháng vào cột này sẽ tự động đưa về dạng dd-mm-yyyy [01-01-2014]
Sheet danh sách nhân viên hầu hết là nhập bằng tay nên không có gì phức tạp.
Lưu ý: bạn chừa ra khoảng 2-3 dòng trên cùng để tạo liên kết tới các sheet khác. Bên trái cách ra 1 cột để dự phòng nếu cần bổ sung gì thêm.
Đầu tiên bạn tạo khung cho bảng chấm công, gồm các nội dung: tiêu đề – bảng chấm công, tháng, bộ phận chấm công, định mức ngày công trong tháng, các cột gồm: mã nhân viên, tên nhân viên, ngày trong tháng [31 cột tương ứng với 31 ngày – số ngày lớn nhất của 1 tháng], 4-5 cột tính quy ra công, 1 cột để ghi chú
Tiếp sau đó bạn co độ rộng của các cột sao cho gọn và dễ nhìn. Các cột ngày trong tháng có thể co lại nhỏ vừa đủ để chấm công thôi. Cột quy ra công cũng không cần rộng lắm. Chủ yếu cột tên và mã nhân viên hiển thị đủ là được.
Cách co độ rộng: bạn bôi đen các cột ngày trong tháng, quy ra công [trong ví dụ là bôi đen từ cột E đến cột AM], sau đó co độ rộng ở cột E khoảng 30 pixels [hoặc bôi đen từ cột E đến cột AM, sau đó bấm chọn command Column Width, chọn 3,13 tương ứng 30 pixels]
Vậy là bạn đã làm xong phần khung của Bảng chấm công. Tiếp theo ta sẽ thực hiện thao tác chọn ngày tháng của bảng chấm công.
– Đầu tiên ta xác định năm sử dụng. Tại ô D1 nhập giá trị của năm. Ví dụ năm 2014
– Tại ô B4 [xác định tháng chấm công], ta nhập hàm xác định ngày: =date[$D$1;1;1] Lưu ý dấu ngăn cách các thành phần trong hàm có thể là dấu ; hoặc dấu , tùy thiết lập của máy tính. Ở máy tính của tôi sử dụng dấu ;
Nội dung hàm date: xác định giá trị của ngày tháng theo giá trị xác định trong hàm. giá trị theo thứ tự là Năm, tháng, ngày. Ở hàm trên là Năm = giá trị tại ô D1, Tháng = 1, Ngày = 1
– Sau khi nhập hàm xong, tại ô B4, chọn format cell / custom / nhập giá trị [“tháng “mm” năm “yyyy] vào ô Type bên phải, xong rồi ấn ok [dấu nháy kép + tháng + dấu cách + dấu nháy kép + mm + dấu nháy kép + năm + dấu cách + dấu nháy kép + yyyy]
– Bạn có thể marge cell từ ô B4 đến D4 để cho hiển thị đủ nội dung cho dễ nhìn.
– Tại ô ngày 1 [ ô E9], bạn nhập =b4 để xác định ngày đầu tiên trong tháng.
– Tại ô F9, bạn nhập =e9+1 [ngày tiếp theo trong tháng]
– Copy công thức tại ô F9 sang các ô bên cạnh, cho đến ô ngày thứ 31 [ô AI9] bằng cách sau:
- Cách 1: Bôi đen từ ô F9 đến ô AI9, bấm Ctrl + R
- Cách 2: Bấm chuột vào ô F9, giữ chuột tại vị trí dấu chấm đen trong ô để con trỏ chuột trở thành dấu +, kéo chuột tới ô AI9 rồi thả ra
– Bôi đen từ ô E9 đến ô AI9, chọn Format cells / custom / mục Type bạn gõ chữ dd rồi bấm ok [chỉ hiện thị số ngày]
Nội dung trong ô E9 đến ô AI9 sẽ hiển thị ra ngày như trong bảng trên.
– Tại ô E10 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”]
Nội dung hàm:
- Weekday[e9] : là lấy giá trị ngày trong tuần của ô E9. Nếu nội dung trong hàm weekday không có đặt quy định về thứ tự ngày trong tuần thì sẽ tự trả về theo thứ tự Chủ nhật, thứ hai, thứ 3… thứ 7 [Bắt đầu là chủ nhật, kết thúc là thứ 7], và trả về giá trị số từ 1 đến 8
- Choose: là hàm chọn giá trị trả về. Giá trị đầu tiên trong hàm là giá trị được chọn để làm căn cứ xác định. Các giá trị tiếp theo là nội dung được trả về tương ứng theo giá trị đầu tiên.
Trong hàm trên, ngày 01/01/2014 khi dùng hàm weekday sẽ trả về giá trị là 4, khi dùng hàm choose với thứ tự tương ứng là Chủ nhật, thứ hai, thứ ba… thứ bảy thì giá trị 4 sẽ tương ứng với thứ tư. Bởi vì hàm weekday không tự trả về thứ theo tiếng việt, nên ta phải kết hợp với hàm choose để có được nội dung là thứ mấy trong tuần
– Copy công thức tại ô E10 sang các ô kế tiếp bên phải, cho tới ô AI10 [ngày thứ 31]
Tuy nhiên không phải tháng nào cũng có 31 ngày, nên để tránh việc hiển thị các ngày của tháng khác vào tháng này, ta thực hiện các thao tác sau: + Bắt đầu từ ngày thứ 29 [ô AG9], ta điều chỉnh về hàm như sau: =IF[DAY[AF9+1]=DAY[E9];””;AF9+1]
Nội dung hàm: Nếu giá trị ngày của ô AF9 +1 bằng giá trị ngày của ô E9 sẽ trả về giá trị là rỗng, nếu không bằng thì sẽ trả về giá trị ô AF9 + 1 [Nghĩa là nếu tháng 2 có 28 ngày, thì ngày thứ 29 sẽ là ngày 01/03, vậy giá trị ngày là 1, bằng với giá trị ngày của ô E9, nếu đúng như thế thì sẽ là rỗng, không hiển thị ngày 01/03. Còn nếu tháng 2 đó có 29 ngày thì sẽ hiện ngày 29]
+ Tại ô AH9, ta dùng hàm sau: =IF[AG9=””;””;if[DAY[AG9+1]=DAY[E9];””;AG9+1]]
Nội dung hàm:
- IF thứ 1: Nếu tại ô AG9 là giá trị rỗng, thì ô AH9 cũng sẽ có giá trị rỗng [tức là vào trường hợp tháng 2 có 28 ngày thì sẽ không có ngày thứ 30]
- IF thứ 2: tương tự với hàm if tại ô AG9, tức là trường hợp tháng 2 có 29 ngày, thì sẽ không hiển thị ngày 30.
+ Tại ô AI9, ta dùng hàm sau: =IF[AH9=””;””;if[DAY[AH9+1]=DAY[E9];””;AH9+1]]
Nội dung hàm:
- IF thứ 1: Nếu AH9 là giá trị rỗng, thì AI9 cũng sẽ có giá trị rỗng [tức là trường hợp không có ngày thứ 30 thì cũng không có ngày thứ 31]
- IF thứ 2: trường hợp tháng chỉ có 30 ngày thì sẽ không có ngày thứ 31
+ Khi các ô AG9, AH9, AI9 là rỗng thì hàm choose ở các ô AG10, AH10, AI10 sẽ phát sinh lỗi. Nhưng không ảnh hưởng gì đến bảng tính nên không cần lo lắng về việc này.
Như vậy ta đã thực hiện được phần các ngày trong tháng, và các thứ trong tuần.
Tiếp theo là việc tự động đổi màu cho các ngày thứ 7, chủ nhật cho khác với ngày thường
+ Bôi đen nội dung trong bảng [Từ E9 đến AI30 – tức là tất cả các nội dung về ngày trong tháng lẫn phần chấm công của các nhân viên], chọn chức năng Conditional Formatting [định dạng theo điều kiện], trong mục Condittional Formatting, chọn New Rule.
Trong bảng New Formatting Rule, bạn chọn dòng cuối cùng trong mục Select a Rule Type là Use a formula to determine which cells to format [Dùng công thức để mô tả cách định dạng cells]
+ Tại mục Format values where this formula is true, ta nhập hàm
=if[weekday[e$9]=1;true;false]
Nội dung hàm: Nếu ngày trong tuần tại ô e9 là 1 [tức là chủ nhật] thì là đúng, không thì sẽ sai. Nếu dúng thì sẽ áp dụng định dạng của Conditional formatting. Đặt dấu cố định e$9 là cố định hàng 9, tính từ cột e.
+ Sau khi đặt hàm, ta thiết lập định dạng cho CF. Trong bảng định dạng, bạn có thể chọn font chữ, màu chữ tại Font, màu nền tại Fill, kẻ đường viền ô tại Border… [giống định dạng ô của Format cells thông thường]. Chọn định dạng xong bấm ok.
Kết quả khi thực hiện Conditional formatting với điều kiện ngày chủ nhật.
+ Làm tương tự với định dạng ngày thứ 7:
Bôi đen vùng trong bảng, chọn chức năng Conditional Formatting/ New rule/ Use a formulas…, nhập hàm =if[weekday[e$9]=7;true;false] , chọn kiểu định dạng cho ngày thứ 7
Kết quả khi thực hiện định dạng thứ 7 và chủ nhật
+ Thiết lập định dạng cho các cột không chứa giá trị ngày [ví dụ tháng 2 có 28 ngày, tự động bôi đen các ngày trống để phân biệt]
Bước 1: Tại ô B4, sửa tạm giá trị tháng là 2 để làm thử
Bước 2: Bôi đen vùng trong bảng, chọn chức năng Conditional formatting / new rule / use a formulas…, nhập hàm =if[e$9=””;true;false], chọn định dạng là tô màu xám cho chữ và nền, ta có kết quả như sau:
Khi làm xong nhớ trả lại giá trị tháng 1 ở ô B4 nhé.
Phù, vậy là xong phần thiết lập ngày tháng cho bảng chấm công. Giờ còn khâu cuối cùng là đặt ký hiệu chấm công nữa là xong.
+ Bạn chọn 1 vài ký hiệu chấm công cho 1 số loại công như sau:
- Ngày công thực tế [đi làm đủ số công]:
- Nửa ngày công [làm nửa ca, nửa ngày…]
- Ngày nghỉ hưởng nguyên lương [đi học, họp, phép… nghỉ mà có lương]
- Nghỉ không lương
…. [tùy đơn vị mà chọn các hình thức chấm công khác nhau]
Lưu ý mỗi kí hiệu chấm công sẽ tương ứng với 1 cột quy ra công [hoặc có thể gộp], ngoài ra thêm 1 cột tính tổng số công
Tại ví dụ tôi đặt như sau:
- Ngày công thực tế: x
- Nửa ngày công : v
- Ngày nghỉ hưởng nguyên lương: P
- Nghỉ không lương: K
- Ốm đau, thai sản: O
Tại cột Ngày công thực tế [cột AJ], tại ô AJ11, đặt hàm sau:
=COUNTIF[$E11:$AI11;$G$34]
Nội dung hàm: Đếm số lần xuất hiện của giá trị tại ô G34, trong vùng E11 đến AI11. Giá trị tại ô G34 là ký hiệu chấm công của ngày công đủ, vùng E11 đến AI11 là số ngày công trong tháng của người đầu tiên [hàng 11], cố định cột E và AI để khi copy công thức không bị ảnh hưởng tới vùng chấm công
Tương tự với các cột khác, ta đặt công thức:
- Ô AK11 [Nửa công] = countif[$E11:$AI11;$G$35]
- Ô AL11 [Nghỉ hưởng lương] = countif[$E11:$AI11;$G$36]
- Ô AM11 [nghỉ không lương] = countif[$E11:$AI11;$G$37]
- Ô AN11 [ốm đau, thai sản] = countif[$E11:$AI11;$G$38]
Tổng số công sẽ tính tùy theo yêu cầu tính công của đơn vị. Ví dụ: tổng ngày công = Ngày công thực tế + Nửa công x 0,5 + Nghỉ hưởng lương + Ốm đau, thai sản
Ô AO11 = AJ11+AK11*0,5+AL11+AN11
Sau khi đặt công thức xong, ta copy công thức xuống cho các nhân viên khác
Khi hoàn thiện sẽ có như sau:
Như vậy là đã hoàn thiện xong việc chấm công, tạo ký hiệu và tính số công. Giờ bạn có thể kiểm tra công thức bằng cách chấm thử vài ký hiệu vào bảng để kiểm tra.
Như vậy là ta đã hoàn chỉnh xong bảng chấm công của tháng 1. Giờ chỉ còn việc copy ra các tháng còn lại.