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

[µo°Ý] ½Ð°Ý­n¦p¦ó±a¥X¹B¶O³æ»ù?

[µo°Ý] ½Ð°Ý­n¦p¦ó±a¥X¹B¶O³æ»ù?

¥»©«³Ì«á¥Ñ mrcv3 ©ó 2014-2-9 21:05 ½s¿è

½Ð°Ý¦U¦ì¤j¤j

¹B¶O1
²Ä1°Ï¡A·í¤ÑªO¼Æ9ªO¡A¬G¹B¶O³æ»ù¬°700



¹B¶O2
²Ä3°Ï¡A·í¤Ñ­«¶q6257¡A­«¶q¦b5000¥H¤W¡A¬G¹B¶O³æ»ù¬°0.95



¸Ó¦p¦ó³]©w¤~¯à±a¥X¹B¶O³æ»ù? ÁÂÁÂ

¹B¶O.rar (5.09 KB)

¥»©«³Ì«á¥Ñ aer ©ó 2014-2-11 16:01 ½s¿è

¦^´_ 1# mrcv3

¹B¶O1 ªí®æ«Øij­×§ï¦p¤U¹Ï¤º®e,¨Ã¼W¥[»²§UÄæ°Ï§O



E15=IF(C15>="²Ä7°Ï",IF(SUMIFS($D$15:$D$22,$C$15:$C$22,$C15)<=3,INDEX($N$2:$Q$5,MATCH(SUMIFS($D$15:$D$22,$C$15:$C$22,$C15),$N$2:$N$5,0),MATCH($B15,$N$2:$Q$2,0)),($D15-$N$5)*1500+5000),INDEX($A$2:$L$12,MATCH(SUMIFS($D$15:$D$22,$C$15:$C$22,$C15),$A$2:$A$12,0),MATCH($B15,$B$2:$L$2,0)+1))
¦V¤U½Æ»s

¹B¶O2ªí®æ«Øij­×§ï¦p¤U¹Ï¤º®e



D7=IF(SUMIFS($C$7:$C$11,$B$7:$B$11,$B7)>=5000,INDEX($A$2:$O$4,3,MATCH($B7,$B$2:$O$2,0)+1),INDEX($A$2:$O$4,2,MATCH($B7,$B$2:$O$2,0)+1))
¦V¤U½Æ»s

¹B¶O.zip (6.27 KB)

TOP

¦^´_ 1# mrcv3

«Øijªí®æ¯à¦³¤@­P©Ê¡A¸Õ¸Õªþ¥ó
    ¹B¶O.zip (10.29 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# Hsieh

½Ð±Ð¶W¯Åª©¥D¦³¦X¨ÖÀx¦s®æªº­È¬O«ç»ò§ä¥X¨Óªº©O¡HÁÂÁÂ

TOP

ÁÂÁ¦U¦ì¤j¤jªºÀ°¦£

TOP

¦^´_ 4# aer

¬Ý¨ìx©w¸q¦WºÙ¤½¦¡
­Y§@¥Î¤¤Àx¦s®æ¦¡²Ä15¦C¡A¨ä¤¤
LOOKUP(9.9E+307,$A$1:$A15)
·|§ä¨ì15¦C©¹¤W³Ì¥ý§ä¨ìªº¼Æ­È
¤]´N§ä¨ì¦X¨ÖÀx¦s®æ²Ä¤@­Ó¦ì¸mªº­È
¦ÓDÄ檺¤½¦¡¤º
LOOKUP("ùÕ",OFFSET($B$1,,,,MATCH($B15,$B$2:$Q$2,0)))
MATCH($B15,$B$2:$Q$2,0)³o¼Ë·|¦b²Ä2¦C§ä¨ì¦a°Ï¦WºÙªº¦ì¸m
OFFSET($B$1,,,,MATCH($B15,$B$2:$Q$2,0))´N·|±qB1¶}©lÂX®i¦¨MATCH($B15,$B$2:$Q$2,0)ªº½d³ò°µLOOKUPªº·j´M½d³ò
LOOKUP("ùÕ",OFFSET($B$1,,,,MATCH($B15,$B$2:$Q$2,0)))
´N¦b¸Ó½d³ò¤º§ä¨ì³Ì«áªº¤å¦r¡A´N±o¨ì°Ï°ì§O
¦b¦¹§ó¥¿ªþ¥ó¤ºD15ªº¤½¦¡
=(MATCH(B15,$B$2:$Q$2,0)>13)*(MAX(0,(C15-3))*1500+5000)+(MATCH(B15,$B$2:$Q$2,0)<13)*(INDEX($B$3:$Q$12,SUMIF(x,LOOKUP("ùÕ",OFFSET($B$1,,,,MATCH($B15,$B$2:$Q$2,0))),$C$15:$C$65536),MATCH(LOOKUP("ùÕ",OFFSET($B$1,,,,MATCH($B15,$B$2:$Q$2,0))),$B$1:$Q$1,0)))
§â­pºâCÄæ½d³òÂX¥R¨ì¤u§@ªíÁ`¦C¼Æ
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh

ÁÂÁ¶W¯Åª©¥Dªº¸Ñ»¡¡I
ÁÙ¦³¤T­Ó°ÝÃD½Ð±Ð¡G
① ¦³Ãö¦WºÙ X ¬°¦ó¤Á´«¨ì¤£¦Pªº¤u§@ªí®É¡A¨ä©w¸q¦WºÙªº°Ñ¦Ò¦ì¸m·|¦Û°Ê§ïÅÜ¡A³o¼Ëªº³]­p¤è¦¡¦³¤°»ò¸ê®Æ¥i¥H°Ñ¦Ò¡H
② ¬O§_¤G­Ó¤u§@ªí¬Ò©w¸q¬°¦P¤@­Ó¦WºÙ¡H¦ý¬O¡A©w¸qªº½d³ò¤£¦P¡H¦pªG¬Oªº¸Ü¡A¤S­n¦p¦ó¨Ó¿ë§O·í«e©Ò¿ï¾Üªº¤u§@ªí¬O¨º¤@­Ó¡H¸Ó¥Î¨º¤@­Ó¦WºÙ½d³ò©O¡H
③ ´N¬Olookup_value ¬°¤°»ò­n¥Î¤¤¤å¦r¡H¬O¦]¬°­n¬d¸ßªº­ì©l¸ê®Æ¬O¤å¦r®æ¦¡ªºÃö«Y¶Ü¡HÁÙ¦³¬°¤°»ò­n¥Î"ùÕ"³o­Ó¦r¡H¨ä¥L¦r¬O§_¤]¥i¥H¡H©Î¬O¥u­n¿é¤J¤ñ¹º¦h¤@ÂIªº¤¤¤å¦r§Y¥i¡H§Ú¦³¸ÕµÛ±N¤¤¤å¦r§ï¬°"øÊ"¡A¤]¬O¤@¼Ë¥i¥H¨Ï¥Î¡C¤£¤F¸Ñ¦¹³]­pªº¥Î·N¬°¦ó¡H·Ð½Ð«ü±Ð¡A·PÁ±z¡I

TOP

¦^´_ [urltex87883828=http://forum.twbts.com/redirect.php?goto=findpost&pid=64048&ptid=11208]7#[/url] aer

x¦WºÙ¤§©Ò¥H¥i¥H·í¿ï¾Ü¤£¦P¤u§@ªí¥ç¥i¦Û°ÊÅܤƤu§@ªí°Ñ·Ó
³o¬O¦]¬°¬Û¹ï°Ñ·Ó®æ¦¡©Ò­P¡A¬Ý¨ì¦WºÙªº¤½¦¡
½d³ò°Ñ·Ó«e¥[¤W¡I¡A³o¼Ëªº°Ñ·Ó¼Ë¦¡¥u¯à¦b©w¸q¦WºÙ¨Ï¥Î
·N«ä¬O·í«e§@¥Î¤¤¤u§@ªíªº°Ñ·Ó
lookup¤¤¨Ï¥ÎùÕ¦r°µ¬°·j´M­È¡A¦]¬°¦¹¦r¬O­Ó¤H©Òª¾³Ì¤jªº¦r½X
©Ò¥H¦b·j´M®É´N¤£·|¦]¬°¸ê®Æ¤¤¥X²{§ó¤jªº¦r½X¡A·íµM¡A¦pªG§A½T©w¸ê®Æ¤¤³Ì¤jªº¦r½X
¥Î¤j©óµ¥©ó¸Ó¦r°µ·j´M­È¥ç¥i¡A­Y¬O­n·j´M¸ê®Æ¤¤³Ì«áªº¼Æ­È
«h¨Ï¥ÎEXCEL®e³\ªº³Ì¤j­È9.9E+307
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# Hsieh

ÁÂÁ¶W¯Åª©¥Dªº¸Ñ´b¡I·P®¦

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