Hướng dẫn tạo bảng chấm công trong Excel chi tiết, cụ thể

Hướng dẫn cách tạo bảng chấm công trong excel đơn giản chỉ với vài thao  tác cơ bản

Bảng chấm công sẽ được các công ty, doanh nghiệp ứng dụng để tính công cho nhân viên trong những dịp trả lương. Bảng này sẽ giúp người quản lý dễ dàng kiểm soát được nhân lực và lưu trữ thông tin cho từng nhân viên một cách chính xác nhất. Trong bài viết này, Mega sẽ hướng dẫn bạn cách tạo bảng chấm công trong Excel đơn giản và nhanh chóng chỉ với vài thao tác cơ bản.

Hướng dẫn bạn cách tạo bảng chấm công trong Excel đơn giản và nhanh chóng chỉ với vài thao tác cơ bản

I. Bảng chấm công là gì? Công dụng của bảng chấm công

1. Bảng chấm công

Khi đi làm, chắc chắn bạn sẽ không xa lạ với cụm từ bảng chấm công. Hình thức chấm công nhằm điểm danh đầu giờ với nhân viên, minh chứng được rằng họ đã đến công ty tại thời điểm đó. Sau 1 tháng, người quản lý (kế toán) sẽ tổng kết số công trên bảng chấm công để quản lý số ngày mà từng nhân viên tới công ty là bao nhiêu và bắt đầu chi trả lương. Thường thì bảng chấm công sẽ được thực hiện trên Excel hoặc những phần mềm có trả phí khác.

2. Công dụng của bảng chấm công

Bảng chấm công được tạo ra nhằm cho người làm dễ quan sát, cũng như việc tính toán được chính xác hơn. Không những thế, công dụng của bảng chấm công còn giúp cho công ty, doanh nghiệp có thể nhận xét và đánh giá tần suất đi làm của mỗi cá nhân, để biết được nhân viên nào chuyên cần mà khen thưởng hay tán dương vào dịp cuối năm. 

II. Xác định mô hình bảng chấm công trong Excel

Để lập một bảng chấm công, thì người làm cần xác định quy mô hay mô hình bảng như thế nào, gồm những gì, có bao nhiêu sheet,... để có thể tạo được một bảng Excel chuẩn và hoàn hảo như ý muốn.

Trong bài viết này, Mega sẽ ví dụ cách lập mô hình bảng chấm công như sau: 

• Bao gồm 13 sheet, mỗi sheet là 1 tháng và 1 sheet làm danh sách nhân viên
• Trung bình mỗi tháng là 20 nhân viên
• Ký hiệu chấm công có thể thay đổi được tùy người sử dụng (Bạn phải tạo cho mỗi nhân viên một 1 kiểu ký hiệu, nhưng sẽ đồng nhất trong 12 tháng)
• Trong mỗi bảng chấm công có ghi rõ ngày, thứ trong tháng. Vào thứ 7 và chủ nhật thì tự đổi màu khác so với ngày thường.
• Hàng ngày sẽ chấm công vào các ngày trong tháng. Cuối tháng sẽ tự tính số tổng số công trong tháng.
• Tên nhân viên trong bảng chấm công sẽ lấy từ danh sách nhân viên
• Bên cạnh đó, người dùng còn có thể liên kết giữa các tháng với nhau để dễ thao tác.

III. Hướng dẫn chi tiết cách tạo bảng chấm công trong Excel

1. Bố cục Sheet

Về mô hình đã đặt ra là 13 sheet, nhưng khi bắt đầu làm bạn chỉ cần 2 sheet là đủ. 

• 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)

2. Sheet danh sách nhân viên

Nội dung chủ yếu trong sheet này là Tên và Mã nhân viên. Người dùng cần tạo mã nhân viên để tránh trường hợp trùng tên. 

Ngoài ra còn có các nội dung khác liên quan tới nhân viên để xác định đối tượng/ cá nhân như: ngày sinh, quê quán, số CMT,

ngày vào làm…

Sheet danh sách nhân viên

Về cột E (ngày sinh), bạn bôi đen cả cột, bạn định dạng Format cell/Number/Custome/ chọn dd-mm-yyyy, đây là tính năng tự động đưa ngày tháng về theo chuẩn mà cài đặt trước đó, tức là theo khổ ngày/tháng/năm. 

Sheet danh sách nhân viên

Còn về sheet danh sách nhân viên thì bạn chỉ cần nhập tay là được, sẽ hơi tốn thời gian nhưng không phức tạp gì nhiều. 
Lưu ý: Trong quá trình thực hiện bảng chấm công thì bạn cần chừa ra thêm khoảng 2-3 dòng trên cùng để có thể tạo liên kết tới các sheet khác trong bảng. Bên trái bạn cũng cần cách ra 1 cột để dự phòng nếu khi muốn bổ sung gì thêm.

3. Sheet tháng 1

- Đầu tiên bạn tạo khung cho bảng chấm công, gồm các nội dung sau:

• 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ú

Sheet tháng 1

Tiếp sau đó bạn điều chỉnh độ rộng của các cột sao cho dễ nhìn. Để có thể điều chỉnh được các bạn thực hiện như sau: 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) như hình dưới đây. 

Sheet tháng 1

4. Tạo ngày tháng

- Tạo ngày tháng cho tháng có 31 ngày

Bước 1: Đầu tiên ta xác định năm sử dụng bảng chấm công. Sau khi đã xác định xong, tại ô D1 nhập giá trị của năm. Ví dụ trong bài này là năm 2014

Bước 2: 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 của bạn. Ở máy tính của mình sẽ sử dụng là dấu ;

Bây giờ thì bạn cần xác định 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

Bước 3: 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ước 4: Bạn có thể marge cell từ ô B4 đến D4 để cho hiển thị đủ nội dung cho dễ nhìn.

Bước 5: Tại ô ngày 1 ( ô E9), bạn nhập =b4 để xác định ngày đầu tiên trong tháng.

Bước 6: Tại ô F9, bạn nhập =e9+1 (ngày tiếp theo trong tháng)

Bước 7: 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ước 8: 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.

Bước 9: 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

Bước 10: 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)

- Tạo ngày tháng cho tháng có 30 ngày 

Bước 1: 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)

Bước 2: 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.

Bước 3: 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

Bước 4: 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ước 5: 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.

Bước 8: 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. 

 

 

 

 

Bước 9: 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) 

• Tại ô B4, sửa tạm giá trị tháng là 2 để làm thử
• 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ư hình dưới đây. 

 

 

 

Khi làm xong nhớ trả lại giá trị tháng 1 ở ô B4 nhé.

 

5. Đặt kí hiệu

 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ụ mình sẽ đặ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 kí hiệu

6. Các hàm, công thức tính công trong Excel

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 còn lại, bạn đặ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 bạn đã đặt công thức xong, lúc này chỉ cần copy công thức xuống cho các nhân viên khác là đã có thể hoàn thiện như hình dưới đây. 

Hàm, công thức tính công

IV. Một số lưu ý khi tạo bảng chấm công trong Excel

• Xác định rõ ràng thời gian theo ngày, giờ, nghỉ, làm bù,…
• Thiết kế ra những tiêu chí chính tạo thành những bảng lớn. Trong mỗi bảng sẽ có những mục bảng nhỏ và chi tiết hơn, bạn cần tránh rườm rà và làm ảnh hưởng đến việc tổng hợp công lúc hết tháng.
• Ghi rõ ràng những mục thời gian ngày, giờ khi hoàn thành dữ liệu chấm công thô để tránh bị nhầm lẫn.

V. Tổng kết

Như vậy trong bài viết này Mega đã hướng dẫn bạn cách tạo bảng chấm công trong excel đơn giản chỉ với vài thao tác cơ bản. Hy vọng những hướng dẫn trong bài viết này sẽ hỗ trợ được cho bạn trong quá trình tạo một bảng chấm công hoàn thiện. Chúc bạn thực hiện thành công. 

Xem thêm >>>

Hướng dẫn tạo dấu chấm trong excel đơn giản và nhanh chóng

Hướng dẫn tạo lịch trong excel đơn giản chỉ với vài thao tác

Tổng hợp cách làm tròn số trong Excel đơn giản nhất 2022

 copyright © mega.com.vn