Sub tuhocvba1008()
Dim rend As Long 'Dong cuoi
Dim i As Long
Dim shn As String 'Ten sheet lam viec. Ex: sheet1
Dim lop As String 'ten lop
Dim cnt As Long 'Dem so luong hoc sinh trong mot lop
Dim cn As Double 'Can nang
Const rbd As Long = 4 'Dong bat dau
shn = ActiveSheet.Name
rend = ThisWorkbook.Sheets(shn).Cells(Rows.Count, 1).End(xlUp).Row
If rend < rbd Then Exit Sub 'Ket thuc chuong trinh vi khong tim thay du lieu
'Sap xep lai du lieu, tranh truong hop du lieu xep lung tung
'Xep theo ten lop
'Tham khao sort:
'https://tuhocvba.net/threads/thuc-thi-sap-xep-du-lieu-bang-doi-tuong-sort.171/
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Cells(rbd, 1), Order:=xlDescending
.SetRange Range(Cells(rbd, 1), Cells(rend, 3)) 'Vi co 3 cot du lieu, neu co nhieu cot du lieu thi phai sua code cho nay
.Header = xlNo
.Apply
End With
lop = "tuhocvba.net" 'khoi tao thong so ban dau
cnt = 0
cn = 0
i = rbd
Do Until i > rend
If Cells(i, 1) <> lop And Cells(i, 1) <> "" Then
'Kiem tra phat hien lop moi thi tach dong
If cnt <> 0 Then
'fix loi
lop = Cells(i, 1)
Rows(i).Insert Shift:=xlDown
rend = rend + 1
Cells(i, 3) = cn / cnt 'ghi can nang trung binh
'Reset lai gia tri
cn = 0
cnt = 0
Else
'Lan dau tien phat hien data
'gan ten lop moi
lop = Cells(i, 1)
cnt = 1
cn = Cells(i, 3)
End If
ElseIf Cells(i, 1) = lop And Cells(i, 1) <> "" Then
cnt = cnt + 1
cn = cn + Cells(i, 3)
End If
i = i + 1
Loop
If cnt <> 0 Then
Cells(rend + 1, 3) = cn / cnt 'ghi can nang trung binh lan cuoi
End If
MsgBox "Da hoan thanh"
End Sub