求Google Sheets单公式(ARRAYFORMULA/QUERY)：按类别拼接指定日期前的匹配行

针对5万+行的大规模数据场景，直接使用嵌套FILTER/QUERY的ARRAYFORMULA会因重复运算导致工作表卡顿，以下是高效的原生公式优化方案：

核心优化公式 #

假设：

• 第一个工作表（Sheet1）：A列存类别，B列存日期
• 第二个工作表（Sheet2）：A列存目标日期，B列存类别

方案一：预处理分组+精准提取 #

1. 在Sheet1新增辅助列（如C列），提前按类别分组拼接所有日期：

=ARRAYFORMULA(IFERROR(VLOOKUP(A:A, QUERY(A:B, "SELECT A, TEXTJOIN(', ', TRUE, B) GROUP BY A"), 2, FALSE), ""))

2. 在Sheet2的C列输入数组公式，提取当前类别下早于目标日期的所有日期：

=ARRAYFORMULA(IF(Sheet2!A:A="", "", TEXTJOIN(", ", TRUE, FILTER(SPLIT(VLOOKUP(Sheet2!B:B, Sheet1!A:C, 3, FALSE), ", "), DATEVALUE(SPLIT(VLOOKUP(Sheet2!B:B, Sheet1!A:C, 3, FALSE), ", ")) < Sheet2!A:A))))

方案二：简化版数组公式（适用于数据量稍小的场景） #

若预处理操作受限，可使用单个数组公式（性能略逊于方案一，但远优于逐行复制）：

=ARRAYFORMULA(IFERROR(TEXTJOIN(", ", TRUE, FILTER(Sheet1!B:B, Sheet1!A:A=Sheet2!B:B, Sheet1!B:B<Sheet2!A:A)), ""))

性能优化要点 #

• 提前按类别分组拼接日期，避免每行重复遍历全表，将运算量从O(n²)降至O(n)
• 优先使用原生数组函数，避免自定义脚本或IMPORTRANGE带来的权限与性能问题
• 避免在数组公式中嵌套过多FILTER/QUERY调用，减少重复运算

内容的提问来源于stack exchange，提问作者Aleister Tanek Javas Mraz