Cách Xếp Lịch Dạy Thêm Không Trùng Giờ Bằng Google Sheets

Trả lời nhanh: Lập bảng lịch học mỗi dòng một buổi (lớp, thứ, giờ bắt đầu, thời lượng, giáo viên, phòng), đổi giờ ra số phút, rồi dùng COUNTIFS đếm các buổi cùng thứ, cùng giáo viên hoặc cùng phòng có khoảng giờ giao nhau. Kết quả lớn hơn 1 nghĩa là trùng. Sau đó tô đỏ dòng đó bằng định dạng có điều kiện.

Tối chủ nhật, cô Lan ngồi chép lịch tuần mới vào sổ tay. Thứ 3 có lớp Toán 9A lúc 17:30, đến lớp Lý 9A lúc 18:30 mới sực nhớ là Toán còn dạy tới 19:00. Phụ huynh đã nhận lịch từ tuần trước. Giờ cô phải nhắn từng nhóm để dời giờ, và một nhóm sẽ phải đổi lịch đưa đón con. Đây là tình huống minh họa, nhưng ai dạy thêm vài lớp một lúc chắc đều từng gặp một phiên bản của nó.

Lỗi trùng giờ hiếm khi do ẩu. Nó xuất hiện vì lịch nằm ở nhiều chỗ: sổ tay, tin nhắn nhóm, vài ghi chú trên điện thoại. Mắt người chỉ so sánh được hai ba dòng một lúc. Bảng tính thì so sánh một trăm dòng chỉ trong vài giây. Bài này đi từng bước dựng cái bảng đó.

Dựng bảng lịch học: một buổi một dòng

Sai lầm hay gặp nhất là vẽ lịch như thời khóa biểu, thứ chạy ngang, giờ chạy dọc, mỗi ô ghi tên lớp. Nhìn thì dễ chịu, nhưng công thức không đếm được trùng giờ trên kiểu bảng đó. Hãy làm bảng dạng danh sách: mỗi buổi học là một dòng, mỗi thông tin là một cột. Dòng 1 là tiêu đề, dữ liệu bắt đầu từ dòng 2.

Cột	Nội dung	Ví dụ
A	Tên lớp	Toán 9A
B	Thứ	Thứ 3
C	Giờ bắt đầu (định dạng giờ)	17:30
D	Thời lượng (phút)	90
E	Giáo viên	Cô Lan
F	Phòng hoặc "Online"	Phòng 1
G	Phút bắt đầu (công thức)	1050
H	Phút kết thúc (công thức)	1140
I	Kết quả kiểm tra (công thức)	OK / Trùng giáo viên / Trùng phòng

Có hai điều cần giữ nghiêm. Thứ nhất, cách ghi cột B phải đồng nhất. Đã chọn "Thứ 3" thì mọi dòng đều "Thứ 3", đừng lúc viết "T3", lúc viết "thứ ba", vì công thức coi chúng là ba ngày khác nhau. Thứ hai, tên giáo viên cũng vậy: "Cô Lan" và "Cô Lan " (thừa dấu cách) là hai người. Cách đỡ mệt nhất là dùng danh sách thả xuống: chọn cột B, vào Dữ liệu → Xác thực dữ liệu, chọn "Danh sách mục" rồi nhập Thứ 2 đến Chủ nhật. Làm tương tự cho cột E và F.

Với lớp học online, cột F ghi chữ "Online" còn đường link để ở một cột riêng bên phải. Lý do sẽ rõ ở phần công thức.

Đổi giờ ra số phút trước khi so sánh

Nghe thì thừa, nhưng đây là chỗ nhiều người hỏng công thức mà không biết. Google Sheets lưu giờ dưới dạng phân số của một ngày: 19:00 là 0,7916666… chứ không phải một số tròn. Khi ghép số đó vào điều kiện của COUNTIFS bằng dấu &, nó bị cắt bớt chữ số. Hai lớp nối tiếp nhau (lớp này hết đúng 19:00, lớp kia bắt đầu đúng 19:00) có thể bị báo trùng oan, hoặc ngược lại bị bỏ sót. Báo trùng oan còn tệ hơn không báo, vì sau vài lần bạn sẽ ngừng tin cảnh báo đỏ.

Cách tránh là quy mọi thứ ra số nguyên phút. Giả sử dòng 2 là Toán 9A, bắt đầu 17:30, dạy 90 phút. Ở ô G2 gõ:

=IF(A2="","",HOUR(C2)*60+MINUTE(C2))

Ô G2 ra 1050 (17 giờ × 60 + 30 phút). Ở ô H2, phút kết thúc:

=IF(A2="","",G2+D2)

Ô H2 ra 1140, tức đúng 19:00. Kéo hai ô này xuống hết các dòng sẽ dùng, ví dụ đến dòng 100. Phần IF(A2="","",…) giữ cho những dòng chưa nhập không hiện số rác.

Công thức phát hiện trùng giáo viên và trùng phòng

Hai buổi được coi là chồng giờ khi cùng thứ và buổi này bắt đầu trước lúc buổi kia kết thúc, đồng thời kết thúc sau lúc buổi kia bắt đầu. Viết thành điều kiện: phút bắt đầu của buổi khác nhỏ hơn phút kết thúc của dòng đang xét, và phút kết thúc của buổi khác lớn hơn phút bắt đầu của dòng đang xét. Dấu "nhỏ hơn" và "lớn hơn" chặt (không có dấu bằng) là cố ý: lớp hết 19:00 và lớp bắt đầu 19:00 là hai buổi nối tiếp, không phải trùng.

Ở ô I2, dán công thức sau:

=IF(A2="","",IF(COUNTIFS($B$2:$B$100,B2,$E$2:$E$100,E2,$G$2:$G$100,"<"&H2,$H$2:$H$100,">"&G2)>1,"Trùng giáo viên",IF(AND(F2<>"Online",COUNTIFS($B$2:$B$100,B2,$F$2:$F$100,F2,$G$2:$G$100,"<"&H2,$H$2:$H$100,">"&G2)>1),"Trùng phòng","OK")))

Đọc từ trong ra ngoài thì dễ hiểu hơn nhìn cả dòng. COUNTIFS đầu tiên đếm các dòng có cùng thứ (cột B), cùng giáo viên (cột E) và khoảng phút giao với dòng đang xét. Dòng nào cũng giao với chính nó nên kết quả tối thiểu là 1. Vì thế điều kiện trùng là lớn hơn 1, không phải lớn hơn 0. COUNTIFS thứ hai làm y hệt nhưng thay cột giáo viên bằng cột phòng, và bỏ qua các lớp ghi "Online" để hai lớp học qua mạng cùng giờ không bị báo trùng phòng. Nếu hai lớp online do cùng một giáo viên thì COUNTIFS đầu tiên vẫn bắt được.

Thử với bốn dòng minh họa

Giả sử bảng có bốn buổi vào Thứ 3 như sau (số liệu giả định):

Lớp	Bắt đầu	Thời lượng	Giáo viên	Phòng	Phút (từ–đến)	Kết quả
Toán 9A	17:30	90	Cô Lan	Phòng 1	1050–1140	Trùng giáo viên
Lý 9A	18:30	90	Cô Lan	Phòng 2	1110–1200	Trùng giáo viên
Anh 7B	19:00	60	Thầy Nam	Phòng 1	1140–1200	OK
Văn 8C	17:45	60	Thầy Nam	Phòng 1	1065–1125	Trùng phòng

Toán 9A và Lý 9A đụng nhau vì cô Lan không thể đứng hai lớp trong khoảng 18:30 đến 19:00. Anh 7B vào Phòng 1 lúc 19:00, đúng lúc Toán 9A trả phòng, nên công thức cho qua. Văn 8C bị báo trùng phòng vì Phòng 1 đang có Toán 9A từ 17:30 đến 19:00. Thầy Nam thì không sao, vì Văn 8C hết 18:45 còn Anh 7B bắt đầu 19:00.

Một chi tiết cần biết: dòng Toán 9A thực ra còn trùng phòng với Văn 8C, nhưng công thức kiểm giáo viên trước nên chỉ hiện "Trùng giáo viên". Sửa xong lỗi đầu, nhãn mới đổi sang "Trùng phòng". Nếu muốn thấy cả hai lỗi cùng lúc, tách thành hai cột kiểm tra riêng. Với vài chục buổi một tuần thì một cột là đủ.

Tô đỏ dòng trùng và khóa cột công thức

Chữ "Trùng giáo viên" nằm ở cột I thì dễ bị bỏ qua khi bạn đang cuộn xuống phía dưới. Hãy để cả dòng đổi màu:

• Chọn vùng A2:I100, vào Định dạng → Định dạng có điều kiện.
• Ở phần "Định dạng ô nếu", chọn "Công thức tùy chỉnh là".
• Dán =LEFT($I2,5)="Trùng". Dấu $ trước chữ I giữ cố định cột I, còn số 2 không có $ để quy tắc tự dời theo từng dòng.
• Chọn nền đỏ nhạt, chữ đỏ đậm rồi bấm Xong.

Dùng LEFT thay vì so sánh từng nhãn để cả hai kiểu "Trùng giáo viên" và "Trùng phòng" đều ăn màu bằng một quy tắc. Dòng chưa nhập có cột I rỗng nên không bị tô.

Còn một rủi ro mà ai chia sẻ file cho trợ giảng đều gặp: có người gõ đè lên cột G, H hoặc I, công thức biến mất và bảng im lặng thôi cảnh báo mà không ai hay. Nên bảo vệ ba cột này ngay từ đầu bằng Dữ liệu → Bảo vệ trang tính và dải ô. Cách làm chi tiết, kể cả trường hợp Excel, có trong bài khóa công thức không cho chỉnh sửa khi chia sẻ file.

Mẹo xếp lịch để ít phải sửa

Công thức chỉ báo lỗi sau khi bạn đã gõ vào. Phần việc còn lại là xếp sao cho ít đụng nhau từ đầu, và ở đây vài thói quen có ích hơn mọi công thức.

Chừa khoảng nghỉ giữa hai buổi

Lịch nối tiếp sát nút nhìn thì gọn, nhưng chỉ cần lớp trước kéo dài 10 phút vì còn bài chưa chữa là cả chuỗi phía sau lệch. Thử hình dung thầy Nam dạy ba lớp liền buổi tối, mỗi lớp 60 phút. Nếu mỗi lớp trễ trung bình 8 phút, lớp thứ ba bắt đầu muộn gần 16 phút mà học sinh vẫn tan đúng giờ đón. Chừa 15 phút giữa hai lớp thì cả buổi tối ba lớp chỉ tốn thêm 30 phút, đổi lại phòng có thời gian dọn và giáo viên kịp uống nước. Muốn công thức tự giữ khoảng nghỉ này, cộng thêm 15 vào cột H (=IF(A2="","",G2+D2+15)) và các buổi sát nhau sẽ bị báo trùng. Chọn cách này khi bạn muốn bảng nghiêm khắc hơn thực tế.

Gom lớp theo buổi thay vì rải đều

Rải mỗi ngày một lớp trông công bằng nhưng kéo giáo viên đi lại cả tuần, và phòng học thì trống lúc này, kẹt lúc kia. Gom các lớp vào vài buổi cố định (ví dụ Thứ 3, Thứ 5 và sáng Thứ 7) thì số cặp có thể đụng nhau giảm hẳn, vì bạn chỉ phải cân nhắc trong ba buổi thay vì sáu. Những khung giờ được cả nhà đồng ý (tối các ngày thường, sáng cuối tuần) nên được giữ cho lớp đông nhất và lớp có học sinh thi chuyển cấp. Lớp nhỏ, linh hoạt thì lấp vào các khung còn lại.

Cho lớp online một "phòng" riêng

Lớp học qua mạng không chiếm phòng, nhưng vẫn chiếm giáo viên. Ghi "Online" ở cột F như trên là đủ, đừng gõ tên phòng giả để cho khỏi trống.

Mượn cách tư duy chống trùng của ngành khác

Bài toán "hai người một chỗ, một khung giờ" xuất hiện ở mọi nghề có lịch hẹn. Phòng khám xử lý nó bằng cách đếm số lịch trùng bác sĩ và trùng giờ, cũng là COUNTIFS với điều kiện chồng khoảng thời gian. Nếu muốn đọc cách người ta đặt cột và xử lý lịch hẹn bị hủy, xem quản lý lịch hẹn phòng khám bằng Google Sheets, chống trùng giờ. Phần lớn ý tưởng chuyển sang lớp học được gần như nguyên vẹn.

Khi bảng tính bắt đầu chật

Một bảng lịch như trên chạy ổn cho vài giáo viên và khoảng vài chục buổi mỗi tuần. Nhưng nó có giới hạn thật, và nên biết trước. Nó chỉ canh giáo viên và phòng. Một học sinh đăng ký hai lớp trùng giờ thì bảng không biết. Nó cũng không nói gì về chuyện lớp nào đã học buổi nào, ai vắng, ai còn nợ học phí. Những thứ đó phải sống ở các bảng khác, và khi tách ra, bạn bắt đầu phải nhập một thông tin ở hai nơi.

Nếu đến mức đó, Phần Mềm Quản Lý Dạy Thêm trên Google Sheets gom mọi thứ vào một file. Lịch học được tạo theo từng lớp với thứ trong tuần, giờ bắt đầu, thời lượng, địa điểm và link học online, và phần mềm tự kiểm tra trùng lịch khi khung giờ trong cùng một ngày giao nhau. Cùng file đó có điểm danh (có mặt, vắng, có phép), ghi nhận học phí đầy đủ hoặc một phần, và danh sách công nợ. Nó có giao diện web mở qua đường link, còn dữ liệu nằm trong Google Sheets của chính bạn trên Google Drive, nên cần một tài khoản Google. Phần mềm làm cho giáo viên tư và trung tâm gia sư nhỏ, không dành cho trung tâm lớn nhiều cơ sở.

Giá là 399.000đ, mua một lần dùng trọn đời, không có phí hằng tháng, và có thể dùng thử 7 ngày. Nếu bảng COUNTIFS ở trên đã đủ cho lớp của bạn thì cứ dùng nó. Chỉ khi bạn thấy mình đang ghép lịch, điểm danh và học phí từ ba file khác nhau thì mới đáng cân nhắc gom lại.

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

Vì sao phải đổi giờ ra số phút trước khi dùng COUNTIFS?

Google Sheets lưu giờ dưới dạng phân số của một ngày, ví dụ 19:00 là 0,7916666…. Khi ghép số này vào điều kiện của COUNTIFS bằng dấu &, nó bị cắt bớt chữ số, nên hai lớp nối tiếp có thể bị báo trùng oan hoặc bị bỏ sót. Quy ra số nguyên phút (17:30 thành 1050) thì tránh được lỗi này.

Tại sao điều kiện trùng là lớn hơn 1 chứ không phải lớn hơn 0?

Vì mỗi dòng luôn giao với chính nó, nên COUNTIFS đếm ra tối thiểu là 1. Chỉ khi kết quả lớn hơn 1 mới có một buổi khác chồng giờ với dòng đang xét. Ngoài ra, dùng dấu nhỏ hơn và lớn hơn chặt, không có dấu bằng, để lớp hết 19:00 và lớp bắt đầu 19:00 được tính là nối tiếp, không phải trùng.

Lớp học online có bị báo trùng phòng không?

Không, nếu cột phòng ghi chữ "Online" thì công thức bỏ qua lớp đó khi kiểm tra trùng phòng, nên hai lớp học qua mạng cùng giờ không bị báo oan. Tuy nhiên lớp online vẫn chiếm giáo viên: nếu hai lớp online do cùng một người dạy và giờ giao nhau, công thức kiểm giáo viên vẫn báo "Trùng giáo viên".

Làm sao để công thức tự chừa khoảng nghỉ giữa hai buổi học?

Cộng thêm 15 vào cột phút kết thúc, tức công thức =IF(A2="","",G2+D2+15). Khi đó các buổi sát nhau sẽ bị báo trùng, và bảng nghiêm khắc hơn thực tế. Cách này hợp khi bạn muốn có 15 phút giữa hai lớp để phòng được dọn và giáo viên kịp nghỉ, tránh lớp trước kéo dài làm lệch cả chuỗi phía sau.