0

## EXCEl函數撰寫

a2951058 iT邦新手 5 級 ‧ 2020-04-23 17:09:18 檢舉

### 2 個回答

1

iT邦大神 1 級 ‧ 2020-04-23 17:40:53

``````C5=IF(\$C\$2>\$B5,\$B5,\$C\$2)
C6=IF((\$C\$2-SUM(\$C\$5:\$C5))>\$B6,\$B6,(\$C\$2-SUM(\$C\$5:\$C5)))
C7=IF((\$C\$2-SUM(\$C\$5:\$C6))>\$B7,\$B7,(\$C\$2-SUM(\$C\$5:\$C6)))
C8=IF((\$C\$2-SUM(\$C\$5:\$C7))>\$B8,\$B8,(\$C\$2-SUM(\$C\$5:\$C7)))
C9=IF((\$C\$2-SUM(\$C\$5:\$C8))>\$B9,\$B9,(\$C\$2-SUM(\$C\$5:\$C8)))

D5=IF(B5=C5,0,IF(\$D\$2>(B5-C5),B5-C5,\$D\$2))
D6=IF(B6=C6,0,IF((\$D\$2-SUM(\$D\$5:\$D5))>(B6-C6),B6-C6,(\$D\$2-SUM(\$D\$5:\$D5))))
D7=IF(B7=C7,0,IF((\$D\$2-SUM(\$D\$5:\$D6))>(B7-C7),B7-C7,(\$D\$2-SUM(\$D\$5:\$D6))))
D8=IF(B8=C8,0,IF((\$D\$2-SUM(\$D\$5:\$D7))>(B8-C8),B8-C8,(\$D\$2-SUM(\$D\$5:\$D7))))
D9=IF(B9=C9,0,IF((\$D\$2-SUM(\$D\$5:\$D8))>(B9-C9),B9-C9,(\$D\$2-SUM(\$D\$5:\$D8))))

E5=IF(B5=SUM(C5:D5),0,IF(\$E\$2>(B5-C5-D5),B5-C5-D5,\$E\$2))
E6=IF(\$B6=SUM(\$C6:D6),0,IF((\$E\$2-SUM(\$E\$5:\$E5))>(\$B6-\$C6-D6),\$B6-\$C6-D6,\$E\$2-SUM(\$E\$5:\$E5)))
E7=IF(\$B7=SUM(\$C7:D7),0,IF((\$E\$2-SUM(\$E\$5:\$E6))>(\$B7-\$C7-D7),\$B7-\$C7-D7,\$E\$2-SUM(\$E\$5:\$E6)))
E8=IF(\$B8=SUM(\$C8:D8),0,IF((\$E\$2-SUM(\$E\$5:\$E7))>(\$B8-\$C8-D8),\$B8-\$C8-D8,\$E\$2-SUM(\$E\$5:\$E7)))
E9=IF(\$B9=SUM(\$C9:D9),0,IF((\$E\$2-SUM(\$E\$5:\$E8))>(\$B9-\$C9-D9),\$B9-\$C9-D9,\$E\$2-SUM(\$E\$5:\$E8)))
``````
a2951058 iT邦新手 5 級 ‧ 2020-04-23 21:24:08 檢舉

0
ccenjor
iT邦研究生 2 級 ‧ 2020-04-23 21:12:26

C5儲存格公式為：
=IF(SUM(B\$4:B5)<C\$2,B5,IF(SUM(C\$4:C4)<C\$2,C\$2-SUM(B\$4:B4),0))

D5儲存格公式為：
=IFERROR(IF(SUM(\$C5:C5)=\$B5,0,IF(SUM(D\$4:D4)+\$B5<=D\$2,\$B5-C5,IF(D\$2-SUM(D\$4:D4)+C5>\$B5,\$B5-C5,D\$2-SUM(D\$4:D4)))),"0")

a2951058 iT邦新手 5 級 ‧ 2020-04-23 21:23:37 檢舉

QQ謝謝各位大大