15问答网 > 如下的计算，excel中的if函数嵌套层数超过允许值以上怎么处理？

如下的计算，excel中的if函数嵌套层数超过允许值以上怎么处理？

2024-12-03 19:49:51

推荐回答（2个）

回答1：

公式也很长。。。
=(A16-LOOKUP(A16,{0,100.001,500.001,1000.001,5000.001,10000.001,50000.001,100000.001,500000.001,1000000.001},{0,100,500,1000,5000,10000,50000,100000,500000,1000000}))*INDIRECT("C"&LOOKUP(A16,{0,100.001,500.001,1000.001,5000.001,10000.001,50000.001,100000.001,500000.001,1000000.001},{4,5,6,7,8,9,10,11,12,13}))+IF(A16>100,SUM(INDIRECT("D4:D"&LOOKUP(A16,{0,100.001,500.001,1000.001,5000.001,10000.001,50000.001,100000.001,500000.001,1000000.001},{4,5,6,7,8,9,10,11,12,13})-1)),0)

回答2：

用 vba 吧看着就头大
Sub ss()
Dim i, j, l, mg

Select Case [a16]
Case Is <= 100: i = 3: j = 0
Case Is <= 500: i = 4: j = 100
Case Is <= 1000: i = 5: j = 500
Case Is <= 5000: i = 6: j = 1000
Case Is <= 10000: i = 7: j = 5000
Case Is <= 50000: i = 8: j = 10000
Case Is <= 100000: i = 9: j = 50000
Case Is <= 500000: i = 10: j = 100000
Case Is <= 1000000: i = 11: j = 500000
Case Is > 1000000: i = 12: j = 1000000
End Select

If i = 3 Then
[b16] = [a16] * [c4]
Else
mg = Range(Cells(i, 4), Cells(4, 4))

[b16] = ([a16] - j) * Cells(i + 1, 3) + WorksheetFunction.Sum(mg)
End If
End Sub