File Excel xếp lịch làm việc nhân viên: mẫu xếp ca theo tuần, tính giờ công, kiểm tra đủ người và thời gian nghỉ

Trả lời nhanh: file Excel xếp lịch làm việc là một lưới với nhân viên theo hàng và ngày theo cột; mỗi ô ghi một mã ca như S (sáng), C (chiều), Đ (đêm), N (nghỉ), P (nghỉ phép). Một bảng phụ ghi giờ bắt đầu, giờ kết thúc và số giờ của từng mã ca, nhờ đó Excel tự tính:

• Giờ công mỗi người: =SUMPRODUCT(COUNTIF(C5:I5,BangCa[Mã])*BangCa[Số giờ])
• Số người mỗi ca mỗi ngày: =COUNTIF(C$5:C$10,"S"), so với số người tối thiểu cần có
• Cảnh báo đổi ca khi thời gian nghỉ giữa hai ca quá ngắn

Bài viết hướng dẫn dựng file và đưa ví dụ lịch tuần cho một quán cà phê 6 nhân viên. Tên và giờ làm trong ví dụ là giả định.

Bước 1: Bảng mã ca

Tạo sheet “Ca”, định dạng thành bảng tên BangCa:

Nếu ca có thời gian nghỉ giữa ca không tính lương, ghi số giờ thực làm vào cột Số giờ. Khi đổi giờ một ca, chỉ cần sửa bảng này, toàn bộ lịch tự cập nhật.

Bước 2: Lưới lịch làm việc

1. Sheet “Lịch”: ô C3 nhập ngày đầu tuần (hoặc đầu tháng), D3 nhập =C3+1, kéo sang phải 7 cột (hoặc 31 cột). Định dạng ô kiểu ddd dd/mm để hiện thứ và ngày.
2. Cột A ghi tên nhân viên, cột B ghi loại hợp đồng hoặc số giờ chuẩn mỗi tuần.
3. Chọn vùng xếp ca → Data → Data Validation → List, nguồn =INDIRECT("BangCa[Mã]") để chỉ chọn được mã hợp lệ.
4. Tô màu cuối tuần bằng định dạng có điều kiện =WEEKDAY(C$3,2)>5.

Bước 3: Tính giờ công và chi phí nhân công

• Giờ công tuần của mỗi người (cột J): =SUMPRODUCT(COUNTIF(C5:I5,BangCa[Mã])*BangCa[Số giờ])
• Số ngày nghỉ: =COUNTIF(C5:I5,"N")+COUNTIF(C5:I5,"P")
• Chênh lệch so với giờ chuẩn: =J5-B5; số dương là giờ vượt cần xử lý theo quy định làm thêm giờ.
• Chi phí nhân công dự kiến: =J5*DonGiaGio, cộng cả cột để biết chi phí tuần.

Lịch làm việc là kế hoạch; giờ làm thực tế (đi muộn, về sớm, đổi ca đột xuất) cần ghi trong mẫu bảng chấm công Excel để tính lương chính xác.

Bước 4: Kiểm tra đủ người mỗi ca

Dưới danh sách nhân viên, thêm các dòng:

• Số người ca sáng ngày đầu: =COUNTIF(C$5:C$10,"S")
• Số người tối thiểu cần (nhập tay theo ngày, ví dụ cuối tuần đông khách cần nhiều hơn)
• Cảnh báo: =IF(C12<C13,"THIẾU "&C13-C12,""), tô đỏ

Khi có người xin nghỉ phép, đổi mã thành P và nhìn ngay ngày nào bị thiếu người.

Bước 5: Cảnh báo thời gian nghỉ và giờ làm

Bộ luật Lao động có các quy định về thời giờ làm việc, nghỉ ngơi mà lịch phải tôn trọng, trong đó có: thời giờ làm việc bình thường không quá 8 giờ một ngày và 48 giờ một tuần; người làm theo ca được nghỉ ít nhất 12 giờ trước khi chuyển sang ca khác; mỗi tuần được nghỉ ít nhất 24 giờ liên tục; làm thêm giờ có giới hạn theo ngày, tháng và năm; giờ làm việc ban đêm tính từ 22 giờ đến 6 giờ sáng hôm sau. Hãy đối chiếu với văn bản hiện hành, thỏa ước và nội quy của doanh nghiệp.

Với bảng ca ở trên, ca C kết thúc 22:00 và ca S bắt đầu 6:00 hôm sau, chỉ cách nhau 8 giờ, nên chuỗi C → S không đủ thời gian nghỉ khi đổi ca. Dòng kiểm tra cho từng nhân viên:

=IF(AND(C5="C",D5="S"),"ĐỔI CA <12H","")

Thêm cảnh báo giờ tuần: =IF(J5>48,"VƯỢT 48H",""). Lưu ý ca chiều kết thúc đúng 22:00 nên không phát sinh giờ làm đêm; nếu bạn kéo ca đến 23:00, giờ từ 22:00 trở đi là giờ làm việc ban đêm và có chế độ trả lương riêng.

Ví dụ: lịch tuần của quán cà phê 6 nhân viên

Quán mở cửa cả tuần, cần 2 người ca sáng và 2 người ca chiều từ thứ Hai đến thứ Sáu; thứ Bảy, Chủ nhật đông khách buổi tối nên cần 3 người ca chiều. Mỗi người làm 5 ca 8 giờ (40 giờ/tuần) và nghỉ 2 ngày.

Kiểm tra: mọi ngày đều đủ người, không ai có chuỗi C → S, ai cũng có 2 ngày nghỉ và 40 giờ/tuần. Lịch xoay theo hướng “sáng trước, chiều sau” (Phúc: S, S rồi C) là cách đơn giản để luôn đủ thời gian nghỉ khi đổi ca. Tuần sau nên xoay vòng ngày nghỉ để không ai nghỉ cuối tuần mãi, bắt đầu từ Lan, người tuần này được nghỉ cả thứ Bảy và Chủ nhật.

Bước 6: Tô màu và chia sẻ lịch

• Tạo một quy tắc định dạng có điều kiện cho mỗi mã: Format only cells that contain → Cell Value → equal to → ="S", chọn màu; lặp lại với C, G, N, P.
• Freeze Panes ở ô C5 để luôn thấy tên và ngày khi cuộn.
• Page Layout → Landscape, Fit to 1 page wide để in dán ở quầy.
• Chia sẻ bản xem trên Google Sheets hoặc gửi ảnh chụp lịch vào nhóm chat của quán mỗi cuối tuần.
• Khóa công thức, chỉ cho sửa ô xếp ca (Review → Protect Sheet).

Lỗi thường gặp

• Gõ giờ trực tiếp vào ô (“6h-14h”) thay vì mã ca, không tính được giờ công.
• Không kiểm tra đủ người, đến ngày mới biết thiếu người.
• Xếp ca chiều rồi ca sáng hôm sau, nhân viên không đủ thời gian nghỉ.
• Lịch công bố quá sát ngày, nhân viên khó sắp xếp việc riêng.
• Không lưu lịch các tuần trước, khó đối chiếu khi tính lương hay giải quyết khiếu nại.

Câu hỏi thường gặp

Làm sao xếp ca xoay vòng tự động?

Ghi mẫu xoay vòng (ví dụ chu kỳ 14 ngày) vào một dòng phụ, rồi dùng =INDEX(MauXoay,MOD(C$3-NgayBatDau+DoLech,14)+1) cho mỗi nhân viên với độ lệch khác nhau. Sau đó vẫn cần kiểm tra lại số người mỗi ca vì nghỉ phép làm thay đổi lịch.

Nhân viên part-time tính giờ thế nào?

Tạo mã ca riêng cho từng khung giờ part-time (như G ở trên) với số giờ tương ứng; công thức tổng giờ vẫn giữ nguyên.

Dùng Google Sheets được không?

Được. COUNTIF, SUMPRODUCT, IF, AND, INDEX và MOD đều có trên Google Sheets; thay tham chiếu bảng bằng vùng ô thông thường.

Xem thêm các mẫu vận hành trong nhóm Vận hành và nhân sự.