電癮院

以經常會遇到的問題及「初學者」的角度,來看待「電腦教學」這回事。

Excel深度教學:VLOOKUP查表函數及比對中文字串時發生錯誤的問題

利用VLOOKUP查表公式,尋找其它欄位的數值

  當我們利用「資料驗證」製作好文具用品分類的下拉式選單,也製作好第二層「品名」的動態下拉式選單之後,請參考「Excel深度教學:「二層」、「三層」的動態「下拉式選單」設定」,再來就是要傷腦筋每一項文具產品所對應的「單位」及「單價」了,總不能製作好便利的下拉式選單之後,卻要自己逐一的輸入各文具產品的「單位」及「單價」吧?

  因此在這邊就要介紹一個在Excel中,非常好用的高階函式,也是「VLOOKUP」。

excel_vlookup-1.jpg

  以下是微軟官方網站「如何使用 VLOOKUP 或 HLOOKUP 尋找完全相同的值」,對於VLOOKUP的參數資料,而括號內的文字是我的註解:

  =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

  • Lookup_value:在陣列第一欄中所要尋找的值。(要用來比對的文字)
  • Table_array:查閱資料中的資訊表格。(用來「被」比對的資料庫)
  • col_index:相符數值應該傳回的 table_array 應傳回相符的值。(比對後,要傳回資料庫的哪個欄位,從1開始)
  • Range_lookup:無論您想要尋找完全相符或近似的相符值,其為指定的邏輯值。如果是 TRUE 或是省略,就會傳回近似的相符值;也就是說,如果沒有找到完全相符的值,就會傳回下一個小於 Lookup_value 的最大值。如果是 FALSE,VLOOKUP 則會尋找完全相符值。如果找不到完全相同的值,就會傳回 #N/A 錯誤值。

  看完了這些微軟的拉拉喳喳說明後,總之就是「要查什麼?從哪裡查?傳回什麼資料?」,接下來直接做個實例應用給大家看,就可以知道這個公式要如何運用了。

「VLOOKUP」函數範例說明

  首先在使用VLOOKUP之前,我們要先把要查表的「表格」給準備好,這是一定的,因為您想要查表,總是要先有一個「表」來查吧?

  Step 1. 以我們一直以來的文具用品的例子來說,我另外建立了一個「售價表」的工作表,如下圖所示,而我們所有使用的查表範圍即為紅色框框的地方,你知道為什麼要省略「分類」及「流水號」嗎?

excel_vlookup-2.jpg

  這是因為VLOOKUP的函式規定,所要查詢的表格第一欄,就是我們所要比對的數值,因此我們就要把「品名」這個將要用來比對的資料,安排在第一欄的位置。

  Step 2. 接下來就要在要輸入的表格中,設定VLOOKUP的公式了,請點擊「單位」下面的儲存格,接著輸入「=VLOOKUP(C2,售價表!C1:E10, 2, FALSE)」,公式中的說明如下:

  C2 => 為參考值,在這為「鉛筆」,我們就是要利用這個字串,來查詢「表格」對應該欄位資料。
售價表!C1:E10 => 為資料庫的表格,要查詢的資料,都是從這邊來查詢的,也就是在上一步中的表格資料範圍。
2 => 是要回傳「表格」的欄位。(1為品名、2為單位、3是單價),因此這邊會回傳「單位」這個欄位的值。
FALSE => 比對時,必需要完全相同,才回傳資料。

excel_vlookup-3.jpg

  而您應該會注意到,我所輸入的公式,好像和我剛才說的不太一樣:

  • =VLOOKUP(C2,售價表!C1:E10, 2, FALSE)
  • =VLOOKUP(C2,售價表!C$1:E$10, 2, FALSE)
    =>
  • =>

  這兩個差別在於「$錢號」,稍微懂Excel的朋友應該知道,「$」在拖曳儲存格複製公式時,可以「固定」行號或列號,這我們等一下再示範。

比對中文字會發生錯誤的問題

  在這裡我要提出一個問題:

  「=VLOOKUP(C2,售價表!C1:E10, 2)」,若您在輸入公式時,少掉「FALSE」時,會發生什麼狀況呢?

  由於少了FALSE,在比對時,就會以相似的比率來決定回傳的數值,因此這在比對「中文字」時,就會出現問題,例如下圖,當我將公式的FALSE拿掉之後,居然單位出現的是「包」,而不是正確的「支」了。

excel_vlookup-4.jpg

  其實,為了要百分百的抓出正確的數值,把「FALSE」加上去就對了。

  Step 3. 回到我們輸入資料的地方,當我們把「$」放進欄位的地方,在使用「上、下」拖曳複製公式時,C2這個儲存格的位置就會自動的遞增,第三格變C3,以此類推,所以下面的儲存格,也能快速的填好資料,如下圖:

excel_vlookup-5.jpg

  而以同樣的方法來填入「單價」的公式,如下:

  • =VLOOKUP(C2,售價表!C$1:E$10, 3, FALSE)

  和之前「單位」的差別只差在回傳的欄位值,回傳「單價」時,要回傳第3欄,這是為什麼這裡要放「3」的原因。

excel_vlookup-6.jpg

  同樣將「單價」下方的儲存格,也複製好公式,就可以完成我們所要製作的表格了。

利用「名稱管理員」可以將VLOOKUP做的更好

  以上述的方式來使用「VLOOKUP函式」已經是非常好了,同樣的公式,還可以寫的更簡潔、更好維護,也就利用前幾篇Excel教學文章中所提供的「名稱管理員」。

  只要您新增定義「售價表」這個「名稱」之後,如下圖:

excel_vlookup-7.jpg

  在輸入VLOOKUP公式的表格範圍時,就不需輸入又臭又長的表格定義了,不僅如此,將來若是查表的「表格」資料有異動,您都只需要去修正「名稱管理員」的資料定義範圍即可,而不需要大工程的修正輸入工作表的所有儲存格,因此在使用VLOOKUP時,強烈建議一定要使用「名稱」來輔助。

excel_vlookup-8.jpg

  若不曉得名稱要如何定義的話,請參考「Excel深度教學:使用「資料驗證」來製作「下拉式選單」」這篇文章。

Previous

Excel深度教學:「二層」、「三層」的動態「下拉式選單」設定

Next

插入USB隨身碟之後,出現「USB裝置無法辨識 (USB Device Not Recognized)」的問題

35 Comments

  1. MI YA KO

    好厲害喔
    連EXCEL深度教學都有

    我EXCEL學學後
    除了常用的東西
    有很多都還給老師了XD

  2. changyang319

    美彌過獎了,我剛好Excel自學了蠻多東西,其實Excel配合VBA及資料庫,也可以做出蠻好用的系統,我會先將這些比較基礎先教,日後有機會的話,再針對比較進階的應用做一個完整的分享。

  3. jenniferlee0926

    老師您好 我用自己的資料試了一次
    請問為何
    「=VLOOKUP(C2,售價表!C$1:E$10, 2, FALSE)」
    若打FALSE 計算結果變成NA
    若打TRUE又是錯誤的結果
    這樣要怎麼辦呢?
    謝謝

  4. changyang319

    如果您所列在上面的公式,是直接從您Excel上複製貼上的話,那就是
    您的「=VLOOKUP(C2」後面打了全形的分號「,」,應該要打「,」,就和之後打的分號一樣。

  5. steven

    請問如果是在左邊的欄位呢

  6. changyang319

    抱歉,我不懂您的意思?

    左邊欄位是什麼意思?可以再說仔細一些,謝謝您。

  7. Noel Tsui

    想請問,怎樣用vlookup 去找尋多個值? 因為一般lookup 後都只能找出一個值.
    例如:
    A1:apple B1:red
    A2:orange B2: orange
    A3:rice B3:white
    A1:apple B4:green

    如平時用VLOOKUP去找尋APPLE,只能找尋RED, 不能同時顯示RED,GREEN, 我要怎樣設定方式呢? 謝謝幫忙

  8. changyang319

    這種狀況,我也想不到有現成的函數可以使用,似乎你只能用VBA來寫程式了。

  9. Sandy

    請問一下, 常使用vlookup時, 回復結果#N/A但若直接copy/paste去對應表格ctrl+F search是可以找的到的, 是否每格的"數值"不同會影響結果呢?

  10. changyang319

    你有在參數的最後加上「FALSE」了嗎?有時會抓不到,都是因為這個的緣故。

  11. kYle1127

    A B
    1 apple red
    2 orange orange
    3 rice white
    4 apple green

    10 apple

    at B10 to B50 (say), enter
    =IF(ISERROR(INDEX($A$1:$B$4,SMALL(IF($A$1:$A$4=$A$10,ROW($A$1:$A$4)),ROW(1:1)),2)),"",INDEX($A$1:$B$4,SMALL(IF($A$1:$A$4=$A$10,ROW($A$1:$A$4)),ROW(1:1)),2))
    and press <Ctrl>+<Shift>+<Enter> at end of the formula to make it an Array Formula

  12. kk

    請問 VLOOPUP 從兩個相同FORMAT FILE A 和 B.
    FILE A 找不到找FILE B, 可不可以?

  13. changyang319

    Excel可以跨檔案使用。

    例如:
    =VLOOKUP(A2,'C:\Users\Andrew\Desktop\[Database.xlsx]Sheet1'!$A$1:$B$4, 2)

    我有試過,確實是可以從另一個檔案中,將資料取回的。

  14. kk

    我已用過, 我是想問, 如果 2 個相同FORMAT FILE C:\Users\Andrew\Desktop\Database1.xlsx 和 C:\Users\Andrew\Desktop\Database2.xlsx, 從Database1.xlsx 找不到, 便到 Database2.xlsx 找, o.k. 嗎? 不會出 N/A!!!

  15. changyang319


    IF(ISERROR(Database1), Database2, Database1 )

    範例:
    =IF(ISERROR(VLOOKUP(A2,'[Database.xlsx]Sheet1'!$A$2:$B$4, 2, FALSE)), VLOOKUP(A2,'[Database2.xlsx]Sheet1'!$A$2:$B$4, 2, FALSE), VLOOKUP(A2,'[Database.xlsx]Sheet1'!$A$2:$B$4, 2, FALSE))

  16. 凱西:)

    請問excel有沒有辦法跨表格查詢?
    就是我要找的數值是A,B,C三欄都剛好符合[公司名稱,季別,國家]
    這樣會跨欄位,目前用VLOOKUP直接拉會在LOOKUP_VALUE那顯示:[A1:C1]
    不過這樣出來的結果是:#N/A
    請問有解決的辦法嗎?

  17. changyang319

    這種要做的話,最容易達成的方式,大概就是直接使用寫VBA程式,用SQL資料庫語法,直接將符合的資料給一次選擇出來,建議您可以朝這方面去研究。

  18. 凱西:)

    感謝回覆,最近買了:Excel VBA一點都不難
    決定朝以VBA的方式找出解答,希望會有幫助
    感謝版主

  19. changyang319

    若沒有程式語言的基礎,一時要馬上進入狀況,確實是不容易的,但您這麼積極想要學習VBA,相信一定能很快就學好的。

    您在學的時候,最後就要學會將資料存在Access這類的檔案型資料庫中,最後才用SQL指令來把資料給載入。

  20. megrimvox .

    很棒的教學,深入淺出,獲益匪淺!

  21. changyang319

    謝謝您的誇獎。

  22. 皇吟 陳

    將資料存在Access這類的檔案型資料庫中,最後才用SQL指令來把資料給載入

    請問這要如何設定呢?
    有教學嗎?

  23. changyang319

    這部份我沒有寫教學,如果你要找的話,請Google「Excel VBA」、「Access」這類的關鍵字,就可以找到相關的教學。

    當然最容易吸收的方式,就是去買本Excel VBA的書籍,有些書會有這方面的教學。

  24. 力勤

    請問C2可以是合併儲存格?

  25. changyang319

    我想是可以的。

    因為就算C2和D2合併,在合併之後的名稱,也還是C2。

  26. FI

    您好
    想請問 自己在試玩VLOOKUP,但公式填入的結果並不是我原始資料表的內容,該如何除錯比較好,謝謝您

  27. YOKO

    你好! 之前用VLOOKUP跨表單查詢都沒問題
    不過前幾天做新報表vlookup都只顯示公式而不是運算結果
    請問是哪邊出問題了嗎?

  28. changyang319

    sorry,如果你之前有使用過vlookup,那現在發生這樣的問題,我還真不曉得是什麼情況。

  29. Casber

    我使用 VLOOKUP 連結到分頁,但是檔案另存新檔之後,會變成連結到原檔的原分頁,請問要怎麼處理才能保持連結到自己檔案的自己分頁,謝謝。

  30. changyang319

    我剛試了一下,並不會有你說的情況。

    以我的例子
    =VLOOKUP(A3,Sheet2!A1:B4, 2, FALSE)

    這裡的Sheet2,都是自己檔案的Sheet2,不曉得你的公式是怎麼下的?

  31. Ting

    你好~~
    我最近在使用VLOOKUP
    已經在Sheet1(工作頁名稱訂為一月)設定了"一月收據編號結算"
    格子內是 前月收據結存數 0 0
    本月新領收據數 50 701-750
    本月使用收據張數 13 701-714
    本月作廢收據張數 2 713、715

    在Sheet3(工作頁名稱104年彙整)的D2是名稱"本月新領收據數"
    D4=VLOOKUP(D2,一月收據編號結算,2,FALSE)
    【本是希望Sheet3裡面的D4會自動顯示出比對D2("本月新領收據數")跟Sheet1裡的"一月收據編號結算"後跑出50】
    卻都是跑出0的數字
    請問是何處設定錯誤了嗎?

    謝謝

  32. changyang319

    奇怪,我照著你給我的資訊,做出來的表格,跑出的值是 50 沒錯,我不曉得你為什麼會跑出0。

    可以把檔案寄到我的mail嗎?
    changyang319@gmail.com

  33. co

    您好,想請問N/A的部分,能否讓它顯示為0呢?

    謝謝

  34. 訪客

    您好,請問如想做VLOOKUP個公司名,但兩個檔案公司名有異,即A檔案公司名" HK Bank Ltd", B檔案公司名"Hong Kong Bank Limited",如這樣能否做到?

    謝謝

  35. ET

    您好,想請問一下,
    若 A 為 123 , B 為 123/456 ,這樣能否比對呢?!
    我已經選了 TRUE 了,但結果還是 #N/A,
    請問有什麼方法能解決呢?!
    麻煩您了,謝謝!

發表迴響

Powered by WordPress & Theme by Anders Norén