智能審計中的XLOOKUP函數(shù)應(yīng)用場景探究

施謙（副研究員） 高德花

（云南大學經(jīng)濟學院 云南昆明 650504）

一、引言

隨著經(jīng)濟的發(fā)展和時代的進步，計算機、互聯(lián)網(wǎng)與人工智能技術(shù)發(fā)展迅速，審計信息化與審計智能化成為大勢所趨，引起國家的重視。習近平總書記在中央審計委員會第一次會議中強調(diào)“要堅持科技強審，加強審計信息化建設(shè)”；《“十四五”國家審計工作發(fā)展規(guī)劃》中提出要“加強審計技術(shù)方法創(chuàng)新，充分運用現(xiàn)代信息技術(shù)開展審計”；《會計改革與發(fā)展“十四五”規(guī)劃綱要》中提出要“切實加快會計審計數(shù)字化轉(zhuǎn)型步伐，為會計事業(yè)發(fā)展提供新引擎、構(gòu)筑新優(yōu)勢”。在國家各種政策的大力支持下，計算機輔助審計技術(shù)獲得飛速發(fā)展，從測試數(shù)據(jù)技術(shù)、審計軟件技術(shù)逐漸走上智能審計的道路，智能審計作為計算機審計的演進產(chǎn)物，其審計覆蓋數(shù)據(jù)更廣、數(shù)據(jù)分析更全面，且具有自動化處理、風險洞察、實時監(jiān)控等能力，在提高審計效率的同時極大地降低了審計成本。然而目前對智能審計的實際應(yīng)用研究還停留在初步階段，應(yīng)用路徑和應(yīng)用方式都較為籠統(tǒng)抽象，缺乏系統(tǒng)性的總結(jié)。

EXCEL 中的XLOOKUP 函數(shù)作為VLOOKUP 函數(shù)的“升級版”，其語法和功能更具高級性、便捷性、靈活性，兼具單條件精確匹配、單條件近似匹配、多條件匹配功能，是智能審計技術(shù)的重要工具。本文以XLOOKUP為例，分析其較VLOOKUP 函數(shù)的優(yōu)越之處，深入挖掘其在社會審計、內(nèi)部審計、國家審計三大領(lǐng)域中的應(yīng)用場景，主要包括審計抽樣、個人所得稅計算器制作、賬齡分析以及補貼數(shù)據(jù)稽核。通過案例系統(tǒng)性分析智能審計的具體應(yīng)用場景和路徑，為審計智能化的理論研究和實踐發(fā)展提供了參考與借鑒。

二、XLOOKUP函數(shù)語法與功能分析

（一）XLOOKUP函數(shù)語法簡介

XLOOKUP 與LOOKUP、VLOOKUP、HLOOKUP 等函數(shù)同屬于“查找與引用”類別。它能夠在某個區(qū)域或數(shù)組內(nèi)搜索匹配項，并通過第二個區(qū)域或數(shù)組返回相應(yīng)的項，默認的匹配方式為精確匹配。如果不存在匹配項，則可以通過設(shè)置參數(shù)使XLOOKUP 函數(shù)返回最接近的（近似）匹配項，其語法為：

其中，“l(fā)ookup_value”是指想要在查找區(qū)域內(nèi)搜索的項，若省略該參數(shù)，查詢結(jié)果將顯示在查找區(qū)域（lookup_array）中空白單元格所對應(yīng)的項?！發(fā)ookup_array”是將要在其范圍內(nèi)進行搜索的區(qū)域或數(shù)組?！皉eturn_array”是要返回的區(qū)域或數(shù)組，即想要搜索到的結(jié)果所在的區(qū)域或數(shù)組。“if_not_found”是搜索不到有效匹配項時顯示的搜索結(jié)果。如果XLOOKUP 函數(shù)搜索不到有效匹配項，在設(shè)置該參數(shù)的情況下，搜索結(jié)果為在該參數(shù)中設(shè)置的文本；若選擇忽略該參數(shù)，則搜索結(jié)果為錯誤值“#N/A”。“match_mode”是指定匹配類型。該參數(shù)設(shè)置為“0”，則為精確匹配，如果未找到有效匹配項，搜索結(jié)果顯示［if_not_found］中設(shè)置的文本或錯誤值“#N/A”；設(shè)置為“-1”，為近似匹配，如果未找到有效匹配項，則匹配到查找區(qū)域中與查找值最接近的較小項；設(shè)置為“1”，為近似匹配，如果未找到有效匹配項，則匹配到查找區(qū)域中與查找值最接近的較大項；設(shè)置為“2”，為通配符①可以代表任意字符的符號，常用的有2個：“?”代表任意單個字符；“*”代表任意多個字符匹配，可以對“*”和“?”通配符進行查找?！皊earch_mode”是函數(shù)要使用的搜索模式。該參數(shù)設(shè)置為“1”，則從查找區(qū)域中的第一項開始進行搜索；設(shè)置為“-1”，從查找區(qū)域中的最后一項開始執(zhí)行反向搜索；設(shè)置為“2”，當查找區(qū)域按升序或降序排序時，進行對應(yīng)排序的二進制搜索，如查找區(qū)域未排序，函數(shù)返回無效結(jié)果。

（二）XLOOKUP函數(shù)較VLOOKUP函數(shù)的優(yōu)勢分析

VLOOKUP 作為功能強大、操作便捷的縱向查找函數(shù)，經(jīng)常被用于財稅工作和審計實務(wù)中，然而因功能限制，其在審計實務(wù)的應(yīng)用中仍存在諸多不便之處，如無法橫向查找、多列查找和反向查找等。2019年，XLOOKUP函數(shù)出現(xiàn)，相較于VLOOKUP函數(shù)它擁有更強大、更高級、更便捷的查找功能，可以看作VLOOKUP函數(shù)的“升級版”，主要體現(xiàn)在以下幾個方面。

1.能夠進行橫向查找。VLOOKUP 函數(shù)作為縱向查找函數(shù)，無法進行橫向查找，而XLOOKUP函數(shù)兼具縱向查找與橫向查找功能。

2.特定條件下進行多列查找。在使用VLOOKUP 進行縱向查找時，一次性只能查找到一列結(jié)果，需要將其與MATCH 函數(shù)或CULOMN 函數(shù)相嵌套才能夠?qū)崿F(xiàn)多列查找。而XLOOKUP 函數(shù)可以使用一個函數(shù)提取多個數(shù)據(jù)點，實現(xiàn)多列查找，條件是查找結(jié)果表頭與要返回的區(qū)域表頭序列需保持一致。

3.查找方向不再受限。使用VLOOKUP 函數(shù)只能通過左邊列的信息查找右邊列的信息，而使用XLOOKUP 函數(shù)能夠?qū)崿F(xiàn)從右至左的反向查找。

4.屏蔽錯誤值。當使用VLOOKUP 函數(shù)無法查詢到有效匹配信息時，單元格會顯示錯誤值“#N/A”，逐個修改錯誤值效率較低且易產(chǎn)生錯誤?？梢酝ㄟ^設(shè)置XLOOKUP函數(shù)的第四參數(shù)“if_not_found”，在無法查找到有效匹配項時得到預先指定的文本，屏蔽錯誤值“#N/A”，在更加美觀便捷的同時便于函數(shù)的進一步嵌套。

5.實現(xiàn)通配符查找。將XLOOKUP 的第五參數(shù)設(shè)置為“2”，能夠進行通配符查找，使用該特性，可以利用XLOOKUP達到通過關(guān)鍵字查找數(shù)據(jù)的效果。

6.更便捷的多條件查找。想要利用VLOOKUP 函數(shù)進行多條件查找，往往需要設(shè)置輔助列將不同條件進行綁定，操作較為復雜，在使用XLOOKUP函數(shù)進行多條件查找時只需要用“&”將多個條件進行鏈接，不再需要設(shè)置輔助信息。

三、XLOOKUP函數(shù)在三大審計領(lǐng)域中的應(yīng)用場景

（一）單條件精確匹配功能應(yīng)用于社會審計抽樣

審計人員在實施控制測試和細節(jié)測試的過程中，經(jīng)常需要進行審計抽樣。使用科學的方法進行審計抽樣，能極大地提高審計效率，但同時會帶來抽樣風險與非抽樣風險。利用XLOOKUP 函數(shù)的單條件精確匹配，以抽樣序號作為唯一條件，從總體中隨機選取一定數(shù)量的樣本，能夠?qū)崿F(xiàn)智能系統(tǒng)抽樣。使用一個函數(shù)提取多個數(shù)據(jù)點獲取樣本的多項信息，提高了抽樣效率，相對于人工手動操作抽樣降低了錯誤發(fā)生的概率，降低了非抽樣風險。

1.確定抽樣總體與樣本量。為了測試某公司固定資產(chǎn)投入使用的授權(quán)審批程序是否得到有效執(zhí)行，審計人員擬使用系統(tǒng)抽樣從該公司2022 年度投入使用固定資產(chǎn)清單中抽取10份固定資產(chǎn)進行控制測試。首先對30份固定資產(chǎn)數(shù)據(jù)按照1-30進行編號，如表1所示。

表1 樣本總體

2.使用系統(tǒng)抽樣確認擬抽取的10 個序號，如下頁表2所示。在表2“資產(chǎn)類別”下方的目標單元格B64 中插入XLOOKUP 函數(shù)，公式為“=XLOOKUP（A64，A3：A32，B3：G32）”，其中想要搜索的值即為抽樣序號；目標搜索區(qū)域為總體中所有樣本序號，要返回的區(qū)域為總體中所有樣本的所有信息；不設(shè)置第四、第五參數(shù)與第六參數(shù)，默認為進行精確匹配。函數(shù)完成后，能夠一次性查找到抽樣序號所代表的固定資產(chǎn)所有信息，實現(xiàn)多列匹配。

表2 利用XLOOKUP函數(shù)獲取抽樣結(jié)果

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

（二）單條件近似匹配功能應(yīng)用于內(nèi)部審計數(shù)據(jù)匹配

在內(nèi)部審計中，審計人員通常需要將數(shù)據(jù)批量匹配歸納到對應(yīng)的區(qū)間，如個人所得稅計算和賬齡、貨齡分析統(tǒng)計。此時，利用XLOOKUP函數(shù)的近似匹配功能，可以將階梯式標準轉(zhuǎn)化為分段函數(shù)，智能、精準、高效、便捷地實現(xiàn)數(shù)據(jù)的批量區(qū)間匹配。XLOOKUP 函數(shù)的第五參數(shù)設(shè)定為“-1”和“1”時，分別有不同的近似匹配效果。

1.利用近似匹配“-1”，制作個人所得稅計算器。內(nèi)部審計人員在審查單位或部門實際代扣代繳個人所得稅額情況時，需要審查核對納稅金額，但逐個計算應(yīng)納稅額過程繁雜、效率較低，且不能保證結(jié)果的準確性。利用XLOOKUP函數(shù)的近似匹配和多列查找功能制作個人所得稅計算器，能夠在已知全年應(yīng)納稅所得額的情況下，快速、便捷、準確地一次性查找到相應(yīng)的稅率和速算扣除數(shù)，并計算應(yīng)交所得稅額，從而大幅提高審計效率。執(zhí)行步驟是：

第一步，在EXCEL 中打開“個人所得稅稅率表”，其中包含全年應(yīng)納稅所得額的階梯式區(qū)間，以及不同區(qū)間對應(yīng)的稅率和速算扣除數(shù)，如表3 所示。在進行應(yīng)交稅額的計算時，需要識別應(yīng)納所得額所在區(qū)間并找到對應(yīng)的稅率和速算扣除數(shù)。XLOOKUP 函數(shù)的近似匹配功能可以在未找到有效匹配項時返回下一個較小的值。因此，將全年應(yīng)納稅所得額一列的內(nèi)容由階梯模式轉(zhuǎn)換為該區(qū)間的最小數(shù)值，即將表3 轉(zhuǎn)換為表4 中的“個人所得稅稅率表（轉(zhuǎn)換后）”。

表3 個人所得稅稅率初始表格

表4 個人所得稅計算器

第二步，在個人所得稅計算器中“稅率”下方的單元格中插入XLOOKUP 函數(shù)，公式為“=XLOOKUP（B12，B3：B9，C3：D9，，-1）”。其中查找值為所得額數(shù)值；查找區(qū)域為個人所得稅稅率表中的“全年應(yīng)納稅所得額”一列，要返回的區(qū)域為個人所得稅稅率表中的“稅率”和“速算扣除數(shù)”兩列；匹配條件為“-1”，即近似匹配，若輸入的應(yīng)納稅所得額在兩個數(shù)據(jù)之間，將會返回較小的數(shù)值所對應(yīng)的稅率和速算扣除數(shù)。完成該函數(shù)后，在計算器中輸入所得額，即可得到相對應(yīng)的稅率和速算扣除數(shù)。

第三步，由于“應(yīng)交所得稅額=全年應(yīng)納稅所得額*稅率-速算扣除數(shù)”，在個人所得稅計算器中“應(yīng)交所得稅額”下方的空白單元格中插入一個簡單計算函數(shù)②公式為“=B12*C12-D12”。，即可在個人所得稅計算器得到最終應(yīng)繳納的個人所得稅額。

2.妙用近似匹配“1”，便捷賬齡分析。在審查應(yīng)收賬款、其他應(yīng)收款等科目的準確性時，不可避免地要對賬齡進行分析。為了從宏觀層面了解賬齡分布情況，審計人員通常將賬齡劃分為幾個連續(xù)的區(qū)間，再將每個賬戶分類至對應(yīng)賬齡區(qū)間，但逐筆人工識別賬戶所在的區(qū)間效率較低且極易發(fā)生錯誤。利用XLOOKUP函數(shù)的近似匹配和橫向查找功能，可以實現(xiàn)賬戶賬齡的批量自動匹配。執(zhí)行步驟是：

第一步，將賬齡區(qū)間劃分表由表5 轉(zhuǎn)換為表6 中的格式，因為若將XLOOKUP 函數(shù)的匹配模式設(shè)置為“1”，在未查找到有效值時會返回下一個較大的項，所以將賬齡區(qū)間替換為該區(qū)間的最大值，其中“3 653 以上”沒有最大值，用任意遠大于合理賬齡天數(shù)的數(shù)據(jù)代替即可。同時，利用DAYS函數(shù)③公式為“=DAYS(C3,B3)”。計算出每筆應(yīng)收賬款的賬齡天數(shù)，其中第一參數(shù)為截止日期，第二參數(shù)為應(yīng)收賬款入賬時間。下拉填充柄即可得到所有賬齡天數(shù)信息，如表6所示。

表5 賬齡區(qū)間劃分表

第二步，在E3 單元格中插入XLOOKUP 函數(shù)，公式為“=XLOOKUP（D3，$B$20：$E$20，$B$21：$E$21，，1）”。其中查找值為賬齡天數(shù)；查找區(qū)域為“賬齡區(qū)間劃分表（轉(zhuǎn)換后）”中的“賬齡（天）”一列；要返回的區(qū)域為“賬齡區(qū)間劃分表（轉(zhuǎn)換后）”中的“區(qū)間”列；匹配條件為“1”，即近似匹配，如果賬戶賬齡天數(shù)在兩個數(shù)值之間，則匹配到較大的數(shù)值對應(yīng)的賬齡區(qū)間。在此函數(shù)中，為便于填充時函數(shù)正確溢出，需對查找區(qū)域和返回的區(qū)域進行絕對引用。完成該函數(shù)后，下拉E3單元格填充柄，即可將每一筆應(yīng)收賬款匹配到對應(yīng)的賬齡區(qū)間，如表6所示。

（三）多條件精確匹配功能應(yīng)用于國家審計數(shù)據(jù)稽核

在國家審計中，政府財政資金使用的合法性、合規(guī)性是重點審計的內(nèi)容，如各類補貼的發(fā)放。在實務(wù)中審計人員經(jīng)常要審查被補貼人是否違規(guī)領(lǐng)取多項不能夠疊加享受的補貼。利用XLOOKUP的多條件查找功能，可以精準、快捷地查找出此類情況。

1.公共就業(yè)服務(wù)崗位補貼與其他類型的就業(yè)補貼不能夠疊加享受。從某省人社局獲取該省某縣2022 年就業(yè)補助發(fā)放人員清單，主要包含公共就業(yè)服務(wù)崗位補貼、勞務(wù)協(xié)作補貼、基層就業(yè)補貼和求職創(chuàng)業(yè)補貼的發(fā)放信息，如表7所示。

表7 利用XLOOKUP函數(shù)進行補助發(fā)放數(shù)據(jù)稽核

2.要查找到領(lǐng)取公共就業(yè)服務(wù)崗位補貼的同時享受其他類型補助的人員，需要將領(lǐng)取公共就業(yè)服務(wù)崗位補貼人員的身份證號和其他補助類型兩個條件進行鏈接，在領(lǐng)取勞務(wù)協(xié)作補貼、基層就業(yè)補貼和求職創(chuàng)業(yè)補貼人員名單中進行查找。在公共就業(yè)服務(wù)崗位補貼人員信息后面插入三列輔助列，分別為勞務(wù)協(xié)作補貼、基層就業(yè)補貼和求職創(chuàng)業(yè)補貼，如上頁表7所示。

3.在F3 單元格中插入XLOOKUP 函數(shù)，公式為“=XLOOKUP（B3&E3，$B$11：$B$27&$C$11：$C$27，$D$11：$D$27，"未領(lǐng)取"）”。查找值為身份證號和輔助列中的“勞務(wù)協(xié)作補貼”，兩者用“&”符號鏈接，將兩個條件進行綁定；查找區(qū)域為發(fā)放人員清單中的“身份證號”一列和“領(lǐng)取補貼類型一列”，兩者用“&”鏈接；要返回的區(qū)域為補貼金額一列；第四參數(shù)設(shè)置為“未領(lǐng)取”，即未查到疊加領(lǐng)取補貼時目標單元格顯示“未領(lǐng)取”；默認為精確匹配；為便于下拉填充，對查找區(qū)域和返回的區(qū)域進行絕對引用。下拉填充柄復制函數(shù)，能夠查找到同時領(lǐng)取了勞務(wù)協(xié)作補貼的人員?；鶎觿?chuàng)業(yè)補貼和求職創(chuàng)業(yè)補貼的篩查步驟相同，將公式中的查找值替換為對應(yīng)補貼類型即可。

四、XLOOKUP函數(shù)應(yīng)用的局限性與應(yīng)對

XLOOKUP 應(yīng)用中有諸多優(yōu)勢的同時，也存在一些不足。

（一）多列查找時存在條件限制

利用XLOOKUP函數(shù)可以通過一個函數(shù)自動提多個數(shù)據(jù)點，實現(xiàn)多行多列查找，然而在使用時存在“目標查找信息的行排序必須與要返回區(qū)域的行排序保持一致”的限制條件，不滿足時將會得到錯誤的匹配信息，導致XLOOKUP函數(shù)無法進行正確的區(qū)域查找。當審計人員想要得到的信息與函數(shù)要返回區(qū)域序列不一致時，調(diào)整序列或刪除、增加多余列會導致操作步驟的冗余和審計效率的下降。對此，可以將CHOOSE 函數(shù)與XLOOKUP 相嵌套，實現(xiàn)靈活的多行多列查找匹配。

（二）查找值存在重復項時無法識別多個匹配結(jié)果

當查找值在查找區(qū)域出現(xiàn)重復時，XLOOKUP 只能按照設(shè)定的查找順序進行單個查找，無法同時獲取查找值所對應(yīng)的多個匹配結(jié)果。例如在賬齡分析時，無法通過XLOOKUP 函數(shù)一次性查找到賬齡在2 至5 年這個區(qū)間內(nèi)的多個應(yīng)收賬款賬戶。此時，可以使用EXCEL自帶的篩選功能，或?qū)LOOKUP 函數(shù)與COUNTIF 函數(shù)進行搭配，達到最終效果。

（三）多條件查找只適用于一維表格

在一維表格中④一維表是將相同屬性的數(shù)據(jù)放在同一列，一個數(shù)據(jù)表可以有多個屬性；二維表是以一個或多個屬性數(shù)據(jù)橫向排列，同一屬性數(shù)據(jù)占據(jù)多列的呈現(xiàn)方式。，當存在多個查找條件時，可以使用“&”將若干個構(gòu)成條件的屬性列綁定，利用XLOOKUP進行多條件查找。但在二維表格中，構(gòu)成條件的屬性數(shù)據(jù)橫向排列，無法用“&”進行綁定，XLOOKUP 函數(shù)無法準確定位其查找區(qū)域。此時可以利用VLOOKUP 函數(shù)和MATCH 函數(shù)的嵌套組合實現(xiàn)二維表格的多條件查找。

綜上所述，XLOOKUP函數(shù)相較VLOOKUP函數(shù)應(yīng)用更具便捷性、靈活性和高級性，但函數(shù)功能的針對性和普適性之間存在固有矛盾，因此XLOOKUP 函數(shù)在應(yīng)用中也存在一定固有局限。此時可以通過利用其他基礎(chǔ)函數(shù)的輔助功能與XLOOKUP函數(shù)相結(jié)合，增強函數(shù)應(yīng)用的靈活性，從而達到實現(xiàn)審計目標、提升審計效率的目的。