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

Question

=IF(A16>1000000,(A16-1000000)*C13+D12+D11+D10+D9+D8+D7+D6+D5+D4,IF(A16>500000,(A16-500000)*C12+D11+D10+D9+D8+D7+D6+D5+D4,IF(A16>100000,(A16-100000)*C11+D10+D9+D8+D7+D6+D5+D4,IF(A16>50000,(A16-50000)*C10+D9+D8+D7+D6+D5+D4,IF(A16>10000,(A16-10000)*C9+D8+D7+D6+D5+D4,IF(A16>5000,(A16-5000)*C8+D7+D6+D5+D4,IF(A16>1000,(A16-1000)*C7+D6+D5+D4,IF(A16>500,(A16-500)*C6+D5+D4,IF(A16>100,(A16-100)*C5+D4,IF(A16<=100,A16*C4))))))))))

Answer 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)

Answer 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本回答被网友采纳