Bật Mí Cách Tạo Bảng Chấm Công Trong Excel Chuyên Nghiệp - Box-edu

Bất kỳ một doanh nghiệp, công ty hay cơ sở kinh doanh (nhiều nhân viên) bất kỳ nào đều cần tới bảng chấm công để thực hiện tính công cho nhân viên của mình. Vậy cách tạo bảng chấm công trong Excel để giúp bạn dễ dàng quản trị nhân lực và lưu trữ thông tin đi làm cho từng nhân viên chính xác. Hãy cùng Box.edu khám phá ngay trong bài viết dưới đây để tự lập cho mình một bảng chấm công chuyên nghiệp nhé!

Xem thêm: Hướng dẫn 4 cách tính đơn giá trong Excel siêu đơn giản

Xem thêm: 3 Cách tính điểm trung bình trong Excel siêu đơn giản, chính xác

Mục lục bài viết

Lợi ích của việc tạo bảng chấm trong Excel đối với doanh nghiệp 

Ngày nay Excel đã trở thành một công cụ hỗ trợ đắc lực cho người dùng từ học tập, kinh doanh, quản lý doanh nghiệp. Thành thạo Excel đã trở thành một yêu cầu bắt buộc mà bất kỳ ai đi xin việc cần có.

Đặc biệt là trong các doanh nghiệp dù là mô hình nhỏ, vừa hay lớn đều sử dụng đến Excel để quản lý nhân sự, số liệu công ty, doanh số. Đặc biệt là sử dụng Excel để lập bảng chấm công.

Cách tạo bảng chấm công trong Excel mang đến ưu điểm rất lớn vì nó đơn giản, dễ dàng sử dụng và không cần tốn nhiều thời gian để đào tạo nhân sự và sử dụng Excel hoàn toàn miễn phí.

Nhân viên dễ dàng thống kê được chi tiết số công cá nhân trong quá trình đi làm. Đặc biệt nếu có sự sai sót thì cũng dễ dàng tìm ra để chỉnh sửa. Bộ phận quản lý cũng dễ dàng thống kê, kiểm soát và đánh giá từng nhân viên một cách dễ dàng

Đối với các doanh nghiệp có số lượng nhân viên lớn sẽ phải tốn nhiều thời gian để thống kế, rà soát, đối chiếu bảng chấm công của nhân sự. Đặc biệt là đối với các doanh nghiệp không thể tự động hoá dữ liệu chấm công của doanh nghiệp mình. Điều này sẽ khiến cho nhiều rủi rõ tiềm ẩn xảy ra và dễ bị thất lạc, sai sót thông tin.

Chắc chắn đây sẽ là một trong những khó khăn lớn đối với đội ngũ HR của doanh nghiệp đó. Khi mà thực hiện tổng hợp, tính toán ngày công cho nhân viên. Bạn cũng sẽ phải đối mặt với những ý kiến tiêu cực từ nhân viên và những góp ý không hài lòng về bảng chấm câm bị sai sót, thiếu sự chuyên nghiệp

Để hạn chế điều này bạn cần phải trang bị cho đội ngũ HR và doanh nghiệp của mình một bảng chấm công chuyên nghiệp, đầy đủ và chính xác. Để làm được điều đó bạn cần tham khảo ngay cách tạo bảng chấm công trong Excel sẽ được chia sẻ ngay trong phần tiếp theo. Cùng theo dõi nhé!

Xác định bố cục tổng thể cho bảng chấm công trên Excel 

Đối với bất kỳ ai bắt tay vào sử dụng Excel để thống kê số liệu bảng chấm công cần định hướng được cho mình bố cục nội dung mong muốn. Điều này để thống kê dữ liệu và giúp người đọc dễ dàng hiểu chi tiết về bảng chấm công. Bạn cần phải xác định được bảng chấm công cần những gì:

• Gồm những gì?
• Cần sử dụng bao nhiêu Sheet?
• Nội dung cụ thể ra sao?
• Cách hoạt động ra sao?
• Áp dụng các tính năng gì và sử dụng các hàm nào?
• …

Khi bạn tìm được câu trả lời cho các tiêu chí trên thì đã có thể hình dung được đến 80% bảng chấm công của bạn sẽ như thế nào rồi. Lúc này bạn cần thao tác tạo bố cục trên phần mềm Excel và điều chỉnh những phần còn thiếu với từng bước được chia sẻ trong phần cách tạo bảng chấm công trong Excel dưới đây.

Hướng dẫn cách tạo bảng chấm công trong Excel nhanh nhất 

Ví dụ minh hoạ

Để giúp bạn hiểu rõ hơn về cách tạo bảng chấm công trong Excel chúng tôi có ví dụ minh hoạ cụ thể sau: Lập một bảng chấm công theo các tiêu chí sau:

• File Excel bao gồm 13 Sheet bao gồm 1 Sheet chính là danh sách nhân viên và 12 Sheet còn lại là bảng chấm công của 12 tháng trong năm
• Mỗi tháng sẽ có trung bình khoảng 20 nhân viên được chấm công
• Ký hiệu chấm công có thể tự thay đổi được tuỳ vào người sử dụng (Nghĩa là mỗi người sẽ có thể chấm 1 kiểu ký hiệu và đồng nhất trong suốt 12 tháng)
• Bảng chấm công ghi rõ ngày tháng ứng với các thứ trong tuần rõ ràng. Với những ngày nghỉ (Thứ 7, chủ nhật) được đánh màu khác để dễ phân biệt
• Nhân viên đi làm hàng ngày sẽ được chấm công vào trong file dữ liệu và tổng kết vào cuối tháng
• Bảng chấm công sẽ lấy tên nhân viên từ Danh sách nhân viên có sẵn trước đó
• Liên kết các tháng với nhau để dễ dàng thao tác

Lập khung cho bảng chấm công trên Excel

Bố cục các Sheet

Theo yêu cầu trên về bảng chấm công cần tạo File Excel bao gồm 13 Sheet bao gồm 1 Sheet chính là danh sách nhân viên và 12 Sheet còn lại là bảng chấm công của 12 tháng trong năm.

Tuy nhiên khi mới bắt đầu xây dựng Sheet cho File Excel bạn chỉ cần tạo 2 Sheet

• Sheet chính là danh sách nhân viên (DSNV)
• Sheet bảng chấm cân của 1 tháng (Tháng 1). Bạn chỉ cần làm hoàn chỉnh 1 sheet sau đó thực hiện thao tác nhân bản thêm 11 sheet tương tự, chỉ cần thay đổi tên thành từng tháng là được

Khởi tạo Sheet danh sách nhân viên 

Đối với Sheet danh sách nhân viên thì bạn bắt buộc cần tạo các dữ liệu sau: Tên, Mã nhân viên. Lý do bạn cần tạo thêm dữ liệu mã nhân viên vì có thể trong danh sách dữ liệu của bạn xảy ra trường hợp tên nhân viên trùng nhau. Việc trùng lặp này sẽ gây ảnh hưởng đến việc quản lý dữ liệu và tính toán ngày công, lương lậu của nhân viên bị sai.

Ngoài ra bạn có thể tạo thêm các cột dữ liệu như ngày tháng năm sinh, quê quán, nơi ở, số CMT, ngày bắt đầu làm việc, chức vụ,… tương ứng với từng nhân viên

Bảng dữ liệu sau khi được xây dựng xong:

Nếu trong cơ sở dữ liệu sheet danh sách nhân viên của bạn tạo thêm cột ngày tháng năm sinh thì cần phải được định dạng. Việc định dạng này sẽ giúp cho số liệu về ngày tháng năm sinh được hiển thị đúng theo dạng dd-mm-yyyy.

Thực hiện thao các bước sau:

Bôi đen toàn bộ cột Ngày tháng năm sinh => Format Cells => Number => Custome => Chọn dd/mm/yyyy

Ở Sheet Danh sách nhân viên này hầu hết bạn chỉ cần thao tác thủ công để nhập dữ liệu. Nên chưa cần sử dụng nhiều đến các công thức, tính năng khác và không có gì phức tạp

Lưu ý: Bạn nên để lại 2-3 dòng trống ở trên cùng của bảng để các bước sau thực hiện thao tác liên kết với các Sheet khác. Nếu bạn quên thì có thể chèn thêm hàng bằng công cụ Insert

Khởi tạo Sheet tháng 1

Đối với Sheet tháng 1 bạn cần tạo khung cho bảng chấm công bao gồm các cột nội dung sau:

• Tiêu đề
• Bảng chấm công tháng
• Tháng
• Bộ phận chấm công
• Định mức ngày công trong tháng
• Mã nhân viên
• Tên nhân viên
• Ngày trong tháng (Tối đa 31 cột tương đương với 31 ngày – Số ngày lớn nhất 1 tháng)
• 4 – 5 cột để sử dụng tính quy ra công
• Cột ghi chú

Tiếp đến bạn cần điều chỉnh động to nhỏ của từng cột để sao cho chứa vừa dữ liệu hiển thị (Không bị ẩn hiển thị nội dung). Đối với các cột ngày chấm công và cột quy ra công trong tháng có thể điều chỉnh nhỏ lại. Bạn chú ý điều chỉnh cột Tên và Mã nhân viên để hiển thị đủ là được

Bạn cũng có thể điều chỉnh theo kích thước sau: Các cột ngày trong tháng, cột quy ra công có thể điều chỉnh về 30 pixels hoặc 3,13

Tạo ngày tháng trong bảng chấm công trong file Excel

Bước tiếp theo trong cách tạo bảng chấm công trong Excel là cần tạo ngày tháng trong bảng chấm công bạn cần xác định được năm thực hiện chấm công. Ví dụ năm nay là năm 2014

Nhập dữ liệu vào ô B4 (Xác đinh chính xác tháng chấm công) sau đó ta nhập hàm xác định ngày theo công thức:

=date($D$1;1;1)

Lưu ý: Điều chỉnh dấu ngăn cách giữa các thành phần trong hàm có thể sử dụng dấu chấm phẩy, dấu phẩy tuỳ mỗi máy tính và yêu cầu cá nhân của bạn. Đối với ví dụ minh hoạ này chúng tôi sử dụng dấu chấm phẩy.

Hàm Date sẽ giúp xác định giá trị của ngày tháng theo giá trị xác định trong hàm. Tương ứng với các giá trị theo thứ tự là Năm, Tháng, Ngày. Đối với hàm trên là Năm = Giá trị tại ô D1, Tháng =1 và ngày =1

Hàm sau khi nhập xong tại ô B4 cần thao tác thực hiện thêm các bước sau:

Chọn Format Cells => Custom => Nhập giá trị [“tháng “mm” năm “yyyy]  vào trong ô Type bên phải sau đó nhấn OK (Bao gồm 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)

Tuỳ chỉnh Marge Cell tại ô B4 đến D4 sau đó cho hiển thị nội dung để dễ nhìn

Đối với các ô ngày 1 thì bạn nhập =B4 tại ô E9 để xác định ngày đầu tiên trong tháng đó

Trong ô F9 bạn cần nhập E9+1 (Nghĩa là số ngày tiếp theo trong tháng)

Tiếp tục copy công thức sang các ô bên cạnh để thực hiện tính số ngày theo 2 cách sau:

• Cách 1: Bôi đen toàn bộ ô F9 đến AI9 => Sử dụng tổ hợp phím CTRL + R
• Cách 2: Thao tác bấm chuột vào ô F9 sau đó nhấn giữ chuột tại vị trí dấu chấm đen hiển thị trong ô. Giúp con trỏ chuột thay đổi thành dấu + sau đó thao tác kéo thả tới cuối ô AI9

Tiếp tục bôi đen từ ô E9 đến AI9 rồi lựa chọn Format Cells => Custim => Type rồi thao tác gõ chữ dd và bấm OK để hiển thị số ngày

Nội dung trong ô E9 => AI9 được hiện lên trong bảng

Tiếp tục nhập dữ liệu tại ô E10 với công thức sau:

=CHOOSE(WEEKDAY(E9);”Chủ nhật”;”T. hai”;”T. ba”;”T. tư”;”T. năm”;”T. sáu”;”T. bảy”)

Trong đó:

• Weekday(e9) : Giá trị của ô E9 được lấy. Nếu bạn không điều chỉnh nội dung trong hàm weekday theo quy định về thứ tự ngày trong tuần thì kết quả sẽ tự trả về bắt đầu là chủ nhật, kết thúc là thứ 7 (Chủ nhật, thứ hai, thứ 3… thứ 7 ), và trả về giá trị số từ 1 đến 8
• Choose: Lựa chọn hàm chọn giá trị trả về. Đối với giá trị đầu tiên trong hàm là giá trị được chọn để làm căn cứ xác định. Còn 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 Choose trên vừa nhập thì dữ liệu ngày 1/1/2014 được áp dụng vào hàm weekday sẽ được trả về giá trị là 4. Bởi vậy khi bạn sử dụng hàm Choose thì thứ tự sẽ được tương ứng với ngày chủ nhật, thứ hai, thứ ba,… thứ bảy. Ở vị trị giá trị = 4 là ngày thứ 4 trong tuần. Bởi vì hàm Weekday sẽ không tự trả về kết quả theo tiếng Việt nên bạn cần phải kết hợp hàm Choose để tìm được nội dung chính xác trong tuần

Tiếp tục Copy công thức ở ô E10 sang các ô bên cạnh đến AI10 là ngày tháng 31 trong tháng

Với cách tạo bảng chấm công trong Excel này bạn cần chú ý 12 tháng trong năm không phải tháng nào cũng 31 ngày. Có tháng 30 ngày và có tháng 28,29 ngày (Tháng 2 dương lịch). Vì thế để tránh việc hiển thị các ngày của tháng khác vào tháng này bạn cần thực hiện thêm thao tác sau:

• Bắt đầu từ ô AG9 – ngày thứ 29 điều chỉnh hàm với công thức như sau:

=IF(DAY(AF9+1)=DAY(E9);””;AF9+1)

Hàm này được sử dụng để tính giá trị ngày của ô AF9 + 1. Nghĩa là trong tháng 2 sẽ có 28 ngày thì ngày 29 sẽ là ngày 1/3 của tháng sau. Giá trị ngày là 1 sẽ bằng với giá trị ngày của ô E9. Nếu đúng thì kết quả sẽ trả về ô rỗng và không hiển thị ngày 1/3. Còn nếu tháng 2 có 29 ngày thì ô trống đó sẽ là ngày 29/2

• Trong ô AH9 bạn sử dụng hàm sau:

=IF(AG9=””;””;if(DAY(AG9+1)=DAY(E9);””;AG9+1))

Trong đó:

+ IF thứ 1: Đối với dữ liệu trống ở ô AG9, thì ô AH9 cũng sẽ có giá trị rỗng (Nghĩa là là đối với trường hợp tháng 2 có 28 ngày thì sẽ không có ngày thứ 30)

+ IF thứ 2: Giống với hàm if tại ô AG9, là trường hợp tháng 2 có 29 ngày và sẽ không hiển thị ngày 30

• Đối với các ô giá trị AG9, AH9 và AI9 mà rỗng thì nghĩa là hàm Choose ở các ô AG10, AH10 và AI10 sẽ bị phát sinh hiển thị lỗi. Tuy nhiên sẽ không ảnh hưởng gì đến bảng tính nên bạn không cần quá lo lắng về trường hợp này

Như vậy là bạn đã hoàn thiện các bước thực hiện nhập dữ liệu các phần ngày trong tháng và các thứ trong tuần vào sheet dữ liệu tháng 1

Bạn cần tiếp tục đổi màu cho các ngày thứ 7, chủ nhật sao cho dễ dàng nhận diện và tách biệt với ngày làm việc từ thứ 2 đến thứ 6 trong tuần theo các bước thao tác sau đây:

Thực hiện bôi đen nội dung trong bảng từ E9 đến AI30 để điều chỉnh tất cả các nội dung về ngày trong tháng và cả các phần chấm công của nhân viên trong công ty. Bạn điều chỉnh tuỳ chọn chức năng Conditional Formatting (định dạng theo điều kiện), trong mục Condittional Formatting => New Rule.

Trong hộp thoại New Formatting Rule bạn lựa 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ịch ra nghĩa là sử dụng công thức để thực hiện mô tả các định dạng Cells)

Đối với mục Format Values where this formula is true bạn sử dụng hàm sau:

=if(weekday(e$9)=1;true;false)

Hàm này có nghĩa là nếu ngày trong tuần tại ô E9 là 1 nghĩa là ngày chủ nhật thì hàm True, còn nếu không thì là False. Đối với trường hợp True trong hàm sẽ áp dụng định dạng Conditional formatting. Sau đó tiếp tục cố định E$9 là cố định hàng 9 cột E trong bảng dữ liệu

Sau khi thao tác thực hiện đặt hàm bạn sẽ thiết lập được định dạng cho CF. Đối với bảng định dạng trong Excel hiện lên. Bạn có thể điều chỉnh lại Font chữ, màu chữ và màu nền hoặc đường kẻ viền. Lần lượt tương đương với các ô chức năng Font, Fill, Border trong bảng hộp thoại. Các chức năng này giống hệ với định dạng ô của Format Cells. Cơ bản bạn hay sử dụng. Lựa chọn định dạng => Nhấn OK để hoàn thành

Tiếp tục làm tương tự với ngày thứ 7. Bạn tiếp tục bôi đen vùng trong bảng và tuỳ chọn chức năng cho ô dữ liệu theo các bước sau:

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 phù hợp cho ngày thứ 7

Tiếp tục thiết lập định dạng cho các cột không chứa giá trị ngày. Ví dụ đối với tháng 2 có 28 ngày thì bạn cần tự động bôi đen các ngày trống. Để dễ dàng phân biệt với các tháng và dữ liệu khác. Thao tác như sau:

Bước 1: Thực hiện sửa giá trị tháng 2 để làm thử tại ô B4

Bước 2: Lựa chọn bôi đen vùng dữ liệu trong bảng sau đó chọn 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 của ô giá trị. Kết quả hiển thị như sau:

Sau khi làm xong ta cần phải trả lại các giá trị ở ô B4 thao tác trong bước 1 vừa rồi.

Đặt các ký hiệu chấm công trong file Excel

Với cách tạo bảng chấm công trong Excel. Yêu cầu thao tác điều chỉnh vài ký hiệu trong bảng chấm công. Để thực hiện một số loại công như sau:

• Ngày công đi làm thực tế
• Nửa ngày công đi làm
• Ngày nghỉ phép (Nghỉ hưởng lương)
• Nghỉ không lương

Và một số loại công khác mà các công ty có thêm

Lưu ý: Đối với mỗi ký hiệu chấm công được bạn điều chỉnh sẽ tương ứng với 1 cột quy ra công. Hoặc bạn có thể gộp lại. Sau đó bạn có thể tạo thêm 1 cột tính tổng theo số công

Trong ví dụ minh hoạ này tôi sẽ tính như sau:

• Ngày công đi làm thực tế: X
• Nửa ngày công: V
• Ngày nghỉ phép: P
• Ngày nghỉ không lương: K
• Ốm đau, thai sản: O

Các hàm chấm công trong file Excel

Như vậy là hoàn thành được 80% cách tạo bảng chấm công trong Excel. Để hoàn thiện bạn cần tạo thêm các hàm chấm công trong File Excel. Một số hàm được sử dụng bao gồm có:

• Hàm Countif: Hàm được sử dụng ở cột Ngày công thực tế như sau:

=COUNTIF($E11:$AI11;$G$34)

Hàm được sử dụng để tính số lần xuất hiện giá trị tại ô G34 trong vùng E11 và AI11. Đối với giá trị tại ô G34 được đánh dấu ký hiệu chấm công của ngày công đủ. Và vùng dữ liệu E11 và AI11 cũng là số ngày công trong tháng của một nhân viên tại hàng thứ 11. Thao tác cố định cột E và AI để thực hiện copy công thức không làm ảnh hưởng tới vùng chấm công

Thao tác thực hiện với các cột khác mà bạn đã đặt trong công thức:

• Nghỉ nửa công: = countif($E11:$AI11;$G$35)
• Nghỉ hưởng phép: = countif($E11:$AI11;$G$36)
• Nghỉ không lương: = countif($E11:$AI11;$G$37)
• Nghỉ ốm đau, thai sản: = countif($E11:$AI11;$G$38)

Đối với tổng số công được tuỳ chỉnh theo yêu cầu của doanh nghiệp. Chủ đơn vị tính công của đơn vị.

Ví dụ minh hoạ: Để tính tổng ngày công bằng số ngày thưc tế đi làm cùng với nửa ngày công x 0,5. Nghỉ phép và nghỉ ốm đau thai sản theo công thức:

= AJ11+AK11*0,5+AL11+AN11

Sau đó bạn thực hiện áp dụng công thức này cho các nhân viên còn lại. Như vậy là bạn đã hoàn thành cách tạo bảng chấm công trong Excel. Kết quả hiển thị như sau

Tổng kết 

Như vậy là bạn đã có thể hoàn toàn tự xây dựng cho mình một bảng chấm công hoàn chỉnh. Với cách tạo bảng chấm công trong Excel chuyên nghiệp này bạn sẽ dễ dàng quản trị nhân lực và tính toán lương cho từng nhân viên một cách chuẩn xác và nhanh chóng. 

Để biết thêm nhiều kiến thức hữu ích về Excel bạn đọc có thể tham khảo thêm các khoá học Excel online trên trên Box các chuyên gia sẽ hướng dẫn chi tiết giúp bạn nắm được kiến thức từ cơ bản đến nâng cao để áp dụng vào trong công việc hiệu quả hơn.

Chúc bạn thành công!

5/5 - (5 bình chọn)