• 视力保护色:
  • 字体:[ ]

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

发表时间: 2020-09-04 10:12
倪爱民

在工伤保险审计中,伤残津贴支付明细表中的职工,是否都含在工伤职工明细表中,有无享受伤残津贴的职工不是工伤职工。反过来,符合条件的伤残职工是否都在伤残津贴支付明细表中,有无符合条件的职工未享受伤残津贴。诸如此类的问题都可以用VLOOKUP函数来快速解决。VLOOKUP函数,是一个查询函数,在审计中经常用到其精确匹配和大致匹配功能,对数据进行查询分析,生成审计疑点。

一、VLOOKUP函数的功能

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

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

2.数值表。为需要在其中查找数据的数据表,可以使用对区域或区域名称的引用。

    3.列序数。为待返回的匹配值的列序号,为3时,返回数据表第3列中的数值。

    4.匹配条件。指定在查找时是要求精确匹配,还是大致匹配。精确匹配,参数为0。大致匹配,参数为1

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

二、举例说明VLOOKUP函数的用法

(一)一般用法,即匹配条件为精确匹配,参数为0

VLOOKUP函数的精确匹配,是该函数的常规用法,在审计中经常使用。下面以笔者某年开展的工伤保险基金审计为例介绍其用法。按照政策,工伤保险经办机构每年年初要补发上年度工伤职工伤残津贴、生活护理费等待遇。为了查明该政策落实情况,审计人员取得了20191月份和20201月份的工伤保险伤残待遇支付表,20201月份的支付表中包含了补发上年数。把两张表放在一个表格文件中,以两个表中的身份证号为关键字段,用VLOOKUP函数查询20191月份的所有工伤职工在20201月份是否补发工伤待遇。

    1.20191月份和20201月份的工伤保险伤残待遇支付表,见图一图二。

image.png

图一

image.png

图二

2.20191月表中,增加一列“20201月份补发待遇人员”(P列),以20191月份的身份证号为查找值,查询区域为20201月份的D列“身份证号”。P5就是查询D520201月是否补发待遇。其计算公式如下:

P5=VLOOKUP(D5,'20201月份'!$D$5:$D$26,1,0)

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

image.png

图三

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

    (二)特殊用法,即匹配条件为大致匹配,参数为1

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

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

image.png

图四

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

image.png

图五

3.计算应纳税额。首先确定每个人适用的税率和速算扣除数,然后计算应纳税额。

第一步确定每个人适用的税率(I列)。A适用税率I3,应以H3A的全年应纳税所得额45000)作为查找值,搜索税率表的B3:E9区域,以查找是否有值与H3大致匹配,如果匹配,则显示这个值所在行的第3列(D列)作为I3的值。I3的计算公式如下:

I3=VLOOKUP(H3,$B$3:$E$9,3,1)(见图六)

image.png

图六

复制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函数还可以与数据的有效性搭配使用,生成小型的查询器等,这里就不一一赘述了。


扫描分享