使用EXCEL进行数据分析教材课件

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