A005 使用 VLookup 函數尋找資料

要製作自動化的工作表, 最重要的函數可能就是 VLookup. 這個函數可以在一串資料之中搜尋特定的資料列. 善用 VLookup 可以讓工作表針對不同的資料顯示不同的結果.

首先我們從台灣證交所匯入水泥工業股票的收盤價 (參考 A003 匯入Web外部資料).
從證交所網址 http://www.twse.com.tw 進入後, 選擇 交易資訊/盤後資訊/每日收盤行情

從每日收盤行情的畫面選擇 水泥工業 (不要選全部, 會等很久)

 選擇收盤價格的表格匯入

為了說明方便, 我們要在資料的上方寫公式, 所以選擇匯入到A6.

 

在上方輸入如下圖的資料, B1:B3 輸入名稱, C1輸入股票代碼, C2輸入如D2內容的 VLOOKUP 公式

C2的公式讓 Excel 去尋找 A10:I16 這個範圍的資料. VLOOKUP 函數會在這個範圍的第一欄(A欄)中找與第一個參數 C1 的內容相同的列(第10列). 第三個參數告訴函數要回應的是這個範圍的第幾欄. 公式中. 在上圖中第三個參數是2, 所以會回應 A10:I16 這個範圍的第二欄(B欄). 所以整個參數回應的值是 B10 的值 (台泥).

可以試著將C1的值改為其他股票的代碼. 看看名稱有沒有變.

現在我們來試試看如果輸入資料裡面沒有的代碼會有什麼結果. 先試試 1107

當我們輸入1107時, VLOOKUP回應的是1104的環泥. 為什麼會這樣呢? 我們點選函數精靈(在公式前方 fx 的地方) 看看為什麼:

函數精靈告訴我們 VLOOKUP 函數有第四個參數 Range_lookup. 選擇第四個參數之後, 函數精靈告訴我們這個參數的用法:

原來省略第四個參數會找出最接近的值(在我們的範例是前一個值 1104) . 我們將 C2 的公式加上第四個參數 FALSE.

因為 VLOOKUP 找不到 1107, 所以回應了錯誤值 #N/A. Range_lookup要用 True 還是 False 要視應用的情況而定, 如果要找到正確的資料時, 一定別忘了加上 False, 以免找到錯誤的資料而不自知.

再來我們要複製公式到下一格, 先在公式列上選擇 C1 公式的前兩個參數

按一下鍵盤上最帥的鍵 F4 (在數字 3 的上方)

公式會改為絕對參照. 這樣子將公式複製到其他儲存格時不會變動位址. 複製到C3之後, 再把第三個參數改為 9 (第9欄), 就如同下圖的樣子了.

延伸習作

1. 如果資料的第一欄沒有排序的話會有什麼結果?
2. 如果我的資料範圍寬度只有8 (A10:H16), 而第三個參數是9時, 會有什麼結果?