¦r¨å¨Ì±ø¥ó­pºâ¦U¤ëª÷ÃB»P¼Æ¶q

shuo1125

¦U¦ì«e½ú¦n!
½Ð°Ý·Q±q¸ê®Æªí¤¤­pºâ¦U¤ë¤ä¥IÁ`ÃB¤Î¼Æ¶q¿é¥X©ó²Î­pªí¤¤¡A
³o­Ó­Y­n¥Î¦r¨å¤è¦¡¨Ó¦p¦ó°µ..?
¦pªþÀÉ¡A·Ð½Ð°ª¤âÀ°¦£¸Ñµª¡A
ÁÂÁ¤j®a!!

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

hcm19522

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

shuo1125

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

shuo1125

¦^´_ 2# Andy2483
·PÁÂAndy¤jÁ`¬O§Ö³tªº¸Ñµª....

Andy2483

¦^´_ 5# shuo1125

ÁÂÁ«e½ú¦^´_
«á¾Ç¦AÀ˵ø¨Ã°µ¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú°Ñ¦Ò

Option Explicit
Sub TEST()
Dim Brr, V&, Y, Z, i&, j%, T$, xR As Range
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([¸ê®Æ!D2], [¸ê®Æ!A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥H¸ê®ÆªíA~DÄæÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!±N¦U²Õ¦Xkey©w¸q¨äitem­È¯Ç¤JY¦r¨å¤¤
   T = Brr(i, 1): V = Brr(i, 4)
   '¡ô¥O°}¦C­È¥HÅܼƲ±¸Ë
   Z = Switch(V <= 5000, "5000¥H¤U", (V > 5000) * (V <= 10000), "5001~10000", V > 10000, "10000¥H¤W")
   '¡ô¥OZÅܼÆ=Switch(±ø¥ó1,­È1,±ø¥ó2,­È2,±ø¥ó3,­È3)
   Y(T & "|" & Z) = Y(T & "|" & Z) + V: Y(T & "|" & Z & "|Qty") = Y(T & "|" & Z & "|Qty") + 1
   '¡ô¥O²Õ¦X¦r¦ê·íkey,item¬O°}¦C­È²Ö¥['¡ô¥O²Õ¦X¦r¦ê·íkey,item¬OÃþ§O­Ó¼Æ²Ö¥[
Next
'=========================================
[²Î­p!B2:G4,²Î­p!B8:G10].ClearContents
'¡ô¥O²Î­pªí¸ê®Æ²M°£
Set xR = [²Î­p!A1:G4]: Brr = xR
'¡ô¥OxRÅܼƬO Àx¦s®æ,¥OBrr´«¸ËxR(·sÀx¦s®æ­È)
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!
   For j = 2 To UBound(Brr, 2): Brr(i, j) = Y(Brr(1, j) & "|" & Brr(i, 1)): Next
   '¡ô³]¶¶°j°é!¥O°j°é°}¦C­È¬O(¥H¼ÐÃD¦C³s±µ"|",¦A³s±µ¼ÐÃDÄ檺²Õ¦X¦r¦ê¬dY¦r¨å±oitem­È)
Next
xR = Brr
'¡ô¥OÀx¦s®æ­È¬OBrr°}¦C­È
'=========================================
Set xR = [²Î­p!A7:G10]: Brr = xR
'¡ô¥OxRÅܼƬO Àx¦s®æ,¥OBrr´«¸ËxR(·sÀx¦s®æ­È)
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!
   For j = 2 To UBound(Brr, 2): Brr(i, j) = Y(Brr(1, j) & "|" & Brr(i, 1) & "|Qty"): Next
   '¡ô³]¶¶°j°é!
   '¥O°j°é°}¦C­È¬O(¥H¼ÐÃD¦C³s±µ"|",¦A³s±µ¼ÐÃDÄæ,³Ì«á³s±µ"|Qty"ªº²Õ¦X¦r¦ê¬dY¦r¨å±oitem­È)
Next
xR = Brr
'¡ô¥OÀx¦s®æ­È¬OBrr°}¦C­È
Set Y = Nothing: Set xR = Nothing: Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub

­ã´£³¡ªL

Z = Switch(V > 10000, "10000¥H¤W", V > 5000, "5001~10000", V = V, 5000¥H¤U")

shuo1125

¦^´_ 7# ­ã´£³¡ªL
­ã¤j¦n!
·Q½Ð°Ý­Y­n¦Û°ÊÂX¥R¦~¤ë¤Î¦X­p...³o¦³¿ìªk³B²z¶Ü?
§Ú¦³´¿¸g¬Ý¹L§A¼gÃþ¦üªº...¦ý§Ú¹ê¦b¤£ª¾¹D«ç½Õ¾A...
(Xl0000789)±zªº³o¥÷¬O¥H«È¤á¨Ó°µ²Î­p¤ÀÃþ»P§Ú³o°ÝÃD¦³ÂI¤£¤Ó¤@¼Ë¡A
ÁÂÁ§A¤F!!

Andy2483

¦^´_ 7# ­ã´£³¡ªL


    ÁÂÁ«e½ú«ü¾É

Z = Switch(V > 10000, "10000¥H¤W", V > 5000, "5001~10000", V = V, 5000¥H¤U")
1.10000¥H¤Wªºµ§¼Æ¸û5000¥H¤Uªºµ§¼Æ¦h
2.¤j¨ì¤p§PÂ_·|¦ÛµM²£¥ÍÅÞ¿è°Ï¶¡¬OSwitch()ªº­«­nÆ[©À

Andy2483

¦^´_ 1# shuo1125


    ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æµ²ªG:

¤U¸ü (167.89 KB)

2023-6-8 11:49

Option Explicit
Sub TEST_1() '¡ô
Dim Brr, Crr(1 To 100, 1 To 100), Z, A, B$, i&, R&, C%, Y&, X%, T$, K%, S%, N&, M, Q
Set Z = CreateObject("Scripting.Dictionary"): C = 1: R = 1
A = Array(0, 5000, 10000, 50000, 100000, 500000, 10 ^ 6, 5000000, 10 ^ 7, 10 ^ 10)
K = UBound(A): S = K + 3
For i = 0 To K - 1
   R = R + 1
   Crr(R, 1) = A(i) + 1 & "~" & vbLf & A(i + 1): Crr(R + S, 1) = Crr(R, 1)
   Z(A(i + 1) & "$") = R: Z(A(i + 1) & "N") = R + S
Next
Brr = Range([¸ê®Æ!D2], [¸ê®Æ!A65536].End(3))
Sheets("²Î­p").UsedRange.Clear
With Sheets("²Î­p").[A1:D4].Resize(UBound(Brr))
   .Value = Brr
   .Sort KEY1:=.Item(1), Order1:=1, Header:=2, Orientation:=1: Brr = .Value
   For i = 1 To UBound(Brr)
      T = Brr(i, 1)
      If Z(T & "y") = "" Then
         C = C + 1: Crr(1, C) = T: Z(T & "y") = C: Crr(S + 1, C) = T
      End If
   Next
   .Sort KEY1:=.Item(4), Order1:=1, Header:=2, Orientation:=1
   Brr = .Value: .Clear
End With
Crr(1, 1) = "·JÁ`-NTD": Crr(S + 1, 1) = "·JÁ`-QTY": B = "[Total]"
R = R + 1: Crr(R, 1) = B: Crr(R + S, 1) = B
C = C + 1: Crr(1, C) = B: Crr(S + 1, C) = B
'=========================================
For i = 1 To UBound(Brr)
   Q = Val(Brr(i, 4))
   If Q > M Then N = N + 1: M = A(N)
   X = Z(Brr(i, 1) & "y"): Y = Z(M & "$")
   Crr(Y, X) = Crr(Y, X) + Q: Crr(R, X) = Crr(R, X) + Q
   Crr(Y, C) = Crr(Y, C) + Q: Crr(R, C) = Crr(R, C) + Q
   Crr(Y + S, X) = Crr(Y + S, X) + 1: Crr(R + S, X) = Crr(R + S, X) + 1
   Crr(Y + S, C) = Crr(Y + S, C) + 1: Crr(R + S, C) = Crr(R + S, C) + 1
Next
With [²Î­p!A1].Resize(R + S, C)
   .Columns.ColumnWidth = 14
   Intersect(.Cells, Rows("1:" & K + 2)).Borders.LineStyle = 1
   Intersect(.Cells, Rows(S + 1 & ":" & R + S)).Borders.LineStyle = 1
   Union(.Rows(1), .Rows(K + 2), .Rows(S + 1)).Font.Bold = True
   Union(.Rows(R + S), .Columns(C)).Font.Bold = True
   Range([B2], .Cells(K + 2, C)).NumberFormatLocal = "#,##0_ "
   Range(.Cells(S + 2, 2), .Cells(R + S, C)).NumberFormatLocal = "#,##0_ "
   .Value = Crr
End With
Set Z = Nothing: Erase Brr, Crr, A
End Sub