ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦p¦ó±NCOUNTIF¤½¦¡ÂàVBA»yªk

[µo°Ý] ¦p¦ó±NCOUNTIF¤½¦¡ÂàVBA»yªk

Dear all,
§Ú¸ÕµÛ·Q§â¤U¦C¤½¦¡§ï¦¨VBA»yªk¡A¦ýµ²ªG¬O¥¢±Ñªº¡A¬O§_¥i¥HÀ°§Ú¬Ý¤@¤U»yªk­þ¸Ì¦³»~¡H
F2=COUNTIF(ú®w¶q!G$2:G$20000,A2)




Sub Countif()

    For i = 2 To 65536 Step 1
            For j = 2 To [g65536].End(3).Row
   
        Cells("G", i).Value = WorksheetFunction.CountIf(Sheets("ú®w¶q").Cells("G", j), Cells("A", i))
        
        Next j
    Next i

End Sub
Just do it.

¦^´_ 1# jsc0518

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, Brr, i&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([g2], [g65536].End(3))
For i = 1 To UBound(Arr): xD(Arr(i, 1) & "") = 1: Next
Brr = Range([a2], [a65536].End(3))
For i = 1 To UBound(Brr): Brr(i, 1) = xD(Brr(i, 1) & ""): Next
Range("f2").Resize(UBound(Brr)) = Brr
End Sub

TOP

¦^´_ 2# samwang
Dear samwang,
¤È¦w¡I·PÁ±zªº¦^ÂСC
©êºp°ÝÃD¡BÀÉ®×¥¼´£¨Ñ»¡©ú²M·¡¡C§Úªºµ²ªGµe­±¦p¤U¹Ï¡A¦ý§Ú²{¦b¬O¥ÎExcel¤½¦¡¡A·Q§âCOUNTIF¤½¦¡ÂàVBA»yªk
Noname.jpg
2021-11-22 14:57


TEST.rar (8.6 KB)
Just do it.

TOP

¦^´_  samwang
Dear samwang,
¤È¦w¡I·PÁ±zªº¦^ÂСC
©êºp°ÝÃD¡BÀÉ®×¥¼´£¨Ñ»¡©ú²M·¡¡C§Úªºµ²ªGµe­±¦p¤U¹Ï ...
jsc0518 µoªí©ó 2021-11-22 14:57


½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, Brr, xD, i&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([ú®w¶q!g2], [ú®w¶q!g65536].End(3))
For i = 1 To UBound(Arr): xD(Arr(i, 1) & "") = xD(Arr(i, 1) & "") + 1: Next
With Sheets("Analysis")
    Brr = .Range(.[a2], .[a65536].End(3))
    For i = 1 To UBound(Brr):
        If xD(Brr(i, 1) & "") > 0 Then
            Brr(i, 1) = xD(Brr(i, 1) & "")
        Else
            Brr(i, 1) = 0
        End If
    Next
    .Range("f2").Resize(UBound(Brr)) = Brr
End With
End Sub

TOP

¦^´_ 4# samwang
Dear samwang,
´ú¸ÕOK¡A·PÁ±zªºÀ°¦£¡I¤j·P®¦¡I
Just do it.

TOP

Sub Countif()

    a = Sheets("ú®w¶q").Range("G65536").End(3).Row
    b = ActiveSheet.Range("A65536").End(3).Row
   
    For i = 2 To b Step 1
   
        ActiveSheet.Range("G" & i).Value = WorksheetFunction.Countif(Sheets("ú®w¶q").Range("G2:G" & a), ActiveSheet.Range("A" & i).Value)
        
    Next i

End Sub
·s¤â¤W¸ô¡A½Ð¦h¥]²[¡C

TOP

Sub Macro1()
    Set sh = Sheets("Analysis")
    With Range(sh.[A2], sh.[A2].End(xlDown))
        .Offset(, 4).Formula = "=COUNTIF(ú®w¶q!G:G,A2)"
        .Offset(, 4).Value = .Offset(, 4).Value
    End With
End Sub
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# ML089
Dear ML089,
±ß¤W¦n¡I·PÁ±zªº¼ö¤ß«ü¾É»P¤À¨É¡I
¸Õ¥Î¹L«á¥i¥Î¡C¤Ó´ÎÅo¡I¤j·PÁ¡I
^^
Just do it.

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD