- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-10-13 15:19 ½s¿è
ÁÂÁ¦U¦ì«e½ú4545¤Ñ¥H¨Óªº°Ñ»P&¤À¨É&©^Äm....«ü¾É
³o½×¾Â«Ü¼F®`!ÁÂÁ½׾¹ζ¤!
¨«¹L¥²¯d¨¬¸ñ!
¥H¤U¤ß±o¤À¨É¨ÃÀµ½Ð¦A«ü¾É!
¦¹½m²ß±¡¹Ò¨S¦³¥[¤J¦P¤é´Áªº§PÂ_
Option Explicit
Sub °}¦C_¦r¨å½m²ß()
Application.ScreenUpdating = False
Dim Sh(2), K&, Ay, x, v&, NN
'¡ô«Å§iÅÜ¼Æ Sh(2):Sh(0)~Sh(2)
Set Sh(1) = Sheet1: Set Sh(2) = Sheet2
'¡ô¥OSh(1)¬O²Ä¤@Ó¤u§@ªí:¥OSh(2)¬O²Ä¤GÓ¤u§@ªí
Sh(2).UsedRange.EntireRow.Delete
'¡ô²Ä¤GÓ¤u§@ªí©Ò¦³¨Ï¥Î¹Lªº¦C²[»\ªº½d³ò¦C§R°£
Ay = Split("¯Z¦¸,³sÄò¦¸¼Æ,½s¸¹,¤é´Á,¯Z¦¸,¨®¸¹,®É¶¡,®É¶¡Âà´«,³t«×,³sÄò®É¶¡,³sÄò¶ZÂ÷", ",")
'¡ô¥OAy¬O¤@ºû°}¦C:¦r¦ê¥Î³rÂI,¤Á³Î¶}˶i¥h
Dim Arr, Brr(1 To 999, 1 To 12), Crr, xD, i&, j%, T1$, T2&, Tn&, T3%, T4&, TT$, z
Dim N&, xA As Range, Q&, CC&
'¡ô«Å§iÅܼÆ
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxD¬O¦r¨å
Arr = Sh(1).Range(Sh(1).Cells(1, 7), Sh(1).Cells(Rows.Count, 1).End(3).Item(2, 1))
'¡ô¥OArr¬O°}¦C:ªí1ªºG2¨ìAÄæ³Ì«áÀx¦s®æ½d³òªºÈˤJ
For i = 2 To UBound(Arr) - 1
T1 = Arr(i, 3)
'¡ô¥OT1¬O ¯Z¦¸
T2 = Arr(i, 1)
'¡ô¥OT2¬O ½s¸¹
T3 = Arr(i, 7)
'¡ô¥OT3¬O ³t«×
T4 = Arr(i, 6)
'¡ô¥OT4¬O ®É¶¡Âà´«
Tn = Arr(i + 1, 1)
'¡ô¥OTn¬O ¤UÓ½s¸¹
TT = T1 & "|" & T2 - Q
'¡ô¥OTT¬O ¯Z¦¸&"|"&½s¸¹ ²Õ¦Xªº¦r¦ê
'Qªì©lȬO0,©Ò¥Hi=1®ÉTT="801|1116"
xD(T2 - Q) = xD(T2 - Q) + 1
'¡ô¥O½s¸¹¬Okey,¶}©litem²Ö¥[ 1
'Qªì©lȬO0,key=1116,item= 0+1 =1
Crr = xD(TT & "/c")
'¡ô¥OCrr¨ì¦r¨å¸Ì§äkey= ¯Z¦¸&"|"&½s¸¹&"/c",³o²Õ¦X¦r¦êªºITEM
'¦]¬°§ä¤£¨ì©Ò¥H¬O µL
xD(TT) = xD(TT) + 1
'¡ô¥O¯Z¦¸&"|"&½s¸¹²Õ¦X¦r¦ê¬Okey,¶}©litem²Ö¥[ 1
'i=1®ÉTT="801/1116",item= 0+1 =1
If Not IsArray(Crr) And (Tn - T2 = 1) Then
'¡ô¦pªGCrr¤£¬O°}¦C ¥B¤U¤@Ó½s¸¹-½s¸¹=1
'i=1®É,Crr¤£¬O°}¦C¥B1117-1116=1 §k¦X±ø¥ó
Crr = Brr
'¡ô¥OCrr=Brr³oӪŰ}¦C
N = N + 1
'¡ô¥ON²Ö¥[ 1,³o¼Æ¦r¬On·íkey
',Nªì©lÈ=0,N²Ö¥[1=1
xD(N) = TT
'¡ôi=1®É¥O ¼Æ¦r1 ¬Okey,"801|1116"¦r¦ê¬Oitem
If Not xD.Exists(T1) Then
'¡ô¦pªG¯Z¦¸¦bxD¦r¨å¸ÌÁ٧䤣¨ì
'i=1®É ¯Z¦¸ "801" ³o¦r¦êkeyÁÙ¨S¦³¦bxD¦r¨å¸Ì
K = K + 1
'¡ô¥OK²Ö¥[ 1,³o¼Æ¦r¬On·íitem
',Nªì©lÈ=0,K²Ö¥[1=1
xD(T1) = K
'¡ôi=1®É¥O "801"¦r¦ê ¬Okey,item=1
End If
xD(TT & "/³t«×") = T3
'¡ô¥Oi=1®É "801|1116/³t«×" ¦r¦ê ¬Okey,item=52 ¼Æ¦r
xD(T1 & "/c") = xD(T1 & "/c") + 1
'¡ô¥Oi=1®É "801/c" ¦r¦ê ¬Okey,item²Ö¥[ 1
End If
If Tn - T2 = 1 Or xD(TT) > 1 Then
'¡ô¦pªG¤U¤@Ó½s¸¹-³o½s¸¹=1 ©Î key¬O TT ªºitem>1
'Or xD(TT) > 1¤]n§PÂ_!
'¬O¦]¬°½s¸¹³sÄòªº³Ì«á¤@Ó½s¸¹ªº¸ê®Æ¤]nˤJCrr°}¦C¸Ì
',§_«h·|º|±¼½s¸¹³sÄòªº³Ì«á¤@Ó½s¸¹ªº¸ê®Æ
For j = 3 To 9
'¡ô³]°j°éÅý²Ä3 ¨ì9Ä檺¸ê®ÆˤJCrr°}¦C¸Ì
Crr(xD(TT), j) = Arr(i, j - 2)
Next j
End If
If Tn - T2 = 1 Then
'¡ô¦pªG¤U¤@Ó½s¸¹-³o½s¸¹=1
Q = Q + 1
'¡ô¦pªG±ø¥ó¦¨¥ß Q²Ö¥[1,³o¬O¨ì¤U¤@Ói®Énµ¹«e¤è¦©±¼Q!
'§PÂ_¬O¤£¬O½s¸¹³sÄò¥Îªº
Else
'¡ô¦pªG±ø¥ó¤£¦¨¥ß!¤]¬O½s¸¹¤£³sÄòªº·N«ä
Q = 0
'¡ôQ´NÂk¹s
End If
'¡ô¦PQ = IIf(Tn - T2 = 1, Q + 1, 0)
If Q > 1 Or xD.Exists(TT & "/³sÄò®É¶¡") Then
'¡ô¦pªGQ(³sÄò½s¸¹¦¸¼Æ>1 ©Î xD¦r¨å¸Ì¦³key¬O TT & "/³sÄò®É¶¡"
xD(TT & "/³sÄò®É¶¡") = xD(TT & "/³sÄò®É¶¡") + (Arr(i, 6) - Arr(i - 1, 6))
'¡ô¦pªG±ø¥ó¦¨¥ß! key¬O TT & "/³sÄò®É¶¡"²Ö¥[ ¨C¬qªº®É¶¡
End If
xD(TT & "/c") = Crr
'¡ô¥O TT & "/c"³o¦r¦ê·íkey,item¬O°}¦CCrr
i01: Next i
'¡ô°j°éÁ`µ²´N¬O«Ü²Ö«ÜÃø!¤£ª¾¹D«ç»ò»¡¤F!
'¡õ³Ð¥ß¦r¨åˤJ¦r¨å Ãø! §â¸ê®Æ½Õ¥X¦r¨å§óÃø!
For Each z In xD.keys
'¡ô³]°j°é¥Oz¬OxD¦r¨åkey¸Ìªº¤@¥÷¤l,±q«e±¶]¨ì³Ì«á
If z Like "###|####" = False Then
'¡ô¦pªGz_key¤£¬O ¼Æ¦r¼Æ¦r¼Æ¦r|¼Æ¦r¼Æ¦r¼Æ¦r¼Æ¦r
GoTo 333
'¡ô¦pªG±ø¥ó¦¨¥ß´N¸õ¨ì 333ªº¦ì¸mÄ~Äò°õ¦æ
'¦]¬°§ÚÌn§ä ¯Z¦¸&"|"&½s¸¹ ²Õ¦Xªº¦r¦ê
End If
If xD(z) = 1 Then
'¡ô¦pªGz_item¬O1
GoTo 333
'¡ô¦pªG±ø¥ó¦¨¥ß´N¸õ¨ì 333ªº¦ì¸mÄ~Äò°õ¦æ
'¦]¬°¯Z¦¸&"|"&½s¸¹ ²Õ¦Xªº¦r¦ê¥u¦³1µ§ªº§Ṳ́]¤£n
End If
'¡õ±µ¤U¨Ó´Nn¶}©l§G¼ÐÃD¦C¸ò Ë¥X°}¦C¸ê®Æ¤F
T1 = Split(z, "|")(0)
'¡ô¥OT1¬O²Å¦X±ø¥óªº¯Z¦¸
v = xD(Split(z, "|")(0) & "/c")
'¡ô¥Ov¬O³o¯Z¦¸³sÄò¹Lªº¦¸¼Æ
CC = (xD(T1) - 1) * UBound(Brr, 2) + 1
'¡ô¥OCC¬O¼ÐÃD¦C¶}©lˤJªº°_©lÄæ¦ì¦ì¸m
NN = 1
'¡ô¥ONN()ªº°_©lȬO1
'¬O¼ÐÃD¦C¶}©lˤJªº°_©l¦C¦ì¦ì¸m1
If NN = 1 And Sh(2).Cells(1, CC) <> "" Then
'¡ô¦pªGNN¬O1 ¥Bªí2ªº ¼ÐÃD¦C¤w¸g¥Î¹L¤F
GoTo 333
'¡ô´N¸õ¨ì 333ªº¦ì¸mÄ~Äò°õ¦æ!±Æ°£¦r¨å¸Ì³B¸Ì¹Lªº¯Z¦¸
End If
For Each x In xD.keys
'¡ô³]°j°é¥Ox¬OxD¦r¨åkey¸Ìªº¤@¥÷¤l,±q«e±¶]¨ì³Ì«á
If InStr(x, T1 & "|") = 1 And InStr(x, "/") = 0 Then
'¡ô¦pªGx_key ¦³ T1&"|" ³o¦r¦ê¥B¤£¥]§t"/" ¦r¤¸
'§ä¯Z¦¸ªº·N«ä
If NN = 1 Then
'¡ô¦pªG«e±§ä¨ì¤F¯Z¦¸«á!
'¡ô¦pªG³o®ÉNN = 1
Sh(2).Cells(1, CC).Resize(1, UBound(Ay) + 1) = Ay
'¡ô§â¼ÐÃD¦C¶K¤Jªí2¦b«e¤è¨M©wªº²Ä¤@¦C¦ì¸m
Sh(2).Cells(2, CC) = T1
'¡ô¼ÐÃD¦C¦ì¸mªº¤U¤@¦C²Ä¤@®æ©ñ¯Z¦¸
Sh(2).Cells(2, CC + 1) = xD(x)
'¡ô¼ÐÃD¦C¦ì¸mªº¤U¤@¦C²Ä¤G®æ©ñ½s¸¹³sÄòªº¦¸¼Æ
Sh(2).Cells(2, CC + 9) = xD(x & "/³sÄò®É¶¡")
'¡ô¼ÐÃD¦C¦ì¸mªº¤U¤@¦C²Ä¤Q®æ©ñ½s¸¹³sÄòªº²Ö¿n®É¶¡
Sh(2).Cells(2, CC + 10) = xD(x & "/³sÄò®É¶¡") * xD(x & "/³t«×")
'¡ô¼ÐÃD¦C¦ì¸mªº¤U¤@¦C²Ä¤Q¤@®æ©ñ½s¸¹³sÄòªº²Ö¿n³sÄò¶ZÂ÷
NN = 3
'¡ô¼ÐÃD¦C¸ò²Îp¦C³B¸Ì§¹!´NÅýNN = 3,¶}©lˤJ°}¦C¸ê®Æ
Crr = xD(z & "/c")
'¡ô½Õ¥X°}¦C
Sh(2).Cells(NN, CC).Resize(UBound(Crr), 11) = Crr
'¡ô§âCrr°}¦C¶K¨ìªí2ªº¬Û¹ï¦ì¸m!
NN = NN + xD(x) + 1
'¡ôNNn¥[¤J°}¦C¦C¼Æ¦A¥[1
'¦A¥[1¬Oµ¹¤U¤@Ó²Îp¦C¥Îªº
ElseIf xD(x) > 1 Then
'¡ô§_«hNN¤£¬O1,¦Ó¥B¦pªG ¯Z¦¸&"|"&½s¸¹¬O¦³³sÄòªº
Sh(2).Cells(NN - 1, CC) = T1
'¡ôªí2ªº¬Û¹ï²Îp¦C²Ä¤@®æ©ñ¯Z¦¸
Sh(2).Cells(NN - 1, CC + 1) = xD(x)
'¡ôªí2ªº¬Û¹ï²Îp¦C²Ä¤G®æ©ñ½s¸¹³sÄòªº¦¸¼Æ
Sh(2).Cells(NN - 1, CC + 9) = xD(x & "/³sÄò®É¶¡")
'¡ôªí2ªº¬Û¹ï²Îp¦C²Ä¤Q®æ©ñ½s¸¹³sÄòªº²Ö¿n®É¶¡
Sh(2).Cells(NN - 1, CC + 10) = xD(x & "/³sÄò®É¶¡") * xD(x & "/³t«×")
'¡ôªí2ªº¬Û¹ï²Îp¦C²Ä¤Q¤@®æ©ñ½s¸¹³sÄòªº²Ö¿n³sÄò¶ZÂ÷
Crr = xD(x & "/c")
'¡ô½Õ¥X°}¦C
Sh(2).Cells(NN, CC).Resize(UBound(Crr), 11) = Crr
'¡ô§âCrr°}¦C¶K¨ìªí2ªº¬Û¹ï¦ì¸m!
NN = NN + xD(x) + 1
'¡ôNNn¥[¤J°}¦C¦C¼Æ¦A¥[1
'¦A¥[1¬Oµ¹¤U¤@Ó²Îp¦C¥Îªº
End If
End If
Next
333
Next
End Sub |
|