錦子老師

四、多餘的空格或不可見字元

第7種：資料表中指定範圍儲存格含有多餘的空格，以下圖為例，由於A欄學號某儲存格(A5)內含有多餘的空格，造成查找錯誤。

錯誤原因：多一個空格，用不帶空格的字元查找當然會出錯了。

解決方案:

1、點取含有空格(白)字元儲存格，手動刪除掉空格(白)字元。建議用這個方法;

2、在公式中用TRIM函數替換空格而必須要用資料公式形式輸入。

B12儲存格輸入公式「=VLOOKUP(A9,TRIM(A1:D6),2,0)」後，按CTRL + SHIFT + ENTER將其轉換為陣列形式「{=VLOOKUP(A9,TRIM(A1:D6),2,0)}」。

【注意】如果儲存格內容為純數值時，在數值前後按空白鍵輸入空白字元，看時沒問題，但當按ENTER鍵後，EXCEL 會自動刪除空白字元。

第8種：類空格但非空格的字元。

在指定範圍的某些儲存格中存在大量的「空格」，但又用空格無法替換掉時，這些就是所的「類空格」的不可見字元，這時可以“以其人之道還之其人之身”，直接在儲存格中複製不可見字元貼到取代視窗，取代掉即可。

第9種：不可見字元的影響

以下圖為例的A欄中，A欄中看不到不存在空格和類空格字元，但查找結果還是出錯。

出錯原因：這是從網頁或資料庫中導入資料時帶來的不可見字元，造成了查找的錯誤。

解決方案：在A欄後插入幾列空白欄，然後對A欄進行分欄操作，點取「資料 > 資料剖析」圖示，如下圖所示。

在「資料剖析精靈 - 步驟3之1」對話方塊，先選擇「分隔符號」選項鈕，如下圖所示，在點取「下一步」鈕。

在「資料剖析精靈 - 步驟3之2」對話方塊，選擇無法顥示的空白字元為何，例如「TAB鍵」，若是其他不可顯示的字元，則點取「其他」核取方塊右邊文字區塊，按CTRL + Ｖ貼上無法顯示的字元，如下圖所示，再點取「下一步」鈕。

在「資料剖析精靈 - 步驟3之3」對話方塊，一一點取不同的欄位，設定其資料格式，設定完成後點取「完成」鈕，即可把不可見字元分離出去。

第10種：反向查找VLOOKUP不支援產生的錯誤。

以下圖為例，在資料表中，想要根據姓名查找學號，結果返回了錯誤。

錯誤原因：VLOOKUP不支持反向查找。

解決方法：

1、用IF函數重組區域，讓兩列顛倒位置。

B12儲存格公式為「=VLOOKUP(A12,IF({0,1},A2:A7,B2:B7),2,0)」

2、用INDEX + MATCH組合實現。

B12儲存格公式為「=INDEX(A2:A7,MATCH(A12,B2:B7,0))」

第11種：萬用字元引起的查找錯誤

以下圖為例，根據分數區間查找其等級傳回錯誤值。

錯誤原因：「~」用於查找萬用字元，如果在VLOOKUP公式中出現，會被認為特定用途，非真正的~。如在表格中尋找3*6 ，356，376也被查找到。

如果精確查找3*6，需要使用~，如下圖所示。

解決方法：用~~就可以表示查找~了。所以公式可以修改為E3儲存格公式為「=VLOOKUP(SUBSTITUTE(D3,"~","~~"),A2:B7,2,0)」。

第12種：VLOOKUP函數第1個參數不直接支援陣列形式產生的錯誤

以下圖為例，同時尋找A和C，然後用AVERAGE函數求學期成績平均。

錯誤原因：VLOOKUP第一個參數不能直接用於陣列。

解決方法：利用N/T+IF結構轉化一下陣列，如果不瞭解N/T+IF結構用法，可以參考下面這個公式。

公式修改為：

C11儲存格公式為「=AVERAGE(VLOOKUP(T(IF({1},A11:B11)) , B2:E7 , 4 , ) ) 」。