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

[µo°Ý] ª½¦¡§ï¾î¦¡·j´Mªº¤½¦¡

[µo°Ý] ª½¦¡§ï¾î¦¡·j´Mªº¤½¦¡

¥¼©R¦W_1.png
2019-9-10 02:56
  
¥¼©R¦W_2.png
2019-9-10 02:57

¨ç¼ÆINDEX+SMALL+IF
·Q±Nª½¦¡ªí®æ·j´M¡A§ï¬°¾î¦¡ªí®æ·j´M
½Ð°Ý¡J
Sheet2! L2ªº¤½¦¡­n«ç»ò½s¼g?
½Ð¦U¦ìÀ°¦£! ÁÂÁÂ!

´ú¸ÕÀÉ :   ª½¦¡§ï¾î¦¡·j´Mªº¤½¦¡.rar (2.75 KB)

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-5-31 10:28 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ßVBA°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æµ²ªG:
20230531_2.jpg
2023-5-31 10:16



Option Explicit
Sub TEST()
Dim Brr, Crr, Y, j%, T$, T1$, R%
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([B5], Cells(1, Columns.Count).End(1))
'¡ô¥OBrrÅܼƬO¤Gºû°}¦C,¥H1~5¦C¸ê®Æ±a¤J°}¦C¤¤(¤£¥]§t¼ÐÃDÄæ»PªÅÄæ)
ReDim Crr(1 To UBound(Brr, 2), 1 To 2)
'¡ô¥OCrrÅܼƬO ¤GºûªÅ°}¦C,Áa¦V¦PBrr¾î¦V½d³ò,¾î¦V1~2
For j = 1 To UBound(Brr, 2)
'¡ô³]¶¶°j°é
   T = Brr(4, j): T1 = Brr(2, j)
   '¡ô¥OTÅܼƬO ²Ä4¦C°}¦C­È,¥OT1ÅܼƬO ²Ä2¦C°}¦C­È
   If Y(T) = "" Then R = R + 1: Y(T) = R: Crr(R, 1) = T: Crr(R, 2) = T1: GoTo j01
   '¡ô¦pªGTÅܼƬO²Ä1¦¸¯Ç¤JY¦r¨å,´N¥ORÅܼƲ֥[1(¬ö¿ý¦C¸¹),
   '¥OTÅܼƦbY¦r¨å¸Ìªºitem("")´«¦¨RÅܼÆ,
   '¥OCrr°}¦C²Ä1Äæ©ñ ¼Æ¶q,¥OCrr°}¦C²Ä2Äæ©ñ ²Ä1­Ó½c¸¹
   '¸õ¨ì¼Ð¥Üj01¦ì¸mÄ~Äò°õ¦æ

   Crr(Y(T), 2) = Crr(Y(T), 2) & "," & T1
   '¡ôµ{§Ç·|¶]¨ì³o¦ì¸m!³£¬O²Ä2¦¸¥H¤W¥X²{ªºkey,
   '¥OCrr°}¦C²Ä2ÄæÄ~Äò²Ö¿n½c¸¹,¥H³r¸¹¹j¶}

j01: Next
[I8:J8] = [{"¼Æ¶q","½c¸¹"}]: [I9].Resize(R, 2) = Crr
'¡ô¥OÀx¦s®æ²Ä8¦C¬O¼ÐÃD¦C,¥OCrr°}¦C±q[I9]¶}©l¼g¤JÀx¦s®æ¸Ì
Set Y = Nothing: Erase Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub


'===================================================
'¼ÐÃDÄæ°j°é¤¤³B²zªº¤è®×


Option Explicit
Sub TEST_1()
Dim Brr, Crr, Y, j%, T$, T1$, R%
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([A5], Cells(1, Columns.Count).End(1))
ReDim Crr(1 To UBound(Brr, 2), 1 To 2)
For j = 1 To UBound(Brr, 2)
   T = Brr(4, j): T1 = Brr(2, j)
   If Y(T) = "" Then R = R + 1: Y(T) = R: Crr(R, 1) = T: Crr(R, 2) = T1: GoTo j01
   Crr(Y(T), 2) = Crr(Y(T), 2) & "," & T1
j01: Next
[I8].Resize(R, 2) = Crr
Set Y = Nothing: Erase Brr, Crr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

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


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

°õ¦æµ²ªG:
20230531_1.jpg
2023-5-31 08:58



Function GetCTNo(xA As Range, xB As Range, xNo)
'¡ô¦Û­q¨ç¼ÆGetCTNo(),«Å§iÅܼÆ:(xA,xB)¬OÀx¦s®æÅܼÆ,xNo¬O³q¥Î«¬ÅܼÆ
'¨ç¼Æµ²ªG¬O¦r¦ê

Dim i%, TT$
'¡ô«Å§iÅܼÆ:i¬Oµu¾ã¼Æ,TT¬O¦r¦êÅܼÆ
For i = 1 To xA.Count
'¡ô³]¶¶°j°é!i±q1 ¨ìxAÅܼƼƶq(Àx¦s®æ¼Æ¶q)
    If xA(i) = xNo Then TT = TT & "," & xB(i)
    '¡ô¦pªGxA i°j°éÀx¦s®æªº­È»P xNoÅܼƬۦP!
    '´N¥OTTÅܼƱNxB i°j°éÀx¦s®æªº­È¯Ç¤J¨ì«á¤è,¥H³r¸¹¶¡¹j

Next i
GetCTNo = Mid(TT, 2)
'¡ô¥OGetCTNo¨ç¼Æ¦^¶Ç TTÅܼƱq²Ä2¦r¤¸¶}©lªº«á¤è¦r¦ê
End Function
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2019-9-23 23:50 ½s¿è

¦^´_ 18# ­ã´£³¡ªL
­ã´£ª©¥D:
¯u´Îªº¸Ñµª¡C
³o¼Ë§Ú¥u­n¤£°Êµ{¦¡½X¡A¥u­n§ï¤½¦¡¬d¸ßªº¦ì§}´N¥i¥H¤F¡C
·PÁ±z­@¤ßªº¸ÑÃD©M«ü¾É¡C

TOP

¦^´_ 17# papaya

¥ÎVBA¦Û­q¨ç¼Æ, »P¤½¦¡¥Îªk¬Û¦P:
²Å¦X±ø¥óªº­È»ô»E1­ÓÀx¦s®æV1.rar (7.29 KB)

TOP

¦^´_ 16# ­ã´£³¡ªL
­ã´£ª©¥D :
ÁÂÁ±zªº¦^À³¡C

VBA«D±`´Î¡A¦ý¬O§Ú¤£À´¡A©È¤£¦n(·|)ºûÅ@¡C
§Ú¦³¥J²Ó¬d¬Ý¤½¥q¥H©¹ªº¬ö¿ý¡A­«½Æ¼Æ¶qªº³Ì°ª¬ö¿ý¬O6­Ó¡A
¦pªG¤£¦æ¡A¬Ý¶QEXCELª©¯à®e³\¦³´X­Ó¡A´N´X­Ó¡C¨ä¥¦¤£¨¬ªº¡A§Ú¦A·Q¿ìªk¡C

­ì¦³ªºªí®æ¬O¼ÒÀÀÃD¡A©Ò¥H±N¬d¸ßªí²¾¨ìA8:B8
¦p¹Ï¥Ü :
¥¼©R¦W.png
2019-9-23 12:23

¦A¦¸·PÁ±z^^

TOP

¦^´_ 15# papaya


§ÚÁ٥Ϊ©excel, µLªk¦ê±µ¤Ó¦h¤å¦r,
«Øij¥Îvba

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2019-9-22 21:00 ½s¿è

¦^´_ 14# ­ã´£³¡ªL
­ã´£ª©¥D :
ÁÂÁ¦^À³¡C

1_¨C¤é¥X³f½c¼Æ¤£¤@©w¡A¦ý¥Ø«eªº¥X³f¼Æ¤£·|¶W¹L100½c¡A¤]´N¬O½c¸¹³Ì¦h¬°1~99¸¹¡C
2_½c¸¹³£¬°¯Â¼Æ­È½s¸¹¡C
3_¦P¼Æ¶qªº­«½Æ­Ó¼Æ³Ì¦h10­Ó¡C
¥H¤W ·q½Ð½ç±Ð!
ÁÂÁ±z^^

TOP

¦^´_ 13# papaya


½c¸¹½T»{¥u¦³1~9½c??? ¥B¬°¯Â¼Æ¦r???

TOP

¦^´_ 9# ­ã´£³¡ªL
²Å¦X±ø¥óªº­È»ô»E1­ÓÀx¦s®æ
²Å¦X±ø¥óªº­È»ô»E1­ÓÀx¦s®æ.rar (2.88 KB)
­ã´£ª©¥D:
¤£¦n·N«ä¡A§Ú·Q±NSheet1!L1ªº¤½¦¡¡A¦A¶i¶¥¬°¦pSheet2ªºL1¡J±N²Å¦X±ø¥óªº­È¡A»ô»E©ñ¸m¦b1­ÓÀx¦s®æ¡A
³o¼Ë´N¤ð»Ý¦A¹w¯d³\¦h­ÓÀx¦s®æ¡C

½Ð°Ý±z¡JSheet2!L1ªº¨ç¼Æ¤½¦¡­n¦p¦ó½s¼¶¡H
ÁÂÁ±z¡I

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD