Chia Lịch Trực Bằng Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Chia Trang Excel Thành 2 Phần: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Mở Đầu

Trong môi trường làm việc hiện đại, việc quản lý lịch trực cho nhân viên, đội ngũ bảo vệ, y tá, nhân viên bán hàng hay bất kỳ nhóm nào có nhu cầu làm việc ca trực là một nhiệm vụ quan trọng nhưng không hề đơn giản. Lịch trực không chỉ giúp đảm bảo hoạt động liên tục mà còn giúp cân bằng khối lượng công việc, tránh tình trạng quá tải hay thiếu hụt 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 nhất để chia lịch trực. Bài viết này sẽ hướng dẫn bạn cách sử dụng Excel để thiết kế, quản lý và tự động hoá lịch trực một cách hiệu quả, từ những bước căn bản cho người mới bắt đầu cho tới các kỹ thuật nâng cao dành cho người đã có kinh nghiệm.

Có thể bạn quan tâm: Chia Cột Chéo Trong Excel: Kỹ Thuật, Công Thức Và Ứng Dụng Thực Tiễn

1. Tại Sao Nên Sử Dụng Excel Để Chia Lịch Trực?

1.1. Đa Dạng Tính Năng

• Công thức tính toán: Tự động tính tổng giờ làm, ngày nghỉ, hay các khoản phụ cấp.
• Định dạng có điều kiện: Tô màu tự động dựa trên các tiêu chí (ví dụ: ca sáng, ca chiều, ca đêm).
• Bảng Pivot: Tổng hợp nhanh các số liệu thống kê.
• Macro/VBA: Tự động hoá các thao tác lặp lại, tạo báo cáo nhanh.

1.2. Dễ Dàng Chia Sẻ

Excel có thể lưu dưới dạng .xlsx hoặc .csv, dễ dàng gửi qua email, lưu trữ trên OneDrive, Google Drive hay SharePoint để mọi người cùng truy cập, chỉnh sửa.

1.3. Tính Linh Hoạt

Bạn có thể tùy chỉnh bảng lịch phù hợp với quy tắc làm việc của công ty: số ca, thời gian nghỉ, ngày lễ, v.v.

Có thể bạn quan tâm: Chi Phí Đám Cưới Excel: Cách Lập Bảng Ngân Sách Hoàn Chỉnh Và Quản Lý Chi Tiêu

2. Chuẩn Bị Dữ Liệu Cơ Bản

2.1. Danh Sách Nhân Viên

Tạo một sheet riêng (đặt tên “Nhân viên”) để lưu trữ thông tin:
| Mã NV | Họ & Tên | Phòng Ban | Chức Vụ | Số Ca/tuần | Ngày Nghỉ Được Phép |
|——-|———-|———–|———|————|———————-|
| NV001 | Nguyễn Văn A | Bảo vệ | Nhân viên | 5 | 2 |
| NV002 | Trần Thị B | Y tá | Nhân viên | 4 | 1 |
| … | … | … | … | … | … |

• Mã NV: duy nhất, giúp tra cứu nhanh.
• Số Ca/tuần: quy định số ca tối đa mỗi tuần cho mỗi nhân viên.
• Ngày Nghỉ Được Phép: số ngày nghỉ được phép trong tháng.

2.2. Định Nghĩa Các Ca Trực

Trong sheet “Ca”, liệt kê các ca làm việc:
| Mã Ca | Tên Ca | Thời Gian Bắt Đầu | Thời Gian Kết Thúc | Loại Ca |
|——-|——–|——————-|——————–|———|
| C1 | Sáng | 07:00 | 15:00 | Ngày |
| C2 | Chiều | 15:00 | 23:00 | Ngày |
| C3 | Đêm | 23:00 | 07:00 | Đêm |

Bạn có thể thêm các ca đặc biệt như “Ca nghỉ lễ”, “Ca làm thêm”, “Ca hỗ trợ”.

3. Xây Dựng Bảng Lịch Trực Cơ Bản

3.1. Thiết Kế Giao Diện

1. Tạo Sheet “Lịch Trực”.
2. Cột A: Ghi ngày tháng (ví dụ: 01/07/2026, 02/07/2026,…).
3. Cột B: Ghi ngày trong tuần (Thứ Hai, Thứ Ba,…). Dùng công thức =TEXT(A2,"dddd") để tự động.
4. Các cột tiếp theo: Mỗi cột đại diện một ca (Sáng, Chiều, Đêm). Đặt tiêu đề ở hàng 1.

Ví dụ:

3.2. Nhập Thủ Công Hoặc Sử Dụng Công Thức

3.2.1. Nhập Thủ Công

• Gán mã nhân viên vào ô tương ứng với ca. Ví dụ: “NV001” vào ô C2 (ngày 01/07 ca sáng).

3-2.2. Sử Dụng Công Thức VLOOKUP/KÍCH HOẠT ĐIỀU KIỆN

Nếu muốn tự động điền tên nhân viên dựa trên danh sách ca, bạn có thể tạo một bảng “Phân ca” tạm thời:

Sau đó, trong sheet “Lịch Trực”, ô C2 (Sáng ngày 01/07) dùng công thức:

=IFERROR(VLOOKUP($A2&"_"&"C1", PhânCa!$A$2:$C$500, 3, FALSE), "")

• $A2&"_"&"C1" tạo khóa hợp nhất “01/07/2024_C1”.
• PhânCa!$A$2:$C$500 là vùng dữ liệu chứa các bản ghi.
• Kết quả trả về là Mã NV; bạn có thể dùng VLOOKUP lại để lấy Tên từ sheet “Nhân viên”.

3.3. Định Dạng Có Điều Kiện (Conditional Formatting)

Để lịch trực dễ nhìn:
1. Chọn toàn bộ khu vực ca (cột C đến E, các hàng ngày).
2. Vào Home → Conditional Formatting → New Rule → Use a formula.
3. Công thức tô màu ca sáng (Cột C): =C$1="Sáng" và chọn màu xanh nhạt.
4. Tương tự cho ca chiều (màu vàng) và ca đêm (màu tím).

Bạn cũng có thể tô màu nhân viên nghỉ lễ:
– Công thức: =COUNTIF(Nghỉ!$A$2:$A$100, C2)>0 → màu đỏ.

4. Tự Động Phân Ca Dựa Trên Quy Tắc

4.1. Quy Tắc Cơ Bản

4.2. Sử Dụng Solver (Giải thuật tối ưu)

Có thể bạn quan tâm: Công Thức Lấy Phần Dư Trong Excel: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao

Solver là công cụ tích hợp trong Excel (cần kích hoạt Add‑in) giúp giải bài toán tối ưu hoá.

Bước 1: Cài Đặt Solver

• File → Options → Add‑ins → Manage: Excel Add‑ins → Go → Check “Solver Add‑in” → OK.

Bước 2: Xây Dựng Ma trận Quyết Định

Giả sử có 5 nhân viên (NV001‑NV005) và 30 ngày, 3 ca mỗi ngày → 90 ô ca.
Tạo một ma trận 5×90, mỗi ô là 0/1 (1 = nhân viên này làm ca đó).

Bước 3: Đặt Hàm Mục Tiêu

Ví dụ: Tối đa hoá tổng số ca được lấp đầy (đảm bảo không có ca trống). Hàm mục tiêu:

=SUM(Ma trận)

Bước 4: Đặt Ràng Buộc

• Mỗi ca chỉ một nhân viên: SUM(Trên cột ca) = 1.
• Số ca mỗi nhân viên ≤ Số Ca/tuần × 4 (tuần trong tháng).
• Không cho ca đêm liên tiếp > 2: sử dụng công thức logic để kiểm tra chuỗi 1.

Bước 5: Chạy Solver

• Data → Solver.
• Đặt Set Objective là hàm mục tiêu (tối đa hoá).
• By Changing Variable Cells là ma trận 0/1.
• Add Constraints cho các ràng buộc trên.
• Solve và chờ kết quả.

Sau khi Solver tính toán, bạn sẽ nhận được một bảng phân ca tối ưu, sau đó copy‑paste vào sheet “Lịch Trực”.

4.3. Sử Dụng Macro Đơn Giản

Nếu không muốn dùng Solver, bạn có thể viết macro VBA để luân phiên ca:

Sub PhanCaTuDong() Dim wsNV As Worksheet, wsLich As Worksheet Set wsNV = Worksheets("Nhân viên") Set wsLich = Worksheets("Lịch Trực") Dim lastRowNV As Long, lastRowLich As Long lastRowNV = wsNV.Cells(wsNV.Rows.Count, "A").End(xlUp).Row lastRowLich = wsLich.Cells(wsLich.Rows.Count, "A").End(xlUp).Row Dim i As Long, j As Long, caIdx As Long Dim caList As Variant caList = Array("Sáng", "Chiều", "Đêm") Randomize For i = 2 To lastRowLich For caIdx = LBound(caList) To UBound(caList) Dim nvIdx As Long nvIdx = Application.WorksheetFunction.RandBetween(2, lastRowNV) wsLich.Cells(i, caIdx + 3).Value = wsNV.Cells(nvIdx, 1).Value 'Mã NV Next caIdx Next i
End Sub

Macro này sẽ ngẫu nhiên gán nhân viên cho mỗi ca, sau đó bạn có thể kiểm tra thủ công các ràng buộc và điều chỉnh.

5. Kiểm Tra và Điều Chỉnh Lịch Trực

5.1. Kiểm Tra Số Ca/tuần

Trong sheet “Nhân viên”, tạo cột “Số Ca Thực Tế”:

=COUNTIF('Lịch Trực'!$C$2:$E$100, A2)

So sánh với cột “Số Ca/tuần”. Dùng conditional formatting để tô đỏ nếu vượt quá.

5.2. Kiểm Tra Ca Đêm Liên Tiếp

Thêm một cột phụ trong “Lịch Trực” để đánh dấu ca đêm:

=IF(E2<>"",1,0) 'E là cột Đêm

Sau đó, dùng công thức SUMPRODUCT để kiểm tra chuỗi 1:

=MAX(FREQUENCY(IF(E2:E100=1,ROW(E2:E100)),IF(E2:E100<>1,ROW(E2:E100)))) 'Đòi hỏi nhập dưới dạng mảng Ctrl+Shift+Enter

Nếu kết quả > 2 → có ca đêm liên tiếp quá 2 ngày.

5.3. Kiểm Tra Ngày Nghỉ

Tạo sheet “Nghỉ” liệt kê ngày nghỉ cho từng nhân viên. Sau đó, dùng công thức:

=IF(COUNTIFS(Nghỉ!$A:$A,$A2, Nghỉ!$B:$B, C2)>0, "NGỈ", "")

Nếu trả về “NGỈ” nhưng ô đã có nhân viên, thì cần sửa.

6. Tự Động Cập Nhật và Gửi Báo Cáo

6.1. Tạo Báo Cáo Tổng Hợp

Sử dụng PivotTable:
– Rows: Mã NV hoặc Tên NV.
– Columns: Loại Ca (Sáng, Chiều, Đêm).
– Values: Đếm số ô (số ca).

Kết quả cho phép quản lý nhanh tổng số ca mỗi nhân viên, tổng ca từng loại.

6.2. Gửi Email Tự Động

Với VBA, bạn có thể gửi lịch trực cho từng nhân viên:

Sub GuiMailLich() Dim OutlookApp As Object, Mail As Object Dim ws As Worksheet, wsNV As Worksheet Dim lastRow As Long, i As Long Set ws = Worksheets("Lịch Trực") Set wsNV = Worksheets("Nhân viên") Set OutlookApp = CreateObject("Outlook.Application") lastRow = wsNV.Cells(wsNV.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow Dim maNV As String, emailNV As String maNV = wsNV.Cells(i, 1).Value emailNV = wsNV.Cells(i, 7).Value 'Giả sử cột G chứa email Set Mail = OutlookApp.CreateItem(0) With Mail .To = emailNV .Subject = "Lịch Trực Tháng " & Format(Date, "mm/yyyy") .Body = "Kính gửi " & wsNV.Cells(i, 2).Value & vbCrLf & _ "Đây là lịch trực của bạn trong tháng:" & vbCrLf & _ GetLichCa(maNV, ws) 'Hàm tự viết trả về chuỗi lịch cá nhân .Send End With Next i
End Sub Function GetLichCa(maNV As String, ws As Worksheet) As String Dim rng As Range, r As Long, s As String Set rng = ws.Range("A2:E100") 'Vùng lịch For r = 1 To rng.Rows.Count If rng.Cells(r, 1).Value <> "" Then If rng.Cells(r, 3).Value = maNV Then s = s & rng.Cells(r, 1).Text & " - Sáng" & vbCrLf If rng.Cells(r, 4).Value = maNV Then s = s & rng.Cells(r, 1).Text & " - Chiều" & vbCrLf If rng.Cells(r, 5).Value = maNV Then s = s & rng.Cells(r, 1).Text & " - Đêm" & vbCrLf End If Next r GetLichCa = s
End Function

Lưu ý: Để macro chạy được, bạn cần bật “Trust access to the VBA project object model” trong Trust Center.

6.3. Đặt Lịch Tự Động Cập Nhật Hàng Tháng

• Tạo một Workbook Open Event để kiểm tra ngày hiện tại, nếu là ngày 1 của tháng, tự động chạy macro phân ca và gửi email.

Private Sub Workbook_Open() If Day(Date) = 1 Then Call PhanCaTuDong Call GuiMailLich End If
End Sub

7. Một Số Mẫu Lịch Trực Thực Tế

7.1. Mẫu Lịch Trực Bảo Vệ (5 người, 3 ca)

• Giới hạn: Mỗi người không quá 5 ca/tuần, ca đêm không quá 2 ngày liên tiếp.

7.2. Mẫu Lịch Trực Y Tá (4 người, 2 ca)

• Quy tắc: Không cho cùng một y tá làm ca sáng và chiều liên tiếp quá 3 ngày.

8. Những Sai Lầm Thường Gặp Khi Chia Lịch Trực Bằng Excel

9. Tối Ưu Hóa Hiệu Suất Khi Lịch Trực Lớn

1. Giảm số công thức volatile (NOW(), TODAY()) trong bảng lớn.
2. Sử dụng bảng (Table) thay vì phạm vi để giảm kích thước file.
3. Tắt tính năng Auto‑Recover khi không cần thiết.
4. Lưu dưới định dạng .xlsb (binary) để giảm dung lượng và tăng tốc mở/đóng.
5. Phân chia lịch theo tháng: mỗi file một tháng, tránh một file quá lớn.

10. Tổng Kết

Việc chia lịch trực bằng Excel không chỉ là một công việc hành chính đơn thuần mà còn là cơ hội để áp dụng các kỹ năng phân tích dữ liệu, tối ưu hoá và tự động hoá. Từ việc chuẩn bị dữ liệu nhân viên và ca, xây dựng bảng lịch cơ bản, đến việc sử dụng Solver hoặc VBA để tự động hoá, bạn có thể tạo ra một hệ thống lịch trực chuyên nghiệp, linh hoạt và dễ quản lý.

Các Bước Chính:

1. Thu thập dữ liệu: danh sách nhân viên, mô tả ca, ngày nghỉ lễ.
2. Thiết kế bảng lịch: cột ngày, ca, định dạng có điều kiện.
3. Áp dụng quy tắc: số ca/tuần, ca đêm liên tiếp, ngày nghỉ.
4. Tự động hoá: Solver, Macro, VBA, gửi email.
5. Kiểm tra & điều chỉnh: công thức kiểm tra ràng buộc, pivot tổng hợp.
6. Bảo trì: cập nhật dữ liệu, tối ưu file, sao lưu.

Bằng cách tuân thủ quy trình trên, bạn sẽ giảm thiểu sai sót, nâng cao hiệu quả làm việc của đội ngũ, đồng thời tạo ra một công cụ quản lý lịch trực mạnh mẽ, có thể mở rộng và tùy biến cho mọi loại hình doanh nghiệp.

Chúc bạn thành công trong việc chia lịch trực bằng Excel và đạt được sự cân bằng công việc tối ưu cho toàn bộ nhân viên!

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ẻ.