VLOOKUP函数在审计工作中的应用

VLOOKUP函数，是EXCEL软件当中的一个常用查询函数，在审计工作中经常用其进行数值匹配，对数据进行查询分析，查找审计疑点。笔者根据自身参与工伤保险审计过程中借助VLOOKUP函数快速匹配，定位疑点的经验，在此与大家分享这一函数的应用方法，希望能帮助大家提升审计效率。

一、VLOOKUP函数功能简介

VLOOKUP函数，是在表格或数值数组的首列，查找指定数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP函数主要用于数据的查询，包括四个参数，分别为：查找值、数值表、列序数、匹配条件。

（一）查找值。为需要在表格或数组第一列中查找的数值。可以为数值、引用或文本字符串。

（二）数值表。为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。

（三）列序数。为待返回的匹配值的列序号，为3时，返回数据表第3列中的数值。

（四）匹配条件。指定在查找时是要求精确匹配，还是模糊匹配。精确匹配，参数为0；模糊匹配，参数为1。

例如：函数VLOOKUP（F3，A2：B39，3，0），就是使用单元格F3（查找值）中的值作为搜索词，搜索单元格A2到B39（数值表）区域，以查找是否有值与F3中的值精确匹配（匹配条件），如果找到了匹配的值，则显示该值对应行的第3列（列序数）所对应的值。0，表示匹配条件为精确匹配。

二、VLOOKUP函数在审计中的应用

（一）一般用法，即匹配条件为精确匹配。

VLOOKUP函数的精确匹配，是该函数的常规用法，在审计中经常使用。下面以笔者某年开展的工伤保险基金审计为例介绍其用法。按照政策，工伤保险经办机构每年年初要补发上年度工伤职工伤残津贴、生活护理费等待遇。为了查明该政策落实情况，审计人员取得了2019年1月份和2020年1月份的工伤保险伤残待遇支付表，2020年1月份的支付表中包含了补发上年数。

第一步：把两张表放在一个表格文件中，以两个表中的身份证号为关键字段，用VLOOKUP函数查询2019年1月份的所有工伤职工在2020年1月份是否补发工伤待遇。见图一图二。

图一

图二

第二步：在2019年1月表中，增加一列“2020年1月份补发待遇人员”（P列），以2019年1月份的身份证号为查找值，查询区域为2020年1月份的D列“身份证号”。P5就是查询D5在2020年1月是否补发待遇。其计算公式如下：

P5＝VLOOKUP（D5，＇2020年1月份＇！＄D＄5：＄D＄26，1，0）

选中单元格P5，可以看到表格上方的完整公式，鼠标放到单元格右下角，出现实心十字符号时拖动鼠标，复制公式到下边的单元格，得到P6－P27。见图三。

图三

从上图可以看出，P列有两处报错，表明2019年有两个工伤职工在2020年没有补发待遇。进一步查询业务档案核实，这两个职工于2019年10月份去世，2020年调整待遇时，工伤保险经办机构漏发了其前9个月份的生活护理费待遇。

（二）特殊用法，即匹配条件为模糊匹配，参数为1。

VLOOKUP函数的模糊匹配功能，是指当匹配条件不是一个精确的值，而是一个区域时的数据查询匹配功能。这个功能非常有用并且简单，在审计工作中推广使用，可以大大提高工作效率。下面以某单位是否按税法规定及时足额代扣代缴个人所得税为例，介绍VLOOKUP函数的模糊匹配功能。

1.某单位职工的工资所得，属于综合所得。按照规定，适用百分之三至百分之四十五的超额累进税率（见图四）。图中，全年应纳税所得额是指依照规定，居民个人取得综合所得以每一纳税年度收入额减除费用六万元以及专项扣除、专项附加扣除和依法确定的其他扣除后的余额。

图四

 2.取得某单位1－12月份的工资表，经过处理后，得到所有职工的全年应纳税所得额，即H列（见图五）。

图五

3.计算应纳税额。第一步首先确定每个人适用的税率和速算扣除数，然后计算应纳税额。确定每个人适用的税率（I列）。A适用税率I3，应以H3（A的全年应纳税所得额45000）作为查找值，搜索税率表的B3：E9区域，以查找是否有值与H3大致匹配，如果匹配，则显示这个值所在行的第3列（D列）作为I3的值。I3的计算公式如下：

I3＝VLOOKUP（H3，＄B＄3：＄E＄9，3，1）（见图六）

图六

复制I3的公式，得到I4－I10的值。

计算I3，还可以用其他函数，例如我们经常使用的IF条件函数。如果用IF函数，I3的计算公式如下：

I3＝IF（H3＜36000，＄D＄3，IF（H3＜144000，＄D＄4，IF（H3＜300000，＄D＄5，IF（H3＜420000，＄D＄6，IF（H3＜660000，＄D＄7，IF（H3＜960000，＄D＄8，＄D＄9））））））。

    很显然，相对IF函数，VLOOKUP函数更加简单明了，只要找准查找值、查询区域就行了。

第二步计算每个人适用的速算扣除数（J列）。同理，只需要把序列数3改成4就可以了。J3的计算公式如下：

J3＝VLOOKUP（H3，＄B＄3：＄E＄9，4，1）

第三步计算应纳税额。应纳税额＝全年应纳税所得额＊适用税率－适用的速算扣除数，即K3＝H3＊I3－J3。再将应纳税额与实际缴纳数对比，核实单位是否按税法规定履行及时足额代扣代缴个人所得税的义务。

（三）使用VLOOKUP函数应注意以下几点：

一是查找值H3应在查询区域的首列，否则会报错。

二是复制公式前，必须将查询区域用快捷键F4锁定，避免出错。例如：要将B3：E9区域锁定，先选定区域，再按F4，这个区域就被锁定了（即＄B＄3：＄E＄9）。

三是输入的公式，所有的标点符号都要在英文半角输入法状态下输入。

综上所述，在审计工作中巧用VLOOKUP函数查找值和查询区域，精确匹配和模糊匹配这两个功能，能够大大提升工作效率。除此外，VLOOKUP函数还可以与数据的有效性搭配使用，生成小型的查询器等，这里就不一一赘述了。

注：文章内容属作者个人观点，不代表本网站立场。