excel在统计学当中的应用正式教案

1、第一章excel数据表及其相关基础知识 本章要点 Excel与统计学的关系 Excel与统计有关的基础知识1.1excel与统计学的关系。以例1.1为例，这是一个咖啡店的咖啡销售情况，我们现在要统计其一周各种咖啡的销售情况，应用excel快速统计出销售数量，可以简化得到结果的过程，通过以上案例我们可以知道我们可以通过excel中的函数公式和图表快速的得到统计结果。接下来我们要了解excel中与统计有关的基础知识1.2.excel与统计有关的基础知识一数据的手工输入单元格中可以输入文本，数值（包括日期），公式，其中，前两类是常量，公式则为变量，一旦该公式所引用的单元格的数值发生变化，该公式的值将

2、立即自动重新计算。要注意的是当输入数字型文本的时候，即数字串实际属于字符串, 比如说电话号码，应按 6393672方法输入（即先输入） 或者对选定的单元格设定为文字格式(通过”格式”菜单中的”单元格”中的“数字”下的“文本”。（尤其是在函数当中，这点很重要。）公式的输入要以“=”开始，公式可以包括以下内容之一：函数，引用，运算符，常量。例：=pi()*c42二.Excel公式及函数基础1.运算符及其类型 算术运算符（加法） （减法） *（乘法） （除法） （乘方） %（百分比） 比较运算符=（等于）<>（不等于）、>(大于)，<（小于），<=（小于等于）,>

3、=(大于等于) 文本运算符&(连接) 例 本月销售的结果是本月销售。（注意，在公式中输入文本常量得加上符号）若要改变，请加括弧。在公式中，每个运算符都需要特定类型的数据与之对应，如果输入数据的类型与其不符合，excel将对数据的格式自动进行转换。2.单元格的引用在excel中单元格的默认引用样式为A1样式，即字母+数字格式，我们在excel中会经常使用公式的复制，在复制中我们会对单元格引用产生不同的需求，针对这种情况，excel把单元格的引用分为3种类型。 相对引用：在默认引用样式条件下，直接采用A1样式，如=B1+B2则把公式复制到其他单元格时，行或列的引用会改变 绝对引用：在默认引

4、用样式条件下，在行列标前都加上“$”符号，如=$B$1+$B$2,则在复制公式时，行和列的引用不会改变。 混合引用：在默认引用样式条件下，行或列中有一个是相对引用，一个是绝对引用。如=$B1+B$2最后谈一下三维地址引用：如果引用的是本工作表里的单元格，可以不用在单元格地址前加工作表名和感叹号，否则就要用例如sheet2!c7来引用sheet2工作表中的c7单元格。例sheet1！1+2(注意:其中的a2是指公式所在的工作表的a2)3.函数函数分为内置函数，扩展函数和用户自定义函数，内置函数只要启动excel就可以使用；扩展函数必须通过选择工具/加载宏命令加载，然后才能像内置函数那样使用；用户

5、自定义函数可以通过visual basic for applications(VBA)来创建。参数：函数右边括号中的部分称为参数，是我们传给函数用来执行运算的数值各个参数需要用括号（ ）括起来，假如使用多个参数，那么参数与参数之间使用半角逗号进行分隔。各个函数对参数的要求不同，可以根据函数的要求进行输入参数。注意: 如果将引用作为一个参数, 而且这一引用使用逗号做合并运算,则用括弧将引用括起来.例：sum(A1,C1).嵌套函数：即一个函数是另一个函数的参数。准许多达七级的嵌套函数。函数嵌套：例：IF(AVERAGE(A1:A3)>50,MAX(A1:A3),SUM(A1:A3)。含义是

6、:如果A1,A2,A3三个数的均值>50,则求A1,A2,A3三个数的最大值，否则就求A1,A2,A3三个数的和。函数的输入： 1手工输入函数2.利用插入函数按钮。4.公式的编辑双击单元格可以在单元格内部直接编辑单元格内容，或者选择要编辑的单元格，然后单击编辑栏，可以在编辑栏中编辑单元格内容。三Excel图表基础所谓图表是工作表内容的图形表示。图表是由图表区域及区域中的对象组成，即包括图表区，图表标题，绘图区，网格线，系列，坐标轴，坐标轴标题，图例。Excel中提供我们3类图表-标准图表类型，excel自定义的图表类型，用户自定义图表类型，我们以例1.2来看一下他们的样子。我们可以通过图

7、表向导工具来建立图表。图表的编辑：1. 图表区域及其选择：图表的选择和图表对象（图例，图表标题）的选择2. 图表区域的修饰与编辑：a修饰图表区域：单击图表区域/图表区格式按钮。b编辑（移动，缩放，复制，删除）3. 图表对象的修饰：a.图表标题的修饰。b.图表坐标轴的修饰。c.图表数据系列的修饰，双击某个数据系列d.图表图例的修饰。e.编辑网格线4. 数据修改：a.增加和删除图表数据，使用源数据对话框添加或删除数据b.改变图表数据，默认下图表与原始数据之间是相互链接的，因此改变原始数据（或图表数据），则图表数据（或原始中的数据）将随之做相应的变动c.为数据系列添加趋势线：可以在非堆积型二维条形图

8、，柱形图等图中为数据系列添加趋势线。来显示实际数据变动的情况。5. 改变图表的类型。6.标准图表按用途可分为 表示变化趋势的柱形图，条形图，折线图,面积图,雷达图，圆锥图，圆柱图，棱锥图。 表示所占比例的饼图，圆环图 表示变量之间关系的XY散点图，气泡图 用来画图的XY散点图，曲面图。 表示股价变化的股价图。第二章使用 Excel输入.整理及显示统计数据本章要点 数据的输入 数据的整理 数据的图形显示2.1 数据的输入一导入数据1.手工输入，2.从数据库和文件导入数据：数据/导入外部数据/导入数据，在选取数据源对话框中选取相应的数据库文件（比如Access文件），若数据不在本机，则单击选取数据

9、源对话框中的新建源，出现数据连接向导，以此来从外部数据连接导入数据3.用Microsoft Query导入数据：要使用Query导入数据，首先安装Query：在首次使用数据/导入外部数据/新建数据库查询时，系统将提示安装Query，安装后，再利用数据/导入外部数据/新建数据库查询打开query查询，利用query向导，获得我们所需的数据。4.从web导入数据：数据/导入外部数据/新增web查询，在指定网站位置即可。2.2数据的整理一初步的数据整理1.排序，根据某特定列的内容来重排数据清单（就是一个选定的表区域）中的行。应用数据/排序命令进行排序工作。2.筛选是查找和处理数据清单中数据子集的快捷

10、方式，筛选后将暂时隐藏不必显示的行。Excel中有两种筛选命令：自动筛选和高级筛选。 自动筛选：首先选择表，再单击数据/筛选/自动筛选 高级筛选：首先得制定两个区域：数据区域和条件区域，其中条件区域首行为列标志，后面的行为条件。如例2.1二数据分组整理成次数分布表首先，将原始数据输入工作表的单元格中，之后进行编组，计次，让数据呈现出具体的意义，即分组整理成次数分布表1. 单项分组（使用countif()）使用COUNTIF（）函数计算区域中满足给定条件的单元格的个数。 语法：COUNTIF(range,criteria)Range：为需要计算其中满足条件的单元格数目的单元格区域。Criteri

11、a ：为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、"apples"或">32" （表达式要用“”包起来）。见例2.22. 组距分组数据的次数分布表 求出全距R ，使用max（）-min（）函数 决定组数，主要是凭经验确定，也可以按照一个经验公式即组数m=1+3.322lgN,N为总次数。（LOG10(number)，） 决定组距，如果是等距的话,组距i=R/m在用经验公式计算m和i时，计算的结果采用舍去进一法。即直接将小数舍去，并在整数位上加1。先应用trunc(nu

12、mber,num_digits)截尾取整，（Number：需要截尾取整的数字， Num_digits（可选参数）用于指定取整精度的数字，不写该参数表示把小数点后的数都去掉），之后在加1。 组距上，下限的选择(一般遵循上组限不在内的原则) 计算组中值，对于缺上限的开口组组中值=下限+邻组组距/2 计算各组的次数，利用frequency( array,bins )函数来计算某个范围内数值出现的次数。(array是指原始资料的存放区域，bins指统计分组的组上限构成的数值序列。Frequency函数在统计次数时，将与对应上限值一样大的数也统计在内，由于函数 FREQUENCY 返回一个数组，所以它是

13、数组公式，即首先选择输出的区域，打入函数，之后按ctr+shift+enter，这点与非数组公式不同，非数组公式是直接按回车) 累加次数分布见例2.3。三数据透视表和透视图数据透视表其实是对表内数据的分类汇总（与数据/分类汇总命令类似，但是它的汇总是不管相邻与否，一律汇总，不像数据/分类汇总只是汇总相邻同分类字段值的对应汇总字段数值。）和建立交叉表的交互式格式表格，它是一种组织数据的软设备。用户可以在透视表中指定想显示的字段和数据项，以确定如何组织数据见例2.4。1.透视表的创建 单击表格区域中的任意单元格 单击数据/数据透视表和数据透视图，按要求选择相应的选项。 拖动右边的字段按钮到图中来做

14、成透视表的行列数据 例如把地区作为页字段，产品代号作为行字段，季度作为列字段，销售额作为数据项。2.修改透视表 行列互换：在列字段名单元格A4按下左键拖曳到行字段中即可。（行同上） 添加和删除字段：添加额外的字段:单击工具栏上的“数据透视表”/向导重新进行区域选择,将已有字段添加或删除：单击数据透视表工具栏/字段列表，将字段拖入数据透视表（添加）或将字段拖入字段列表中（删除）3. 刷新透视表（有以下两种方法） 选择数据透视表，执行数据/更新数据 单击“数据透视表”工具栏上的“！”4. 对数据透视表排序选择字段，在选择数据/排序。5. 改变透视表的汇总方式选择汇总字段，右击选择字段设置，在弹出的

15、对话框内选择汇总方式。6. 显示和隐藏明细数据 右单击透视表中的列字段名（如“季度”） 执行“组及显示明细数据”显示明细数据 在对话框中选择明细数据所在的字段，（如“地区”）确定7. 创建计算字段 选择汇总字段，单击数据表工具栏数据透视表公式计算字段弹出对话框 在“名称”栏中输入计算字段的名称，在“公式”栏中输入计算字段的公式， 单击“添加”确定8.数据透视图的创建和编辑数据透视图是数据透视表的可视化，可以通过数据透视表和数据透视图向导来建立或通过数据透视表直接建立数据透视图（激活数据透视表后，单击数据透视表工具栏中的图表向导按钮，可以建立数据透视图）数据透视图的编辑和之前一般图表的编辑类似。

16、四做汇总统计表对多个工作表和工作薄内相类似的数据进行合并计算，将合并的结果放到一张合并的工作表内，利用数据/合并计算按钮。见例2.52.3统计数据的图形显示按照不同的实际要求使用我们前面所提到的图表。比如说对于等距数列，我们关心的是频数或频率，如果是不等距数列，我们关心的是频数密度或频率密度，则将相应的数值算出来，然后做图。第三章计算数值分布特征的指标 本章知识要点 集中趋势的计算 离散趋势的计算 偏度和峰度的计算31 集中趋势 算术平均数（average函数） 调和平均数（harmean()） 几何平均数(geomean() 中位数（median函数） 分位数(四分位数quartile(ar

17、ray,quart)，百分位数percentile(array,k) 众数（mode函数）3.2离散趋势 极差 用max()-min() 四分位差 平均差 avedev() 方差 分为总体方差和样本方差 （var(),varp()） 标准差（stdev(),stdevp()） 离散系数 数据的标准化3.3分布的偏态和峰度 偏态系数（skew()） 峰度系数（kurt（）3.4总结我们除了利用函数来求得这些数值以外呢，我们还可以利用excel中的分析工具箱加载宏来快速求得以上的各种数值即点击工具/数据分析/描述统计。见例3.1。第四章估计和检验本章知识要点 掌握几个常用的分布函数 抽样 估计和检

18、验 方差分析4.1掌握几个常用的分布函数一.正态分布1. NORMDIST(x,mean,standard_dev,cumulative)其中X 为需要计算其分布的数值，Mean 分布的算术平均值。Standard_dev 分布的标准偏差。Cumulative 为一逻辑值，指明函数的形式。如果 cumulative 为 TRUE，函数 NORMDIST 返回累积分布函数；如果为 FALSE，返回概率密度函数。可以利用上述函数和xy散点图制作正态分布图。2. NORMSDIST(z),是用来返回标准正态累计分布的函数。3. NORMINV(probability,mean,standard_de

19、v),其中Probability 正态分布的概率值，Mean分布的算术平均值，Standard_dev分布的标准偏差。函数返回指定平均值和标准偏差的正态累积分布函数的反函数。4. NORMSINV(probability)，其中Probability 正态分布的概率值，返回标准正态累积分布函数的反函数。5. ZTEST(array,0,sigma)其中Array为用来检验 0 的数组或数据区域。0为被检验的值。Sigma为样本总体（已知）的标准偏差，如果省略，则使用样本标准偏差。函数(Z检验) 返回 z 检验的单尾概率值二.t分布1. TDIST(x,degrees_freedom,tails) X 为需要计算分布的数字，Degrees_freedom为表示自由度的整数。Tails指明返回的分布函数是单尾分布还是双尾分布。如果 tails = 1，函数 TDIST 返回单尾分布。如果 tails = 2，函数 TDIST 返回双尾分布。即 tails = 1，TDIST 的计算公式为 TDIST = P( X>x )，其中 X 为服从 t 分布的随机变量。如果 tails = 2，TDIST 的计算公式为 TDIST = P(|X| > x) = P(X > x or X < -x)。2. TINV()函数用来返回t分布的t值。3.