您的一個「讚」,就能讓我高興一整天!
  • 若文章中的教學解決不了您的問題,請直接找電腦公司會比較快。
  • 原本我不寫廠商的邀約文,一方面真的是很忙,另一方面我也不是什麼超高人氣部落格,但還是許多廠商會寄信過來邀約。因此,我現在決定如果廠商所提供的產品,我覺得很有用或有趣的,我會「免費」接案( changyang319@gmail.com ),但寫好寫壞,還有截稿的時間不要限制我喔。
電腦重灌 還原鍵製作還原光碟製作修復光碟WinXPWin 7(英文)Win7(中文)Windows 8重灌前準備驅動程式安裝更新Hotfix學習DOS檢查硬碟壞軌測試記憶體如何進入BIOS開機順序/電腦溫度安全模式
常見問題 Wi-Fi不能上網螢幕變黑開機到一半卡住主機不斷重開機喇叭沒有聲音麥克風沒有聲音主機風扇很吵主機自動斷電光碟機退不出來
選購經驗 筆記型電腦平板電腦桌上型電腦印表機分享器雙螢幕LCD液晶螢幕滑鼠UPS不斷電系統電視卡無線網卡MacBook Air平板VS筆電
辦公文書 Word必犯錯誤Word合併列印Excel樞紐分析表備份Outlook郵件FTP軟體操作Word打一半當機設定Outlook收Gmail
IP分享器 無線WiFi延伸(WDS)串接分享器另類分享器接法測Wi-Fi訊號頻道DDNS(D-Link)DDNS(No-IP)

利用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深度教學:使用「資料驗證」來製作「下拉式選單」」這篇文章。

, ,

Posted by 唐先生 at 痞客邦 PIXNET Guestbook(17) 人氣()


留言列表 (17)

Post Comment
  • MI YA KO
  • 好厲害喔
    連EXCEL深度教學都有

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

    唐先生 replied in 2013/11/13 17:13

  • jenniferlee0926
  • 老師您好 我用自己的資料試了一次
    請問為何
    「=VLOOKUP(C2,售價表!C$1:E$10, 2, FALSE)」
    若打FALSE 計算結果變成NA
    若打TRUE又是錯誤的結果
    這樣要怎麼辦呢?
    謝謝
  • 如果您所列在上面的公式,是直接從您Excel上複製貼上的話,那就是
    您的「=VLOOKUP(C2」後面打了全形的分號「,」,應該要打「,」,就和之後打的分號一樣。

    唐先生 replied in 2014/02/10 01:20

  • steven
  • 請問如果是在左邊的欄位呢
  • 抱歉,我不懂您的意思?

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

    唐先生 replied in 2014/02/15 22:10

  • 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, 我要怎樣設定方式呢? 謝謝幫忙
  • 這種狀況,我也想不到有現成的函數可以使用,似乎你只能用VBA來寫程式了。

    唐先生 replied in 2014/03/24 16:54

  • Sandy
  • 請問一下, 常使用vlookup時, 回復結果#N/A但若直接copy/paste去對應表格ctrl+F search是可以找的到的, 是否每格的"數值"不同會影響結果呢?
  • 你有在參數的最後加上「FALSE」了嗎?有時會抓不到,都是因為這個的緣故。

    唐先生 replied in 2014/05/17 06:45

  • 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
  • kk
  • 請問 VLOOPUP 從兩個相同FORMAT FILE A 和 B.
    FILE A 找不到找FILE B, 可不可以?
  • Excel可以跨檔案使用。

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

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

    唐先生 replied in 2014/06/21 11:33

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

  • 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))

    唐先生 replied in 2014/06/21 17:58

  • 凱西:)
  • 請問excel有沒有辦法跨表格查詢?
    就是我要找的數值是A,B,C三欄都剛好符合[公司名稱,季別,國家]
    這樣會跨欄位,目前用VLOOKUP直接拉會在LOOKUP_VALUE那顯示:[A1:C1]
    不過這樣出來的結果是:#N/A
    請問有解決的辦法嗎?
  • 這種要做的話,最容易達成的方式,大概就是直接使用寫VBA程式,用SQL資料庫語法,直接將符合的資料給一次選擇出來,建議您可以朝這方面去研究。

    唐先生 replied in 2014/07/24 05:55

  • 凱西:)
  • 感謝回覆,最近買了:Excel VBA一點都不難
    決定朝以VBA的方式找出解答,希望會有幫助
    感謝版主
  • 若沒有程式語言的基礎,一時要馬上進入狀況,確實是不容易的,但您這麼積極想要學習VBA,相信一定能很快就學好的。

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

    唐先生 replied in 2014/08/06 05:11

  • megrimvox .
  • 很棒的教學,深入淺出,獲益匪淺!
  • 謝謝您的誇獎。

    唐先生 replied in 2014/10/12 17:47

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

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

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

    唐先生 replied in 2014/12/09 22:45

  • 力勤
  • 請問C2可以是合併儲存格?
  • 我想是可以的。

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

    唐先生 replied in 2014/12/19 16:29

  • FI
  • 您好
    想請問 自己在試玩VLOOKUP,但公式填入的結果並不是我原始資料表的內容,該如何除錯比較好,謝謝您
  • YOKO
  • 你好! 之前用VLOOKUP跨表單查詢都沒問題
    不過前幾天做新報表vlookup都只顯示公式而不是運算結果
    請問是哪邊出問題了嗎?
  • sorry,如果你之前有使用過vlookup,那現在發生這樣的問題,我還真不曉得是什麼情況。

    唐先生 replied in 2016/03/04 15:40

  • Casber
  • 我使用 VLOOKUP 連結到分頁,但是檔案另存新檔之後,會變成連結到原檔的原分頁,請問要怎麼處理才能保持連結到自己檔案的自己分頁,謝謝。
  • 我剛試了一下,並不會有你說的情況。

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

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

    唐先生 replied in 2016/06/02 18:17

  • 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的數字
    請問是何處設定錯誤了嗎?

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

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

    唐先生 replied in 2016/06/10 21:45