2020年3月24日 星期二

善用 Excel 進階函數 vlookup,解決工作上大小事。

Excel 我們知道提供很多函數,從數學、財務、統計、日期...等等有上百個函數,但要在這些函數選一個最常用的函數,我想非 vlookup 莫屬,這個函數主要用途是在您指定的資料範圍內,透過「查閱值」,去尋找在所指定資料範圍內,你所要的欄位資料,其語法如下:
VLOOKUP(查閱值 , 資料範圍 , 指定欄位索引, [已排序])
其參數說明如下:

  • 查閱值我們所要搜尋的值,使用上有二個限制:
    1. 必需是資料範圍內第一欄所會出現的值。
    2. 資料範圍第一欄內,該值最好在第一欄內是唯一值,否則結果可能不如預期。
  • 資料範圍係我們所要搜尋的範圍,系統會在該範圍的第一欄尋找搜尋詞中指定查閱值。
  • 指定欄位索引,代表要傳回的值所屬的欄索引。資料範圍中的第一欄編號為 1,也就是說,如果你想要傳回資料範圍內第3欄的資料,指定欄位索引就設為3,如指定欄位索引並非介於 1 和資料範圍的欄數之間,系統會傳回 #VALUE!
  • [已排序]有二種情形:
    1. 絕大多數情況下,請設為 FALSE,該設定會使系統會傳回完全相符的值。如果有多個相符值,系統會傳回與找到的資料範圍第一欄中第一個值對應的儲存格內容;如果找不到相符值,則會傳回  #N/A
    2. 設為 TRUE 或省略,系統會傳回最接近的相符值 (小於或等於搜尋詞)。如果進行搜尋的欄中所有的值均大於搜尋詞,則會傳回 #N/A
以下舉一實際範例來說明如何應用,現假設我們想從下面範例的股票股利股息表,捉出特定公司現金股息及股價,算出其現金殖利率,在此因為股票代號是唯一的,當然我們就選用股票代號為查閱值,去查出該股票本年度的現金股息及股價。



因此在下面現金殖利率表中,股票代號由我們手動輸入所要查詢的股票代號,股價欄位則應用 vlookup 捉取,語法如下:


=vlookup(B2,'股票股利股息表'!A:F,6,false)

在此查閱值為現金殖利率表的B2,也就是股票代號欄位;資料範圍為股票股利股息表的A到F欄;指定欄位索引在此我們要的是股價,也就是在股票股利股息表由股票代號起算的第6個欄,因此設定為6;[已排序]一般則設為 false 。同樣的現金股息的部分也是相同的方式,語法如下:


=vlookup(B2,'股票股利股息表'!A:F,3,false)

得到股價及現金股息,就可以計算出現金股息殖利率了,在下表的範例1,我們展示計算泰詠現金股息殖利率的結果;在範例2,我們展示如果我們沒輸入股票代號或輸入股票代號,不在股票股利股息表中時,會傳回  #N/A,如果要避免出現,可加上 iferror 函數,將 #N/A轉為我指定的值,因此前面兩個函數可修改如下:


=iferror(vlookup(B4,'股票股利股息表'!A:F,6,false),) 
=iferror(vlookup(B4,'股票股利股息表'!A:F,3,false),)
修改後的結果如範例3所示,在此我們將 #N/A 改為空白 。



前面介紹完 vlookup 語法,粗略來看該語法所做的事情,事實上很簡單,就是將我們所要的資料,由指定資料範圍找出來,有點像是簡單查詢程式,但其應用變化多端存乎一心,用的好的話可以解決工作上的很多問題,例如可用在下面場合:

  1. 如同前面的範例,將其中一個表當作資料庫,將我們所要欄位資料捉到另一個表做計算使用。
  2. 比對兩個表或欄位資料是否有差異,在文書作業中,我們常常會製做很多表,有的時侯版本控管沒控管好,時間久了我們會忘了那一版為最終版,這時可用 vlookup 函數來做比對,方法是在其中一個表中捉取另一個表中的資料,看是全部都會捉到,還是會出現#N/A,由此可知兩個表的差異,當然如果要更細緻一點,可以在加上 if , and ,or 等判斷式,做更細部的確認
  3. 可以用於將多張不同的表,匯整成一張大表使用。
  4. 將適用於試算表運算的表,改為適合的閱讀或文書作業的表,就如同在如何將紙本表單轉為 Google 表單後,讓填寫人填寫完後轉為紙本表單格式印出。 所做的,將表單填表人所填的資訊,轉為紙本表單格式印出。
  5. 跟 4 的應用反過來,是將適合的閱讀或文書作業的表改為試用試算表計算用的表,在此時單用 vlookup 可能沒辦法完全處理好,需要搭配使用 index 、 column、row 等函數才有辦法處理。
以上為 vlookup 使用方式及其應用的介紹,如果能應用的好,事實上可以解決工作上的很多問題,故我一直認為 vlookup 為 Excel 最重要的函數,不過如果你問我在 Google 試算表是否也是如此,那我會說不一定,原因為在 Google 試算表有提供另一個同類型的函數 query ,其使用限制更少,功能更強,有興趣朋友可以參考我這篇文章。但 query 使用語法較為複雜,需下如 SQL 的語法才能使用,而且限制在Google 試算表才能用,因此 vlookup 還是一值得學習的函數。

沒有留言:

張貼留言