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

[µo°Ý] ¦p¦ó´M§ä³Ì«á¤@µ§¸ê®Æ,¤Î³Ì«á¤@µ§¬Û²Å¸ê®Æ?

[µo°Ý] ¦p¦ó´M§ä³Ì«á¤@µ§¸ê®Æ,¤Î³Ì«á¤@µ§¬Û²Å¸ê®Æ?

Â^¨ú.PNG
2014-4-14 17:50


D3¤ÎE3¬°D2¤ÎE2À³¸ÓÅã¥Üªº¸ê®Æ

§Ú¨Ï¥Î=LOOKUP(REPT("Æg",255),B:B)¥i¥H¬d¸ß¨ì³Ì«á¤@µ§¸ê®Æ
¦ý¹ïÀ³¨ì¤é´Á...¥ÎMATCH¤Îindex¨ç¼Æ«o¤S§ì¤£¹ï

¦³¤H¥i¸Ñ´b¤@¤U
ÁÂÁÂ

D2Àx¦s®æ§Úª¾¹D¸Ó«ç¥Î¤F
  1. =LOOKUP(REPT("Æg",255),B:B,A:A)
½Æ»s¥N½X
³o¼Ë´N§ì¨ì³Ì«á¤@µ§¸ê®Æ®É¶¡¤F

¦ý¬OE2ÁÙ¬O·Q¤£¥X¨Ó,¦³¨º¦ì°ª¤â¥i¥H¸Ñµª
ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-4-15 16:05 ½s¿è

¦^´_ 2# easygo
1¡B½Ð°é¿ïª¬ºA(A1:B20)¡A«öCtrl+Shift+F3¡A¥H¡u³»ºÝ¦C¡v¬°½d³ò¦WºÙ¡C
2¡B¿é¤J¤U¦C°}¦C¤½¦¡ («öCtrl+Shift+Enter¿é¤J¤½¦¡)
Àx¦s®æD2  =INDEX(¤é´Á,MAX(IF(LEN(ª¬ºA)>0,ROW(ª¬ºA),FALSE))-1)
Àx¦s®æE2  =INDEX(ª¬ºA,MAX(IF(LEN(ª¬ºA)>2,ROW(ª¬ºA),FALSE))-1)
½Ð°Ñ¦Ò¡I

TOP

¥»©«³Ì«á¥Ñ easygo ©ó 2014-4-16 12:09 ½s¿è
¦^´_  easygo
1¡B½Ð°é¿ïª¬ºA(A1:B20)¡A«öCtrl+Shift+F3¡A¥H¡u³»ºÝ¦C¡v¬°½d³ò¦WºÙ¡C
2¡B¿é¤J¤U¦C°}¦C¤½¦¡ ...
p212 µoªí©ó 2014-4-15 15:59


¥i¥H¤F,ÁÂÁÂ

©Ò¥H­n¨Ï¥Î°}¦C¤½¦¡¤~¯à¸Ñ¨M??
°}¦C¤½¦¡¹ï§ÚÁÙ»¡ÁÙ¦³ÂIÃú·Ù·Ù... :L
¥uºCºCºN¯Á.

TOP

D2 =LOOKUP(1,0/("¼Æ¶q"=LEFT($B2:$B20,2)),A2:A20)
¥k©Ô
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-4-16 14:31 ½s¿è

¦^´_ 5# ML089
ª©¤j¯u¼F®`¡I
¥i¥H¸ÑªR¤@¤U¤½¦¡¡H(ÁÙ¤£¤ÓÁA¸ÑLOOKUP¨ç¼Æ¡A²Ä¤G­Ó¤Þ¼Æ0/("¼Æ¶q"=LEFT($B2:$B20,2))¡A¬O¦b³y {#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!...} ªºµ²ªG¡H)
ÁÂÁ¡I

TOP

¦^´_ 6# p212

>> ¥i¥H¸ÑªR¤@¤U¤½¦¡¡H(ÁÙ¤£¤ÓÁA¸ÑLOOKUP¨ç¼Æ¡A²Ä¤G­Ó¤Þ¼Æ0/("¼Æ¶q"=LEFT($B2:$B20,2))¡A¬O¦b³y {#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!...} ªºµ²ªG¡H)
   
¤@¡B0/("¼Æ¶q"=LEFT($B2:$B20,2)) ´N¬O ·í $B2:$B20 «e2¦r¬° "¼Æ¶q" ®É   0/TRUE=0¡A§_®É 0/FALSE=#DIV/0!¡A³y {#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!...} ³o¼Ëªº°}¦C¡C

¤G¡B§Q¥ÎLOOKUP¯S©Ê
1.  ²Ä¤G­Ó¤Þ¼Æ¦³¿ù»~¸ê®Æ#DIV/0!®É·|©¿²¤¡C ¥Î 0/FALSE=#DIV/0! ±N¤£­nªº¸ê®Æ¥h°£¡C
2.  ²Ä¤G­Ó¤Þ¼Æ¥u³Ñ¤U 0 ¬°¦³®Ä¸ê®Æ¡C¥Î 0/TRUE=0 ±N²Å¦Xªº¸ê®ÆÂର0(°£0ªººë¯«)¡C
3. ²Ä¤@­Ó¤Þ¼Æ 1 ¤j©ó ²Ä¤G­Ó¤Þ¼Æ©Ò¦³¸ê®Æ 0 ®É¡A·|¦^¶Ç³Ì«á¤@²Õ 0 ©Ò¹ïÀ³ ²Ä¤T­Ó¤Þ¼Æ¸ê®Æ¡C

¤T¡B§Q¥Î µ´¹ï($)»P¬Û¹ïÀx¦s®æÃö«Y¡A°µ¦¨³æ¤@¤½¦¡
D2 =LOOKUP(1,0/("¼Æ¶q"=LEFT($B2:$B20,2)),A2:A20)
¥k©Ô½Æ»s¤½¦¡¬°E2¤½¦¡¦p¤U
E2=LOOKUP(1,0/("¼Æ¶q"=LEFT($B2:$B20,2)),B2:B20)

¥D­n§PÂ_¦¡¬OBÄæ¡A$B2:$B20±NÄæ¦ì³]©w¦¨µ´¹ï($)¦ì¸m¡AÅý¥k©Ô½Æ»s¤½¦¡®É¤£·|§ïÅÜ
D2¦^¶Ç­È A2:A20¡A±NÄæ¦ì³]©w¦¨¬Û¹ï¦ì¸m¡AÅý¥k©Ô½Æ»s¤½¦¡®É§ïÅܬ° B2:B20

¥|¡BLOOKUP°£¥Î¨Ó§ä°Ï¶¡­È¥~¡A¤U¦¡³Ì±`¨Ï¥Îªº¤@ºØ¤è¦¡
=LOOKUP(1,0/(¤ñ¸û¦¡),¦^¶Ç­È)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-4-16 16:39 ½s¿è

¦^´_ 7# ML089
 ¶W·PÁÂML089ª©¤j¸Ñ¶}§^¤HÃö¥d
¡i²Ä¤@­Ó¤Þ¼Æ 1 ¤j©ó ²Ä¤G­Ó¤Þ¼Æ©Ò¦³¸ê®Æ 0 ®É¡A·|¦^¶Ç³Ì«á¤@²Õ 0 ©Ò¹ïÀ³ ²Ä¤T­Ó¤Þ¼Æ¸ê®Æ¡C
LOOKUP°£¥Î¨Ó§ä°Ï¶¡­È¥~¡A¤U¦¡³Ì±`¨Ï¥Îªº¤@ºØ¤è¦¡ =LOOKUP(1,0/(¤ñ¸û¦¡),¦^¶Ç­È)¡j
ÁÂÁ¦Ѯv¼ö¤ß«ü¾É¡I¶}¤ß¦¬ÂáI

TOP

D2 =LOOKUP(1,0/("¼Æ¶q"=LEFT($B2B20,2)),A2:A20)
¥k©Ô
ML089 µoªí©ó 2014-4-16 14:05


ÁÂÁÂ,µªÂÐ

¦ý¬O³Ì«á¤@¦¸ªº¤é´ÁD2Åã¥ÜÁÙ¦³¦³»~,À³¸Ó¬O§ì³Ì«á¤@µ§¤é´Á,¦Ó¤£¬O³Ì«á¦³¼Æ¶qªº¤é´Á

E3Àx¦s®æ¤½¦¡¥Î
  1. =LOOKUP(1,0/("¼Æ¶q"=LEFT($B2:$B20,2)),B2:B20)
½Æ»s¥N½X
Åã¥Ü´N¥¿½T¤F

¦ý§Ú¥ÎEXECL¥i¥H¥Î³o¤è¦¡
®M¥Î¨ìgoogle¸Õºâªí....´NµLªk¥Î³o¤è¦¡,¨Ï¥Î·|Åã¥Ü#DIV/0!

TOP

¦^´_ 9# easygo

7¼Ó¤w¸g»¡©ú
D2 =LOOKUP(1,0/("¼Æ¶q"=LEFT($B2:$B20,2)),A2:A20)
¥k©Ô½Æ»s¤½¦¡¬°E2¤½¦¡¦p¤U
E2=LOOKUP(1,0/("¼Æ¶q"=LEFT($B2:$B20,2)),B2:B20)


GOOGLE ¤¤ LOOKUP¨Ï¥Î¤è¦¡»PEXCEL¤£¦P¡A©Ò¥H¤£¯à¨Ï¥Î
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¡i»X½ªªº¦Û¥Ñ¡j¤H±`¦b¤°»ò³£¥i¥H¦Û¥Ñ¦Û¦bªº®É­Ô¡A«o³Q³oºØÀH¤ß©Ò±ýªº¦Û¥Ñ»X½ª¡AµêÂY®É¥ú¦Ó²@µLıª¾¡C
ªð¦^¦Cªí ¤W¤@¥DÃD