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

[µo°Ý] match ©Î lookup ¨ç¼Æ¥\¯à°ÝÃD

[µo°Ý] match ©Î lookup ¨ç¼Æ¥\¯à°ÝÃD

¦U¦ì¦n, §Ú¦b¨Ï¥Îmatch ©Î lookup ¨ç¼Æ®Éµo²{¦³°ÝÃD: Book1.zip (6.89 KB)

¨ä¹ê§Ú¥u·Q¥Î¨â¨ç¼Æ§ä²Å¦X«ü©w¼Æ­È¨ºÄæ³Ì¤W­±ªº¼ÐÃD­È, ¦p§ä´M½d³ò¸Ì¦³¨ä¥L¼Æ­È¤j¹L«ü©w­n¨D®É(§Y¶À¦â³æ¤¸®æ), ¨ç¼Æ·|¥X²{¿ù»~, ·Q½Ð±Ð¦U¦ì­ì¦]©Î¦³¨S¦³¨ä¥L¨ç¼Æ¥i¥N´À¨Ï¥i¹F¨ì§Ú·Q­nªºµ²ªG. ¥ýÁÂÁ¦U¦ìÀ°¦£!

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-24 08:35 ½s¿è

¦^´_ 7# ML089
«¢«¢¡I¦~ªñ50¦Ñªá¥¼¨£MATCH(30,B2:K2)»PMATCH(30,B2:K2,)ªº®t§O¡A½Ð¨£½Ì¡I
«D±`·PÁÂML089ª©¤j¼ö¤ßªº¸Ñ»¡¡A¶Ç±Â¹ï¡u«D±Æ§Ç¡v¸ê®Æ¤§¼Ò½k¬d´M¨Ï¥ÎLOOKUP(0,0/±ø¥ó,¬d´M°Ï°ì)ªºÆ[©À»P¤½¦¡Â²¬ù¤Æªº§Þ¥©¡C

TOP

¦^´_ 7# ML089

·PÁª©¥DML089 ¤Î p212 ¸ÔºÉ¸Ñµª, ¤×¨ä³oÀ³¥Î 0/(B34:K34=30) = {#DIV/0!,#DIV/0!,0,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}
Áö¼È¥¼¯à§¹¥þ²z¸Ñ, ¦ý¤w¥i¸Ñ¨M§Ú·Q­nªº°ÝÃD¤F, ÁÂÁÂ~

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2014-7-23 22:34 ½s¿è

¦^´_ 6# p212
>>¥i§_·Ð½ÐML089ª©¥D½ç±Ð¡H
>>1¡BL2 =OFFSET($A$1,0,MATCH(30,B2:K2,))
>>(1)¨ämatch_type¡u¬Ù²¤¡v©M¿é¤J¡u1¡v¬O¤@¼Ëªº¡A¦ý¬°¦ó¦b1#ªº½d¨ÒÀÉ·|§e²{¡­N/A¡H
>>(2)±z¹ïmatch_type¬°¦ó¤£¥Î¡u0¡v¡H¡]1#¤£¬O¥ÎMATCH«ü©w´M§ä¡u30¡v¡^


¦^ÂÐ:
(1)
¦pªG match_type ¬O 1¡A«h MATCH ¨ç¼Æ·|§ä¨ìµ¥©ó©Î¶È¦¸©ó lookup_value ªº­È¡CLookup_array ¥²¶·¥H»¼¼W¦¸§Ç±Æ¦C¡G...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE¡C
­ì¤½¦¡¤¤¦]Lookup_array¨S¦³»¼¼W¦¸§Ç±Æ¦C©Ò¥H¿ù»~
(2)
=OFFSET($A$1,0,MATCH(30,B2:K2)) ³o¼Ëmatch_type¬O¬Ù²¤¬° 1
=OFFSET($A$1,0,MATCH(30,B2:K2,))³o¼Ëmatch_type¬O­n¿é¤J¨S¦³¿é¤J¬° 0 (³o¬O²¤Æ¿é¤JªºÃa²ßºD¡A½Ð¨£½Ì)


>>2¡BL22 =LOOKUP(,0/(B22:K22=30),$B$1:$K$1)
>>(1)¨älookup_value¬°¦ó¥i¬Ù²¤¡H¬Ù²¤ªº­Èªí¥Ü¬°¡u0¡v¡H
>>(2)¦b¦¹LOOKUP±o¨ìªº°}¦C¬O¤°»ò¡H


¦^ÂÐ:
(1)
L22 =LOOKUP(,0/(B22:K22=30),$B$1:$K$1)
lookup_value¨Ã¨S¦³¬Ù²¤¥u¬O­n¿é¤J¨S¦³¿é¤J®É¬° 0 (³o¬O²¤Æ¿é¤JªºÃa²ßºD¡A½Ð¨£½Ì)
(2)
B34:K34 = {100,100,30,100,100,100,100,100,100,100}
0/(B34:K34=30) = {#DIV/0!,#DIV/0!,0,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}
L34 =LOOKUP(,0/(B34:K34=30),$B$1:$K$1) = 3
LOOKUP¨ã¦³®e¿ù¯à¤O¡AÁöµM Lookup_vector¤¤¦³#DIV/0!¿ù»~¸ê®Æ®É¨Ã¤£·|¤¤Â_¤´·|Ä~Äò¶i¦æ§@·~¡A²Å¦X®É 0/TRUE = 0¡Alookup_value ¥Î  0¥i§ä²Å¦X±ø¥ó¥ô¤@µ§(¤@¯ë¥Î©ó°ß¤@²Å¦X±ø¥ó)¡A¥Î 1¥i§ä²Å¦X±ø¥ó³Ì«á¤@µ§¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-23 15:14 ½s¿è

¦^´_ 5# ML089
¥i§_·Ð½ÐML089ª©¥D½ç±Ð¡H
1¡BL2 =OFFSET($A$1,0,MATCH(30,B2:K2,))
(1)¨ämatch_type¡u¬Ù²¤¡v©M¿é¤J¡u1¡v¬O¤@¼Ëªº¡A¦ý¬°¦ó¦b1#ªº½d¨ÒÀÉ·|§e²{¡­N/A¡H
(2)±z¹ïmatch_type¬°¦ó¤£¥Î¡u0¡v¡H¡]1#¤£¬O¥ÎMATCH«ü©w´M§ä¡u30¡v¡^
2¡BL22 =LOOKUP(,0/(B22:K22=30),$B$1:$K$1)
(1)¨älookup_value¬°¦ó¥i¬Ù²¤¡H¬Ù²¤ªº­Èªí¥Ü¬°¡u0¡v¡H
(2)¦b¦¹LOOKUP±o¨ìªº°}¦C¬O¤°»ò¡H
ÁÂÁ¡I

TOP

¦^´_ 1# ricky8751

LOOKUP¤ÎMATCH¥Îªk¦p¤U

    L2 =OFFSET($A$1,0,MATCH(30,B2:K2,))
L22 =LOOKUP(,0/(B22:K22=30),$B$1:$K$1)

¤U©Ô½Æ»s
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-23 12:58 ½s¿è

¦^´_ 1# ricky8751
­ìªþÀɤ§Àx¦s®æL34¤½¦¡=LOOKUP(30,B34:K34,$B$1:$K$1)
·|¤£·|¬O¦]¬°¡ulookup_vector ¤¤ªº­È¥²¶·¥H»¼¼W¶¶§Ç±Æ¦C¡G...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE¡F§_«h¡ALOOKUP ¨ç¼Æ¥i¯à¤£·|¶Ç¦^¥¿½Tªº­È¡C¡v¦Ó­P#N/Aªºµ²ªG¡H
½Ð°Ñ¦Ò¡I

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-23 12:41 ½s¿è

¦^´_ 1# ricky8751
­ìªþÀɤ§Àx¦s®æL12¤½¦¡«Øij­×§ï¬°
=OFFSET($A$1,0,MATCH(30,B12:K12,0),,)
½Ð°Ñ¦Ò¡I

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-23 12:34 ½s¿è

¦^´_ 1# ricky8751
Àx¦s®æL12¿é¤J°}¦C¤½¦¡
=INDEX($B$1:$K$1,,SMALL(IF($B12:$K12=30,COLUMN($B12:$K12)-1,FALSE),1))
¦V¤U½Æ»s
½Ð°Ñ¦Ò¡I

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD