查表函數
查表函數
查表函數有三種：
水平查表
HLOOKUP
垂直查表
VLOOKUP
查表
LOOKUP
VLOOKUP垂直查表
VLOOKUP(查表依據,表格,第幾欄,
是否不用找到完全相同值
)
=Vlookup(lookup_vaule, table_array, col_index_num,
[range_lookup])
在一表格的最左欄中，尋找含查表依據的欄位，並傳回同一列中第幾
欄所指定之儲存格內容
表格是要在其中進行找尋資料的陣列範圍，且必須按其第一欄之內容
遞增排序。
是否不用找到完全相同值
為一邏輯值，為
TRUE（或省略）時，如果
找不到完全符合的值，會找出僅次於查表依據的值。當此引數值為
FALSE時，必須找尋完全符合的值，如果找不到，則傳回錯誤值
#N/A。
假定，員工之業績獎金係依其業績高低，給予不同之比例：
安排此一表格時，標題之文字內容並無作用，重點為代表業績
及獎金比例之數字，第一個
0很重要，很多使用者直接於0的位
置上輸入
300,000，將會使業績未滿300,000者，找不到可用
之獎金比例，而顯示錯誤值
#N/A。此外，務必記得要依第一
欄之業績內容遞增排序。
假定，各員工之基本薪及業績資料為：參見
【
VLOOKUP1
】
於
E欄，擬依D欄之業績計算其業績獎金。首先，於
E13處可使用
=VLOOKUP(D13,$A$3:$B$9,2,TRUE)
依
D欄之業績（查表依據），於A3:B9（表格）中找
出適當（第
2欄）之獎金百分比
最後一個引數為何要使用
TRUE？
這是因為業績內容很少恰好等於
A3:A9的間距數字。將其安排為
TRUE（或省略）時，於A3:A9找不到完全符合D欄之業績值，
將找出僅次於查表依據的值。如：業績
1,025,000者，不可能會
給予與
1,500,000同列之1%為獎金比例，而是找到僅次於
1,025,000之1,000,000，而回應與1,000,000同列之0.8%為
其獎金比例。
此外，安排業績與其獎金比例之表格原範圍為
A3:B9，為了方便
向下抄給其它儲存格，應記得將其安排為
$A$3:$B$9。
於判斷查表所取得之獎金比例無誤後，將其乘上業績：
=VLOOKUP(D13,$A$3:$B$9,2,TRUE)*D13
即可算出業績獎金：
最後，將
C欄之基本薪加上E欄業績獎金，即可獲致F
欄之總所得：
前例之
VLOOKUP()中的最後一個引數使用TRUE，如果找不到完
全符合的值，會找出僅次於查表依據的值。但，於下表中：
雖同樣以數字性質之編號進行找尋，就不可以於找不到完全符合的編號
值，即以編號較小的另一筆記錄內容來替代。故應將
VLOOKUP()中的最
後一個引數，改為使用
FALSE，必須要找尋完全符合的值，如果找不到，
則傳回錯誤值
#N/A。
假定，要利用使用者所輸入之員工編號，傳回如下示之表格內
容：
其處理步驟為：參見【
VLOOKUP2
】
1.
安排妥表格外觀
2.
於
C13輸入一已存在之員工編號（如：1316）
3.
於
E13輸入
=VLOOKUP($C$13,$A$2:$H$10,2,FALSE)
公式，可找出該編號所對應之員工姓名
前兩個引數，使用含
$之絕對參照，係因此公式仍要
抄給其它儲存格使用。最後一個引數，使用
FALSE，
表一定要找到完全相同之員工編號；否則，即顯示
#N/A之錯誤，而不是找一個編號較低者來替代。
4.
按
鈕，記下
E13之內容
5.
按住
Ctrl鍵，選取選取C14:C17與E14:E15儲存格
6.
按
鈕，貼上所記下之內容，可獲致
7.
將
C14:C15、E14:E15與C16:C17等儲存格之公
式內容的第三個引數，由
2分別改為所對應之欄數。
如：
C14
=VLOOKUP($C$13,$A$2:$H$10,3,FALSE)
E14
=VLOOKUP($C$13,$A$2:$H$10,4,FALSE)
C15
=VLOOKUP($C$13,$A$2:$H$10,5,FALSE)
E15
=VLOOKUP($C$13,$A$2:$H$10,6,FALSE)
C16
=VLOOKUP($C$13,$A$2:$H$10,7,FALSE)
C17
=VLOOKUP($C$13,$A$2:$H$10,8,FALSE)
可獲致
8.
將
E15處安排為民國年代之日期格式，即可大功告成
茲再舉一文字串之實例，假定，某公司之產品編號、品
名及單價，如下表之
A1:C8所示，建立表格時，必須按
A欄之編號遞增排序，但仍允許跳號。參見
【
VLOOKUP3
】
於交易發生時，為方便輸入資料，可於輸入產品編號
後，以
VLOOKUP()查得其品名及單價。因為，不可能
會依編號順序發生交易，故下表並無必須按編號遞增排
序之要求，且允許重複出現：
要利用
VLOOKUP()依編號查表取其品名及單價，可
先於
C12輸入
=VLOOKUP($B12,$A$2:$C$8,2,FALSE)
可取得品名：
必須要找到完全相同之編號，最後一個引數要安排為
FALSE（或０）。將其抄給D12後，可獲得一完全相
同之公式，將其第三個引數改為
3：
=VLOOKUP($B12,$A$2:$C$8,3,FALSE)
即可獲得其單價：
將
C12:D12抄給C13:D19，即可取得各筆交易之品
名及單價：
剩下來之工作，僅須輸入各筆交易之數量，即可以單
價乘以數量，求得其金額：
往後，若再有新交易發生，只須繼續向下進行輸入即
可，並不用再複製公式，
Excel會自動進行必要之公
式的複製。例如，輸入完日期與編號後，即可自動取
得品名及單價：
續再輸入數量，即可自動算出金額：
HLOOKUP水平查表
HLOOKUP(查表依據,表格,第幾列,
是否不用找到完全相同值
)
公式：
=Hlookup(lookup_vaule, table_array,
row_index_num, [range_lookup])
Range_lookup>0(false)或1(true)
在一表格的第一列中尋找含查表依據的欄位，並傳回同一欄中
第幾列所指定之儲存格內容。
本函數之相關規定，同
VLOOKUP()，只差其查表係以水平方
式進行而已。
HLOOKUP有三個參數
第一個參數為想要查詢的資料
第二個參數為查詢對照表的位置範圍，通常以絕對座標表示
第三個參數為對照表在查詢表中的第幾列，“
2”代表第二列。
HLOOKUP水平查表
HLOOKUP有三個參數
第一個參數為想要查詢的資料
第二個參數為查詢對照表的位置範圍，通常以
絕對座標表示
第三個參數為對照表在查詢表中的第幾列，“
2”
代表第二列。
下表將西元年代除以
12後之餘數（使用MOD()函
數），以遞增方式排列，並將其所對應之中國生肖匯
集在一起
即可於
B5輸入任一西元年代後，以
=HLOOKUP(MOD(B5,12),B1:M3,3)
利用餘數來以
HLOOKUP()來查表取得其生肖
用
HLOOKUP水平查表
垂直查表
VLOOKUP(查表依據,表格,第幾欄,
是否不用找到完全相同
值
)
=Hlookup(lookup_vaule, table_array,
col_index_num, [range_lookup])
在一表格的最左欄中，尋找含查表依據的欄位，並傳回同一
列中第幾欄所指定之儲存格內容
表格是要在其中進行找尋資料的陣列範圍，且必須按其第一
欄之內容遞增排序。
是否不用找到完全相同值
為一邏輯值，為
TRUE（或省略）
時，如果找不到完全符合的值，會找出僅次於查表依據的
值。當此引數值為
FALSE時，必須找尋完全符合的值，如果
找不到，則傳回錯誤值
#N/A。
不用找到完全相同值之實例
假定，員工之業績獎金係依其業績高低，給予不同之比
例。
茲將其對照表安排於下表之
A3:B9，安排此一表格
時，標題之文字內容並無作用，重點為代表業績及獎金
比例之數字，第一個
0很重要，很多使用者直接於0的
位置上輸入
300,000，將會使業績未滿300,000者，
找不到可用之獎金比例，而顯示錯誤值
#N/A。此外，
務必記得要依第一欄之業績內容遞增排序。
假定，各員工之基本薪及業績資料為：參見
【
VLOOKUP1
】
於
E欄，擬依D欄之業績計算其業績獎金。首先，於
E13處可使用
=VLOOKUP(D13,$A$3:$B$9,2,TRUE)
依
D欄之業績（查表依據），於A3:B9（表格）中找出
適當（第
2欄）之獎金百分比
最後一個引數為何要使用
TRUE？這是因為業績內容很
少恰好等於
A3:A9的間距數字。將其安排為TRUE（或
省略）時，於
A3:A9找不到完全符合D欄之業績值，將
找出僅次於查表依據的值。
如：業績
1,025,000者，不可能會給予與1,500,000同列之
1%為獎金比例，而是找到僅次於1,025,000之
1,000,000，而回應與1,000,000同列之0.8%為其獎金比
例。
此外，安排業績與其獎金比例之表格原範圍為
A3:B9，為了
方便向下抄給其它儲存格，應記得將其安排為
$A$3:$B$9。
於判斷查表所取得之獎金比例無誤之後，將其乘上業績：
=VLOOKUP(D13,$A$3:$B$9,2,TRUE)*D13
即可算出業績獎金：
最後，將
C欄之基本薪加上E欄業績獎金，即可獲致F欄之總
所得：
馬上練習
續前例，假定所得稅率為：
試依查表取得適當稅率計算所得稅，並計算扣除所得
稅後之淨所得：參見【
VLOOK2
】
必須找到完全相同值之實例
前例之
VLOOKUP()中的最後一個引數使用TRUE，如
果找不到完全符合的值，會找出僅次於查表依據的值。
但，於下表中，雖同樣以數字性質之編號進行找尋，就
不可以於找不到完全符合的編號值，即以編號較小的另
一筆記錄內容來替代。故應將
VLOOKUP()中的最後一
個引數，改為使用
FALSE，必須要找尋完全符合的值，
如果找不到，則傳回錯誤值
#N/A。參見
【
VLOOK3
】
假定，要利用使用者所輸入之員工編號，傳回如下示之
表格內容：
其處理步驟為：
1.
安排妥表格外觀
其中，
C3:E3與C4:E4係分別以「格式(O)/儲存格
(E)…」，將其設定為合併儲存格
2.
將整個表格內容，設定為靠左對齊
3.
於
C13輸入一已存在之員工編號，如：1316
4.
於
E13輸入
=VLOOKUP($C$13,$A$2:$H$10,2,FALSE)公
式，可找出該編號所對應之員工姓名（第
2欄）
前兩個引數，使用含
$之絕對參照，係因此公式仍要抄
給其它儲存格使用。最後一個引數，使用
FALSE，表
一定要找到完全相同之員工編號；否則，即顯示
#N/A
之錯誤，而不是找一個編號較低者來替代。
5.
按
鈕，記下
E13之內容
6.
按住
Ctrl鍵，選取C14:C15、E14:E15與C16:C17
等儲存格
7.
以「編輯
(E)/選擇性貼上(S)…」，選擇只貼上「公式
(F)」
8.
按［確定］鈕，可獲致
9.
將
C14:C15、E14:E15與C16:C17等儲存格之公式
內容的第三個引數，由
2分別改為所對應之欄數。如：
C14=VLOOKUP($C$13,$A$2:$H$10,3,FALSE)
E14=VLOOKUP($C$13,$A$2:$H$10,4,FALSE)
C15=VLOOKUP($C$13,$A$2:$H$10,5,FALSE)
E15=VLOOKUP($C$13,$A$2:$H$10,6,FALSE)
C16=VLOOKUP($C$13,$A$2:$H$10,7,FALSE)
C17=VLOOKUP($C$13,$A$2:$H$10,8,FALSE)
可獲致
10.
將
E15處安排為民國年代之日期格式，即可大功告成
往後，於
C13處輸入員工編號，即可取得其相關之所
有資料內容：
但若輸入一個不存在之員工編號（如：
1215），即顯
示
#N/A之錯誤，而不是找一個編號較低者（1208）
來替代：
但若輸入員工編號（如：
1203），即顯示該員工資料：
將
#N/A改為"找不到"
若要將
#N/A改為"找不到"，可使用如
=IF(ISNA(VLOOKUP($C$13,$A$2:$H$10,
2,FALSE)),"找不到",VLOOKUP($C$13,$A$2:
$H$10,2,FALSE))
之公式來判斷。
當員工編號不存在，即將
#N/A改為"找不到"。將所
有使用
VLOOKUP()之儲存格，均改類似之公式後，
可獲致：參見【
VLOOK4
】
設定僅能輸入編號進行查詢
由於前例係於
C13處輸入員工編號進行查詢，但使用
者仍可能於
C13以外的其它儲存格輸入內容，如此，
難保不會破壞查詢表中之公式內容。因為，其公式確
實複雜，要重打得浪費不少時間！
可以下列步驟，將其設定為僅能於
C13輸入編號進行
查詢，於其它位置輸入任何資料（或編輯
/刪除）均不
被允許
1.
停於
C13，執行「格式(O)/儲存格(E)…」，選取
『保護』標籤
2.
取消「鎖定
(L)」與「隱藏(I)」
3.
按［確定］鈕
4.
執行「工具
(T)/保護(P)/保護工
作表
(P)…」
5.
輸入密碼，續按
(確定)鈕
6.
再輸入一次完全相同之密碼
7.
按［確定］鈕，即可完成設定
如此，僅能於
C13輸入編號進行查
詢，於其它位置輸入任何資料、編輯
或刪除儲存格內容，均將獲致錯誤訊
息
文字串之實例
假定，某公司之產品編號、品名及單價，如下表之
A1:C8所示。建立表格時，必須按A欄之編號遞增排
序，但仍允許跳號。參見【
VLOOK6
】
於交易發生時，為方便輸入資料，可於輸入產品編號
後，以
VLOOKUP()查得其品名及單價。因為，不可
能會依編號順序發生交易，故下表並無必須按編號遞
增排序之要求，且允許重複出現：
要利用
VLOOKUP()依編號查表取其品名及單價，可
先於
C12輸入
=VLOOKUP($B12,$A$2:$C$8,2,FALSE)
可取得品名
由於這也是一個必須要找到完全相同之編號的例子，
故最後一個引數要安排為
FALSE。將其抄給D12
後，可獲得一完全相同之公式，將其第三個引數改為
3：
=VLOOKUP($B12,$A$2:$C$8,3,FALSE)
即可獲得其單價
將
C12:D12抄給C13:D20，即可取得各筆交易之
品名及單價
剩下來之工作，僅須輸入各筆交易之數量，即可以單
價乘以數量，求得其金額
往後，若再有新交易發生，只須繼續向下進行輸入即
可，並不用再複製公式，
Excel會自動進行必要之公
式的複製。例如，輸入完日期與編號後，即可自動取
得品名及單價
續再輸入數量，即可自動算出金額
LOOKUP查表-向量型
LOOKUP(查表依據,查表向量,結果向量)
所使用的兩個向量，均為單列或單欄的陣列。
本類型之函數，會在查表向量中找尋查表依據之內容，然後移到
另一個結果向量中的同一個位置上，傳回該儲存格的內容。
但應注意：
兩向量之
儲存格個數應一致
查表向量之
內容應事先遞增排序
如果於查表向量中無法找到查表依據之內容，將取用較小的
一個值來替代
如果查表依據之內容小於整個查表向量之所有值，將回應
#N/A之錯誤值
如，將成績高低分為下列幾組
將其內容安排於下表之
A1:B5位置。如此，A2:A5
即可當查表向量；
B2:B5即可當結果向量。參見
【
LOOKUP
】
假定，要將成績內容，於其備註欄上填入適當之組別
文字，
C9處之公式可為：
=LOOKUP(C9,$A$2:$A$5,$B$2:$B$5)
由於其結果向量僅能為單列或單欄的陣列。
故若假定要使用學號來找出姓名、成績及備註欄內
容。就得標定不同之結果向量，如下表中
G10:G12
之內容將分別為：
G10 =LOOKUP(G9,A9:A17,B9:B17)
G11 =LOOKUP(G9,A9:A17,C9:C17)
G12 =LOOKUP(G9,A9:A17,D9:D17)
分別使用三組不同的結果向量，才可找到適當之資料
內容
LOOKUP
查表
-陣列型
LOOKUP(查表依據,陣列)
會在陣列的第一列（或第一欄），搜尋指定的
查表依據，然後傳回其最後一列（或欄）的同
一個位置上之儲存格內容。
所以，同上例之要求，要使用此一類型之
LOOKUP()函數，依成績高低，於其備註欄上
填入適當之組別文字，
C9處之公式將改為：
=LOOKUP(B9,$A$2:$B$5)
將原分為兩個向量之內容，組合成單一陣列即
可。參見【
LOOKUP2
】
但由於此類型之
LOOKUP()函數，不管陣列之欄列
數多寡，將永遠傳回最後一列（或欄）的對應內容
故若要於
A9:D17表中，依學號找出姓名、成績及備
註欄內容。就得標定不同之三組陣列，分別讓所要的
內容安排於最後一欄才可
如下表中
G10:G12之內容將分別為：
G10 =LOOKUP(G9,A9:B17)
G11 =LOOKUP(G9,A9:C17)
G12 =LOOKUP(G9,A9:D17)
分別使用三組不同欄數之陣列，每個陣列均讓所要找
出之內容安排於最後一欄，才可找到適當之資料內容
製作個人成績單
VLOOKUP 函數的用法
個人成績單的製作
快速建立
VLOOKUP 函數公式
VLOOKUP 函數的用法
VLOOKUP
函數的功用就是在清單中的第一
欄尋找特定值
, 若找到時, 就傳回所找到的
那一列中某個欄位的值。
公式為：
個人成績單的製作
請您開啟範例檔案
Ch08-03 , 並切換到
個
人成績單
工作表：
個人成績單的製作
這是一張已經設計好的個人成績單
, 接著要
開始填入每位學生的成績。
1.
請您選取
C4, 然後按下
插入函數
鈕
, 開啟
插入函數
交談窗。
2.
在
插入函數
交談窗中
, 選取
檢視與參照
函數
類別的
VLOOKUP
函數
, 並按下
確定
鈕：
個人成績單的製作
個人成績單的製作
個人成績單的製作
由於
C3 尚未輸入任何資料, 因此會出現錯
誤值。請您在
C3 輸入某位學生的姓名, 看
看結果對不對：