Trang chủ/Office templates/Excel templates – Bảng điểm danh học viên (đơn giản) admin 30th June 2020 1,013 Less than a minute Bài viết liên quan Giaoan.link chia sẻ cùng bạn mẫu trợ năng “Mẫu báo cáo điểm danh hàng tuần”.Theo dõi điểm danh hàng tuần cho tối đa 30 học viên . Các tùy chọn có mặt, đi muộn, nghỉ không phép và nghỉ có phép đều được liệt kê cho từng ngày trong tuần.
Giới thiệu mẫu
- Sử dụng mẫu này để tạo Báo cáo điểm danh hàng tuần.
- Điền vào Tên hoặc số phòng, Thời gian và Tiết học, Giáo viên, Tên khóa học và Ngày bắt đầu tuần.
- Nhập Tên học viên và C cho có trong bảng Điểm danh để đánh dấu học viên Có mặt, Đi muộn, Nghỉ không phép hoặc Nghỉ có phép cho ngày tương ứng.
Lưu ý:
- Các hướng dẫn bổ sung được cung cấp trong cột A của trang tính BÁO CÁO ĐIỂM DANH HÀNG TUẦN. Phần văn bản này được cố ý ẩn đi. Để loại bỏ văn bản, chọn cột A, rồi chọn XÓA. Để bỏ ẩn văn bản, chọn cột A, rồi thay đổi màu phông chữ.
- Để tìm hiểu thêm về bảng, hãy nhấn SHIFT rồi nhấn F10 trong bảng, chọn tùy chọn BẢNG, rồi chọn VĂN BẢN THAY THẾ.
Học excel thực hành | Học word | Học powerpoint
Xin chào các bạn, 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ên
- Bả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é. 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 nhau
- Dễ dàng gọi dữ liệu trong bảng bằng “tên” của bảng
- Dễ 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ảng
- Bước 2: Chọn tab “INSERT” –> “Table” hoặc nhấn phím [Ctrl] + [T]
- Bước 3: Nếu vùng bạn chọn có cả tiêu đề (như ví dụ của mình) thì chọn vào ô vuông “My table has headers”.
- Bước 4: Nhấn vào vùng của table, sẽ hiển thị thêm tab “TABLE TOOLS” => “DESIGN”. Các bạn đặt tên table tại “Table Name”
- Bước 5: Các bạn có thể lựa chọn các mẫu định dạng table tùy thích.
Ở đâ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áng
- Tạ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 ; [return_type]) | | 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. 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ốn
- Cá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á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: 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ỉ. Hẹn gặp lại các bạn ở bài khác.
| 
