Google Sheets QUERY多变量筛选冗余公式优化方案问询

优化后完整公式 #

无需单独拆分参数到F1/G1单元格，直接读取E1的分号分隔输入即可：

=LET(
  // 缓存导入的数据源，避免重复调用IMPORTRANGE
  raw_data, IMPORTRANGE("Spreadsheetidabcd", "RawData!A:Z"),
  // 固定的查询返回列规则
  select_rule, "Select Col1,Col12,Col2,Col10,Col3 ",
  // 固定的基础筛选条件
  base_condition, "where Col11='' ",
  // 处理E1的输入，拆分出所有搜索关键词并去空
  search_keys, TOCOL(SPLIT(TRIM(E1), ";", 1, 1), 1),
  // 判断是否有搜索输入
  IF(
    E1 = "",
    // 无搜索输入时仅按基础条件筛选
    QUERY(raw_data, select_rule&base_condition, 1),
    // 有搜索输入时拼接正则匹配规则
    QUERY(
      raw_data,
      select_rule&base_condition&
      "AND LOWER(Col1||'|'||Col12||'|'||Col2||'|'||Col10||'|'||Col3||'|'||Col4||'|'||Col5) 
      matches '.*("&JOIN("|", LOWER(search_keys))&").*'",
      1
    )
  )
)

核心优化点 #

• 用LET函数缓存IMPORTRANGE返回的数据源，避免原来的公式重复2次拉取同一份数据，大幅提升计算效率
• 将所有需要匹配的字段统一拼接后做正则匹配，替代原来冗余的OR判断，公式长度缩减70%以上
• 自动适配任意数量的分号分隔搜索参数，后续新增参数无需修改匹配逻辑，天然支持E1输入N个分号分隔的关键词
• 统一转小写后匹配，无需为每个字段单独写LOWER()判断，天然支持不区分大小写的模糊搜索

拓展调整方法 #

• 若搜索内容包含正则特殊字符（. * + ? | () [] {} $ ^），可在处理搜索关键词时新增转义逻辑，将search_keys的定义替换为：

  search_keys, REGEXREPLACE(TOCOL(SPLIT(TRIM(E1), ";", 1, 1), 1), "([.*+?|()\[\]{}\\$^])", "\\$1"),
  

• 若需要调整搜索匹配的字段，仅需要修改LOWER()内拼接的列即可，无需调整其他逻辑
• 若需要从包含匹配改为完全匹配，将正则前后的.*删除即可

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