有點暈啊!應該是可以簡化的,公式為:
=SUM(12*TEXT(E1/12-{0,3,12,25,35,55,80}*1000,"0;!0")*{3,7,10,5,5,5,10}%)+11*LOOKUP(E1/12-1%%,{0,3,12,25,35,55,80}*1000,{0,210,1410,2660,4410,7160,15160})
若是妳有壹個數據列表,比如L2:L8是數據0,3,12,25,35,55,80;M2:M8是數據3%,7%,10%,5%,5%,5%,10%;N2:N8是數據0,210,1410,2660,4410,7160,15160,則公式會更簡單的:
=SUMPRODUCT(12*TEXT(E1/12-L2:L8,"0;!0")*M2:M8)+11*LOOKUP(E1/12-1%%,L2:L8,N2:N8)
若是需要復制並下拉的,則公式為:
=SUMPRODUCT(12*TEXT(E2/12-$L$2:$L$8,"0;!0")*$M$2:$M$8)+11*LOOKUP(E2/12-1%%,$L$2:$L$8,$N$2:$N$8)