aer

TEST-°ÊºA°}¦C1.rar (12.53 KB)

¤U¸ü¦¸¼Æ: 6

2021-6-17 15:58

¦^´_ 4# jsc0518
§ï°ÊºA°}¦C¸Õ¸Õ

samwang

¦^´_ 5# jsc0518

½Ð¦A¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub test2()
Dim Arr, xD, xD1, T1, i&
Set xD = CreateObject("Scripting.Dictionary")
Set xD1 = CreateObject("Scripting.Dictionary")
Arr = Range([c1], [a65536].End(3))
For i = 2 To UBound(Arr)
    T1 = Arr(i, 1)
    xD(T1 & "") = xD(T1 & "") + 1
    xD1(T1 & "") = xD1(T1 & "") + Arr(i, 3)
Next
Arr = Range([g2], [f65536].End(3))
For i = 1 To UBound(Arr)
    T1 = Arr(i, 1)
    Arr(i, 1) = xD(T1 & "")
    Arr(i, 2) = xD1(T1 & "")
Next
Range("g2").Resize(UBound(Arr), 2) = Arr
End Sub

jsc0518

¦^´_ 3# samwang
Dear samwang,
±z¦n¡I·PÁ±z¼ö¤ßªº«ü¾É¦^ÂÐ
°õ¦æVBA«á¡A¦bG2~G4Äõ©_¼Æ¾Ú¨Ã¥¼¦]§å¸¹¦h¹è¦Ó¦Û°Ê­pºâÅÜ°Ê
¦p¤U¹Ï

¤U¸ü (345.61 KB)

2021-6-17 14:35

jsc0518

¦^´_ 2# aer
Dear aer,
±z¦n¡I«D±`·PÁ±zªº¼ö¤ß«ü¾É¡A¤½¦¡´ú¸Õ«áµo²{¤@­Ó°ÝÃD
·í§Ú§â¨Ó·½¸ê®Æ§R°£¤@Äæ¦C®É¡A¤½¦¡·|¦³¿ù»~¡A³o³¡¤À¬O§_¥i¥H­×§ï©O¡H
¦]§Úªº¸ê®Æ¨C¤ë³£·|²£¥X(·|·s¼W)¡A©Ò¥HÄæ¦ìµLªk©T©w¡C
ÁÂÁ±z¡I

¤U¸ü (326.03 KB)

2021-6-17 14:30

samwang

¦^´_ 1# jsc0518

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub test()
Dim Arr, xD, xD1, T1, TT, i&
Set xD = CreateObject("Scripting.Dictionary")
Set xD1 = CreateObject("Scripting.Dictionary")
Arr = Range([c1], [a65536].End(3))
For i = 2 To UBound(Arr)
    T1 = Arr(i, 1): TT = Arr(i, 1) & Arr(i, 2)
    If xD.Exists(TT) Then
        xD(TT & "") = xD(TT & "") + 1
        xD(T1 & "") = xD(TT & "")
    Else
        xD(TT & "") = 1: xD(T1 & "") = 1
    End If
    xD1(T1 & "") = xD1(T1 & "") + Arr(i, 3)
Next
Arr = Range([g2], [f65536].End(3))
For i = 1 To UBound(Arr)
    Arr(i, 1) = xD(Arr(i, 1) & "")
    Arr(i, 2) = xD1(Arr(i, 1) & "")
Next
Range("g2").Resize(UBound(Arr)) = Arr
End Sub

aer

¦^´_ 1# jsc0518
½Ð°Ñ¦Ò
G3=SUMPRODUCT((1/COUNTIF($B$2:$B$10,$B$2:$B$10)*($A$2:$A$10=F2)))

H3=SUMIF($A$2:$A$10,F2,$C$2:$C$10)