智能審計中的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函數相結合，增強函數應用的靈活性，從而達到實現審計目標、提升審計效率的目的。