Excel数据转置后σ计算公式修正技术咨询

原竖向数据结构与公式 #

• A列：序号1-84
• B列：Volume 1数据
• C列：Volume 2数据
• D列：对应行的平均值

原数组公式（需按Ctrl+Shift+Enter执行）：

=SQRT(SUM((B2:C85-D2:D85)^2)/((COUNT(B2:C2)-1)*COUNT(A2:A85)))/(GAMMA((COUNT(A2:A85)/2))*SQRT(2/(COUNT(A2:A85)-1))/GAMMA((COUNT(A2:A85)-1)/2))

转置后的横向数据结构 #

• 第1行：序号1-84
• 第2行：Volume 1数据
• 第3行：Volume 2数据
• 第4行：对应列的平均值

原横向公式的问题点 #

你编写的横向公式结果不符，核心错误在于：

1. 平方和计算冗余且逻辑对应偏差，原竖向公式是一次性计算所有数据与对应平均值的平方差总和，你拆分的两个SUMPRODUCT没必要，且未直接复用已计算好的第4行平均值；
2. 分母的(COUNTA(B2:CG2)-1)*COUNTA(B2:CG2)逻辑错误，原公式是(每组样本数-1)*组数，这里错误地将组数减1参与计算，而非每组样本数减1。

修正后的横向公式 #

=SQRT(SUM((B2:CG2-B4:CG4)^2+(B3:CG3-B4:CG4)^2)/((COUNT(B2:B3)-1)*COUNTA(B2:CG2)))/(GAMMA(COUNTA(B2:CG2)/2)*SQRT(2/(COUNTA(B2:CG2)-1))/GAMMA((COUNTA(B2:CG2)-1)/2))

修正说明 #

• 平方和部分：直接调用第4行的平均值，用SUM一次性计算所有数据与对应平均值的平方差总和，和原竖向公式逻辑完全对齐；
• 分母部分：COUNT(B2:B3)获取每组样本数（固定为2），减1后乘以COUNTA(B2:CG2)得到的组数（84），和原公式的(COUNT(B2:C2)-1)*COUNT(A2:A85)完全匹配；
• Gamma函数计算部分：因逻辑是基于组数（84）推导，无需修改，和原公式保持一致。

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