excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)标准差是样本的标准差和总体的标准差。前者是指从人群中提取一些数据作为样本来计算标准差,后者是指计算所有数据的标准差,现实中往往不可能得到所有数据

大家好,欢迎来到IT知识分享网。

标准差是样本的标准差和总体的标准差。前者是指从人群中提取一些数据作为样本来计算标准差,后者是指计算所有数据的标准差,现实中往往不可能得到所有数据。因此,它通常计算样本的标准差。Excel标准差计算有6个函数,但实际上只有4个,因为有两个新函数只用来代替旧函数,所以它们与旧函数相同。Stdev、Stdev.S和StdevA用于计算样本的标准偏差。Stdev P、Stdev.P和StdevPA用于计算整个总体的标准差。除了计算一组值的标准偏差外,还可以计算满足两个或两个以上条件的标准偏差。此外,标准差功能还可以与抵销和匹配功能相结合,自动计算标准差,并在添加数据后计算最近几天的标准差。

一、 Excel中标准差的计算功能

(一) 样品标准差的计算函数

1、表达

Stdev函数表达式:Stdev(Number1,[Number2],…)

Stdev.S函数表达式:Stdev.S(Number1,[Number2],…)

StdevA函数表达式:StdevA(Value1,[Value2],…)

2、说明:

A、 Stdev函数和Stdev.S函数用于计算样本的标准差,它们必须至少使用一个参数,最多255个;Stdev.S函数是在Excel 2010中添加的,用于替换Stdev函数,在以后的Excel版本中,Stdev函数可能不再受支持。

B. The StdevA函数可以计算逻辑的样本的标准偏差(真或假)和文本值,除了计算值的样本的标准偏差外,还必须具有至少一个值和最大值为255个值。

C、 Stdevn(Stdev.S)和StdevA函数的区别:它们的区别仅限于如何处理逻辑值和文本值,对于逻辑值和文本值,如果直接输入到参数列表中,它们将被Stdev函数包含在标准差的计算中,True转换为1,False转换为0;如果它们位于要引用的数组或单元格中,Stdev函数将忽略它们。StdevA函数将逻辑值和文本值计算为标准偏差,而不管它们是在哪里输入的。此外,如果参数列表中有无法转换为数值的文本,则它们都将返回错误;如果数组或单元格中有无法转换为数值的文本,则将忽略这些文本。

(二) 人口标准差的计算函数

1、表达

StdevP函数表达式:StdevP(Number1,[Number2],…)

Stdev.S函数表达式:Stdev.S(Number1,[Number2],…)

StdevA函数表达式:StdevA(Value1,[Value2],…)

2、说明:

A、 Stdev P函数和Stdev.P函数也用于计算值填充的标准差,它们必须至少使用一个参数,最多255个;Stdev.P函数也添加在Excel 2010中,用于替换StdevP函数,StdevP函数可能在将来的Excel版本中被放弃。

B. STDEVPA函数用于计算逻辑(真或假)和文本值的标准偏差(除了用于计算值的总体标准偏差)之外,还必须具有至少一个值和最大值为255个值。

C、 Stdev P函数(Stdev.P函数)和StdevPA函数的区别:它们的区别也仅限于如何处理逻辑值和文本值,对于逻辑值和文本值,如果它们直接输入到参数列表中,它们将包含在StdevP函数的标准差计算中,True将转换为1,False转换为0;如果它们位于要引用的数组或单元格中,StdevP函数将忽略它们。StdevPA函数在计算标准偏差时计算逻辑值和文本值。此外,如果参数列表中有无法转换为数值的文本,则它们都将返回错误;如果数组或单元格中有无法转换为数值的文本,则它们也将被忽略。

二、 如何在excel中计算标准差(样本标准差)

(一) 使用Stdev(Stdev.S)计算值的标准偏差

1、如果你想计算英语成绩样本的标准差。双击单元格B2,将公式=STDEV(B2:B18)复制到B2,按Enter键,返回8.;双击B3,将公式=STDEV.S(B2:B18)复制到B3,按Enter键,返回8.;选择B2:B3,当前选项卡为“Home”,单击“General”右侧的黑色小倒三角形,在弹出的选项中选择“Number”,标准差四舍五入到小数点后两位;操作步骤如图1所示:

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

图1

2、公式说明:

A、 B2:B18是计算标准差要引用的单元格,两个公式返回的结果相同,表明Stdev的工作原理与Stdev.S相同。

B、 默认情况下,公式返回的结果会四舍五入到多个小数位,在演示中,只有两个小数位会四舍五入到多个小数位。如果要舍入到其他小数位,可以按Ctrl+1打开“格式单元格”窗口,选择“数字”页签,然后选择左侧的“数字”,然后在右侧的“小数位”中输入具体值,单击“确定”。

(二) 正确和错误都会被计算和忽略

1、标准差的计算中包括真假

A、 双击单元格A1,将公式=STDEV(24,27)复制到A1,按回车键,返回2.;双击A2,将公式=STDEV(24,27,TRUE)复制到A2,按回车键,返回14.;双击A3,将公式=STDEV(24,27,FALSE)复制到A3,按回车键,返回14.;操作步骤如图2所示:

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

图2

B、 公式说明:

公式=STDEV(24,27,TRUE)和=STDEV(24,27,FALSE)返回的结果与=STDEV(24,27)不同,这表示计算的标准偏差中同时计算了TRUE和FALSE,这表示逻辑值直接写入STDEV函数的参数列表,并包含在标准偏差中。

2、计算标准偏差时忽略真值和假值

A、 双击单元格C2,将公式=STDEV(A2:A3)复制到C2,按Enter键,返回到2.;双击C3,将相同的公式复制到C3,然后将公式更改为=STDEV(A2:A4),按Enter键,也返回2.;选择B4,将TRUE更改为false,单击C3,C3中的值没有更改;双击C6,复制公式=STDEV({24,27,TRUE,FALSE})到C2,按Enter,也返回2.;操作过程步骤如图3所示:

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

图3

)-B.配方说明:

从演示中可以看到,每个公式返回相同的结果,而不管它是否包含True或False,这表明Stdev函数在单元格或数组中忽略True和False。

(三) 有些文本可以转换为数值,但会被计算和忽略。

1、双击单元格A1,将公式=STDEV(2.58,2.39,“2.67”)复制到A1,按回车键,返回0.;再次双击A1,删除,“2.67”,按回车键,返回0.;双击A2,将公式=STDEV(B1:B3)复制到A2,按回车键,返回0.;操作步骤如图4所示:

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

图4

2、公式说明:

由公式=STDEV(2.58,2.39,“2.67”)返回的结果与由=STDEV(2.58,2.39)返回的结果不同,表明标准偏差计算中包含数字文本“2.67”;由公式=STDEV(B1:B3)和=STDEV(2.58,2.39)返回的结果相同,表明B3中的2.67不被计算在内在计算出的标准差中,因为它是文本。

(四) 返回错误,因为有些文本无法转换为数值并被忽略

1、双击单元格A1,将公式=STDEV(2.58,2.39,“Ten”)复制到A1,按Enter键,返回值error#value!;双击A2,将公式=STDEV(B1:B3)复制到A2,按回车键,返回0.,操作步骤如图5所示:

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

图5

2、公式说明:

这两个公式计算相同值和文本的标准差。formula=STDEV(2.58,2.39,“Ten”)返回一个错误,指示无法转换为值的文本无法写入参数列表;相反,formula=STDEV(B1:B3)可以返回正确的值,指示要引用的数组或单元格具有无法转换为值的文本,在不影响计算的情况下忽略文本。

(一) 单元格中有要引用的数值、文本值和逻辑值

1、双击单元格B2,将公式=STDEVA(A2:A6)复制到B2,按回车键返回47.00;选择A4,删除86,B2中的值变为49.,选择A5,删除True,B2中的值变为49.,删除A6中的False,B2中的值变为5.,操作步骤如图6所示:

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

图6

2、公式说明:

A2和A3中的值是数字,A4中的值是文本。删除A4中的值时,标准偏差的结果从47.00更改为49.,表示StdevA函数将单元格中的文本计数为计算出的标准偏差。依次删除True和False时,标准差的结果也会发生变化,表明StdevA函数将逻辑值计算为标准差。

(二) StdevA和Stdev比较并计数文本数字计数和忽略的示例

1、无论文本值是直接写入参数列表还是写入单元格,它们都由StdevA包含在标准偏差中,但对小数点的处理不同。例如,E3、E4和E5中的计算结果不同,表示StdevA将文本值计算为标准差;当直接将“2.67”写入参数列表时,文本类型“2.67”将转换为数字2.67,当单元格中的2.67为文本时,它被小数点替换,然后转换成一个数字。转换成值后的结果是267。这可以通过将C5中的2.67更改为267来确认,而E5中的结果没有更改。演示如图7所示:

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

图7

2、StdevA和Stdev对于文本值的区别是:Stdev只统计直接写入标准值的参数列表中的文本值“2.67”,StdevA统计参数列表中的文本值“2.67”和标准偏差中的单元格。在图7中,相同颜色的计算结果相同,即以相同的方式处理文本值。

三、 如何在excel中计算标准差(总体标准差)

(一) 用Stdev P和Stdev.P计算总体标准差的一个例子

1、双击单元格B2,将公式=STDEVP(A2:A6)复制到B2,按回车键,返回0.;双击B3,将同一公式复制到B3,然后在P之前添加一个点,公式变为=STDEV.P(A2:A6),按回车键,返回相同结果;操作步骤如图8所示:

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

图8

2、公式说明:

公式=STDEV P(A2:A6)和=STDEV.P(A2:A6)返回相同的结果,表明它们共同计算值的总体标准差。

(二) 使用StdevPA计算包含数值、文本值和逻辑值的总体的标准偏差

1、双击单元格B2,将公式=STDEVPA(A2:A6)复制到B2,按回车键,返回3.;选择A4,删除文本值8.16,B2中的值变为3.;删除A5中的False,B2中的值变为3.;删除A6中的True,B2中的值变为0.205;操作步骤如图9所示:

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

图9

2、公式说明:

删除A4中的文本值时,标准差的结果发生变化,表示StdevPA函数将文本值计入标准差;然后删除True和False,标准差的结果也发生变化,表示StdevPA函数也将逻辑值计入标准差。

提示:StdevP和StdevPA函数在处理数字文本、逻辑值和无法转换为值的文本方面的区别,请参阅Stdev和StdevA函数之间的区别。

四、 Excel标准差计算功能的应用实例

(一) 计算满足两个标准的样品的标准偏差

1、如果你想计算样品在纽约销售“毛衣”的标准差。双击单元格D12,将公式=STDEV((B2:B11=“Sweater”)*(C2:C11=“New York”)*(D2:D11))复制到D12,按Ctrl+Shift+Enter返回结果728.2405;操作过程步骤如图10所示:

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

图10

2、公式=STDEV((B2:B11=“毛衣”)*(C2:C11=“纽约”)*(D2:D11))说明:

A、 B2:B11=“Sweater”是公式的第一个条件,意思是:在B2:B11中查找“Sweater”,如果是“Sweater”,则返回True,否则返回False。在执行时,B2第一次从B2:B11中取出;因为B2是“羽绒服”,所以返回False;B3第二次从B2:B11中取出,并且由于B3是“便服”,所以也返回False;other等等,最后返回数组{False;False;False;TRUE;False;TRUE;TRUE;False;TRUE;TRUE;TRUE;False;False;TRUE}。

B、 C2:C11=“New York”是公式的第二个标准,意思是:在C2:C11中查找“New York”。如果是“纽约”,则返回True,否则返回False,最后返回数组{True;False;True;True;False;True;False;False}。

C、 然后(B2:B11=“Sweater”)*(C2:C11=“New York”)变成{FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}*{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE},然后,首先,第一个数组的第一个元素乘以第二个数组的第一个元素,结果是0(因为TRUE被转换为1,所以FALSE被转换为第二,获取第一个数组的第二个元素,并将第二个数组的第二个元素相乘,结果为0;其他元素依此类推,最后返回数组{0;0;0;1;0;1;0;0;0;0;0}。

D、 D2:D11将D2到D11中的值作为数组返回,该数组返回{854;765;1895;1023;754;2180;1467;987;2353;1584}。

E、 然后公式变为=STDEV({0;0;0;1;0;0;0;0}{763;692;1090;969;583;1286;990;686;960;986}),然后,将两个数组的相应元素相乘,公式进一步更改为=STDEV({0;0;0;969;0;1286;0;0;0;0}),最后由STDEV获得样本的标准偏差。

提示:以上是计算符合两个标准的样品的标准差。如果要计算满足三个或更多条件的样本的标准偏差,可以在公式的第二个*之后继续添加条件。另外,如果要计算总体的标准差,请将Stdev更改为StdevP。

(二) Stdev+OffSet+Match组合,新增数据后自动计算标准差,计算最近几天的标准差

(一) 计算所有数据样本的标准差

1、如果需要自动计算每个附加“指标”的指标样本的标准差。双击单元格C3,将公式=STDEV(偏移量(B1,1,匹配(9E+307,B:B)-1))复制到C3,按回车键,返回结果71.0624;选择A8,输入“7”,按Tab键将光标移到B8,输入837,单击A9,C3中的值将自动更改为64.9300,即增加一行后,自动重新计算包含新增数据的标准差;继续在A9和B9中输入数据,按回车键,C3中的值自动变为68.3013;操作步骤如图11所示:

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

图11

2、公式=标准偏差(偏移量(B1,1,匹配(9E+307,B:B)-1))说明:

A.、9E+ 307是Excel所允许的最大值,用于表示最后一行。MATCH(9E+307,B:B)用于返回B列中最后一行的位置;当表只有7行时,返回7;MATCH省略最后一个参数,默认为1,因为第二个参数B:B后面没有逗号,这意味着:在B列中查找小于等于9E+ 307的最大值,并将其返回B列中的位置。

B、 然后OFFSET(B1,1,MATCH(9E+307,B:B)-1)变为OFFSET(B1,1,7-1),然后基于B1,返回对B1下1行与B1右侧0列相交处的单元格的引用,高度返回6,宽度返回1,即返回$B$2:$B$7。OffSet函数省略第三个参数列号,默认值为0。第五个参数宽度也省略,默认宽度与B1相同,即取1。

C、 公式变成=STDEV($B$2:$B$7),最后计算B2:B7中所有值的样本标准偏差。

D、 在表后添加行时,MATCH(9E+307,B:B)返回8,OFFSET(B1,1,8-1)返回$B$2:$B$8,它只包含新添加的行。因此,添加新数据后,新数据会自动计入标准差。

(二) 计算最近7天样品的标准差

1、双击单元格D3,将公式=STDEV(OFFSET(B1,MATCH(9E+307,B:B)-1,,-7))复制到D3,按Enter键,返回结果58.5800;操作过程如图12所示:

excel中计算标准差(6函数,带Stdev、Stdev.S、StdevA、StdevP)

图12

2、公式=标准偏差(偏移量(B1,匹配(9E+307,B:B)-1,,-7))说明:

A、 MATCH(9E+307,B:B)还用于返回表中B列最后一行的位置,结果是9。

B、 然后OFFSET(B1,MATCH(9E+307,B:B)-1,,-7)变为OFFSET(B1,9-1,,-7),然后,基于B1,返回对B1下8行与B1右侧0列相交处的单元格的引用,高度为-7,宽度为1,即返回$B$3:$B$9。接下来的8行B1到B9,-7表示从下到上,这里,它的意思是:从B9到上7行,也就是B3。

C、 公式变成=STDEV($B$3:$B$9),最后计算B3:B9中值的样本标准差,即计算最近7天的标准差。如果要计算5或14天,请将-7改为-5或-14。

免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://yundeesoft.com/82933.html

(0)

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

关注微信