Excel VBA 新个人所得税税率自定义函数in_tax()及用法.doc

Excel VBA 新个人所得税税率自定义函数in_tax()及用法 ?1、VBA代码Public Function in_tax(in_month As Single) As Single ???? Dim sl As Single, kcs As Single, ynse As Single 定义税率sl,扣除数kcs,应纳税额ynse变量 ???? ynse = in_month - 3500???? ??? Select Case ynse??? ??? Case 0 To 1500?????? 如果应纳税额=1500,税率3%,速算扣除数0。???????? sl = 0.03???????? kcs = 0???????? ???? Case 1501 To 4500???? 如果应纳税额(1500,4500],税率10%,速算扣除数105。???????? sl = 0.1???????? kcs = 105???????? ???? Case 4501 To 9000???? 如果应纳税额(4500,9000],税率20%,速算扣除数555。???????? sl = 0.2???????? kcs = 555???????? ???? Case 9001 To 35000??? 如果应纳税额(9000,35000],税率25%,速算扣除数1005。???????? sl = 0.25???????? kcs = 1005???????? ???? Case 35001 To 55000?? 如果应纳税额(35000,55000],税率30%,速算扣除数2755。???????? sl = 0.3???????? kcs = 2755???????? ???? Case 55001 To 80000?? 如果应纳税额(55000,80000],税率35%,速算扣除数5505。???????? sl = 0.35???????? kcs = 5505???????? ???? Case Else????????如果应纳税额80000,税率45%,速算扣除数13505。???????? sl = 0.45???????? kcs = 13505???????? ???? End Select???? ???? If ynse = 0 Then???????? in_tax = 0???? Else???????? in_tax = Round(ynse * sl - kcs, 2)???? End If???? End Function2、使用方法打开Excel,菜单“工具——宏——Visual Basic编辑器”，打开Visual Basic编辑器窗口，“插入——模块”，左侧窗口显示“模块1”双击“模块1”,在右侧代码窗口复制“Public Function.......End Function”(含)之间的自定义函数代码，如下图所示:点击“保存”，退出Visual Basic编辑器窗口。回到工作表界面，in_tax()函数用法如下图所示：如图所示，月薪酬总额为5000时，应纳个税45。b4=in_tax(a4)