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

[µo°Ý] ¦h­«±ø±ø¥ó¬d¸ß

[µo°Ý] ¦h­«±ø±ø¥ó¬d¸ß

½Ð±Ð¦U¦ì«e½ú­Ì
»Ý¨D¡G©óD6¡BE6¡BF6¡A¤À§O¿é¤J¦~«×¡B³f§O¡B³f¸¹«á¡A¥i¦Û°Ê¦bH6 ~ K6Åã¥Ü©Ò¹ïÀ³ªº³f¬[¦ì¸mªº¤½¦¡­n¦p¦ó¼g¡H
tt.jpg
2013-11-28 20:27

test.zip (36.72 KB)

¦^´_ 1# aer

§A¤£¬O¤w¸g¼g¦n¤F¶Ü?
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_  aer

§A¤£¬O¤w¸g¼g¦n¤F¶Ü?
ML089 µoªí©ó 2013-11-28 21:26


¬O¦³¼g¤F¡A¥i¬O¥u¦³97¦~«×¥i¥H¬d¸ß¡A¦p§ïÅܨä¥L±ø¥ó®É¡A¤£·|§ïÅܬd¸ßµ²ªG¡C·Ð½ÐÀ°¦£¡A·PÁ¡I

TOP

  1. H6 =LOOKUP(,0/(($D6=¸¹½X¸ê®Æ®w!$A$2:$A$1999)*($E6=¸¹½X¸ê®Æ®w!$B$2:$B$1999)*($F6>=¸¹½X¸ê®Æ®w!$C$2:$C$1999)*($F6<=¸¹½X¸ê®Æ®w!$D$2:$D$1999)),¸¹½X¸ê®Æ®w!E$2:E$1999)
  2. ¥k©Ô
½Æ»s¥N½X
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 4# ML089

ML089 ÁÂÁ±z!´ú¸Õ¨S°ÝÃD¡C¦A½Ð±Ð±z lookup ¤½¦¡¤¤ªº lookup value ¤£¶ñ¥ô¦ó­È¬O¥Nªí¤°»ò·N«ä©O¡H

TOP

¦^´_ 5# aer


>> ¦A½Ð±Ð±z lookup ¤½¦¡¤¤ªº lookup value ¤£¶ñ¥ô¦ó­È¬O¥Nªí¤°»ò·N«ä©O¡H

lookup value ¤£¶ñ¥ô¦ó­È¬O¥Nªí 0
²Ä¤G¶µ°Ñ¼Æ¤ºªº¤½¦¡¸g¤ñ¹ï«á¬° 0 ©Î ¿ù»~­È¡Alookup value ¥Î0¥i¥H§ä¨ì¨ä¤¤¤@¶µ¡A­Y¥Î1¥i¥H§ä¨ì³Ì«á¤@¶µ¡C¤@¯ë±¡ªp¥u¦³¤@­Ó 0 ©Ò¥H²ßºD¤£¤U°Ñ¼Æ¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ML089
³o¦^¥i­nºÙ©I§A¦Ñ®v¤F¡C
  1. H6 =LOOKUP(,0/(($D6=¸¹½X¸ê®Æ®w!$A$2:$A$1999)*($E6=¸¹½X¸ê®Æ®w!$B$2:$B$1999)*($F6>=¸¹½X¸ê®Æ®w!$C$2:$C$1999)*($F6<=¸¹½X¸ê®Æ®w!$D$2:$D$1999)),¸¹½X¸ê®Æ®w!E$2:E$1999)
  2. ¥k©Ô
½Æ»s¥N½X
½Ð¦Ñ®v«ü¾É¡A$D6=¸¹½X¸ê®Æ®w!$A$2:$A$1999)*($E6=¸¹½X¸ê®Æ®w!$B$2:$B$1999)
¬°¦ó©¼¦¹­n¥Î­¼ (*) ¨Ó¦ê³s¡AµM«á¤S¥Î¹s (0) ¨Ó³Q°£¡H
LOOKUP(, 0  ¤ºªº ",0" ³o¤S¬O«üªº¬Æ»ò¡H  Help ¤¤¬Æ»ò¤]¬d¤£¨ì¡AÆZ¦n©_ªº¡C
P.S.  ¤£¦n·N«ä¡A¸I¨ì¤½¦¡§Ú¯uªº¤£¦æ (¹D¦æ¤Ó²L¤F)¡C^0^

TOP

¦^´_ 7# c_c_lai

>> $D6=¸¹½X¸ê®Æ®w!$A$2:$A$1999)*($E6=¸¹½X¸ê®Æ®w!$B$2:$B$1999)
>> ¬°¦ó©¼¦¹­n¥Î­¼ (*) ¨Ó¦ê³s¡AµM«á¤S¥Î¹s (0) ¨Ó³Q°£¡H
>> LOOKUP(, 0  ¤ºªº ",0" ³o¤S¬O«üªº¬Æ»ò¡H

c_c_lai¤j ¤Ó«È®ð¤F¡A¤j®a¬Û¤¬¾Ç²ß¤¬³q¦³µL

H6 =LOOKUP(,0/(($D6=¸¹½X¸ê®Æ®w!$A$2:$A$1999)*($E6=¸¹½X¸ê®Æ®w!$B$2:$B$1999)*($F6>=¸¹½X¸ê®Æ®w!$C$2:$C$1999)*($F6<=¸¹½X¸ê®Æ®w!$D$2:$D$1999)),¸¹½X¸ê®Æ®w!E$2:E$1999)

¤W¦¡¤¤¬õ¦â¦r¬°§PÂ_¦¡¡A¾ã­Ó¦¡¤lºc¬[¦p¤U
H6 =LOOKUP(,0/(§PÂ_¦¡°}¦C),¸¹½X¸ê®Æ®w!E$2:E$1999)
H6 =LOOKUP(,0/(0;1;0;1;0;...;0),¸¹½X¸ê®Æ®w!E$2:E$1999)
0/{0;1}±o±¡ªp¦p¤U 0/1 =0 ; 0/0 = #DIV/0!¡A¤W¦¡¦p¤U
H6 =LOOKUP(,0/(#DIV/0!;0;#DIV/0!;0;#DIV/0!;...;#DIV/0!),¸¹½X¸ê®Æ®w!E$2:E$1999)
¾ã­Ó lookup_vector ´N·|Åܦ¨¥u¦³ 0 »P ¿ù»~­È#DIV/0!©Ò²Õ¦¨ªº°}¦C¡C
§Q¥ÎLOOKUPªº¨ç¼Æ¯S©Ê - ¿ù»~­È ·|©¿²¤¡A©Ò¥H¬d¸ß­È¥Î >= 0 ªº¼Æ­È³£¥i¥H

¦pªG§ä°ß¤@­È¡Alookup_vector ¤º¥u¦³¤@­Ó 0 ­È¡A©Ò¥Hlookup_value¥Î0´N¥i¥H¡A0¬°¤º©w­È´N¤£¥Î¿é¤J
¦pªG¤£¬O°ß¤@­È¡A¥i¥H¥Î 1( >0ªº¥ô¤@­È³£¥i¥H)§ä³Ì«á¤@­È¡A¥Î 0 ·|§ä¨ì²Ä¤@²Õ0ªº³Ì«á¤@­Ó¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 8# ML089
ÁÂÁ§A¸ÔºÉ¦a¸Ñ»¡¡A·Pı¤W¤ñ¨Ï¥Î VBA ÁÙ©â¶H¡A
³o¥i¯à¬O§Ú±q¥¼¨Ï¥Î¹L LookUp ªº­ì¦]§a¡I

TOP

¸ô¹L¾ß¨ìÄ_~½Ð±Ð¤@¤UML089¤j¤j
³o¨ç¼Æªº·N«ä¬O¤£¬O§Q¥Î°}¦Cªº¥|­Ó±ø¥ó·í§@·j´M±ø¥ó¡A
¥Î"0/"¨Ó½T«O4­Ó±ø¥ó§¡¦¨¥ß¡A¨Ó§ä¨ì³f¬[¦ì¸m
¦Ó±z´£¨ì¦pªG¤£¬O°ß¤@­È¡A¥i¥H¥Î 1¡A½Ð°Ý¬O«ü
LOOKUP(1,0/~¡AÁÙ¬OLOOKUP(,1/~
®©©Ê¤£¨¬¡A½Ð¦h«ü±Ð¡AÁÂÁ¡I
Adam

TOP

        ÀR«ä¦Û¦b : ¦a¤WºØ¤Fµæ¡A´N¤£©öªø¯ó¡F¤ß¤¤¦³µ½¡A´N¤£©ö¥Í´c¡C
ªð¦^¦Cªí ¤W¤@¥DÃD