Skip to content

Excel 第4課:查找和引用公式

在 Excel 中,查找和引用公式可以説明我們快速查找資料,並從大量資料中提取出需要的資訊。本節課將介紹三個常用的查找和引用公式:VLOOKUP、HLOOKUP、XLOOKUP、INDEX、MATCH、INDIRECT、OFFSET。

4.1 VLOOKUP、HLOOKUP、XLOOKUP

VLOOKUP 函數可以在一個表格中查找一個值,並返回該值所在行的另一列中的資料。HLOOKUP 函數與 VLOOKUP 函數類似,不同的是它查找的是列而不是行。XLOOKUP 函數是 Office 365 新增的函數,它可以在一個表格中查找一個值,並返回該值所在行或列中的任意資料。

例如,假設我們有一個銷售資料表格,包含商品名稱、銷售日期、銷售額等資訊,我們可以使用 VLOOKUP 函數查找商品名稱所對應的銷售額。

商品名稱銷售日期銷售額
iPhone1/1/202210,000.00
iPad1/1/20228,000.00
AirPods2/1/20224,000.00
MacBook3/1/202220,000.00
AirPods3/1/202212,000.00
iPad4/1/20227,000.00
請先把以上數據複製和貼上 Excel

請輸以下公式:
=VLOOKUP(“iPhone”, A2:C7, 3, FALSE)

這個公式將在 A2:C7 的表格中查找 “iPhone”,並返回它所在行的第 3 列,即銷售額。

類似地,如果以上的例子中的數據於橫行和直例互相反轉,我們可以使用 HLOOKUP 函數做到相同效果。

請輸以下公式:
=HLOOKUP(“iPhone”, A1:G3, 3, FALSE)

如果我們使用的是 Office 365,我們可以使用 XLOOKUP 函數來達到 VLOOKUP 和 HLOOKUP 的功能。

請輸以下公式:
=XLOOKUP(“iPhone”, A2:A7, C2:C7)

XLOOKUP 函數比 VLOOKUP 和 HLOOKUP 更加靈活,當在資料表格中間新增一行時,VLOOKUP 和 HLOOKUP 函數需要更新 <要傳回值的欄位號> 的數值,而XLOOKUP 函數會自動更新傳回值的範圍。

XLOOKUP 函數可以指定「查找失敗時的回傳值」,以下公式是當查找失敗時,回傳「Item not found」。

請輸以下公式:
=XLOOKUP(“Apple TV”, A2:A7, C2:C7, “Item not found”)

同時,XLOOKUP 函數可以一次查找多列。

請輸以下公式:
=XLOOKUP(“iPad”&”-“&VALUE(“4/1/2022″), A2:A7&”-“&B2:B7,C2:C7)

以上公式可以查找同時達成「商品名稱 = iPad」和 「銷售日期 = 4/1/2022」的銷售額。

而且,XLOOKUP 函數可以一次查找行和列。

請輸以下公式:
=XLOOKUP(“iPhone”, A2:A7, XLOOKUP(“銷售日期”, B1:C1, B2:C7))
=XLOOKUP(“iPhone”, A2:A7, XLOOKUP(“銷售額”, B1:C1, B2:C7))

以上公式使用 2 個 XLOOKUP 函數的組合,造成同時查找行和列的效果。

4.2 INDEX、MATCH

INDEX 函數可以返回一個表格中指定位置的數值,MATCH 函數可以查找一個表格中的值,並返回它所在的位置。

我們沿用以上的銷售資料表格作例子,例子中包含商品名稱、銷售日期、銷售額等資訊,我們可以使用 INDEX 和 MATCH 函數來查找某一商品在某一日期的銷售額。

請輸以下公式:
=INDEX(C2:C7, MATCH(“iPhone”, A2:A7, 0))

這個公式將在 A1:C7 的表格中查找 “iPhone” ,並返回它所在位置的銷售額。

INDEX 和 MATCH 函數的組合有著與 XLOOKUP 函數同樣的靈活性,當在資料表格中間新增一行時,INDEX 函數會自動更新傳回值的範圍。

4.3 小結

本節課介紹了 Excel 中常用的查找和引用公式:VLOOKUP、HLOOKUP、XLOOKUP、INDEX、MATCH,以及如何使用它們來快速查找資料,並從大量資料中提取出需要的資訊。這些公式可以説明我們更好地利用 Excel 進行資料分析和決策。

Leave a Reply

Your email address will not be published. Required fields are marked *

en_USEnglish