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

¦Û°Ê¦V¤U¶ñº¡·|¶W¥X¸ê®Æ½d³ò

¦Û°Ê¦V¤U¶ñº¡·|¶W¥X¸ê®Æ½d³ò

¦b³B²z¾Ç¥Í³ø¦W¸ê®Æªº®É­Ô¡A¾Ç¥Í¤H¼Æ¬OÅܼơA¦³®É10¤H¡A¦³®É50¦h¤H¡C
¦]¬°¾Ç¥Í¸ê®ÆÃe¤j¡A©Ò¥H³]­p¤½¦¡§ä¥X¾Ç¥Í©Ê§O¡A¨Ã³]­p¦Û°Ê¶ñº¡VBA¡C
¦ý¤£ª¾¬O¤°»ò­ì¦]¡A¦V¤U¶ñº¡·|¶W¥X½d³ò¡A¥X²{¦n´X­Ó#N/A¡C
·Q½Ð°Ý¦U¦ìVBA¤j®v¯à§_¸ÑµªºÃ´b¡A¬O¤°»ò­ì¦]©O? ¬Oµ{¦¡½s¼g¦³°ÝÃDÁÙ¬O®æ¦¡¦³°ÝÃD? ·PÁ¸ѵª~

ªþ¤WExcelµ{¦¡ÀÉ¡A·PÁ¸Ѵb¡C

½Òµ{ªí³æ¦^À³µ{¦¡.rar (105.11 KB)

µ{¦¡ÀÉ

TOP

¦^´_ 2# minarabbit


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
½Ð«e½ú¸Õ¸Õ¬Ý
F2¤½¦¡=IFERROR(VLOOKUP($A2,'C:\¨C¾Ç´Á§ó·s\[±`¥Î¸ê®Æ(¾Ç¥Í+¾É®v).xlsx]111-2¾Ç¥Í¸ê®Æ'!$A$1:$G$2100,5,FALSE),"")
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-3-16 14:04 ½s¿è

¦^´_ 2# minarabbit


    ÁÂÁ«e½ú
«á¾Ç«ØijVBAªº¸Ñ¨M¤è®×¦p¤U,½Ð°Ñ¦Ò

Option Explicit
Sub §R°£()
Sheets("ªí³æ¦^À³¿é¤J°Ï").UsedRange.Offset(1, 0).EntireRow.Delete
'¡ô¤u§@ªí¤¤¦³¨Ï¥ÎÀx¦s®æ½d³ò©¹¤U°¾²¾1¦Cªº½d³ò©Ò¦bªº¦C ¾ã¦C§R°£
End Sub
Sub ¦V¤U¶ñº¡()
Dim C&, A$
'¡ô«Å§iÅܼÆ:C¬Oªø¾ã¼ÆÅܼÆ,A¬O¦r¦êÅܼÆ
C = [ªí³æ¦^À³¿é¤J°Ï!C65536].End(3).Row
'¡ô¥OC³oªø¾ã¼ÆÅܼƬO¤u§@ªí¸ÌCÄæ³Ì«á¤@­Ó¦³¤º®eÀx¦s®æ¦C¸¹
If C < 2 Then Exit Sub
'¡ô¦pªGCÅܼƤp©ó 2(¥NªíCÄæ¨S¦³¶ñ¤J¾Ç¥Í¸ê®Æ)!´Nµ²§ôµ{¦¡°õ¦æ
A = "'C:\¨C¾Ç´Á§ó·s\[±`¥Î¸ê®Æ(¾Ç¥Í+¾É®v).xlsx]111-2¾Ç¥Í¸ê®Æ'"
'¡ô¥OA³o¦r¦êÅܼƬO"~~~"¦r¦ê
Range([ªí³æ¦^À³¿é¤J°Ï!A2], Cells(C, "A")).Formula = "=C2&D2"
'¡ô¥O¤u§@ªí¸Ì[A2]¨ì AÄæ²ÄCÅܼƦCÀx¦s®æªº¤½¦¡¬O "=C2&D2"
Range([ªí³æ¦^À³¿é¤J°Ï!F2], Cells(C, "F")).Formula = _
"=VLOOKUP($A2," & A & "!$A$1:$G$2100,5,FALSE)"
'¡ô¥O¤u§@ªí¸Ì[F2]¨ì FÄæ²ÄCÅܼƦCÀx¦s®æªº¤½¦¡¬O "~~~~~"
End Sub
===================================
«ö§R°£¶sµ²ªG:


¤â°Ê¶ñ¤J¾Ç¥Í¸ê®Æ:


«ö¦V¤U¶ñº¡¶s°õ¦æµ²ªG:
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 4# Andy2483

±z¤ÓÁ¾µê¤F¡A§Ú¥u¬O¤p¤p«á½ú¦Ó¤w¡AVBAÁÙ¦b¬ã¨s¾Ç²ß¤¤¡C
±z¼gªºµ{¦¡¤£¶È²µu¥BÀu¤Æ³\¦h¡AÅý¸ê®Æ¥i¥H¹ï»ô¡A¯u¬O¤Ó·PÁ±z¤F!!

§Q¥ÎÅܼÆA³]©w¸ê®Æ¦ì¸m¡A¥ÎRange³]©w°_©lÀx¦s®æ¡A¥ÎCells³]©wÅܼÆC©M¦V¤U¶ñº¡Äæ¦ì©Ò¦b¡A
¥ÎFormula®M¤J¦UºØExcel¨ç¼Æµ{¦¡¡A¡i" & A & "!¡j§Q¥ÎÅܼÆAºë²¦aÁYµu¤FVlookup¨ç¼Æ¡C
´Nºâ¤£¤p¤ß§R°£­ì¥»¦ì¦b²Ä¤G¦Cªº¤½¦¡¡AVBA¦V¤U¶ñº¡¤w¸g¦³¤º«Ø¤½¦¡¤F¡A©Ò¥Hµe­±¤]Åܱo«Ü°®²b¡C

TOP

¦^´_ 5# minarabbit


    ÁÂÁ«e½ú¦^´_
º¡©Û·l,Á¾¨ü¯q
½Ð«e½ú±`¤W½×¾Â¤@°_¾Ç²ß
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : Ãø¦æ¯à¦æ¡AÃø±Ë¯à±Ë¡AÃø¬°¯à¬°¡A¤~¯àª@µØ¦Û§Úªº¤H®æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD