ARTICLE · 1343763

Excel 函数与公式 -- 内容搜索(十一)

发布时间：2026-11-11 13:38:12 最近访问：2026-11-11 13:38:13

上文结束时，我们给出了一个 XMATCH 和 INDEX 结合进行综合查询的例子，如下图所示：

图1 XMATCH 和 INDEX 结合查询

右侧的查询面板根据 H1 单元格的内容对左侧的数据表进行查询。但这个例子有一个重大缺陷。我们看 H2 单元格的公式：

H2：=INDEX(A2:A24, XMATCH(H1, D2:D24))

因为 XMATCH 的搜索区域指定为 D2:D24，所以只能就 D 列即产品列进行查询，在不修改公式的情况下无法就其他列进行提问。

我们想针对数据表的任何一列进行查询，怎么做？

要相对数据表的任何一列进行查询，就要知道查询的是哪一列，然后从给出的值中查得所在行，就可以将该行的数据全部提取出来。

由上面的分析，可知问题的关键是从给出的查询条件中获得列名。因此，作为查询项的 H1 单元格就不能单纯的只是要查询的“值”，而必须要带有更多的信息。为此目的，我们可以要查询的值所在的单元格作为查询条件，譬如，要查询产品“梨干”，我们不在 H1 中简单地输入“梨干”，而是输入公式：=D7。此时，H1 单元格显示出来的内容仍然是“梨干”，但我们可以从 H1 的内部值获取列名 D，知道列名以后，就可以组合出 XMATCH 的搜索区域，剩下的就简单了。

现在 Excel 的最新版支持 LAMBDA 表达式，使得我们可以在公式中进行编程，而无需借助 VBA。为了从 H1 单元格构建 XMATCH 的搜索区域，我们写一个 LAMBDA 表达式，并将其命名为 BuildRange。

点击 Excel 菜单上的公式 → 名称管理器，或按下快捷键 Ctrl + F3，弹出“名称管理器”对话框，如图所示：

图2 Excel 名称管理器

点击“新建……”，打开新建名称对话框，如图3所示：

图3 新建名称

在名称栏输入 BuildRange，范围选工作簿，批注栏可以输入一些注释性信息，引用位置栏输入如下的 LAMBDA 公式：

=LAMBDA(rcell,startcol,endcol, LET(xcol, MID(FORMULATEXT(rcell), 2, 1), INDIRECT(CONCAT(xcol, startcol)):INDIRECT(CONCAT(xcol, endcol))))

这个公式看起来很复杂，先看其主体结构：

=LAMBDA(rcell, startcol, endcol, LET(...))

在这个公式中，rcell、startcol、endcol 是三个参数，后面的 LET(...) 是 LET 函数，利用前面的三个参数实现构建搜索区域的功能。LET 函数后面再详细讨论。

点击“确定”，完成名称 BuildRange 的创建。这样创建了以后，BuildRange 就可以作为一般函数来使用了。

下面来看 H2 单元格的新公式：

H2：=INDEX(A2:A24, XMATCH(H1, BuildRange(H1, 2, 24))

此时，如何 H1 单元格输入 =D7，BuildRange(H1, 2, 24) 将返回 D2:D24。如果 H1 输入 =A3，BuildRange(H1, 2, 24) 将返回 A2:A24。以此类推。这样一来，查询面板就可以针对数据表的任何一列进行提问，而不再固定为某一列。如图4所示：

图4 改进的查询面板

对比图4 和图1，表面上看来没有任何区别，但查询面板各个单元格的公式已经不一样了。

下面来分析 LET 公式。

LET 函数

LET 是 Excel 2021 新增加的函数，其一般格式如下：

其中，name 和 name_value（以及 name2 和 name_value2，……）是配对的，name 是名字，name_value 是一个表达式。LET 的作用是逐个计算 name_value（name_value2，……）表达式的值，并赋给对应的 name（name2，……），最后将这些 name（即变量）代入 calculation 中，计算 calculation 表达式，并返回该表达式的值。

例：

=LET(x, 1, x + 1)：将值 1 赋给名字 x，最后将 x 代入表达式 x + 1 中，计算表达式的值，返回该表达式的值 2。

=LET(x, 3, y, 10, x + y)：分别将值 3 和 10 赋给名字 x 和 y，最后将 x 和 y 代入表达式 x + y 中，计算该表达式的值，返回值 13。

为了理解上面 LAMBDA 表达式中的 LET，还需要了解如下四个函数。

MID 函数

这是从字符串中获取子串的函数。一般格式如下：

其中，text 是原始字符串，start_num 是起始位置，num_chars 表示要截取的子串的字符数。

例：

=MID("Hello", 2, 1)：从第 2 个位置开始，截取一个字符，返回 "e"。

=MID("Good morning!", 1, 2)：从第 1 个位置开始，截取 2 个字符，返回 "Go"。

FORMULATEXT 函数

FORMULATEXT 返回单元格中的公式，以文本形式表示。一般格式为：

其中，reference 是单元格引用。

例：

假设 H1 单元格中有公式 =D7，

=FORMULATEXT(H1)：返回以字符串形式表示的公式“=D7”。

=MID(FORMULATEXT(H1), 2, 1)：返回“D"。

CONCAT 函数

字符串连接函数。其一般形式为：

其中，text1，text2，……，是要连接的文本，CONCAT 函数的作用是将这些文本连接在一起，组成一个完整的字符串。

例：

=CONCAT(“abc")：返回字符串"abc"。

=CONCAT("Hello", ", ", "World", "!")：返回"Hello, World!"。

INDIRECT 函数

将以字符串形式表示的单元格转换为单元格引用并返回。其一般格式为：

其中，ref_text 表示单元格的字符串表示，可选参数 a1 表示单元格的表示方式：

如果 a1 省略，或 a1 为 TRUE，则使用 A1 形式的单元格引用。
如果 a1 为 FALSE，则使用 R1C1 形式的单元格引用。

例：

=INDIRECT("A2")：返回 A2 单元格引用。

=INDIRECT("B" & 100)：返回 B100 单元格引用。

假设 H1 单元格有公式 =D7

=INDIRECT(MID(FORMULATEXT(H1), 2, 1) & "20")：返回 D20 单元格引用

=INDIRECT(CONCAT(MID(FORMULATEXT(H1), 2, 1), 20)：同上。

现在，我们就可以来分析上面的复杂的 LET 函数了。

如果我们把上面的 LET 函数写成下面的形式：

就看得很清楚了。xcol 是 name，MID(FORMULATEXT(rcell), 2, 1) 是 name_value，LET 函数先计算 MID(FORMULATEXT(rcell), 2, 1)，再将结果赋给 name，也就是 xcol。最后再将 xcol 代入两个 CONCAT 函数，分别与 startcol 和 endcol 组合，拼接为 A1 形式的单元格字符串，最后再由 INDIRECT 函数将字符串形式的单元格转换为单元格引用。

最终，这个 LET 函数的结果是 D2:D24 形式的区域（Range），这也是整个 LAMBDA 表达式的返回值。

下面是整个 LAMBDA 公式的完整形式：

这个公式被命名为 BuildRange。而 BuildRange 的一般形式就成为：

例：

设 H1 单元格内容为 =D8，

=BuildRange(H1, 3, 10)：返回区域 D3:D10。

=XMATCH(H1, BuildRange(H1, 3, 10))：相当于：

=XMATCH(H1, D3:D10)：在区域 D3:D10 中搜索 H1 单元格中的内容。

=BuildRange(H1, 5, 30)：返回区域 D5:D30。

=XMATCH(H1, BuildRange(H1, 5, 30))：相当于：

=XMATCH(H1, D5:D30)：在区域 D5:D30 中搜索 H1 单元格中的内容。

至此，Excel 函数与公式的内容搜索部分就全部讲完了。可以看出，Excel 为内容搜索提供了充分的手段，特别是 LAMBDA 表达式的引入，为我们在公式中编程提供了可能。微软称，引入 LAMBDA 表达式后，Excel 的公式就成为“图灵完备”的了，这意味着一般编程语言能实现的功能，使用 Excel 的函数与公式也一样能实现。所以，学习、掌握 LAMBDA 表达式，即使不懂编程语言，也一样能编程！