Cách xếp lịch trực trên Excel cho trung tâm đào tạo: mẫu bảng, công thức phát hiện trùng ca

Cách xếp lịch trực trên Excel dễ kiểm soát nhất cho một trung tâm đào tạo là ghi mỗi lượt trực thành một dòng gồm ngày, ca, vị trí và người trực, giữ danh mục người và ca ở sheet riêng, chặn lỗi nhập bằng Data Validation, rồi dùng hàm COUNTIFS cùng định dạng có điều kiện (Conditional Formatting) để tô màu những dòng trùng ca, những ca còn thiếu người hoặc xếp nhầm người đã báo bận. Bảng lưới nhiều màu chỉ nên là bản để xem; dữ liệu gốc dạng dòng mới là thứ Excel đếm và kiểm tra được.^[1][2][3]

Bài viết dành cho người điều phối vận hành, giáo vụ và quản lý cơ sở đang xếp lịch trực lễ tân, tư vấn tuyển sinh, trợ giảng hay giảng viên các buổi cuối tuần. Bạn sẽ có cấu trúc file, các công thức kiểm tra, cách xoay vòng cho công bằng, cách nhận lịch bận và yêu cầu đổi ca, cách chia sẻ cho cả đội, và các dấu hiệu cho thấy đã đến lúc dùng công cụ chuyên cho việc xếp lịch.

Lịch trực ở trung tâm đào tạo phải thỏa những ràng buộc nào?

Bài toán xếp lịch y tá (nurse scheduling problem) trong vận trù học chia điều kiện của một lịch làm việc thành hai loại: ràng buộc cứng, phải thỏa thì lịch mới hợp lệ, và ràng buộc mềm, nên có nhưng không bắt buộc, dùng để so lịch nào tốt hơn. Ràng buộc cứng có thể đến từ giới hạn thực tế hoặc yêu cầu pháp lý; ràng buộc mềm thường là chính sách của đơn vị hoặc nguyện vọng của từng người. Chuyển sang một trung tâm đào tạo, danh sách thường trông như sau:^[6]

• Cứng: ca nào cũng có đủ người theo nhu cầu, ví dụ quầy lễ tân buổi tối luôn có người khi lớp còn học.
• Cứng: một người không trực hai vị trí trong cùng một ca.
• Cứng: ca cần kỹ năng riêng phải do người đủ điều kiện trực, ví dụ ca tư vấn khóa doanh nghiệp hay trợ giảng lớp luyện thi.
• Cứng hoặc mềm tùy đơn vị: người mới vào được xếp cùng một người đã có kinh nghiệm.
• Mềm: mọi người trực số ca cuối tuần gần bằng nhau, và các ca khó (buổi khai giảng, ca tối đông học viên) cũng được chia gần đều.
• Mềm: tôn trọng nguyện vọng như không trực tối thứ Tư vì đi học, hoặc muốn trực cùng một đồng nghiệp.

^[6]

Ghi danh sách này ra trước khi mở Excel. Công thức chỉ kiểm tra được những điều bạn đã định nghĩa thành cột và quy tắc; ràng buộc nào chỉ nằm trong đầu người xếp lịch thì sẽ bị quên khi người đó nghỉ phép.

Cấu trúc file: mỗi lượt trực là một dòng

Nhiều mẫu trên mạng dựng một lưới với tên người ở hàng và ngày ở cột. Lưới dễ nhìn, nhưng nếu chỉ ghi tên và tô màu bằng tay mà không có công thức kiểm tra, người điều phối phải tự dò để tìm ca thiếu người hay một người bị xếp hai vị trí cùng lúc. Dữ liệu dạng dòng giúp áp cùng một bộ công thức cho cả tháng. Hãy tách dữ liệu theo vai trò của từng sheet:

• DanhMuc: mỗi dòng là một cặp người và vị trí người đó được phép trực (lễ tân, tư vấn, trợ giảng, giảng viên), kèm cơ sở làm việc. Một người trực được ba vị trí thì có ba dòng.
• Ca: mã ca (Sáng, Chiều, Tối, Cả ngày), giờ bắt đầu và giờ kết thúc.
• NhuCau: ngày, ca, vị trí và số người cần cho từng ca. Đây là thứ ban giám đốc duyệt trước, không phải tên người.
• KhaDung: người, ngày, ca và trạng thái Bận hoặc Ưu tiên, do chính nhân sự điền.
• Lich: ngày, ca, vị trí, người trực, loại ngày (ngày thường, cuối tuần, lễ), ghi chú và các cột kiểm tra. Chỉ thêm dòng khi đã có người cho lượt trực đó.
• DoiCa: nhật ký yêu cầu đổi ca, người duyệt và trạng thái.

Các công thức trong bài dùng đúng thứ tự cột sau, hàng đầu là tiêu đề, dữ liệu bắt đầu từ hàng thứ hai. Lich: A Ngày, B Ca, C Vị trí, D Người trực, E Loại ngày, F Ghi chú, G Trùng ca, H Báo bận, I Sai điều kiện. NhuCau: A Ngày, B Ca, C Vị trí, D Số người cần, E Số đã xếp. KhaDung: A Người, B Ngày, C Ca, D Trạng thái. DanhMuc: A Người, B Vị trí, C Cơ sở.

Bản lưới để dán lên bảng tin hay gửi vào nhóm chat được dựng từ sheet Lich, không gõ tay song song. Giữ một nguồn duy nhất là cách đơn giản nhất để không có hai phiên bản lịch khác nhau trong cùng một tuần.

Chặn lỗi nhập bằng Data Validation

Data Validation của Excel giới hạn kiểu dữ liệu hoặc giá trị được nhập vào ô, có thể hiện danh sách thả xuống, hiện lời nhắc khi người dùng chọn ô và báo lỗi khi họ nhập sai. Với file lịch trực, nên đặt ba lớp:^[3]

• Cột Ca và Loại ngày: chọn Allow là List, ô Source gõ các giá trị cách nhau bằng dấu phẩy, ví dụ Sáng,Chiều,Tối,Cả ngày, và giữ ô In-cell dropdown được chọn để hiện mũi tên thả xuống.
• Cột Ngày: chọn Allow là Date và đặt khoảng từ ngày đầu đến ngày cuối của tháng đang xếp, để không ai gõ nhầm sang tháng khác.
• Cột Người: với đội ít người, có thể dùng List và gõ thẳng các tên vào ô Source; với đội đông, để cột kiểm tra I ở phần sau bắt những tên không có trong DanhMuc cho vị trí đó.

^[3]

Ở thẻ Error Alert, kiểu Stop buộc người nhập sửa lỗi trước khi đi tiếp, kiểu Warning báo dữ liệu không hợp lệ và hỏi có muốn tiếp tục không, còn một kiểu nữa chỉ báo rồi cho đi tiếp sau khi tắt thông báo. Ô Ca nên để Stop; ngày ngoài tháng có thể để Warning nếu đôi khi bạn xếp trước cho tuần đầu tháng sau. Đặt các quy tắc này trước khi chia sẻ file hoặc bảo vệ sheet, vì Excel không cho đổi thiết lập Data Validation khi sổ làm việc đang được chia sẻ hoặc sheet đang được bảo vệ.^[3]

Công thức COUNTIFS phát hiện trùng ca, thiếu người và xếp nhầm người bận

COUNTIFS áp các điều kiện lên nhiều vùng ô và đếm số lần mọi điều kiện cùng được thỏa; mỗi cặp vùng và điều kiện được xét lần lượt từng ô. Hai điều cần nhớ: mọi vùng điều kiện phải có cùng số hàng và số cột với vùng đầu tiên, và nếu điều kiện tham chiếu tới một ô trống thì hàm coi ô trống đó là giá trị 0. Vì vậy các công thức dưới đây đều có thêm LEN($D2)>0 để chỉ xét dòng đã có tên người trực, và mọi vùng đều dùng cùng hàng từ 2 đến 500.^[1]

• Trùng ca (sheet Lich, cột G): =AND(LEN($D2)>0,COUNTIFS($A$2:$A$500,$A2,$B$2:$B$500,$B2,$D$2:$D$500,$D2)>1). Kết quả TRUE nghĩa là cùng ngày, cùng ca, người này xuất hiện ở hơn một dòng. Ví dụ Lan có hai dòng ca Sáng thứ Bảy, ở Lễ tân và Trợ giảng: cột G của cả hai dòng đều là TRUE.
• Xếp nhầm người đã báo bận (cột H): =AND(LEN($D2)>0,COUNTIFS(KhaDung!$A$2:$A$500,$D2,KhaDung!$B$2:$B$500,$A2,KhaDung!$C$2:$C$500,$B2,KhaDung!$D$2:$D$500,"Bận")>0).
• Chưa đủ điều kiện cho vị trí (cột I): =AND(LEN($D2)>0,COUNTIFS(DanhMuc!$A$2:$A$500,$D2,DanhMuc!$B$2:$B$500,$C2)=0).
• Số người đã xếp cho từng ca (sheet NhuCau, cột E): =COUNTIFS(Lich!$A$2:$A$500,$A2,Lich!$B$2:$B$500,$B2,Lich!$C$2:$C$500,$C2). So cột này với cột D (số người cần). Lưu ý COUNTIFS đếm số dòng phân công, không đếm người hợp lệ: một dòng trùng ca hay xếp nhầm người bận vẫn được tính, nên chỉ đọc kết quả đủ hay thiếu người sau khi đã xử lý hết các dòng bị tô màu ở sheet Lich.

^[1][2]

Công thức trong bài theo cú pháp trên trang hỗ trợ của Microsoft, dùng dấu phẩy ngăn đối số; nếu Excel trên máy bạn không nhận, hãy kiểm tra dấu ngăn đối số trong thiết lập vùng của máy. Một giới hạn cần biết: công thức so theo mã ca, nên nếu đơn vị có hai ca chồng giờ nhau, chẳng hạn ca Sáng và ca Cả ngày, Excel sẽ không tự thấy chúng trùng. Cách dễ nhất là thiết kế các ca không chồng giờ, hoặc tách ca Cả ngày thành hai dòng Sáng và Chiều.^[1]

Data Validation cũng có thể chặn trùng ca ngay lúc nhập. Microsoft minh họa quy tắc Custom =COUNTIF($A$2:$A$10,A2)=1 để bảo đảm mọi giá trị trong vùng là duy nhất; áp ý đó cho cột Người với công thức COUNTIFS ba điều kiện ngày, ca, người bằng 1 thì Excel từ chối khi ai đó xếp một người vào ca thứ hai trong cùng buổi. Nhược điểm là người xếp lịch không thể tạm nhập để thử phương án, nên ở giai đoạn nháp, cảnh báo bằng màu thường tiện hơn.^[3]

Tô màu dòng lỗi bằng định dạng có điều kiện

Trong Conditional Formatting, chọn New Rule rồi Use a formula to determine which cells to format. Công thức phải mở đầu bằng dấu = và trả về TRUE hoặc FALSE; có thể ghép nhiều phép kiểm tra bằng AND, OR, và có thể đánh giá ô nằm ở sheet khác trong cùng sổ làm việc. Nếu muốn Excel điều chỉnh tham chiếu cho từng ô trong vùng được chọn, dùng tham chiếu tương đối; khi bạn bấm chọn ô trên sheet, Excel chèn tham chiếu tuyệt đối, nên phải sửa lại cho đúng.^[2]

• Chọn toàn vùng dữ liệu của sheet Lich từ cột A đến cột I, tạo quy tắc =$G2=TRUE với nền đỏ nhạt: cả dòng trùng ca đổi màu, vì cột G được khóa bằng dấu $ còn số hàng thì đổi theo từng dòng.
• Thêm quy tắc =$H2=TRUE với nền cam cho người đã báo bận, và =$I2=TRUE với nền tím nhạt cho người chưa đủ điều kiện.
• Ở sheet NhuCau, quy tắc =$D2>$E2 (số người cần lớn hơn số đã xếp) tô đỏ ca thiếu người, =$E2>$D2 tô vàng ca thừa người.

^[2]

Microsoft có ví dụ dùng định dạng có điều kiện trên cột Instructor để tìm người dạy nhiều hơn một lớp, bằng quy tắc Duplicate Values. Với lịch trực cả tháng, quy tắc này không đủ: tên một người lặp lại ở nhiều ngày là chuyện bình thường, nên mọi ô đều bị tô. Trong cấu trúc bảng này, công thức đếm đồng thời ngày, ca và người giúp phân biệt trùng ca với việc một người xuất hiện ở nhiều ngày. Lưu ý thêm: nếu ô chứa công thức trả về lỗi, định dạng có điều kiện không được áp vào ô đó; bọc công thức bằng IFERROR để lỗi không làm mất cảnh báo.^[2]

Xoay vòng công bằng và đếm số ca từng người

Ví dụ ràng buộc mềm hay gặp trong các mô tả bài toán xếp lịch là mọi người trực số ca cuối tuần gần bằng nhau và gánh độ khó gần như nhau. Excel không tự chia đều cho bạn, nhưng giúp bạn nhìn thấy chỗ lệch. Lập sheet ThongKe với mỗi người một dòng:^[6]

• Tổng số ca trong tháng: =COUNTIFS(Lich!$D$2:$D$500,$A2).
• Số ca cuối tuần và ngày lễ: =COUNTIFS(Lich!$D$2:$D$500,$A2,Lich!$E$2:$E$500,"Cuối tuần") cộng với công thức tương tự cho "Lễ".
• Số ca tối hoặc ca khai giảng, nếu đơn vị coi đó là ca nặng: thêm một cột đánh dấu ca nặng trong sheet Lich và đếm theo cột đó.

^[1]

Để xếp nháp, nhiều người dùng một vòng xoay cố định: sắp danh sách nhân sự của từng vị trí theo một thứ tự, tuần đầu bắt đầu từ người thứ nhất, tuần sau dịch xuống một người, cuối danh sách thì quay lại đầu. Sau đó mới sửa tay theo lịch bận và kỹ năng. Trước khi chốt, xem bảng ThongKe: nếu người nhiều nhất và người ít nhất chênh nhau quá ngưỡng mà đơn vị đã thống nhất, hãy đổi vài lượt trực rồi để các cột kiểm tra chạy lại. Ngưỡng này là chính sách nội bộ, nên ghi nó ở đầu file để ai xếp lịch cũng dùng chung một chuẩn.

Nhận lịch bận và yêu cầu đổi ca mà không làm rối file

Sheet KhaDung chỉ có ích khi nhân sự tự điền trước hạn. Để họ điền được mà không vô tình sửa sheet Lich hay công thức, dùng tính năng bảo vệ sheet theo hai bước: bỏ chọn Locked ở thẻ Protection cho những ô người khác được sửa, rồi vào Review, Protect Sheet và chọn các thao tác được phép. Microsoft lưu ý bảo vệ sheet không phải tính năng bảo mật; nó chỉ ngăn sửa các ô đã khóa. Nếu cần giới hạn ai được mở file, đó là chuyện phân quyền khi chia sẻ, không phải chuyện khóa ô.^[4]

Với đổi ca, quy tắc đơn giản nhất là không ai sửa trực tiếp sheet Lich. Người muốn đổi ghi một dòng vào DoiCa: ngày, ca, người xin đổi, người nhận thay, lý do. Người điều phối duyệt, sửa dòng tương ứng trong Lich và ghi người duyệt cùng thời điểm vào nhật ký. Các cột kiểm tra sẽ tự báo nếu người nhận thay đang trùng ca hoặc đã báo bận. Cách này chậm hơn nhắn tin cho nhau vài phút, nhưng cuối tháng bạn biết vì sao lịch khác bản đã gửi.

Chia sẻ lịch cho cả đội

Muốn nhiều người cùng mở một file thay vì mỗi người giữ một bản đính kèm, hãy dùng tính năng đồng tác giả (co-authoring) của Excel. Theo trang hỗ trợ của Microsoft, file cần lưu trên OneDrive, OneDrive for Business hoặc thư viện SharePoint Online, ở định dạng .xlsx, .xlsm hoặc .xlsb; người dùng ứng dụng Excel trên máy tính cần gói Microsoft 365 và nên bật AutoSave. Người chưa có phiên bản hỗ trợ vẫn có thể sửa trong trình duyệt.^[5]

• Khi bấm Share, mặc định mọi người nhận đều được sửa; đổi tùy chọn Can edit để phần lớn nhân sự chỉ xem, còn quyền sửa dành cho người điều phối và những người cần điền lịch bận.
• Gửi đường dẫn tới file chứ không gửi bản sao, để mọi người luôn thấy bản mới nhất.
• Khi có tranh cãi về việc ai đã đổi gì, mở File, Info, Version History để xem và khôi phục một phiên bản cũ.

^[5]

Cách xếp lịch trực trên Excel cho một tháng: bảy bước

Ghép các phần trên lại, một chu kỳ xếp lịch hằng tháng có thể đi theo bảy bước dưới đây. Lần đầu sẽ mất thời gian dựng file; từ tháng thứ hai, bạn chủ yếu chép sheet NhuCau, nhận lịch bận và xếp lại.

• Chốt ràng buộc cứng và mềm của tháng, kể cả ngưỡng chênh lệch số ca được chấp nhận.
• Cập nhật DanhMuc: người mới, người nghỉ, vị trí mỗi người đã đủ điều kiện trực.
• Lập NhuCau theo lịch khai giảng, sự kiện tuyển sinh và giờ mở cửa từng cơ sở.
• Mở KhaDung cho nhân sự điền trước một hạn chót rõ ràng.
• Xếp nháp theo vòng xoay, rồi sửa theo lịch bận và kỹ năng.
• Chạy kiểm tra: không còn dòng đỏ, cam, tím ở Lich, không còn ca đỏ ở NhuCau, bảng ThongKe nằm trong ngưỡng.
• Chia sẻ bản chốt, chuyển mọi thay đổi sau đó qua DoiCa.

Giới hạn của Excel và khi nào nên chuyển sang công cụ xếp lịch

Bài toán xếp lịch nhân viên có lõi gần với bài toán xếp lịch y tá, vốn thuộc loại NP-khó; độ phức tạp đến từ việc gán người vào rất nhiều phương án ca, mỗi phương án chịu những quy tắc khác nhau. Phần mềm xếp lịch thường có các chức năng như xử lý yêu cầu nghỉ và đổi ca, phát hiện ca thừa hoặc thiếu người, chỉ ra ca chưa có ai nhận; dù vậy, cách dùng hiệu quả thường là kết hợp phần mềm với phán đoán của người quản lý thay vì phó mặc hoàn toàn cho máy.^[7]

Với một trung tâm đào tạo, những dấu hiệu sau cho thấy file Excel đã chạm giới hạn:

• Nhiều cơ sở dùng chung một nhóm giảng viên hoặc trợ giảng, và mỗi cơ sở giữ một file riêng.
• Đổi ca diễn ra gần như mỗi ngày, nhật ký DoiCa dài hơn chính sheet Lich.
• Khi một giảng viên báo nghỉ, bạn phải dò tay xem những lớp nào bị ảnh hưởng và ai đủ điều kiện dạy thay.
• Bộ phận tài chính cần số giờ dạy đã đối soát, còn bạn phải cộng tay từ nhiều sheet.

Lịch trực lễ tân hay tư vấn tuyển sinh thường vẫn ổn trong Excel nếu đã có các cột kiểm tra ở trên. Phần dễ vượt khỏi bảng tính là lịch gắn với lớp học: giảng viên, trợ giảng và các buổi cuối tuần. Quản lý giảng viên của Thisme lưu hồ sơ của giảng viên cơ hữu, thỉnh giảng và cộng tác viên với lĩnh vực chuyên môn, lịch khả dụng và các giới hạn đã thống nhất; hệ thống hỗ trợ lọc người phù hợp, phát hiện xung đột và lưu quyết định phân công, và khi có thay đổi, người phụ trách biết lớp nào bị ảnh hưởng để tìm phương án thay thế. Phạm vi của nó dừng ở nguồn lực giảng dạy: chấm công, tính lương và đánh giá nhân sự vẫn thuộc hệ thống nhân sự của đơn vị.

Nếu vấn đề nằm ở vận hành từng buổi học, Quản lý lớp học của Thisme giúp lập lịch, giữ phòng, gán giảng viên và trợ giảng, kèm bảng theo dõi nêu xung đột hay buổi chưa có giảng viên. Không cần chuyển mọi lớp cùng lúc: có thể thử với một nhóm lớp hoặc một chu kỳ mới, chốt quy tắc lịch, đổi buổi và người được thông báo trước.

Bắt đầu từ tháng tới

Tóm lại, cách xếp lịch trực trên Excel không bắt đầu từ một mẫu lưới đẹp mà từ danh sách ràng buộc, một sheet dữ liệu dạng dòng và vài cột COUNTIFS được tô màu khi có lỗi. Hãy dựng file cho một vị trí trước, chẳng hạn lễ tân, chạy thử một tháng, rồi mới thêm tư vấn, trợ giảng và giảng viên cuối tuần.^[1][2]

Khi lịch giảng viên và trợ giảng bắt đầu kéo theo nhiều lớp, nhiều cơ sở, chúng ta có thể cùng xem quy trình phân công hiện tại của đơn vị trước khi bàn đến công cụ. Đăng ký buổi khảo sát để Thisme cùng bạn rà lại một chu kỳ xếp lịch cụ thể.

Nguồn tham khảo

1. COUNTIFS function – Microsoft Support
2. Use conditional formatting to highlight information in Excel – Microsoft Support
3. Apply data validation to cells – Microsoft Support
4. Protect a worksheet – Microsoft Support
5. Collaborate on Excel workbooks at the same time with co-authoring – Microsoft Support
6. Nurse scheduling problem – Wikipedia
7. Employee scheduling software – Wikipedia