½Ð±Ð2«¤U©Ô¦¡¿ï³æ¿z¿ïªº°ÝÃD
- ©«¤l
- 1425
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1449
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-4-16
|
¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-2-23 09:06 ½s¿è
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾Ç¥H1#½d¨Ò½m²ß°}¦C»P¦r¨å,¾Ç²ß±¡¹Òµ²ªG¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:
°õ¦æµ²ªG:
Option Explicit
Sub TEST()
Dim Brr, Z, i&, R&, N&, T1$, T2$
'¡ô«Å§iÅܼÆ:&¬Oªø¾ã¼Æ,$¬O¦r¦êÅܼÆ,¨S¦³«ü©wªº¬O³q¥Î«¬ÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZ³o³q¥Î«¬ÅܼƬO ¦r¨å
Brr = Range([B1], [A65536].End(xlUp))
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥H[B1]¨ì AÄæ³Ì«á¦³¤º®eÀx¦s®æ,³o¨âÀx¦s®æ²[»\¤è¥¿½d³òÀx¦s®æȱa¤J°}¦C¤¤
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!¥OiÅܼƱq1 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
T1 = Trim(Brr(i, 1)): R = Z(T1): T2 = Trim(Brr(i, 2))
'¡ô¥OT1³o¦r¦êÅܼƬO i°j°é¦C1ÄæBrr°}¦CÈ¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«áªº·s¦r¦ê
'¥OR³oªø¾ã¼ÆÅܼƬO¥H T1ÅܼƬdZ¦r¨å¦^¶ÇitemÈ,¥OT2³o¦r¦êÅܼƬO i°j°é¦C2ÄæBrr°}¦CÈ¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«áªº·s¦r¦ê
If R = 0 Then N = N + 1: R = N: Brr(R, 1) = T1: Brr(R, 2) = T2: Z(T1) = R: GoTo i01
'¡ô¦pªGRÅܼƬO 0!´N¥ONÅܼƲ֥[1,¥ORÅܼƦPNÅܼÆ,¥OR¦C1ÄæBrr°}¦CȬO T1ÅܼÆ,¥OR¦C2ÄæBrr°}¦CȬO T2ÅܼÆ
'¥OT1ÅܼƬ°keyªºitemÈ´«¥H RÅܼƩñ¦^Z¦r¨å¤¤,¥Oµ{§Ç¸õ¨ì¼Ð¥Ü i01¦ì¸mÄ~Äò°õ¦æ
If InStr("/" & Brr(R, 2) & "/", "/" & T2 & "/") = 0 Then Brr(R, 2) = Brr(R, 2) & "/" & T2
'¡ô¦pªGR¦C2ÄæBrr°}¦CȦb«e«á³s±µ"/"²Å¸¹©Ò²Õ¦¨ªº·s¦r¦ê¸Ì ¨S¦³¥]§tT2ÅܼƦb«e«á³s±µ"/"²Å¸¹©Ò²Õ¦¨ªº·s¦r¦ê!,
'´N¥OT2ÅܼƥH"/"³s±µ¦bR¦C2ÄæBrr°}¦CȸÌ
i01: Next
[E1].Resize(N, 2) = Brr
'¡ô¥O[E1]Àx¦s®æ¦V¤UÂX®iNÅܼƦC,¦V¥kÂX®i2Ä檺Àx¦s®æ½d³ò,¥HBrr°}¦Cȱa¤J
End Sub |
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|
- ©«¤l
- 1425
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1449
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-4-16
|
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾Ç¥H1#½d¨Ò½m²ß°}¦C»P¦r¨å,¾Ç²ß¥t¤@±¡¹Ò»Ý¨D¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:
°õ¦æµ²ªG:
Option Explicit
Sub TEST_2()
Dim Brr, Z, i&, R&, N&, T1$, T2$, C%, U%, M%
'¡ô«Å§iÅܼÆ:&¬Oªø¾ã¼Æ,%¬Oµu¾ã¼Æ,$¬O¦r¦êÅܼÆ,¨S¦³«ü©wªº¬O³q¥Î«¬ÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZ³o³q¥Î«¬ÅܼƬO ¦r¨å
Brr = Range([B1], [A65536].End(xlUp))
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥H[B1]¨ì AÄæ³Ì«á¦³¤º®eÀx¦s®æ,³o¨âÀx¦s®æ²[»\¤è¥¿½d³òÀx¦s®æȱa¤J°}¦C¤¤
ReDim Crr(UBound(Brr), UBound(Brr))
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!¥OiÅܼƱq1 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
T1 = Trim(Brr(i, 1)): R = Z(T1): T2 = Trim(Brr(i, 2)): C = Z(T1 & T2): U = Z(T1 & "/c")
'¡ô¥OT1³o¦r¦êÅܼƬO i°j°é¦C1ÄæBrr°}¦CÈ¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«áªº·s¦r¦ê
'¥OR³oªø¾ã¼ÆÅܼƬO¥H T1ÅܼƬdZ¦r¨å¦^¶ÇitemÈ(¦pªGR¬O0,³oµ{§Ç¤w¸g«Ø¥ßkey=T1,item=0 ¦bZ¦r¨å¤¤)
'¥OT2³o¦r¦êÅܼƬO i°j°é¦C2ÄæBrr°}¦CÈ¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«áªº·s¦r¦ê
'¥OC³oµu¾ã¼ÆÅܼƬO¥H T1ÅܼƳs±µT2ÅܼƲզ¨ªº·s¦r¦ê¬dZ¦r¨å¦^¶ÇitemÈ(¦pªGC¬O0,³oµ{§Ç¤w¸g«Ø¥ßkey=T1 & T2,item=0 ¦bZ¦r¨å¤¤)
'¥OU³oµu¾ã¼ÆÅܼƬO¥H T1ÅܼƳs±µ"/c"¦r¦ê²Õ¦¨ªº·s¦r¦ê¬dZ¦r¨å¦^¶ÇitemÈ(¦pªGU¬O0,³oµ{§Ç¤w¸g«Ø¥ßkey=T1 & "/c",item=0 ¦bZ¦r¨å¤¤)
If R = 0 Then N = N + 1: R = N: Crr(R, 0) = T1: Z(T1) = N
'¡ô¦pªGRÅܼƬO 0!´N¥ONÅܼƲ֥[1,¥ORÅܼƦPNÅܼÆ,¥ORÅܼƦC0ÄæCrr°}¦CȬO T1ÅܼÆ,¥OT1ÅܼƬ°keyªºitemÈ´«¥H NÅܼƩñ¦^Z¦r¨å¤¤
If C = 0 Then U = U + 1: Crr(R, U) = T2: Z(T1 & "/c") = U: Z(T1 & T2) = 1
'¡ô¦pªGCÅܼƬO 0!´N¥OUÅܼƲ֥[1,¥ORÅܼƦCUÅܼÆÄæCrr°}¦CȬO T2ÅܼÆ,
'¥OT1ÅܼƳs±µ"/c"¦r¦ê²Õ¦¨ªº·s¦r¦ê¬°keyªºitemÈ´«¥H UÅܼƩñ¦^Z¦r¨å¤¤
'¥OT1ÅܼƳs±µT2ÅܼƲզ¨ªº·s¦r¦ê¬°keyªºitemÈ´«¥H ¼Æ¦r1©ñ¦^Z¦r¨å¤¤
If M < U Then M = U: Crr(0, M) = Brr(1, 2) & "_" & M
'¦pªGM³oµu¾ã¼Æ¤p©óMÅܼÆ!´N¥OMÅܼƦPUÅܼÆ,¥O0¦CMÅܼÆÄæCrr°}¦CȬO ²Ä1¦C2ÄæBrr°}¦CÈ,³s±µ¤U©³½u,¦A³s±µMÅܼƩҲզ¨ªº·s¦r¦ê
i01: Next
Crr(0, 0) = Brr(1, 1) & "\" & Brr(1, 2): [I1].Resize(N + 1, M + 1) = Crr
'¡ô¥O[I1]Àx¦s®æ¦V¤UÂX®iNÅܼƦC,¦V¥kÂX®i2Ä檺Àx¦s®æ½d³ò,¥HBrr°}¦Cȱa¤J
End Sub |
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|