Nay tớ giới thiệu các bạn 1 ví dụ cụ thể trong việc ứng dụng Excel trong thực tế, đó là LẬP BẢNG CHẤM CÔNG
Tùy vào nhu cầu thực tế của các bạn thì các bạn có thể thay đổi mục đích sử dụng thành nhiều loại khác nhau:
Bảng chấm công nhân viênBảng điểm danh học sinh, sinh viên…
Giờ tớ chia sẻ các bạn cách lập 1 bảng chấm công nhân viên hoặc bảng điểm danh học sinh nhé.
Đang xem: Mẫu điểm danh học sinh
I. Lập danh sách
Tớ tạo sheet đầu tiên là “Danh sách” để ghi danh sách toàn bộ nhân viên/học sinh vào đây, dễ dàng quản lý và theo dõi.
Các bạn có thể sử dụng “Mã số” để phân biệt các nhân viên, học sinh với nhau, tránh các trường hợp trùng họ tên.
Ngoài ra, các bạn có thể thêm các cột như “Phòng ban/Tổ”, “Ngày sinh”, … tùy vào thông tin mà các bạn muốn quản lý.
Như ví dụ của tớ, tớ đặt bảng danh sách này thành dạng “Table”.
Lợi ích khi sử dụng Table:
Có định dạng các hàng, các cột đồng nhất với nhauDễ dàng gọi dữ liệu trong bảng bằng “tên” của bảngDễ dàng bổ sung dữ liệu ở các hàng bên dưới, mà không cần phải định dạng lại các hàng mới này
Bước 1: Nhập các tiêu đề bảng và bôi đen vùng để lập bảngBước 2: Chọn tab “INSERT” –> “Table” hoặc nhấn phím
Ở đây, mình sẽ đặt table tên là “DS”.
II. Lập bảng chấm công, điểm danh theo từng tháng
Các bạn tạo thêm sheet thứ 2 để lập bảng chấm công tại sheet này.
1. Tạo các cột “ngày” trong tháng
Tớ đặt 2 giá trị tại ô C3 và C4 là tháng và năm hiện tại. Đây là dạng số bình thường.
Tiếp theo đến phần hiển thị ngày tháng và thứ của ngày đó.
Ở hàng 7, tớ hiện danh sách các ngày trong tháng:
| Ô E7 | Giá trị ngày đầu tiên của tháng | = DATE (C4; C3; 1) |
| Ô F7 | Giá trị ngày kế tiếp | = E7 + 1 |
| Ô G7 -> về sau | Sao chép công thức từ ô F6 |
Ở đây, tớ đang ví dụ tháng 1 thì sẽ có 31 ngày.
Nhưng đối với các tháng khác, tháng 2 có 28 hoặc 29 ngày, tháng 4 có 30 ngày, … thì tớ sẽ bổ sung tại công thức ở ô AG7 (ngày 29), AH7 (ngày 30) và AI7 (ngày 31) để có thể sử dụng đối với các tháng khác:
| =IFERROR ( IF (MONTH(AF7+1)=$C$3 ; AF7+1 ; “” ) ; “”) |
Với công thức trên, ở các tháng khác (như tháng 2 năm 2020) sẽ chỉ hiện 29 ngày.
Vì định dạng của các ô ở hàng 7 này là dạng NGÀY THÁNG (DATE) nên tớ sẽ thay đổi cách hiển thị để chỉ hiện mỗi “ngày”, không hiện “tháng, năm”
Để thay đổi cách hiển thị ở các ô này thành “chỉ hiện ngày tháng”, các bạn làm theo cách sau:
Bôi đen vùng ngày thángTại tab “HOME”, trong nhóm “Number”, nhấn vào mũi tên ở góc dưới bên phải. Sẽ hiển thị khung “Format Cells”.Chọn Category là “Custom”Tại ô phía dưới chữ “Type” nhập “dd” => nếu muốn hiện 2 chữ số như ví dụ trên.
2. Tạo các cột “thứ” trong tuần tương ứng
Ở hàng 6, sẽ hiển thị thứ trong tuần của các ngày tháng ở hàng số 7:
Các bạn sẽ sử dụng hàm WEEKDAY để biết được ngày đó là thuộc thứ mấy trong tuần.
| Công thức: | = WEEKDAY( serial_number ; |
| serial_number | giá trị ngày tháng |
| return_type | Loại trả về. |
| Ý nghĩa: | Dựa vào giá trị ngày tháng của serial_number để biết được đây là thứ mấy trong tuần, theo return_type |
| Tại ô E6: | = WEEKDAY( E7 ; 1)Ở đây, return_type = 1 nghĩa là – Chủ nhật sẽ là 1– Thứ 2 là 2– … |
Để hiện được kiểu “T2, T3, T4, …, T7, CN” hoặc “Thứ hai, Thứ ba, …, Thứ bảy, Chủ nhật” thì tớ sẽ bổ sung thêm công thức tại ô E6.
Có 2 cách:
Đối với trường hợp hiện kiểu “T2, T3, T4, T5, T6, T7, CN”
| = IF ( WEEKDAY( E7 ; 1) = 1 ; “CN” ; “T”&WEEKDAY( E7 ; 1) ) |
hoặc
| = CHOOSE ( WEEKDAY( E7 ; 1) ; “CN” ; “T2” ; “T3” ; “T4” ; “T5” ; “T6” ; “T7”) |
Đối với trường hợp hiện kiểu “Thứ hai, Thứ ba, …, Thứ bảy, Chủ nhật”
| = CHOOSE ( WEEKDAY( E7 ; 1) ; “Chủ nhật” ; “Thứ hai” ; “Thứ ba” ; “Thứ tư” ; “Thứ năm” ; “Thứ sáu” ; “Thứ 7”) |
Và đối với các ô có giá trị trống (khi tháng có ít hơn 31 ngày) thì sẽ gây là lỗi #VALUE! ở đây.
Xem thêm: Những Mẫu Xe Ô Tô 2 Chỗ Ngồi Giá Rẻ Đáng Mua Nhất 2020, Phát Sốt Với Chiếc Ô Tô Điện Chở Được 2
Tớ sẽ bổ sung thêm hàm IFERROR
| = IFERROR ( IF(WEEKDAY(E7;1)=1;”CN”;”T”&WEEKDAY(E7;1)) ; “”) |
Sau đó, các bạn copy công thức này sang các cột F6 -> AI6.
Thành quả đến hiện tại, sẽ là:
Để thay đổi độ rộng các cột hàng loạt và đều nhau, các bạn thực hiện như sau:
Bôi đen toàn bộ các cột (click chuột ở vị trí các chữ cái ở từng cột)Cách 1: Đưa chuột vào 1 vị trí bất kỳ ở giữa 2 cột trong các cột đã bôi đen. Con trỏ chuột sẽ hiện biểu tượng như hình bên dưới: mũi tên 2 chiều và có gạch dọc ở chính giữa)Các bạn nhấn giữ và di chuyển đến kích thước mong muốnCách 2:Bạn click chuột phải tại ký tự chữ cái đại diện của 1 cột, chọn “Column Width…”Nhập giá trị mong muốn. Như hình ảnh thì sẽ nhập: 3,13
3. Định dạng màu sắc cho các cột là ngày Thứ bảy, Chủ nhật
Các bạn chọn vùng cần định dạng (bao gồm các hàng Thứ, Ngày và các hàng cho các nhân viên ở bên dưới)Tại tab “HOME” –> “Conditional Formating” –> “New Rule…”Chọn “Use a formula to determine which cells to format”Nhập công thức để tô màu sắc cho các cột là “Chủ nhật” và nhấn vào nút “Format…” để định dạng cho các cột này. Sau đó nhấn nút OK.
| Công thức: | = E$6 = “CN” |
Các bạn có thể thay “CN” bằng “Chủ nhật”, … tùy vào cột bạn muốn định dạng
Nếu muốn định dạng cả “Thứ 7” và “Chủ nhật”, các bạn có thể sử dụng công thức sau:
| Công thức: | = OR (E$6 = “T7” ; E$6 = “CN” ) |
Định dạng các cột là “T7” và “CN”
Nếu muốn định dạng các cột trống phía sau (sẽ hiện khi các tháng có ít hơn 31 ngày), các bạn sử dụng công thức sau:
| Công thức: | = E$6 = “” |
Tớ sẽ có kết quả như sau:
4. Định dạng các cột là ngày nghỉ lễ
Tớ có 1 sheet “NgayNghi” để liệt kê danh sách các ngày nghỉ lễ, ngày nghỉ bù trong năm.
Để định dạng các cột có ngày trùng với các ngày nghỉ lễ trong danh sách trên, thì các bạn tạo Rule theo công thức sau:
| = ISERROR(MATCH(E$7;NgayNghi!$C$5:$C$16;0)) = FALSE |
Giải thích:
| MATCH(E$7;NgayNghi!$C$5:$C$16;0) | Trả về vị trí của ngày ở ô E7 trong danh sách NgayNghi.Nếu không có trong danh sách ngày nghỉ thì hàm này sẽ báo lỗi. |
| ISERROR(MATCH(E$7;NgayNghi!$C$5:$C$16;0)) | Kiểm tra hàm này có lỗi hay không. |
| = ISERROR(MATCH(E$7;NgayNghi!$C$5:$C$16;0)) = FALSE | Nếu có trong danh sách ngày nghỉ thì hàm này sẽ trả về TRUE. |
Như vậy, theo dữ liệu tớ nhập vào thì tháng 4 năm 2020 sẽ có 2 ngày nghỉ lễ, và tớ đã tô màu đỏ, như hình sau:
5. Đặt ký hiệu đánh dấu và tính tổng theo từng ngày
Tớ bổ sung thêm 1 sheet “QuyTac” để liệt kê danh sách các ký hiệu sử dụng để điểm danh theo từng ngày.
Mục đích sheet này để đặt làm quy tắc chung. Các bạn có thể ghi chú ngay bên dưới bảng chấm công từng tháng để dễ theo dõi.
Ở sheet “T1”, các bạn có thể đặt Conditional Formatting cho các ký hiệu này.
Ở các hàng bên dưới danh sách nhân viên/học sinh, tớ bổ sung các hàng đếm tổng số lượng theo từng loại.
Tớ sử dụng hàm COUNTIF để đếm số lượng theo 1 điều kiện.
| Công thức: | = COUNTIF ( range ; criteria ) |
| range | Vùng giá trị để đếm số lượng |
| criteria | Điều kiện để đếm số lượng |
| Ý nghĩa: | Dếm số lượng các giá trị trong vùng range thỏa điều kiện criteria |
| Tại ô E18: | = COUNTIF ( E$8:E$17 ; $B18 )hoặc= COUNTIF ( E$8:E$17 ; “A” )– Ở đây, đếm số lượng các giá trị bằng ô B18 hoặc bằng “A” ở trong vùng E8:E17– Đối với vùng đếm số lượng: tớ cố định hàng “E$8:E$17” để khi sao chép công thức sang các điều kiện bên dưới thì không bị thay đổi vị trí của vùng giá trị này.– Đối với điều kiện: Tớ cố định cột ở điều kiện “$B18” để khi sao chép công thức sang các ô bên cạnh thì không bị thay đổi vị trí của ô điều kiện. |
Các bạn có thể sử dụng công thức tương tự để đếm số lượng ngày làm việc/đi học trong 1 tháng của từng cá nhân.
Và đây là thành quả của tớ:
Để sao chép sheet hiện tại và sử dụng cho các tháng khác:
Bạn nhấn chuột phải vào sheet này, chọn “Move or Copy…”Tại danh sách các sheet “Before sheet”, chọn sheet mà sheet mới tạo sẽ nằm phía sau đó.Chọn vào ô vuông “Create a copy”Và nhấn nút “OK”.
Như vậy các bạn đã biết được cách tạo một bảng chấm công/điểm danh hoàn chỉnh rồi nhỉ.