[¤À¨É] §PÂ_¥X²{¤£¦P§å¸¹´X¦¸

jsc0518

¤j®a¦n¡I§Ú¦³°ÝÃD·Q½Ð±Ð
1.¸ê®Æ¤¤¦³®Æ¸¹¡A©Ò¥H§Ú¥i¥H§Q¥Î¤½¦¡§ì¥X¨Ó¦oªºÁ`¼Æ¶q
2.¦ý¬O¡A¨C¤@®Æ¸¹¤U³£·|²£¥X³\¦h§å¸¹(¦]¬°¬O¤£¦P¤Ñ²£¥X)
3.§Ú·Q­n§â¸ê®ÆÅܦ¨¤U¹Ï¤º®e¡A¥kÃ䪺Äæ¦ì¼Ë¤l

2021-6-16 20:11

ÁٽФj¤jÀ°À°§Ú
·P®¦


TEST.rar (8.98 KB)

¤U¸ü¦¸¼Æ: 11

2021-6-16 20:11

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)

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

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

2021-6-17 14:30

jsc0518

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

2021-6-17 14:35

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

aer

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

¤U¸ü¦¸¼Æ: 6

2021-6-17 15:58

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

hcm19522

https://blog.xuite.net/hcm19522/twblog/589839401

samwang

¦^´_ 5# jsc0518

¤£¦n·N«ä¡A¬Ý¤F¨ä¥L¤j¤j¸Ñµª¡A²×©ó¤F¸Ñ±zªº»Ý¨D¤F¡A6¼Óµ{¦¡½Ð©¿²¤¡A¤U­±µ{¦¡½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C

Sub test3()
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 Not xD.Exists(TT) Then
         xD(TT & "") = xD(TT & "") + 1
         xD(T1 & "") = xD(T1 & "") + xD(TT & "")
     End If
     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

¦^´_ 7# aer

Hi ±z¦n¡I·PÁ±zªº¼ö¤ßÀ°¦£¡A
­è­èRUN¤F¤@¤U»yªk¡Aµ²ªG¬O§Ú­nªº¡A¦ý»Ý­n¶]¤@¨Ç®É¶¡(³s¦sÀɮ׳£­n¤@ÂI®É¶¡)¡A§Úªº¸ê®Æ¥Ø«e¬ù¦³2381µ§