请求修改数据透视表最近5条结果加权平均公式（适配缺失场景）

问题描述 #

公式用于计算数据透视表最近5条结果的加权平均值，结果取值为1、2或3。当前公式在数据包含至少两类结果时正常运行，但当缺失1类或2类结果（例如仅存在3类结果）时失效。原公式如下：

={"Average last 5 results";byrow(E7:G;lambda(Σ;SI(NBVAL(Σ)=0;;let(Λ;bycol(chooserows(E7:index(G:G;LIGNE(Σ));sequence(min(LIGNE(Σ)-6;5);1;-1;-1));lambda(x;SOMME(x)));average.weighted(E6:G6;switch(Λ;;0;Λ))))))}

问题原因 #

原公式中，当某类别对应的求和结果为空（即该类别无数据）时，switch(Λ;;0;Λ)未能正确将空值转换为0，导致average.weighted函数因权重数组包含空值而无法正常计算。

修复后公式 #

={"Average last 5 results";byrow(E7:G;lambda(Σ;SI(NBVAL(Σ)=0;;let(Λ;bycol(chooserows(E7:index(G:G;LIGNE(Σ));sequence(min(LIGNE(Σ)-6;5);1;-1;-1));lambda(x;COALESCE(SOMME(x);0)));average.weighted(E6:G6;Λ)))))

修改说明 #

1. 替换空值为0：使用COALESCE(SOMME(x);0)直接将空的求和结果转换为0，确保权重数组中无空值
2. 简化逻辑：移除冗余的switch判断，因为COALESCE已完成空值处理，average.weighted可直接使用处理后的权重数组计算

验证场景 #

• 全类别数据：与原公式逻辑一致，计算结果正确
• 缺失1-2类数据：对应类别权重自动设为0，average.weighted忽略无效类别，返回正确的加权平均值

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