|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 黑狼-诺 于 2020-1-16 19:29 编辑
Public Function LL(dt As Range)
If dt > #10/24/2015# Then LL = 4.75
Else: LL = 5.5
End Function
我想把dt作为单元格内的时间,然后判断dt在哪个时间范围内,就用那个时间段的贷款利率来计算贷款,但是我这个一直提示函数错误,怎么回事呢?求大神指教
能不能更高级一点,直接实现VBA函数能够计算,在打款日期和截止日期之间总共产生多少利息?
我在最新上传的附件里边用公式解决了问题,但是公式太复杂了,而且太繁复,将来想验证和检查都会困难。所以我觉得VB和数组函数应该能更简单的解决问题,而且方便检查。
VB的循环计算语句应该是最优的选择,因为本金是定值,时间段和不同的时间段产生的利率是变量,每次都是在循环和迭代计算,每次循环的时候能够带入计算不同的时间段和利率,这个循环会很复杂吗?
我不会这个,有大神能够用吗?谢谢
以下是我在表格里编的计算公式:
’=IF(D3>DATE(2015,10,24), DATEDIF(D3,E3,"Y")*C3*K3+DATEDIF(D3,E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2015,8,26),DATEDIF(D3,"2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2015,6,28),DATEDIF(D3,"2015/8/26","D")*(C3*M3/J3)+
DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2015,5,11),DATEDIF(D3,"2015/6/28","D")*(C3*N3/J3)+
DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2015,3,11),DATEDIF(D3,"2015/5/11","D")*(C3*O3/J3)+
DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2014,11,22),DATEDIF(D3,"2015/3/11","D")*(C3*P3/J3)+
DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2014,9,11),DATEDIF(D3,"2014/11/22","D")*(C3*Q3/J3)+
DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2012,7,6),DATEDIF(D3,"2014/9/11","YD")*C3*R3+DATEDIF(D3,"2014/9/11","YD")*(C3*R3/J3)+
DATEDIF("2014/9/11","2015/11/22","D")*(C3*Q3/J3)+DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2012,6,8),DATEDIF(D3,"2012/7/6","D")*(C3*S3/J3)+
DATEDIF("2012/7/6","2014/9/11","Y")*C3*R3+DATEDIF("2012/7/6","2014/9/11","YD")*(C3*R3/J3)+DATEDIF("2014/9/11","2015/11/22","D")*(C3*Q3/J3)+DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2011,7,7),DATEDIF(D3,"2012/6/8","D")*(C3*T3/J3)+
DATEDIF("2012/6/8","2012/7/6","YD")*(C3*S3/J3)+DATEDIF("2012/7/6","2014/9/11","Y")*C3*R3+DATEDIF("2012/7/6","2014/9/11","YD")*(C3*R3/J3)+DATEDIF("2014/9/11","2015/11/22","D")*(C3*Q3/J3)+DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2011,4,6),DATEDIF(D3,"2011/7/7","D")*(C3*U3/J3)+
DATEDIF("2011/7/7","2012/6/8","D")*(C3*T3/J3)+DATEDIF("2012/6/8","2012/7/6","D")*(C3*S3/J3)+DATEDIF("2012/7/6","2014/9/11","Y")*C3*R3+DATEDIF("2012/7/6","2014/9/11","YD")*(C3*R3/J3)+DATEDIF("2014/9/11","2015/11/22","D")*(C3*Q3/J3)+DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2011,2,9),DATEDIF(D3,"2011/4/6","D")*(C3*V3/J3)+
DATEDIF("2011/4/6","2011/7/7","D")*(C3*U3/J3)+DATEDIF("2011/7/7","2012/6/8","D")*(C3*T3/J3)+DATEDIF("2012/6/8","2012/7/6","D")*(C3*S3/J3)+DATEDIF("2012/7/6","2014/9/11","Y")*C3*R3+DATEDIF("2012/7/6","2014/9/11","YD")*(C3*R3/J3)+DATEDIF("2014/9/11","2015/11/22","D")*(C3*Q3/J3)+DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2010,12,26),DATEDIF(D3,"2011/2/9","D")*(C3*W3/J3)+
DATEDIF("2011/2/9","2011/4/6","D")*(C3*V3/J3)+DATEDIF("2011/4/6","2011/7/7","D")*(C3*U3/J3)+DATEDIF("2011/7/7","2012/6/8","D")*(C3*T3/J3)+DATEDIF("2012/6/8","2012/7/6","D")*(C3*S3/J3)+DATEDIF("2012/7/6","2014/9/11","Y")*C3*R3+DATEDIF("2012/7/6","2014/9/11","YD")*(C3*R3/J3)+DATEDIF("2014/9/11","2015/11/22","D")*(C3*Q3/J3)+DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),0)))))))))))))
|
|