Cách Làm Thời Khóa Biểu Trên Excel Không Trùng Lịch: Xem Theo Lớp Và Giáo Viên

Trả lời nhanh: Làm thời khóa biểu trên Excel không trùng lịch cần một sheet phân công (mỗi dòng là một tiết học) và hai sheet hiển thị theo lớp, theo giáo viên. Bạn nhập một lần ở sheet phân công, dùng COUNTIFS để báo trùng và INDEX với MATCH để vẽ ra dạng lưới. Cách này không cần macro.

Xếp thời khóa biểu thủ công thường hỏng ở một chỗ: cô giáo bị xếp hai lớp cùng một tiết mà đến lúc in ra mới thấy. Bài này hướng dẫn dựng bộ ba sheet để lỗi đó hiện ra ngay khi bạn nhập. Mọi con số trong ví dụ là dữ liệu giả định để minh họa.

Thời khóa biểu trên Excel cần những sheet nào?

Ý tưởng cốt lõi là tách nơi nhập và nơi xem. Dữ liệu chỉ nằm ở một chỗ, các bảng hiển thị chỉ đọc lại từ đó.

Sheet	Vai trò	Ai sửa
PhanCong	Mỗi dòng là một tiết học: thứ, tiết, lớp, giáo viên, môn	Người xếp lịch, nhập tay
TKBLop	Chọn một lớp, hiện lưới thứ nhân tiết với tên môn	Chỉ chọn lớp, không sửa công thức
TKBGiaoVien	Chọn một giáo viên, hiện lưới thứ nhân tiết với tên lớp	Chỉ chọn giáo viên, không sửa công thức

Tên sheet viết liền, không dấu cách, để công thức không cần đặt trong dấu nháy đơn và ít lỗi hơn.

Sheet PhanCong nhập thế nào để báo được trùng lịch?

Dòng 1 là tiêu đề, dữ liệu bắt đầu từ dòng 2 và công thức kéo xuống đến dòng 100.

Cột	Nội dung	Cách điền
A	Thứ	Nhập số 2 đến 7 (2 là thứ Hai)
B	Tiết	Nhập số 1 đến 5
C	Lớp	Ví dụ 6A, 6B
D	Giáo viên	Ví dụ Lan, Hùng
E	Môn	Ví dụ Toán, Văn
F	Khóa tra theo lớp	Công thức, không nhập tay
G	Khóa tra theo giáo viên	Công thức, không nhập tay
H	Kiểm tra trùng	Công thức, chỉ hiện kết quả

Ba cột công thức cho dòng 2:

• F2: =IF($A2="","",$A2&"|"&$B2&"|"&$C2). Ví dụ thứ 2, tiết 1, lớp 6A cho khóa 2|1|6A.
• G2: =IF($A2="","",$A2&"|"&$B2&"|"&$D2). Ví dụ thứ 2, tiết 1, giáo viên Lan cho khóa 2|1|Lan.
• H2: =IF($A2="","",IF(COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$D$2:$D$100,$D2)>1,"Trùng giáo viên",IF(COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2)>1,"Trùng lớp","OK")))

Công thức H2 đếm số dòng có cùng thứ, cùng tiết và cùng giáo viên. Nếu có hơn một dòng thì một giáo viên đang đứng ở hai nơi. Vế thứ hai làm tương tự với lớp, vì một lớp cũng không thể học hai môn cùng lúc. Bọc ngoài bằng IF($A2="","",…) để các dòng chưa nhập không hiện gì.

Dữ liệu giả định để thử:

Thứ	Tiết	Lớp	Giáo viên	Môn	Kiểm tra
2	1	6A	Lan	Toán	Trùng giáo viên
2	2	6A	Hùng	Văn	OK
2	1	6B	Hùng	Văn	OK
2	1	6C	Lan	Toán	Trùng giáo viên

Dòng cuối xếp cô Lan dạy lớp 6C vào thứ 2 tiết 1, trong khi dòng đầu đã xếp cô dạy lớp 6A đúng tiết đó. Cột kiểm tra hiện "Trùng giáo viên" ở cả hai dòng liên quan, nên bạn biết phải sửa một trong hai.

Làm hai sheet hiển thị dạng lưới như thế nào?

Sheet TKBLop. Ô B1 là nơi chọn lớp: vào Data, Data Validation, chọn List và trỏ về danh sách lớp bạn gõ sẵn ở một vùng trống. Hàng 2 từ B2 đến G2 điền số 2 đến 7. Cột A từ A3 đến A7 điền số 1 đến 5 (tiết). Để hiển thị "Thứ 2" thay vì số 2, đặt định dạng số tùy chỉnh "Thứ "0 cho hàng 2.

Ô B3, kéo sang phải đến G3 rồi kéo xuống đến hàng 7:

=IFERROR(INDEX(PhanCong!$E$2:$E$100,MATCH(B$2&"|"&$A3&"|"&$B$1,PhanCong!$F$2:$F$100,0)),"")

Công thức ghép thứ ở hàng 2, tiết ở cột A và lớp đang chọn thành khóa, rồi tìm khóa đó trong cột F của PhanCong. Tìm thấy thì lấy tên môn ở cột E, không thấy thì để trống. Dấu $ đặt đúng chỗ để kéo công thức không bị lệch.

Sheet TKBGiaoVien. Làm giống hệt, chỉ khác hai điểm: B1 chọn tên giáo viên, và công thức tra cột G, lấy ra tên lớp ở cột C:

=IFERROR(INDEX(PhanCong!$C$2:$C$100,MATCH(B$2&"|"&$A3&"|"&$B$1,PhanCong!$G$2:$G$100,0)),"")

Với dữ liệu giả định ở trên, chọn lớp 6A trong TKBLop sẽ thấy Toán ở thứ 2 tiết 1 và Văn ở thứ 2 tiết 2. Chọn giáo viên Hùng trong TKBGiaoVien sẽ thấy 6B ở thứ 2 tiết 1 và 6A ở thứ 2 tiết 2. Hai bảng khớp nhau vì cùng đọc một nguồn.

Lưu ý về dấu phân cách: nếu máy cài vùng Việt Nam hoặc châu Âu, Excel có thể yêu cầu dấu chấm phẩy giữa các đối số, tức là viết MATCH(…;…;0) thay vì dấu phẩy. Danh sách chọn gõ tay cũng phân cách bằng dấu chấm phẩy trong trường hợp đó.

Tô đỏ ô trùng và những lỗi hay gặp

Chọn vùng A2:H100 của PhanCong, vào Home, Conditional Formatting, New Rule, chọn công thức và nhập =AND($H2<>"",$H2<>"OK"), đặt nền đỏ nhạt. Từ đó mọi dòng xung đột tự tô đỏ, bạn không cần dò từng dòng.

• Tên giáo viên viết khác nhau. "Lan" và "Lan " (thừa dấu cách) bị coi là hai người, nên không báo trùng. Dùng danh sách chọn cho cột D để mọi dòng cùng một cách viết.
• Thứ hoặc tiết nhập thành chữ. Số 2 và chữ "2" khác nhau với MATCH. Đặt Data Validation cho cột A và B chỉ nhận số nguyên trong khoảng cho phép.
• Quên kéo công thức xuống. Dòng nào thiếu F, G, H thì không tra được và không báo trùng. Kéo công thức đến dòng 100 ngay từ đầu.
• Một lớp học hai môn một tiết (chia nhóm). Khóa tra theo lớp sẽ trùng và cột H báo "Trùng lớp". Nếu trường bạn có chia nhóm, thêm cột Nhóm vào khóa.
• In ra bị tràn trang. Đặt vùng in là A1:G7 của từng sheet hiển thị, chọn hướng ngang và Fit to 1 page wide.

Bộ ba sheet này chỉ kiểm tra xung đột giáo viên và lớp. Nó không biết phòng học, không biết giáo viên nào nghỉ ngày nào, và không tự xếp lịch thay bạn. Muốn thêm phòng học, thêm một cột Phòng và một cột kiểm tra trùng phòng theo cùng kiểu COUNTIFS.

Khi nào nên dùng mẫu có sẵn hoặc phần mềm?

Nếu không muốn dựng từ đầu, trang Template Thời Khóa Biểu Giáo Viên & Học Sinh có mẫu dựng sẵn cho nhiều lớp, nhiều giáo viên và nhiều phòng học, kèm cảnh báo khi trùng lịch.

Nếu bạn mở lớp dạy thêm và cần quản lý luôn học sinh, điểm danh, học phí cùng lịch học, hãy xem phần mềm bên dưới.

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

Làm sao để Excel báo khi một giáo viên bị xếp hai lớp cùng tiết?

Thêm một cột kiểm tra trong sheet phân công, dùng COUNTIFS đếm số dòng có cùng thứ, cùng tiết và cùng giáo viên. Nếu kết quả lớn hơn 1 thì cột hiện chữ Trùng giáo viên, kết hợp định dạng có điều kiện để tô đỏ cả dòng.

Thời khóa biểu theo lớp và theo giáo viên có phải nhập hai lần không?

Không. Bạn chỉ nhập một lần ở sheet phân công. Hai sheet hiển thị dùng INDEX và MATCH để lấy dữ liệu ra dạng lưới, nên khi sửa một dòng phân công thì cả hai bảng đổi theo.

Cách này dùng được trên Google Sheets không?

Được. COUNTIFS, INDEX, MATCH và IFERROR hoạt động giống nhau trong Google Sheets. Nếu công thức báo lỗi, hãy thử đổi dấu phẩy giữa các đối số thành dấu chấm phẩy tùy cài đặt vùng của bạn.

Có cần macro hay Apps Script để xếp thời khóa biểu không?

Không cần. Cách trong bài chỉ dùng công thức và định dạng có điều kiện. Excel không tự động xếp lịch hộ bạn, nó chỉ giúp nhập một lần, hiển thị theo lớp hoặc giáo viên và báo ngay khi có xung đột.