用Excel计算个人工资所得税的四套方案课件

2016年04月用Excel计算个人工资所得税的四套方案一、个人工薪所得纳税相关背景知识1、个人取得的工资、薪金所得,是指个人因任职或者受雇而取得的工资、薪金、奖金、年终加薪、劳动分红、津贴、补贴以及与任职或受雇有关的其他所得;个人所得税是对按税法规定具有纳税义务的中国公民和外籍人员的个人收入或所得征收的一种税;2、工资、薪金所得项目税率表：表13、工资、薪金所得按以下步骤计算缴纳个人所得税：每月取得工资收入后,先减去个人承担的基本养老保险金、医疗保险金、失业保险金,以及按省级政府规定标准缴纳的住房公积金,再减去费用扣除额1600元/月来源于境外的所得以及外籍人员、华侨和香港、澳门、台湾同胞在中国境内的所得每月还可附加减除费用3200元,为应纳税所得额,按5%至45%的九级超额累进税率计算缴纳个人所得税;计算公式是：应纳个人所得税税额=应纳税所得额×适用税率—速算扣除数例：王某当月取得工资收入9000元,当月个人承担住房公积金、基本养老保险金、医疗保险金、失业保险金共计1000元,费用扣除额为1600元,则王某当月应纳税所得额=9000-1000-1600=6400元;应纳个人所得税税额=6400×20%-375=905元;4、个人取得工资、薪金所得应缴纳的个人所得税,统一由支付人负责代扣代缴,支付人是税法规定的扣缴义务人;5、根据十届全国人大常委会第十八次全体会议关于修改〈中华人民共和国个人所得税法〉的决定,自2006年1月1日起,工薪所得个调税起征点执行中国公民1600元、外籍人士4800元的标准,计征方法仍执行原定九级累进方案如表1所示;二、工薪所得个调税的Excel计算解决方案表2方案一——IF嵌套版：优点：简单明了,上手容易方案二——辅助数据版：在E、F、G、H、I五列插入辅助列如表2所示;缺点：需要借助于辅助列,可移植性不强;方案三——数组公式版：优点：同样克服了IF嵌套版不能涵盖全部九级累进区间的缺陷,且公式内数据相对容易维护,整个公式维护起来也比较简单;缺点：数组公式的操作方法比较独特,每次修改后都要用Ctrl+Shift+Enter组合键加以识别,且可扩展性不如在后台运行的VBA代码;P.S. ：数组公式：数组公式对一组或多组值执行多重计算,并返回一个或多个结果;数组公式括于大括号{}中;按Ctrl+Shift+Enter可以输入数组公式;方案四——VBA代码版：工具 -- 宏--VisualBasic编辑器--插入--模块=============复制以下代码至编辑窗口================计算个人收入调节税IndividualIncomeAdjustmentTaxFunction iiataxx,yDim basicnum As IntegerDim downnum As Variant, upnum As Variant,ratenumAsVariant,deductnum As Variant If y = 0 Thenbasicnum=1600定义中国公民个税起征点ElseIf y = 1 Thenbasicnum=4800定义外国公民个税起征点Else: basicnum = NullEnd Ifdownnum = Array0, 500, 2000, 5000, 20000,40000,60000,80000,100000定义累进区间下限upnum = Array500, 2000, 5000, 20000, 40000,60000,80000,100000,100000000定义累进区间上限ratenum = Array0.05, 0.1, 0.15, 0.2, 0.25, 0.3,0.35,0.4,0.45定义累进税率deductnum = Array0, 25, 125, 375, 1375, 3375,6375,10375,15375定义累进速算扣除数If IsNumericx = False ThenMsgBox请检查计税工资是否为数值End IfIf x < 0 ThenMsgBox计税工资为负,重新输入End IfIf x >= 0 And x < basicnum Theniiatax = 0End IfFor i = 0 To UBounddownnumIf x - basicnum > downnumi And x -basicnum<=upnumiTheniiatax = Round x - basicnum ratenumi-deductnumi,2End IfNext iEnd Function=============复制以上代码至编辑窗口================P.S. ：1、iiatax参数1,参数2,参数1引用计税工资,参数2用0表示中国公民的所得税起征点,用1表示外国公民的起征点;2、使用时可以像使用函数一样,如B2=iiataxA2,0,或者B2=iiatax6500,1;如何避免以下情况ETXCEL计算工资,经常重复名字,用条件格式显示相同的名字,有的可以显示,有的因为手误输入,没有显示出来,今个月,到发工资时才发现重复了一个人名,如何可以杜绝这种失误 THS;;1、用公式不能直接解决输入名字时的手误：如,将“张大”误输入“张达”,用任何公式或条件格式,都不回视为重复;解决办法有：在新表名字列如A列后增加辅助B列,在B2输入“=VLOOKUPA2,上月正确名字区域,1,”,向下复制到需要处,B列返回错误符号者,即是本月笔误或本月新增加人员；核对后,再将B列删除;2、例如姓名在A列,B列为空在B1输入公式=COUNTIFA:A,A1向下填充到需要的地方,值就是对应的A列中的姓名在A列出现的次数,如果值大于1就是重复了3、A、姓名较多,手工输入有可能出差错,导致输入重名这种情况解决的办法是将所有员工的姓名单独放在一列中,然后在需要用到姓名的地方,使用“数据有效性”中的“序列”定义,每次要输姓名的时候,直接按下拉按钮选择即可,既提高录入速度,也不会因操作失误搞错人名；B员工较多,有重名现象这种情况也比较常见,最好的做法是在工资表里增加员工编号没有重复或者身份证号;另外新建一张表,用来存储员工的基本信息,在工资表里使用Vlookup来根据员工编号或者身份证号查询员工姓名,这样出错的机率会大大降低;C工资表有一定的继承性,通常是这个月做的表就在上个月做的基础上修改,有员工进出时,容易误删人名或者该删的未删,导致出错这种情况也比较常见,利用Excel的基本功能做出来的工资表,手工干预的操作比较多,如果不够仔细的话,这种错误很普遍;解决的办法有两个,一个是对表格做技术升级,使用VBA配合函数做一个简单的工资计算系统,让系统自动判断哪些人应该做在当月的工资表里；另一个是做表的人在技术达不到的情况下仔细仔细再仔细、认真认真再认真,多核对几遍;4、我也在单位做工资,也出现同样的问题,解决办法是：给每个员工一个唯一的编号,这样无论这个员工到哪个部门,都不会影响工资的计算,也不会出现重名的现象Excel制作打印工资表使用Excel做工资统计,是很多中、小企业都会做的,但是如何让工资条打印既快速又漂亮是很多人头痛的问题,下面就提供几种综合解决方案,有需要的朋友可以根据自己的实际情况选择一种方案来操作;一、人少好办事对于一些小企业或者办公室类型的公司来说,公司全体人数也不多,少的几个人,多的也就十几、二十人的样子,即便是每个人的工资条打成一张纸,也不会造成什么大的浪费,所以我们可以简单设置让其每条记录打印在一张纸上,这样是最简单快捷的方法了;这里利用的是一种类似的“页眉”的方式实现每一页打印后都有一个“标题行”,这样保证工资的明细项目在工资条上体现,然后每一个“标题行”下方跟一条记录,最后打印出的效果就是工资条了,具作如下;在Excel做好的工资表中,执行“文件”→“页面设置”命令,在打开的“页面设置”对话框中切换到“工作表”标签,单击“顶端标题行”右侧的按钮,在工作表中选择工资表的头部区域,然后单击回车键,使“页面设置”对话框如图 1所示,然后单击“确定”按钮使设置生效;图 1 选择工资表的头部接下来单击“打印预览”按钮,使工作表呈打印预览状态,然后在“打印预览”对话框中单击“页边距”按钮,使打印预览对话框中显示出调整页边距的虚线,使用鼠标拖动“下边距”虚线至工作表第二行处,即最终效果为页面中显示的只有两行,一行是工资细目,一行是数据如图 2,接下来就打印吧,打印出的每页纸中就是一位员工的工资条了;图 2 拖动下边距小提示：这种打印工资条的方法是最简单,最安全不容易出错,最快速的方法,当然弊端就是太浪费纸张了,如果公司人数稍微多一点,就不太适合了,不过你可以试试下面的方法;二、妙用排序,轻松打印工资条工资条的最终结果就是一行工资明细目,一行员工对应的数据,通常做好的工资表都是只有第一行有数据工资细目,下面全部是数据,如果能从第二行开始,每一行的结尾都添加一行工资细目的数据就好了;这一点可以使用排序轻松实现,具体实现方法如何,就请接着往下看吧;假设当前工资表中有300行数据,显然使用第一种方法是行不通的;首先复制第一行的工资细目数据,从300行之后的任一行粘贴,并使用填充柄拖动复制300行;接下来在A列的前面插入两列,第一列使用填充柄输入序号,从1一直拉到N本例操作时为1000,这个N可根据自己的喜好以及实际需求,基本上只要大于工资表中数据的两倍即可,主要用来打印工资条后下次重新做工资时恢复表格顺序;第二列从第二行开始,依次输入1、3、5、7,然后选中这四个单元格,使用填充柄填充至员工数据结束位置;在下方的工资细目数据区,从第一行依次输入2、4、6、8,然后选中这四个单元格,使用填充柄填充至结束,大致效果如图 3所示;图 3 输入数字,为下一步作准备执行“数据”→“筛选”→“自动筛选”命令,单击B列的下拉按钮,在弹出的下拉菜单中选择“升序排列”,这样工资条就做好了如图 4;图4 最终效果从上图中可以看出,工资条效果已经实现了,这时将A、B两列隐藏,然后打印,我们的工作就算完成了;等下个月需要使用这张工资表格重新计算工资时,只需要将隐藏的A、B两列取消隐藏,使用“自动筛选”,然后按照A列“升序排列”即可还原原来的顺序;三、轻松插入工资细目数据行其实用Excel打印工资条,归根结底就是让Excel制作的工资表格中,一行工资细目数据,一行员工的记录;如果在每一个员工数据的上面插入一行工资细目数据显然我们的要求也就完成了,当然纯手工的一行一行插入显然是个“不可能完成的任务”,这里需要有点小技巧;第一步：在工资细目的右侧两列中,交叉输入任意数字主要是为了后面的“空位”空值,所以数字可任意输,然后选中交叉的四个单元格,双击右下角的“填充柄”,使这种格式一直填充至工资表的结束行如图 5;图5 填充数据第二步：执行“编辑”→“定位”命令,在打开的“定位”对话框中单击“定位条件”按钮,在打开的“定位条件”对话框中,选择“空值”,然后单击“确定”按钮如图 6;图6 定位空行第三步：执行“插入”→“行”命令,这时便会从第三行开始,每一行的前面插入了一个空行;第四步：复制表头的工资细目数据,选中工资表A列的数据区域,执行“编辑”→“定位”命令,在打开的“定位”对话框中单击“定位条件”按钮,在打开的“定位条件”对话框中,选择“空值”,然后单击“确定”按钮,这时会选中刚插入的空行,执行“编辑”→“粘贴”命令,这时工资条就算制作完成了如图 7;图7 最终效果四、小结一提到用Excel打印工资条,很多人就会想到高深的VBA或者是难以理解的函数,经常看着别人给出的解决方案中的VBA代码或者是函数,因为难以理解其意,所以总是担心出错;其实很多时候只要抓住需求,仔细分析,然后再用模拟日常的操作,通过一些附加的操作提高效率,我们的要求也就达到了;因为是简单、易用,并且很容易掌握,也不容易出错,就算偶尔出错,也很容易查出错在哪;这里给出的三种打印工资的条的方法可以说是非常简单、易用的;希望对有需要的有所帮助,即便是用不上的朋友也可以通过上面的事例,在遇到相关应用的时候,能够多一些解决问题的思路;来自:仅需一个公式让Excel按人头打出工资条使用Excel按人头制作工资条,网上有两个解决方案,一个是利用Word的“邮件合并”功能,另一个是利用VBA来实现;用“邮件合并”功能操作虽说不难,但数据过多时也很麻烦；用VBA处理起来倒是很方便,但几十行的程序也够初学者忙活的;经过一番试验,笔者找出了一个比较简单的方法,只需一个公式就可以按人头打印出工资条来;新建一Excel文件,在sheet1中存放工资表的原始数据,假设有N列;第一行是工资项目,从第二行开始是每个人的工资;在sheet2中我们来设置工资条;根据实际情况,工资条由三行构成,一行对应工资项目,一行对应一个人的工资数据,然后是一个空行用来方便切割;这样三行构成一个工资条;工资项目处在行号除以3余数为1的行上；空行处在行号能整除3的行上;以上两行不难设置,关键是工资数据行,牵扯到sheet1与 sheet2中数据的对应,经分析不难看出“sheet1中的数据行=INTsheet2中的数据行+4/3”;这样我们在sheet2的A1单元格中输入公式“=IFMODROW,3=0,"",IFMODROW,3=1,Sheet1A$1,INDEXSheet1$A:$N,INTROW+4/3,COLUMN”;确认后选择A1单元格,把鼠标放在A1单元格的右下角,鼠标变成“+”时,向右拖动鼠标自动填充至N列,这样工资条中的第一行就出来了;选定A1：N1,把鼠标放在N1单元格的右下角,鼠标再次变成“+”时,向下拖动鼠标自动填充到数据的最后一行,工资条就全部制作完成了;该公式运用IF函数,对MOD函数所取的引用行号与3的余数进行判断;如果余数为0,则产生一个空行；如果余数为1,则固定取sheet1中第一行的内容；否则运用INDEX函数和INT函数来取Sheet1对应行上的数;最后来设置一下格式,选定A1：N2设上表格线,空行不设;然后选定A1：N3,拖动N3的填充柄向下自动填充,这样有数据的有表格线,没有数据的没有表格线;最后调整一下页边距,千万别把一个工资条打在两页上;怎么样,还满意吧。