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

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

4.1 VLOOKUP、HLOOKUP、XLOOKUP

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

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

請輸以下公式：
=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 進行資料分析和決策。