在 Excel 当中计算方差和标准差,核心是调用内置统计函数,重点区分样本统计和总体统计两套函数,样本方差使用 VAR.S,总体方差使用 VAR.P,样本标准差使用 STDEV.S,总体标准差使用 STDEV.P,很多用户出错就是混淆样本和总体,样本是抽取一部分数据做推断,分母是数据个数减一,总体是全部数据,分母是数据总个数,两者计算结果会有差别,方差是数值偏离平均值的平方平均,标准差是方差开平方,单位和原始数据保持一致,是数据分析里面衡量数据离散程度最常用指标,函数支持连续单元格区域引用,也支持多个不连续区域,还可以搭配 IFERROR 做容错,新版 Excel 旧版 Excel 函数名称有差异,旧版 VAR STDEV 属于兼容函数,新版推荐使用带后缀 S 和 P 的新版函数。

基础计算步骤

1 提前把全部待计算数据录入同一列或者同一行,例如数据放在 A2 到 A10 单元格区域

2 点击存放结果的空白单元格

3 如果计算样本方差,输入公式 =VAR.S (A2:A10)

4 如果计算总体方差,输入公式 =VAR.P (A2:A10)

5 如果计算样本标准差,输入公式 =STDEV.S (A2:A10)

6 如果计算总体标准差,输入公式 =STDEV.P (A2:A10)

7 按下回车键,单元格输出计算结果,需要多组数据分别计算,可以复制公式修改数据区域

函数对比表格

函数名称统计类型公式示例分母规则适用场景
VAR.S样本方差=VAR.S(A2:A10)n 减 1抽样数据,用样本推断总体离散程度
VAR.P总体方差=VAR.P(A2:A10)n全部数据就是研究整体
STDEV.S样本标准差=STDEV.S(A2:A10)n 减 1抽样数据标准差
STDEV.P总体标准差=STDEV.P(A2:A10)n全部数据整体标准差
VAR旧版兼容样本方差=VAR(A2:A10)n 减 1老版本表格兼容,不推荐新表使用
STDEV旧版兼容样本标准差=STDEV(A2:A10)n 减 1老版本表格兼容,不推荐新表使用

进阶深度要点

函数计算的时候,只会识别区域里面的数值,单元格里面的文字空白单元格逻辑值会自动忽略,如果区域里面全部都是文本,函数会返回错误值 DIV/0,代表没有有效数值可以参与方差和标准差计算。

如果需要分组计算方差和标准差,也就是按类别分别统计,可以搭配 AVERAGEIFS 之外,新版 Excel 可以使用动态数组函数,或者使用数据透视表,在数据透视表值字段设置里面,直接选择方差和标准差汇总方式,适合大批量分组统计,不需要手动写多组公式。

方差单位是原始数据单位的平方,标准差单位和原始数据相同,所以业务分析一般优先看标准差,方差更多用于统计推导计算。如果数据里面存在异常极值,方差和标准差会被极值放大,因为是平方运算,极端大数值会明显拉高结果,这个是指标本身特性,分析的时候需要留意。

如果公式提示 NAME 错误,检查函数名称拼写,低版本 Excel 不支持 VAR.S VAR.P STDEV.S STDEV.P,这种情况就改用旧版 VAR 和 STDEV 函数。如果需要排除零值再计算方差和标准差,需要搭配数组公式筛选有效数值,把等于零的数据剔除之后再传入统计区域。

如果同时需要方差和标准差,可以在一个单元格算出方差,另一个单元格使用 SQRT 函数,对方差开平方得到标准差,公式 = SQRT (VAR.S (A2:A10)),这个方式可以核对函数计算结果是否正确。

完成方差和标准差计算之后,要确认选择的是样本统计还是总体统计,不要选错函数,否则统计结论会出现偏差,分组统计场景优先使用数据透视表提升处理效率。

【常见问题】

问题 1 excel 算方差和标准差样本和总体怎么区分

回答 1 excel 算方差和标准差的时候,样本统计使用 VAR.S 和 STDEV.S,分母是 n 减 1,总体统计使用 VAR.P 和 STDEV.P,分母是 n,如果数据只是抽样部分,就选择样本函数,如果是全部完整数据,就选择总体函数。

问题 2 excel 算方差和标准差出现 DIV/0 是什么原因

回答 2 excel 算方差和标准差出现 DIV/0 错误,代表选中的数据区域里面没有有效数值,全部是文字空白单元格,函数无法计算方差和标准差,检查数据区域,确认单元格是数值格式,补充有效数字之后公式就可以正常运算。

问题 3 excel 算方差和标准差可以分组统计吗

回答 3 excel 算方差和标准差支持分组统计,可以使用数据透视表,值汇总方式选择方差或者标准差,也可以手动写函数公式按分组区域单独计算,适合多类别数据批量计算方差和标准差。

【免责申明】

本文内容基于 Excel 常规版本操作编写,不同版本函数支持与界面细节可能存在差异,操作仅供学习参考,统计分析结果仅作参考,重要业务数据请提前备份,因操作失误造成的数据损失,由使用者自行承担。