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

延續上一篇的 vlookup 教學文章,藉由先給定參照值,再於指定範圍內尋找符合條件的資料並傳回指定「欄位」的對應數值,藉著介紹的 hlookup 也大同小異,但找到符合條件的資料後卻是傳回指定「列」的對應數值。

由於大部分資料庫匯出的格式,欄位代表的是變數,列資料代表的是樣本(或筆數),以至於絕大部份時候會用到的是 vlookup ,因為我們希望回傳的通常是同一筆樣本在不同變數的數值,這樣就能夠在不同的資料庫(例如客服中心電話紀錄 vs 銷售紀錄)根據相同的 key 值(例如客戶編號),建立一個新的資料集或摘要表來使用,但有些時候 hlookup 也是可以派得上用場,像是典型的實驗設計表格。

範例表格如下,這是一個飲料好感度盲測研究的實驗資料。

假如我們想把指定的資料填入一組如下方的特定清單,該怎麼做呢?

當然就是要透過 hlookup 囉!

如同 vlookup , hlookup 也擁有 4 個參數,唯一的差別是傳回值的參數,在 vlookup 中是「Col_index_num」,可指定要傳回數值的垂直範圍,在 hlookup 中則是「Row_index_num」,用來指定要傳回數值的水平範圍。

Lookup_value

想要指定的參照值在此設定,以本例而言,是各廠牌的飲料名稱,先選擇摘要工作表中的 A1:A10 範圍作為參照值。

Table_array

接著指定要被比對的資料範圍在此設定,至少需要包含兩列(Row),一列用來和參照值比對,另一列則是提供要回傳的資料。以本例而言,選擇 A1:K5 的範圍。

Row_index_num

針對被比對的資料範圍(根據 Table_array 設定的範圍),指定要回傳的值,指定的方式是選擇所要回傳的「第幾列」的資料,順序由上至下。以本例而言,希望回傳盲測組 C 的評分,也就是第 4 列,填入數字 4。

Range_lookup

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

假如真的想要採用「大略比對」,這裡的 excel 討論串中有一些建議可以幫助你傳回正確的值。

結果如下,接著把公式往下拓展到每一格就能得到對應的值。

往下複製公式的時候有一個小技巧,在撰寫 VBA 時經常會用到,就是當所要指定的範圍或儲存格是固定時,為了不讓 Excel 自動 +1 (例如第一格 A4 ,往下複製時 Excel 公式自動變為 A5 ),你可以在行/列或格子編號前加上符號「$」來固定選擇範圍。

例如 $ A1 表示固定在 A 欄當中,但是往下複製時還是會出現 A2 、 A3 ….,另一方面 $ A $ 1 則表示固定選擇為「 A1 」儲存格,往下複製時該位置永遠只有 A1 。

範例如下:

基本上, vlookup 和 hlookup 只需要學一個就可以了,最常用到的當然還是 vlookup ,但假如偶爾有使用到「列參照」的需求,那其實還有一個小撇步,那就是運用 transpose 函數,將行列顛倒之後再用 vlookup 來執行 hlookup 的工作。

聽起來很複雜? 不,一點也不。

因為你甚至不需要會 transpose 函數,記得複製貼上時會出現的小選單嗎? 其中就包含了自動執行 transpose 再貼上的選項。

範例如下:

精選閱讀:

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

* 服務科學課程回顧: 應用商業分析(APPLIED BUSINESS ANALYTICS)

* 統計R語言實作筆記系列 – 2D視覺化進階 GGPLOT()的基本架構(一)

* CROSS TABLE:卡方分配與卡方檢定

 

(Visited 28,904 times, 117 visits today)

Wendell.Huang

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

One Comment

發表迴響

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