- ©«¤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 ©ó 2023-3-13 12:01 ½s¿è
¦^´_ 18# Andy2483
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾Ç½Æ²ß¤ß±o¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
Option Explicit
Sub TEST_1()
Dim Brr, Crr, Z, i&, N&, j&, T$
'¡ô«Å§iÅܼÆ:(Brr,Crr,Z)¬O³q¥Î«¬ÅܼÆ,(i,N,j)¬Oªø¾ã¼ÆÅܼÆ,T¬O¦r¦êÅܼÆ
Brr = Range([G1], Cells(Rows.Count, "A").End(3))
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥H[G1]¨ìAÄæ³Ì«á¦³¤º®eÀx¦s®æȱa¤J
ReDim Crr(1 To UBound(Brr) * 2, 1 To UBound(Brr, 2))
'¡ô«Å§iCrr¬O¤Gºû°}¦C,½d³ò¤j¤p,Áa¦V±q1¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æªº2¿,
'¾î¦V±q1¨ì Brr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹¼Æ
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q1¨ì Brr°}¦CÁa¦V¬°¤j¯Á¤Þ¦C¸¹
T = Brr(i, 2)
'¡ô¥OT³o¦r¦êÅܼƬO i°j°é¦C²Ä2ÄæBrr°}¦CÈ
If InStr("/9:16:000/13:31:000/", "/" & T & "/") Then
'¡ô¦pªGTÅܼƪº«e«á¦U³s±µ"/"²Å¸¹«áªº·s¦r¦ê,¦b«ü©wªº¦r¦ê¸Ì³Q¥]§t¤F??
'«ü©wªº¦r¦ê:"/9:16:000/13:31:000/"
N = N + 1
'¡ô¥ON³oªø¾ã¼ÆÅܼƲ֥[1
Crr(N, 1) = Brr(i, 1): Z = Split(T, ":")
'¡ô¥ONÅܼƦC²Ä1ÄæCrr°}¦CȬO i°j°é¦C²Ä1ÄæBrr°}¦CÈ:
'¥OZÅܼƬO¤@ºû°}¦C,¥OTÅܼƥH":"¤À³Î«á±a¤J
Crr(N, 2) = Join(Array(Z(0), Z(1) - 1, Z(2)), ":")
'¡ô¥ONÅܼƦC²Ä2ÄæCrr°}¦CȬO
'(²Ä0¯Á¤ÞZ°}¦CÈ,²Ä1¯Á¤ÞZ°}¦CÈ-1,²Ä2¯Á¤ÞZ°}¦CÈ)
'³o¤TÓ¦r¦ê¥H":"¦ê±µ¦¨·s¦r¦ê
For j = 3 To 6
'¡ô³]¶¶°j°é!j±q3¨ì6
Crr(N, j) = Brr(i, 3)
'¡ô¥ONÅܼƦC²Äj°j°éÄæCrr°}¦CȬO iÅܼƦC²Ä3ÄæBrr°}¦CÈ
Next
End If
N = N + 1
'¡ô¥ONÅܼƲ֥[1
For j = 1 To UBound(Brr, 2)
'¡ô³]¶¶°j°é!j±q1¨ìBrr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹¼Æ
Crr(N, j) = Brr(i, j)
'¡ô¥ONÅܼƦC²Äj°j°éÄæCrr°}¦CȬO iÅܼƦC²Äj°j°éÄæBrr°}¦CÈ
Next
Next
[J1].Resize(N, UBound(Crr, 2)) = Crr
'¡ô¥O[J1]ÂX®i¦V¤UNÅܼƦC,ÂX®i¦V¥kCrr¾î¦V³Ì¤j©ÒÄ渹¼Æ,
'³o½d³òÀx¦s®æÈ¥H Crr°}¦Cȱa¤J
Erase Brr, Crr, Z
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
==========================================================
Option Explicit
Sub TEST_2()
Dim Brr, Z, Y, B, i&, N&, j&, T$, A(6)
'¡ô«Å§iÅܼÆ:(Brr,Z,Y,B)¬O³q¥Î«¬ÅܼÆ,(i,N,j)¬Oªø¾ã¼ÆÅܼÆ,T¬O¦r¦êÅܼÆ,
'A¬O¤@ºû°}¦C(0~6)ªÅ°}¦C
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY³o³q¥Î«¬ÅܼƬO ¦r¨å
Brr = Range([G1], Cells(Rows.Count, "A").End(3))
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥H[G1]¨ìAÄæ³Ì«á¦³¤º®eÀx¦s®æȱa¤J
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q1¨ì Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
T = Brr(i, 2)
'¡ô¥OT³o¦r¦êÅܼƬO i°j°é¦C²Ä2ÄæBrr°}¦CÈ
If InStr("/9:16:000/13:31:000/", "/" & T & "/") Then
'¡ô¦pªGTÅܼƪº«e«á¦U³s±µ"/"²Å¸¹«áªº·s¦r¦ê,¦b«ü©wªº¦r¦ê¸Ì³Q¥]§t¤F??
'«ü©wªº¦r¦ê:"/9:16:000/13:31:000/"
N = N + 1: B = A
'¡ô¥ON³oªø¾ã¼ÆÅܼƲ֥[1:¥OB³o³q¥Î«¬ÅܼƬOA³oªÅ°}¦C
B(0) = Brr(i, 1): Z = Split(T, ":")
'¡ô¥O0¯Á¤Þ¸¹B°}¦CȬO i°j°é¦C²Ä1ÄæBrr°}¦CÈ:
'¥OZÅܼƬO¤@ºû°}¦C,¥OTÅܼƥH":"¤À³Î«á±a¤J
B(1) = Join(Array(Z(0), Z(1) - 1, Z(2)), ":")
'¡ô¥O0¯Á¤Þ¸¹B°}¦CȬO
'(²Ä0¯Á¤ÞZ°}¦CÈ,²Ä1¯Á¤ÞZ°}¦CÈ-1,²Ä2¯Á¤ÞZ°}¦CÈ)
'³o¤TÓ¦r¦ê¥H":"¦ê±µ¦¨·s¦r¦ê
For j = 2 To 5
'¡ô³]¶¶°j°é!j±q2¨ì 5
B(j) = Brr(i, 3)
'¡ô¥Oj¯Á¤Þ¸¹B°}¦CȬO i°j°é¦C²Ä3ÄæBrr°}¦CÈ
Next
Y(N) = B
'¡ô¥ONÅܼƬ°key,item¬OBÅܼƯǤJY¦r¨å¸Ì
End If
N = N + 1: B = A
'¡ô¥ONÅܼƲ֥[1 :¥OBÅܼƬOAÅܼÆ
For j = 0 To UBound(B)
'¡ô³]¶¶°j°é!j±q0¨ì B°}¦C³Ì¤j¯Á¤Þ¸¹
B(j) = Brr(i, j + 1)
'¡ô¥Oj°j°éB°}¦CȬO i°j°é¦C²Äj°j°é+1ÄæBrr°}¦CÈ
Next
Y(N) = B
'¡ô¥ONÅܼƬ°key,item¬OBÅܼƯǤJY¦r¨å¸Ì
Next
[J1].Resize(N, UBound(A) + 1) = Application.Transpose(Application.Transpose(Y.Items))
'¡ô¥O[J1]ÂX®i¦V¤UNÅܼƦC,ÂX®i¦V¥kA°}¦C³Ì¤j¯Á¤Þ¸¹+1Äæ,
'³o½d³òÀx¦s®æÈ¥H Y¦r¨åªºitemÂà¸m¨â¦¸±a¤J
Erase Brr, B, A, Z: Set Y = Nothing
'¡ô¥OÄÀ©ñÅܼÆ
End Sub |
|