使用EXCEL进行数据分析PPT.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,使用,EXCEL,进行数据分析,主讲老师：李玲娟,1,数据分析与数据挖掘,数 据,信 息,统计分析（报表）,数据挖掘（专业工具和算法）,2,掌握,EXCEL,的常用统计函数；,熟悉使用,EXCEL,透视表进行数据分析和分类汇总；,了解使用,EXCEL,进行数据分析,教学要求,3,实验环境,版本：,EXCEL 2000,完全安装,典型安装,最小化安装,4,讲授内容,常用统计函数,数据透视分析,图表,数据透视表,(,图,),数据分析,描述统计,检验分析,z-,检验：双样本平均差检验,F-,检验：双样本方差,t-,检验：双样本等方差假设,t-,检验：双样本异方差假设,t-,检验：平均值的成对二样本分析,方差分析,单因素方差分析,无重复双因素分析,可重复双因素分析,相关系数与回归分析,协方差,相关系数,回归分析,5,常用统计函数,一般统计函数：用来统计给定某个区域的数据的平均值、最大值或者最小值等,数理统计函数：对数据进行相关的概率分 布的统计，进行回归分析等。,6,应用举例一,某班级的成绩表，如下图所示，其中有两个学生的该门课程缺考，需要分别用到,AVERAGE,和,AVERAGEA,函数,来计算两个不同的平均值。,7,【,操作步骤,】,在单元格,D3,中输入公式,“,=AVERAGE(B2:B9),”,，可以计算参加考试学生的平均成绩，函数,AVERAGE,在计算时对缺考的两个学生的成绩自动认为是,0,。,在单元格,D6,中输入公式,“,=AVERAGEA(B2:B9),”,，可以计算全体学生的平均成绩，对缺考的学生成绩，,AVERAGE,函数将自动忽略。,8,应用举例二,某班级的成绩表，如下图所示。需要用到,FREQUENCY,函数来来统计各个分数段的分布学生人数。,9,FREQUENCY,函数,功能：统计指定单元格区域中数据的,频率分布,。由于函数,FREQUENCY,返回一个数组，必须以数组公式的形式输入。,语法形式,:,FREQUENCY(data_array,bins_array),data_array,为一数组或对一组数值的引用，用来计算频率。如果,data_array,中不包含任何数值，函数,FREQUENCY,返回零数组。,bins_array,为一数组或对数组区域的引用，设定对,data_array,进行频率计算的分段点。如果,bins_array,中不包含任何数值，函数,FREQUENCY,返回,data_array,元素的数目。,10,【,操作步骤,】,在,E4E7,单元格输入进行频率计算的分段点,59,70,80,90,；,选中,F4F8,单元格,作为输出统计结果的单元格；,在公式栏输入,“,=FREQUENCY(B2:B11,E4:E8),”,，然后按下,“,Ctrl,Shift,Enter,”,组合键锁定数组公式即可。,输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按,Ctrl,Shift,Enter,组合键锁定数组公式，,Excel,将在公式两边自动加上花括号“,”,。,11,CORREL,函数,功能是单元格区域中的两个数列之间的,相关系数,。,表达式为,CORREL,（,arrnyl,，,arrny2,）。,arrnyl,和,arrny2,表示的都是两个数据列,CORREL,函数可以用来分辨两种属性之间的关系。,12,应用举例三,某医生为了探讨缺碘地区母婴,TSH,水平关系，应用免疫放射分析测定了,160,名孕妇（孕周,15,17w,）及分娩时脐带血,TSH,水平（,mU/L,），现随机抽取,10,对数据如图,2-8,所示，试求脐带血,TSH,水平对母血,TSH,水平的相关系数。,13,【,操作步骤,】,选中结果输出单元格,B14,；,在公式栏输入,“,=CORREL(C3:C12,B3:B12),”,点击公式栏 按钮；,在单元格,A14,输入,“,相关系数,”,即可。,14,应用举例四,某地,120,名,7,岁男童的身高，已知身高均数为,119.41cm,，标准差为,4.38cm,。试估计该地,7,岁男童身高在,110cm,以下者的概率，身高在,110cm,120cm,之间的概率。如果要制定一个健康标准使得,90,的,7,岁男童符合健康标准，那么健康标准应该定多少？,15,NORMDIST,和,NORMINV,函数,NORMDIST,函数,功能：返回指定平均值和标准偏差的正态分布函数,表达式：,NORMDIST,（,x,，,mean,，,standard_dev,，,cumulative,）。,x,是需要计算其分布的数值；,mean,是分布的算术平均值：,standard_dev,表示分布的标准偏差；,cumulative,是一逻辑值，指明函数的形式。如果,cumulative,为,TRUE,，,NORMDIST,函数返回累积分布函数，否则返回概率密度函数。此函数在统计方面应用范围广泛。,NORMINV,函数,功能：返回指定平均值和标准偏差的正态累积分布函数的反函数。,表达式：,NORMINV,（,probability,，,mean,，,standard_dev,）,probability,是正态分布的概率；,mean,是分布的算术平均值；,standard_dev,表示分布的标准偏差。,16,应用举例五,某地,120,名,7,岁男童的身高，已知身高均数为,119.41cm,，标准差为,4.38cm,如下图所示。试估计该地,7,岁男童身高在,110cm,以下者的概率，身高在,110cm,120cm,之间的概率。如果要制定一个健康标准使得,90,的,7,岁男童符合健康标准，那么健康标准应该定多少？,17,【,操作步骤,】,在单元格,C5,中输入公式,“,=NORMDIST(110,B2,B3,TRUE),”,，计算身高在,110cm,以下者的概率为,0.015841,；,在单元格,C6,中输入公式,“,=NORMDIST(120,B2,B3,TRUE)-C5,”,，计算成功身高在,110cm,120cm,之间的概率为,0.537736,；,在单元格,C7,中输入公式,“,=NORMINV(0.9,B2,B3),”,，计算使,90,的,7,岁男童身高达标的健康标准为,125.0232cm,。,18,分析结果,=NORMDIST(110,B1,B2,TRUE,),=NORMDIST(120,B1,B2,TRUE)-C5,=NORMINV(0.9,B2,B3),19,图表,概念：工作表数据的图形化表示、清晰易懂、形象直观。,种类：,14,种图表类型，包含了若干种不同的图表子类型。在处理医药数据中常用的几类图表：,1,、折线图,2,、柱形图,3,、饼图,4,、,XY,散点图,5,、条形图,6,、雷达图,20,柱形图,柱形图是,Excel,的默认图表类型，也是用来处理医药数据常用的图表类型之一。通常把每个数据点显示为一个垂直柱体，其高度对应于数值，用来显示一段时期内数据的变化或描述各项之间的比较关系。分类项水平组织，数值垂直组织，即强调数据随时间的变化。,柱形图共有,7,种子图表类型：,簇状柱形图、堆积柱形图、百分比堆积柱形图、三维簇状柱形图、三维堆积柱形图、三维百分比堆积柱形图和三维柱形图。,。,21,应用举例五,在药物实验中的应用，现有药物止血效果实验采集的数据表，,A,列为实验对照组和七种不同的止血药物，分别做了六次药物止血实验，得到六组凝血时间实验数据（单位：秒）。通过柱形图可以形象地描绘了这七种不同药物止血功能的差异。,返回,22,【,操作步骤,】,(1),选择图表类型，单击,“,插入,”,|,“,图表,”,。,(2),选择图表源数据。,(3),设置标题和图例。,(4),确定图表位置。,(5),编辑图表区。用户需要编辑的内容包括颜色、填充和边框等,。,(6),编辑数据轴、分类轴。,23,雷达图,雷达图是由一个中心向四周射出多条数值坐标轴，每个指标都拥有自己的数值坐标轴，把同一数据序列的值用折线连接起来而形成。雷达图用来比较若干个数据序列指标的总体情况，可比较多个单位不同指标的数据特征，如最大值、最小值等。雷达图的,3,个子图表类型分别为雷达图、数据点雷达图和填充雷达图。,24,为了反映医院管理的质量，某市抽样调查了几所医院的,6,项指标（指标值越大表示质量越高），其中三所医院的调查结果如图,2-65,所示，请绘制雷达图反映三所医院的管理质量。,应用举例六,25,26,数据透视分析,数据透视表,数据透视图,27,数据透视表,数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格和图表。,在,Excel,5,0,以上版本就提供有这一功能，它不仅可以转换,行和列,以查看源数据的不同汇总结果，显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据，这对分析、组织复杂数据是非常方便有用的。,数据透视表最大的特点就是它的,交互性,。创建一个数据透视表以后，可以任意重新排列数据信息，并且还可以根据习惯将数据分组。,28,数据透视表布局,横向分类依据的字段,纵向分类依据的字段,统计依据的字段,分类显示（筛选）依据的字段,29,应用举例七,以下图数据为例分析药材的分布、产量情况。,30,数据透视表的制作,选择,“,数据,”,菜单中的,“,数据透视表和数据透视图,”,选项，弹出,“,数据透视表和数据透视图向导,”,对话框，按步骤依次完成。,编辑数据透视表,添加、删除分类和统计字段,改变统计字段的算法,修改、删除,数据透视表,更新数据透视表中的数据,31,数据透视图,根据透视表建立透视图,:,选择数据透视表中的任意单元格，然后在,“,数据透视表,”,工具栏上单击,“,数据透视表,”,按钮，单击,“,数据透视图,”,命令，。,对应于透视表的列字段,对应于透视表的行字段,对应于透视表的数据区域,对应于透视表的页字段,32,数据透视图,根据源数据建立数据透视图：,根据数据库的源数据建立透视图的方法类似于创建数据透视表，具体操作步骤如下：,(1),单击欲建立数据透视图的数据清单。,(2),选择,“,数据,”,菜单中的,“,数据透视表和数据透视图,”,选项，在弹出的对话框中选择,“,数据透视图,”,，按步骤依次完成。,返回,33,数据统计与分析,描述统计,检验分析,z-,检验：双样本平均差检验,F-,检验：双样本方差,t-,检验：双样本等方差假设,t-,检验：双样本异方差假设,t-,检验：平均值的成对二样本分析,方差分析,单因素方差分析,无重复双因素分析,可重复双因素分析,相关系数与回归分析,协方差,相关系数,回归分析,统计检验计算,检验分析报告,34,“,分析工具库,”,的加载,其基本步骤如下：,1,、启动,Excel2000,，执行菜单栏中的,“,工具,”,|,“,加载宏,”,命令，打开,“,加载宏,”,对话框，选中,“,分析工具库,”,复选框。,2,、单击,“,确定,”,按钮，返回到工作表。执行菜单栏的,“,工具,”,|,“,数据分析,”,命令，打开,“,数据分析,”,对话框，即可使用各种数据分析工具。,注意：,EXCEL,必须是,“,完全安装,”,35,描述统计,“,描述统计,”,是基础分析工具之一，用于生成对输入区域中数据的,单变量,统计分析报表，提供有关数据趋中性和易变性的信息，而这些信息是进一步分析数据的起点，可以指示哪一种检验适合进一步的探讨。,描述统计的统计函数一般包括：,MAX,、,MIN,、,AVERAGE,、,MEDIAN,、,GEOMEAN,、,HARMEAN,、,AVEDEV,、,STDEV,、,VAR,、,KURT,和,SKEW,函数等，他们分别用于计算最大值、最小值、平均值、中位数、几何平均数、调和平均数，以及变异统计的平均差、标准差、方差峰度和偏度。,36,应用举例八,某老师对几名学生的五次考试成绩进行分析，以便对这几个学生在学习方面存在的问题加以解决。使用,“,描述统计,”,分析工具对其数据进行分析。,返回,37,【,操作步骤,】,1,、执行菜单栏的,“,工具,”,|,“,数据分析,”,命令，打开,“,描述统计,”,对话框，选中,“,分析工具,”,列表框中的,“,描述统计,”,选项，单击,“,确定,”,按钮，打开,“,描述统计,”,对话框。,3,、在,“,输入区域,”,编辑框中键入三列数据所在的单元格区域引用（,$A$4:$F$8,）。,4,、单击,“,逐行,”,选项。,5,、选中,“,标志位于第一列,”,选项。,6,、在,“,输出选项,”,下单击,“,新工作表组,”,选项，并在对应编辑框中输入新工作表的名称，如：,“,描述统计结果,”,。,38,t-,检验：平均值的成对双样本分析,“,t-,检验：平均值的成对双样本分析,”,分析工具及其公式可以进行成对双样本,t-,检验，用来确定样本平均值是否不等。此,t-,检验窗体并不假设两个总体的方差是相等的。当样本中出现,自然配对,的观察值时，可以使用这种成对检验。例如，对一个样本组进行了两次检验，抽取实验前的一次和实验后的一次。,39,应用举例九,某医院用中药治疗,9,例再生障碍性贫血，结果如下图所示。试用,“,t-,检验：平均值的成对二样本分析,”,工具，分析治疗前后是否有改变？,返回,40,【,操作步骤,】,（,1,）在,Excel2003,，创建一个新的,Excel,工作薄，如图,2-108,所示输入需要分析的数据；,（,2,）执行菜单栏中的,“,工具,”,|,“,数据分析,”,命令，打开,“,数据分析,”,对话框，选中,“,分析工具,”,列表框中的,“,t-,检验：平均值的成对二样本分析,”,选项。,41,单因素方差分析,该分析工具通过简单的方差分析，对两个以上样本平均值进行相等性假设检验（样本取自具有相同平均值的样本总体）。此方法是对双平均值检验（如,t-,检验）的扩充。,42,应用举例十,某医院用三种方法治疗某种疾病，结果如下表,2-2,所示，试问三组治愈天数有无差别？,中药,3,7,7,6,2,西药,9,12,11,8,5,中西结合,1,2,4,6,7,返回,返回,43,【,操作步骤,】,（,1,）执行菜单栏中的,“,工具,”,|,“,数据分析,”,命令，打开,“,数据分析,”,对话框，选中,“,分析工具,”,列表框中的,“,方差分析：单因素方差分析,”,选项；,（,2,）单击,“,确定,”,按钮，执行,“,方差分析：单因素方差分析,”,对话框；,44,协方差,“,协方差,”,工具用来度量两个区域中数据的关系，用于返回各数据点与其各自所在数据组的平均值之间的偏差乘积的平均值。,使用协方差工具可以用来确定两个区域中数据的变化是否相关，即一个集合的较大数据是否与另一个集合的较大数据相对应（正协方差）；或者一个集合的较小数据是否与另一个集合的较大数据相对应（负协方差）；还是两个集合中的数据互不相关（协方差为零）。协方差对度量单位是敏感的。,45,18,名儿童身高、体重、表面积测定数据如下图所示，试利用,“,协方差,”,分析工具分析身高、体重、表面积三个指标之间的关系。,应用举例十一,返回,46,【,操作步骤,】,（,1,）执行菜单栏中的,“,工具,”,|,“,数据分析,”,命令，打开,“,数据分析,”,对话框，选中,“,分析工具,”,列表框中的,“,协方差,”,选项；,（,2,）单击,“,确定,”,按钮，执行,“,协方差,”,对话框。,47,相关系数,用于度量两个数据集（可以使用不同的度量单位）之间的相关性，与两组数据的相关分析和度量单位无关。总体相关性计算的返回值为两组数据集的协方差除以它们标准偏差的乘积。相关系数计算公式如下：,可以使用相关系数分析工具来确定两个区域中数据的变化是否相关，即一个集合的较大数据是否与另一个集合的较大数据相对应（正相关）；或者一个集合的较小数据是否与另一个集合的较大数据相对应（负相关）；还是两个集合中的数据互不相关（相关性接近零）。,返回,48,回归分析,通过对一组观察值使用,“,最小二乘法,”,直线拟合进行线性回归分析，可用来分析单个因变量是如何受一个或几个自变量影响的。例如，观察某个运动员的运动成绩与一系列统计因素的关系，如年龄、身高和体重等。可以基于一组已知的成绩统计数据，确定这三个因素分别在运动成绩测试中所占的比重，使用该结果可对尚未进行过测试的运动员的表现做出预测。,在实际工作中，相关系数与回归分析是研究现象之间相互依存关系的不可分割的两个方面。一般先进行相关分析，根据相关系数或相关指数的大小对变量进行筛选，剔除不相关或是相关性小的变量，然后再进行回归分析，建立回归模型，进行预测。,49,应用举例十二,设有某地,10,名,3,岁儿童体重与表面积资料如下图所示，试做相关系数分析和回归分析。,50,【,操作步骤,】,（,1,）首先进行相关系数的分析。,执行菜单栏中的,“,工具,”,|,“,数据分析,”,|,“,相关系数,”,选项；,执行,“,相关系数分析,”,对话框依次完成。,（,2,）,由于,3,岁儿童的体重与体表面积有较强的相关性，因此需要利用回归分析工具进一步建立回归模型。,执行菜单栏中的,“,工具,”,|,“,数据分析,”,|,“,回归,”,选项；,执行,“,回归,”,对话框依次完成。,51,再见,52,