Hướng dẫn tạo bảng chấm công trên Excel vô cùng đơn giản
Về mô hình là 13 sheet, nhưng lúc khởi đầu làm ta chỉ cần 2 sheet:
+ 1 sheet là Danh sách viên chức (DSNV)
+ 1 sheet là Tháng 1 (làm hoàn chỉnh cho 1 tháng, những tháng sau mang thể copy sheet này rồi đổi tên)
Sheet Danh sách viên chức:
Nội dung chủ yếu trong sheet này là Tên và Mã viên chức. Cần tạo mã viên chức vì mang thể mang trường hợp trùng tên. Lúc quản lý theo mã sẽ tránh trường hợp bị trùng.
Ngoài ra còn mang những nội dung khác liên quan tới viên chức, như: ngày sinh, quê quán, số CMT, ngày vào làm… Bạn mang 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 tìm cả cột), bạn định dạng Format cell/Quantity/Custome/ tìm dd-mm-yyyy . Tác dụng: lúc bạn nhập bất kỳ dữ liệu kiểu tháng ngày vào cột này sẽ tự động đưa về dạng dd-mm-yyyy (01-01-2014)
Sheet danh sách viên chức hồ hết là nhập bằng tay nên ko mang 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 những 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.
Sheet Tháng 1
Trước tiên bạn tạo khung cho bảng chấm công, gồm những nội dung: tiêu đề – bảng chấm công, tháng, phòng ban chấm công, định mức ngày công trong tháng, những cột gồm: mã viên chức, tên viên chức, ngày trong tháng (31 cột tương ứng với 31 ngày – số ngày to nhất của 1 tháng), 4-5 cột tính quy ra sức, 1 cột để ghi chú
Tiếp sau đó bạn co độ rộng của những cột sao cho gọn và dễ nhìn. Những cột ngày trong tháng mang thể co lại nhỏ vừa đủ để chấm công thôi. Cột quy ra sức cũng ko cần rộng lắm. Chủ yếu cột tên và mã viên chức hiển thị đủ là được.
Cách co độ rộng: bạn bôi đen những cột ngày trong tháng, quy ra sức (trong ví dụ là bôi đen từ cột E tới cột AM), sau đó co độ rộng ở cột E khoảng 30 pixels (hoặc bôi đen từ cột E tới cột AM, sau đó bấm tìm command Column Width, tìm 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 tìm tháng ngày của bảng chấm công.
Tạo tháng ngày trong bảng chấm công
– Trước 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 những thành phần trong hàm mang 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 tháng ngày theo giá trị xác định trong hàm. giá trị theo trật 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 lúc nhập hàm xong, tại ô B4, tìm format cell / customized / nhập giá trị [“tháng “mm” năm “yyyy] vào ô Kind bên phải, xong rồi ấn okay (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)
Xem thêm: Định dạng tháng ngày trong Excel
– Bạn mang thể marge cell từ ô B4 tới 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 trước 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 những ô không tính, cho tới ô ngày thứ 31 (ô AI9) bằng cách sau:
- Cách 1: Bôi đen từ ô F9 tới ô 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 tới ô AI9, tìm Format cells / customized / mục Kind bạn gõ chữ dd rồi bấm okay (chỉ hiện thị số ngày) Nội dung trong ô E9 tới ô 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 ko mang đặt quy định về trật tự ngày trong tuần thì sẽ tự trả về theo trật tự Chủ nhật, thứ hai, thứ 3… thứ 7 (Khởi đầu là chủ nhật, kết thúc là thứ 7), và trả về giá trị số từ 1 tới 8
- Select: là hàm tìm giá trị trả về. Giá trị trước tiên trong hàm là giá trị được tìm để làm căn cứ xác định. Những giá trị tiếp theo là nội dung được trả về tương ứng theo giá trị trước tiên.
Trong hàm trên, ngày 01/01/2014 lúc sử dụng hàm weekday sẽ trả về giá trị là 4, lúc sử dụng hàm select với trật 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 ko tự trả về thứ theo tiếng việt, nên ta phải phối hợp với hàm select để mang được nội dung là thứ mấy trong tuần
– Copy công thức tại ô E10 sang những ô kế tiếp bên phải, cho tới ô AI10 (ngày thứ 31)
Tuy nhiên ko phải tháng nào cũng mang 31 ngày, nên để tránh việc hiển thị những ngày của tháng khác vào tháng này, ta thực hiện những thao tác sau: + Khởi đầ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 ko bằng thì sẽ trả về giá trị ô AF9 + 1 (Tức thị nếu tháng 2 mang 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, ko hiển thị ngày 01/03. Còn nếu tháng 2 đó mang 29 ngày thì sẽ hiện ngày 29)
+ Tại ô AH9, ta sử 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ẽ mang giá trị rỗng (tức là vào trường hợp tháng 2 mang 28 ngày thì sẽ ko mang 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 mang 29 ngày, thì sẽ ko hiển thị ngày 30.
+ Tại ô AI9, ta sử 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ẽ mang giá trị rỗng (tức là trường hợp ko mang ngày thứ 30 thì cũng ko mang ngày thứ 31)
- IF thứ 2: trường hợp tháng chỉ mang 30 ngày thì sẽ ko mang ngày thứ 31
+ Lúc những ô AG9, AH9, AI9 là rỗng thì hàm select ở những ô AG10, AH10, AI10 sẽ phát sinh lỗi. Nhưng ko thúc đẩy gì tới bảng tính nên ko cần lo lắng về việc này.
Như vậy ta đã thực hiện được phần những ngày trong tháng, và những thứ trong tuần.
Tiếp theo là việc tự động đổi màu cho những 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 tới AI30 – tức là tất cả những nội dung về ngày trong tháng lẫn phần chấm công của những viên chức), tìm chức năng Conditional Formatting (định dạng theo điều kiện), trong mục Condittional Formatting, tìm New Rule.
Trong bảng New Formatting Rule, bạn tìm dòng cuối cùng trong mục Choose a Rule Kind là Use a system to find out which cells to format (Sử dụng công thức để mô tả cách định dạng cells) + Tại mục Format values the place this system 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ìa là đúng, ko thì sẽ sai. Nếu dúng thì sẽ vận dụng định dạng của Conditional formatting. Đặt dấu một mực e$9 là một mực hàng 9, tính từ cột e. + Sau lúc đặt hàm, ta thiết lập định dạng cho CF. Trong bảng định dạng, bạn mang thể tìm 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). Mua định dạng xong bấm okay. 
Kết quả lúc 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, tìm chức năng Conditional Formatting/ New rule/ Use a formulation…, nhập hàm =if(weekday(e$9)=7;true;false) , tìm kiểu định dạng cho ngày thứ 7 
Kết quả lúc thực hiện định dạng thứ 7 và chủ nhật
+ Thiết lập định dạng cho những cột ko chứa giá trị ngày (ví dụ tháng 2 mang 28 ngày, tự động bôi đen những 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, tìm chức năng Conditional formatting / new rule / use a formulation…, nhập hàm =if(e$9=””;true;false), tìm định dạng là tô màu xám cho chữ và nền, ta mang kết quả như sau:
Lúc 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 tháng ngày 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.
Đặt ký hiệu chấm công
+ Bạn tìm 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à mang lương)
- Nghỉ ko lương
…. (tùy đơn vị mà tìm những 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 sức (hoặc mang 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ỉ ko lương: Okay
- Ốm đau, thai sản: O
Hàm, công thức tính công
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 tới AI11. Giá trị tại ô G34 là ký hiệu chấm công của ngày công đủ, vùng E11 tới AI11 là số ngày công trong tháng của người trước tiên (hàng 11), một mực cột E và AI để lúc copy công thức ko bị thúc đẩy tới vùng chấm công
Tương tự với những 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ỉ ko 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 lúc đặt công thức xong, ta copy công thức xuống cho những viên chức khác
Lúc hoàn thiện sẽ mang 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 mang 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 những tháng còn lại.
Ngoài việc chấm công, chúng ta còn cần phải làm những công việc khác liên quan tới tiền lương, bảo hiểm, thuế TNCN… Để nắm được những tri thức và cách ứng dụng Excel trong công việc hành chính-nhân sự, mời bạn tham gia khóa học Hành chính nhân sự từ A-Z của Học Excel On-line. Mách nhỏ là bạn còn được tặng kèm phần mềm quản lý nhân sự lúc tham gia khóa học này nhé. À quên, bạn mang thể tải về file excel theo dõi nghỉ phép lúc tham gia khóa học nữa. Yếu tố xem tại: Học Excel On-line