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

[µo°Ý] match¨ç¼Æ³B²z­«½Æ¼Æ­È¡A¦p¦ó¶Ç¦^³Ì«á²Å¦Xªº¦C¡H

[µo°Ý] match¨ç¼Æ³B²z­«½Æ¼Æ­È¡A¦p¦ó¶Ç¦^³Ì«á²Å¦Xªº¦C¡H

¤ñ¤è»¡¡G
A¦C¡G
1  A
2  B
3  C
4  D
5  B
6  E
7  S
8  A
9  B

1. match("A",$A$1:$A$9,0)=1
2. match("A",$A$1:$A$9)=8
3. large(if($A$1:$A$9="A",row($A$1:$A$9),""),1)=8
§Ú§Æ±æ¥X²{3¦¡ªºµ²ªG(8)¡A¦ý¬Omatch¥²»Ý­nºë½T(¦]¦¹2¦¡¤£¦æ)¡A¥i¬O¥Îlarge¨ç¼Æ¤S¤Ó¦Y¸ê·½
·Q½Ð±Ð¤@¤Umatch¨ç¼Æ¹J¨ì­«´_­È¡A¬O§_¯àª½±µ¶Ç¦^³Ì«á¤@­Ó²Å¦Xªº¦C¡H

¦^´_ 1# Bodhidharma

=LOOKUP(2,1/(A1:A9="A"),ROW(A1:A9))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2013-3-11 15:28 ½s¿è

¦^´_ 2# Hsieh
¶W¯Åª©¥D¡G¥i§_«ü¾É¤@¤U²Ê¬õ¦rÅ鳡¥÷¥Î·N¦ó¦b¡HÁÂÁ¡I

=LOOKUP(2,1/(A1:A9="A"),ROW(A1:A9))

TOP

¦^´_ 3# p212

¸Ô²Ó½Ð¬ÝLOOKUP¨ç¼Æ»¡©ú
¦]¬°¦bµL±Æ§Çª¬ºA­n§ä¨ì³Ì«á¤@­Ó²Å¦X¤p©óµ¥©ó·j´M­È³Ì¤è«Kªº´N¬OLOOKUP
¦¹¨Ò¤¤2¬°·j´M­È
1/(A1:A9="A")
·|²£¥Í¤@­Ó1»P°£¥H0ªº¿ù»~­È°}¦C
¦b¦¹°}¦C§ä¨ì³Ì«á¤@­Ó¤p©óµ¥©ó2ªº¦ì¸m§Y¬°©Ò¨D
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh
½Ð°Ý¶W¯Åª©¥D¡G
¨º¬°¦ó·j´M­È¤£³w¥Î¡u1¡v¡A¦Ó¥Î¡u2¡v¡H
¡u1/(A1:A9="A")¡v²£¥Íªºµ²ªG¤£¬O¡Õ¡×1¡H

TOP

¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# Bodhidharma

=INDEX(MATCH(2,1/(A1:A9="A")),)

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2013-3-12 15:04 ½s¿è

¦^´_ 6# Hsieh
ÁÂÁ¶W¯Åª©¥Dªº«ü¾É¡I¨ü¯q¨}¦h¡C
¹ï©óExcel¤º«Ø±Ð¾Ç»¡©ú¡A¦ü¥G¥¼´£¨Ñ½Ñ¦p¥H1/(A1:A9="A")¤è¦¡§@¬°¤Þ¼Æ¡A¥H»s³y¥X°}¦C®ÄªG¤§­ì²z¡A½Ð°Ý¦Û¦ó³B(©Î­þ¤@Ãþ±M®Ñ)¥iÀò±o¦p¦¹°}¦C®ÄªG¤§¨t²Î©Ê°ò¦Æ[©À»P¸ÑªR¡H
ÁÂÁ½ç±Ð¡I

TOP

¦^´_ 8# p212
¨ç¼Æ»¡©ú³£¥u¬O°ò¥»¥\¯à»¡©ú
À³¥Î§¹¥þ¬O¨Ì¾Ú­Ó¤H«ä¦Ò¦Ó²£¥Í¤£¦Pµ{«×ªº®ÄªG
³oÃþ®ÑÄy¦ü¥GÁÙ¨S¦³¡A¦]¬°²¦³º³o·|»Pªí®æ°t¸m¡B¸ê®Æ«¬ºAµ¥¦]¯À
¦Ó¦³¤£¦Pªº«ä¦Ò¡A¥u¦³¦Û¤v¦h¬Ý¡B¦h·Q¤~¯à°÷¿Ä·|³e³q
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁª©¥Dªº»¡©ú
¬Ý¨ì
a. =LOOKUP(2,1/(A1:A9="A"),ROW(A1:A9))³o­Ó¦¡¤l®É
°¨¤W·|·Q¨ì
b. =LOOKUP(1,1/(A1:A9="A"),ROW(A1:A9))
c. =LOOKUP(2,1*(A1:A9="A"),ROW(A1:A9))
³o¨âºØ¤è¦¡¡A¤£¹L³£¤£¦æ¡A¬Ý¤Fgongª©¥D¨º½g»¡©ú¤§«á¤j·§¤ñ¸û¤F¸Ñ¡G
b¦¡¤¤·|¥X²{¤º§t1¥H¤Î#DIV/0!ªº°}¦C¡A¦]¬°lookup¨ç¼Æ¬O¥Î¤G¤À·j´Mªk¡A¦]¦¹µLªk½T©w·|§ä¨ì­þ¤@­Ó1(¥¼¥²¬O³Ì«á¤@­Ó)
c¦¡¤¤·|¥X²{¤º§t1¤Î0ªº°}¦C¡A¦ý¬O1©Î0³£¤p©ó2¡A¦]¦¹¤G¤À·j´M·|¤@ª½©¹¤U·j´M¡Aª½¨ì³Ì«á¤@­Ó¡A¦]¦¹¤@©w·|¦^¶Ç³Ì«á¤@­Ó­È(¥i¯à¬O1¤]¥i¯à¬O0¡A¬O0ªº¸Ü´N¿ù¤F)¡A¦]¦¹¥²»Ý¥Îa¦¡¡A¥H°£ªº»s³y¥X#DIV/0!¡A¦AÅýlookup¨ç¼Æ¦Û°Ê§R±¼¡A¦]¦¹³Ì«á¤@©w·|¶Ç¦^³Ì«á¤@­Ó1

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD