u7490074

檔案欄位N~AE：是算費用分攤的月份(算頭不算尾)，超過12(即Y欄)，就算是隔年(本商品最多發行6個月，所以才有13月~18月)
==>單一儲存格公式太多，檢核很辛苦會漏，且每換一列，追綜列就要動態變動

檔案如下網址 : 要下載到個人電腦才能看~
https://docs.google.com/file/d/0BytNKnm2laTvQXFaQUE2bTNRRlNXMThwQjFFOVlNR0gzTFF3/edit?usp=sharing

u7490074

檔案欄位N~AE：是算費用分攤的月份(算頭不算尾)，超過12(即Y欄)，就算是隔年(本商品最多發行6個月，所以才有 ...
u7490074 發表於 2014-9-30 15:30

對了,因為這樣長的公式←無果再加上INDIRECT("M"&ROW())則因為公式太長會被EXCEL拒絕再修改~~~困擾且不好編修~~


【R13儲存格】
=IF(AND(R12<=12,DATE(YEAR(INDIRECT("$b"&ROW())),MONTH(INDIRECT("$b"&ROW()))+1,)=DATE(YEAR(INDIRECT("$b"&ROW())),R12+1,)),ROUND($H13*($L13/$G13),0),IF(AND(R12<=12,DATE(YEAR(INDIRECT("$c"&ROW())),MONTH(INDIRECT("$c"&ROW()))+1,)=DATE(YEAR(INDIRECT("$b"&ROW())),R12+1,)),ROUND($H13*($M13/$G13),0),IF(AND(R12>12,DATE(YEAR(INDIRECT("$C"&ROW())),MONTH(INDIRECT("$C"&ROW()))+1,)=DATE(YEAR(INDIRECT("$b"&ROW()))+1,(R12-12)+1,)),ROUND($H13*($M13/$G13),0),0)))+IF(AND(R12<=12,DATE(YEAR(INDIRECT("$b"&ROW())),MONTH(INDIRECT("$b"&ROW()))+1,)<DATE(YEAR(INDIRECT("$b"&ROW())),R12+1,),DATE(YEAR(INDIRECT("$b"&ROW())),R12+1,)<DATE(YEAR(INDIRECT("$C"&ROW())),MONTH(INDIRECT("$C"&ROW()))+1,)),ROUND($H13*(DAY(DATE(YEAR(INDIRECT("$C"&ROW())),R12+1,))/$G13),0),)+IF(AND(R12>12,DATE(YEAR(INDIRECT("$b"&ROW())),MONTH(INDIRECT("$b"&ROW()))+1,)<DATE(YEAR(INDIRECT("$b"&ROW()))+1,(R12-12)+1,),DATE(YEAR(INDIRECT("$b"&ROW()))+1,(R12-12)+1,)<DATE(YEAR(INDIRECT("$C"&ROW())),MONTH($C13)+1,)),ROUND($H13*(DAY(DATE(YEAR($B13)+1,(R12-12)+1,))/$G13),0),0)

ML089

回復 12# u7490074

檔案我看了，但沒有看到使用 INDIRECT("M"&ROW())的目的，無從了解及說明，是否在再補充一下
使用原公式中使用 INDIRECT("M"&ROW()) 這種方法太複雜

你可以將原先公式中需要的6個儲存格分別用引用
R1 = 13
R2 = INDIRECT("B"&R1)
R3 = INDIRECT("C"&R1)
R4 = INDIRECT("G"&R1)
R5 = INDIRECT("H"&R1)
R6 = INDIRECT("L"&R1)
R7 = INDIRECT("M"&R1)
再將R2~R7帶入公式中
你也可以先複製表格，用 剪下/貼上 的方式來移動 B13、C13...L13、M13，公式就能自動改變，再將公式內容複製你要的地方。

ML089

回復 12# u7490074
N13
=IF(AND(N$12<=12,DATE(YEAR($B13),MONTH($B13)+1,)=DATE(YEAR($B13),N$12+1,)),ROUND($H13*($L13/$G13),0),IF(AND(N$12<=12,DATE(YEAR($C13),MONTH($C13)+1,)=DATE(YEAR($B13),N$12+1,)),ROUND($H13*($M13/$G13),0),IF(AND(N$12>12,DATE(YEAR($C13),MONTH($C13)+1,)=DATE(YEAR($B13)+1,(N$12-12)+1,)),ROUND($H13*($M13/$G13),0),0)))+IF(AND(N$12<=12,DATE(YEAR($B13),MONTH($B13)+1,)<DATE(YEAR($B13),N$12+1,),DATE(YEAR($B13),N$12+1,)<DATE(YEAR($C13),MONTH($C13)+1,)),ROUND($H13*(DAY(DATE(YEAR($C13),N$12+1,))/$G13),0),IF(AND(N$12>12,DATE(YEAR($B13),MONTH($B13)+1,)<DATE(YEAR($B13)+1,(N$12-12)+1,),DATE(YEAR($B13)+1,(N$12-12)+1,)<DATE(YEAR($C13),MONTH($C13)+1,)),ROUND($H13*(DAY(DATE(YEAR($B13)+1,(N$12-12)+1,))/$G13),0),0))

N13公式可以簡化，只需要 起日期/迄日期/利息總費用及月數等資料就可以計算

=ROUND($H13/($C13-$B13)*FREQUENCY(ROW(INDIRECT($B13&":"&$C13-1)),DATE(YEAR($B13),N$12+1,)),) - SUM($M13:M13,-$M13)

每月利息費用會四捨伍入，誤差累積超過1元時該月會增加1元

ML089

回復 12# u7490074


    AG13驗證合計這樣寫會有問題，因為EXCEL儲存格可以格式化為整數但其實是有含小數的數字，用SUM(....)的總數會對，但用眼睛看用計算機計算就是會不一樣。

將 =SUM(N13:AE13)
改為 =SUMPRODUCT(ROUND(N13:AE13,0))

ML089

回復 14# ML089


    14樓公式使用陣列方式比較慢一些，直接數學計算比較快

N13
=ROUND($H13/($C13-$B13)*(MAX(0,MIN($C13,DATE(YEAR($B13),N$12+1,1))-$B13)),) - SUM($M13:M13,-$M13)

公式 = 四捨伍入至整數( 總利息/總天數 * (起日期至各月底天數) ) - 前面的利息合計

u7490074

回復  u7490074

檔案我看了，但沒有看到使用 INDIRECT("M"&ROW())的目的，無從了解及說明，是否在再補充 ...
ML089 發表於 2014-10-1 09:05

INDIRECT("M"&ROW())或INDIRECT("b"&ROW())或INDIRECT("c"&ROW())...等 : 均是考量在N13儲存格垂直下拉時，列號會變，才用這樣的土方式啦~

ML089

回復 17# u7490074

你這說明是函數說明，我知道。
如果只是= INDIRECT("M"&ROW()) 下拉，是否採用 =M13 下拉效果一樣   

例如
N13 =INDIRECT("M"&ROW())
N13 =M13
以上兩式下拉效果相同，當然採用 N13 =M13

一般使用
A2 輸入你要的列號，例如 13
B2 =INDIRECT("B"&A2) 起日
C2 =INDIRECT("C"&A2) 迄日
D2 =INDIRECT("H"&A2) 利息
E2 輸入你的公式，B2、C2、D2就是你的公式裡參數

u7490074

回復  u7490074

你這說明是函數說明，我知道。
如果只是= INDIRECT("M"&ROW()) 下拉，是否採用 =M13 下 ...
ML089 發表於 2014-10-1 14:34

拍謝啦~應該 是說= INDIRECT("M"&ROW()) 是要固定每列用m欄儲存格，與水平由1月(n13)拖曳到18月時，能利用ROW()固定找該列的m儲存格(即m13)

如果：直接寫N13=M13，則拖曳到O13時，則O13=N13，而非O13仍=M13啦~~前面說明寫錯了~

ML089

回復 19# u7490074

拍謝啦~應該 是說= INDIRECT("M"&ROW()) 是要固定每列用m欄儲存格，與水平由1月(n13)拖曳到18月時，能利用ROW()固定找該列的m儲存格(即m13)

如果：直接寫N13=M13，則拖曳到O13時，則O13=N13，而非O13仍=M13啦~~前面說明寫錯了~

要水平右拉 =$M13 固定 M 不動