[Excel]-職場使用頻率最高的VLOOKUP、HLOOKUP函數詳解

2021-12-14

885
0
OFFICE
2022-12-03

文、意如

任務1：
任務1-1：VLOOKUP用在什麼地方？
任務1-2：利用VLOOKUP函數查詢表格資訊

任務2：
任務2-1：HLOOKUP用在什麼地方？
任務2-2：利用HLOOKUP函數查詢表格資訊

任務1

任務1-1：VLOOKUP用在什麼地方？

我們先準備一張表，這個表上記錄著各學區分店的地址、電話。

圖1-工作表一、分店資訊表

接著另外一張表，存著學生報名表，如果我們想知道該分店的地址與電話，就
會需要去對照上面這張工作表一的＂分店資訊表＂來取得。

圖2-工作表二、學生報名表

假設資料有100筆，我們一個一個對，未免也太慢了，而且多少也有機會出現資料key錯的可能性，所以這時候就會需要仰賴VLOOKUP這個強大的函數來幫我們對資料了。

任務1-2：利用VLOOKUP函數查詢表格資訊

>> C2儲存格輸入=VLOOKUP（

>> 點選fx 進入函數引數提示區

>> 第一個引數 Lookup_value ，這裡要我們填的是你想要查詢的資訊是什麼？

我們現在要查詢的是B2儲存格（基隆學區的地址）所以，這裡填上B2（在第一格點一下B2）

>> 接著第二個引數 Table_array ，點選我們要查詢的表格，這時我們點選工作表一的整個表格

>> 第三個引數Col_index_num，填上我們要的資訊欄位在第幾欄，我們目前所需要的資料是地址（B欄）在第二欄，所以我們填上2。

>> 第四個引數的值只接受0（false）跟 1（true），為的是讓我們可以更精準地找到我們的資料，如果你想要找的資料是完全符合的值（例如：我們查詢的值是”基隆”，工作表一的表格也有相對應的值”基隆”，那麼就是完全符合我們要找的，這時就要輸入0（false）

那第四個引數的1（True）哪時候會用到呢？

比如說你想查詢的是成績 86分，而你的表格資料是0~60分，70~90分這一組，
這時候你就會需要用到1（True）了

因為等等我們的地址欄位是要往下複製，所以欄位址第2個引數Table_array，我們參考的表格A1：C11是固定不變的，所以必須加上固定符號$像是 $A$1：$C$11。

>> 我們可以先點下第2引數的位置反白後按下快速鍵F4
值=工作表1！$A$1：$C$11

>> 接著點選確定

>> 點選C2儲存格右下角往下拖曳至C9（完成複製）

接著我們點選公式、顯示公式可以看到工式往下的變化

VLOOKUP重點整理

1. 要查詢的值，往下複製時會自動遞增B2（基隆）B3（公館）B4、B5．．．
2. 如加上$會鎖住欄或列,所以要查詢的分店表格$A$1：$C$11是固定的。

3. 我們要的資訊欄位在查詢表格中的第幾欄，我們目前所需要的資料是地址（B欄）在第二欄，所以我們填上2。
4. 要找到完全符合的資料所以固定為“0”。

任務2

任務2-1：HLOOKUP用在什麼地方？

HLOOKUP與VLOOKUP差別就是在對照表，如果你的對照表是是垂直的那麼您就會需要用到VLOOKUP函數，相反的如果你的對照表是水平的那麼您需要用到的函數就是HLOOKUP，如下圖：

對照表
成績	60	75	85	95	100
等級	戊	丁	丙	乙	甲
獎學金	甲	500	1000	1500	2000

任務2-2：利用HLOOKUP函數查詢表格資訊（現在要查詢的是多少成績對應到可以領多少獎學金）

馬上來實作看看吧！

首先我們要用成績來查詢下面的對照表來看可以領多少獎學金以及等級為多少？

>> C2儲存格 = HLOOKUP（
>> fx
>> 輸入引數，用法跟前面vlooup一樣
第1個引數為搜尋值：B2
第2個引數為對照表（因為等等要往下複製，所以記得按下F4 加上固定符號 $ 鎖住欄列。
第3個引數為我們要查詢對照表的第幾列，獎學金在第3列，因此我們輸入3
第4個引數為 0，要搜尋完全符合的資料。

>> 最後按下確定，往下複製