cypd

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

¯u¬O¤Ó±j¤F!!  ·PÁ ª©¥D ­ã´£³¡ªL ...^^

ª©¥D¥[±j¤F¥H¤U¤½¦¡
Sub ¬y¤ô½s¸¹1()
Dim Arr, i&, P%, V&, N&, T1$, T2$, TT$, x%, Y&, SS(2)
Arr = Range([c1], [b65536].End(xlUp)(1, 0))
For i = 3 To UBound(Arr)
    If Arr(i, 1) = "¦X­p" Then Exit For
    N = N + 1: Arr(i - 2, 1) = ""
    P = Val(Arr(i, 2)): V = Val(Arr(i, 3))
    x = Switch(P = 100, 1, P = 200, 2, P = 500, 2, P = P, 0)
    If P = 0 Or V = 0 Or x = 0 Then GoTo i01
    Y = Array(64564, 81140)(x - 1): TT = Array("A", "B")(x - 1)
    T1 = TT & Format(Y + SS(x) + 1, "0000000")
    T2 = TT & Format(Y + SS(x) + V, "0000000")
    Arr(i - 2, 1) = T1 & IIf(T1 = T2, "", "-" & T2)
    SS(x) = SS(x) + V
i01: Next i
[d3].Resize(N) = Arr
End Sub

©Ò­z°ÝÃD¤w§¹¬ü³B²z...·P®¦

¤U¸ü (75.02 KB)

2021-12-15 12:57

Ãö©ó¨ç¼Æ¤½¦¡ REPLACE(TEXT(MMULT((IF(B3=100,64564,81140)+SUMIF(B$2:B2,B3,C$2:C2)+C3^{0,1}),10^{7;0}),REPT(IF(B3=100,"A","B")&"0000000",2)),9,,"-")

¤U¸ü (177.08 KB)

2021-12-15 13:00

¦¹°ÝÃD¦³¨ä¥L¤è¦¡¥i­×¥¿¶Ü??

­ã´£³¡ªL

¦^´_ 31# cypd


³£¬O³Ì´¶³qªº¨ç¼Æ, ·|¦³¤@­Ó¥¿±`,¤@­Ó¿ù»~­È, ¤]³\­É§O¤Hªº¹q¸£¸Õ¸Õ,
¥i¯à­n­«·s¦w¸ËEXCEL

cypd

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

«D±`·PÁ±z

¸ÓÀÉ®×¥H
®a¤¤®à¾÷*1(WIN7+2013)+µ§¹q*2(WIN10+2013 and WIN7+2016)
¤½¥q®à¾÷*1(WIN10+2010)

´ú¸Õµ²ªG­±ÃB  500 ¤§Àx¦s®æ¥X²{ #VALUE!  ¤§°ÝÃD¤´¤@¼Ë¦s¦b...

Andy2483

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


    ÁÂÁ½׾Â,ÁÂÁ«e½ú«ü¾É
«á¾Ç¾Ç²ß«e½úªº¤è®×,¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É

Sub ¬y¤ô½s¸¹1()
Dim Arr, i&, V&, N&, Y&, T1$, T2$, TT$, P%, x%, SS(2)
'¡ô«Å§iÅܼÆ:Arr¬O³q¥Î«¬ÅܼÆ,(i,V,N,Y)¬Oªø¾ã¼ÆÅܼÆ,(T1,T2,TT)¬O¦r¦êÅܼÆ,
'(P,x)¬Oµu¾ã¼ÆÅܼÆ,SS¬O¤@ºû°}¦C(¯Á¤Þ¸¹0~2)
Arr = Range([c1], [b65536].End(xlUp)(1, 0))
'¡ô¥OArr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥H[C1]¨ì BÄæ³Ì«á¦³¤º®eÀx¦s®æ¥ª°¼®æ(AÄæ),
'¥H³o½d³òÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 3 To UBound(Arr)
'¡ô³]¶¶°j°é!i±q3¨ìArr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
    If Arr(i, 1) = "¦X­p" Then Exit For
    '¡ô¦pªGi°j°é¦C²Ä1ÄæArr°}¦C­È¬O "¦X­p"? True´Nµ²§ô°j°é°õ¦æ
    N = N + 1: Arr(i - 2, 1) = ""
    '¡ô¥ON³oªø¾ã¼ÆÅܼƲ֥[ 1
    '¥O(i°j°é-1)¦C²Ä1ÄæArr°}¦C­È¬O ªÅ¦r¤¸
    P = Val(Arr(i, 2)): V = Val(Arr(i, 3))
    '¡ô¥OP³oµu¾ã¼Æ¬Oi°j°é¦C²Ä2ÄæArr°}¦C­È
    '¥OV³oªø¾ã¼Æ¬Oi°j°é¦C²Ä3ÄæArr°}¦C­È
    x = Switch(P = 100, 1, P = 200, 2, P = 500, 2, P = P, 0)
    '¡ô¥Ox³oµu¾ã¼Æ¬OSwitch()¨ç¦¡¦^¶Ç­È
    https://learn.microsoft.com/zh-t ... elp/switch-function
    If P = 0 Or V = 0 Or x = 0 Then GoTo i01
    '¡ô¦pªGPÅܼƬO0 ©ÎV¬O0 ¤S©Îx¬O0,¨ä¤¤¤@±ø¥ó¦¨¥ß!´N¸õ¨ì ¼Ð¥Üi01ªº¦ì¸mÄ~Äò°õ¦æ
    Y = Array(64564, 81140)(x - 1): TT = Array("A", "B")(x - 1)
    '¡ô¥OY³oªø¾ã¼ÆÅܼƬO ¤@ºû°}¦Cªº¯Á¤Þ¸¹(xÅܼÆ-1)°}¦C­È
    '¥OTT³o¦r¦êÅܼÆÅܼƬO ¤@ºû°}¦Cªº¯Á¤Þ¸¹(xÅܼÆ-1)°}¦C­È
    T1 = TT & Format(Y + SS(x) + 1, "0000000")
    '¡ô¥OT1ÅܼƬO TTÅܼƳs±µ (YÅܼÆ+x¯Á¤Þ¸¹SS°}¦C­È¦A+1)Âন7½X¼Æ­È,©Ò²Õ¦¨ªº·s¦r¦ê
    T2 = TT & Format(Y + SS(x) + V, "0000000")
    '¡ô¥OT2ÅܼƬO TTÅܼƳs±µ (YÅܼÆ+x¯Á¤Þ¸¹SS°}¦C­È¦A+VÅܼÆ)Âন7½X¼Æ­È,©Ò²Õ¦¨ªº·s¦r¦ê
    Arr(i - 2, 1) = T1 & IIf(T1 = T2, "", "-" & T2)
    '¡ô¥O(i°j°é-2)¦C²Ä1ÄæArr°}¦C­È¬O T1ÅܼƳs±µ ªÅ¦r¤¸©Î ("-"³s±µT2ÅܼƲզ¨¦r¦ê)
    'IIf():¦pªGT1ÅܼƦPT2ÅܼÆ(§Y±i¼Æ¬O1±i)!´N¦^¶Ç ªÅ¦r¤¸,§_«h¦^¶Ç¦r¦ê
    SS(x) = SS(x) + V
    '¡ô¥OxÅܼƯÁ¤Þ¸¹SS°}¦C­È¬O ²Ö¥[VÅܼƪº¼Æ­È
i01: Next i
[d3].Resize(N) = Arr
'¡ô¥O[D3]ÂX®i¦V¤U NÅܼƦCÀx¦s®æ­È ¥HArr°}¦C­È±a¤J
End Sub
'============================================================

Option Explicit
Sub TEST()
Dim Brr, E, P, Q&, i&, H&, c, K%, S&(1), T$, Ts$, Te$
c = Application.Match("¦X­p", [A:A], 0)
If IsError(c) Then Exit Sub
Brr = [B3].Resize(c - 3, 3)
E = Array(64564, 81140): P = Array("A", "B")
For i = 1 To UBound(Brr)
   Q = Val(Brr(i, 1)): H = Val(Brr(i, 2))
   K = Switch(Q = 100, 0, InStr("200/500", Q), 1, Q = Q, -1)
   T = P(K) & "0000000": Brr(i, 1) = ""
   If (Q = 0) + (H = 0) + (K = -1) < 0 Then GoTo i01
   S(K) = E(K) + 1: E(K) = E(K) + H
   Ts = Format(S(K), T): Te = Format(E(K), T)
   Brr(i, 1) = Ts & IIf(S(K) = E(K), "", "-" & Te)
   S(K) = E(K)
i01: Next
[I3].Resize(UBound(Brr)) = Brr
End Sub

hcm19522

(¿é¤J½s¸¹11999) googleºô§}:https://hcm19522.blogspot.com/

cypd

¦^´_ 35# hcm19522

·PÁ hcm19522  ¼ö¤ß«ü¾É
¥i¾A¥Î¦UºØ¹ô­È±i¼Æ¬y¤ô½s¸¹©O  ^^

cypd

¦^´_ 34# Andy2483

«D±`·PÁ  Andy2483 ´£¨Ñªº¸Ô²Ó¸Ñ»¡µ{¦¡½X¥Nªí§t·N¤Î¬ÛÃöVBA...
­Y¬O¦]¥t¦³»Ý­n®É
©Ò»Ý­±ÃB ¦P®É§t¦³  100  200  500  1000  ...¦UºØ­±ÃB®É(«e¦r­y­^¤å¦r¥À¦U¤£¦P)
100(A0000125)  200(B0000100)  500(C0000085)  1000(D0000055)

¸Ó¦p¦ó­×¥¿ ??

Andy2483

¦^´_ 37# cypd


    ÁÂÁ«e½ú¦^´_
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò
20231115.zip (13.71 KB)

¤U¸ü¦¸¼Æ: 3

2023-11-15 08:43

°õ¦æ«e:

¤U¸ü (79.21 KB)

2023-11-15 08:42

°õ¦æµ²ªG:

¤U¸ü (115.63 KB)

2023-11-15 08:42

Option Explicit
Sub TEST_1()
Dim Brr, Z, C, A(2), P%, Q%, i%, V%, F$, T$, Ts$, Te$
Set Z = CreateObject("Scripting.Dictionary")
C = Application.Match("¦X­p", [A:A], 0)
If IsError(C) Then Exit Sub Else F = "0000000"
Brr = [B3].Resize(C - 3, 3)
A(0) = [{1,2,5,10}]
A(1) = [{124,99,84,54}]
A(2) = [{"A","B","C","D"}]
For i = 1 To UBound(A(0)):
   Q = A(0)(i) * 100: Z(Q) = A(1)(i): Z(Q & "") = A(2)(i)
Next
For i = 1 To UBound(Brr)
   P = Brr(i, 1): V = Z(P): T = Z(P & "")
   Ts = T & Format((V + 1), F)
   V = V + Brr(i, 2): Z(P) = V
   Te = T & Format((V), F)
   Brr(i, 1) = Ts & "-" & Te
Next
[D3].Resize(UBound(Brr)) = Brr
End Sub

cypd

¦^´_ 38# Andy2483

¦A·PÁ  Andy2483 ¼ö¤ß´£¨Ñªº¸Ô²ÓVBA...
1.·s¼W J2:L6 ­±ÃB¬ÛÃö¬y¤ô½s¸¹¸ê°T(§Æ±æ¥H¦¹¸ê°TÄæ¦ì¼Æ¾Ú¬°¥D)
2.­±ÃB±i¼Æ¦³ 0 ¥X²{¤§±¡§Î...
3.­±ÃB¹ô­È³¡¤À A(0) = [{1,2,5,10}]¡KQ = A(0)(i) * 100: Z(Q) = A(1)(i): Z(Q & "") = A(2)(i)
  ¤£¸Ñ¬°¦ó¬O [{1,2,5,10}]¡KQ = A(0)(i) * 100 ªº¤è¦¡?¤£¬Oª½±µ¥H 100 200 500 1000¡K¤è¦¡§e²{

¤U¸ü (125.28 KB)

2023-11-15 14:18

20231115+.rar (13.5 KB)

¤U¸ü¦¸¼Æ: 2

2023-11-15 14:19

Andy2483

¦^´_ 39# cypd


    ÁÂÁ½׾Â,ÁÂÁ«e½ú«ü¾É
«á¾ÇÂǦ¹©«½m²ß­×§ï·s¤è®×,¾Ç²ß¨ì«Ü¦hª¾ÃÑ,½Ð«e½ú¦A«ü¾É

20231115+_20231115.zip (15.48 KB)

¤U¸ü¦¸¼Æ: 4

2023-11-15 16:39

°õ¦æ«e:

¤U¸ü (154.95 KB)

2023-11-15 16:40

°õ¦æ3¦¸:

¤U¸ü (211.91 KB)

2023-11-15 16:40

Option Explicit
Sub TEST_1()
Dim Brr, Z, C, A(2), P1&, P2&, Q%, i&, j%, V&, F$, T$, Ts$, Te$, R&, U%
Set Z = CreateObject("Scripting.Dictionary")
U = [IV2].End(xlToLeft).Column: Brr = Range(Cells(2, U), [H65536].End(3)(1, 2))
For j = 1 To UBound(Brr, 2): Z(Val(Brr(1, j))) = Val(Brr(UBound(Brr), j)): Next
For j = 0 To UBound(Z.KEYS()): Z(Z.KEYS()(j) & "") = Brr(2, j + 1): Next
R = UBound(Brr): C = Application.Match("¦X­p", [A:A], 0)
If IsError(C) Then Exit Sub Else F = Application.Rept("0", 7)
Brr = [B3].Resize(C - 3, 3)
For i = 1 To UBound(Brr)
   P1 = Val(Brr(i, 1)): P2 = Val(Brr(i, 2)): Brr(i, 1) = ""
   If P1 * P2 = 0 Then GoTo i01
   V = Z(P1): T = Z(P1 & ""): If V = 0 Then GoTo i01
   Ts = T & Format((V + 1), F)
   V = V + Brr(i, 2): Z(P1) = V
   Te = T & Format((V), F)
   Brr(i, 1) = Ts & "-" & Te
i01: Next
[D3].Resize(UBound(Brr)) = Brr
Cells(R + 2, "I").Resize(1, U - 8) = Z.ITEMS: Cells(R + 2, "I").Item(1, 0) = Now
End Sub