最神奇的VLOOKUP

直到這兩三年開始跟別人解釋我學習Excel的歷程時，才開始回想開始讓我驚奇"原來Excel可以這麼做"的功能，應該就是VLOOKUP吧!
從念書一直到我第一份工作結束前，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。
重點：

  1. V只會從找範圍的最左上第一欄，從上「垂直」往下找到範圍的尾巴。
  2. 找到第一個符合的值，就開始從左往右數指定的欄數，將結果回傳。不管下面是否有其他符合的值。
  3. 從左往右數的時候，第一欄也會納入計算。如果Col_index_num設成1，有找到結果一定會跟Lookup_value一樣。
  4. Range_lookup務必要輸入FALSE或數字0。
  5. 找不到結果會顯示成#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。

留言

張貼留言

這個網誌中的熱門文章

Excel技巧(1)：檔案肥大的原因，附上減肥撇步

圖片
應該遇過：明明 Excel 沒存很多資料，可是檔案卻異常的龐大。甚至存檔會鈍鈍的。有時候這只是因為 Excel 檔案 " 虛胖 " 喔 !! 經驗來說，可能是以下幾種原因造成的，提供一些簡單的解決方式供參考。 1. 工作表有異常多的空白行或列。 怎麼判斷檔案有這種狀況呢？只要觀察檔案橫向 ( 水平 ) 與縱向 ( 垂直 ) 的滾軸 (scroll bar) ，當你認為你當下選取的位置已經是資料的尾端，然而滾軸卻看起來往下 ( 或往右 ) 還未到底，而且差很遠，請檢查你認為的資料尾端到把滾軸拉到底部中間，是否真的沒有資料 ( 這裡是在檢查你是否有漏看 ) 。大多數的情況你其實都可以很快地判定出來，沒有的情況就表示 Excel 工作表存在了沒有用到的行或列，而且多半數量非常的多。 造成這樣的原因，應該是 Excel 認為中間這一大段範圍中，存在過資料、或以為會有資料，因此 Excel 已經把空間空出來，然而之後卻沒正常地判斷出那些空間是不再需要了。 簡單的作法就是手動地執行刪除，大多數的情況是可以解決這問題，少數情況在舊版的 Excel 必須依靠微軟曾推出的 1 個增益集來用巨集的方式刪除，只是這增益集並沒有再繼續更新，無法直接在新版 Excel 套用。 手動刪除的步驟可參考如下：在認為資料的最後 1 列 ( 行 ) 之後，選取下 1 個空白列 ( 行 ) ，然後按著 Ctrl + Shift + 方向鍵下 ( 行的話則改方向鍵右 ) ，這時候你會看到跳到 Excel 檔案上限的最尾端，中間都被選取。這時候只要按右鍵功能表，選擇 " 刪除 " 。檔案存檔關閉後，重新打開即應可按到 scroll bar 回復正常。 2. 檔案中有使用大量引用其他檔案的公式 引用其他檔案的公式，指的是像使用 VLOOKUP 去另一個檔案 "V" 資料回來。 Excel 的儲存格存公式會比存值多佔一些空間，但如果公式的內容是其他檔案的時候，所佔的空間會來得更大上許多。在工作中應該最常發生去 VLOOKUP 一個從郵件打開的 Excel 檔，這個資料所在的 Excel 檔其實也只存在本機的暫存目錄，不可能可以更新這公式，好的工作習慣是 VLOOKUP 完之後，把結果再貼成值。只是實務面常會有
...more... »

Excel技巧(2)：拜託殺了那些看不見的空白吧！

看過儲存格內怎麼看明明都是數字沒錯，可是卻怎麼樣都沒有辦法用它來做任何運算吧? 就算使用很基本的公式(TRIM)、或者資料剖析的方式，始終沒有辦法有效的改正。 其實原因大多都是你的資料很有可能是從網頁上的表格複製下來，或者檔案根本就是網頁格式(Excel很聰明可以自動開啟，只是有些地方沒有處理)。 如何診斷 ： 使用公式 =LEN(儲存格) 來判斷，如果你眼睛明明看到的就1個3位數的數字，理當用LEN來數把3位數字當成文字的長度，應該也是3，可是結果卻是3以上的數字。 肇因 ： 儲存格除了看到的數字之外，也存在了其他「不是用鍵盤上空白鍵輸入的空白」....很像在繞口令，但文字中其實有很多沒有顯示、或者顯示跟空白相同，但是他卻不等於空白。 舉例來說：換行符號、Tab鍵(一般人非常難再儲存格中輸入TAB)、或HTML的空白 。這些都是導致該儲存格被Excel判定是文字，可是我們卻沒有辦法用取代空白的方式改變。 解法 ； 如果只有幾筆資料的話，真的！自己另外在別格看著數字重新敲一次吧。這絕對是最有效率與簡單的方法(沒開玩笑，我真的是建議幾個數字而已，手敲吧~~) 用滑鼠或鍵盤點選編輯該儲存格，在數字的前後想辦法可否選取到那看不見的空白，想辦法複製1個，再用取代的功能，用Ctrl+V貼上那看不到的空白。(一般的HTML空白是可以這麼處裡的!) 使用巨集寫成Function 或者 Sub，然後用以下的程式碼處理它。我自己是把這個類似的功能寫成Sub，然後固定儲存在個人巨集，設定快速鍵來呼叫。(改天再來寫怎麼做出個人巨集 Sub Trim_Range(selRange As Range)     Dim i As Long, j As Long, cnt As Integer     cnt = 0     For i = 1 To selRange.Areas.Count         For j = 1 To selRange.Areas(i).Count             If IsEmpty(selRange.Areas(i).Item(j).Value) Then                 cnt = cnt + 1             Else                 If cnt >
...more... »

Excel版本與檔案格式

要談Excel之前，必須先談Office。近期(也快10年了?!)Office的分水嶺是 Office 2007，主要改變和差異如下： Office 97, 2000, 2003: 過去的版本，彼此間的相對差異不大，整體而言2003增加一些人性化的功能，只不過不是每個都成功、甚至導致問題。例如選單項目根據使用頻率顯示或隱藏(最後我都一律關掉這功能)，或者在Word中排版圖片或表格，使用Word 2003會讓我想砸電腦，永遠搞不定改回用Word 2000可以3秒搞定的排版。 Office 2007, 2010, 2013, 2016: 最顯著的差異在Ribbon介面，初接觸時非常令人卻步，但習慣反而覺得這樣群組選單是不錯的設計。其次是每個應用程式都使用新的檔案格式(規則是副檔名加上1個X，例如*.DOC換成*.DOCX、*.XLS換成*XLSX)。其他就是越新的版本越強化了視覺化和預先定義的功能(把預想的目的直接做成一個按鈕或設定)，以及與雲端網路的更深入整合。 而針對Excel，環境許可的話，請不要再用Excel 2003以前的舊版本，很多小地方是可以大幅簡化和加快使用的作業。只不過我建議會不要使用Excel 2007，其他任何版本都沒太大差異。在過去的經驗上，這個版本蠻常導致檔案最後無法存檔、或無法正常開啟(升級到Office 2007 SP1僅些微改變)。 在我的介紹中，會避開著墨哪個版本開始導入的，這些都可以在微軟的線上說明找到答案，也可以參考Wikipedia的比較「 Microsoft Excel 」條目。 接下來討論Excel新舊格式的差異，主要可用這個屬性在表格中解釋。 --> 舊格式 新格式 *.XLS *.XLSX *.XLSM *.XLSB 官方名稱 Excel 97-2003活頁簿 Excel 活頁簿 Excel 啟用巨集的活頁簿 Excel 二進位活頁簿 工作表允許最大列數 65,536列 1,048,756列 工作表允許最大欄數 256欄 16,384欄 允許檔案內儲存巨集 Yes No Yes Yes 用Excel 2003開啟 完整讀取 & 修改 僅可讀取無法更新存檔 用Excel 2007以後開啟 「相容模式」開啟 & 修改 完整讀取 & 修改 檔案格式(概念)
...more... »