微精选

今天我们来讲一个传统案例，用多角度的视野来分析。

我们遵循：

方法不论优劣，思路不辨难易，重点是找到自身知识储备范围内最舒适的解题方式。

这个问题是这样的：

根据Excel表格中A1:B4区域的对应关系：A列为籍贯，B列为用逗号合并的多个姓名。针对D列提供的单个姓名（如D2的”吴越”、D3的”周昊”等），在E列查询并返回每个姓名对应的籍贯。要求使用Excel函数公式实现查询，确保姓名与籍贯一一匹配。

以上这个问题会受到我们分析问题的角度差异，产生很多种不同的解题方式。这种”多解性”告诉我们：

所以我们一定要培养自己的这种认识：

技术多样性源于思维角度的多样性

动态数组法（结构重构思路）：

TEXTSPLIT+REDUCE+VSTACK（现代函数解法）

虽然公式可能长、涉及到的函数多，但公式的可读性高，数组思维的整体性是无法替代的。

利用OFFSET函数获取A2籍贯单元格向右偏移1个单元格后的B2姓名单元格的内容。公式如下：

=OFFSET(A2,,1)

利用TEXTSPLIT拆分函数，通过行分隔符”,”，将A2对应的B2单元格“合并姓名”，拆分到多行显示（简称分行），公式如下：

利用HSTACK函数横向拼接，将B2分行后的“1列3行”的姓名数组与之对应的A2单元格姓名拼接合并，公式如下：

=HSTACK(TEXTSPLIT(OFFSET(A2,,1),,”,”),A2)

因为此时“拼接”的两个数组形状不一致，我们称之为“不规则数组”的拼接，如果数组行数不同，HSTACK会用#N/A填充空缺部分，如下图结果所示：

利用IFNA错误值处理函数，将错误值继续返回与之对应的A2籍贯单元格，公式如下：

=IFNA(HSTACK(TEXTSPLIT(OFFSET(A2,,1),,”,”),A2),A2)

借助VSTACK函数纵向拼接，纵向合并首行A1:B1标题行，公式如下：

将首行标题行A1:B1设置为：初始值（初始累积值）x

将A2:A4籍贯区域的（各个值）A2设置为：当前值y

用匿名函数LAMBDA表示其逻辑：

=LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(TEXTSPLIT(OFFSET(y,,1),,”,”),y),y)))

最后外嵌REDUCE遍历，输出累积结果：

最后使用VLOOKUP函数，在构造的这个“姓名-籍贯”一维表中，利用查找值精确匹配第2列的籍贯值，公式如下：

单一查找函数法：


XLOOKUP+通配符 传统经典解法

我们可以使用XLOOKUP函数：

=XLOOKUP(“*”&D2:D3&”*”,B2:B4,A2:A4,,2)

因为D列各单元格要查找的姓名值，在数据源的B列，是“合并姓名”单元格里面的一部分。

所以对D2:D3区域姓名加上通配符后，变成：“*”&D2:D3&”*”，可等价于数据源B列的“合并姓名”。

最后用XLOOKUP函数的特殊匹配模式“通配符匹配”，只要将第5参数设置为2，即可切换到此模式。

查找值：“*”&D2:D3&”*”（带通配符的姓名）

查找区域：B2:B4（数据源姓名列）

返回区域：A2:A4（数据源籍贯列）

这样我们就查找到了D列姓名在数据源中对应的籍贯信息。

利用IF函数的判断逻辑：如果是逻辑值TRUE（1），则返回B2:B4区域姓名列；相反，如果是逻辑值FALSE（0）,则返回A2:A4区域籍贯列

重构了查询表的列位置，我们就将一个“逆向查询”转换为了“正向查询”，最后利用VLOOKUP函数查询：

=VLOOKUP(“*”&D2:D3&”*”,IF({1,0},B2:B4,A2:A4),2,0)

因为VLOOKUP第一参数查找值自然支持通配符的使用。所以：

查找模式：0表示精确匹配

这样我们就查找到了D列姓名在数据源中对应的籍贯信息。

利用FIND函数在B2:B4区域中查找D2姓名的位置，返回数字（找到时）或错误值（未找到时），公式如下：

将找到的位置转换为0，错误值仍为错误，生成一个由0和错误值组成的数组。公式如下：

正则函数法（嵌套函数）

利用REGEXP函数的判断模式，判断D2姓名是否在B$2:B$4区域存在：

因为TRUE的位置是D2姓名在B列的位置，所以利用FILTER筛选出条件为TRUE时A$2:A$4区域的籍贯即可：