Cách sử dung hàm Countif trong VBA

Các thầy giúp em thu gọn code này với ạ. em cảm ơn

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
'Bo qua loi
On Error Resume Next
'TONG HOP HOC LUC
Range("C6").Value = WorksheetFunction.CountIfs("bieutong12").[f11:f100],B6)
Range("C6").Value = "=COUNTIFS(bieutong12!f11:f100,B6)"       'tieng viet
Range("C7").Value = "=COUNTIFS(bieutong12!f11:f100,B7)"
Range("C8").Value = "=COUNTIFS(bieutong12!f11:f100,B8)"
Range("C9").Value = "=COUNTIFS(bieutong12!f11:f100,B9)"

Range("D6").Value = "=COUNTIFS(bieutong12!h11:h100,B6)"      'toan
    Range("D7").Value = "=COUNTIFS(bieutong12!h11:h100,B7)"
    Range("D8").Value = "=COUNTIFS(bieutong12!h11:h100,B8)"
    Range("D9").Value = "=COUNTIFS(bieutong12!h11:h100,B9)"

Range("E6").Value = "=COUNTIFS(bieutong12!j11:j100,B6)"   'TNXH
        Range("E7").Value = "=COUNTIFS(bieutong12!j11:j100,B7)"
        Range("E8").Value = "=COUNTIFS(bieutong12!j11:j100,B8)"
        Range("E9").Value = "=COUNTIFS(bieutong12!j11:j100,B9)"

Range("f6").Value = "=COUNTIFS(bieutong12!k11:k100,B6)"   'Ngoai ngu
            Range("f7").Value = "=COUNTIFS(bieutong12!k11:k100,B7)"
            Range("f8").Value = "=COUNTIFS(bieutong12!k11:k100,B8)"
            Range("f9").Value = "=COUNTIFS(bieutong12!k11:k100,B9)"

Range("g6").Value = "=COUNTIFS(bieutong12!m11:m100,B6)"   'Tin hoc
                Range("g7").Value = "=COUNTIFS(bieutong12!m11:m100,B7)"
                Range("g8").Value = "=COUNTIFS(bieutong12!m11:m100,B8)"
                Range("g9").Value = "=COUNTIFS(bieutong12!m11:m100,B9)"

Range("h6").Value = "=COUNTIFS(bieutong12!o11:o100,B6)"       'Tieng dan toc
                Range("h7").Value = "=COUNTIFS(bieutong12!o11:o100,B7)"
                Range("h8").Value = "=COUNTIFS(bieutong12!o11:o100,B8)"
                Range("h9").Value = "=COUNTIFS(bieutong12!o11:o100,B9)"

Range("i6").Value = "=COUNTIFS(bieutong12!q11:q100,B6)"       'Dao duc
                Range("i7").Value = "=COUNTIFS(bieutong12!q11:q100,B7)"
                Range("i8").Value = "=COUNTIFS(bieutong12!q11:q100,B8)"
                Range("i9").Value = "=COUNTIFS(bieutong12!q11:q100,B9)"

Range("j6").Value = "=COUNTIFS(bieutong12!r11:r100,B6)"           'Am nhac
            Range("j7").Value = "=COUNTIFS(bieutong12!r11:r100,B7)"
            Range("j8").Value = "=COUNTIFS(bieutong12!r11:r100,B8)"
            Range("j9").Value = "=COUNTIFS(bieutong12!r11:r100,B9)"

Range("k6").Value = "=COUNTIFS(bieutong12!s11:s100,B6)"            'my thuat
        Range("k7").Value = "=COUNTIFS(bieutong12!s11:s100,B7)"
        Range("k8").Value = "=COUNTIFS(bieutong12!s11:s100,B8)"
        Range("k9").Value = "=COUNTIFS(bieutong12!s11:s100,B9)"

Range("l6").Value = "=COUNTIFS(bieutong12!t11:t100,B6)"               'HDTN
    Range("l7").Value = "=COUNTIFS(bieutong12!t11:t100,B7)"
    Range("l8").Value = "=COUNTIFS(bieutong12!t11:t100,B8)"
    Range("l9").Value = "=COUNTIFS(bieutong12!t11:t100,B9)"

Range("M6").Value = "=COUNTIFS(bieutong12!u11:u100,B6)"       'the chat
        Range("M7").Value = "=COUNTIFS(bieutong12!u11:u100,B7)"
        Range("M8").Value = "=COUNTIFS(bieutong12!u11:u100,B8)"
        Range("M9").Value = "=COUNTIFS(bieutong12!u11:u100,B9)"

Range("c6:m9").Value = Range("c6:m9").Value         'chuyen toan bo sang value

'TONG HOP NANG LUC chung
Range("C15").Value = "=COUNTIFS(bieutong12!v11:v100,B15)"     'tu quan
Range("C16").Value = "=COUNTIFS(bieutong12!v11:v100,B16)"
Range("C17").Value = "=COUNTIFS(bieutong12!v11:v100,B17)"

Range("D15").Value = "=COUNTIFS(bieutong12!w11:w100,B15)"     'Giao tiep
    Range("D16").Value = "=COUNTIFS(bieutong12!w11:w100,B16)"
    Range("D17").Value = "=COUNTIFS(bieutong12!w11:w100,B17)"

Range("E15").Value = "=COUNTIFS(bieutong12!x11:x100,B15)"     'Giai quyet van de
            Range("E16").Value = "=COUNTIFS(bieutong12!x11:x100,B16)"
            Range("E17").Value = "=COUNTIFS(bieutong12!x11:x100,B17)"

'nang luc dac thu
            Range("F15").Value = "=COUNTIFS(bieutong12!y11:y100,B15)"       'Ngon ngu
            Range("F16").Value = "=COUNTIFS(bieutong12!y11:y100,B16)"
            Range("F17").Value = "=COUNTIFS(bieutong12!y11:y100,B17)"

Range("G15").Value = "=COUNTIFS(bieutong12!z11:z100,B15)"       'toan hoc
    Range("G16").Value = "=COUNTIFS(bieutong12!z11:z100,B16)"
    Range("G17").Value = "=COUNTIFS(bieutong12!z11:z100,B17)"

Range("H15").Value = "=COUNTIFS(bieutong12!Aa11:Aa100,B15)"       'tham my
Range("H16").Value = "=COUNTIFS(bieutong12!Aa11:Aa100,B16)"
Range("H17").Value = "=COUNTIFS(bieutong12!Aa11:Aa100,B17)"

Range("i15").Value = "=COUNTIFS(bieutong12!Ab11:Ab100,B15)"       'the chat
            Range("i16").Value = "=COUNTIFS(bieutong12!Ab11:Ab100,B16)"
            Range("i17").Value = "=COUNTIFS(bieutong12!Ab11:Ab100,B17)"

'Pham chat

Range("j15").Value = "=COUNTIFS(bieutong12!Ac11:Ac100,B15)"       'Yeu nuoc
            Range("j16").Value = "=COUNTIFS(bieutong12!Ac11:Ac100,B16)"
            Range("j17").Value = "=COUNTIFS(bieutong12!Ac11:Ac100,B17)"

Range("k15").Value = "=COUNTIFS(bieutong12!Ad11:Ad100,B15)"       'Nhan ai
            Range("k16").Value = "=COUNTIFS(bieutong12!Ad11:Ad100,B16)"
            Range("k17").Value = "=COUNTIFS(bieutong12!Ad11:Ad100,B17)"

Range("L15").Value = "=COUNTIFS(bieutong12!Ae11:Ae100,B15)"       'Cham chi
            Range("L16").Value = "=COUNTIFS(bieutong12!Ae11:Ae100,B16)"
            Range("L17").Value = "=COUNTIFS(bieutong12!Ae11:Ae100,B17)"

Range("M15").Value = "=COUNTIFS(bieutong12!Af11:Af100,B15)"       'Trung thuc
            Range("M16").Value = "=COUNTIFS(bieutong12!Af11:Af100,B16)"
            Range("M17").Value = "=COUNTIFS(bieutong12!Af11:Af100,B17)"

Range("N15").Value = "=COUNTIFS(bieutong12!Ag11:Ag100,B15)"       'trach nhiem
            Range("N16").Value = "=COUNTIFS(bieutong12!Ag11:Ag100,B16)"
            Range("N17").Value = "=COUNTIFS(bieutong12!Ag11:Ag100,B17)"

Range("C15:N17").Value = Range("C15:N17").Value
'Mo lai bao loi
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Tự thêm gia vị

Sub ABC()
  Dim sArr(), aDK(), aCol, Res()
  Dim sRow&, sR&, sC&, i&, r&, j&, dk

sArr = Sheets("bieutong12").Range("F11:U100").Value
  aDK = Range("B6:B9").Value
  aCol = Array("", 1, 3, 5, 6, 8, 10, 12, 13, 14, 15, 16)
  sRow = UBound(sArr)
  sR = UBound(aDK): sC = UBound(aCol)
  ReDim Res(1 To sR, 1 To sC)
  For i = 1 To sR
    dk = aDK(i, 1)
    If dk <> Empty Then
      For r = 1 To sRow
        For j = 1 To sC
          If sArr(r, aCol(j)) = dk Then Res(i, j) = Res(i, j) + 1
        Next j
      Next r
    End If
  Next i
  Range("C6:M9") = Res

sArr = Sheets("bieutong12").Range("V11:AG100").Value
  aDK = Range("B15:B17").Value
  aCol = Array("", 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
  sRow = UBound(sArr)
  sR = UBound(aDK): sC = UBound(aCol)
  ReDim Res(1 To sR, 1 To sC)
  For i = 1 To sR
    dk = aDK(i, 1)
    If dk <> Empty Then
      For r = 1 To sRow
        For j = 1 To sC
          If sArr(r, aCol(j)) = dk Then Res(i, j) = Res(i, j) + 1
        Next j
      Next r
    End If
  Next i
  Range("C15:N17") = Res
End Sub

www.giaiphapexcel.com/diendan/threads/c%C3%A1ch-s%E1%BB%AD-dung-h%C3%A0m-countif-trong-vba.71363/post-1027207

Khóa học SprinGO phù hợp

Học Nhân sự Tổng hợp – Trở thành chiến binh nhân sự vững nghiệp vụ

Con người là một trong những yếu tố quan trọng của công ty, là tài sản quý giá của doanh nghiệp. Chính vì thế,...

Xem khóa học