将复杂Map公式移至Google Sheets表头的报错问题求助

原公式说明 #

我有一段用于匹配ID并按日期区间求和的Google Sheets复杂公式：

=map(A2:index(A:A,match(,0/(A:A<>""))),lambda(Σ,if(Σ="",,map(BZ1:CW1,lambda(Λ,let(x,index(sumifs('Ref4'!Q:Q,'Ref4'!G:G,Σ,--'Ref4'!P:P,">="&Λ,--'Ref4'!P:P,"<"&if(day(Λ)=1,offset(Λ,,1),eomonth(Λ,)))),if(x=0,,x)))))))

该公式的作用是匹配指定ID，同时统计日期落在对应区间内的数值总和。

需求背景 #

由于经常对表格进行高级排序（Data-->Sort Range-->Advanced Range Sorting Options），原公式位置会随排序变动导致计算出错，因此需要将公式移至表头，理想位置为BY1或BZ1：

• 若放在BZ1，需保留原单元格的日期值01/01/24

我之前使用过的表头数组公式示例如下：

={"Total Harvest kg";
 ARRAYFORMULA(SUMPRODUCT(('Ref4'!$G$2:$G = TEXT($A$2:$A, "0")) * 'Ref4'!$Q$2:$Q))}

错误尝试与问题 #

当我尝试将原公式改写成表头数组形式时：

={"Don't Delete";
map(A2:index(A:A,match(,0/(A:A<>""))),lambda(Σ,if(Σ="",,map(BZ1:CW1,lambda(Λ,let(x,index(sumifs('Ref4'!Q:Q,'Ref4'!G:G,Σ,--'Ref4'!P:P,">="&Λ,--'Ref4'!P:P,"<"&if(day(Λ)=1,offset(Λ,,1),eomonth(Λ,)))),if(x=0,,x)))))))
 }

出现#VALUE!错误，提示：In ARRAY_LITERAL, an Array Literal was missing values for one or more rows。

解决方案 #

原因分析 #

报错的核心是数组字面量结构不匹配：

• 第一行"Don't Delete"是单个单元格值（仅1列）
• 第二行的MAP公式返回二维数组（行数等于A列非空ID数，列数等于BZ1:CW1的列数）
  两者列数不一致，导致数组字面量无法正常拼接。

修正后的公式 #

方案1：放在BY1单元格

如果将公式放在BY1，需要让表头文本横向扩展为和BZ1:CW1相同的列数，确保上下数组列数一致：

={BYCOL(BZ1:CW1,lambda(_,"Don't Delete"));
 map(A2:index(A:A,match(,0/(A:A<>""))),lambda(Σ,if(Σ="",,map(BZ1:CW1,lambda(Λ,let(x,index(sumifs('Ref4'!Q:Q,'Ref4'!G:G,Σ,--'Ref4'!P:P,">="&Λ,--'Ref4'!P:P,"<"&if(day(Λ)=1,offset(Λ,,1),eomonth(Λ,)))),if(x=0,,x)))))))}

• 第一行用BYCOL将文本重复填充为和BZ1:CW1相同的列数，保证上下数组列数匹配
• 第二行保留原MAP逻辑，正常返回对应行的计算结果

方案2：放在BZ1单元格（保留原日期）

如果要放在BZ1并保留原日期01/01/24，直接将表头行设置为原日期范围，再拼接计算结果：

={BZ1:CW1;
 map(A2:index(A:A,match(,0/(A:A<>""))),lambda(Σ,if(Σ="",,map(BZ1:CW1,lambda(Λ,let(x,index(sumifs('Ref4'!Q:Q,'Ref4'!G:G,Σ,--'Ref4'!P:P,">="&Λ,--'Ref4'!P:P,"<"&if(day(Λ)=1,offset(Λ,,1),eomonth(Λ,)))),if(x=0,,x)))))))}

• 第一行直接引用原日期范围BZ1:CW1，保留原有日期值
• 第二行的MAP公式返回对应行的计算结果，和表头列数完全匹配，不会报错

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