Lọc kết quả và thêm công thức

duong_nam_dao · 5/9/08

Tổng hợp kết quả

Em có 1 tài liệu kèm theo. Em muốn lọc kết quả theo từng giáo viên, sau đó tự tính điểm trung bình của mỗi người. Em chỉ biết làm bằng tay nên lâu vì mỗi giai đoạn lại phải đánh 1 công thức. Các bác xem hộ em với. Giáo dục là quốc sách mà, rất mong các bác giúp đỡ !
Em chờ mãi mà chưa thấy các bác. Nếu kết hợp công thức khó quá thì để em làm sau. Nhờ các bác tổng hợp kết quả giùm em. Em đã thử nhiều cách nhưng không được. Kiến thức còn hạn chế quá. Mong các bác giúp đỡ để em học hỏi.

ptm0412 · 6/9/08

Có côngthức này nhưng không hay lắm:
Ô I44 là kết quả của phép chia:
Tử số: (SUMPRODUCT((($C44:$C45="Toán")+($C44:$C45="Văn"))*F44:F45*2)+SUMPRODUCT(($C44:$C45<>"Văn")*($C44:$C45<>"Toán")*F44:F45))

Mẫu số: COUNTIF($C44:$C45;"toán")+COUNTIF($C44:$C45;"Văn")+COUNTA($C44:$C45)

Kết quả:
=(SUMPRODUCT((($C44:$C45="Toán")+($C44:$C45="Văn"))*F44:F45*2)+SUMPRODUCT(($C44:$C45<>"Văn")*($C44:$C45<>"Toán")*F44:F45))/(COUNTIF($C44:$C45;"toán")+COUNTIF($C44:$C45;"Văn")+COUNTA($C44:$C45))

Có thể copy qua N42, N44, S44, X42, X44
Nhưng dở là không copy vào I42 và S42 là các ô chỉ có 1 môn học. Và cái dở lớn nhất là dài quá.

Ghi chú: Cái mẫu số trên có thể dùng làm mẫu số cho ô TK theo TT

Muốn công thức ngắn chắc phải dùng bảng phụ.

SA_DQ · 7/9/08

Những ý kiến ban đầu

Rất muốn giúp bạn, nhưng không phải do 'Giáo dục là Quốc sách' mà giúp!
Một số góp ý ban đầu, như sau:
Trong CSDL không nên có các dòng trống dữ liệu;
Nếu để dễ phân biệt số dòng, bạn có thể dùng màu nền nhạt cho các dòng records (bội số 5 chẵng hạn!). Khi gặp dòng trống thì người xài VBA ngại vô cùng;
Không nên chưa xong báo cáo/tổng hợp mà đã trang trí trang tính, như trộn ô, bỏ trống các dòng phía trên CSDL;
Cũng như không nên có các khung như những khung 'giai đoạn' của bạn
Cả các vấn đề trên, muốn nói rằng, bảng dữ liệu thô nên có cấu trúc CSDL, mà những điều cơ bản của nó là:
* Không có ô trống nào trong CSDL;
* Tên trường không có khoảng trắng, không nên dùng tiếng việt (Do ta nhiều loại font khác nhau cùng tồn tại & chúng không biết nhường nhịn nhau bao giờ!)
Theo như của bạn, nên là: [TT], [HTen], [MonHoc], [Lop], [SoHS]. . . .

Nói chung sẽ tiết kiệm thời gian hơn cho bạn & cho mọi người, mà mình có những ý kiến trên. Rất mong bạn chịu khó lắng nghe & chớ giận!!

duong_nam_dao · 7/9/08

Ồ không, dốt thì phải hỏi, phải lắng nghe chứ. Rất cám ơn bác đã góp ý.
Trong file của em có các dòng trống vì còn liên kết với các trang khác. số liệu trên đó chỉ là giả định thôi. Đây là vấn đề cuối cùng trong tác phẩm của em để phục vụ cho trường trong năm học mới này, năm học nâng cao CNTT. Mong các bác lọc giúp em kết quả theo giáo viên (phần dữ liệu từ dòng 4 đến 37), còn phần công thức em làm sau. Chờ tin các bác

SA_DQ · 7/9/08

Mong các bác lọc giúp em kết quả theo giáo viên (phần dữ liệu từ dòng 4 đến 37), còn phần công thức em làm sau. Chờ tin các bác

Thứ nhất, mình có sửa đổi trong sheets DL (dữ liệu) một chút;
Kết quả mình đưa ra tại 'S1'
Rất mong chúng ta sẽ tiếp tục phần bổ sung công thức tính trong thời gian tới!

PHP:

Option Explicit
Sub TongHop()
 Const GDoan As String = "Giai Doan "
 Dim lRow As Long, Wzj As Long:                    Dim BFf As Byte
 Dim Clls As Range:                                Dim sTen As String
 
 lRow = Sheets("LocGV6").[b65500].End(xlUp).Row
 Application.ScreenUpdating = False
 With Sheets("S1")
   .Range("A1:z" & lRow - 3) = Sheets("LocGV6").Range("A4:Z" & lRow).Value
   .[a2] = "TT":        .[b2] = "HTenGV":          .[c2] = "MonHoc"
   .[d2] = "Lop":                                  .[E2] = "SiSo"
   For BFf = 1 To 4
      Set Clls = Choose(BFf, .[f1], .[k1], .[p1], .[U1])
      Clls = GDoan & Choose(BFf, "I", "II", "III", "IV")
      Clls.Font.Bold = True:                       Clls.Resize(, 3).Merge
   Next BFf
   .Range("B2:W" & lRow).Sort Key1:=.Range("B3"), Order1:=xlAscending, _
        Key2:=.Range("C3"), Order2:=xlAscending, Key3:=.Range("D3"), _
        Order3:=xlAscending, Header:=xlYes, OrderCustom:=1
   .Range("A1:E1") = "":           .[a3] = 1
   BFf = 0:                         .Range("A4:A" & lRow).Clear
   For Wzj = 4 To lRow
      With .Cells(Wzj, "B")
         If .Value = "" Then Exit For
         .Offset(, -1) = Wzj - 2
         If .Value = sTen Or .Value = .Offset(-1) Then
            .Value = ""
         Else
            sTen = .Value
         End If
      End With
   Next Wzj
 End With
End Sub

SA_DQ · 8/9/08

Để tiếp tục, ta phải từ dễ đến khó mà thôi!

PHP:

Option Explicit

Sub TongHop()
 Const GDoan As String = "Giai Doan "
 Dim lRow As Long, Wzj As Long:                    Dim BFf As Byte
 Dim Clls As Range:                                Dim sTen As String, MChinh As String
1 ' Lap DS Giao Vien Day Cac Mon Hoc Tai "S1"'
 lRow = Sheets("LocGV6").[b65500].End(xlUp).Row
 Application.ScreenUpdating = False
 With Sheets("S1")
   .Range("A1:z" & lRow - 3) = Sheets("LocGV6").Range("A4:Z" & lRow).Value
   .[a2] = "TT":        .[b2] = "HTenGV":          .[c2] = "MonHoc"
   .[d2] = "Lop":                                  .[E2] = "SiSo"
   For BFf = 1 To 4
      Set Clls = Choose(BFf, .[f1], .[k1], .[p1], .[U1])
      Clls = GDoan & Choose(BFf, "I", "II", "III", "IV")
      Clls.Font.Bold = True:                       Clls.Resize(, 3).Merge
   Next BFf
   .Range("B2:W" & lRow).Sort Key1:=.Range("B3"), Order1:=xlAscending, _
        Key2:=.Range("C3"), Order2:=xlAscending, Key3:=.Range("D3"), _
        Order3:=xlAscending, Header:=xlYes, OrderCustom:=1
   .Range("A1:E1") = "":           .[a3] = 1
   BFf = 0:                         .Range("A4:A" & lRow).Clear
   For Wzj = 4 To lRow
      With .Cells(Wzj, "B")
         If .Value = "" Then Exit For
         .Offset(, -1) = Wzj - 2
         If .Value = sTen Or .Value = .Offset(-1) Then
            .Value = ""
         Else
            sTen = .Value
         End If
      End With
   Next Wzj
2 ' Thay Ma Mon Hoc "VNTNLYSISUDDAH"'
   lRow = .[c65500].End(xlUp).Row:        Sheets("Luu").Select
   For Wzj = lRow To 3 Step -1
      Set Clls = Range("B2:B15").Find(what:=.Cells(Wzj, "C"), LookIn:=xlFormulas)
      If Not Clls Is Nothing Then .Cells(Wzj, "C") = Clls.Offset(, -1)
   Next Wzj
   Sheets("S1").Select
   
   For Wzj = 3 To lRow + 1
      With .Cells(Wzj, "B")
3 ' Tong Hop Voi Nhung GV Chi Day 01 Mon Hoc'
         If .Value <> "" And .Offset(1) <> "" And .Offset(1) <> .Value Or Wzj = lRow Then
            For Each Clls In Union(.Offset(, 4), .Offset(, 9), .Offset(, 14), .Offset(, 19))
               If Clls <> "" Then
                  Clls.Offset(, 3) = Clls.Value:   Clls.Offset(, 4) = Clls.Offset(, 2).Value
               End If
            Next Clls
         End If
4 ' Tong Hop Voi Nhung GV Day 02 Mon Hoc'
         If .Value <> "" And .Offset(1) = "" And .Offset(2) <> "" Then
41    ' Truong Hop 02 Mon Dieu La Mon Hoc Phu Hay Chinh'
            MChinh = "VNTN":           sTen = "SISUDDAN"
            If InStr(MChinh, .Offset(, 1)) > 0 And InStr(MChinh, .Offset(1, 1)) > 0 Or _
               InStr(sTen, .Offset(, 1)) > 0 And InStr(sTen, .Offset(1, 1)) > 0 Then
               For Each Clls In Union(.Offset(, 4), .Offset(, 9), .Offset(, 14), .Offset(, 19))
                  If Clls <> "" Then
                     Clls.Offset(, 3) = (Clls.Value + Clls.Offset(1)) / 2
                     Clls.Offset(, 4) = (Clls.Offset(, 2).Value + Clls.Offset(1, 2)) / 2
                  End If
               Next Clls
            End If
42    ' Truong Hop 01 Mon Hoc La Phu Con Mon Kia La Chinh'
421      ' Truong Hop Mon Hoc Tren La Phu'
            If InStr(sTen, .Offset(, 1)) > 0 And InStr(MChinh, .Offset(1, 1)) > 0 Then
               For Each Clls In Union(.Offset(, 4), .Offset(, 9), .Offset(, 14), .Offset(, 19))
                  If Clls <> "" Then
                     Clls.Offset(, 3) = (Clls.Value + 2 * Clls.Offset(1)) / 3
                     Clls.Offset(, 4) = (Clls.Offset(, 2).Value + 2 * Clls.Offset(1, 2)) / 3
                  End If
               Next Clls
422      ' Truong Hop Mon Hoc Tren La Chinh'
            ElseIf InStr(MChinh, .Offset(, 1)) > 0 And InStr(sTen, .Offset(1, 1)) > 0 Then
               For Each Clls In Union(.Offset(, 4), .Offset(, 9), .Offset(, 14), .Offset(, 19))
                  If Clls <> "" Then
                     Clls.Offset(, 3) = (Clls.Value * 2 + Clls.Offset(1)) / 3
                     Clls.Offset(, 4) = (Clls.Offset(, 2).Value * 2 + Clls.Offset(1, 2)) / 3
                  End If
               Next Clls
            End If
         End If
5 ''
      End With
   Next Wzj
   
   .Columns("A:Z").EntireColumn.AutoFit
 End With
End Sub

SA_DQ · 9/9/08

Rất cần tác gia khẳng định điều này

a./ Rằng không có giáo viên nào được bố trí dạy 3 môn học khác nhau
b./ Các giáo viên được bố trí dạy 3 lớp trở lên chỉ dạy ở 1 môn học mà thôi?!

Chờ tin của bạn;
Nếu bạn cho ý kiến nhanh, chúng ta có thể kết thúc sớm vụ việc này!
Sau đây là macro có bổ sung chức năng so với bài #6

PHP:

Option Explicit
Sub TongHop()
 Const GDoan As String = "Giai Doan "
 Dim lRow As Long, Wzj As Long:                    Dim GPE As Byte
 Dim Clls As Range:                                Dim sTen As String, MChinh As String
1 ' Lap DS Giao Vien Day Cac Mon Hoc Tai "S1"'
 lRow = Sheets("LocGV6").[b65500].End(xlUp).Row
 Application.ScreenUpdating = False
 With Sheets("S1")
   .Range("A1:z" & lRow - 3) = Sheets("LocGV6").Range("A4:Z" & lRow).Value
   .[a2] = "TT":        .[b2] = "HTenGV":          .[c2] = "MonHoc"
   .[d2] = "Lop":                                  .[E2] = "SiSo"
   For GPE = 1 To 4
      Set Clls = Choose(GPE, .[f1], .[k1], .[p1], .[U1])
      Clls = GDoan & Choose(GPE, "I", "II", "III", "IV")
      Clls.Font.Bold = True:                       Clls.Resize(, 3).Merge
   Next GPE
   .Range("B2:W" & lRow).Sort Key1:=.Range("B3"), Order1:=xlAscending, _
        Key2:=.Range("C3"), Order2:=xlAscending, Key3:=.Range("D3"), _
        Order3:=xlAscending, Header:=xlYes, OrderCustom:=1
   .Range("A1:E1") = "":
   GPE = 0:                         .Range("A4:A" & lRow).Clear
   For Wzj = 3 To lRow
      With .Cells(Wzj, "B")
         If .Value = "" Then Exit For
         .Offset(, -1) = Wzj - 2
         If .Value = sTen Or .Value = .Offset(-1) Then
            .Value = ""
         Else
            sTen = .Value
         End If
      End With
   Next Wzj
2 ' Thay Ma Mon Hoc "VNTNLYSISUDDAH"'
   lRow = .[c65500].End(xlUp).Row:        Sheets("Luu").Select
   For Wzj = lRow To 3 Step -1
      Set Clls = Range("B2:B15").Find(what:=.Cells(Wzj, "C"), LookIn:=xlFormulas)
      If Not Clls Is Nothing Then .Cells(Wzj, "C") = Clls.Offset(, -1)
   Next Wzj
   MChinh = "VNTN":           sTen = "SISUDDAN"
   Sheets("S1").Select
 
   For Wzj = 3 To lRow + 1
      With .Cells(Wzj, "B")
3 ' Tong Hop Voi Nhung GV Chi Day 01 Mon Hoc'
         If .Value <> "" And .Offset(1) <> "" And .Offset(1) <> .Value Or Wzj = lRow Then 
            For Each Clls In Union(.Offset(, 4), .Offset(, 9), .Offset(, 14), .Offset(, 19))
               If Clls <> "" Then
                  Clls.Offset(, 3) = Clls.Value:   Clls.Offset(, 4) = Clls.Offset(, 2).Value
               End If
            Next Clls
         End If
4 ' Tong Hop Voi Nhung GV Day 02 Mon Hoc'
         If .Value <> "" And .Offset(1) = "" And .Offset(2) <> "" Then
41    ' Truong Hop 02 Mon Dieu La Mon Hoc Phu Hay Chinh'
            If InStr(MChinh, .Offset(, 1)) > 0 And InStr(MChinh, .Offset(1, 1)) > 0 Or _
               InStr(sTen, .Offset(, 1)) > 0 And InStr(sTen, .Offset(1, 1)) > 0 Then
               For Each Clls In Union(.Offset(, 4), .Offset(, 9), .Offset(, 14), .Offset(, 19))
                  If Clls <> "" Then
                     Clls.Offset(, 3) = (Clls.Value + Clls.Offset(1)) / 2
                     Clls.Offset(, 4) = (Clls.Offset(, 2).Value + Clls.Offset(1, 2)) / 2
                  End If
               Next Clls
            End If
42    ' Truong Hop 01 Mon Hoc La Phu Con Mon Kia La Chinh'
421      ' Truong Hop Mon Hoc Tren La Phu'
            If InStr(sTen, .Offset(, 1)) > 0 And InStr(MChinh, .Offset(1, 1)) > 0 Then
               For Each Clls In Union(.Offset(, 4), .Offset(, 9), .Offset(, 14), .Offset(, 19))
                  If Clls <> "" Then
                     Clls.Offset(, 3) = (Clls.Value + 2 * Clls.Offset(1)) / 3
                     Clls.Offset(, 4) = (Clls.Offset(, 2).Value + 2 * Clls.Offset(1, 2)) / 3
                     Clls.Offset(, 3).Resize(, 2).NumberFormat = "0.00"
                  ElseIf Clls = "" And Clls.Offset(1) <> "" Then
                     Clls.Offset(1, 3) = Clls.Offset(1)
                     Clls.Offset(1, 4) = Clls.Offset(1, 2).Value              '*'
                  End If
               Next Clls
422      ' Truong Hop Mon Hoc Tren La Chinh'
            ElseIf InStr(MChinh, .Offset(, 1)) > 0 And InStr(sTen, .Offset(1, 1)) > 0 Then
               For Each Clls In Union(.Offset(, 4), .Offset(, 9), .Offset(, 14), .Offset(, 19))
                  If Clls <> "" Then
                     Clls.Offset(, 3) = (Clls.Value * 2 + Clls.Offset(1)) / 3
                     Clls.Offset(, 4) = (Clls.Offset(, 2).Value * 2 + Clls.Offset(1, 2)) / 3
                     Clls.Offset(, 3).Resize(, 2).NumberFormat = "0.00"
                  End If
               Next Clls
            End If
         End If
5 ''
      End With
   Next Wzj
 
   .Columns("A:Z").EntireColumn.AutoFit
 End With
End Sub

haonlh · 9/9/08

Xem file dữ liệu của bạn mới thấy bạn còn "tù mù" lắm.
Ví dụ: bạn biết dồn (merger) 2 ô trên dưới (như cột TT, Họ và tên giáo viên...) nhưng không biết dồn 3 ô nằm ngang như các ô có nội dung là GIAI ĐOẠN I, GIAI ĐOẠN II...
Làm được đó bạn ạ.
Về câu hỏi của bạn, tôi xử lý thế này: thêm 1 cột ở đầu Sheet, nhập số thứ tự tăng dần cho nó.
Sắp thứ tự theo cột họ tên thì người cùng họ tên về với nhau. Cần về trạng thái cũ, Sort theo cột đầu tiên.
Ngoài ra, tôi không biết "TK theo TT" và "TK theo TB" phải xử lý thế nào khi số môn học của giáo viên tăng lên. Chắc phải nhập công thức thủ công.

duong_nam_dao · 9/9/08

haonlh đã viết:
Xem file dữ liệu của bạn mới thấy bạn còn "tù mù" lắm.
Ví dụ: bạn biết dồn (merger) 2 ô trên dưới (như cột TT, Họ và tên giáo viên...) nhưng không biết dồn 3 ô nằm ngang như các ô có nội dung là GIAI ĐOẠN I, GIAI ĐOẠN II...
Làm được đó bạn ạ.
Về câu hỏi của bạn, tôi xử lý thế này: thêm 1 cột ở đầu Sheet, nhập số thứ tự tăng dần cho nó.
Sắp thứ tự theo cột họ tên thì người cùng họ tên về với nhau. Cần về trạng thái cũ, Sort theo cột đầu tiên.
Ngoài ra, tôi không biết "TK theo TT" và "TK theo TB" phải xử lý thế nào khi số môn học của giáo viên tăng lên. Chắc phải nhập công thức thủ công.

Xin giải thích thêm với các bác rằng đây chỉ là file giả định, ở file thật còn liên kết với trang khác, em không dồn các ô nằm ngang ở các giai đoạn vì ở đó còn có dữ liệu phục vụ cho các liên kết kia

Còn về số môn học, giáo viên dạy thì chỉ có các môn đó mới thi ở các giai đoạn, giáo viên có thể dạy 3 môn ở nhiều lớp khác nhau.

Rất cảm ơn các bác đã giúp đỡ em. Em bổ xung thêm việc thay thể các mã môn của bác thành tên môn ban đầu nhưng không biết lỗi ở chỗ nào

Các bác nghiên cứu tiếp hộ em. Cám ơn các bác nhiều !

Lọc kết quả và thêm công thức

duong_nam_dao

Thành viên chính thức

File đính kèm

ptm0412

Bad Excel Member

SA_DQ

/(hông là gì!

duong_nam_dao

Thành viên chính thức

SA_DQ

/(hông là gì!

SA_DQ

/(hông là gì!

File đính kèm

SA_DQ

/(hông là gì!

haonlh

Thành viên tích cực

duong_nam_dao

Thành viên chính thức

File đính kèm

Bài viết mới nhất

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian