《梦醒蝶飞：释放Excel函数与公式的力量》5.4 Match函数

5.4  Match函数

5.4.1  match函数的概念

MATCH函数是Excel中的一个查找和引用函数，它用于在数据表或数组中搜索指定项，并返回该项在数组中的相对位置。以下是MATCH函数的几个关键概念：

1)查找值（Lookup Value）

这是你想要在数据表或数组中查找的值。`MATCH`函数会搜索这个值，并尝试找到它在数组中的位置。

2)查找数组（Lookup Array）

这是包含可能匹配值的一行或一列。`MATCH`函数会在这片区域内搜索查找值。

3)匹配类型（Match Type）

0：精确匹配。这是默认选项，要求查找值与查找数组中的值完全相同。

1：小于等于查找值的最大值。如果查找值不在数组中，函数会返回最接近查找值且不大于查找值的最大值的位置。

-1：大于等于查找值的最小值。如果查找值不在数组中，函数会返回最接近查找值且不小于查找值的最小值的位置。

4)相对位置（Relative Position）

MATCH函数返回的是查找值在查找数组中的相对位置，而不是工作表中的绝对位置。位置是基于1的索引，即数组中的第一个元素的位置是1。

5)单列或单行数据（Single Column or Single Row Data）

MATCH函数通常用于单列或单行的数据查找。如果查找数组包含多列或多行，MATCH只会在一个方向上（行或列）搜索查找值。

6)性能和效率

MATCH函数在处理大型数据集时非常有效，特别是当需要频繁查找数据时。

7)错误处理

如果MATCH函数在查找数组中找不到查找值，并且匹配类型设置为0，它会返回错误值#N/A。

8)灵活性

MATCH函数可以与其他函数（如INDEX）结合使用，以实现更复杂的数据检索和分析任务。

理解这些概念对于有效使用MATCH函数至关重要，它们帮助你精确地控制查找过程，并确保从数据集中检索正确的信息。

5.4.2MATCH函数的基本语法

MATCH函数的基本语法如下：

MATCH(lookup_value, lookup_array, [match_type])

lookup_value：这是你要在`lookup_array`中查找的值。

lookup_array：这是包含可能的匹配值的一行或一列。

[match_type]：这是一个可选参数，用来指定匹配的类型：

  `0` 表示精确匹配。

  `1` 或省略，表示小于等于`lookup_value`的最大值。

  `-1` 表示大于等于`lookup_value`的最小值。

5.4.3MATCH函数的功能

MATCH函数用于在一行或一列中查找特定值，并返回该值在数组中的相对位置。它的功能包括：

1)确定一个值在数据集中的位置。假设我们有一个销售数据表，我们想要使用MATCH函数来确定一个特定产品在产品列表中的位置。

假设我们要查找“橙子”在列表中的位置。可以在单元格B1输入以下公式：=MATCH("橙子", A1:A5, 0)

理解公式

"橙子" 是我们要在查找数组中查找的值。

A1:A5 是包含可能匹配值的查找数组。

0 表示我们希望进行精确匹配。

2）可以跨工作表或工作簿进行查找。

使用MATCH函数在一个工作表中查找另一个工作表中的数据。

案例场景

假设我们有两个工作表：一个是“产品列表”，另一个是“销售记录”。我们想要在“销售记录”工作表中查找某个产品在“产品列表”工作表中的索引位置。

步骤1：创建工作表和数据

打开Excel，创建两个新的工作表，分别命名为“产品列表”和“销售记录”。

在“产品列表”工作表中，输入以下数据：

 

在“销售记录”工作表中使用MATCH函数

假设我们要查找“苹果”在“产品列表”工作表中的位置。

在“销售记录”工作表的C2单元格输入以下公式：

=MATCH(B2, 产品列表!$A$1:$A$5, 0)

这里B2是“销售记录”工作表中的产品名称单元格，产品列表!$A$1:$A$5是“产品列表”工作表中的产品名称区域。

理解公式

B2 是我们要查找的产品名称。

产品列表!$A$1:$A$5 是“产品列表”工作表中包含可能匹配值的查找数组。

0 表示我们希望进行精确匹配。

3）支持精确匹配和近似匹配。

5.4.4 MATCH函数的应用场景

1）数据分析：在数据分析中，MATCH可以用于根据特定条件查找数据点的位置。

案例场景

在一个公司的销售数据中，我们想要找出特定产品在特定季度的销售记录的位置。以下是创建这个案例的步骤和示例数据：

步骤1：创建销售数据表

打开Excel，选择一个新的工作表，例如命名为“销售数据”。

在单元格A1开始输入数据，创建一个包含日期、产品和销售额的数据表：

步骤2：使用MATCH函数查找数据点位置

假设我们要查找“苹果”在第二季度的销售记录的位置。

在单元格F1输入“苹果”，在单元格G1输入“Q2”。

在单元格H1输入以下公式来查找“苹果”在第二季度的销售记录的位置：

=IFERROR(INDEX(ROW(B2:B100), MATCH(1,(B2:B100=F1) * (C2:C100="Q2"),0)),"未找到记录")

这里假设销售数据在B2:B100和C2:C100的范围内。

步骤3：理解公式

ROW(B2:B100) 生成一个行号数组。

(B2:B100=F1) 生成一个布尔数组，其中产品名称等于 F1 的单元格为 TRUE，其余为 FALSE。

(C2:C100="Q2") 生成一个布尔数组，其中季度为 "Q2" 的单元格为

TRUE，其余为 FALSE。

(B2:B100=F1) * (C2:C100="Q2") 将上述两个布尔数组相乘，得到同时满足两个条件的布尔数组。

MATCH(1, ... , 0) 查找上述条件数组中第一个 TRUE 的位置（即行号）。

IFERROR(... , "未找到记录") 用来捕获错误并返回一个友好的消息。

请注意，上述公式是一个数组公式，在Excel中需要使用Ctrl+Shift+Enter来输入，这样Excel会将其作为数组公式处理，并在公式栏中用大括号 {} 包围起来。

使用这种方法，你可以准确地找到特定产品在特定季度的行号，而不会出现负数或其他错误。

2）报表生成：在生成报表时，MATCH可以帮助从大量数据中提取特定信息。

案例场景：报表生成中使用MATCH函数

假设我们有一个记录了一周内每天两种产品销售额的数据表，我们需要生成一个报表来显示每种产品在这周内的总销售额。

步骤1：准备数据

打开Excel，选择一个新的工作表，例如命名为“销售数据2”。

在单元格A1开始输入以下数据，创建一个包含日期、产品名称和销售额的数据表：

步骤2：创建报表

在新的工作表中，例如命名为“周销售报表”。

创建报表的标题和列标题：

步骤3：使用MATCH函数提取特定信息

在“周销售报表”工作表的A2单元格输入“苹果”，B2单元格输入以下公式：

=SUMIF(销售数据2!$B$1:$B$7, A2, 销售数据2!$C$1:$C$7)

将A2单元格中的“苹果”替换为“香蕉”，然后复制B2单元格中的公式到其他单元格，以计算不同产品的总销售额。

步骤4：查看结果

当你按下Enter键后，B2单元格将显示“苹果”这周的总销售额。

通过更改A2单元格中的文本，您可以为不同的产品计算总销售额。

这个简化的案例展示了如何在报表生成中使用SUMIF函数来从大量数据中提取特定信息。SUMIF函数是MATCH和SUM函数的组合，它根据一个条件对范围内的值求和。在这个例子中，我们没有直接使用MATCH函数，但SUMIF的工作原理与MATCH函数密切相关。

3）图表制作：在制作图表时，MATCH可以用于确定数据系列的位置。

如何在图表制作中使用MATCH函数来确定数据系列的位置。

案例场景：使用MATCH函数在图表中确定数据系列位置

假设我们有一个记录了一周内三种产品销售额的数据表，我们想要制作一个图表来展示这三种产品随时间变化的销售额，并使用MATCH函数来确定每种产品在图表中的位置。

步骤1：准备数据

1. 打开Excel，选择一个新的工作表。

2. 在单元格A1开始输入以下数据，创建一个包含日期和三种产品每日销售额的数据表：

步骤2：创建图表

1. 选择数据区域（假设是A1:D8）。

2. 转到“插入”选项卡，选择“折线图”或“柱状图”。

3. Excel将自动生成一个图表，展示一周内三种产品的销售额变化。

步骤3：使用MATCH函数确定数据系列位置

1. 假设我们需要确定“产品B”在图表中的位置。

2. 在图表旁边的空白单元格（例如F1）输入以下公式：

     =MATCH("产品B", B1:D1, 0)

     这将返回“产品B”在图表的数据源中的列号，即2。

步骤4：查看结果

根据MATCH函数返回的列号，我们可以知道“产品B”的数据系列在图表中的位置。

如果需要，可以手动选择图表中的数据系列进行格式化或调整。

或分析。

5.4.5 MATCH函数与其他函数的联合使用

1）与INDEX函数结合：MATCH和INDEX函数结合使用可以实现更灵活的数据查找。MATCH函数返回查找值在数组中的相对位置，INDEX函数

使用这个位置来返回对应的值。

公式示例： =INDEX(返回值区域, MATCH(查找值, 查找数组, 0))

假设我们有一个记录了一周内三种产品每日销售额的数据表，我们需要根据用户输入的产品名称和日期来查找对应的销售额。

步骤1：准备数据

打开Excel，选择一个新的工作表，例如命名为“销售数据3”。

在单元格A1开始输入以下数据，创建一个包含日期、产品名称和销售额的数据表

步骤2：准备查找条件

在单元格H1输入要查找的日期，例如“星期二”。

在单元格I1输入要查找的产品名称，例如“橙子”。

步骤3：使用MATCH和INDEX函数组合查找销售额

在单元格J1输入以下公式来查找特定日期和产品名称的销售额：

步骤4：理解公式

A2:A1000&B2:B1000 是将日期和产品名称合并的区域，以便MATCH函数可以查找完整的字符串。

H1&I1 是将用户输入的日期和产品名称合并的字符串。

MATCH(H1&I1, A2:A1000&B2:B1000, 0) 查找合并字符串在合并区域中的位置。

INDEX(C2:C1000, ...) 使用MATCH函数返回的位置来从销售额列中检索对应的值。

2）与VLOOKUP或HLOOKUP函数结合：MATCH可以作为VLOOKUP（垂直查找）或HLOOKUP（水平查找）的替代，提供更精确的查找功能。

使用MATCH和VLOOKUP函数组合进行数据查找。

假设我们有一个包含员工姓名和工号的数据表，我们需要根据员工的姓名查找其工号。

步骤1：准备数据

打开Excel，选择一个新的工作表，例如命名为“员工信息”。

在单元格A1开始输入以下数据，创建一个包含员工姓名和工号的数据表：

步骤2：准备查找条件

在单元格F1中输入要查找的员工姓名，例如“张三”。

步骤3：结合使用MATCH和VLOOKUP函数

在单元格G1中输入以下公式来结合使用MATCH和VLOOKUP函数查找工号：

=VLOOKUP(F1, A1:B3, MATCH("工号", A1:B1, 0), FALSE)

步骤4：理解公式

F1 是包含查找值（员工姓名）的单元格。

A1:B3 是包含员工信息的数据区域。

MATCH("工号", A1:B1, 0) 查找“工号”这一列标题在第一行的位置，0 表示精确匹配。

FALSE 表示VLOOKUP函数进行精确匹配。

MATCH("工号", A1:B1, 0) 这部分实际上返回的是2，因为“工号”在第一行的第二列。所以整个VLOOKUP函数的意思是：在A1:B3范围内查找F1单元格中的员工姓名，返回该行的第二列（工号）的值。

步骤5：查看结果

输入员工姓名“张三”后，按下Enter键，单元格G1将显示对应的工号“101”。

5.4.6常见错误和解决方案

条件查找：通过结合MATCH和IF函数，可以实现基于条件的数据查找。

假设我们有一个记录员工销售业绩的数据表，我们想要找出特定季度中销售额最高的员工的姓名和业绩。

步骤1：准备数据

打开Excel，选择一个新的工作表，例如命名为“销售业绩”。

在单元格A1开始输入以下数据，创建一个包含员工姓名、季度和销售额的数据表：

步骤2：准备查找条件

在单元格E1中输入要查找的季度，例如“Q2”。

步骤3：使用MATCH和IF函数组合查找销售额最高的员工

在单元格F1中输入以下公式来查找特定季度销售额最高的员工的姓名：

=INDEX(A2:A6, MATCH(MAX( IF(B2:B6=E1, C2:C6, 0) ), IF(B2:B6=E1, C2:C6, 0), 0))

按下Ctrl+Shift+Enter而不是仅仅Enter来输入这个公式。这将使Excel将其作为数组公式处理，并在公式栏中用大括号{}包围起来。

步骤4：理解公式

IF(B2:B6=E1, C2:C6, 0) 创建一个逻辑数组，如果季度列B2:B6中的值等于E1单元格中的值，则返回对应的销售额，否则返回0。

MAX(... , 0) 在上述逻辑数组中找到最大值。

MATCH(... , IF(B2:B6=E1, C2:C6, 0) , 0) 查找这个最大值在逻辑数组中的位置。

INDEX(A2:A6, ...) 使用MATCH函数返回的位置来从员工姓名列A2:A6中检索对应的员工姓名。

步骤5：查看结果

输入季度“Q2”后，按下Ctrl+Shift+Enter，单元格F1将显示“Q2”季度销售额最高的员工的姓名“赵六”。

MATCH函数是Excel中一个非常强大的工具，通过掌握其基本用法和高级技巧，可以极大地提高工作效率和数据处理能力。