Excel 資料分析 - 查詢函式

---

---

您可以使用 Excel 函式來：

• 在資料範圍內查詢值 - VLOOKUP 和 HLOOKUP
• 從表格或區域中獲取值或值的引用 - INDEX
• 獲取指定項在單元格區域中的相對位置 - MATCH

您還可以組合這些函式，根據您的輸入獲得所需的結果。

使用 VLOOKUP 函式

VLOOKUP 函式的語法為

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

其中

• lookup_value − 是您要查詢的值。Lookup_value 可以是值或單元格引用。Lookup_value 必須位於您在 table_array 中指定的單元格區域的第一列中

• table_array − 是 VLOOKUP 將在其中查詢 lookup_value 和返回值的單元格區域。table_array 必須包含

  • 第一列中的 lookup_value，以及

  • 您要查詢的返回值

    注意 − 包含 lookup_value 的第一列可以按升序排序，也可以不排序。但是，結果將基於此列的順序。

• col_index_num − 是 table_array 中包含返回值的列號。數字從 table-array 最左列的 1 開始

• range_lookup − 是一個可選的邏輯值，指定您希望 VLOOKUP 查詢精確匹配還是近似匹配。range_lookup 可以是

  • 省略，在這種情況下，它被假定為 TRUE，並且 VLOOKUP 嘗試查詢近似匹配

  • TRUE，在這種情況下，VLOOKUP 嘗試查詢近似匹配。換句話說，如果找不到精確匹配，則返回小於 lookup_value 的下一個最大值

  • FALSE，在這種情況下，VLOOKUP 嘗試查詢精確匹配

  • 1，在這種情況下，它被假定為 TRUE，並且 VLOOKUP 嘗試查詢近似匹配

  • 0，在這種情況下，它被假定為 FALSE，並且 VLOOKUP 嘗試查詢精確匹配

注意 − 如果 range_lookup 被省略或為 TRUE 或 1，則只有當 table_array 中的第一列按升序排序時，VLOOKUP 才能正常工作。否則，可能會導致錯誤的值。在這種情況下，請為 range_lookup 使用 FALSE。

使用 range_lookup 為 TRUE 的 VLOOKUP 函式

考慮一個學生成績列表。您可以使用 VLOOKUP 從包含分數區間和透過類別的一個數組中獲取相應的等級。

table_array −

請注意，用於獲取等級的第一列分數按升序排序。因此，使用 TRUE 作為 range_lookup 引數，您可以獲得近似匹配，這正是需要的。

將此陣列命名為Grades。

建議以這種方式命名陣列，這樣您就不需要記住單元格範圍。現在，您可以按如下方式查詢您擁有的分數列表的等級：

如您所見，

• col_index_num − 表示 table_array 中返回值的列為 2

• range_lookup 為 TRUE

  • table_array 等級中包含查詢值的第 1 列按升序排列。因此，結果將是正確的。

  • 您還可以獲得近似匹配的返回值。即 VLOOKUP 計算如下：

您將獲得以下結果：

使用 range_lookup 為 FALSE 的 VLOOKUP 函式

考慮一個包含產品 ID 和每個產品的價格的產品列表。每當推出新產品時，產品 ID 和價格都將新增到列表的末尾。這意味著產品 ID 不需要按升序排列。產品列表可能如下所示：

table_array −

將此陣列命名為 ProductInfo。

您可以使用 VLOOKUP 函式獲取給定產品 ID 的產品價格，因為產品 ID 在第一列中。價格在第 3 列中，因此 col_index_num 應為 3。

• 使用 range_lookup 為 TRUE 的 VLOOKUP 函式
• 使用 range_lookup 為 FALSE 的 VLOOKUP 函式

ProductInfo 陣列的正確答案是 171.65。您可以檢查結果。

您觀察到您得到了：

• 當 range_lookup 為 FALSE 時得到正確的結果，以及
• 當 range_lookup 為 TRUE 時得到錯誤的結果。

這是因為 ProductInfo 陣列中的第一列未按升序排序。因此，請記住，當資料未排序時，請使用 FALSE。

使用 HLOOKUP 函式

如果資料位於行而不是列中，則可以使用HLOOKUP 函式。

示例

讓我們以產品資訊為例。假設陣列如下所示：

• 將此陣列命名為 ProductRange。您可以使用 HLOOKUP 函式查詢給定產品 ID 的產品價格。

HLOOKUP 函式的語法為

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

其中

• lookup_value − 是要在表格的第一行中查詢的值

• table_array − 是查詢資料的表格

• row_index_num − 是 table_array 中將返回匹配值的行的行號

• range_lookup − 是一個邏輯值，指定您希望 HLOOKUP 查詢精確匹配還是近似匹配

• range_lookup 可以是

  • 省略，在這種情況下，它被假定為 TRUE，並且 HLOOKUP 嘗試查詢近似匹配

  • TRUE，在這種情況下，HLOOKUP 嘗試查詢近似匹配。換句話說，如果找不到精確匹配，則返回小於 lookup_value 的下一個最大值

  • FALSE，在這種情況下，HLOOKUP 嘗試查詢精確匹配

  • 1，在這種情況下，它被假定為 TRUE，並且 HLOOKUP 嘗試查詢近似匹配

  • 0，在這種情況下，它被假定為 FALSE，並且 HLOOKUP 嘗試查詢精確匹配

注意 − 如果 range_lookup 被省略或為 TRUE 或 1，則只有當 table_array 中的第一列按升序排序時，HLOOKUP 才能正常工作。否則，可能會導致錯誤的值。在這種情況下，請為 range_lookup 使用 FALSE。

使用 range_lookup 為 FALSE 的 HLOOKUP 函式

您可以使用 HLOOKUP 函式獲取給定產品 ID 的產品價格，因為產品 ID 在第一行中。價格在第 3 行中，因此 row_index_num 應為 3。

• 使用 range_lookup 為 TRUE 的 HLOOKUP 函式。
• 使用 range_lookup 為 FALSE 的 HLOOKUP 函式。

ProductRange 陣列的正確答案是 171.65。您可以檢查結果。

您觀察到，與 VLOOKUP 的情況一樣，您得到了

• 當 range_lookup 為 FALSE 時得到正確的結果，以及

• 當 range_lookup 為 TRUE 時得到錯誤的結果。

這是因為 ProductRange 陣列中的第一行未按升序排序。因此，請記住，當資料未排序時，請使用 FALSE。

使用 range_lookup 為 TRUE 的 HLOOKUP 函式

考慮在 VLOOKUP 中使用的學生成績示例。假設您將資料儲存在行而不是列中，如下表所示：

table_array −

將此陣列命名為 GradesRange。

請注意，用於獲取等級的第一行分數按升序排序。因此，使用 HLOOKUP 並將 TRUE 作為 range_lookup 引數，您可以獲得近似匹配的等級，這正是需要的。

如您所見，

• row_index_num − 表示 table_array 中返回值的列為 2

• range_lookup 為 TRUE

  • table_array 等級中包含查詢值的第 1 列按升序排列。因此，結果將是正確的。

  • 您還可以獲得近似匹配的返回值。即 HLOOKUP 計算如下：

您將獲得以下結果：

使用 INDEX 函式

當您有一個數據陣列時，您可以透過指定該值在陣列中的行號和列號來檢索陣列中的值。

考慮以下銷售資料，其中您將找到銷售人員在每個北部、南部、東部和西部地區的銷售額。

• 將陣列命名為 SalesData。

使用 INDEX 函式，您可以找到：

• 某個特定區域中任何銷售人員的銷售額。
• 所有銷售人員在某個區域中的總銷售額。
• 某個銷售人員在所有區域中的總銷售額。

您將獲得以下結果：

假設您不知道銷售人員的行號和區域的列號。然後，在使用索引函式檢索值之前，您需要先找到行號和列號。

您可以使用下一節中解釋的 MATCH 函式來執行此操作。

使用 MATCH 函式

如果您需要項在區域中的位置，則可以使用 MATCH 函式。您可以按如下方式組合 MATCH 和 INDEX 函式：

您將獲得以下結果：