GSheets带ARRAYFORMULA的QUERY公式添加If/Filter筛选方法

场景说明 #

• 调研响应表存储在「Module 1 Responses」工作表，包含100余个Yes/No类型问题列，规则为受访者选No时需填写对应自由文本说明
• 原有公式可实现按「Action Plan」工作表的表头匹配，拉取对应列的作答数据，原公式为：

=QUERY('Module 1 Responses'!$A$2:$AS,"SELECT "&join(",",arrayformula(SUBSTITUTE(ADDRESS(1,MATCH($A$1:$W$1,'Module 1 Responses'!$A$1:$AS$1,0),4),1,""))))

• 目标需求：在原有公式基础上增加逻辑，要么仅展示No相关内容，要么把所有Yes的返回值转为空值，方便整理行动项

可用公式 #

方案1：保留所有作答行，仅清空Yes值 #

适合需要留存全量受访者记录，仅快速定位No项的场景，直接将以下公式粘贴到「Action Plan」表的A2单元格即可：

=ARRAYFORMULA(
  LET(
    raw_export, QUERY('Module 1 Responses'!$A$2:$AS,"SELECT "&join(",",arrayformula(SUBSTITUTE(ADDRESS(1,MATCH($A$1:$W$1,'Module 1 Responses'!$A$1:$AS$1,0),4),1,"")))),
    IF(raw_export="Yes", "", raw_export)
  )
)

逻辑说明：

• 用LET函数将原有QUERY的导出结果定义为raw_export，避免重复计算
• 外层用数组判断遍历所有导出单元格，值为Yes的直接返回空字符串，No选项、对应自由文本、受访者基础信息等其余内容全部原样保留

方案2：过滤全Yes无效行，仅保留存在No回答的记录 #

适合只需要整理待跟进行动项，不需要留存全选Yes的无效记录的场景，公式如下：

=ARRAYFORMULA(
  LET(
    match_cols, ARRAYFORMULA(SUBSTITUTE(ADDRESS(1,MATCH($A$1:$W$1,'Module 1 Responses'!$A$1:$AS$1,0),4),1,"")),
    raw_export, QUERY('Module 1 Responses'!$A$2:$AS,"SELECT "&JOIN(",",match_cols)),
    valid_rows, QUERY(raw_export, "WHERE "&JOIN(" OR ", match_cols&"='No'")),
    IF(valid_rows="Yes", "", valid_rows)
  )
)

逻辑说明：

• 先匹配「Action Plan」表头对应原表的列，拉取全量目标列数据
• 给QUERY增加WHERE筛选条件，只要任意一个目标问题列值为No就保留整行，自动过滤全选Yes的记录
• 最后同样将保留行中的Yes值清空，直接展示No选项和对应的自由文本说明

使用提示：请确保「Action Plan」表A1:W1的表头文本和「Module 1 Responses」表的对应问题表头完全一致，否则列匹配会出现偏差。

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