• Thứ Ba, 15/03/2011 10:20 (GMT+7)

    Xếp lớp học với Excel

    Nguyễn Văn Hiệp
    Bài viết trình bày một hướng giải quyết yêu cầu của bạn đọc về khả năng ứng dụng Excel để xếp lớp cho danh sách học sinh mới tuyển sinh lớp 5 lên lớp 6. Một ứng dụng rất cần và thường dùng ở các trường học.

    Việc xếp lớp cần phải thoả được các tiêu chuẩn sau:

    1. Chia đều học sinh của "5 trường tiểu học" và chia đều số học sinh có các học lực giỏi, khá, trung bình vào mỗi lớp. 

    2. Chọn riêng học sinh khá giỏi vào 1 lớp, còn lại chia đều. 

    3. Nếu thiết kế form giao diện thì có 1 textbox để nhập số lớp cần chia trên tổng số học sinh, 2 OptionButton để người dùng chọn tiêu chuẩn chia lớp (1 hay 2). 

    Để lập trình giải quyết yêu cầu đặt ra, bạn cần tìm hiểu và nắm vững các vấn đề sau:

    - Cách thức thiết kế trực quan giao diện cho form ứng dụng.

    - Cú pháp các lệnh VBA để viết đúng các lệnh hầu miêu tả được thuật giải mong muốn.

    - Cách thức lập trình truy xuất cell trong từng worksheet Excel.

    - Thuật giải xếp lớp theo từng tiêu chuẩn mong muốn.

    Thường có nhiều phương pháp, thuật giải khác nhau cùng giải quyết được bài toán xác định, mỗi phương pháp, thuật giải có những ưu, khuyết điểm riêng. Thí dụ để xếp lớp học sinh theo tiêu chuẩn 1, bạn có thể dùng thuật giải sau đây:

    1. Tách các học sinh trong danh sách ra m danh sách khác nhau, mỗi danh sách chỉ chứa học sinh của 1 trường, trong danh sách này, các học sinh sẽ được xếp theo thứ tự từ điểm cao xuống thấp (hay từ giỏi xuống trung bình).

    2. Lấy danh sách học sinh của từng trường tiểu học để xếp cho n lớp như sau:

    2.1. Lấy từng học sinh và phân phối vào từng lớp theo thứ tự lớp tăng dần.

    2.2. Khi phân phối 1 lượt xong cho n lớp thì lấy từng học sinh còn lại của trường hiện hành và phân phối vào n lớp theo thứ tự lớp giảm dần.

    2.3. Quay lại bước 2.1 nếu còn học sinh của trường đó.

    Tương tự, để xếp lớp học sinh theo tiêu chuẩn 2, bạn có thể dùng thuật giải sau đây:

    1. Xếp các học sinh trong danh sách theo thứ tự điểm tổng kết từ cao xuống thấp (hay từ giỏi xuống trung bình).

    2. Tính số học sinh cho từng lớp SoHS = tổng số/n lớp, có thể thêm 1 học sinh nữa nếu còn dư.

    3. Lấy SoHS đầu tiên trong danh sách (đây là các học sinh giỏi nhất) phân phối cho lớp n.

    4. Phần các học sinh còn lại sẽ được xếp vào n-1 lớp theo tiêu chuẩn 1 đã giới thiệu ở trên.

    Sau đây là qui trình điển hình để xây dựng form Excel phục vụ chia lớp theo yêu cầu của bạn dùng 2 thuật giải đề nghị ở trên:

    1. Chạy Excel, mở file Excel chứa danh sách học sinh cần chia lớp. Nếu chưa có, bạn có thể chạy macro TaoDSHS được viết dưới đây để tạo danh sách học sinh giả.

    2. Chọn menu Tools.Macro.Visual Basic Editor để mở cửa sổ soạn code VBA. 

    3. Dời chuột về phần tử gốc của cửa sổ "Project" nằm ở trên trái màn hình, ấn phải chuột vào phần tử gốc để hiển thị menu lệnh, chọn mục Insert.Module để tạo mới 1 module mã nguồn VBA. Khi cửa sổ mã nguồn trống hiển thị, bạn hãy nhập đoạn code trong phần Mã nguồn đi theo bài viết (bạn có thể tải về file Excel chứa mã nguồn trong phần thư viện Chương trình mẫu trên website của TGVT).

    4. Hiệu chỉnh giá trị các hằng TSHS, SOCOT, COTTR, COTHL, STARTROW trong các lệnh định nghĩa đầu tiên sao cho phù hợp với dữ liệu thực tế.

    5. Dời chuột về phần tử gốc của cửa sổ "Project" nằm ở trên trái màn hình, nhấn phải chuột vào phần tử gốc để hiển thị menu lệnh, chọn mục Insert.UserForm để tạo mới 1 Form. Khi cửa sổ thiết kế hiển thị Form trống, bạn thiết kế Form chứa các phần tử giao diện như sau:

    Lưu ý là vẽ 2 optionButton trong 1 frame có tiêu đề là "Chon tieu chuan xep lop". Đặt tên cho textbox là txtSolop, cho button là btnXeplop, cho optionButton 1 là optButton1, cho optionButton 2 là optButton2.

    6. Nhấn đúp chuột vào button để tạo thủ tục xử lý sự kiện click chuột trên button rồi viết code cho thủ tục như sau:

    Private Sub btnXepLop_Click()
    Dim solop As Integer
    'xác định số lớp cần chia
    solop = CInt(txtSolop.Text)
    'chia lớp theo tiêu chuẩn được chọn
    If optButton1.Value Then
    Xeplop1 (solop)
    Else
    Xeplop2 (solop)
    End If
    End Sub

    7. Chọn menu File.Close and Return... để quay về cửa sổ hiển thị bảng tính.

    8. Nếu chưa có danh sách học sinh cần xếp lớp, bạn hãy nhập vào và đặt tên cho sheet chứa danh sách học sinh là "DSHS". Nếu muốn tạo danh sách học sinh giả, chọn menu Tools.Macro.Macros để hiển thị cửa sổ macro. Chọn macro có tên là TaoDSHS, chọn button Run để chạy nó và tạo danh sách học sinh giả gồm 300 học sinh của 5 trường tiểu học khác nhau, mỗi học sinh có 3 field thông tin thiết yếu nhất là: họ tên, tên trường, điểm tổng kết.

    9. Chọn menu Tools.Macro.Visual Basic Editor để mở lại cửa sổ soạn code VBA. 

    10. Dời chuột về cửa sổ Project ở phía trên trái màn hình, nhấn đúp chuột vào mục UserForm1 để hiển thị nó. Chọn menu Run.Run Sub/userForm để chạy Form ứng dụng.

    11. Khi form ứng dụng hiển thị, nhập số lớp cần chia vào textbox, chọn tiêu chuẩn chia lớp, nhấn chuột vào button "Xep lop" để máy tính chia lớp.

    12. Đóng form ứng dụng lại, chọn menu File.Close and Return... để quay về cửa sổ hiển thị bảng tính. Bạn sẽ thấy có nhiều worksheet có tên là "Lop i", mỗi worksheet này chứa danh sách học sinh của 1 lớp.

    Lưu ý trước khi cần chia lại lớp theo tiêu chuẩn khác, bạn phải xóa các worksheet "Lop i".

    Option Explicit
    'định nghĩa các hằng gợi nhớ cần dùng
    Const TSHS = 300 'tổng số học sinh trong danh sách
    Const STARTROW = 2 'hàng chứa học sinh đầu tiên
    Const SOCOT = 3 'số cột thông tin của mỗi học sinh
    Const COTTR = 2 'chỉ số cột chứa mã (hay tên) trường
    Const COTHL = 3 'chỉ số cột chứa điểm TK hay loại xếp hạng
    'định nghĩa kiểu miêu tả lớp học
    Type Lop
    name As String
    row As Integer
    End Type
    'định nghĩa danh sách các lớp
    Dim aLop() As Lop

    'macro tạo danh sách 300 học sinh giả
    Sub TaoDSHS()
    Dim rg As Range
    Dim i As Integer, matr As Integer
    Dim diem As Double
    matr = 0: diem = 10
    Sheets.Add
    ActiveSheet.name = "DSHS"
    Columns("A:A").ColumnWidth = 22
    Cells(1, 1) = "Ho ten"
    Cells(1, 2) = "Ma Truong"
    Cells(1, 3) = "Diem TK"
    Set rg = Range("A2:Z301")
    For i = 1 To 300
    rg.Cells(i, 1) = "Hoc sinh " & i
    rg.Cells(i, 2) = matr
    rg.Cells(i, 3) = diem
    matr = matr + 1
    If matr = 5 Then matr = 0
    diem = diem - 0.1
    If diem < 5 Then diem = 10
    Next i
    End Sub

    'thủ tục xếp lớp theo tiêu chuẩn 1
    Sub Xeplop1(n As Integer)
    'định nghĩa các biến cần dùng
    Dim i As Integer
    Dim col As Integer
    Dim srhl As String
    Dim srtr As String
    'xác định vị trí cột điểm
    srhl = Chr(COTHL + Asc("A") - 1) & STARTROW
    'xác định vị trí cột mã trường
    srtr = Chr(COTTR + Asc("A") - 1) & STARTROW
    'copy DSHS từ sheet gốc sang sheet tạm
    Sheets("DSHS").Copy Before:=Sheets(1)
    'đặt tên sheet tạm
    ActiveSheet.name = "DSTAM"
    'thiết lập vùng cell chứa thông tin các học sinh
    Set rg = Range("A" & STARTROW & ":Z" & (TSHS + STARTROW - 1))
    rg.Select
    'xếp danh sach học sinh theo trường
    'mỗi trường xếp từ điểm cao -> thấp
    Selection.Sort Key1:=Range(srtr), Order1:=xlAscending, Key2:=Range(srhl), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 :=xlSortNormal
    'cấp phát n record quản lý n lớp
    ReDim aLop(1 To n)
    'tạo n sheet chứa học sinh của n lớp
    For i = 1 To n
    Sheets.Add
    ActiveSheet.name = "Lop " & i
    aLop(i).name = "Lop " & i
    aLop(i).row = STARTROW
    'copy hàng tiêu đề từ sheet DSHS sang lớp i
    For col = 1 To SOCOT
    ActiveSheet.Cells(STARTROW - 1, col) = Sheets("DSTAM").Cells(STARTROW - 1, col)
    Next col
    Next i
    'chọn sheet DSTAM để làm việc với nó
    Sheets("DSTAM").Select
    'gọi thủ tục Xeplop1t để xếp các học sinh
    'vào n lớp theo tiêu chuẩn 1
    Xeplop1t n, STARTROW
    'xóa sheet tạm
    Sheets("DSTAM").Select
    ActiveWindow.SelectedSheets.Delete
    End If

    'Thủ tục xếp các học sinh vào n lớp theo tiêu chuẩn 1
    Sub Xeplop1t(n As Integer, rstart As Integer)
    'định nghĩa các biến cần dùng
    Dim idt As Integer, i As Integer, il As Integer
    Dim step As Integer
    Dim col As Integer
    Dim idxt(0 To 10) As Integer
    Dim smt(0 To 10) As Integer
    Dim rg As Range
    Dim row As Integer, rmax As Integer
    'xác định số trường tiểu học và
    'vị trí học sinh đầu tiên của từng trường
    idt = 0: idxt(0) = rstart
    smt(0) = Cells(rstart, COTTR).Value
    For i = rstart To STARTROW + TSHS - 1
    If Cells(i, COTTR).Value <> smt(idt) Then
    idt = idt + 1
    idxt(idt) = i
    smt(idt) = Cells(i, COTTR).Value
    End If
    Next i
    'xếp các học sinh từng trường vào n lớp
    il = 1: step = 1
    For i = 0 To idt
    row = idxt(i)
    If i < idt Then
    rmax = idxt(i + 1) - 1
    Else
    rmax = STARTROW + TSHS - 1
    End If
    While row <= rmax
    'copy 1 học sinh từ sheet tạm sang lớp il
    For col = 1 To SOCOT
    Sheets(aLop(il).name).Cells(aLop(il).row, col) = Cells(row, col)
    Next col
    'cập nhật hàng cần thêm học sinh
    aLop(il).row = aLop(il).row + 1
    row = row + 1
    'thay đổi lớp
    il = il + step
    If il > n Then
    step = -1
    il = n
    End If
    If il < 1 Then
    step = 1
    il = 1
    End If
    Wend
    Next i
    End Sub

    'thủ tục xếp lớp theo tiêu chuẩn 2
    Sub Xeplop2(n As Integer)
    'định nghĩa các biến cần dùng
    Dim i As Integer, idt As Integer
    Dim idxt(0 To 10) As Integer
    Dim smt(0 To 10) As Integer
    Dim rg As Range
    Dim row As Integer, rmax As Integer
    Dim col As Integer, SoHS As Integer, HSDu As Integer
    Dim srhl As String
    Dim srtr As String
    'xác định vị trí cột điểm
    srhl = Chr(COTHL + Asc("A") - 1) & STARTROW
    'xác định vị trí cột mã trường
    srtr = Chr(COTTR + Asc("A") - 1) & STARTROW
    'copy DSHS từ sheet gốc sang sheet tạm
    Sheets("DSHS").Copy Before:=Sheets(1)
    'đặt tên sheet tạm
    ActiveSheet.name = "DSTAM"
    'thiết lập vùng cell chứa thông tin các học sinh
    Set rg = Range("A" & STARTROW & ":Z" & (TSHS + STARTROW - 1))
    rg.Select
    'xếp danh sách học sinh theo thứ tự điểm từ cao -> thấp
    Selection.Sort Key1:=Range(srhl), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    'cấp phát n record quản lý n lớp
    ReDim aLop(1 To n)
    'tạo n sheet chứa học sinh của n lớp
    For i = 1 To n
    Sheets.Add
    ActiveSheet.name = "Lop " & i
    aLop(i).name = "Lop " & i
    aLop(i).row = STARTROW
    'copy hàng tiêu đề từ sheet DSHS sang lớp i
    For col = 1 To SOCOT
    ActiveSheet.Cells(STARTROW - 1, col) = Sheets("DSTAM").Cells(STARTROW - 1, col)
    Next col
    Next i
    'chọn sheet DSTAM để làm việc với nó
    Sheets("DSTAM").Select
    'tính số học sinh cho mỗi lớp
    SoHS = TSHS \ n
    HSDu = TSHS Mod n
    row = STARTROW
    'phân phối các học sinh giỏi nhất vào lớp n
    i = 0
    While i < SoHS
    'copy 1 học sinh từ sheet tạm sang lớp n
    For col = 1 To SOCOT
    Sheets(aLop(n).name).Cells(aLop(n).row, col) = Cells(row, col)
    Next col
    'cập nhật hàng cần thêm học sinh
    aLop(n).row = aLop(n).row + 1
    row = row + 1
    'cập nhật số học sinh đã phân phối
    i = i + 1
    Wend
    'nếu còn dư học sinh thì lấy thêm 1 học sinh nữa
    If HSDu <> 0 Then
    'copy 1 học sinh từ sheet tạm sang lớp n
    For col = 1 To SOCOT
    Sheets(aLop(n).name).Cells(aLop(n).row, col) = Cells(row, col)
    Next col
    'cập nhật hàng cần thêm học sinh
    aLop(n).row = aLop(n).row + 1
    End If
    Set rg = Range("A" & row & ":Z" & (TSHS + STARTROW - 1))
    rg.Select
    'xếp danh sách học sinh còn lại theo trường
    'mỗi trường xếp từ điểm cao -> thấp
    Selection.Sort Key1:=Range(srtr), Order1:=xlAscending, Key2:=Range(srhl), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 :=xlSortNormal
    'gọi thủ tục Xeplop1t để xếp các học sinh còn lại
    'vào n-1 lớp còn lại theo tiêu chuẩn 1
    Xeplop1t n - 1, row
    'xóa sheet tạm
    Sheets("DSTAM").Select
    ActiveWindow.SelectedSheets.Delete
    End Sub
     


    Nguyễn Văn Hiệp
     

    ID: A1101_98