excel标准差的计算公式函数 满足指定条件的标准差计算教程

microsoft office excel 2007(办公软件) v12.0.6612.1000

★★★★☆

办公教育 | 大小:102.14MB

查 看

excel2016 v2016

★★★★☆

办公教育 | 大小:5.75MB

查 看

Excel2025 永久激活密钥版

★★★★☆

办公教育 | 大小:1GB

查 看

很多小伙伴使用Excel办公软的时候不知道怎么去计算标准差，Excel标准差的计算一共有6个函数，不同的函数计算的方法还是有差异，有些是计算样本标准差，还有是计算整体标准差。如果想要计算特定条件下是标准差那可以用*组合多个条件或者与 OffSet、Match函数组合，如果你还分不清楚的话可以一起来学习一下具体的操作方法，一起来学习一下吧！

一、Excel标准差的计算方法

(一)用Stdev函数计算样本标准差

假如要计算服装每月销量的标准差。选中 C2 单元格，输入公式 =STDEV(B2:B7)，按回车，返回结果 176.1836，操作过程步骤，如图1所示：

1

(二)用StdevP函数计算总体标准差

同样以计算服装每月销量的标准差为例。双击 C4 单元格，把公式 =STDEVP(B2:B7) 复制到 C4，按回车，返回结果 160.8329，操作过程步骤，如图2所示：

2

二、Excel计算满足指定条件的标准差

(一)求同时满足两个条件的标准差

1、假如要求羽绒服在上海的销量的标准差。双击 D11 单元格，把公式 =STDEV((B2:B10="羽绒服")*(C2:C10="上海")*(D2:D10)) 复制到 D11，如图3所示：

3

2、按 Ctrl + Shift + 回车，返回结果 481.1830，如图4所示：

图4

3、公式说明：

A、(B2:B10="羽绒服") 为公式的一个条件，意思是在 B2 至 B10 中找出所有等于“羽绒服”服装;执行时，第一次取出 B2，如果等于“羽绒服”返回 True，否则返回 False，由于 B2 中的内容是“羽绒服”，因此返回 True;第二次取出 B3，由于内容为“休闲西服”，所以返回 False，其它的以此类推，最后返回数组 {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}。

B、(C2:C10="上海") 为公式的第二个条件，用于在 C2 至 C10 中找出所有销售地区为“上海”的服装;它与条件 (B2:B10="羽绒服") 是一个意思，最后返回数组 {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}。

C、则 (B2:B10="羽绒服")*(C2:C10="上海") 变为 {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}*{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}，接着，把两个数组对应的元素相乘，相乘时 TRUE 被转为 1、FALSE 被转为 0，相乘结果为 {1;0;1;0;0;0;1;0;0}。

D、D2:D10 以数组形式返回 D2 至 D10 的值，即返回 {763;692;1090;969;583;1286;990;686;960}。

E、则公式变为 =STDEV({1;0;1;0;0;0;1;0;0}*{763;692;1090;969;583;1286;990;686;960})，进一步计算再把两个数组的对应元素相乘，则公式变为 =STDEV({763;0;1090;0;0;0;990;0;0})，最后对数组求标准差。

提示：如果要求总体标准差，只需把公式中 STDEV 改为 STDEVP，其它的保持不变。

(二)添加数据后自动计算标准差

(1)从起始行计算到末尾行

1、有一个收入表，要求每添加一条记录自动计算包含新收入的标准差。双击 C2 单元格，把公式 =STDEV(OFFSET(B1,1,,MATCH(E9+307,B:B)-1)) 复制到 C2，按回车，返回 3.2408;双击 A9，输入“8日”，把光标移到 B9，输入 89.8，则 C2 中的值变为 4.3078;接着再在下一行输入“9日和88.2”，标准差变为 4.5060;每次增加一行收入都自动计算了标准差，操作过程步骤，如图5所示：

图5

2、公式说明：

A、E9+307 是 Excel 中能输入的最大值，在这里表示最后一行;MATCH(E9+307,B:B)-1 意思是返回 B 列的最后一行位置(值为 8)，按住 Alt，依次按 M 和 V，打开“公式求值”窗口，一直求值到计算完Match函数部分可知，如图6所示：

图6

B、再用 8 减 1，则公式变为 =STDEV(OFFSET($B$1,1,,7))，接着用 OffSet 返回 B1 下 1 行 0 列且高度为 7 的单元格引用，即返回 $B$2:$B$8;则公式变为 =STDEV($B$2:$B$8)，最后对 B2:B8 求标准差。

C、当在表格后新增一行后，OffSet 返回的是 B2:B9，自己可以用上述方法打开“公式求值”窗口测试。

(2)计算最近指定天数的标准差

1、假如在表格后面新增记录后，只计算最近 7 天的收入。双击 C4 单元格，把公式 =STDEV(OFFSET(B1,MATCH(9E+307,B:B)-1,,-7)) 复制到 C4，按回车，返回 3.2408;同样在后面增加两行数据，则 C4 中的值自动变化;操作过程步骤，如图7所示：

2、公式说明：

A、公式与上一个公式相似，只是把 Match 返回位置作为 OffSet 的行数，当表格只有 8 行时，Match 返回值也为 8，则 OffSet 变为 OFFSET(B1,8-1,,-7)，也就是返回 B1 下 7 行 0 列且高度为 -7 的单元格引用，关键是 -7，B1 下 7 行就是 B8，而 -7 表示 B8 往上 7 行，恰好是 B2，即 OffSet 也返回对 B2:B8 的引用。当增加一行后，Match 返回的是 9，则 OffSet 返回的是 B9 上 7 行到 B9 的引用，即 B3:B9。

B、公式中的 -7 表示最近 7 天，从以上的推理可知，每增加一行，OffSet 总能返回从最后一行到往上 7 行的引用，从而确保总能计算最近 7 天的标准差;如果要计算其它指定天数的标准差，只需把 -7 改为相应数值。

三、Excel标准差计算六个函数 Stdev、Stdev.S、StdevA、StdevP、Stdev.P、StdevPA 的区别

标准差又称为均方差，分为样本和总体两种，其中样本标准差是指从一组数据中抽取样本来计算，总体标准差是指取所有数据来计算;平常计算标准差通常只计算样本标准差，因为大多情况下不易于取得所有数据。在 Excel 中，计算标准差有六个函数，分别为：Stdev、Stdev.S、StdevA、StdevP、Stdev.P、StdevPA，它们区别如下：

1、计算样本标准差的函数为：Stdev、Stdev.S、StdevA;其中 Stdev 和 Stdev.S 只有版本区别，前者是旧版本，后者是新版本，即 Stdev.S 是 Excel 2010 新增的函数;而 Stdev 与 StdevA 的区别为：前者只用于计算数值的标准差，后者能计算数值、字符串和逻辑值(真为 1、假为 0)的标准差。

2、计算总体标准差的函数为：StdevP、Stdev.P、StdevPA;其中 StdevP 与 Stdev.P 也是新旧版本之别，前者用于 Excel 2007 及以下版本，后者用于 Excel 2010 及以上版本;StdevP 与 StdevPA 也是计算范围的区别，前者忽略文本和逻辑值，后者则包含。