如何在Google Sheets中按问题组计算多组数据的Standard Deviation？

场景说明 #

现有多名受访者（例：4人），每人回答若干问题（例：10个），问题分为Basic、Advanced、Other三个组别。需基于以下表格结构计算每组的标准差：

• A:K列：受访者答案数据（A列为问题标识，B:K为各受访者对应问题的答案）
• M:N列：问题分组映射（M列为问题标识，N列为对应分组）
• Q列：输出每个分组的标准差

解决方案公式 #

假设Q列依次为各分组的结果单元格（如Q2对应Basic、Q3对应Advanced、Q4对应Other），可使用以下公式：

1. 硬编码分组名称的公式

针对Basic组（输入到Q2）：

=STDEV.S(FLATTEN(FILTER(B:K, ISNUMBER(XMATCH(A:A, FILTER(M:M, N:N="Basic"))))))

针对Advanced组（输入到Q3）：

=STDEV.S(FLATTEN(FILTER(B:K, ISNUMBER(XMATCH(A:A, FILTER(M:M, N:N="Advanced"))))))

针对Other组（输入到Q4）：

=STDEV.S(FLATTEN(FILTER(B:K, ISNUMBER(XMATCH(A:A, FILTER(M:M, N:N="Other"))))))

2. 引用分组名称的动态公式

如果Q列单元格已填写分组名称（如Q2单元格内容为Basic），可使用动态公式，下拉即可批量计算：

=STDEV.S(FLATTEN(FILTER(B:K, ISNUMBER(XMATCH(A:A, FILTER(M:M, N:N=Q2))))))

公式逻辑说明 #

• FILTER(M:M, N:N="Basic")：提取所有属于目标分组的问题标识列表
• XMATCH(A:A, ...)：判断A列的问题是否在目标分组列表中，返回匹配位置或错误值
• ISNUMBER(...)：将匹配结果转为布尔值，筛选出对应问题的所有受访者答案
• FLATTEN(...)：把二维的答案区域转为一维数组，确保标准差函数能计算所有样本
• STDEV.S(...)：计算样本标准差；若需计算总体标准差，替换为STDEV.P

注意事项 #

• 确保A列与M列的问题标识完全一致（无空格、大小写统一），否则匹配会失效
• 空白单元格会被自动忽略，不影响计算结果

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