Excel 交互參照必學神器, 用 lookup 系列函數一鍵搞定資料比對 : vlookup篇

有一陣子沒有寫部落格了,最近遇見很多年輕的朋友,發現許多人等到進入職場以後,才發現原來職務說明中所謂「具備運用 Excel 的能力」,並不是指能夠打打加減乘除而已,只有學會了運用 Excel 的函數,才能在資料分析時達到事倍功半的效果,也才算是真正會用 Excel。

正因如此,就來分享一下 Excel 的使用技巧吧。

Excel 當中主要的函數列表,可以參考 Microsoft 提供的清單(按此)。在官方的分類底下,有一類就叫做「查閱與參照函數」,相同的分類也可以在各版的 Excel 函數說明中找到,可惜對於 Excel 使用不深的人,比起「統計」、「財務」等類別,這是一個經常會被遺忘的函數集,不過若以泛用性而言,參照函數或許才是各 Excel 課程首先必須著墨的部分。

不得不說,它真的太重要啦,在企業中 Excel 的使用量很大,就算自己不用,你的客戶或供應商八成也都用得很兇,而且 Excel 的應用範圍也很廣,幾乎所有部門都能用得到,各種專案合作中只要看到別人該用卻不用對應的函數,只靠手動製作資料摘要,我就會忍不住內心嘆氣+翻白眼,因為這實在是太浪費人力了,畢竟好人才的時間成本可是很貴的啊!!

各位看官,介紹參照函數必學第一名,就是大名鼎鼎的「vlookup」函數。

假如你有以下的狀況…:

  • 必須同時管理許多個部份資料有重疊的工作表
  • 沒有任何單一工作表包含所有要用到的資料欄位
  • 工作表的資料筆數很多,不太可能花時間人工一一對照

那麼再不學 vlookup ,就真的太遜啦!

本文用氣象局的觀測資料為例,範例包含兩個工作表各自包含「酸雨PH值」及「雨量」的資料,其中「地點(鄉鎮名)」是兩個資料集的共同點,如下圖所示:


這裡另外加入一個獨立的工作表,包含了鄉鎮名稱資料方便檢視結果。

假如我們想要建立一個包含鄉鎮、雨量以及酸雨PH值的資料集(如 test 工作表的範例),那麼該怎麼做?

如果不利用 Excel 函數,那麼恐怕我們就得一筆一筆資料尋找,然後複製貼上了,假若只有幾個地點那倒無所謂,但是面對數百個鄉鎮地點,顯然最好的辦法不是手動。藉由 vlookup 函數,我們可以設定一組參照值,然後讓公式根據參照值,在給定的工作表每一列當中尋找符合參照值的資料,回傳設定好的欄位所對應的數值。

然後呢? 點兩下複製公式到所有格子就完成了! 就這麼簡單。

vlookup 共有幾個參數要設定,一一說明如下:

lookup_value

想要指定的參照值在此設定,以我們的例子來說,就是「鄉鎮地名」,藉由這個參照值,我們可以分別在「雨量」和「PH值」的資料當中,尋找具有對應地名的資料。這裡稍微偷懶,直接選擇整個 A 欄,不過不影響結果就是了。

Table_array

被比對的資料範圍在此設定,至少需要有兩欄(Column)。例如稍後展示的例子中,在「PH值」資料表中就選定了「I欄、J欄、K欄」三個欄位的資料。

Col_index_num

針對被比對的資料範圍(在前面的 Table_array 當中設定),指定要回傳的值。例如稍後展示的例子中,根據上述「Table_array」參數在「PH值」資料表中已經選定的「I欄、J欄、K欄」,選擇要回傳的欄位資料,本例選擇「K欄」,由於公式是左邊起算,因此填上「3」。


Range_lookup

這是一個邏輯判斷式,填入「TRUE」or「FALSE」以指定選擇的標準。若填上 FALSE ,則參照值和被比對的值必須完全一樣,若為 TRUE,則會採用大略比對(但是這是站在程式的角度來看,跟你以為的大略符合常常有很大差異,經驗上非常不建議採用 TRUE )。

把公式往下複製到每一列,結果就會如下圖所示。PH 值只要比照辦理,調整參數值,馬上就可以得到結果。

範例中出現許多「N/A」值,表示在原始參照資料中並沒有這筆資料,或者在被比對的資料中沒有對應的項目。

事實上, test 工作表中的鄉鎮清單是來自另外的資料來源。之所以要刻意讓 N/A 值出現,正是要補充 vlookup 函數除了自動填入資料之外,同時也是用來查勘錯誤的一大利器。

例如,配合「 iferror 」函數以及「條件格式化」技巧,我們還可以得到額外視覺化標記的效果:

vlookup - 10

vlookup - 11

附帶一提的是,其實範例的資料並不適合用 vlookup…,假如你有稍微練習就會發現,由於參照資料和被比對的資料的項目都不是唯一(例如,同時有多個" 新竹 “的存在),這時候 vlookup 只能取到其中一個值而已,當然不合用,但是在多個資料集共用一組各自唯一的 key 值時,這個函數就能做報表時幫上大忙。

這個情況,也是許多人會粗心忽略的地方,在檢查別人的公式時可得特別小心。再補充一點,這裡的「 v 」指的是 vertical (垂直) 的意思,你可以從實作中品味出命名的邏輯。

你可能好奇,既然有 vlookup ,有沒有 hlookup (horizontal,水平)呢?

答案是有的, lookup 參照函數系列共有三個函數: 「lookup」、「vlookup」、「hlookup」。 由於常見資料集格式的關係,導致大多數人最常用到的是「vlookup」。至於其他兩個函數的使用範例,就請繼續參考之後的續集囉。

精選閱讀

* EXCEL VBA – 讓 USER FORM 右上角出現放大縮小按鈕的方法

* 統計R語言實作筆記系列 – R簡介與資料輸入

* EXCEL 交互參照必學神器, 用 LOOKUP 系列函數一鍵搞定資料比對 : HLOOKUP篇

* 數大有時不美的統計性質

* 廣義線性模型觀點:統計迴歸分析(REGRESSION)的基本原理與結構

 

(Visited 78,168 times, 553 visits today)

Wendell.Huang

科技公司嫌棄太活潑,消費品牌挑剔太沉悶..., 經常必須解釋自己在學什麼, 不小心就摔破對方眼鏡的跨領域玩家。

2 Comments

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *