Lớp học & trung tâm · 16 phút đọc
Xếp thời khoá biểu cho trung tâm là ghép từng buổi học của mỗi lớp vào một ô (thứ × ca × phòng) sao cho không phòng nào có hai lớp cùng lúc, không giáo viên nào dạy hai nơi, phòng đủ chỗ và giáo viên thật sự rảnh. Cách chắc nhất trên Google Sheets là ghi lịch dạng danh sách, mỗi buổi một dòng, rồi dùng COUNTIFS để báo trùng và FILTER để dựng lưới tuần theo phòng, theo giáo viên. Bài này đi qua dữ liệu đầu vào, 4 công thức kiểm tra, cách xem theo phòng và theo giáo viên, cách tìm phòng trống cho buổi bù và cách gửi lịch cho học viên, mỗi công thức có ví dụ số để thử lại.
Trước khi kéo lớp nào vào ô nào, bạn cần bốn bảng nhỏ. Thiếu bảng nào thì sẽ có một kiểu lỗi không bắt được: không có sức chứa phòng thì không biết lớp 26 em bị nhét vào phòng 24 chỗ, không có giờ rảnh thì xếp giáo viên vào buổi họ đang dạy trường khác.
| Tab | Cột | Ví dụ một dòng |
|---|---|---|
| Ca | A: Ca · B: Bắt đầu · C: Kết thúc | Ca 1 · 17:30 · 19:00 |
| Phong | A: Mã phòng · B: Sức chứa | P01 · 20 |
| Ranh | A: Giáo viên · B: Thứ · C: Ca | Thầy Quân · T2 · Ca 1 |
| Lich | A: Mã lớp · B: Thứ · C: Ca · D: Phòng · E: Giáo viên · F: Sĩ số | TOAN9-A · T3 · Ca 2 · P01 · Thầy Bình · 18 |
Chia giờ theo ca thay vì gõ giờ tự do. Trung tâm dạy buổi tối thường có 2 ca trong tuần (Ca 1 17:30–19:00, Ca 2 19:15–20:45) và 3 ca cuối tuần (Ca 3 8:00–9:30, Ca 4 9:45–11:15, Ca 5 14:00–15:30). Lớp nào cũng bắt đầu đúng đầu ca thì việc kiểm trùng chỉ còn là so ba chữ: thứ, ca, phòng. Mười lăm phút giữa hai ca để học sinh ra vào và giáo viên đổi phòng.
Thứ ghi bằng chữ cố định T2, T3… T7, CN, và dùng danh sách thả xuống cho các cột Thứ, Ca, Phòng, Giáo viên. Chỉ cần một ô gõ "t3" hay "Thứ 3" là COUNTIFS coi như một ngày khác và bỏ sót chỗ trùng.
Tab Lich là bảng gốc. Mỗi buổi cố định trong tuần là một dòng: lớp học thứ 3 và thứ 5 thì có hai dòng. Đừng xếp lịch thẳng vào một lưới đẹp rồi mới kiểm, vì lưới không đếm được; lưới chỉ nên là bảng xem, dựng ra từ danh sách bằng công thức.
Ý tưởng rất đơn giản: với mỗi dòng, đếm xem có bao nhiêu dòng cùng thứ, cùng ca, cùng phòng. Đếm ra 1 là chính nó; ra 2 trở lên là có lớp khác chen vào. Cú pháp COUNTIFS nhiều điều kiện có ở bài hàm SUMIFS, COUNTIFS; ở đây chỉ cần dùng.
Cột G — trùng phòng. Gõ vào G2 rồi kéo xuống tới dòng 60:
=IF(A2="";"";IF(COUNTIFS($B$2:$B$60;B2;$C$2:$C$60;C2;$D$2:$D$60;D2)>1;"Trùng phòng";""))Thử lại: dòng 4 là TOAN9-A, T3, Ca 2, P01. Dòng 6 IELTS-B cũng T3, Ca 2, P01. COUNTIFS đếm được 2 dòng nên cả G4 và G6 hiện Trùng phòng. Dòng 5 (TOAN9-A, T5, Ca 2, P01) chỉ có chính nó, đếm ra 1, để trống.
Cột H — trùng giáo viên. Thay cột phòng bằng cột giáo viên:
=IF(A2="";"";IF(COUNTIFS($B$2:$B$60;B2;$C$2:$C$60;C2;$E$2:$E$60;E2)>1;"Trùng GV";""))Thử lại: Thầy Bình có TOAN9-A ở P01 (dòng 4) và ANH6-A ở P02 (dòng 7), cùng T3, Ca 2. Hai phòng khác nhau nên cột G không báo, nhưng cột H đếm ra 2: H4 và H7 hiện Trùng GV.
Cột I — quá sức chứa. Lấy sức chứa của phòng bằng XLOOKUP rồi so với sĩ số:
=IF(A2="";"";IFNA(IF(F2>XLOOKUP(D2;Phong!$A$2:$A$30;Phong!$B$2:$B$30);"Quá "&XLOOKUP(D2;Phong!$A$2:$A$30;Phong!$B$2:$B$30)&" chỗ";"");"Sai mã phòng"))Thử lại: HOA9-A 26 em ở P03. Tab Phong ghi P03 24 chỗ. 26 > 24 nên I9 hiện Quá 24 chỗ. TOAN9-A 18 em ở P01 20 chỗ thì để trống.
Cột J — giáo viên không rảnh. Đếm trong tab Ranh xem giáo viên đó có đăng ký thứ và ca này không:
=IF(A2="";"";IF(COUNTIFS(Ranh!$A$2:$A$200;E2;Ranh!$B$2:$B$200;B2;Ranh!$C$2:$C$200;C2)=0;"GV không rảnh";""))Thử lại: tab Ranh của Thầy Quân chỉ có hai dòng: T2 Ca 1 và T4 Ca 1. LY9-A xếp thầy vào T6 Ca 1, COUNTIFS đếm ra 0, J8 hiện GV không rảnh.
Tô đỏ cả dòng bị trùng
Chọn A2:F60 › Định dạng có điều kiện › Công thức tuỳ chỉnh: =COUNTIFS($B$2:$B$60;$B2;$C$2:$C$60;$C2;$D$2:$D$60;$D2)>1 và chọn nền đỏ. Thêm một quy tắc nữa với cột $E cho trùng giáo viên. Dấu $ trước chữ cột, không có trước số dòng, để quy tắc áp đúng cho từng dòng.
Khi các lớp không bắt đầu đúng đầu ca
Nếu trung tâm có lớp 18:00–19:30 và lớp 19:00–20:30 thì so "cùng ca" không đủ. Thay cột Ca bằng hai cột Bắt đầu (C) và Kết thúc (D), phòng sang cột E, và đếm các buổi giao nhau: =COUNTIFS($B$2:$B$60;B2;$E$2:$E$60;E2;$C$2:$C$60;"<"&D2;$D$2:$D$60;">"&C2)>1. Hai buổi giao nhau khi buổi này bắt đầu trước lúc buổi kia kết thúc và ngược lại. Thử: 18:00–19:30 với 19:00–20:30 cùng phòng ra 2 (trùng); 18:00–19:30 với 19:30–21:00 ra 1 (nối tiếp, không trùng).
Danh sách dài để kiểm, nhưng giáo vụ, giáo viên và phụ huynh cần nhìn lưới: hàng là ca, cột là thứ. Lưới này dựng bằng công thức nên sửa tab Lich là lưới tự đổi.
Lưới theo phòng. Tạo tab TKB_Phong. Ô B1 chọn mã phòng (danh sách thả xuống lấy từ Phong!A2:A30). B3:H3 là T2, T3, T4, T5, T6, T7, CN; A4:A8 là Ca 1 tới Ca 5. Gõ vào B4 rồi chép ra cả vùng B4:H8:
=IFERROR(TEXTJOIN(" + ";TRUE;FILTER(Lich!$A$2:$A$60;Lich!$B$2:$B$60=B$3;Lich!$C$2:$C$60=$A4;Lich!$D$2:$D$60=$B$1));"")Thử lại: B1 = P01. Ô C5 (cột T3, dòng Ca 2) ra TOAN9-A + IELTS-B — dấu + trong ô là có trùng. Ô B4 (T2, Ca 1) ra TOAN8-A. Thêm quy tắc tô màu với công thức =ISNUMBER(SEARCH("+";B4)) để ô có hai lớp nổi đỏ, đúng như hình bìa bài này.
Lưới theo giáo viên. Chép tab thành TKB_GV, B1 chọn tên giáo viên, đổi điều kiện cuối sang cột E và kèm phòng vào tên lớp để giáo viên biết đi đâu:
=IFERROR(TEXTJOIN(" + ";TRUE;FILTER(Lich!$A$2:$A$60&" ("&Lich!$D$2:$D$60&")";Lich!$B$2:$B$60=B$3;Lich!$C$2:$C$60=$A4;Lich!$E$2:$E$60=$B$1));"")Thử lại: B1 = Thầy Bình. Ô C5 (T3, Ca 2) ra TOAN9-A (P01) + ANH6-A (P02); ô E5 (T5, Ca 2) ra TOAN9-A (P01).
Danh sách theo giáo viên bằng QUERY. Khi cần một bảng gọn để in hay gửi, hàm QUERY cho ra danh sách đã sắp xếp. Vì "CN" đứng trước "T2" theo thứ tự chữ cái, thêm cột phụ K2 =IF(B2="";"";MATCH(B2;{"T2";"T3";"T4";"T5";"T6";"T7";"CN"};0)) để có số thứ tự ngày, rồi:
=QUERY(Lich!$A$1:$K$60;"select B, C, A, D, F where E = '"&B1&"' order by K, C";1)Thử lại: B1 = Thầy Bình ra 3 dòng: T3 Ca 2 TOAN9-A P01 18; T3 Ca 2 ANH6-A P02 12; T5 Ca 2 TOAN9-A P01 18. Hai dòng đầu cùng giờ là dấu hiệu phải sửa.
Đếm tải để xếp cho công bằng. Số buổi mỗi giáo viên trong tuần: =COUNTIFS(Lich!$E$2:$E$60;A2). Tỷ lệ dùng phòng: =COUNTIFS(Lich!$D$2:$D$60;A2)/16, với 16 là số ô mở cửa mỗi tuần (T2–T6 hai ca = 10, T7 và CN ba ca = 6). Thử lại: P01 có 6 dòng trong tab Lich (dòng 2, 3, 4, 5, 6, 8), 6/16 = 37,5%. Phòng nào dưới 30% là ứng viên để gộp lớp hoặc dành cho buổi bù.
Buổi bù là buổi có ngày cụ thể, không lặp hằng tuần. Câu hỏi lúc nào cũng là: ngày đó, ca đó, phòng nào còn trống và đủ chỗ? Tạo tab Bu với B1 là ngày bù, B3 là ca, B4 là sĩ số lớp, B5 là giáo viên.
Đổi ngày ra thứ để so với tab Lich (B2):
=CHOOSE(WEEKDAY(B1);"CN";"T2";"T3";"T4";"T5";"T6";"T7")Danh sách phòng trống đủ chỗ (B7):
=FILTER(Phong!$A$2:$A$30;Phong!$A$2:$A$30<>"";COUNTIFS(Lich!$D$2:$D$60;Phong!$A$2:$A$30;Lich!$B$2:$B$60;$B$2;Lich!$C$2:$C$60;$B$3)=0;Phong!$B$2:$B$30>=$B$4)Thử lại: TOAN9-A nghỉ buổi thứ 3 ngày 08/09/2026, bù vào thứ Bảy 19/09/2026, Ca 4, 18 em. B2 ra T7. Trong tab Lich không có lớp nào T7 Ca 4, nên cả ba phòng đều trống; P02 chỉ 15 chỗ bị loại. Kết quả: P01, P03.
Giáo viên có trống không (B6):
=IF(COUNTIFS(Lich!$E$2:$E$60;$B$5;Lich!$B$2:$B$60;$B$2;Lich!$C$2:$C$60;$B$3)>0;"Đã có lớp";"Trống ca này")Thử lại: B5 = Thầy Bình, T7 Ca 4: thầy không có dòng nào, ra Trống ca này. Đổi ngày bù sang thứ Năm 17/09/2026 Ca 2 thì ra Đã có lớp — chính là buổi TOAN9-A cố định của thầy.
Buổi bù khác cũng chiếm phòng
Công thức trên chỉ nhìn lịch cố định. Nếu tuần đó đã có buổi bù hoặc học thử khác, ghi chúng vào một tab PhatSinh (Ngày, Ca, Phòng, Lớp, Giáo viên) và thêm vào FILTER một điều kiện nữa: COUNTIFS(PhatSinh!$C$2:$C$100;Phong!$A$2:$A$30;PhatSinh!$A$2:$A$100;$B$1;PhatSinh!$B$2:$B$100;$B$3)=0. Buổi bị huỷ thì ngược lại: phòng được trả, nhưng công thức không biết, nên bạn tự xem.
Phụ huynh không cần cả lưới của trung tâm, chỉ cần lịch của lớp con mình. Ở tab danh sách lớp (cột A là mã lớp), một công thức gom các buổi thành một câu:
=A2&": "&TEXTJOIN(", ";TRUE;FILTER(Lich!$B$2:$B$60&" "&Lich!$C$2:$C$60&" phòng "&Lich!$D$2:$D$60;Lich!$A$2:$A$60=A2))Thử lại: A2 = TOAN9-A ra TOAN9-A: T3 Ca 2 phòng P01, T5 Ca 2 phòng P01. Thêm một dòng giờ của các ca ở cuối tin nhắn (Ca 2: 19:15–20:45) là phụ huynh đọc được ngay.
Các công thức trên đủ cho một trung tâm 1 cơ sở, vài chục lớp. Khi đã có điểm danh, học phí, lương giáo viên ở các file khác nhau, mỗi lần đổi lịch là phải sửa ba bốn chỗ. Mẫu Quản lý lớp học PRO gom lịch vào chung một file với những phần đó. Phần liên quan tới thời khoá biểu, theo đúng mô tả của mẫu:
Mẫu không tự xếp lịch thay bạn: bạn vẫn chọn lớp nào vào phòng nào, file chỉ báo chỗ sai. Web App phụ huynh dùng để tra chuyên cần, điểm và học phí, không phải để gửi thời khoá biểu, nên phần gửi lịch vẫn làm như mục trên. Mở bằng Excel thì các tab và công thức vẫn chạy, nhưng mở kỳ tự động, buổi bù có gợi ý phòng và Web App chỉ có trên Google Sheets.
Hướng dẫn cài đặt và dùng mẫu Quản lý lớp học PRO trên Google Sheets
Xem trên YouTube ↗| Hiện tượng | Nguyên nhân | Cách sửa |
|---|---|---|
| Cả cột trống cũng báo Trùng phòng | COUNTIFS đếm các ô trống bằng nhau | Bọc IF(A2="";"";…) |
| Rõ ràng trùng mà không báo | "T3" và "t3", "P01 " có dấu cách thừa | Danh sách thả xuống cho Thứ, Ca, Phòng; TRIM khi dán dữ liệu |
| Lưới theo phòng trống trơn | Tiêu đề B3:H3 hoặc A4:A8 viết khác tab Lich ("Thứ 3" so với "T3") | Gõ tiêu đề giống hệt giá trị trong tab Lich |
| QUERY báo lỗi phân tích | Thiếu dấu nháy đơn quanh tên giáo viên, hoặc tên có dấu nháy | Viết where E = '"&B1&"' |
| Kéo công thức xuống thì vùng trượt | Thiếu dấu $ ở vùng $B$2:$B$60 | Khoá vùng bằng $, chỉ để ô so sánh (B2) không khoá |
Một bẫy nữa không phải công thức: giáo viên báo giờ rảnh bằng tin nhắn rồi đổi ý. Hãy coi tab Ranh là thứ giáo viên xác nhận, và chạy lại cột J mỗi lần họ báo đổi.
Không có hàm nào tự xếp. Xếp lịch tự động là bài toán tối ưu, cần phần mềm chuyên dụng hoặc viết script riêng. Với trung tâm vài chục lớp, cách thực tế là bạn tự xếp theo thứ tự lớp khó trước, còn bảng tính lo việc báo trùng phòng, trùng giáo viên, quá sức chứa và giáo viên không rảnh ngay khi bạn gõ.
Ghi lịch mỗi buổi một dòng (Thứ, Ca, Phòng) rồi dùng =COUNTIFS($B$2:$B$60;B2;$C$2:$C$60;C2;$D$2:$D$60;D2)>1. Kết quả TRUE nghĩa là có ít nhất một lớp khác cùng thứ, cùng ca, cùng phòng. Đổi cột phòng thành cột giáo viên để bắt trùng giáo viên.
Dùng hai cột giờ bắt đầu và kết thúc, đếm các buổi giao nhau: cùng thứ, cùng phòng, bắt đầu trước giờ kết thúc của buổi đang xét và kết thúc sau giờ bắt đầu của nó. Buổi 18:00–19:30 và 19:30–21:00 nối tiếp nhau không bị tính là trùng.
Cả hai, nhưng danh sách là gốc. Danh sách (mỗi buổi một dòng) đếm, lọc, kiểm trùng được; lưới chỉ để xem và gửi, dựng từ danh sách bằng FILTER và TEXTJOIN. Xếp thẳng vào lưới thì không có công thức nào bắt được hai lớp gõ vào hai ô khác nhau nhưng cùng một giáo viên.
Đổi ngày bù ra thứ bằng WEEKDAY, rồi FILTER danh sách phòng với điều kiện COUNTIFS số lớp cố định ở thứ và ca đó bằng 0 và sức chứa không nhỏ hơn sĩ số. Kiểm thêm giáo viên có trống ca đó không, và các buổi bù khác đã xếp trong tuần.
Có. Tab Thời khoá biểu hiện lưới theo phòng (khung 30 phút) và lịch tuần của giáo viên, ô trùng báo đỏ; tab Lịch học và Nhắc việc báo trùng phòng, trùng giáo viên. Mẫu không tự xếp lịch thay bạn. Gợi ý phòng trống cho buổi bù chạy bằng Apps Script nên chỉ có trên Google Sheets.
Cách quản lý lớp dạy thêm bằng Google Sheets: bảng điểm danh, 4 cách tính học phí, nghỉ có phép, học bù, phiếu có mã QR, công nợ và lương giáo viên.
Cách quản lý học viên trung tâm tiếng Anh nhỏ: hồ sơ, test đầu vào xếp lớp, khoá và gia hạn, báo sắp hết khoá, nghỉ nhiều, tỷ lệ giữ chân; có công thức.
Cách làm báo cáo học tập gửi phụ huynh cho lớp dạy thêm, trung tâm: nội dung cần có, tần suất, mẫu nhận xét cụ thể, công thức TEXTJOIN ghép tin Zalo.
Cách tính điểm trung bình môn học kỳ và cả năm THCS, THPT theo Thông tư 22: hệ số 1-2-3, làm tròn, xếp loại Tốt/Khá/Đạt, công thức Google Sheets có ví dụ.
Xem thêm mẹo và video hướng dẫn trên kênh YouTube HiTaVa, hoặc đọc các bài khác trong chủ đề Lớp học & trung tâm.
Cần hỏi thêm?Zalo 0935077673 (Hải Vân)
