使用Python基于特定字符串匹配Excel列并生成有序目标列

场景说明 #

现有两列数据：

• 列1（示例A列）：123_Car_Red、124_Truck_Red、125_Helicopter_Red
• 列2（示例D列）：Helicopter、Car、Truck
  需要生成列3，提取列1中与列2匹配的关键词，保持列1的顺序，最终结果为Car、Truck、Helicopter

方法1：数组公式（兼容所有Excel版本） #

假设列1从A2开始，列2是D1:D3，在C2单元格输入以下公式，旧版Excel按Ctrl+Shift+Enter触发数组计算，新版直接回车即可：

=TEXTJOIN("",TRUE,IFERROR(IF(SEARCH(D$1:D$3,A2)>0,D$1:D$3,""),""))

下拉填充到所有行完成批量处理。

原理：用SEARCH检测列2关键词是否存在于当前行列1内容中，IF返回匹配的关键词，TEXTJOIN合并结果（因每行仅匹配一个关键词，最终输出单个结果）。

方法2：XLOOKUP函数（Excel 365/2021及以上） #

更简洁的写法，C2单元格输入：

=XLOOKUP(TRUE, ISNUMBER(SEARCH(D$1:D$3, A2)), D$1:D$3)

下拉填充即可。

原理：ISNUMBER(SEARCH(...))生成布尔值数组标记匹配项，XLOOKUP直接返回第一个匹配的关键词。

方法3：Power Query批量处理（适合大量数据） #

1. 选中数据区域，点击「数据」选项卡 → 「从表格/区域」，将数据加载到Power Query编辑器
2. 添加自定义列，输入公式：

List.Select(Table.Column(源, "列2"), each Text.Contains([列1], _)){0}

3. 点击「关闭并上载」，将处理结果导入Excel得到列3数据

原理：提取列2关键词组成列表，筛选出包含在当前行列1中的关键词，取唯一匹配项。

内容的提问来源于stack exchange，提问作者Erick Echevarria