Excel 2019替代Google Sheets FILTER函数的方法及公式咨询

在Excel 2019中，可使用INDEX+SMALL+IF的数组公式组合替代FILTER函数，实现从ALGORITHM工作表中筛选C列值为TRUE的日期，并填充到MATRIX工作表的A5至A120000区域。

适用公式（需按数组公式方式输入） #

假设ALGORITHM工作表中的日期存储在B列（若实际日期列不同，替换为对应列标即可），在MATRIX工作表的A5单元格输入以下公式，输入完成后按Ctrl+Shift+Enter确认（Excel会自动在公式外层添加大括号{}），再将公式下拉填充至A120000：

=IFERROR(INDEX(ALGORITHM!$B:$B,SMALL(IF(ALGORITHM!$C:$C=TRUE,ROW(ALGORITHM!$C:$C)),ROW(A1))),"")

公式拆解说明 #

• IF(ALGORITHM!$C:$C=TRUE,ROW(ALGORITHM!$C:$C))：遍历ALGORITHM表的C列，标记出所有值为TRUE的单元格的行号，不符合条件的返回FALSE
• SMALL(...,ROW(A1))：按顺序提取第1、2、3...个符合条件的行号（下拉时ROW(A1)会自动变为ROW(A2)、ROW(A3)，实现依次取值）
• INDEX(ALGORITHM!$B:$B,...)：根据提取的行号，从日期列中获取对应的日期值
• IFERROR(...)：当没有更多符合条件的日期时，返回空值，避免显示#NUM!错误

注意事项 #

• 若日期列不是B列，将公式中的ALGORITHM!$B:$B替换为实际的日期列范围（如ALGORITHM!$A:$A）
• 确保ALGORITHM表的C列数据为逻辑值TRUE/FALSE，而非文本"TRUE"/"FALSE"，否则需调整公式中的判断条件为ALGORITHM!$C:$C="TRUE"

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