內容
本課解釋瞭如何快速處理函數 VPR (VLOOKUP) 不想在 Excel 2013、2010、2007 和 2003 中工作，以及如何識別和修復常見錯誤並克服限制 VPR.
在之前的幾篇文章中，我們探討了函數的各個方面 VPR 在 Excel 中。 如果您仔細閱讀了它們，那麼您現在應該是該領域的專家了。 然而，許多 Excel 專家認為並非沒有道理 VPR 更複雜的功能之一。 它有許多限制和功能，成為許多問題和錯誤的根源。
在本文中，您將找到錯誤的簡單解釋 ＃在 (#不適用), ＃NAME？ （#NAME？）和 ＃值！ (#VALUE!) 使用函數時出現 VPR，以及處理它們的技術和方法。 我們將從最常見的情況和最明顯的原因開始。 VPR 不起作用，因此最好按照文章中給出的順序研究示例。
修復 Excel 中 VLOOKUP 函數中的 #N/A 錯誤
在公式中 VPR 錯誤訊息 ＃在 (#N/A) 表示 不可用 （無數據）– 當 Excel 找不到您要查找的值時出現。 這可能有幾個原因。
1. 所需值拼寫錯誤
好主意先檢查這個項目！ 當您處理由數千行組成的大量數據時，或者當您要查找的值被寫入公式時，通常會出現拼寫錯誤。
2. 使用 VLOOKUP 搜索近似匹配時出現 #N/A 錯誤
如果您使用具有近似匹配搜索條件的公式，即參數 範圍查找 (range_lookup) 為 TRUE 或未指定，您的公式可能會報錯 ＃N / A 在兩種情況下：
- 要查找的值小於要查找的數組中的最小值。
- 搜索列未按升序排序。
3. 使用 VLOOKUP 查找完全匹配時出現 #N/A 錯誤
如果您正在尋找完全匹配，即參數 範圍查找 (range_lookup) 為 FALSE 沒有找到準確的值，公式也會報錯 ＃N / A. 詳細了解如何使用函數搜索精確匹配和近似匹配 VPR.
4.搜索欄不在最左邊
您可能知道，最重要的限制之一 VPR 就是它不能面向左邊，因此表中的查找列必須在最左邊。 在實踐中，我們經常忘記這一點，從而導致公式無效和錯誤。 ＃N / A.
決定： 如果無法更改數據結構以使搜索列位於最左側，則可以使用函數組合 INDEX （索引）和 更多曝光 （匹配）作為更靈活的替代方案 VPR.
5.數字格式化為文本
另一個錯誤來源 ＃N / A 在公式中 VPR 是主表或查找表中文本格式的數字。
這通常發生在您從外部數據庫導入信息時，或者當您在數字前鍵入撇號以保留前導零時。
文本格式的數字最明顯的符號如下圖所示：
此外，數字可以存儲在格式 總 （一般的）。 在這種情況下，只有一個值得注意的特徵——數字與單元格的左邊緣對齊，而默認情況下它們與右邊緣對齊。
決定： 如果是單個值，只需單擊錯誤圖標並選擇 轉換成數字 （轉換為數字）從上下文菜單。
如果這是有很多數字的情況，請選擇它們並右鍵單擊所選區域。 在出現的上下文菜單中，選擇 單元格格式 （格式化單元格）> 選項卡 聯繫電話 (數字) > 格式 聯繫電話 （數字）並按 OK.
6.開頭或結尾有空格
這是該錯誤最不明顯的原因。 ＃N / A 在功能上 VPR，因為在視覺上很難看到這些額外的空間，尤其是在處理大表時，因為大多數數據都在屏幕外。
解決方案一：主表中的多餘空格（VLOOKUP函數所在的地方）
如果主表中出現多餘的空格，您可以通過將參數括起來來確保公式正常工作 Lookup_Array中 (lookup_value) 到函數中 TRIM （修剪）：
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
解決方案 2：查找表中的額外空格（在查找列中）
如果搜索欄中有多餘的空格——簡單的方法 ＃N / A 在公式中 VPR 無法避免。 代替 VPR 您可以將數組公式與函數組合使用 INDEX （指數）， 更多曝光 （比賽） TRIM （修剪）：
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
由於這是一個數組公式，不要忘記按 Ctrl + Shift + Enter 而不是通常的 Enter正確輸入公式。
錯誤 #VALUE！ 在帶有 VLOOKUP 的公式中
大多數情況下，Microsoft Excel 會報告錯誤 ＃值！ (#VALUE!) 當公式中使用的值與數據類型不匹配時。 關於 VPR, 那麼報錯的原因通常有兩個 ＃值！.
1.您要查找的值超過255個字符
注意：函數 VPR 無法搜索包含超過 255 個字符的值。 如果您要查找的值超出此限制，您將收到一條錯誤消息。 ＃值！.
決定： 使用一堆功能 索引+匹配 （索引 + 匹配）。 下面是一個可以很好地完成這項任務的公式：
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2.未指定搜索工作簿的完整路徑
如果您從另一個工作簿中檢索數據，則必須指定該文件的完整路徑。 更具體地說，您必須在方括號 [ ] 中包含工作簿名稱（包括擴展名），然後是工作表名稱，然後是感嘆號。 所有這些結構都必須用撇號括起來，以防書名或工作表名包含空格。
這是函數的完整結構 VPR 在另一本書中搜索：
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
真正的公式可能如下所示：
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
此公式將查找單元格值 A2 在列中 B 在單子上 Sheet1 在工作簿中 新價格 並從列中提取相應的值 D.
如果省略了表路徑的任何部分，則您的函數 VPR 不會工作，會報錯 ＃值！ （即使帶有查找表的工作簿當前是打開的）。
有關該功能的更多信息 VPR引用另一個 Excel 文件，請參閱課程：使用 VLOOKUP 搜索另一個工作簿。
3.參數Column_num小於1
很難想像有人輸入的值小於 1指示要從中提取值的列。 儘管如果此參數的值是由嵌套在其中的另一個 Excel 函數計算的，則有可能 VPR.
所以，如果發生這種論點 Col_index_num為 (column_number) 小於 1功能 VPR 也會報錯 ＃值！.
如果論證 Col_index_num為 (column_number) 大於給定數組中的列數， VPR 會報錯 ＃REF！ （#SSYL！）。
錯誤#NAME？ 在 VLOOKUP 中
最簡單的情況是錯誤 ＃NAME？ (#NAME?) – 如果你不小心寫了一個錯誤的函數名，就會出現。
解決方案很明顯——檢查你的拼寫！
VLOOKUP 不起作用（限制、警告和決定）
除了相當複雜的語法， VPR 比任何其他 Excel 函數都有更多的限制。 由於這些限制，看似簡單的公式 VPR 往往會導致意想不到的結果。 您將在下面找到幾種常見情況的解決方案，其中 VPR 是錯的。
1. VLOOKUP 不區分大小寫
功能 VPR 不區分大小寫，接受大小寫相同的字符。 因此，如果表中有多個元素僅大小寫不同，則 VLOOKUP 函數將返回找到的第一個元素，而不考慮大小寫。
決定： 結合使用另一個可以執行垂直搜索的 Excel 函數（LOOKUP、SUMPRODUCT、INDEX 和 MATCH） 精確區分大小寫的 A。 有關更多詳細信息，您可以從課程中學習——在 Excel 中使 VLOOKUP 區分大小寫的 4 種方法。
2. VLOOKUP 返回找到的第一個值
就像你已經知道的那樣， VPR 返回與找到的第一個匹配項對應的給定列的值。 但是，您可以讓它提取您想要的值的第二個、第三個、第四個或任何其他重複。 如果您需要提取所有重複值，則需要組合功能 INDEX （指數）， 最小 （小）和 LINE （排）。
3. 向表中添加或刪除一列
不幸的是，公式 VPR 每次在查找表中添加或刪除新列時停止工作。 發生這種情況是因為語法 VPR 要求您指定搜索的完整範圍和數據提取的特定列號。 自然，當您刪除一列或插入新列時，給定的範圍和列號都會發生變化。
決定： 功能再次急於提供幫助 INDEX （索引）和 更多曝光 （匹配）。 在公式 索引+匹配 您分別定義搜索和檢索列，因此，您可以根據需要刪除或插入任意數量的列，而不必擔心必須更新所有相關的搜索公式。
4.複製公式時單元格引用亂碼
這個標題詳盡地解釋了問題的本質，對吧？
決定： 始終使用絕對單元格引用（帶有符號 $) 記錄範圍，例如 $ A $ 2：$ C $ 100 or $ A：$ C. 在編輯欄中，您可以通過單擊快速切換鏈接類型 F4.
VLOOKUP – 使用函數 IFERROR 和 ISERROR
如果您不想用錯誤消息嚇唬用戶 ＃N / A, ＃值！ or ＃NAME？，您可以顯示一個空單元格或您自己的消息。 您可以通過放置 VPR 變成一個函數 錯誤 (IFERROR) 在 Excel 2013、2010 和 2007 中或使用一堆函數 如果+錯誤 (IF+ISERROR) 在早期版本中。
VLOOKUP：使用 IFERROR 函數
函數語法 錯誤 (IFERROR) 很簡單，不言自明：
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
也就是說，對於第一個參數，您插入要檢查錯誤的值，對於第二個參數，您指定發現錯誤時要返回的內容。
例如，如果未找到您要查找的值，此公式將返回一個空單元格：
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
如果您想顯示自己的消息而不是函數的標準錯誤消息 VPR, 用引號括起來，像這樣：
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP：使用 ISERROR 函數
自功能 錯誤 出現在 Excel 2007 中，在早期版本中工作時，您將不得不使用組合 IF (IF) 和 歐芝卡 （ISERROR）像這樣：
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
例如，公式 如果+ISERROR+VLOOKUP, 類似於公式 干擾+VLOOKUP如上所示：
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
這就是今天的全部內容。 我希望這個簡短的教程能幫助你處理所有可能的錯誤。 VPR 並使您的公式正常工作。