Board logo

¼ÐÃD: [µo°Ý] ¦p¦ó´M§ä³Ì«á¤@µ§¸ê®Æ,¤Î³Ì«á¤@µ§¬Û²Å¸ê®Æ? [¥´¦L¥»­¶]

§@ªÌ: easygo    ®É¶¡: 2014-4-14 17:51     ¼ÐÃD: ¦p¦ó´M§ä³Ì«á¤@µ§¸ê®Æ,¤Î³Ì«á¤@µ§¬Û²Å¸ê®Æ?

[attach]18015[/attach]

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

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

¦³¤H¥i¸Ñ´b¤@¤U
ÁÂÁÂ
§@ªÌ: easygo    ®É¶¡: 2014-4-15 15:33

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¸Ñµª
ÁÂÁÂ
§@ªÌ: p212    ®É¶¡: 2014-4-15 15:59

¥»©«³Ì«á¥Ñ 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
§@ªÌ: easygo    ®É¶¡: 2014-4-16 12:07

¥»©«³Ì«á¥Ñ 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¯Á.
§@ªÌ: ML089    ®É¶¡: 2014-4-16 14:05

D2 =LOOKUP(1,0/("¼Æ¶q"=LEFT($B2:$B20,2)),A2:A20)
¥k©Ô
§@ªÌ: p212    ®É¶¡: 2014-4-16 14:27

¥»©«³Ì«á¥Ñ 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
§@ªÌ: ML089    ®É¶¡: 2014-4-16 16:22

¦^´_ 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/(¤ñ¸û¦¡),¦^¶Ç­È)
§@ªÌ: p212    ®É¶¡: 2014-4-16 16:38

¥»©«³Ì«á¥Ñ 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
§@ªÌ: easygo    ®É¶¡: 2014-4-16 22:11

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!
§@ªÌ: ML089    ®É¶¡: 2014-4-16 23:35

¦^´_ 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¤£¯à¨Ï¥Î
§@ªÌ: easygo    ®É¶¡: 2014-4-17 12:36

¦^´_  easygo

7¼Ó¤w¸g»¡©ú
D2 =LOOKUP(1,0/("¼Æ¶q"=LEFT($B2B20,2)),A2:A20)
¥k©Ô½Æ»s¤½¦¡¬°E2¤½¦¡ ...
ML089 µoªí©ó 2014-4-16 23:35


¹ï¤£°_.§Ú¨Ï¥ÎEXECL 2013
D2Àx¦s®æÅã¥ÜÁÙ¬O¤£¹ï,¹³§Ú¶Kªº¹Ï,D2À³¸Ó­nÅã¥Ü2014/4/16
¦ý¬O¥Îª©¥D§Aµ¹ªº¤½¦¡,¦oÅã¥Ü¬O2014/4/13

³o°ÝÃD¸òEXECLª©¥»¦³Ãö«Y¶Ü??
§@ªÌ: easygo    ®É¶¡: 2014-4-17 12:56

§Ú¬Ý¦n¹³¨S¦³¥]§t"¥¼¼Æ"ªº§PÂ_,©Ò¥H´N¦h¥[¤@­Ó,Åܦ¨
  1. D2=LOOKUP(1,0/OR(("¼Æ¶q"=LEFT($B2:$B20,2)),("¥¼¼Æ"=LEFT($B2:$B20,2))),A2:A20)
½Æ»s¥N½X
¤£¹L´N·|¥X²{ #DIV/0!
~~....«ç·|©O??ÀY¤j...   :dizzy:
§@ªÌ: ML089    ®É¶¡: 2014-4-17 13:40

¦^´_ 11# easygo
>>D2Àx¦s®æÅã¥ÜÁÙ¬O¤£¹ï,¹³§Ú¶Kªº¹Ï,D2À³¸Ó­nÅã¥Ü2014/4/16

D2 =LOOKUP("ùÜ",$B:$B,A:A)
©Î
D2 =LOOKUP("ùÜ",$B2:$B20,A2:A20)

¥k©Ô
§@ªÌ: easygo    ®É¶¡: 2014-4-17 13:56

¥»©«³Ì«á¥Ñ easygo ©ó 2014-4-17 13:59 ½s¿è
¦^´_  easygo
>>D2Àx¦s®æÅã¥ÜÁÙ¬O¤£¹ï,¹³§Ú¶Kªº¹Ï,D2À³¸Ó­nÅã¥Ü2014/4/16

D2 =LOOKUP("ùÜ",$BB,A:A) ...
ML089 µoªí©ó 2014-4-17 13:40


ÁÂÁª©¥D
  1. D2=LOOKUP("ùÜ",$B2:$B20,A2:A20)
  2. E2=LOOKUP(1,0/("¼Æ¶q"=LEFT($B2:$B20,2)),B2:B20)
½Æ»s¥N½X
³o¼ËÅã¥Ü¥¿½T¤F




Åwªï¥úÁ{ ³Â»¶®a±Ú°Q½×ª©ª© (http://forum.twbts.com/)