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

[µo°Ý] ¦³¼h¦¸ªº¾P°âª÷ÃB¤Î°h³fª÷ÃB¤§¤½¦¡¡A­n¦p¦ó³]©w

[µo°Ý] ¦³¼h¦¸ªº¾P°âª÷ÃB¤Î°h³fª÷ÃB¤§¤½¦¡¡A­n¦p¦ó³]©w

±ø¥ó1¡G¦pªG¶R¶i1¦Ü12­Ó¡A1­Ó1080¤¸
±ø¥ó2¡G¦pªG¦A¶R¶i²Ä13¦Ü40­Ó¡A1­Ó970¤¸
±ø¥ó3¡G¦pªG¦A¶R¶i41­Ó¥H¤W¡A1­Ó700¤¸
±ø¥ó4¡G¦pªG¦³°h³f®É¡A¥Î«á¶i¥ý¥Xªk¡A­pºâ°h³fª÷ÃB¡C

¨Ò¦p1¡GA«È¤áÁ`¦@¶R¶i43­Ó(A1)¡A¥I¥X12*1080+(40-12)*970+(43-40)*700¡×42220¤¸(B1)
¨Ò¦p2¡GA«È¤á«á¨Ó°h¤F5­Ó(A2)¡A°h³fª÷ÃB3*700+2*970¡×4040¤¸(B2)
½Ð°Ý¦p¦ó¤U¾P°âª÷ÃB(B1)¤Î°h³fª÷ÃB(B2)¤§¤½¦¡
julie

¥H¤U¬O§Ú¹ï¾P°âª÷ÃB©Ò¤Uªº¤½¦¡¡A½Ð°Ý¦³§ó²¼äªº¤½¦¡¶Ü?
¾P°âª÷ÃB(B1)=IF(A1>40,(A1-40)*700+40120,IF(AND(41>A1,A1>12),(A1-12)*970+12960,IF(A1<13,A1*1080,0)))
¦ý°h³fª÷ÃB(B2)¤§¤½¦¡¡A¤´¥¼·Q¥X¡A½Ð¥ý¶iÀ°À°¦£
julie

TOP

¦^´_ 1# julieh96590

B1=SUMPRODUCT({1080,970,700},TRANSPOSE(FREQUENCY(ROW(INDIRECT("A1:A"&A1)),{12,40})))
B2=SUM(MIN(A2,(A1-40))*700,MIN(28,(A2-MIN(A2,(A1-40))))*970,MAX(0,12-(A1-A2))*1080)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁ¼ӤWª©¥D¸Ñµª¡A¦ý¬O¦pªG¾P°â¼Æ¶q§C©ó40¡Aµª®×´N¤£¾A¥Î¤F¡A¥i§_¦A³Â·Ð±z¡C
julie

TOP

¸É¥R»¡©ú¡G¾P°â¼Æ¶q§C©ó40¡A¥u¦³°h³fª÷ÃBµª®×¤£¾A¥Î¤F¡A¾P°âª÷ÃBµª®×¬Ook
julie

TOP

¦^´_ 5# julieh96590
B2=SUM(MAX(0,MIN(A2,(A1-40)))*700,MIN(28,(A2-MAX(0,MIN(A2,(A1-40)))))*970,MAX(0,12-(A1-A2))*1080)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦A·PÁ¼ӤWª©¥D¸Ñµª¡A¦ý¬O¦pªG¾P°â¼Æ¶q§C©ó16¡B°h³fª÷ÃB¬°5­Ó(4­Ó*970+1­Ó*1080=4960)®É¡Aµª®×´N¤£¾A¥Î¤F¡A¥i§_¦A³Â·Ð±z¡C
julie

TOP

B2°}¦C¤½¦¡
=SUM(SMALL(IF(ROW(INDIRECT("A1:A"&A1))<13,1080,IF((ROW(INDIRECT("A1:A"&A1))>12)*(ROW(INDIRECT("A1:A"&A1))<41),970,700)),ROW(INDIRECT("A1:A"&A2))))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁª©¥D¡Aok¤F¡A¦ý¨ç¼Æ§Ú¥i¯àÁÙ­n®ø¤Æ¤@¤U¡A¤~·|À´¡A
julie

TOP

¦^´_ 9# julieh96590

¾P³fª÷ÃB(a1)=¾P³fª÷ÃB(a1-a2)+°h³fª÷ÃB
©Ò¥H
°h³fª÷ÃB=¾P³fª÷ÃB(a1)-¾P³fª÷ÃB(a1-a2)=
SUMPRODUCT({1080,970,700},TRANSPOSE(FREQUENCY(ROW(INDIRECT("A1:A"&A1)),{12,40})))-SUMPRODUCT({1080,970,700},TRANSPOSE(FREQUENCY(ROW(INDIRECT("A1:A"&(A1-A2))),{12,40})))

TOP

        ÀR«ä¦Û¦b : ºÉ¦h¤Ö¥»¥÷¡A´N±o¦h¤Ö¥»¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD