智能审计中的XLOOKUP函数应用场景探究

施谦（副研究员） 高德花

（云南大学经济学院 云南昆明 650504）

一、引言

随着经济的发展和时代的进步，计算机、互联网与人工智能技术发展迅速，审计信息化与审计智能化成为大势所趋，引起国家的重视。习近平总书记在中央审计委员会第一次会议中强调“要坚持科技强审，加强审计信息化建设”；《“十四五”国家审计工作发展规划》中提出要“加强审计技术方法创新，充分运用现代信息技术开展审计”；《会计改革与发展“十四五”规划纲要》中提出要“切实加快会计审计数字化转型步伐，为会计事业发展提供新引擎、构筑新优势”。在国家各种政策的大力支持下，计算机辅助审计技术获得飞速发展，从测试数据技术、审计软件技术逐渐走上智能审计的道路，智能审计作为计算机审计的演进产物，其审计覆盖数据更广、数据分析更全面，且具有自动化处理、风险洞察、实时监控等能力，在提高审计效率的同时极大地降低了审计成本。然而目前对智能审计的实际应用研究还停留在初步阶段，应用路径和应用方式都较为笼统抽象，缺乏系统性的总结。

EXCEL 中的XLOOKUP 函数作为VLOOKUP 函数的“升级版”，其语法和功能更具高级性、便捷性、灵活性，兼具单条件精确匹配、单条件近似匹配、多条件匹配功能，是智能审计技术的重要工具。本文以XLOOKUP为例，分析其较VLOOKUP 函数的优越之处，深入挖掘其在社会审计、内部审计、国家审计三大领域中的应用场景，主要包括审计抽样、个人所得税计算器制作、账龄分析以及补贴数据稽核。通过案例系统性分析智能审计的具体应用场景和路径，为审计智能化的理论研究和实践发展提供了参考与借鉴。

二、XLOOKUP函数语法与功能分析

（一）XLOOKUP函数语法简介

XLOOKUP 与LOOKUP、VLOOKUP、HLOOKUP 等函数同属于“查找与引用”类别。它能够在某个区域或数组内搜索匹配项，并通过第二个区域或数组返回相应的项，默认的匹配方式为精确匹配。如果不存在匹配项，则可以通过设置参数使XLOOKUP 函数返回最接近的（近似）匹配项，其语法为：

其中，“lookup_value”是指想要在查找区域内搜索的项，若省略该参数，查询结果将显示在查找区域（lookup_array）中空白单元格所对应的项。“lookup_array”是将要在其范围内进行搜索的区域或数组。“return_array”是要返回的区域或数组，即想要搜索到的结果所在的区域或数组。“if_not_found”是搜索不到有效匹配项时显示的搜索结果。如果XLOOKUP 函数搜索不到有效匹配项，在设置该参数的情况下，搜索结果为在该参数中设置的文本；若选择忽略该参数，则搜索结果为错误值“#N/A”。“match_mode”是指定匹配类型。该参数设置为“0”，则为精确匹配，如果未找到有效匹配项，搜索结果显示［if_not_found］中设置的文本或错误值“#N/A”；设置为“-1”，为近似匹配，如果未找到有效匹配项，则匹配到查找区域中与查找值最接近的较小项；设置为“1”，为近似匹配，如果未找到有效匹配项，则匹配到查找区域中与查找值最接近的较大项；设置为“2”，为通配符①可以代表任意字符的符号，常用的有2个：“?”代表任意单个字符；“*”代表任意多个字符匹配，可以对“*”和“?”通配符进行查找。“search_mode”是函数要使用的搜索模式。该参数设置为“1”，则从查找区域中的第一项开始进行搜索；设置为“-1”，从查找区域中的最后一项开始执行反向搜索；设置为“2”，当查找区域按升序或降序排序时，进行对应排序的二进制搜索，如查找区域未排序，函数返回无效结果。

（二）XLOOKUP函数较VLOOKUP函数的优势分析

VLOOKUP 作为功能强大、操作便捷的纵向查找函数，经常被用于财税工作和审计实务中，然而因功能限制，其在审计实务的应用中仍存在诸多不便之处，如无法横向查找、多列查找和反向查找等。2019年，XLOOKUP函数出现，相较于VLOOKUP函数它拥有更强大、更高级、更便捷的查找功能，可以看作VLOOKUP函数的“升级版”，主要体现在以下几个方面。

1.能够进行横向查找。VLOOKUP 函数作为纵向查找函数，无法进行横向查找，而XLOOKUP函数兼具纵向查找与横向查找功能。

2.特定条件下进行多列查找。在使用VLOOKUP 进行纵向查找时，一次性只能查找到一列结果，需要将其与MATCH 函数或CULOMN 函数相嵌套才能够实现多列查找。而XLOOKUP 函数可以使用一个函数提取多个数据点，实现多列查找，条件是查找结果表头与要返回的区域表头序列需保持一致。

3.查找方向不再受限。使用VLOOKUP 函数只能通过左边列的信息查找右边列的信息，而使用XLOOKUP 函数能够实现从右至左的反向查找。

4.屏蔽错误值。当使用VLOOKUP 函数无法查询到有效匹配信息时，单元格会显示错误值“#N/A”，逐个修改错误值效率较低且易产生错误。可以通过设置XLOOKUP函数的第四参数“if_not_found”，在无法查找到有效匹配项时得到预先指定的文本，屏蔽错误值“#N/A”，在更加美观便捷的同时便于函数的进一步嵌套。

5.实现通配符查找。将XLOOKUP 的第五参数设置为“2”，能够进行通配符查找，使用该特性，可以利用XLOOKUP达到通过关键字查找数据的效果。

6.更便捷的多条件查找。想要利用VLOOKUP 函数进行多条件查找，往往需要设置辅助列将不同条件进行绑定，操作较为复杂，在使用XLOOKUP函数进行多条件查找时只需要用“&”将多个条件进行链接，不再需要设置辅助信息。

三、XLOOKUP函数在三大审计领域中的应用场景

（一）单条件精确匹配功能应用于社会审计抽样

审计人员在实施控制测试和细节测试的过程中，经常需要进行审计抽样。使用科学的方法进行审计抽样，能极大地提高审计效率，但同时会带来抽样风险与非抽样风险。利用XLOOKUP 函数的单条件精确匹配，以抽样序号作为唯一条件，从总体中随机选取一定数量的样本，能够实现智能系统抽样。使用一个函数提取多个数据点获取样本的多项信息，提高了抽样效率，相对于人工手动操作抽样降低了错误发生的概率，降低了非抽样风险。

1.确定抽样总体与样本量。为了测试某公司固定资产投入使用的授权审批程序是否得到有效执行，审计人员拟使用系统抽样从该公司2022 年度投入使用固定资产清单中抽取10份固定资产进行控制测试。首先对30份固定资产数据按照1-30进行编号，如表1所示。

表1 样本总体

2.使用系统抽样确认拟抽取的10 个序号，如下页表2所示。在表2“资产类别”下方的目标单元格B64 中插入XLOOKUP 函数，公式为“=XLOOKUP（A64，A3：A32，B3：G32）”，其中想要搜索的值即为抽样序号；目标搜索区域为总体中所有样本序号，要返回的区域为总体中所有样本的所有信息；不设置第四、第五参数与第六参数，默认为进行精确匹配。函数完成后，能够一次性查找到抽样序号所代表的固定资产所有信息，实现多列匹配。

表2 利用XLOOKUP函数获取抽样结果

3.下拉填充柄，即可得到抽取样本的所有信息。

（二）单条件近似匹配功能应用于内部审计数据匹配

在内部审计中，审计人员通常需要将数据批量匹配归纳到对应的区间，如个人所得税计算和账龄、货龄分析统计。此时，利用XLOOKUP函数的近似匹配功能，可以将阶梯式标准转化为分段函数，智能、精准、高效、便捷地实现数据的批量区间匹配。XLOOKUP 函数的第五参数设定为“-1”和“1”时，分别有不同的近似匹配效果。

1.利用近似匹配“-1”，制作个人所得税计算器。内部审计人员在审查单位或部门实际代扣代缴个人所得税额情况时，需要审查核对纳税金额，但逐个计算应纳税额过程繁杂、效率较低，且不能保证结果的准确性。利用XLOOKUP函数的近似匹配和多列查找功能制作个人所得税计算器，能够在已知全年应纳税所得额的情况下，快速、便捷、准确地一次性查找到相应的税率和速算扣除数，并计算应交所得税额，从而大幅提高审计效率。执行步骤是：

第一步，在EXCEL 中打开“个人所得税税率表”，其中包含全年应纳税所得额的阶梯式区间，以及不同区间对应的税率和速算扣除数，如表3 所示。在进行应交税额的计算时，需要识别应纳所得额所在区间并找到对应的税率和速算扣除数。XLOOKUP 函数的近似匹配功能可以在未找到有效匹配项时返回下一个较小的值。因此，将全年应纳税所得额一列的内容由阶梯模式转换为该区间的最小数值，即将表3 转换为表4 中的“个人所得税税率表（转换后）”。

表3 个人所得税税率初始表格

表4 个人所得税计算器

第二步，在个人所得税计算器中“税率”下方的单元格中插入XLOOKUP 函数，公式为“=XLOOKUP（B12，B3：B9，C3：D9，，-1）”。其中查找值为所得额数值；查找区域为个人所得税税率表中的“全年应纳税所得额”一列，要返回的区域为个人所得税税率表中的“税率”和“速算扣除数”两列；匹配条件为“-1”，即近似匹配，若输入的应纳税所得额在两个数据之间，将会返回较小的数值所对应的税率和速算扣除数。完成该函数后，在计算器中输入所得额，即可得到相对应的税率和速算扣除数。

第三步，由于“应交所得税额=全年应纳税所得额*税率-速算扣除数”，在个人所得税计算器中“应交所得税额”下方的空白单元格中插入一个简单计算函数②公式为“=B12*C12-D12”。，即可在个人所得税计算器得到最终应缴纳的个人所得税额。

2.妙用近似匹配“1”，便捷账龄分析。在审查应收账款、其他应收款等科目的准确性时，不可避免地要对账龄进行分析。为了从宏观层面了解账龄分布情况，审计人员通常将账龄划分为几个连续的区间，再将每个账户分类至对应账龄区间，但逐笔人工识别账户所在的区间效率较低且极易发生错误。利用XLOOKUP函数的近似匹配和横向查找功能，可以实现账户账龄的批量自动匹配。执行步骤是：

第一步，将账龄区间划分表由表5 转换为表6 中的格式，因为若将XLOOKUP 函数的匹配模式设置为“1”，在未查找到有效值时会返回下一个较大的项，所以将账龄区间替换为该区间的最大值，其中“3 653 以上”没有最大值，用任意远大于合理账龄天数的数据代替即可。同时，利用DAYS函数③公式为“=DAYS(C3,B3)”。计算出每笔应收账款的账龄天数，其中第一参数为截止日期，第二参数为应收账款入账时间。下拉填充柄即可得到所有账龄天数信息，如表6所示。

表5 账龄区间划分表

第二步，在E3 单元格中插入XLOOKUP 函数，公式为“=XLOOKUP（D3，$B$20：$E$20，$B$21：$E$21，，1）”。其中查找值为账龄天数；查找区域为“账龄区间划分表（转换后）”中的“账龄（天）”一列；要返回的区域为“账龄区间划分表（转换后）”中的“区间”列；匹配条件为“1”，即近似匹配，如果账户账龄天数在两个数值之间，则匹配到较大的数值对应的账龄区间。在此函数中，为便于填充时函数正确溢出，需对查找区域和返回的区域进行绝对引用。完成该函数后，下拉E3单元格填充柄，即可将每一笔应收账款匹配到对应的账龄区间，如表6所示。

（三）多条件精确匹配功能应用于国家审计数据稽核

在国家审计中，政府财政资金使用的合法性、合规性是重点审计的内容，如各类补贴的发放。在实务中审计人员经常要审查被补贴人是否违规领取多项不能够叠加享受的补贴。利用XLOOKUP的多条件查找功能，可以精准、快捷地查找出此类情况。

1.公共就业服务岗位补贴与其他类型的就业补贴不能够叠加享受。从某省人社局获取该省某县2022 年就业补助发放人员清单，主要包含公共就业服务岗位补贴、劳务协作补贴、基层就业补贴和求职创业补贴的发放信息，如表7所示。

表7 利用XLOOKUP函数进行补助发放数据稽核

2.要查找到领取公共就业服务岗位补贴的同时享受其他类型补助的人员，需要将领取公共就业服务岗位补贴人员的身份证号和其他补助类型两个条件进行链接，在领取劳务协作补贴、基层就业补贴和求职创业补贴人员名单中进行查找。在公共就业服务岗位补贴人员信息后面插入三列辅助列，分别为劳务协作补贴、基层就业补贴和求职创业补贴，如上页表7所示。

3.在F3 单元格中插入XLOOKUP 函数，公式为“=XLOOKUP（B3&E3，$B$11：$B$27&$C$11：$C$27，$D$11：$D$27，"未领取"）”。查找值为身份证号和辅助列中的“劳务协作补贴”，两者用“&”符号链接，将两个条件进行绑定；查找区域为发放人员清单中的“身份证号”一列和“领取补贴类型一列”，两者用“&”链接；要返回的区域为补贴金额一列；第四参数设置为“未领取”，即未查到叠加领取补贴时目标单元格显示“未领取”；默认为精确匹配；为便于下拉填充，对查找区域和返回的区域进行绝对引用。下拉填充柄复制函数，能够查找到同时领取了劳务协作补贴的人员。基层创业补贴和求职创业补贴的筛查步骤相同，将公式中的查找值替换为对应补贴类型即可。

四、XLOOKUP函数应用的局限性与应对

XLOOKUP 应用中有诸多优势的同时，也存在一些不足。

（一）多列查找时存在条件限制

利用XLOOKUP函数可以通过一个函数自动提多个数据点，实现多行多列查找，然而在使用时存在“目标查找信息的行排序必须与要返回区域的行排序保持一致”的限制条件，不满足时将会得到错误的匹配信息，导致XLOOKUP函数无法进行正确的区域查找。当审计人员想要得到的信息与函数要返回区域序列不一致时，调整序列或删除、增加多余列会导致操作步骤的冗余和审计效率的下降。对此，可以将CHOOSE 函数与XLOOKUP 相嵌套，实现灵活的多行多列查找匹配。

（二）查找值存在重复项时无法识别多个匹配结果

当查找值在查找区域出现重复时，XLOOKUP 只能按照设定的查找顺序进行单个查找，无法同时获取查找值所对应的多个匹配结果。例如在账龄分析时，无法通过XLOOKUP 函数一次性查找到账龄在2 至5 年这个区间内的多个应收账款账户。此时，可以使用EXCEL自带的筛选功能，或将XLOOKUP 函数与COUNTIF 函数进行搭配，达到最终效果。

（三）多条件查找只适用于一维表格

在一维表格中④一维表是将相同属性的数据放在同一列，一个数据表可以有多个属性；二维表是以一个或多个属性数据横向排列，同一属性数据占据多列的呈现方式。，当存在多个查找条件时，可以使用“&”将若干个构成条件的属性列绑定，利用XLOOKUP进行多条件查找。但在二维表格中，构成条件的属性数据横向排列，无法用“&”进行绑定，XLOOKUP 函数无法准确定位其查找区域。此时可以利用VLOOKUP 函数和MATCH 函数的嵌套组合实现二维表格的多条件查找。

综上所述，XLOOKUP函数相较VLOOKUP函数应用更具便捷性、灵活性和高级性，但函数功能的针对性和普适性之间存在固有矛盾，因此XLOOKUP 函数在应用中也存在一定固有局限。此时可以通过利用其他基础函数的辅助功能与XLOOKUP函数相结合，增强函数应用的灵活性，从而达到实现审计目标、提升审计效率的目的。