Về cơ bản công việc này phải thực hiện thủ công thôi, sau đó dùng Excel để kiểm tra việc phân công có lặp lại cặp, lặp lại phòng, và còn nữa là 2gv cùng trường có cùng 1 cặp hay không ? (tôi có làm công việc này rồi)
Function Kt_Coi_1_luc_nhieu_phong(dayKiemTra As Variant, soLuongPhongThi As Integer) As Integer
' tra ve ma gv cooi thi nhieu phong
'xep giam dan day cac phong thu duoc
Dim temp, i, j As Integer
Dim dayKiemTraTemp As Variant
dayKiemTraTemp = dayKiemTra
For i = 1 To soLuongPhongThi * 2
For j = i + 1 To (soLuongPhongThi * 2)
If dayKiemTraTemp(j, 1) < dayKiemTraTemp(i, 1) Then
temp = dayKiemTraTemp(j, 1)
dayKiemTraTemp(j, 1) = dayKiemTraTemp(i, 1)
dayKiemTraTemp(i, 1) = temp
Else
End If
Next j
Next i
' quet day neu co trung thi exit va bao ket qua
For i = 1 To soLuongPhongThi * 2
If i > 1 Then
If dayKiemTraTemp(i, 1) = dayKiemTraTemp(i - 1, 1) Then
Kt_Coi_1_luc_nhieu_phong = dayKiemTraTemp(i, 1)
Exit Function
Else
End If
Else
End If
Next i
Kt_Coi_1_luc_nhieu_phong = 0
End Function
Function KTra_trung_cap2(maGV1 As Variant, ma_truong1 As Variant, mon1 As Variant, maGV2 As Variant, ma_truong2 As Variant, mon2 As Variant, maGV3 As Variant, ma_truong3 As Variant, mon3 As Variant, maPhongDauTien As Integer, soLuongPhong As Integer) As Variant
' gia thuat:
' quet cac cot magv rut ra da cac gv co coi thi luu vao mang 1 phu
'dong thoi kiem tra qua 6 lan hay khong
'rut ra mang gvcocoithi
' quet mang gvcocoithi nay , moi phan tu
' quet tung buoi thi lay ra cap GV luu vo mang 2 capGV voiGV thi i
' kiem tra mang 2 co trung 2 phan tu hay khong. Do la ket qua tra ve cua ham
Dim aMaGV, aMaTruong, aMon1, aMon2, aMon3, aMon4, aMon5, aMon6 As Variant
aMon1 = mon1.Value2 ' bat dau tu 1
aMon2 = mon2.Value2
aMon3 = mon3.Value2
' kiem tra hop le du llieu nhap
' so phong thi khop voi day o quet vao hay khong
Dim aGvGhepCap(7) As Variant ' bat dau tu 0
If (mon1.Count / 4) <> soLuongPhong Or maGV1.Count <> soLuongPhong * 2 Or ma_truong1.Count <> soLuongPhong * 2 Then
MsgBox " Day o ban chon khong hop le. "
KTra_trung_cap2 = " Day o ban chon khong hop le. "
Exit Function
Else
End If
If (mon2.Count / 4) <> soLuongPhong Or maGV2.Count <> soLuongPhong * 2 Or ma_truong2.Count <> soLuongPhong * 2 Then
MsgBox " Day o ban chon khong hop le. "
KTra_trung_cap2 = " Day o ban chon khong hop le. "
Exit Function
Else
End If
If (mon3.Count / 4) <> soLuongPhong Or maGV3.Count <> soLuongPhong * 2 Or ma_truong3.Count <> soLuongPhong * 2 Then
MsgBox " Day o ban chon khong khop. "
KTra_trung_cap2 = " Day o ban chon khong hop le. "
Exit Function
Else
End If
Dim i, j, k As Integer
Dim soDongQuet As Integer
soDongQuet = mon1.Count / 2
' Buoc 1 : quet cac cot magv rut ra da cac gv co coi thi luu vao mang 1 phu
'dong thoi kiem tra qua 6 lan hay khong
Dim aGVCoCoiThi(150), aTempGVCoCoiThi(901) As Integer
Dim aMaTruongGVCoCoiThi(150), aTempMaTruongGVCoCoiThi(901) As Variant
Dim aMonj As Variant 'bat dau tu 1
k = 1
For i = 1 To 3
Select Case i
Case 1
aMaGV = maGV1.Value2
aMaTruong = ma_truong1.Value2
Case 2
aMaGV = maGV2.Value2
aMaTruong = ma_truong2.Value2
Case 3
aMaGV = maGV3.Value2
aMaTruong = ma_truong3.Value2
Case Else
' thong bao
'KTra_trung_cap = 2 ' thoat ly do khac
'Exit Function
End Select
' kiem tra coi 2 phong trong 1 buoi thi tuc co trung gv trong 1 cot
Dim t As Integer
t = Kt_Coi_1_luc_nhieu_phong(aMaGV, soLuongPhong)
If t <> 0 Then
'MsgBox "Loi : GV " & t & " Coi thi mot luc nhieu phong "
KTra_trung_cap2 = "Loi : GV " & t & " Coi thi mot luc nhieu phong "
Exit Function
Else
End If
'queet aMaGV cho ket qua vao aTempGVCoCoiThi
For j = 1 To maGV1.Count
aTempGVCoCoiThi(k) = aMaGV(j, 1)
aTempMaTruongGVCoCoiThi(k) = aMaTruong(j, 1)
k = k + 1
Next j
Next i
'tu mang aTempGVCoCoiThi bo bot phan tu trung ra mang aGVCoCoiThi
'dong thoi kien tra gv coi thi qua 5 buoi
' sort theo thu tu tang dan truoc
Dim temp, dem As Integer
Dim tempMaTruong As Variant
For i = 1 To 900
For j = i + 1 To 899
If aTempGVCoCoiThi(j) > aTempGVCoCoiThi(i) Then
temp = aTempGVCoCoiThi(j)
aTempGVCoCoiThi(j) = aTempGVCoCoiThi(i)
aTempGVCoCoiThi(i) = temp
tempMaTruong = aTempMaTruongGVCoCoiThi(j)
aTempMaTruongGVCoCoiThi(j) = aTempMaTruongGVCoCoiThi(i)
aTempMaTruongGVCoCoiThi(i) = tempMaTruong
Else
End If
Next j
Next i
'tao mang aGVCoCoiThi dong thoi kiem tr coi qua 6 buoi tuc la phan cong 2 phong 1 luc
j = 1
For i = 1 To 900
' debug i=246
If i = 246 Then
k = 1
Else
End If
If aTempGVCoCoiThi(i) <> 0 Then
' neu trung thi bo bot dem tang 1
' else thi lay vao aGVcocoithi
If i > 1 Then
If aTempGVCoCoiThi(i) = aTempGVCoCoiThi(i - 1) Then
dem = dem + 1
'If dem = 6 Then
' MsgBox "Phan cong GV " & aTempGVCoCoiThi(i) & "qua 6 lan"
' KTra_trung_cap = 4
' Exit Function
'Else
'End If
Else
aGVCoCoiThi(j) = aTempGVCoCoiThi(i)
aMaTruongGVCoCoiThi(j) = aTempMaTruongGVCoCoiThi(i)
j = j + 1
dem = 0
End If
Else
aGVCoCoiThi(j) = aTempGVCoCoiThi(i)
aMaTruongGVCoCoiThi(j) = aTempMaTruongGVCoCoiThi(i)
j = j + 1
End If
Else
End If
Next i
Dim soGVCoiThi As Integer
soGVCoiThi = 0
' dem so gv co coi thi
For i = 1 To 600
If IsEmpty(aGVCoCoiThi(i)) Then
Exit For
End If
soGVCoiThi = soGVCoiThi + 1
Next i
'dang lam
Dim aMaGVCoiCap(7) As Integer
Dim aMaTruongGVCoiCap(7) As Variant
Dim aMonCoiCap(7) As Integer
'Dim aMonCoPhong(7) As Integer
Dim chiSoHienHanhGVCap As Integer ' la chi so hien hanh cua con tro trong hai mang tren danh quet
chiSoHienHanhGVCap = 1 ' het mot gv reset mot lan
' quet tren danh sach gv coi thi
For i = 1 To soGVCoiThi
For j = 1 To 3 ' quet tung cot gv de tim gv thu i
' tim xem i la gt 1 hay gt 2
' chuyen j tuong ung aMon
' Dim aMonj As Variant 'bat dau tu 1
Select Case j
Case 1
aMonj = aMon1
aMaGV = maGV1.Value2
aMaTruong = ma_truong1.Value2
Case 2
aMonj = aMon2
aMaGV = maGV2.Value2
aMaTruong = ma_truong2.Value2
Case 3
aMonj = aMon3
aMaGV = maGV3.Value2
aMaTruong = ma_truong3.Value2
' Case Else
' thong bao
' KTra_trung_cap = 2 ' thoat ly do khac
' Exit Function
End Select
' trong tung mon quet tu tren xuong
Dim m As Integer ' m dung de debug
For k = 1 To soLuongPhong * 2 ' quet tung cot gv trong tung buoi thi de tim gv thu i
If aGVCoCoiThi(i) = aMaGV(k, 1) Then
' lay ma phong coi thi va vi tri coi thi la gt 1 hay 2
Dim giamThiGVThuk As Integer
If aMonj(k, 1) <> "" Then
giamThiGVThuk = 1
Else
giamThiGVThuk = 2
End If
Dim phongCoiThiCuaGtThuk As Integer
phongCoiThiCuaGtThuk = aMonj(k, giamThiGVThuk)
'___________________________
' den day gv coi thi dang xet la gv thu k chinh la gv thu i
' phong coi thi la phongCoiThiCuaGtThuk
' va la giam thi giamThiGVThuk
'______________________________
' vong lap k xet tung dong tu tren xuong de lay ra GV di cap voi gv thu k
'neu k la gt 1 thi quet cot gt 2
Dim cotQuet As Integer
If giamThiGVThuk = 1 Then
cotQuet = 2
Else
cotQuet = 1
End If
' sau do quet lai mon hien hanh de tim gv cap aGvGhepCap(n)= chi so k
'quet vao mon co cot la cotQuet va noi dung tim la phongcoi thi cua gv thu k
Dim n As Integer
For n = 1 To soLuongPhong * 2
If aMonj(n, cotQuet) = phongCoiThiCuaGtThuk Then
' lay ma gv va ma truong cua gv thu n vao luu vao mang gv cap
aGvGhepCap(chiSoHienHanhGVCap) = aMaGV(n, 1)
aMaTruongGVCoiCap(chiSoHienHanhGVCap) = aMaTruong(n, 1)
aMonCoiCap(chiSoHienHanhGVCap) = j
chiSoHienHanhGVCap = chiSoHienHanhGVCap + 1
Else
End If
Next n
m = 1
Else
End If
Next k
Next j
' kiem tra xem co trung cap , trung truong hay khong
'quet mang aGvGhepCap xem co ghep lai cap cu khong tuc la co 2 phan tu giong nhau khong
Dim p, q As Integer
For p = 1 To 3
For q = p + 1 To 3
If aGvGhepCap(q) = aGvGhepCap(p) And aGvGhepCap(q) <> 0 Then
Dim gv_trung As Variant
gv_trung = aGvGhepCap(p)
KTra_trung_cap2 = "Trung Cap GV : GV " & aGVCoCoiThi(i) & " bi gap lai voi gv " & gv_trung & " vao buoi thu " & aMonCoiCap(q)
Exit Function
Else
End If
Next q
Next p
' KIEM TRA TRUNG TRUONG
For p = 1 To 3
If Trim(aMaTruongGVCoCoiThi(i)) = Trim(aMaTruongGVCoiCap(p)) And aMaTruongGVCoiCap(p) <> "" Then
KTra_trung_cap2 = "Trung truong : GV " & aGVCoCoiThi(i) & " o mon thu " & p
Exit Function
Else
End If
Next p
' reset aGvGhepCap va aMonCoiCap va aMaTruongGVCoiCap
For p = 1 To 3
aGvGhepCap(p) = 0
aMonCoiCap(p) = 0
aMaTruongGVCoiCap(p) = ""
Next p
chiSoHienHanhGVCap = 1
Next i
KTra_trung_cap2 = " 0 "
End Function
42: 1-> 40 ; E2:E42: 41->80;
