[已解决]将这个自定义函数改写用数组

laoau138

将这个自定义函数改写用数组

最佳答案

月排行榜 / 总排行榜

wenzili

2017-4-4 14:42

Function 应纳税(taxableincome As Variant)
Dim notaxbase As Integer
notaxbase = 1600
taxableincome = taxableincome - notaxbase
Dim taxchart As Variant
Dim i As Long
taxchart = [{0,0,0;500,5,0;2000,10,25;5000,15,125;20000,20,375;40000,25,1375;60000,30,3375;80000,35,6375;100000,40,10375}]
If taxableincome <= 0 Then
    应纳税 = 0
    Exit Function
End If
If taxableincome <= 100000 Then
    For i = LBound(taxchart, 1) To UBound(taxchart, 1) - 1
        If taxableincome > taxchart(i, 1) And taxableincome <= taxchart(i + 1, 1) Then
            应纳税 = taxableincome * taxchart(i + 1, 2) / 100 - taxchart(i + 1, 3)
            Exit Function
        End If
    Next
Else
    应纳税 = taxableincome * taxchart(UBound(taxchart, 1), 2) / 100 - taxchart(UBound(taxchart, 1), 3)
End If
End Function

跳转到最佳答案楼层

砂海

自己搜一下 , "个税公式"

wenzili

简化多条件判断的一个通常做法是表驱动法，从表里面查找信息。
个税的话，可以将个税速算扣除表各档值放入数组如 arr(n,1)=第n档纳税额起点,arr(n,2)=第n档税额终点，arr(n,3)第n档税率，arr（n,4)第n档速算扣除额
好像并没有简化什么。
好处是个税政策变化的话，方便修改。

laoau138

wenzili 发表于 2017-4-4 11:45
简化多条件判断的一个通常做法是表驱动法，从表里面查找信息。
个税的话，可以将个税速算扣除表各档值放入 ...

不懂请定代码了

wenzili

Function 应纳税(taxableincome As Variant)
Dim notaxbase As Integer
notaxbase = 1600
taxableincome = taxableincome - notaxbase
Dim taxchart As Variant
Dim i As Long
taxchart = [{0,0,0;500,5,0;2000,10,25;5000,15,125;20000,20,375;40000,25,1375;60000,30,3375;80000,35,6375;100000,40,10375}]
If taxableincome <= 0 Then
    应纳税 = 0
    Exit Function
End If
If taxableincome <= 100000 Then
    For i = LBound(taxchart, 1) To UBound(taxchart, 1) - 1
        If taxableincome > taxchart(i, 1) And taxableincome <= taxchart(i + 1, 1) Then
            应纳税 = taxableincome * taxchart(i + 1, 2) / 100 - taxchart(i + 1, 3)
            Exit Function
        End If
    Next
Else
    应纳税 = taxableincome * taxchart(UBound(taxchart, 1), 2) / 100 - taxchart(UBound(taxchart, 1), 3)
End If
End Function

wenzili

45的税率漏了，修改一下

Function 应纳税(taxableincome As Variant)
Dim notaxbase As Integer
notaxbase = 1600
taxableincome = taxableincome - notaxbase
Dim taxchart As Variant
Dim i As Long
taxchart = [{0,0,0;500,5,0;2000,10,25;5000,15,125;20000,20,375;40000,25,1375;60000,30,3375;80000,35,6375;100000,40,10375;200000,45,15375}]
If taxableincome <= 0 Then
    应纳税 = 0
    Exit Function
End If
If taxableincome <= taxchart(UBound(taxchart, 1) - 1, 1) Then
    For i = LBound(taxchart, 1) To UBound(taxchart, 1) - 1
        If taxableincome > taxchart(i, 1) And taxableincome <= taxchart(i + 1, 1) Then
            应纳税 = taxableincome * taxchart(i + 1, 2) / 100 - taxchart(i + 1, 3)
            Exit Function
        End If
    Next
Else
    应纳税 = taxableincome * taxchart(UBound(taxchart, 1), 2) / 100 - taxchart(UBound(taxchart, 1), 3)
End If
End Function

wenzili

表查询的好处是便于修改,如改为最新适用的所得税计算,只需修改两句：
notaxbase = 3500
taxchart = [{0,0,0;1500,3,0;4500,10,105;9000,20,555;35000,25,1005;55000,30,2755;80000,35,5505;100000,45,13505}]