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

[µo°Ý] ¦p¦ó§P©w®Æ¸¹+§å¸¹¨ä¥Í²£¤Ñ¼Æ

[µo°Ý] ¦p¦ó§P©w®Æ¸¹+§å¸¹¨ä¥Í²£¤Ñ¼Æ

Dear all,
¹Ïªí»¡©ú¦p¤U¹Ï¡C¬O§_¦³¤½¦¡¥i¥H±a¥X§Ú·Q­nªºµ²ªG¡A²{ªp¬O¥H¤H¤u­pºâ¡A±`·|¥X¿ù
Thank you.

133.rar (8.86 KB)

Noname.jpg
2021-11-19 13:33
Just do it.

¦^´_ 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

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 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.
Just do it.

TOP

¦^´_ 2# samwang
Dear samwang,
±ß¤W¦n¡I·PÁ±zªº¼ö¤ß¦^ÂлP«ü¾É¼Ú
­è­è´ú¸Õ¤@¤U»Pªk¦¡OKªº¡A·P®¦
§Úµ¥µ¥·|¦b¤p§ï¤@¤U±zªº»yªk¡A¦³°ÝÃD¦A»P±z½Ð±Ð¼Ú¡I
Thank you.
Just do it.

TOP

¦^´_ 2# samwang
Dear samwang,
§Ú­×§ï¤F»yªk¡A¥X²{400¿ù»~¡C
»¡©ú¤@¤U¡A§Ú§â­ì¥»A~CÄæ¦ìªº¤u§@ªí©R¦W¬°"01"
¦Ó­ì¥»©óG~IÄæ¦ì§Ú§â¥¦©ñ¦b"02"(¤u§@ªí¦WºÙ)
¦A½Ð±z«ü¾É§Ú


»yªk§ó§ï¦p¤U

Sub test()
Dim Arr, xD, Brr(), T$, T1$, i&, n%, m%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([01!a1], [01!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([02!g2]).Resize(n, 3)
    .Value = Brr
    .Sort Key1:=.Item(1), Order1:=1, _
          Key2:=.Item(2), Order2:=1, Header:=2
End With
End Sub
Just do it.

TOP

J2 =COUNT(0/FREQUENCY((G2=B$2:B$99)*(H2=C$2:C$99)*A$2:A$99,A$2-2+ROW($1:$99)))-1
¤U©Ô
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# ML089
Dear ML089,
±ß¤W¦n¡I·PÁ±zªº¼ö¤ß¦^´_»P«ü¾É
´ú¸Õ¥i¥H¨Ï¥Î¼Ú¡I
Thank you.
Just do it.

TOP

¥»©«³Ì«á¥Ñ jsc0518 ©ó 2021-11-19 21:46 ½s¿è

¦^´_ 7# ML089
Dear ML089,
­è­è¸Õ¤F¤@¤U¡Aµo²{¦b¬Û¦P¤é´Á¦³¥X²{2¦¸
¦b¤½¦¡¤]¬O²Ö­p¨ì¦³2¦¸

¦ý§Ú§Æ±æ¬O·í¤é­Y¦³¥X²{2¦¸¥H¤W¡A¤½¦¡À°§Ú§PÂ_ºâ1¦¸


Thank you.
Just do it.

TOP

¦^´_ 6# jsc0518


¤è«Kªþ¥óÀɮ׶Ü?
ÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : ¯àµ½¥Î®É¶¡ªº¤H¡A¥²¯à´x´¤¦Û¤v§V¤Oªº¤è¦V¡C
ªð¦^¦Cªí ¤W¤@¥DÃD