新浪博客

【Excel技能】VLOOKUP模糊查询计算个税缴纳额

2014-05-12 11:47阅读：

http://blog.sina.cn/dpool/blog/u/1660049082

网上流传着这么个说法，说只要你会这么一个函数，你就可以堂而皇之的在公司里冒充高手大半年而不会被发现，除了常用的什么IF、SUM之流，VLOOKUP是被提及最多的一个函数之一，作为一个查询函数，受到如此优待，不是木有道理的，今天和大家聊聊利用VLOOKUP的模糊查询功能计算个税缴纳额。
VLOOKUP的语法是：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
翻译成人话，大概是这么个意思，第一个参数是你要找的值，简称“搜索值”（lookup_value），第二个参数是你要在那个“数据区域”（table_array）里找，第三个参数是，你要在数据区域的第几列（col_index_num）里寻找对应第一个参数的值，示意图如下。
【Excel技能】VLOOKUP模糊查询计算个税缴纳额
最后一个参数有0和1两种，也就是FALSE和TRUE，0是精确匹配，这个比较好理解，就是一模一样才能查找出来。那1近似匹配（也叫模糊匹配）是啥意思呢？
官方的解释是这样滴：


1、如果 range_lookup 为 TRUE 或被省略，则返回精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于 look
up_value 的最大值。


2、如果 range_lookup 为 TRUE 或被省略，则必须按升序排列 table_array 第一列中的值；否则，VLOOKUP 可能无法返回正确的值。


翻译成人话，是这么个意思：
1、如果最后那个参数是1或不写，那么先精确查询，若找不到一模一样的，那就在对应列中找小于“搜索值”的最大值。


2、如果最后那个参数是1或不写，那么“数据区域”里的第一列必须按升序排列，要不然很可能找不到。
利用这个近似查询，可以相对简单的处理一些阶梯性数据，比如计算个人所得税，计算员工绩效奖金等等。
这里用计算个人所得税来举例。下面这张表是个税税率表（为了方便，我们在Excel名称中将该表表命名为“税率表”）。
【Excel技能】VLOOKUP模糊查询计算个税缴纳额
明眼人可能已经发现了，A列是我自己加进去的，而且按照前面提到的近似查询的说明2进行了升序排列。
那么接下来要干嘛呢，大致思路是这样的
1、我要找到某员工应纳税金额减去纳税基数（目前是3500）是属于哪个范围，即属于第几级；
2、然后将找出的对应税率和速算扣除数套入个税公式“个人所得税=（应纳税所得额-3500）*税率-速算扣除数”，从而得出个人所得税。
现在关键在于第一步，如何用VLOOKUP来确定某金额是属于哪个缴税范围呢？举个具体的例子来说明一下。
假设税前工资是6000元，减去3500元，应纳税所得额是2500元（这将是我们的第一个参数，也就是我们的“搜索值”），而在“税率表”中是无法找到2500这个值的，这时如果是模糊查询，会返回那个值呢？还记得前面函数说明1吗？“若找不到一模一样的，那就在对应列中找小于“搜索值”的最大值。”那么在第一列中，小于2500的最大值是多少呢？是的，是1500！即对应的1500-4500元的应纳税所得额。


我们先来写出查询的公式：
对应的税率=VLOOKUP（税前工资-3500,税率表,4,1）
对应的速算扣除数=VLOOKUP（税前工资-3500,税率表,5,1）
然后套入个人所得税公式：
个人所得税=（税前工资-3500）*VLOOKUP（税前工资-3500,税率表,4,1）-VLOOKUP（税前工资-3500,税率表,5,1）


当然，为了照顾那些达不到纳税额的朋友们，你可以用IFERROR，把返回#N/A的错误值改为显示0，如下图所示。（IFERROR函数的用法在之前的博文里有介绍）


【Excel技能】VLOOKUP模糊查询计算个税缴纳额
好了，你也可以回去试试VLOOKUP函数的模糊查询了。
【Excel技能】VLOOKUP模糊查询计算个税缴纳额

我的更多文章

下载客户端阅读体验更佳

APP专享