Cách Xếp Lịch Trực Trên Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

Có thể bạn quan tâm: Cách In Ngang Giấy A4 Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

1. Giới thiệu chung về việc xếp lịch trực

Trong môi trường làm việc hiện đại, việc quản lý lịch làm việc, đặc biệt là lịch trực (điểm danh, ca trực, ca bảo trì, ca trực hỗ trợ khách hàng, v.v.) là một yếu tố then chốt để đảm bảo hoạt động suôn sẻ, tránh trùng lặp và tối ưu hoá nguồn nhân lực. Excel, với tính năng đa dạng và khả năng tùy biến cao, đã trở thành công cụ phổ biến và hiệu quả để xây dựng, quản lý và chia sẻ lịch trực cho các bộ phận, nhóm hoặc toàn công ty.

Bài viết này sẽ cung cấp cho bạn một quy trình chi tiết, từng bước một, để xếp lịch trực trên Excel một cách chuyên nghiệp, trực quan và dễ dàng cập nhật. Bạn sẽ học được cách:

• Thiết kế mẫu lịch trực phù hợp với nhu cầu thực tế.
• Sử dụng công thức, định dạng có điều kiện để tự động hoá việc đánh dấu ca trực.
• Áp dụng hàm và công cụ dữ liệu để tránh trùng lặp, cân bằng ca làm việc.
• Chia sẻ và bảo mật lịch trực trong môi trường làm việc nhóm.
• Tự động cập nhật khi có thay đổi nhân sự hoặc ca làm việc.

Sau khi đọc xong, bạn sẽ có một mẫu Excel hoàn chỉnh, sẵn sàng triển khai ngay cho đội ngũ của mình.

Có thể bạn quan tâm: Cách Vẽ Đồ Thị Bằng Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Chuẩn bị trước khi tạo lịch trực

2.1. Xác định yêu cầu và quy định

Trước khi mở Excel, hãy liệt kê rõ ràng các yếu tố sau:

2.2. Thu thập danh sách nhân sự

• Tên đầy đủ (có thể kèm mã nhân viên để tránh trùng tên).
• Chức vụ / bộ phận (nếu cần phân chia ca theo chuyên môn).
• Giới hạn cá nhân (số ca tối đa/ngày, ngày nghỉ cố định).

Ví dụ:

2.3. Quyết định cấu trúc bảng Excel

Một mẫu lịch trực thường bao gồm:

• Cột A: Ngày (hoặc tuần).
• Cột B: Ngày trong tuần (Thứ 2, Thứ 3,…).
• Cột C trở đi: Ca trực (Ca 1, Ca 2, …) hoặc Nhân viên.

Bạn có thể lựa chọn hai kiểu bố cục:

1. Bố cục “Ngày – Ca”: Mỗi hàng là một ngày, các cột là các ca. Thích hợp cho đội nhỏ, ca ít.
2. Bố cục “Ca – Ngày”: Mỗi hàng là một ca, các cột là ngày. Thích hợp cho lịch dài (tháng) với nhiều ca.

Trong hướng dẫn này, chúng ta sẽ sử dụng bố cục “Ngày – Ca” vì phổ biến hơn.

Có thể bạn quan tâm: Cách Khắc Phục File Excel Bị Chậm: Bí Quyết Tối Ưu Hoá Hiệu Suất Làm Việc

3. Tạo mẫu lịch trực cơ bản trên Excel

3.1. Tạo tiêu đề và định dạng chung

1. Mở một workbook mới và đặt tên sheet là “Lịch Trực Tháng 7”.
2. Dòng 1: Nhập tiêu đề tổng quát, ví dụ: LỊCH TRỰC THÁNG 7/2026 - BỘ PHẬN KỸ THUẬT. Định dạng font size 16, in đậm, căn giữa, và merge các cột A tới G (hoặc hơn tùy số ca).
3. Dòng 2: Đặt tiêu đề cột:
4. A2: Ngày
5. B2: Thứ
6. C2: Ca 1 (08:00‑12:00)
7. D2: Ca 2 (13:00‑17:00)
8. E2: Ca 3 (18:00‑22:00) (nếu có)
9. Định dạng ngày: Cột A sẽ là dd/mm/yyyy. Sử dụng Data Validation để người dùng chỉ nhập ngày hợp lệ.

3.2. Nhập danh sách ngày trong tháng

Giả sử bạn muốn lập lịch cho tháng 7/2026 (31 ngày). Thực hiện:

• Ô A3 nhập công thức =DATE(2026,7,1).
• Kéo công thức xuống tới A33 để tạo ngày từ 01/07/2026 tới 31/07/2026.
• Ô B3 nhập công thức =TEXT(A3,"dddd") (điều chỉnh sang tiếng Việt bằng =TEXT(A3;"$-421dddd") nếu cần). Kéo xuống.

Kết quả: Cột B sẽ hiển thị Thứ Năm, Thứ Sáu, …

3.3. Thiết lập danh sách thả xuống cho ca trực

Để tránh nhập sai tên nhân viên, chúng ta sẽ dùng Data Validation:

1. Tạo một sheet phụ “Danh sách NV” và dán cột A danh sách mã và tên nhân viên (ví dụ: NV001 - Nguyễn Văn A).
2. Quay lại sheet “Lịch Trực”, chọn vùng C3:E33 (tất cả các ô ca).
3. Vào Data → Data Validation → List, trong Source nhập: =Danh_sach_NV!$A$2:$A$100 (điều chỉnh phạm vi).

Bây giờ, mỗi ô ca sẽ hiển thị dropdown danh sách nhân viên.

3.4. Định dạng có điều kiện (Conditional Formatting) để phát hiện trùng lặp

Một trong những vấn đề phổ biến là một nhân viên được xếp vào nhiều ca trong cùng một ngày. Để tự động cảnh báo:

1. Chọn vùng C3:E33.
2. Vào Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Nhập công thức:

=COUNTIF($C3:$E3, C3)>1

1. Chọn màu nền đỏ nhạt và OK.

Giải thích: Công thức đếm số lần xuất hiện của giá trị trong cùng một hàng (ngày). Nếu >1, nghĩa là nhân viên được xếp quá 1 ca trong ngày → tô màu.

3.5. Định dạng ngày nghỉ, ngày lễ

Bạn có thể tạo một danh sách ngày nghỉ trong sheet “Ngày nghỉ” và dùng Conditional Formatting để tô màu cho các ngày:

• Sheet “Ngày nghỉ”: A2:A10 chứa ngày lễ (ví dụ: 01/07/2026, 04/07/2026).
• Quay lại sheet “Lịch Trực”, chọn cột A (ngày) và tạo rule:
• Công thức: =COUNTIF(Ngay_nghi!$A$2:$A$10, A3)>0
• Định dạng nền xám và chữ đậm.

3.6. Tính tổng ca trực cho mỗi nhân viên (tổng hợp)

Để biết mỗi người đã được xếp bao nhiêu ca trong tháng, chúng ta sẽ tạo bảng tổng hợp:

1. Tạo sheet “Thống kê”.
2. Cột A: Danh sách nhân viên (copy từ “Danh sách NV”).
3. Cột B: Công thức tính số ca:

=SUMPRODUCT(--(Lịch_Trực!$C$3:$E$33= A2))

Giải thích: SUMPRODUCT đếm số ô trong vùng C3:E33 bằng tên nhân viên ở A2. Sao chép công thức xuống cho toàn bộ danh sách.

Bạn có thể thêm biểu đồ cột để minh hoạ trực quan.

4. Tự động cân bằng ca trực bằng hàm và Solver

4.1. Đặt mục tiêu cân bằng

Giả sử yêu cầu:

• Mỗi nhân viên không ít hơn 8 ca và không quá 12 ca trong tháng.
• Mỗi ca mỗi ngày cần ít nhất 2 người.

4.2. Sử dụng hàm COUNTIF để kiểm tra

Có thể bạn quan tâm: Cách Xóa Cột Ở Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Trong sheet “Thống kê”, thêm cột:

• Cột C: =IF(B2<8, "Thiếu", IF(B2>12, "Quá tải", "Bình thường"))

Áp dụng conditional formatting để tô màu vàng (thiếu) và đỏ (quá tải).

4.3. Áp dụng Solver để tối ưu hoá

Excel có công cụ Solver (phải bật qua File → Options → Add-ins → Solver Add-in). Mục tiêu: giảm số ô vi phạm (các ô “Thiếu” hoặc “Quá tải”).

Bước thực hiện:

1. Thiết lập biến quyết định: Các ô ca trực (C3:E33) là biến quyết định (có thể thay đổi giá trị nhân viên).
2. Ràng buộc:
3. Ràng buộc 1: Mỗi ô ca chỉ chứa một trong danh sách nhân viên (đã được Data Validation bảo đảm).
4. Ràng buộc 2: Số ca cho mỗi nhân viên (tổng trong “Thống kê” B) ≥8 và ≤12.

5. Ràng buộc 3: Mỗi ngày, mỗi ca phải có ít nhất 2 người (nếu yêu cầu nhiều hơn 1 người, thay đổi công thức).

6. Mục tiêu: Minimize tổng số vi phạm (có thể tạo một ô tính tổng các giá trị “Thiếu” + “Quá tải”).

7. Chạy Solver: Chọn Solve. Sau khi Solver tìm được giải pháp, bạn sẽ có lịch trực cân bằng hơn.

Lưu ý: Solver có thể mất thời gian nếu dữ liệu lớn; trong trường hợp phức tạp, bạn có thể chia lịch thành các tuần và tối ưu tuần từng tuần.

5. Tự động cập nhật khi có thay đổi nhân sự

5.1. Sử dụng Table cho danh sách nhân viên

• Chọn vùng danh sách nhân viên trong sheet “Danh sách NV”, rồi Insert → Table. Đặt tên Table là tblNhanVien.
• Khi thêm hoặc xóa nhân viên, Table sẽ tự động mở rộng, và danh sách thả xuống trong lịch trực cũng sẽ cập nhật.

5.2. Công thức VLOOKUP hoặc XLOOKUP để hiển thị thông tin bổ sung

Nếu bạn muốn hiển thị bộ phận bên cạnh tên nhân viên trong lịch trực:

1. Thêm cột phụ trong sheet “Lịch Trực” (ví dụ: cột F – “Bộ phận”).
2. Công thức trong F3:

=IFERROR(XLOOKUP(C3, tblNhanVienHọ và Tên, tblNhanVienBộ Phận), "")

1. Kéo công thức xuống và sang phải (đối với các ca khác). Khi người dùng chọn nhân viên, bộ phận tương ứng sẽ tự động hiện ra.

5.3. Tự động thông báo qua email (nếu dùng Office 365)

Bạn có thể tạo Macro VBA để gửi email thông báo lịch trực cá nhân tới từng nhân viên:

Sub GuiThongBao() Dim ws As Worksheet, lastRow As Long, i As Long, emailBody As String Set ws = ThisWorkbook.Sheets("Lịch Trực") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") 'Thu thập ca cho mỗi nhân viên For i = 3 To lastRow Dim ngay As String: ngay = ws.Cells(i, "A").Value Dim ca1 As String, ca2 As String, ca3 As String ca1 = ws.Cells(i, "C").Value ca2 = ws.Cells(i, "D").Value ca3 = ws.Cells(i, "E").Value If ca1 <> "" Then dict(ca1) = dict(ca1) & "Ngày " & ngay & " - Ca 1;" & vbCrLf If ca2 <> "" Then dict(ca2) = dict(ca2) & "Ngày " & ngay & " - Ca 2;" & vbCrLf If ca3 <> "" Then dict(ca3) = dict(ca3) & "Ngày " & ngay & " - Ca 3;" & vbCrLf Next i Dim outlookApp As Object, mailItem As Object Set outlookApp = CreateObject("Outlook.Application") Dim key As Variant For Each key In dict.Keys Set mailItem = outlookApp.CreateItem(0) mailItem.To = key & "@yourcompany.com" 'Giả sử email = mã nhân viên@ mailItem.Subject = "Lịch Trực Tháng 7/2026" mailItem.Body = "Xin chào " & key & "," & vbCrLf & vbCrLf & _ "Dưới đây là lịch trực của bạn trong tháng 7/2026:" & vbCrLf & _ dict(key) & vbCrLf & "Trân trọng," mailItem.Send Next key
End Sub

Lưu ý: Macro cần được cho phép chạy, và Outlook phải được cài đặt trên máy.

6. Chia sẻ và bảo mật lịch trực

6.1. Lưu dưới dạng Excel Online (OneDrive/SharePoint)

• Đăng tải file lên OneDrive hoặc SharePoint.
• Chia quyền View cho toàn bộ nhân viên, Edit chỉ cho người quản lý lịch.
• Khi có chỉnh sửa, mọi người sẽ nhận được notification và xem phiên bản mới ngay lập tức.

6.2. Bảo vệ sheet

• Vào Review → Protect Sheet.
• Đánh dấu các tùy chọn Select locked cells, Select unlocked cells, Edit objects, … tùy nhu cầu.
• Đặt mật khẩu (nếu cần) để ngăn người dùng không có quyền thay đổi cấu trúc.

6.3. Sử dụng Data Validation để hạn chế nhập sai

• Đối với cột ngày, áp dụng Data Validation → Date → between để chỉ cho phép ngày trong tháng.
• Đối với cột ca, sử dụng List như đã đề cập để tránh nhập tên không tồn tại.

7. Mẫu lịch trực mẫu (đánh dấu mẫu thực tế)

Lưu ý: Dưới đây là mô tả cấu trúc, bạn cần tự tạo trong Excel theo các bước trên.

• Ô màu đỏ: Nhân viên trùng ca trong cùng một ngày (được Conditional Formatting báo).
• Ô màu xám: Ngày nghỉ lễ (được Conditional Formatting báo).

8. Những lưu ý thường gặp và cách khắc phục

9. Tối ưu hoá nâng cao (tùy chọn)

9.1. Sử dụng Power Query để nhập dữ liệu nhân viên từ hệ thống HR

• Vào Data → Get Data → From Database (hoặc From Excel) để lấy danh sách nhân viên tự động cập nhật.
• Đặt Refresh tự động mỗi ngày làm việc.

9.2. Tích hợp Power Automate (Flow) để tự động gửi email

• Tạo Flow: Khi file Excel được modified, lấy dữ liệu từ sheet “Lịch Trực”, tạo email cho từng nhân viên (sử dụng Apply to each). Điều này thay thế VBA và hoạt động trên nền cloud.

9.3. Dashboard tổng quan

• Dùng PivotTable + Slicer để tạo bảng tổng hợp ca trực theo bộ phận, ngày, hoặc nhân viên.
• Thêm biểu đồ Gantt (sử dụng stacked bar) để hiển thị ca trực theo thời gian.

10. Kết luận

Việc xếp lịch trực trên Excel không chỉ giúp bạn tổ chức công việc một cách khoa học mà còn tạo ra sự minh bạch, công bằng cho toàn bộ nhân viên. Nhờ các tính năng mạnh mẽ như Data Validation, Conditional Formatting, hàm tính toán và thậm chí Solver, bạn có thể tự động hoá hầu hết các bước, giảm thiểu lỗi nhập liệu và cân bằng tải công việc một cách tối ưu.

Hãy áp dụng các bước trong bài viết:

1. Xác định yêu cầu và thu thập dữ liệu nhân sự.
2. Thiết kế bảng lịch với tiêu đề, ngày, ca và dropdown danh sách.
3. Áp dụng định dạng có điều kiện để phát hiện lỗi ngay lập tức.
4. Sử dụng công thức và Solver để cân bằng ca và đáp ứng quy định.
5. Tự động cập nhật khi có thay đổi nhân sự và gửi thông báo qua email.
6. Bảo mật và chia sẻ lịch trực một cách an toàn.

Với sự chuẩn bị kỹ lưỡng và thực hành theo hướng dẫn trên, bạn sẽ có một công cụ lịch trực mạnh mẽ, linh hoạt và sẵn sàng đáp ứng mọi nhu cầu của doanh nghiệp. Chúc bạn thành công trong việc quản lý lịch trực và nâng cao hiệu quả làm việc của đội ngũ!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.