最神奇的VLOOKUP
直到這兩三年開始跟別人解釋我學習Excel的歷程時，才開始回想開始讓我驚奇"原來Excel可以這麼做"的功能，應該就是VLOOKUP吧!
從念書一直到我第一份工作結束前，Excel我只用來使用第3方增益集做統計分析和決策分析，不然只是記錄log，最多拿來暫代VB6開發些寫轉檔程式(重灌後懶得安裝)。直到工作開始要排MPS、匯工單之後，才第一次接觸VLOOKUP (但始終想不起來當時學的心情，教我的應該是丹尼兄或艾琳妹妹...我是個受教還是叛逆的學生哩?)。
總之，累積越來越多樣的工作性質和範圍後，VLOOKUP(簡稱為"V")可以是簡單的查詢函數、更可以複雜計算報表中可活用變化的基礎元素。
要理解這函數，先拆出"LOOKUP"。是的，就是查字典的"lookup a dictionary"，觀念就跟查字典一樣。有一個題目，拿起一本字典翻開目錄查詢有無符合問題的答案，接著依照規則翻到頁面找到答案。
題目，就是「要搜尋的值」；
字典，就是「可搜尋的範圍」，目錄就是「可搜尋範圍的第一欄或第一列」；
規則，就是「要傳回搜尋範圍的第幾欄或列」。
太抽象? 就直接拿VLOOKUP來說明吧!
V表示－Vertical。所以VLOOKUP= Vertical Lookup，垂直方向的查詢。透過函數說明知道它需要以下幾個參數：
-->
EXCEL就是執行從 Table_array左上角第一欄的第一個值開始逐個往下找，找到第一個跟Lookup_value完全符合的值時，依照指定的Col_index_num從那列由左往右找到指定個數的值，傳回這個值，然後結束。當找到範圍的最後仍沒有符合的值，傳回一個錯誤顯示為#N/A。
重點：
Lookup_value輸入是A:A，Excel自動找到跟目前函數對其的那一個A2的資料=X1。建議函數仍應輸入A2，比較易讀與找錯誤。
Table_array輸入是E1:H15，第一欄是E欄，所以就從E欄中範圍的第一個E1開始往下找第一個符合的值，找到第11列發現X1。
Col_index_num輸入是3，表示從找到答案的那一列(包含)開始從左往右數第3欄，找到儲存格G11，回傳它的值G-11。
以這個例子而言，如果將Col_index_num改成1時，它的結果就會是x1，跟Lookup_value相同；E欄第13列同樣是x1是不會被找到的，也就是正常的VLOOKUP是無法回傳儲存格G13的值G-13。
更進一步，當把Col_index_num從3改成10的時候，因為Table_array範圍並沒有10欄，所以即使有找到x1，但會回傳#REF!的錯誤(#REF!表示過程中有一個參照找不到、超過範圍等錯誤)。
這樣應該可以理解 "V" 在做什麼了吧!? 下次你在"V"的時候，試著在模擬一遍第一個結果是怎麼產生的吧!!
同理，HLOOKUP是水平(Horizontal)的查詢，跟VLOOKUP的差別就是在Table_array中從範圍的最左上第一列，從左「水平」往右找到範圍的尾巴，找到第一個符合的值，就開始從上往下數指定的列數(Row_index_num)，將結果回傳。不管下面是否有其他符合的值。(欄<->列、上/下<->左右 這些互換而已，轉90度鏡像)
【溫故知新】
在「Excel版本與檔案格式」中提到的「相容模式」最常造成"V"的時候發生錯誤。主要是你在一個Excel 97-2000檔案格式(*.XLS)中去 "V" 另一個檔案範圍，而該檔案是新Excel檔案格式中(ex: *.XLSX)，此時Excel會提示錯誤訊息：
其實這真的很常發生，譬如你開啟的檔案其實是*.CSV等都會自動以「相容模式」開啟。
解決做法第一個當然是把要跑 "V" 的檔案另存成新的Excle格式，但你必須重新開啟該檔案才可以不再是「相容模式」。
另一個作法是你把 "V" 的範圍不要選擇整欄或整列，改給定一個範圍。而很多時候也不用太執著一定要選擇真的有資料的範圍，例如雖然不知道資料範圍有多大，但肯定落在65536列之內，Ex: 原本是 A:D 的範圍可以直接改成 A1:D60000。
從念書一直到我第一份工作結束前，Excel我只用來使用第3方增益集做統計分析和決策分析，不然只是記錄log，最多拿來暫代VB6開發些寫轉檔程式(重灌後懶得安裝)。直到工作開始要排MPS、匯工單之後，才第一次接觸VLOOKUP (但始終想不起來當時學的心情，教我的應該是丹尼兄或艾琳妹妹...我是個受教還是叛逆的學生哩?)。
總之，累積越來越多樣的工作性質和範圍後，VLOOKUP(簡稱為"V")可以是簡單的查詢函數、更可以複雜計算報表中可活用變化的基礎元素。
要理解這函數，先拆出"LOOKUP"。是的，就是查字典的"lookup a dictionary"，觀念就跟查字典一樣。有一個題目，拿起一本字典翻開目錄查詢有無符合問題的答案，接著依照規則翻到頁面找到答案。
題目，就是「要搜尋的值」；
字典，就是「可搜尋的範圍」，目錄就是「可搜尋範圍的第一欄或第一列」；
規則，就是「要傳回搜尋範圍的第幾欄或列」。
太抽象? 就直接拿VLOOKUP來說明吧!
V表示－Vertical。所以VLOOKUP= Vertical Lookup，垂直方向的查詢。透過函數說明知道它需要以下幾個參數：
-->
| VLOOKUP | ||
| Looup_value | 要搜尋的值 | 可以是單一值，也可以是一欄(個人不建議)。 |
| Table_array | 可搜尋的範圍 | 可以是指定的限定範圍(指定左上到右下的矩形範圍)、好幾欄(整欄的選取)、好幾列(整列的選取)。 |
| Col_index_num | 要傳回搜尋的範圍中的第幾欄 | 上述Table_array範圍中，從最左邊開始數起的第幾欄，從1開始。 |
| Range_lookup | 建議永遠放 0 或 FALSE，表示查詢要找完全符合的值 | 省略會當作1 = TRUE，實務我還沒有遇過需要等於TRUE的情況，請務必注意！ |
EXCEL就是執行從 Table_array左上角第一欄的第一個值開始逐個往下找，找到第一個跟Lookup_value完全符合的值時，依照指定的Col_index_num從那列由左往右找到指定個數的值，傳回這個值，然後結束。當找到範圍的最後仍沒有符合的值，傳回一個錯誤顯示為#N/A。
重點：
- V只會從找範圍的最左上第一欄，從上「垂直」往下找到範圍的尾巴。
- 找到第一個符合的值，就開始從左往右數指定的欄數，將結果回傳。不管下面是否有其他符合的值。
- 從左往右數的時候，第一欄也會納入計算。如果Col_index_num設成1，有找到結果一定會跟Lookup_value一樣。
- Range_lookup務必要輸入FALSE或數字0。
- 找不到結果會顯示成#N/A的錯誤。(#N/A不一定是找不到導致，輸入的任何一個參數是錯誤也會是回傳#N/A，ex: Lookup_value是#N/A，就算範圍中有一個第一欄是#N/A，結果依樣只會是#N/A)
參考以下的簡陋範例，
=VLOOKUP(A:A, E1:H15,3,0)
Lookup_value輸入是A:A，Excel自動找到跟目前函數對其的那一個A2的資料=X1。建議函數仍應輸入A2，比較易讀與找錯誤。
Table_array輸入是E1:H15，第一欄是E欄，所以就從E欄中範圍的第一個E1開始往下找第一個符合的值，找到第11列發現X1。
Col_index_num輸入是3，表示從找到答案的那一列(包含)開始從左往右數第3欄，找到儲存格G11，回傳它的值G-11。
以這個例子而言，如果將Col_index_num改成1時，它的結果就會是x1，跟Lookup_value相同；E欄第13列同樣是x1是不會被找到的，也就是正常的VLOOKUP是無法回傳儲存格G13的值G-13。
更進一步，當把Col_index_num從3改成10的時候，因為Table_array範圍並沒有10欄，所以即使有找到x1，但會回傳#REF!的錯誤(#REF!表示過程中有一個參照找不到、超過範圍等錯誤)。
這樣應該可以理解 "V" 在做什麼了吧!? 下次你在"V"的時候，試著在模擬一遍第一個結果是怎麼產生的吧!!
同理，HLOOKUP是水平(Horizontal)的查詢，跟VLOOKUP的差別就是在Table_array中從範圍的最左上第一列，從左「水平」往右找到範圍的尾巴，找到第一個符合的值，就開始從上往下數指定的列數(Row_index_num)，將結果回傳。不管下面是否有其他符合的值。(欄<->列、上/下<->左右 這些互換而已，轉90度鏡像)
【溫故知新】
在「Excel版本與檔案格式」中提到的「相容模式」最常造成"V"的時候發生錯誤。主要是你在一個Excel 97-2000檔案格式(*.XLS)中去 "V" 另一個檔案範圍，而該檔案是新Excel檔案格式中(ex: *.XLSX)，此時Excel會提示錯誤訊息：
其實這真的很常發生，譬如你開啟的檔案其實是*.CSV等都會自動以「相容模式」開啟。
解決做法第一個當然是把要跑 "V" 的檔案另存成新的Excle格式，但你必須重新開啟該檔案才可以不再是「相容模式」。
另一個作法是你把 "V" 的範圍不要選擇整欄或整列，改給定一個範圍。而很多時候也不用太執著一定要選擇真的有資料的範圍，例如雖然不知道資料範圍有多大，但肯定落在65536列之內，Ex: 原本是 A:D 的範圍可以直接改成 A1:D60000。
留言