用EXCEL计算描述统计量
第四节 用EXCEL计算描述统计量
一、实验目的:掌握用用函数计算描述统计量。具体包括有算术平均数、调和平均数、众数、中位数、几何平均数、极差、标准差、方差、标准差系数等。
二、实验环境:使用的软件为EXCEL2003。硬件为PC机,每人一台机器。 三、实验内容与实验步骤
实验内容、原理分析及具体实验步骤见讲义。 四、实验结果总结
对实验结果进行分析,完成思考题目,总结实验的心得体会,并提出实验的改进意见。
实验内容及步骤:
一、用函数计算描述统计量
excel中内置函数有以下几种类型:数据库函数、日期和时间函数、数学和三角函数、文本函数、逻辑函数、统计函数、工程函数、信息函数、财务函数。
1、输入函数
以等号“=”开始,然后输入函数的名称,再紧跟着一对括号,括号内为一或多个参数,参数之间要用逗号隔开,例如:“=SUM(A1:B10)”。
也可以使用函数向导插入函数:选择要插入函数的单元格——插入/函数,选择相应函数,单击“确定”,弹出“函数参数”对话框——单击Number1文本框右侧的折叠按钮,用鼠标选择所需单元格区域——单击Number1文本框右侧的折叠按钮,返回“函数参数”对话框,单击“确定”按钮,结果显示在单元格中。
2、系统提供的基本函数
在“插入”菜单中选“函数”命令,可以找到常用10种常用函数。当常用函数不够时可以在“插入”菜单中选“函数”命令,在弹出的“粘贴函数”画面中,将鼠标指针指向“统计”,再进行选择。
(1)求和函数(SUM)
1)利用“自动求和”按钮∑求和
方法:选定包含数值的单元格——单击工具栏上的“自动求和”按钮∑。
=SUM(C17:C19,E17:E19)
如果选定一行中连续的单元格,则结果在选定范围的右边一格出现;如果选定一列中连
续的单元格,则结果在选定范围的下边一行出现。如果选定的是一个矩形区域,则各列的和在选定范围的下边一行各列所对应的单元格中出现。
2)利用fx求和 求数学总成绩:选中H2——单击常用工具栏上的fx(粘贴函数)——SUM——确定B2:B2区域——单击“确定”即可。单击选中B9后,也可以利用执行“插入/函数”命令来求和。
3)输入公式求和
选中B9——输入“=SUM(B2:B8)”,此时求连续区域B2:B8的所有数据之和。若输入“=SUM(B2,D2,F2)”或“=SUM(B2+D2+F2)”则计算不连续的三个数之和。
**大于等于90分用红色,60-90之间用蓝色,60以下用黑色。 选定范围(B2:F8)——执行“格式/条件格式”命令,输入条件1 如 >= 90 红色 ;“添加”条件,即为条件2: 介于 60- 90 之间 选兰色,“添加”条件 为条件3
(2)条件求和(SUMIF)
计算数学高于80分的成绩之和。选中B9,输入 =SUMIF(B2:B8,">80") 回车即可。 利用fx,选SUMIF函数(range 输入数据范围,criteria 输入条件,确定就可以求得)。 (3)均值函数
1)算术平均数(AVERAGE)
求所有参数的算术平均数。语法:AVERAGE(Number1,Number2,„„)
A、利用fx求和:选中B9——单击常用工具栏上的fx——AVERAGE——确定B2:B8区域——单击“确定”即可。
B、输入公式求和:选中B9——输入“=AVERAGE(B2:B8)”,此时求连续区域B2:B8的所有数据之和。(不连续区域数据算术平均数计算的原理一样)
2)几何平均数(GEOMEAN)
语法:GEOMEAN(number1,number2,...)
其中:Number1,number2,...为需要计算其平均值的1到30个参数。
A、利用fx求和:选中B9——单击常用工具栏上的fx——GEOMEAN——确定B2:B8区域——单击“确定”即可。
B、输入公式求和:选中B9——输入“= GEOMEAN(B2:B9)”,此时求连续区域B2:B9的所有数据之和。向右拖动其填充柄即得到B9--F9处的几何平均数。
3)调和平均数(HARMEAN)
语法:HARMEAN(number1,number2, ...)
其中:Number1,number2,...为需要计算其平均值的 1 到 30 个参数。
A、利用fx求和:选中B9——单击常用工具栏上的fx—— HARMEAN——确定B2:B8区域——单击“确定”即可。向下拖动其填充柄即得到F9处的调和平均数。
B、输入公式求和:选中B9——输入“=HARMEAN(B2:B8)”,此时求连续区域B2:B8的所有数据之和。
(4)中位数函数
语法:MEDIAN(number1,number2, ...)
如果参数集合中包含有偶数个数字,MEDIAN()将返回位于中间的两个数的平均值。 MEDIAN(1, 2, 3, 4, 5) 等于 3,MEDIAN(1, 2, 3, 4, 5, 6) 等于 3.5即 3 与 4 的平均值。 成绩为例: =MEDIAN(B2:F8) 回车后得到 81 (5)众数函数
语法:MODE(number1,number2, ...)
如果数据集合中不含有重复的数据,则MODE()函数返回错误值N/A。 MODE({5.6, 4, 4, 3, 2, 4}) 等于 4
或选定存放众数的位置——fx——统计——MODE——数据范围——确定 成绩为例:=MODE(B2:F8) 回车后得到 78 (6)最大(小)值函数
求所有参数的最大值。语法:MAX(Number1,Number2,„„) 考试成绩最大91分 以考试成绩为例 =MAX(B2:F8) 得到考试成绩最大值91分
求所有参数的最小值。语法:MIN(Number1,Number2,„„)
以考试成绩为例 =MIN(B2:F8) 得到考试成绩最小值63分
(7)数字项个数
COUNT(单元格范围)——选定单元格范围内数字项的个数;
COUNTBLANK(单元格范围)——选定单元格范围内空白单元格的数目;
COUNTIF(单元格范围,条件)——选定单元格范围内满足所给条件的单元格数目。 “条件”的形式可以为数字、表达式或文本。如条件可以表示为">32"、"孙悟空"、“>=”、“”。
B2:F8 中90分以上的同学数量:COUNTIF(B2:F8,">=90") 等于 4。 (8)平均差(AVEDEV)
表达形式:=AVEDEV(number1,number2,... )
例如求总成绩平均差 =AVEDEV(B2:F8) 等于 5.8648分 (9)样本标准差(STDEV)
语法: STDEV (number1,number2,...)
其中:Number1,number2,...为对应于构成总体样本的 1 到 30 个参数。
假设某次考试的成绩样本为A1=78、A2=45、A3=90、A4=12、A5=85,则估算所有成绩标准差的公式为“=STDEV(A1:A5)”,其结果等于33.00757489。
(10)总体标准差(STDEVP)
语法: STDEVP (number1,number2,...)
求学生成绩的总体标准差 = STDEVP(B2:F8) 等于7.0017。 (11)样本方差(VAR)
语法:VAR(number1,number2,...) (12)总体方差(VARP)
语法:VAR(number1,,number2,...) (13)四舍五入函数ROUND
格式:=ROUND(数值,小数位数)
功能:将括号内数值按指定的小数位数四舍五入。 (14
(15)一键完成求和、计数、最大及最小值等运算
选定计算数据范围—左下角“NUM” ,单击鼠标右键,选择”求和”、“均值”、最大值、最小值、个数等,其值则快速在表的左下方显示出来。
二、使用分析工具库
Excel提供了一组数据分析工具,称为“分析工具库”,在建立复杂统计或工程分析时可
节省步骤。查看可用的分析工具,请单击“工具”菜单中的“数据分析”命令。(如果“工具”菜单中没有“数据分析”命令,则需要安装“分析工具库”:工具——加载宏——分析数据库则可以)。在“数据分析”对话中选中“描述分析”。
汇总统计:结果有:平均值、中值、众数、标准偏差、方差、峰值、偏斜度、极差(全距)、最小值、最大值、总和、总个数和置信度。
仍使用上面的例子,我们已经把数据输入到B2:F8单元格,然后按以下步骤操作: 第一步:在“工具”菜单中选择“数据分析”选项,从其对话框中选择“描述统计”,按确定后打开描述统计对话框。
第二步:在输入区域中输入$B$2:$F$8,在输出区域中选择$A$13,其他复选框可根据需要选定,选择汇总统计,可给出一系列描述统计量;选择平均数置信度,会给出用样本平均数估计总体平均数的置信区间;第K大值和第K小值会给出样本中第K个大值和第K个小值。
第三步:单击确定,可得输出结果,
结果中,平均指样本均值;标准误差指样本平均数的标准差;中值即中位数;模式指众数;标准偏差指样本标准差,自由度为n-1;峰值即峰度系数;偏斜度即偏度系数;区域实
偏斜度:偏态系数,3表示。3=0,表示正态分布,3为正值,表示右偏,3为负值,表示左偏。3的绝对值越大,偏斜的程度就越大。
峰度是分布集中趋势高峰的形状。通常与正态分布相比较,若分布形态比正态分布更瘦更高,则称为尖峰分布,若比正态分布更矮更胖,则称平峰分布。4=3为正态分布,4>3为尖峰分布,43,说明低成绩的同学比较多。 练习:
某商场家用电器销售情况如图所示。计算各种电器的全年销售总量、平均销售量,最大值、最小值、各种电器销售量的中位数、众数,总体标准差、总体标准差系数。