Excel如何基于两个字段筛选排除指定关联值

需求说明 #

• 所用表格共3列：A列为Turbine ID，B列为String ID，C列为Area
• 实现逻辑：输入指定的Turbine ID后，自动匹配该ID对应的B列String ID值，从全表条目中筛选排除所有Turbine ID与该String ID值重合的条目
• 效果示例：输入Turbine = 6时，匹配到对应String ID为4,2，自动排除Turbine ID为4、2的所有条目，示例原始数据如下：

| Turbine ID | String ID | Area  |
|------------|-----------|-------|
| 1          | 8,9       | 800m2 |
| 6          | 4,2       | 600m2 |

可直接套用的公式 #

以下公式适配Excel 365/2021及Google Sheets，假设指定Turbine ID的输入单元格为E1，原始数据范围为A2:C100（可根据实际数据行数调整范围）：

=FILTER(A2:C100,ISNA(XMATCH(A2:A100,TEXTSPLIT(XLOOKUP(E1,A:A,B:B,""),","))),"无匹配数据")

公式逻辑拆解：

• XLOOKUP(E1,A:A,B:B,"")：根据输入的Turbine ID匹配到对应的逗号分隔String ID值
• TEXTSPLIT(...,",")：把逗号分隔的String ID拆分为单个ID组成的数组，作为待排除列表
• XMATCH(A2:A100,...)：逐行判断当前行Turbine ID是否在待排除列表内，存在则返回位置，不存在返回错误值
• ISNA(...)：筛选出所有不在排除列表内的行，返回过滤后的完整三列数据

如果使用的Excel版本不支持TEXTSPLIT函数，可使用兼容旧版本的公式：

=FILTER(A2:C100,NOT(ISNUMBER(SEARCH(","&A2:A100&",",","&XLOOKUP(E1,A:A,B:B,"")&","))),"无匹配数据")

筛选结果输出后，可直接对结果中的Area列做后续汇总计算。

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