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

[µo°Ý] vlookup ¤Î offset¦³¤£¯à·j´Mªº®æ¦¡¶Ü?

[µo°Ý] vlookup ¤Î offset¦³¤£¯à·j´Mªº®æ¦¡¶Ü?

½Ð°Ývlookup ¤Î offset¦³¤£¯à·j´Mªº®æ¦¡¶Ü
ªþÀɧڹï¦P¨Ì°Ï°ì¤À§O¨Ï¥Î³o¨â¦U¨ç¼Æ³£¤£¯à±o¨ì¥¿½Tªºµ²ªG
¦³¤H¥i¥H§i¶D§Ú­ì¦]¶Ü?








Book1.rar (4.24 KB)
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

¦^´_ 1# freeffly
´N±zªºªþÀɬݨӡA¹ïVLOOKUP©MMATCH¨ç¼Æ¦Ó¨¥¡Alookup_value¬Ò¥]§t¦btable_array¤¤¡A¦Û¬OµLªk¥¿½T¦^À³±z©Ò­nªºµ²ªG¡C­Ylookup_value¨Ó¦Û©ó¤â°Ê¿é¤J¸ê®Æ½s¿è¦C¤¤©Î°Ñ·Ó¦Û«Dtable_array¸s¤¤ªº­È¡A«h¥i¦^À³¥X±z­nªºµª®×¡C

TOP

¦^´_ 2# p212


    À³¸Ó¤£¬O¨º¦U°ÝÃD
   §A¦pªG§â§ÚªºaÄæ¸ê®Æ§ï¦¨a¡Bb¡Bc....µ¥¬Ookªº
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

°ÝÃD¥X¦b~²Å¸¹,­n~~¤~§ä±o¨ì¦p
=VLOOKUP("2012¦~1~~9¤ë",$A$3:$F$14,5,0)

TOP

¦^´_ 3# freeffly
½Ð°Ý
1¡B¥HH3Àx¦s®æ¬°¨Ò¡A¨äÀ³¦³µ²ªG¬°¦ó¡H¬O36.16¡]´NA3:F14ªº½d³ò¡A¦ü¥G¤£¤Ó¦³¥i¯àªº²z¥Ñ¯à¹ïªº¤W36.16ªºµª®×¡^¡H§í¬°¥L¼Æ¡H
2¡BI3Àx¦s®æ¹ïÀ³µ²ªG¤S¬°¦ó¡H
¥i§_¦A敍­z¤@¤U°ÝÃD©Î­×¹¢ªþÀÉ¥H¨Ñ¬ã¨s¬ã­×¡HÁÂÁ¡I

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-1-14 22:48 ½s¿è

¦^´_ 5# p212
H3À³¬°34.74¡A¦]¬°¼Ó¥D©¿²¤¤FRange_lookup°Ñ¼Æ,·|Åܦ¨¼Ò½k¤ñ¹ï¡A¤S¦]¬°AÄæ·|¦¨¬°µL±Æ§Çª¬ºA¡A©Ò¥H·|§ì¿ù¸ê®Æ
H3=VLOOKUP(SUBSTITUTE(A3,"~","~~"),$A$3:$F$14,5,0)
I3=OFFSET($A$2,MATCH(SUBSTITUTE(A3,"~","~~"),$A$3:$A$14,0),4)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh
®¤§EµLª¾¡A¦³½ÐHsieh¶W¯Åª©¥D«ü¾É¤@¤U¡AÁÂÁ¡I
1.¬°¦ó¹ïA3Àx¦s®æ°õ¦æSUBSTITUTE(A3,"~","~~")¤½¦¡¡A¤§«á¦A¥H¨ä¡u2012¦~1~~9¤ë¡v¤§µ²ªG§@¬°VLOOKUP¨ç¼Æªºlookup_value¡H(A3Àx¦s®æ­ì¦³¸ê®Æ¬°2012¦~1~9¤ë¡A°õ¦æSUBSTITUTE¤§«á®t§O¦b~~¡C)
2.°ÝÃD½d¨Ò¼Æ¾Ú¤À§G¤w©ú½T¡A¬°¦ó¦A¥HAÄ欰´M§ä­È¡A¹ï¡uAÄæ¦ÜFÄæ¡v½d³ò§ä´M©óEÄæµ²ªG¹ïÀ³¦bHÄæ¡H

TOP

¦^´_ 6# Hsieh
¦^´_ 6# ANGELA


ª©¥Dªº¤è¦¡¥i¥H


·Q½Ð°Ý¤@¤U¬°¤°»ò¦³³o¦U~²Å¸¹ÁÙ­n´«¦¨~~¤~¯à§ì¨ì¸ê®Æ?
ªþÀɬO±qºô¸ô¤W¶×¤J¸ê®Æªº
¤@¯ë¦Û¤v¨Ï¥Î¤£¤Ó·|¥h¨Ï¥Î~³o¦U²Å¸¹
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 7# p212


    ²Ä¤G¦U°ÝÃD¬O­n°Ý¬°¤°»ò§ÚÁÙ­n¥h·j´M¸ê®Æ¶Ü?
    §Ú¬O­n¦b§Oªº¤u§@ªí·j´M¸Ó°Ï°ì¸ê®Æ
   ¥u¬O§Ú¨Ï¥ÎVLOOKUP ¤Î OFFSET³£§ì¤£¨ì
   ©Ò¥H§Ú¤~¥Î½d¨Ò
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 8# freeffly


        ¦r¦ê¤¤§t¦³¸U¥Î¦r¤¸®É¡A³q±`·|³Qµø¬°¸U¥Î¦r¤¸¥Î³~
¨Ò¦p¬P¸¹(*)¥Nªí¥ô¦ó¦r¦ê¡A°Ý¸¹(?)¥Nªí¥ô¦ó³æ¤@¦r¤¸
­Y­n§ä³o¨Ç¸U¥Î¦r¤¸«h¥²¶·¥[¤Wªi®ö¸¹(~*)©Î(~?)
°µ­Ó¹êÅç:¨Ï¥Î¼Ó¥DªºÀÉ®×
¥Î´M§ä¥\¯à¡A¦b´M§ä¥Ø¼ÐÁä¤J~µM«á¥þ³¡´M§ä
¦A¥Î~~´M§ä¡A¬Ý¬Ý2¦¸ªº´M§ä¦³¦ó®t²§
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD