用ADDRESS函数动态生成单元格引用：搭配MATCH函数查找数据

在 Excel 中，根据表头或查找条件动态定位单元格，是报表和分析表格中的常见需求。MATCH 可以返回目标值在区域中的位置，ADDRESS 则可以把行号、列号转换为单元格地址文本。将两者组合起来，可以生成会随查找条件变化的引用，例如从“产品名称”定位到“销售额”所在的单元格。

不过，ADDRESS 返回的是文本形式的地址，不是可以直接参与计算的单元格值。需要取出该地址对应的内容时，通常还要用 INDIRECT 将文本转换成引用。很多查找任务则更适合直接用 INDEX 搭配 MATCH。理解这几种函数的区别，能避免公式看起来动态、实际却难以维护的问题。

一、ADDRESS 和 MATCH 分别做什么

ADDRESS：把行号和列号转换成地址文本

ADDRESS 根据行号、列号生成单元格地址。例如，给定第 3 行、第 2 列，函数可以返回 $B$3。它不会读取这个单元格的值，也不会检查里面是什么内容，只负责生成地址文本。

=ADDRESS(3,2)

在默认设置下，结果为 $B$3。如果要进一步取出 B3 的内容，可以把结果交给 INDIRECT：

=INDIRECT(ADDRESS(3,2))

这条公式最终返回 B3 的值。理解“地址文本”和“单元格引用”之间的差别，是使用这组函数的关键。

MATCH：返回目标在查找区域中的位置

MATCH 用于在一行或一列中查找值，并返回该值在查找区域中的相对位置。它返回的是位置序号，不是目标单元格的值。

=MATCH("青岚茶",A2:A6,0)

如果“青岚茶”位于 A2:A6 中的第 3 个位置，结果就是 3。注意这个位置是相对于查找区域起算的：A2:A6 的第 1 个位置对应工作表的第 2 行，而不是第 1 行。

二、ADDRESS 的语法和参数限制

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

• row_num：必需参数，目标单元格的行号。
• column_num：必需参数，目标单元格的列号。
• abs_num：可选参数，控制行号和列号是绝对引用还是相对引用。
• a1：可选逻辑值，控制返回 A1 样式还是 R1C1 样式的地址。
• sheet_text：可选参数，指定结果中包含的工作表名称。

1. abs_num：控制绝对引用形式

abs_num 只能取 1 到 4，对应如下：

=ADDRESS(3,2,1)
=ADDRESS(3,2,4)

第一条返回 $B$3，第二条返回 B3。这里的“绝对”描述的是生成地址文本中的美元符号，不代表数据不可修改，也不是权限设置。

2. a1：选择 A1 或 R1C1 格式

a1 为 TRUE 或省略时，返回 A1 格式；为 FALSE 时，返回 R1C1 格式。例如：

=ADDRESS(3,2,1,TRUE)
=ADDRESS(3,2,1,FALSE)

结果分别类似于 $B$3 和 R3C2。R1C1 格式适合某些按行列号处理的场景，但如果后续交给 INDIRECT，其 a1 参数也要与地址格式匹配。

3. sheet_text：在地址中指定工作表

可以用 sheet_text 指定工作表名称：

=ADDRESS(3,2,1,TRUE,"销售数据")

生成的地址会带上工作表名称，例如 销售数据!$B$3。包含空格等需要引用的工作表名称，在生成的引用中会按 Excel 的规则加上单引号，例如 '年度 销售'!$B$3。如果使用工作表名作为动态输入，应确保名称实际存在；ADDRESS 生成文本时并不会替你验证目标工作表是否有效。

行号、列号必须落在工作表允许的范围内。现代 Excel 工作表最多有 1,048,576 行和 16,384 列（最后一列为 XFD）；越界的行号或列号会导致错误。abs_num 超出 1 到 4 的范围也会导致错误。不要把从外部输入获得的任意数字直接当作合法行列号，必要时先验证范围。

三、用 MATCH 找到位置，再由 ADDRESS 生成地址

假设工作表中有以下数据，A 列是产品名称，B 列是类别，C 列是销售额：

假设 E2 输入要查找的产品名称，例如“青岚茶”。先在 A2:A6 中查找它的位置：

=MATCH(E2,A2:A6,0)

结果为 2，因为“青岚茶”是查找区域中的第二项。若要生成该产品名称所在单元格的地址，需要把查找区域的起始行号加到相对位置上。A2:A6 从工作表第 2 行开始，因此实际行号为 MATCH(E2,A2:A6,0)+ROW(A2)-1：

=ADDRESS(MATCH(E2,A2:A6,0)+ROW(A2)-1,COLUMN(A2))

公式返回 $A$3。使用 ROW(A2) 和 COLUMN(A2) 表达起始位置，比在公式中手工写死起始行号更容易随着布局调整而理解。

如果要生成同一产品所在行的销售额单元格地址，则列号改为 C 列：

=ADDRESS(MATCH(E2,A2:A6,0)+ROW(A2)-1,COLUMN(C1))

结果为 $C$3。这里 COLUMN(C1) 返回工作表列号 3。也可以直接写数字 3，但用 COLUMN 更能表明所指列。

四、将地址文本转换成实际值

如果目标不是显示地址，而是返回对应单元格的内容，可以在外层使用 INDIRECT：

=INDIRECT(ADDRESS(MATCH(E2,A2:A6,0)+ROW(A2)-1,COLUMN(C1)))

在 E2 为“青岚茶”时，公式先生成 $C$3，再由 INDIRECT 将地址文本解释为引用，最终返回 960。

也可以直接构造包含工作表的地址。如果数据位于“销售数据”工作表，且公式写在其他工作表，可以这样写：

=INDIRECT(ADDRESS(
  MATCH(E2,'销售数据'!A2:A6,0)+ROW('销售数据'!A2)-1,
  COLUMN('销售数据'!C1),
  1,
  TRUE,
  "销售数据"
))

公式中的查找区域、起始行、列号和 sheet_text 都指向同一张表，避免匹配位置来自一张表、最终地址却落到另一张表。若工作簿名含空格，公式引用工作表时要按 Excel 语法加单引号；sheet_text 参数本身则写工作表名称文本，不需要手动把单引号拼进文本。

五、二维查找：同时匹配行和列

有时查找条件不仅包括行项目，还包括列标题。例如 A 列为产品名称，第一行为月份，数据区域为 B2:D6。要根据产品名称和月份定位交叉单元格，可以分别用两个 MATCH 得到行、列位置，再交给 ADDRESS。

假设 E2 是产品名称，F2 是月份标题，标题行在 B1:D1，产品名称在 A2:A6，数据区域为 B2:D6：

=ADDRESS(
  MATCH(E2,A2:A6,0)+ROW(A2)-1,
  MATCH(F2,B1:D1,0)+COLUMN(B1)-1
)

第一个 MATCH 返回产品在 A2:A6 中的位置，第二个返回月份在 B1:D1 中的位置。分别加上各自区域的起始行号或列号，再传给 ADDRESS，就会得到交叉单元格的地址。

如需返回交叉位置的值，可以用 INDIRECT 包住公式：

=INDIRECT(ADDRESS(
  MATCH(E2,A2:A6,0)+ROW(A2)-1,
  MATCH(F2,B1:D1,0)+COLUMN(B1)-1
))

如果表格移动了，公式中的区域也应相应调整。行查找范围、列查找范围和实际数据表必须保持一致，否则即使两个 MATCH 都找到结果，组合出来的位置也可能不是预期交叉点。

六、MATCH 的匹配模式与常见误区

MATCH 的第三个参数 match_type 决定匹配方式：

• 0：精确匹配。用于查找具体名称、编号或表头时，通常应明确写 0。精确匹配的文本查找不区分大小写。
• 1 或省略：查找小于或等于目标值的最大值，查找区域需按升序排列。
• -1：查找大于或等于目标值的最小值，查找区域需按降序排列。

若省略第三个参数，MATCH 默认使用 1，并不是精确匹配。这是常见错误之一：在未排序的文本或数据表中省略该参数，可能得到错误结果或意外结果。对大多数按名称查找的场景，应写成：

=MATCH(E2,A2:A6,0)

使用精确匹配时，查找区域可以不排序。若查找的是文本，MATCH 还支持通配符：* 匹配任意数量的字符，? 匹配单个字符；要查找星号或问号本身，可以用波浪号转义，例如 ~*。需要注意，含有通配符的查找值可能匹配到多个文本中的第一个符合项；若要查找字面意义上的通配符，应正确转义。

七、ADDRESS + MATCH 还是 INDEX + MATCH

如果只需要返回查找到的值，通常更建议使用 INDEX 和 MATCH，不必先生成地址文本再转成引用。对前面的销售额示例，可写为：

=INDEX(C2:C6,MATCH(E2,A2:A6,0))

二维查找则可以写成：

=INDEX(
  B2:D6,
  MATCH(E2,A2:A6,0),
  MATCH(F2,B1:D1,0)
)

INDEX 直接从数据区域返回对应值，不需要把地址文本交给 INDIRECT 解释。这样公式意图更直接，也能避免动态文本地址带来的维护问题。若目标是显示地址、把地址作为文本输出，或某些特定公式设计确实需要文本引用，使用 ADDRESS 才更合适。

INDIRECT 是易失性函数，工作簿重新计算时可能会参与频繁计算；大量使用时会影响性能。此外，它依赖文本拼出的引用，插入、移动区域后不一定能像直接引用那样自动调整。需要跨工作簿引用时，INDIRECT 对关闭的外部工作簿也有限制。因此，不要为了“动态”而把所有查找都写成 ADDRESS 加 INDIRECT。

八、常见错误与排查顺序

1. 省略 MATCH 的精确匹配参数

公式 =MATCH(E2,A2:A6) 默认使用升序近似匹配。对于未排序的名称列表，结果可能不可靠。查找具体文本或编号时，通常改为 =MATCH(E2,A2:A6,0)。

2. 忘记把相对位置换算成工作表行号或列号

MATCH(E2,A2:A6,0) 的结果是相对于 A2:A6 的位置，不是工作表行号。直接写成 =ADDRESS(MATCH(E2,A2:A6,0),1) 会把区域中的第 1 项误当作工作表第 1 行。应加上区域起始位置，例如 MATCH(E2,A2:A6,0)+ROW(A2)-1。

3. 混淆地址文本和单元格的值

=ADDRESS(3,2) 返回的是文本 $B$3，不是 B3 中的内容。需要取值时，可使用 INDIRECT，或者优先考虑 INDEX。

4. 查找区域和地址区域不对应

若产品名称在 A2:A6 中查找，最终地址却使用了另一块数据区域的起始行或列，位置就可能错位。检查 MATCH 的区域、ROW 或 COLUMN 的基准单元格，以及 ADDRESS 组合出的行列号，确认它们属于同一张表、同一布局。

5. 没有处理找不到匹配项的情况

找不到目标时，MATCH 会返回 #N/A，错误会继续传递给外层函数。可用 IFNA 提供可读提示：

=IFNA(
  INDEX(C2:C6,MATCH(E2,A2:A6,0)),
  "未找到产品"
)

如果确实需要显示地址，也可以处理错误：

=IFNA(
  ADDRESS(MATCH(E2,A2:A6,0)+ROW(A2)-1,COLUMN(C1)),
  "未找到产品"
)

用 IFNA 针对未找到项进行处理，通常比用 IFERROR 遮盖所有错误更容易排查问题；若还需捕获其他错误，再根据需求选择相应函数。

6. 忽略参数类型和区域方向

MATCH 的查找区域应是一行或一列。不要把多行多列的数据块直接当作普通的一维查找区域。近似匹配还要求数据按相应方向排序：1 用升序，-1 用降序。ADDRESS 的行列号也要是工作表有效范围内的数值。

九、边界、安全与公式维护

ADDRESS 的用途是生成引用文本，不是访问控制或数据保护机制。把某单元格写成绝对地址、隐藏工作表，或通过 INDIRECT 拼出地址，都不会阻止用户查看或修改数据。涉及权限、敏感信息或数据隔离时，应使用工作簿权限、文件访问控制或合适的数据服务，不能依赖单元格地址公式实现安全隔离。

也不要把 ADDRESS 的结果当作可执行代码。它输出的是地址文本；只有在公式中将其交给 INDIRECT 等函数解释时，才会作为引用使用。若地址组成部分来自用户输入，应限制工作表名称、行号和列号的来源，并校验其是否在预期范围内，避免公式指向意外区域。需要向其他系统导出公式或文本时，还应依据目标系统对公式的处理方式单独评估；单靠 ADDRESS 不能保证导出数据安全。

为提高可维护性，建议遵循几条原则：

• 精确查找时始终显式写出 MATCH 的第三个参数 0。
• 用 ROW(起始单元格) 和 COLUMN(起始单元格) 明确表示区域基准，避免错把相对位置当成工作表位置。
• 只需要返回数据值时，优先使用 INDEX 配合 MATCH；只有确实需要地址文本时才生成地址。
• 处理未找到项，并检查查找区域、目标区域和工作表名称是否一致。
• 在不同 Excel 语言或区域设置中，公式参数分隔符可能显示为分号而不是逗号；复制公式报语法错误时，先确认本机所需的分隔符。

总结

MATCH 负责定位目标在查找区域中的相对位置，ADDRESS 根据行号和列号生成地址文本。组合使用时，要把相对位置转换为工作表行号、列号；需要读取地址指向的内容时，可以再用 INDIRECT，但应留意易失性、外部引用和维护成本。对于常见的单条件或二维取值，INDEX 加 MATCH 通常更直接。掌握函数参数、匹配模式和位置换算后，就能根据实际目标选择更清晰可靠的公式。