jsc0518

¦^´_ 3# hcm19522

Dear hcm19522,

±z¦n¡I·PÁ±zªº¼ö¤ß«ü¾É»P¦^ÂСC

»P±z½Ð±Ð¡A·í¤½¦¡§Ú§ï¦¨

=SUMPRODUCT((B$2:B$20000&C$2:C$20000=G2&H2)/COUNTIFS(A:A,A$2:A$20000,B:B,B$2:B$20000,C:C,C$2:C$20000))

¥¦´N¦³ÂIÃþ¦ü·í¾÷¤@¼Ë¤£·|°õ¦æ¡A¬O§_¬O20000Äæ¦ì¤Ó¦h¤F¡H

Thank you.

hcm19522

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

samwang

¦^´_ 1# jsc0518

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, xD, Brr(), T$, T1$, i&, n%, m%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([a1], [c65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 3)
For i = 2 To UBound(Arr)
    T = Arr(i, 2) & "|" & Arr(i, 3)
    T1 = Arr(i, 1) & "|" & Arr(i, 2) & "|" & Arr(i, 3)
    If xD.Exists(T) Then
        m = xD(T)
        If Not xD.Exists(T1) Then Brr(m, 3) = Brr(m, 3) + 1
    Else
        n = n + 1: xD(T) = n: xD(T1) = n
        Brr(n, 1) = Arr(i, 2)
        Brr(n, 2) = Arr(i, 3)
        Brr(n, 3) = 1
    End If
Next
With Range("g2").Resize(n, 3)
    .Value = Brr
    .Sort Key1:=.Item(1), Order1:=1, _
          Key2:=.Item(2), Order2:=1, Header:=2
End With
End Sub