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

[µo°Ý] LookupµLªk§ä¥X¬Û¹ïÀ³¸ê®Æ

[µo°Ý] LookupµLªk§ä¥X¬Û¹ïÀ³¸ê®Æ

¦pªþ¥ó,¥HÀˬdÄæ¦ìªø«×¤]¨S¦³²§±`
¤£ª¾¹D­þ¸Ì¥X°ÝÃD¤F¡A½Ð¦U¦ìÀ°À°§Ú
                                ÁÂÁÂ

Lookup°ÝÃD.rar (5.71 KB)

YOYO

¦^´_ 1# yanto913


    ¨S¦³±Æ§Çªº¸ê®Æ¥²¶·§ä§¹¥þ²Å¦X
=LOOKUP(2,1/(A2:A15=D2),B2:B15)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁª©¥D
­ì¨ÓLOOKUP¦³±Æ§Çªº­­¨î
¦ÓVLOOKUP¦³¦¬´M½d³ò²Ä¤@¦æ¥²¶·¬O¦¬´M­Èªº­­¨î
¬Ý¨Ó©¹«á­n¦h¦h¥ÎLOOKUP(2,1/(A2:A15=D2),B2:B15)¤ñ¸û«OÀI¤F
YOYO

TOP

¥»©«³Ì«á¥Ñ vvcvc ©ó 2014-10-13 16:44 ½s¿è
  1. =LOOKUP(2,1/(A2:A15=D2),B2:B15)
½Æ»s¥N½X
¤W­±ªº¤½¦¡¤¤Lookup_value¬°¤°»ò­n¿é¤J2 ?
§Ú¹Á¸Õ¿é¤J1¡B3¤]³£¥¿½T¡A¦ý¿é¤J«D¼Æ¦r´N¥X²{#NA
·Q¤£³q¬°¤°»ò¤@©w­n¶ñ¤J¼Æ¦r

TOP

¦^´_ 4# vvcvc

=LOOKUP(2,1/(A2:A15=D2),B2:B15)

¤W­±ªº¤½¦¡¤¤Lookup_value¬°¤°»ò­n¿é¤J2 ?
§Ú¹Á¸Õ¿é¤J1¡B3¤]³£¥¿½T¡A¦ý¿é¤J«D¼Æ¦r´N¥X²{#NA
·Q¤£³q¬°¤°»ò¤@©w­n¶ñ¤J¼Æ¦r


(A2:A15=D2) ¬°§PÂ_¦¡°}¦C {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

TRUE / FALSE ¹J¨ìºâ¼Æ¹Bºâ®É¡A¥i¥Hµø¬° 1/ 0
1/FRUE = 1/1 = 1
1/FALSE = 1/0 = #DIV/0! (¿ù»~­È)

1/(A2:A15=D2)  = {#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}

LOOKUP¨ç¼Æ¨ã¦³®e¿ù¯à¤O·|©¿²¤ #DIV/0!¡A¦¹¤½¦¡´N¬O§Q¥Î¦¹¯S©Ê¦Ó³]­pªº¬d¸ß¤è¦¡  

lookup_value ¨Ï¥Î
¨Ï¥Î 1 ®É¦^¶Ç¨ä¤¤¤@­Ó1ªº¬Û¹ïÀ³­È
¨Ï¥Î 2 ¥H¤Wªº­È®É¦^¶Ç³Ì«á¤@­Ó1ªº¬Û¹ïÀ³­È¡C(³o´N¬O¬d¸ß³Ì¤@­Ó¼Æ­ÈªºÀ³¥Î)
¦]¬°¥»¨Ò¥u¦³¤@­Ó1¡A©Ò¥H¥Î 1 ¡B2¡B3 ...³£·|¥¿½T¦^¶Ç
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 3# yanto913

¨Ï¥ÎVLOOKUPÀ³¸Ó·|§ó²³æ
=VLOOKUP(D2,A:B,2,)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦pªG¤U­±ªº¤½¦¡¤¤
=LOOKUP(2,1/(A2:A15=D2),B2:B15)
D2­nª½±µ¿é¤J´M§äªº¦r¤¸¡A¦p ST01 ¡B ST02¡BST03...
­n¦p¦ó¤@¤f®ð§â§t¦³ST¶}ÀYªº¤@¦¸³£§ä¥X¨Ó?

¦b¥[Á`¹B¥Î®É¡A¥i¥H¨Ï¥Î¥H¤U¤½¦¡
=sumif(A1:A1000,"ST*",B1:B1000)
¦ý·f°tLookup®É¥u»Ý­n¶Ç¦^ TRUE ©Î FALSE ®É¸Ó«ç»ò¼g?

TOP

¦^´_ 7# vvcvc



¦pªG¤U­±ªº¤½¦¡¤¤
=LOOKUP(2,1/(A2:A15=D2),B2:B15)
D2­nª½±µ¿é¤J´M§äªº¦r¤¸¡A¦p ST01 ¡B ST02¡BST03...
­n¦p¦ó¤@¤f®ð§â§t¦³ST¶}ÀYªº¤@¦¸³£§ä¥X¨Ó?


¤½¦¡¨S¦³¿ìªk±N¤å¦r¥þ³¡§ä¥X¨Ó¨Ã³s±µ°_¨Ó ¦p ST01 ¡B ST02¡BST03...
¤@¯ë¤´¶·¤@®æ§ä¤@­Ó¤å¦r¡A¦A±N©Ò¦³»²§U®æ¦ê±µ°_¨Ó




¦b¥[Á`¹B¥Î®É¡A¥i¥H¨Ï¥Î¥H¤U¤½¦¡
=sumif(A1:A1000,"ST*",B1:B1000)
¦ý·f°tLookup®É¥u»Ý­n¶Ç¦^ TRUE ©Î FALSE ®É¸Ó«ç»ò¼g?

¤£ª¾°ÝÃD­n°Ý¤°»ò¡A½Ð¦A»¡©ú²M·¡©Î¦³ÀÉ®×
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

§Ú¬O·Q°Ý¯à¤£¯à¹³¨Ï¥ÎSUMIF¡A±ø¥ó¥u¼g¥X³¡¥÷¦r¦ê¡A³Ñ¾lªº¦a¤è¨Ï¥Î*¥N´À
§Ú¦bLOOKUP¸Ì­±¼gST*·|¥X²{¿ù»~
·Q°Ý°Ý¬Ý¬O¤£¬O¦b¤£¦P¦a¤è­n¥Î¤£¦Pªº¼gªk(³o­Ó¦n¹³¤w¸g¦b«e¤@­Ó°ÝÃD¦^µª¤F)

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2014-10-22 15:14 ½s¿è

¦^´_ 9# vvcvc

LOOKUP ¨S¦³ª½±µ¤ä´© ¸U¥Î¦r¤¸ *¡A¤@¯ë»Ý­n·f°t¨ä¥L¨ç¼Æ¸Ñ¨M¡C
¨Ò¦p
LOOKUP(1, -FIND("ST", ref1), ref2)

¸U¥Î¦r¤¸¥i°Ñ¦Ò
http://office.microsoft.com/zh-tw/excel-help/HP005203612.aspx

¥H¤U¨ç¼Æ¤ä´©¸U¥Î¦r¤¸¡A¬d¸ß¤å¦r¤¤¦³ ? * ~ µ¥²Å¸¹­nª`·N¨Ï¥Î

COUNTIF
HLOOKUP
MATCH
SEARCH
SUMIF
VLOOKUP
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD