ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

½Ð±Ð2­«¤U©Ô¦¡¿ï³æ¿z¿ïªº°ÝÃD

½Ð±Ð2­«¤U©Ô¦¡¿ï³æ¿z¿ïªº°ÝÃD

½Ð±Ð¤@¤U¦U¦ì¤j¤j2­«¤U©Ô¦¡¿ï³æ¿z¿ïªº°ÝÃD
Sheet1¬°¸ê®Æ¨Ó·½¦p¹Ï
01.JPG
Sheet2¬°ªí³æ¦p¹Ï
02.JPG
§Æ±æ±o¨ìµ²ªG¬O
1.ªí³æ(Sheet2)¤ººØÃþÄæ¦ì¥i¤U©Ô¥X²{½¦±a¡A®¹¥]³U¡A§¨Ãì³Uªº¿ï¶µ¡A¸ê®Æ¤£­«½Æ
2.ªí³æ(Sheet2)¤ººØÃþÄæ¦ì¿ï¦n«á¡A®Æ¸¹Äæ¦ì¤U©Ô¥X²{¬Û¹ïÀ³ªº­È,¦p½¦±a´N¥X²{A1¡AA2¡A®¹¥]³U´N¥X²{B1¨ìB6¡A§¨Ãì³U´N¥X²{C1¨ìC3
¤p§Ì¬O¦³¥hª¦¤@¤U¤å¤F¡A¤£¹L¦³¤@¨Ç¤å³¹ªºªþ¥ó¤p§Ì¨SÅv­­¤U¸ü¡A¥i¯à¦³¤j¤j°Ý¹L¤F¡A¦p¦³­«½Æ³Â·Ð«üÂI¤@¤UÁÂÁ¡C

TEST.rar (7.33 KB)

­n¥Î"©w¸q"+OFFSET¨ç¼Æ....
©ÎªÌ...¥h¤U­±£xºô­¶¬Ý¬Ý
http://www.dotblogs.com.tw/lastsecret/archive/2010/07/13/16540.aspx
boyao

TOP

¦^´_ 1# ¿Õ©i
­º¥ý¶·±N²M³æ¾ã²z¦¨¤U¹Ï

¥ý«Ø¥ß¥H¤U2­Ó©w¸q¦WºÙ
ºØÃþ=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,)
®Æ¸¹=OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B)-1,)

E2°}¦C¤½¦¡=IF(ROW(A1)>SUM(1/COUNTIF(ºØÃþ,ºØÃþ)),0,INDIRECT("A"&MIN(IF(ISERROR(MATCH(ºØÃþ,$E$1:E1,0)),ROW(ºØÃþ),""))))
¦V¤U½Æ»s
F2°}¦C¤½¦¡=IF(COLUMN(A$1)>COUNTIF(ºØÃþ,$E2),0,INDIRECT("B"&SMALL(IF(ºØÃþ=$E2,ROW(ºØÃþ),""),COLUMN(A$1))))
¦V¥k¦V¤U½Æ»s
¦A«Ø¥ß¤U¦C3­Ó©w¸q¦WºÙ
List1=OFFSET(Sheet1!$E$2,,,LOOKUP("ùÕ",Sheet1!$E$2:$E$200,ROW(Sheet1!$E$1:$E$199)),)
¿ï¨úSheet2!B2ª¬ºA¤U©w¸q¥H¤U2­Ó¦WºÙ
List=OFFSET(Sheet1!$E$2,MATCH(Sheet2!B$1,List1,0)-1,1,,200)
List2=OFFSET(Sheet1!$E$2,MATCH(Sheet2!B$1,List1,0)-1,1,,LOOKUP("ùÕ",List,COLUMN(List)-5))
¦p¦¹
Sheet2!B1ÅçÃÒ²M³æ¤½¦¡=List1
Sheet2!B2ÅçÃÒ²M³æ¤½¦¡=List2
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# Hsieh


    ·PÁ¤j¤j«ü±Ð¡Aµy·L¸Õ¤F¤@¤U¦]¸Ó¥i¥H¥Î¡A§Ú¦b¸Õ¸Õ®M¥Î¨ì§Ú­n¥Îªºªí®æ¤W¡A¦p¦³ºÃ°Ý§Æ±æ¤j¤j¯à¦b«ü±Ð¤@¤U¡C

TOP

½Ð°Ý§ÚÀ|¸Õ¨Ï¥Î«á·í
ºØÃþ>80ºØ¡F®Æ¸¹>700­Ó    ¡÷¹Bºâ¼Æ¶q«Ü¦h®É
¦b¹Bºâ®É·|¥Î«Ü¤[ªº®É¶¡¡A¦³¿ëªk¥[§Ö¹Bºâ³t«×¶Ü¡H
°¸¦Ó¾Ç²ß

TOP

¦^´_ 3# Hsieh
Sheet1¬°¸ê®Æ¨Ó·½®Æ¸¹¦pªG¥u¬O¿é¤J¼Æ¦rªº¸Ü¡ASheet2ªº®L©Ô´N·|ÅܱoµLªk¨Ï¥Î½Ð°Ý¤j¤j¦³¿ìªk¸Ñ¨M¶Ü?

TEST.rar (23.75 KB)

TOP

¦^´_ 6# ¿Õ©i

List2°Ñ·Ó¨ì¤½¦¡
=OFFSET(Sheet1!$E$2,MATCH(Sheet2!B$1,List1,0)-1,1,,LOOKUP(2,1/(List<>0),COLUMN(List)-5))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁ¤j¤j³á¥Ø«eµ¦¬O¥¿±`¤F¡A¦]¸Ó¨S¦³Ô£°ÝÃD¤F¡A·PÁ¤j¤j¡C

TOP

¥»©«³Ì«á¥Ñ 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

TOP

ÁÂÁ½׾Â,ÁÂÁ¦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

TOP

        ÀR«ä¦Û¦b : ¤H­nª¾ºÖ¡B±¤ºÖ¡B¦A³yºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD