VLOOKUP函數在會計應用中的常見#N/A錯誤分析及對策

摘要：VLOOKUP函數在會計實務中被廣泛應用。本文通過分析VLOOKUP函數在會計應用中的常見#N/A錯誤，可以讓財務人員在對VLOOKUP函數有進一步的了解，并能在以后的會計實務中，采用本文提供的處理辦法，輕松應對類似的錯誤。

關鍵詞：EXCEL 單元格

EXCEL作為一個優秀的數據處理軟件，經常被財務人員用在會計實務中，其中VLOOKUP函數以其強大的功能被廣泛應用。然而在實際應用時，稍有不慎，就會產生錯誤，返回一些奇怪的錯誤代碼，其中最常見的是#N/A錯誤。本文通過實例就一些常見的#N/A錯誤作一個探討，并提出相應的解決方案。

實例：某公司2007年12月1日從銀行提取現金300元。在圖一中，左邊是簡化的總賬科目表，右邊是簡化的會計憑證表。要求在填制會計憑證表總賬科目一欄時，根據科目代號，從總賬科目表中找出相應的總賬科目。正確的做法是，在J3單元格中輸入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”，公式計算結果是“庫存現金”。在J4單元格中輸入公式“=VLOOKUP($I$4，$A$3:$B$4，2，0)”，公式計算結果是“銀行存款”。在實際工作中，計算J3和J4單元格的值時，經常會產生一些#N/A錯誤。

1 精確匹配查找時出現#N/A錯誤

1.1 查詢表相關單元格中的字符或者查詢字符存在空格或者其它字符在圖二中，在J3單元格中輸入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”， 在J4單元格中輸入公式“=VLOOKUP($I$4，$A$3:$B$4，2，0)”，結果出現了#N/A錯誤。

我們用LEN函數和CODE函數來測試A4和I3。LEN(A4)=5，CODE(RIGHT(A4))=32；LEN(I3)=5，CODE(RIGHT(I3))=32。通過測試，我們知道，J3的#N/A錯誤是因為I3單元格包含5個字符，最后一個不可見字符的ASCII碼是32，也就是空格，導致A3和I3不匹配。J4的#N/A錯誤是因為A4單元格包含5個字符，最后一個不可見字符的ASCII碼是32，也就是空格，導致A4和I4不匹配。

對于J3的#N/A錯誤，有兩種辦法處理，一是刪除或者替換I3單元格尾部空格，二是使用TRIM函數清除空格，相應的公式為“=VLOOKUP(TRIM($I$3)，$A$3:$B$4，2，0)”。

對于J4的#N/A錯誤，是函數的第二個參數中包含空格，同樣也有兩種辦法處理，一是刪除或者替換A4單元格尾部空格，二是使用TRIM函數清除空格，在使用第二種方法時，第二個參數已不再是直接引用單元格區域，而是引用對$A$3:$B$4運算TRIM后的內存數組，因此公式就應該應用數組公式，需要按CTRL+SHIFT+ENTER（請注意：此處不用用戶輸入“{}”）。相應的公式為“{=VLOOKUP($I$4，TRIM($A$3:$B$4)，2，0)}”。

如果我們測試CODE(RIGHT(I3))≠32，也就是說I3單元格的最后一個字符是非空格的不可見字符（由網頁復制、其他程序轉出的表格，經常會出現這類字符），可以刪除該字符或者先復制該字符，然后替換該字符為空白。

1.2 查詢表相關單元格中的字符與查詢字符的格式不一致在圖三中，在J3單元格中輸入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”， 在J4單元格中輸入公式“=VLOOKUP($I$4，$A$3:$B$4，2，0)”，結果出現了#N/A錯誤。

J3和J4單元格的#N/A錯誤都是由于查詢表相關單元格中的字符與查詢字符的格式不一致造成的。A3是文本格式，I3是常規格式，J3是按數值查文本。A4是常規格式，I4是文本格式，J4是按文本查數值。

對于此類錯誤，有兩種處理方法。①可以修改查詢字符或者查詢表相關單元格的格式，使之統一修改單元格的格式很簡單，但是要注意以下幾點：

a僅僅將單元格格式更改還不行。例如對于包含數值的常規單元格將格式改為文本后，單元格中的值仍是數值形式，需要激活（雙擊）才會真正轉為文本。

b需要更改的單元格很多時，可以采用分列或選擇性粘貼的方法。采用分列的方法是，點擊菜單欄上的“數據”，選擇“分列”，然后根據提示進行操作。由文本格式轉為數值格式也可以采用選擇性粘貼的方法，是復制一格式為常規的空白單元格或者在其中輸入“0”，然后選定需轉換的數據區，進行選擇性粘貼，在“運算”區域下選擇“加”。例如要把A3單元格轉為數值格式，可以在格式為常規的單元格A5中輸入數字“0”，接著將其復制，然后選定A3進行選擇性粘貼，在“運算”區域下選擇“加”即可。②用公式處理在J3單元格中輸入公式“=VLOOKUP($I$3\"\"，$A$3:$B$4，2，0)”，這個公式將數值1001用連接空字符串\"\"，強制轉為文本。在J4單元格中輸入公式“=VLOOKUP($I$4*1，$A$3:$B$4，2，0)”，這個公式將文本1002用乘1進行運算，強制轉為數值。

1.3 查詢字符不在查詢表的第一列我們在圖一所示的表中最前面插入一列，如圖四所示。在K3單元格中輸入公式“=VLOOKUP($J$3，$A$3:$C$4，2，0)”，在K4單元格中輸入公式“=VLOOKUP($J$4，$A$3:$C$4，2，0)”，結果出現了#N/A錯誤。K3和K4單元格出現#N/A錯誤都是由于查詢字符不在查詢表的第一列，而是在第二列。VLOOKUP一般要求查詢字符在查詢表的第一列，如果區域選擇錯誤不在第一列，則查找錯誤，解決方法為重新選擇數據區域。在這里，K3和K4單元格公式中的$A$3:$C$4更改為$B$3:$C$4即可。

1.4 查詢字符在查詢表中不存在在圖五中，在J3單元格中輸入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”，結果出現了#N/A錯誤。錯誤的原因是因為查詢字符“1000”在查詢表中不存在。

2 近似匹配查找時出現#N/A錯誤近似匹配查找時出現#N/A錯誤，除了前述的格式等原因，還有以下兩種情況。

2.1 查詢表的第一列沒有升序排列

我們在圖一所示的表中，把B3:C3與B4:C4的位置互換一下，如圖六所示。在J3單元格中輸入公式“=VLOOKUP($I$3，$A$3:$B$4，2，1)”， 在J4單元格中輸入公式“=VLOOKUP($I$4，$A$3:$B$4，2，1)”，結果在J3單元格出現了#N/A錯誤。

在這里，我們一般使用精確匹配查找，也就是把上述兩個公式的第四個參數由“1”更改為“0”。當然也可以使用近似匹配查找，這里出現的#N/A錯誤是因為查詢表的第一列沒有升序排列。

如果公式中第四個參數省略，或為true，或為非0數值時，查詢表的第一列必須升序排列，否則不會返回期望的結果（不一定報錯誤）。

2.2 查詢字符小于查詢表第一列數據的最小值

我們返回去看看圖五，如果在J3單元格中用近似匹配查找，輸入公式“=VLOOKUP($I$3，$A$3:$B$4，2，1)”，結果同樣出現#N/A錯誤。這是因為查詢字符小于查詢表第一列數據的最小值。對于此類錯誤，只要建立可能的最小值，使數據區設計合理就可避免。

3 錯誤處理

在圖五中，在J3單元格中輸入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”，結果出現了#N/A錯誤。為了使表格更美觀而不要顯示#N/A錯誤，可以使用條件格式或直接使用公式處理。

3.1 使用條件格式

在圖五所示的工作表中，選定J3單元格，選擇菜單中的“格式－條件格式”，輸入公式“=ISNA(J3)”，設定字體顏色與底色（一般是白色）相同。使用這種方式進行錯誤處理，仍然會顯示代表錯誤的綠色三角。操作結果如圖七所示。

3.2 使用公式處理①綜合應用IF、ISNA和VLOOKUP函數。在圖七所示的工作表中，選定J3單元格，輸入公式“=IF(ISNA(VLOOKUP($I$3，$A$3:$B$4，2，0))，\"\"， VLOOKUP($I$3，$A$3:$B$4，2，0))”。操作結果如圖八所示。②綜合應用IF、TYPE和VLOOKUP函數。在圖七所示的工作表中，選定J3單元格，輸入公式“=IF(TYPE(VLOOKUP($I$3，$A$3:$B$4，2，0))=16，\"\"，VLOOKUP($I$3，$A$3:$B$4，2，0))”。操作結果如圖八所示。③綜合應用COUNTIF和VLOOKUP函數。在圖七所示的工作表中，選定J3單元格，輸入公式“=IF(COUNTIF($A$3:$B$4，$I$3)，VLOOKUP($I$3，$A$3:$B$4，2，0)，\"\")”。操作結果如圖八所示。④綜合應用IF、ISERROR和VLOOKUP函數。在圖七所示的工作表中，選定J3單元格，輸入公式“=IF(ISERROR(VLOOKUP($I$3，$A$3:$B$4，2，0))，\"\"，VLOOKUP($I$3，$A$3:$B$4，2，0))”。操作結果如圖八所示。需要說明的是，在上述四種公式中的空字符，也可以根據需要改寫成“查無此人”等類似的提示。在這個示例中，可以改寫成“無此科目”之類的提示。

通過以上分析，相信對VLOOKUP函數有了進一步的了解，并能在以后的工作中，輕松應對類似的錯誤。

參考文獻：

[1]伊娜.Excel在會計中的應用.高等教育出版社.

[2](美)John Walkenbach.中文版Excel2007高級VBA編程寶典.清華大學出版社.

注：本文中所涉及到的圖表、注解、公式等內容請以PDF格式閱讀原文