如何在Excel中将行内生境值匹配到同名列并赋值1或0？

我有一份大型生境数据集，结构类似附图（原问题附图未提供）。数据集的biotop列单元格内用逗号分隔多个生境，每行包含的生境数量不一。另外还有一批以生境名称命名的独立列（如A1、A5），需要实现：若某行biotop列包含对应生境，则该生境列赋值1，未匹配则赋值0。

之前尝试过VLOOKUP/XLOOKUP，但这类函数无法满足需求，需要更合适的解决方案。

示例输入 #

biotop         A1      A5
A1, L3, L5            
A5, A1               
L3, L5                
L6                    
A1                    

期望输出 #

biotop         A1      A5
A1, L3, L5     1       0
A5, A1         1       1
L3, L5         0       0
L6             0       0
A1             1       0

解决方案 #

方法1：ISNUMBER+SEARCH组合公式（兼容所有Excel版本） #

在A1列的第一个数据单元格（示例中为B2）输入以下公式，然后横向拖拽填充到其他生境列，再纵向拖拽填充到所有行：

=IF(ISNUMBER(SEARCH(","&B$1&",", ","&$A2&",")), 1, 0)

• 细节说明：给生境名称和biotop单元格内容前后都添加逗号，是为了避免部分匹配（比如防止把"A10"误识别为"A1"）；SEARCH查找目标生境的位置，ISNUMBER判断是否找到，最终返回1或0。

方法2：TEXTSPLIT+COUNTIF组合（适用于Excel 365/2021） #

如果你的Excel支持动态数组函数，可使用更简洁的公式：
在B2单元格输入公式后，Excel会自动填充整列及后续生境列：

=--COUNTIF(TEXTSPLIT($A2, ", "), B$1)

• 细节说明：TEXTSPLIT将biotop单元格内容按", "拆分为数组；COUNTIF统计目标生境在数组中的出现次数；前置--将计数结果转换为1（有匹配）或0（无匹配）。

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

若数据集规模极大，用Power Query处理更高效：

1. 选中数据区域，点击「数据」选项卡 → 「从表格/区域」导入Power Query编辑器（勾选「我的表格有标题」）
2. 选中biotop列，点击「转换」选项卡 → 「拆分列」→ 按分隔符", "拆分，选择「拆分为行」
3. 选中拆分后的生境列，点击「转换」选项卡 → 「透视列」，值列选择biotop，值聚合函数选择「计数」
4. 选中透视后的所有列，点击「转换」选项卡 → 「替换值」，将空值替换为0
5. 点击「关闭并上载」，将处理后的数据导出回Excel

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