如何在Google Sheets的Sort+FILTER结果中正确新增固定值列？

问题描述 #

我有一段可正常运行的Google Sheets公式：

=Sort({IFERROR((FILTER({'My Log'!B13:B, 'My Log'!F13:F, 'My Log'!E13:E, 'My Log'!H13:H, 'My Log'!I13:I,'My Log'!J13:J, 'My Log'!K13:K,'My Log'!L13:L, 'My Log'!P13:P, 'My Log'!R13:R}, 
'My Log'!O13:O = "Y", 
'My Log'!N13:N =B1
)), {"","","","","","","","","","",""})
},1, TRUE)

该公式可按需求填充单元格。但我需要为输出的每一行新增一列，填充'My Newvalue'!C1单元格的文本值。尝试直接在FILTER的区域中添加'My Newvalue'!C1引用后，输出结果丢失。

尝试的错误公式：

=Sort({IFERROR((FILTER({'My Log'!B13:B, 'My Log'!F13:F, 'My Log'!E13:E, 'My Newvalue'!C1, 'My Log'!H13:H, 'My Log'!I13:I,'My Log'!J13:J, 'My Log'!K13:K,'My Log'!L13:L, 'My Log'!P13:P, 'My Log'!R13:R}, 
'My Log'!O13:O = "Y", 
'My Log'!N13:N =B1
)), {"","","","","","","","","","","",""})
},1, TRUE)

问题原因 #

直接在FILTER的区域中加入单个单元格引用时，FILTER要求所有参与的列行数必须完全一致。'My Newvalue'!C1是单行数据，而其他列是多行范围，两者维度不匹配，导致FILTER无法返回有效结果，最终输出丢失。

解决方法 #

我们需要先获取FILTER的结果，再生成一个行数与结果一致、所有值都是'My Newvalue'!C1的列，最后将两者合并后排序。推荐使用LET函数让公式结构更清晰：

=LET(
  filtered_data, IFERROR(FILTER({'My Log'!B13:B, 'My Log'!F13:F, 'My Log'!E13:E, 'My Log'!H13:H, 'My Log'!I13:I, 'My Log'!J13:J, 'My Log'!K13:K, 'My Log'!L13:L, 'My Log'!P13:P, 'My Log'!R13:R},
  'My Log'!O13:O = "Y",
  'My Log'!N13:N = B1),
  {"","","","","","","","","","",""}),
  new_value_col, IFERROR(ARRAYFORMULA(IF(SEQUENCE(ROWS(filtered_data)) <= ROWS(filtered_data), 'My Newvalue'!C1, "")), ""),
  SORT({filtered_data, new_value_col}, 1, TRUE)
)

简化写法（不使用LET） #

如果你的Google Sheets版本不支持LET，可以用以下写法：

=SORT({
  IFERROR(FILTER({'My Log'!B13:B, 'My Log'!F13:F, 'My Log'!E13:E, 'My Log'!H13:H, 'My Log'!I13:I, 'My Log'!J13:J, 'My Log'!K13:K, 'My Log'!L13:L, 'My Log'!P13:P, 'My Log'!R13:R},
  'My Log'!O13:O = "Y",
  'My Log'!N13:N = B1),
  {"","","","","","","","","","",""}),
  IFERROR(ARRAYFORMULA(IF(ROW('My Log'!B13:B) <= COUNTA(FILTER({'My Log'!B13:B},'My Log'!O13:O="Y",'My Log'!N13:N=B1)), 'My Newvalue'!C1, "")), "")
}, 1, TRUE)

原理说明 #

1. 先通过FILTER获取符合条件的原始数据，用IFERROR处理无匹配结果的情况。
2. 利用ARRAYFORMULA+SEQUENCE生成与过滤结果行数一致的列，每个单元格填充'My Newvalue'!C1的值。
3. 将原始数据列和新增的固定值列合并成二维数组，最后用SORT完成排序。

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