Zi 字媒體

利用VLOOKUP查表公式，尋找其它欄位的數值 當我們利用「資料驗證」製作好文具用品分類的下拉式選單，也製作好第二層「品名」的動態下拉式選單之後，請參考「Excel深度教學：「二層」、「三層」的動態「下拉式選單」設定」，再來就是要傷腦筋每一項文具產品所對應的「單位」及「單價」了，總不能製作好便利的下拉式選單之後，卻要自己逐一的輸入各文具產品的「單位」及「單價」吧？ 因此在這邊就要介紹一個在Excel中，非常好用的高階函式，也是「VLOOKUP」。 以下是微軟官方網站「如何使用 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. 以我們一直以來的文具用品的例子來說，我另外建立了一個「售價表」的工作表，如下圖所示，而我們所有使用的查表範圍即為紅色框框的地方，你知道為什麼要省略「分類」及「流水號」嗎？ 這是因為VLOOKUP的函式規定，所要查詢的表格第一欄，就是我們所要比對的數值，因此我們就要把「品名」這個將要用來比對的資料，安排在第一欄的位置。 Step 2. 接下來就要在要輸入的表格中，設定VLOOKUP的公式了，請點擊「單位」下面的儲存格，接著輸入「=VLOOKUP(C2，售價表!C1:E10, 2, FALSE)」，公式中的說明如下： C2 => 為參考值，在這為「鉛筆」，我們就是要利用這個字串，來查詢「表格」對應該欄位資料。 售價表!C1:E10 => 為資料庫的表格，要查詢的資料，都是從這邊來查詢的，也就是在上一步中的表格資料範圍。 2 => 是要回傳「表格」的欄位。(1為品名、2為單位、3是單價)，因此這邊會回傳「單位」這個欄位的值。 FALSE => 比對時，必需要完全相同，才回傳資料。 而您應該會注意到，我所輸入的公式，好像和我剛才說的不太一樣： 「=VLOOKUP(C2，售價表!C1:E10, 2, FALSE)」 <= 我剛才所說的公式 「=VLOOKUP(C2，售價表!C$1:E$10, 2, FALSE)」 <= 我實際輸入的公式 這兩個差別在於「$錢號」，稍微懂Excel的朋友應該知道，「$」在拖曳儲存格複製公式時，可以「固定」行號或列號，這我們等一下再示範。 比對中文字會發生錯誤的問題 在這裡我要提出一個問題： 「=VLOOKUP(C2，售價表!C1:E10, 2)」，若您在輸入公式時，少掉「FALSE」時，會發生什麼狀況呢？ 由於少了FALSE，在比對時，就會以相似的比率來決定回傳的數值，因此這在比對「中文字」時，就會出現問題，例如下圖，當我將公式的FALSE拿掉之後，居然單位出現的是「包」，而不是正確的「支」了。 其實，為了要百分百的抓出正確的數值，把「FALSE」加上去就對了。 Step 3. 回到我們輸入資料的地方，當我們把「$」放進欄位的地方，在使用「上、下」拖曳複製公式時，C2這個儲存格的位置就會自動的遞增，第三格變C3，以此類推，所以下面的儲存格，也能快速的填好資料，如下圖： 而以同樣的方法來填入「單價」的公式，如下： 「=VLOOKUP(C2，售價表!C$1:E$10, 3, FALSE)」 和之前「單位」的差別只差在回傳的欄位值，回傳「單價」時，要回傳第3欄，這是為什麼這裡要放「3」的原因。 同樣將「單價」下方的儲存格，也複製好公式，就可以完成我們所要製作的表格了。 利用「名稱管理員」可以將VLOOKUP做的更好 以上述的方式來使用「VLOOKUP函式」已經是非常好了，同樣的公式，還可以寫的更簡潔、更好維護，也就利用前幾篇Excel教學文章中所提供的「名稱管理員」。 只要您新增定義「售價表」這個「名稱」之後，如下圖： 在輸入VLOOKUP公式的表格範圍時，就不需輸入又臭又長的表格定義了，不僅如此，將來若是查表的「表格」資料有異動，您都只需要去修正「名稱管理員」的資料定義範圍即可，而不需要大工程的修正輸入工作表的所有儲存格，因此在使用VLOOKUP時，強烈建議一定要使用「名稱」來輔助。 若不曉得名稱要如何定義的話，請參考「Excel深度教學：使用「資料驗證」來製作「下拉式選單」」這篇文章。 Excel教學相關文章 EXCEL實用技巧教學「樞紐分析表(Pivot Tables)」 Excel深度教學：使用「資料驗證」來製作「下拉式選單」 Excel深度教學：「二層」、「三層」的動態「下拉式選單」設定 Excel深度教學：VLOOKUP查表函數及比對中文字串時發生錯誤的問題 讓Excel自動檢查儲存格中是否有輸入重複的資料 excel教學vlookup查表函式 37 Comments Add Comment → 123 這vlook up好像解釋得太難明了, https://blog.gtwang.org/windows/excel-vlookup-function-tutorial/ 這個比較清潔 2020-04-01 Reply Elle 請問vlookup是否能夠只比對兩列資料中的部分數字?，例如:兩列資料中開頭英文+數字相同的前五碼? 2019-11-07 Reply ET 您好，想請問一下， 若 A 為 123 ， B 為 123/456 ，這樣能否比對呢?! 我已經選了 TRUE 了，但結果還是 #N/A， 請問有什麼方法能解決呢?! 麻煩您了，謝謝！ 2017-03-17 Reply 訪客 您好,請問如想做VLOOKUP個公司名,但兩個檔案公司名有異,即A檔案公司名" HK Bank Ltd", B檔案公司名"Hong Kong Bank Limited",如這樣能否做到? 謝謝 2017-02-28 Reply co 您好，想請問N/A的部分，能否讓它顯示為0呢? 謝謝 2017-02-09 Reply changyang319 奇怪，我照著你給我的資訊，做出來的表格，跑出的值是 50 沒錯，我不曉得你為什麼會跑出0。 可以把檔案寄到我的mail嗎？ changyang319@gmail.com 2016-06-10 Reply 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的數字 請問是何處設定錯誤了嗎? 謝謝 2016-06-05 Reply changyang319 我剛試了一下，並不會有你說的情況。 以我的例子 =VLOOKUP(A3,Sheet2!A1:B4, 2, FALSE) 這裡的Sheet2，都是自己檔案的Sheet2，不曉得你的公式是怎麼下的？ 2016-06-02 Reply Casber 我使用 VLOOKUP 連結到分頁，但是檔案另存新檔之後，會變成連結到原檔的原分頁，請問要怎麼處理才能保持連結到自己檔案的自己分頁，謝謝。 2016-05-25 Reply changyang319 sorry，如果你之前有使用過vlookup，那現在發生這樣的問題，我還真不曉得是什麼情況。 2016-03-04 Reply YOKO 你好! 之前用VLOOKUP跨表單查詢都沒問題 不過前幾天做新報表vlookup都只顯示公式而不是運算結果 請問是哪邊出問題了嗎? 2016-02-29 Reply FI 您好 想請問 自己在試玩VLOOKUP，但公式填入的結果並不是我原始資料表的內容，該如何除錯比較好，謝謝您 2015-10-30 Reply changyang319 我想是可以的。 因為就算C2和D2合併，在合併之後的名稱，也還是C2。 2014-12-19 Reply 力勤 請問C2可以是合併儲存格? 2014-12-11 Reply changyang319 這部份我沒有寫教學，如果你要找的話，請Google「Excel VBA」、「Access」這類的關鍵字，就可以找到相關的教學。 當然最容易吸收的方式，就是去買本Excel VBA的書籍，有些書會有這方面的教學。 2014-12-09 Reply 皇吟 陳 將資料存在Access這類的檔案型資料庫中，最後才用SQL指令來把資料給載入 請問這要如何設定呢？ 有教學嗎？ 2014-12-02 Reply changyang319 謝謝您的誇獎。 2014-10-12 Reply megrimvox . 很棒的教學，深入淺出，獲益匪淺! 2014-10-03 Reply changyang319 若沒有程式語言的基礎，一時要馬上進入狀況，確實是不容易的，但您這麼積極想要學習VBA，相信一定能很快就學好的。 您在學的時候，最後就要學會將資料存在Access這類的檔案型資料庫中，最後才用SQL指令來把資料給載入。 2014-08-06 Reply 凱西：） 感謝回覆,最近買了:Excel VBA一點都不難 決定朝以VBA的方式找出解答,希望會有幫助 感謝版主 2014-07-31 Reply changyang319 這種要做的話，最容易達成的方式，大概就是直接使用寫VBA程式，用SQL資料庫語法，直接將符合的資料給一次選擇出來，建議您可以朝這方面去研究。 2014-07-24 Reply 凱西：） 請問excel有沒有辦法跨表格查詢? 就是我要找的數值是A,B,C三欄都剛好符合[公司名稱,季別,國家] 這樣會跨欄位,目前用VLOOKUP直接拉會在LOOKUP_VALUE那顯示:[A1:C1] 不過這樣出來的結果是:#N/A 請問有解決的辦法嗎? 2014-07-17 Reply 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)) 2014-06-21 Reply kk 我已用過, 我是想問, 如果 2 個相同FORMAT FILE C:\Users\Andrew\Desktop\Database1.xlsx 和 C:\Users\Andrew\Desktop\Database2.xlsx, 從Database1.xlsx 找不到, 便到 Database2.xlsx 找, o.k. 嗎? 不會出 N/A!!! 2014-06-21 Reply changyang319 Excel可以跨檔案使用。 例如： =VLOOKUP(A2,'C:\Users\Andrew\Desktop\[Database.xlsx]Sheet1'!$A$1:$B$4, 2) 我有試過，確實是可以從另一個檔案中，將資料取回的。 2014-06-21 Reply kk 請問 VLOOPUP 從兩個相同FORMAT FILE A 和 B. ＦＩＬＥ　Ａ　找不到找ＦＩＬＥ　Ｂ，　可不可以？ 2014-06-18 Reply 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 2014-06-12 Reply changyang319 你有在參數的最後加上「FALSE」了嗎？有時會抓不到，都是因為這個的緣故。 2014-05-17 Reply Sandy 請問一下, 常使用vlookup時, 回復結果#N/A但若直接copy/paste去對應表格ctrl+F search是可以找的到的, 是否每格的"數值"不同會影響結果呢? 2014-05-14 Reply changyang319 這種狀況，我也想不到有現成的函數可以使用，似乎你只能用VBA來寫程式了。 2014-03-24 Reply 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, 我要怎樣設定方式呢? 謝謝幫忙 2014-03-24 Reply changyang319 抱歉，我不懂您的意思？ 左邊欄位是什麼意思？可以再說仔細一些，謝謝您。 2014-02-15 Reply steven 請問如果是在左邊的欄位呢 2014-02-14 Reply changyang319 如果您所列在上面的公式，是直接從您Excel上複製貼上的話，那就是 您的「=VLOOKUP(C2」後面打了全形的分號「，」，應該要打「,」，就和之後打的分號一樣。 2014-02-10 Reply jenniferlee0926 老師您好 我用自己的資料試了一次 請問為何 「=VLOOKUP(C2，售價表!C$1:E$10, 2, FALSE)」 若打FALSE 計算結果變成NA 若打TRUE又是錯誤的結果 這樣要怎麼辦呢? 謝謝 2014-02-09 Reply changyang319 美彌過獎了，我剛好Excel自學了蠻多東西，其實Excel配合VBA及資料庫，也可以做出蠻好用的系統，我會先將這些比較基礎先教，日後有機會的話，再針對比較進階的應用做一個完整的分享。 2013-11-13 Reply MI YA KO 好厲害喔 連EXCEL深度教學都有 我EXCEL學學後 除了常用的東西 有很多都還給老師了XD 2013-11-13 Reply