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

[µo°Ý] ½Ð±ÐVLOOKUP¤¤³Q¬d¸ß½d³ò¦p¦ó¥[¤J¿ï¦r¼Æ¨ç¼Æ

¦^´_  p212


    §Ú¸Õ¸Õ¬Ý¡A¦]¬°Àɮצ³¨Ç½ÆÂø¡A§Ú§â¥LÂà¦n¤§«á¶Ç°e¤W¨Ó¤j®a°Q½×¬Ý¬Ý¡C
97forum µoªí©ó 2013-5-13 17:12


TEST-VLOOKUP.rar (20.09 KB)
¦bÀɮפ¤¦³­Ó³Q¬d¸ß¤½¥q ®vº~¥ø·~ªÑ¥÷¦³­­¤½¥q
¦pªGª½±µ¬d¸ß¡A¬OµLªk§ä¨ì¸Ó¤½¥q¤§¸ê®Æ(®vº~¥ø·~ªÑ¥÷¦³­­¤½¥q°ª¶¯¤À¤½¥q)¡A¦ý¬O¦pªG³Q¬d¸ß½d³ò¦³¿ï¦r¼Æ(«e¤»­Ó¦r)ªº®É­Ô¡A±N¥i¥H¬d¸ß¨ì¸ê®Æ¡C

ÁÙ±æ¦U¦ì«e½ú«ü¾É¡C

TOP

¥»©«³Ì«á¥Ñ Bodhidharma ©ó 2013-5-14 15:50 ½s¿è

¦^´_ 11# 97forum

°ò¥»¤W´N¥u¬O§Ú­ì¥»¤½¦¡§ï­Ó¦WºÙ
  1. =INDEX($D$1:$D$4,MATCH(1,SEARCH($A$1,$C$1:$C$4),0))
½Æ»s¥N½X
B2°}¦C¤½¦¡
  1. =INDEX(ªí®æ__2003sv_CHIComp01_comCustomer[ID],MATCH(1,SEARCH(A2,ªí®æ__2003sv_CHIComp01_comCustomer[InvoiceHead]),0))
½Æ»s¥N½X
TEST-VLOOKUP_sol.rar (12.02 KB)

TOP

¦^´_ 12# Bodhidharma

©êºp¡A§Ú¤½¦¡¸ÌÀ³¸Ó¦A¥[­Óisnumber¡A¤£µM¥u¦³¦WºÙ¥X²{¦b³Ì«e­±¤~·|·j´Mªº¨ì
B2°}¦C¤½¦¡
  1. =INDEX(ªí®æ__2003sv_CHIComp01_comCustomer[ID],MATCH(TRUE,ISNUMBER(SEARCH(A2,ªí®æ__2003sv_CHIComp01_comCustomer[InvoiceHead])),0))
½Æ»s¥N½X
TEST-VLOOKUP_sol.rar (12.04 KB)

TOP

¦^´_ 13# Bodhidharma

·PÁ Bodhidharma «e½úµ¹¤©ªº«ü¾É¡A§Ú±N±zªºµ{¦¡­×§ï¤§«á¡A¦b§ÚªºÀɮפ¤¥i¥HÀò±o§Ú©Ò»Ý­nªº¸ê®Æ¤F¡C
¤p§Ì¤£¤~¡A¹ï©óExcelªº¨ç¼Æ»{ÃѤ£²`¡AµL½×¦p¦ó«D±`·PÁ Bodhidharma «e½úªºÀ°¦£¡C¤p§ÌÁÙ­n¦A¦h¬ã¨s¤F¡I
ÁÂÁ¡I

TOP

¥»©«³Ì«á¥Ñ Bodhidharma ©ó 2013-5-14 19:05 ½s¿è

¦^´_ 14# 97forum

­è­è§ä¨ì¤@½g¤£¿ùªº»¡©ú¡A(ÃöÁä¦r¡G"µ²ºc¤Æ°Ñ·Ó")
http://office.microsoft.com/zh-tw/excel-help/HA010342999.aspx#BMexamples_of_using_structured_referenc
¤~µo²{§Úªº¤½¦¡¦³¤@ÂI·§©À¤Wªº°ÝÃD
  1. =INDEX(ªí®æ__2003sv_CHIComp01_comCustomer[ID],MATCH(TRUE,ISNUMBER(SEARCH(A2,ªí®æ__2003sv_CHIComp01_comCustomer[InvoiceHead])),0))
½Æ»s¥N½X
¤§¤¤¦³¨Ï¥Î¨ì"A2"¡A¨ä¹ê¦bªí®æ¤¤¤£À³¸Ó¥X²{³o¼ËªºªF¦è¡A±q¸Ó½g»¡©ú¥i¥Hª¾¹DÀ³¸Ó¨Ï¥Î"ªí®æ5[[#³o­Ó¦C],[¤¤¤å®v¦W]]"¡A¤~¤£·|¦]¬°¦ì¸m¦Ó³y¦¨¤½¦¡¦³«D¹w´Áªº®ÄªG
¦]¦¹¾ã­Ó¤½¦¡À³¸Ó¬O°}¦C¤½¦¡¡G
  1. =INDEX(ªí®æ__2003sv_CHIComp01_comCustomer[ID],MATCH(TRUE,ISNUMBER(SEARCH(ªí®æ5[[#³o­Ó¦C],[¤¤¤å®v¦W]],ªí®æ__2003sv_CHIComp01_comCustomer[InvoiceHead])),0))
½Æ»s¥N½X
¥i¥Hµo²{¾ã¦Cªº¤½¦¡³£¤@¼Ë¡A»P©Ò¦bªº¦C¼ÆµLÃö
TEST-VLOOKUP_sol.rar (11.91 KB)

note¡G¥t¥~match¨ç¼Æ¤¤¤@©w­n¨Ï¥Îtrue¡A¤£¯à¥Î1¡A¦]¬°«á­±isnumber¦^¶Çªº·|¬OÅÞ¿è­È(true,false)¦Ó«D¼Æ¦r¡A©Ò¥H¦pªG¥Î1¡A«á­±ªºÅÞ¿è­È¤S¨S¦³Âà´«¬°¼Æ¦rªº¸Ü´N·|§ä¤£¨ì

TOP

¥H­ì¤½¦¡¡G=VLOOKUP(A1,D:E,2,0)
±N¡e¯Á¤Þ­È¡f¥[¤J¡e¸U¥Î¦r¤¸"*"¡f¡A§Y¥i¡e¼Ò½k¤ñ¹ï¡f¨ú±o¹ïÀ³­È¡G
¨Ò¦p¡G¿é¤J¡e¤j¥Ò¦³­­¤½¥q*¡f¡e¤j¥Ò*°ª¶¯*¡f¡A³£¥i¥H§ì¨ì¡e¤j¥Ò¦³­­¤½¥q°ª¶¯¤À¤½¥q¡fªº¹ïÀ³­È¡C

­Y¡e¯Á¤Þ­È¡fºû«ù¬°¡e¤j¥Ò¦³­­¤½¥q¡f¡A«h¡e¸U¥Î¦r¤¸¡f¥i¥[¤J¤½¦¡¤¤¡G
=VLOOKUP(A1&"*",D:E,2,0)¡@¡Ö¡@¦r¦ê­º¬Û¦P§Y¥i§ì¥X¨Ó

¥iÃþ±À¡G
¯Á¤Þ­È¡e°ª¶¯¤À¤½¥q¡f¡A¤½¦¡¡G=VLOOKUP("*"&A1,D:E,2,0)¡@¡Ö¡@¦r¦ê§À¬Û¦P§Y¥i
¯Á¤Þ­È¡e°ª¶¯¡f¡A=VLOOKUP("*"&A1&"*",D:E,2,0)¡@¡Ö¡@¦r¦ê¡e¥]§t¡f¯Á¤Þ¤å¦r§Y¥i

¥H¤W³W«h»P¡e´M§ä¡f¥\¯àÃþ¦ü¡I°Ñ¦Ò°Ñ¦Ò¡I

TOP

­Y·Q¨Ï¥ÎFIND°µ·j´M¡A°Ñ¦Ò¦p¤U¡G
=LOOKUP(9^9,FIND(A1,D1:D4),E1:E4)

¥H¯Á¤Þ­È¡e¤j¥Ò¦³­­¤½¥q¡f¬°¨Ò¡G=FIND(A1,D1:D4)¡@
¡Ö¤½¦¡°}¦C­È¬°{#VALUE!;#VALUE!;#VALUE!;1}
²Å¦XªÌ¬°²Ä¢³®æ¡A§Q¥Î9^9¦¹·¥¤j­È¬°¥Ø¼Ð¡ALOOKUP±N¶Ç¦^¤W­z°}¦C³Ì«á¤@­Ó¼Æ¦r¬Û¹ïÀ³¦ì¸mªº¢Ó¢³Àx¦s®æ­È¢²¢²¡C

¥H¤W¤½¦¡¸ê®Æ¨Ò¡G
¡@¡@¡@A¡@¡@¡@¡@¡@B¡@¡@¡@¡@¡@C¡@¡@¡@¡@¡@¡@¡@¡@D¡@¡@¡@¡@¡@¡@¡@¡@  ¡@E
¤j¥Ò¦³­­¤½¥q¡@¡@¤½¦¡¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¤j¥Ò¥ø·~¦³­­¤½¥q¡@¡@¡@¡@¡@39
¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¤j¥ÒªÑ¥÷¦³­­¤½¥q¡@¡@¡@¡@¡@55
¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¤j¥Ò¬ì§Þ¦³­­¤½¥q¡@¡@¡@¡@¡@24
¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¤j¥Ò¦³­­¤½¥q°ª¶¯¤À¤½¥q¡@¡@33

TOP

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

­ì¨Óvlookup¥i¥H¨Ï¥Î¸U¥Î¦r¤¸¡A¨ü±Ð¡I
©Ò¥H­ìpoÀɮתº­ì©l¤½¦¡
  1. =VLOOKUP(LEFT(ªí®æ5[[#³o­Ó¦C],[¤¤¤å®v¦W]],6),ªí®æ__2003sv_CHIComp01_comCustomer[[#¥þ³¡],[InvoiceHead]:[ID]],2,0)
½Æ»s¥N½X
¥u­nµy·L­×§ï¬°
  1. =VLOOKUP("*"&ªí®æ5[[#³o­Ó¦C],[¤¤¤å®v¦W]]&"*",ªí®æ__2003sv_CHIComp01_comCustomer[[#¥þ³¡],[InvoiceHead]:[ID]],2,0)
½Æ»s¥N½X
§Y¥i¹F¦¨©M§Ú¤½¦¡¬Û¦Pªº®ÄªG

¦Ü©ó=LOOKUP(9^9,FIND(A1,D1:D4),E1:E4)
·í¦³¤£¥u¤@­Ó²Å¦X®É¡A·|¦^¶Ç¡u²Å¦Xªº¦r¦ê¬O¦b³Ì«á­±ªº¡v²Å¦X¶µ¥Ø¡A·Pı¦³ÂIÀH¾÷

TOP

¦^´_ 18# Bodhidharma

¨Ï¥Î¡e¸U¥Î¦r¤¸¡f§Y¬O¶i¦æ¡e¼Ò½k¤ñ¹ï¡f¡A·í²Å¦X¡eÃöÁä¦r¡fªÌ¤£¥u¤@µ§®É¡A¦]¨äÀH¾÷©Ê°ª¡A¥i¯à´N§ì¤£¥X¥¿½T­È¡A
¦]¦¹¡A¤½¦¡ªº«Ø¥ß¡A¨Ï¥ÎªÌ²zÀ³¥ýÂç²M¸ê®Æ«¬ºA¡A¤×¨ä¬O­«ÂФÎÃþ¦ü¸ê®Æªº°ÝÃD¡A¦b¡e¤è«K©Ê¡f¤Î¡e·Ç½T©Ê¡f¤W¶·¨â¾Ü¨ä¤@¡A
¤£¥i¯à¥u¿é¤J¡e¤¤µØ¡f¨â¦r¡A¦Ó¥Ñ¤½¦¡¦Û¦æ§PÂ_¡e¤¤µØ¶l¬F¡fÁÙ¬O¡e¤¤µØ¹q«H¡f¡I
¥t¡eMATCH¡DCOUNTIF¡DSUMIF¡f¤]³£¥i§Q¥Î¸U¥Î¦r¤¸®M¦b¯Á¤Þ­È¤W¥H¨ú±o¬ÛÃö¼Æ¾Ú¡I­Y¬O°w¹ï¡e¸s²Õ©Ê¡fªº²Î­p¡A¬Û·í«K§Q´N¬O¡I

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD