Excel如何仅用公式动态提取匹配/排除指定子串的整行数据

基础需求说明 #

• 工作簿包含多个工作表，核心基础数据表规模约8000行、10列
• 最终目标：输入起止日期、关键词后自动完成多层筛选输出结果，源数据、筛选条件变动时结果自动同步更新，不使用Excel原生手动筛选功能（原生筛选无法自动同步变动）
• 功能测试可参考数据范围A3:C8

已落地的两层筛选逻辑 #

• 第一层：固定关键词提取（Sheet1 → Xtract工作表）
  旧版Excel需按Ctrl+Shift+Enter确认数组公式，公式如下：

  =INDEX(Sheet1!$A$6:$N$6796, SMALL(IF(COUNTIF('12T'!$H$11,Sheet1!$G$6:$G$6796), MATCH(ROW(Sheet1!$A$6:$N$6796),ROW(Sheet1!$A$6:$N$6796)), ""), ROWS(B3:$B$3)), COLUMNS(Sheet1!$A$6:A6))
  

  作用：提取Sheet1中G列匹配指定关键词（示例值：5351 - Facilities: Maintenance: Building）的所有行到Xtract工作表。
• 第二层：日期区间筛选
  1. 匹配条目数统计公式：

  =SUMPRODUCT(($A$2:$A$671>=Q2)*($A$2:$A$671<=Q3))
  

  其中Q2为起始日期单元格、Q3为结束日期单元格。
  2. 区间数据提取公式（适用于Excel 365/2021及以上版本）：

  =FILTER(A2:O671,(A2:A671>=Q2)*(A2:A671<=Q3),"No data")
  

  作用：提取日期列（A列）落在指定起止日期区间内的所有行。

第三层模糊子串筛选实现 #

筛选规则 #

待匹配子串嵌在无固定格式的变长文本中，文本存在上百种变体（示例：12T Q1FY23 Unscheduled/Emergency Maintenance、12T Q4FY23 ERT Spill Stations），部分目标子串不在文本开头，需满足：

• 保留文本列包含指定独立子串（示例值：12T、728M）的整行
• 排除包含特定子串的行（示例：排除含12T-M的行，避免误命中独立12T的匹配规则）

可用公式 #

假设：上一步日期筛选输出的数据源区域为A2:O671，待匹配的文本列为C列，目标匹配子串存在R2单元格，需排除的子串存在R3单元格，结果输出到指定工作表，直接使用以下公式即可：

=FILTER(
  A2:O671,
  (ISNUMBER(SEARCH(" "&R2&" "," "&C2:C671&" ")))*
  (NOT(ISNUMBER(SEARCH(R3,C2:C671)))),
  "No matching data"
)

公式逻辑说明：

• 给待匹配文本前后拼接空格、给目标子串前后拼接空格后再做匹配，可确保命中的是独立存在的子串，不会误匹配子串嵌在其他长字符内部的情况（比如不会把X12T、12TX判定为含独立12T）
• 第二层判断直接排除所有包含指定排除子串的行，规避12T-M这类内容的误匹配
• 多个判断条件用乘法连接代表逻辑“与”，满足全部条件的整行数据会被自动提取
• 若使用旧版Excel不支持FILTER函数，可沿用现有INDEX+SMALL数组公式框架，将原有判断条件替换为上述子串匹配+排除规则即可，输入公式后需按Ctrl+Shift+Enter确认数组运算。

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