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

½Ð±Ð¤j®a¦³ÃöÀx¦s®æ¨ú¥¿¼Æªº°ÝÃD

½Ð±Ð¤j®a¦³ÃöÀx¦s®æ¨ú¥¿¼Æªº°ÝÃD

½Ð±Ð¤@¤U¤j®a:
A1=123...A2=100...A3=200...A4=280
B1=50...B2=200...B3=270...B4=200

§Ú­nÅýC1¥h­pºâ(B1-A1)+(B2-A2)+(B3-A3)+(B4-A4)
·íBn<An«h¤£­p=0+100+70+0=170

½Ð°Ý§Ú­n«ç»ò¼g©O?
³Â·Ð¤j®a¤F

=SUMPRODUCT((B1:B4>A1:A4)*(B1:B4-A1:A4))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁª©¥D,§Ú¸Õ¸Õ¬Ý...
·P®¦...
¦^´_ 2# Hsieh

TOP

ÁÙ¦³­Ó°ÝÃD·Q½Ð±Ð¤@¤U..§Ú¦³¤@­ÓÀx¦s®æ¤½¦¡¦p¤U,½Ð°Ý¦³¨S¦³¤èªk¥i¥HÁY´î¤½¦¡©O?(¨Ò¦p¥Îsum,¤p§Ìµ{«×«Ü¸ê²L,ÁٽЦh¥]²[..·P®¦)

=((IF(B28-$D$2<0,0,B28-$D$2)+IF(B28-$E$2<0,0,B28-$E$2)+IF(B28-$F$2<0,0,B28-$F$2)+IF(B28-$G$2<0,0,B28-$G$2)+IF(B28-$H$2<0,0,B28-$H$2)+IF(B28-$I$2<0,0,B28-$I$2)+IF(B28-$J$2<0,0,B28-$J$2)+IF(B28-$K$2<0,0,B28-$K$2)+IF(B28-$L$2<0,0,B28-$L$2)+IF(B28-$M$2<0,0,B28-$M$2)+IF(B28-$N$2<0,0,B28-$N$2)+IF(B28-$O$2<0,0,B28-$O$2)+IF(B28-$P$2<0,0,B28-$P$2)+IF(B28-$Q$2<0,0,B28-$Q$2)+IF(B28-$R$2<0,0,B28-$R$2))*50*-1)+$S$10

TOP

¸Õ¸Õ¬Ý
=sumproduct(((b28-$d$2:$r$2)>0)*(b28-$d$2:$r$2))*(-50)+$s$10
¨SÅçºâ,¦Û¤v¸Õ¸Õ¬Ý
³Ì¦n¤W¶ÇÀɮפγ̷|µ²ªG¬°¦ó¤è«KÀ˵ø
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

¦^´_ 5# gong


    ·PÁÂgong ªO¥D,§Ú¸Õ¸Õ¬Ý..·P®¦ªü

TOP

        ÀR«ä¦Û¦b : «H¤ß¡B¼Ý¤O¡B«i®ð¤TªÌ¨ã³Æ¡A«h¤Ñ¤U¨S¦³°µ¤£¦¨ªº¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD