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

[µo°Ý] ¸ê®Æ½Æ»s

[µo°Ý] ¸ê®Æ½Æ»s

½Ð±Ð¦U¦ì«e½ú¡A
§Ú¦³2­ÓexcleÀÉ¡A(°ò¥»¤u¸ê.xls and ­Ó¤H¸ê®Æ.xls)¡A¦pªG¤G­ÓÀɮ׳£¦b¶}±Òªºª¬ºA¤U¡A
¦p¦ó¥ý¤ñ¹ï°ò¥»¤u¸ê.xls¤UA1³o­ÓÄæ¦ì¡AµM«á±NB1»PC1¶K¤J­Ó¤H¸ê®Æ.xlsªº¬Û¹ïÄæ¦ì¤¤
(¤G­Ó¸ê®Æ¬Ò¦b¦U¦Ûªºsheet1¬¡­¶¤¤)¡AÁÂÁÂ

°ò¥»¤u¸ê.xls
A1    B1           C1
¤p³¯   22000  ¨k
¤p©ú   35000  ¤k
¤p¤ý   28000  ¨k

­Ó¤H¸ê®Æ.xls
A1¡@B1   C1
¤p©ú¡@
¤p¤ý   
¤p³¯

Sorryµo¿ùª©­¶¡A½Ð°Ý¦p¦ó§R°£³o­Ó­¶­±¡A¨Ã§ïµo¦Üexcelµ{¦¡°Ï¡AÁÂÁÂ

TOP

¦^´_ 1# funnydisk
À³¸Ó¬OAÄæ BÄæ CÄæ ¤£¬OA1 B1 C1 ?

¥Î¤½¦¡´N¥i¥H¤F
¦b­Ó¤H¸ê®Æ.xlsªº
B1Àx¦s®æ¥´¤W  =VLOOKUP(A1,[°ò¥»¤u¸ê.xls]sheet1!A$1:C$3,2)
¦V¤U©Ô¶ñº¡¤½¦¡
C1Àx¦s®æ¥´¤W  =VLOOKUP(A1,[°ò¥»¤u¸ê.xls]sheet1!A$1:C$3,3)
¦V¤U©Ô¶ñº¡¤½¦¡

TOP

·PÁÂstillfish00¤j¤jªº¦^ÂСA¤w¤F¸Ñvoolupªº§@¥Î¡A¥t·Q½Ð°Ý¡A§Ú±Nvlookup¿ý¦¨vba
Àɮתº¦ì¸m¬O§_¥i¨Ï¥ÎÅܼÆ(file_path)¡A­×§ï¦p¤U¡A¦ýµo²{µLªk°õ¦æ¡AÁÂÁÂ

dim file_path as Worksheets
file_path = "C:\¤H¨Æ¸ê®Æ\Á~¸ê.xlsx
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],file_path!R4C[-10]:R1900C[-6],3,FALSE)"

TOP

¦^´_ 4# funnydisk
°Ñ¦Ò¬Ý¬Ý
  1. Sub test()
  2.     Dim file_path As String
  3.     Dim sArr
  4.     file_path = "C:\¤H¨Æ¸ê®Æ\Á~¸ê.xlsx"
  5.     sArr = Split(file_path, "\")
  6.     sArr(UBound(sArr)) = "[" & sArr(UBound(sArr)) & "]"
  7.     file_path = "'" & Join(sArr, "/") & "sheet1'"
  8.     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10]," & file_path & "!R4C[-10]:R1900C[-6],3,FALSE)"
  9. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# stillfish00
¦A¦¸·PÁÂstillfish00ªºÀ°¦£¡A¥t·Q½Ð°Ý¡A¦pªG¿z¿ïªº¸ê®ÆÄæ¦ì¬O¨S¦³³o­Ó¸ê®Æ¡A¬O§_¥i¥HÅý¥L¤]¥HªÅ¥Õ¶ñ¤J¡A¦Ó¤£­n¥X²{#N/A©O¡AÁÂÁÂ

TOP

¦^´_ 6# funnydisk
=IFERROR(VLOOKUP(      ),"")

TOP

        ÀR«ä¦Û¦b : ¦³Ä@©ñ¦b¤ß¸Ì¡A¨S¦³¨­Åé¤O¦æ¡A¥¿¦p¯Ñ¥Ð¤£¼½ºØ¡A¬Ò¬OªÅ¹L¦]½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD