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

§PÂ_¤é´Á, §ì¼Æ¦r¥[Á`

§PÂ_¤é´Á, §ì¼Æ¦r¥[Á`

¦U¦ì¦n,


·Q½Ð°Ý¤@¤U,
°ÝÃD1 : ¦b­q³æ¶i«×ªíªºCÄæ, §Ú·Q­nÅã¥Ü­q³æª÷ÃB, §Y¬O²Î­p¦b­q³æ±Æµ{¸Ìªº¼Æ¶q«á, µM«á©M»ù®æªí®æ¸Ì»ù®æ¥[Á`
               ¤§«e¦³¦b³o¸Ì°Ý¹L¤@­Ó¨ç¼Æ¥i¥H³B²z, ½Ð¬Ý¤U¦C"¨ç¼Æ1", ¦ý§Ú²{¦bªº°ÝÃD¬O, ¦b­q³æ·|¤@ª½©¹¤U¼W¥[ªº,¥H¤Î
              »ù®æÁ`ªí¬O·|¦]¬°¤é´Á¤£¦PÅܰʪº, ¨Ò¦p ¤µ¤Ñ2013/03/22, ©Ò¦³²£«~³£§ó·s»ù®æ¤F, ¨º¦b³o­Ó¤é´Á¤§«á­q³æ, ´N­n¥Î·s»ù®æ,
              ³Â·Ð¬O, §Ú­n¤£Â_ªº¦b"¨ç¼Æ1"¥[¤J¤é´Á¤ñ¹ï, ¤~¯à¥[Á`, ¬O§_¦³¨ä¥L¨ç¼Æ¥i¥H¦Û¦æ¶i¦æ¤é´Á¤ñ¸û?
               ­«ÂI¬Oif¨ç¼Æ¤]µLªkµL­­ªº¥[¤U¥h..  

°ÝÃD2 ¸ò²Ä¤@­Ó°ÝÃD¤]¦³ÂI¹³, ¦b­q³æ±Æµ{ªºFÄæ, ­n§ä¥X»ù®æ, ¦]¬°»ù®æ¤]¬OµL­­¥[¤U¥hªº, §Ú¤§«e¥Îªº¨ç¼Æ¤]¤£¾A¥Î, ½Ð¨£¨ç¼Æ2

¥ýÁÂÁ¦U¦ì¤F!!


¨ç¼Æ1:
=IF(B26="","",SUMPRODUCT(N(OFFSET(IF(AND(¤U³æ¤é´Á>SCDate_121118,¤U³æ¤é´Á<SCDate_130102),SC_Price121118,IF(AND(¤U³æ¤é´Á>SCDate_130102,¤U³æ¤é´Á<SCDate_130214),SC_Price130102,SC_Price130320)),MATCH(z,»ù®æÁ`ªí!$C$3:$C$39,)-1,))*y*(B26=x)))

¨ç¼Æ2:
=IF(AND(TEXT(B3,"yyyymmdd")>TEXT(»ù®æÁ`ªí!$H$1,"yyyymmdd"),TEXT(­q³æ±Æµ{!B3,"yyyymmdd")<TEXT(»ù®æÁ`ªí!$I$1,"yyyymmdd")),VLOOKUP(­q³æ±Æµ{!D3,»ù®æÁ`ªí!$C$2:$K$20,6,0),VLOOKUP(­q³æ±Æµ{!D3,»ù®æÁ`ªí!$C$2:$K$20,7,0))



°ÝÃD.rar (185.7 KB)

¦^´_ 1# yc1031

½Ð¥ý¸Õ¸Õ¬Ýªþ¥ó
¦³°ÝÃD¦A°Q½×

°ÝÃD1.rar (591.32 KB)

TOP

¦^´_ 1# yc1031
2#­q³æ¶i«×ªº¤½¦¡¦pªG¬O2003ª©¥»¡A¤£¥i¥H¨Ï¥Î¾ãÄ檺°Ñ·Ó
±z1#ªº½d¨Ò¤¤¦]¬°­q³æ±Æµ{¤é´Á·|¤p©ó»ù®æÁ`ªí¤¤³Ì¤p¤é´Á¡A©Ò¥H­n¥ý¨ú¥N¦¨¹ï·Óªíªº³Ì¤p¤é´Á
ªþ¥ó¤¤¬°ÁYµu¤½¦¡¡A¨Ã§Q©ó¤é«áºûÅ@¡A±N¤@¨Ç°Ñ¼Æ¨Ï¥Î©w¸q¦WºÙ¡A¸Ô¨£ªþ¥ó

°ÝÃD.zip (310.32 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# Hsieh


    °ÝÃD.rar (185.7 KB)

   ¶W¯Åª©¥D ±z¦n,
   
­è¸Õ¤F¨ç¼Æ, ¦ý¦b¤é´Á¤ñ¹ïªº³¡¤À¦³ÂI°ÝÃD, ³Â·Ð±zÀ°§Ú¬Ý¤@¤U, ÁÂÁÂ!

INDEX(OFFSET(a,,,COUNTA(»ù®æÁ`ªí!$H:$H),COUNTA(a)),MATCH(­q³æ±Æµ{!$D3,»ù®æÁ`ªí!$C:$C,0),LOOKUP(MAX(­q³æ±Æµ{!$B3,MIN(a)),a,ROW(a)))

TOP

¦^´_ 4# yc1031

x¦WºÙ­nºâ¥XÄæ¦ì©Ò¥HÀ³¸Ó§ï¦¨
=LOOKUP(IF(­q³æ±Æµ{!$B3<MIN(a),MIN(a),­q³æ±Æµ{!$B3),a,COLUMN(a)-7)
°ÝÃD.zip (319.27 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

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