shuo1125

¦^´_ 3# hcm19522
hcm¤j¯uªº¬O§â¨ç¼Æµo´§ªº²OºvºÉ­P....ÁÂÁ¤F!
¦ý§Ú¤ñ¸û°¾¦V¥ÎVB³B²z¡C

hcm19522

B8:G10=COUNTIFS(¸ê®Æ!$A:$A,²Î­p!B$7,¸ê®Æ!$D:$D,"<="&CHOOSE(ROW(A1),5000,10000,10^9))-SUM(B$7:B7)+B$7

Andy2483

¦^´_ 1# shuo1125


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨ÒÀÉ
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

¸ê®Æªí:

¤U¸ü (132.29 KB)

2023-6-7 10:06

²Î­pªí°õ¦æ«e:

¤U¸ü (67.6 KB)

2023-6-7 09:46

°õ¦æµ²ªG:

¤U¸ü (94 KB)

2023-6-7 09:47

³o¬O¤£¦Û°Ê(ÂX¼W©ÎÁY´î)¦~¤ÀÄæ¦ìªº¤è®×(¥Î¤½¦¡À³¸Ó¤]¯à³B²z)

Option Explicit
Sub TEST()
Dim Brr, V&, Y, Z, i&, j%, T$, xR As Range
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([¸ê®Æ!D2], [¸ê®Æ!A65536].End(3))
For i = 1 To UBound(Brr)
   T = Brr(i, 1): V = Brr(i, 4)
   Z = Switch(V <= 5000, "5000¥H¤U", (V > 5000) * (V <= 10000), "5001~10000", V > 10000, "10000¥H¤W")
   Y(T & "|" & Z) = Y(T & "|" & Z) + V: Y(T & "|" & Z & "|Qty") = Y(T & "|" & Z & "|Qty") + 1
Next
'=========================================
[²Î­p!B2:G4,²Î­p!B8:G10].ClearContents
Set xR = [²Î­p!A1:G4]: Brr = xR
For i = 2 To UBound(Brr)
   For j = 2 To UBound(Brr, 2): Brr(i, j) = Y(Brr(1, j) & "|" & Brr(i, 1)): Next
Next
xR = Brr
'=========================================
Set xR = [²Î­p!A7:G10]: Brr = xR
For i = 2 To UBound(Brr)
   For j = 2 To UBound(Brr, 2): Brr(i, j) = Y(Brr(1, j) & "|" & Brr(i, 1) & "|Qty"): Next
Next
xR = Brr
Set Y = Nothing: Set xR = Nothing: Erase Brr
End Sub