使用EXCEL进行数据分析ppt课件.ppt

1、利用EXCEL进行数据分析，讲师：李玲娟，1、数据分析与数据挖掘，数据、信息、统计分析(报表)，数据挖掘(专业工具和算法)，2、掌握EXCEL常用的统计功能；熟悉使用EXCEL透视表进行数据分析和分类汇总；了解数据分析使用EXCEL，教学要求，3，实验环境，版本：EXCEL 2000完整安装，典型安装，最小安装，4，教学内容，常用统计函数数据透视分析图表透视表(图)数据分析描述统计检验分析z检验：双样本均值差检验f检验：双样本方差检验：双样本等方差假设检验：双样本异方差假设检验：双样本均值分析，单变量方差分析无重复双变量分析相关系数和5.通用统计函数，通用统计函数：用于统计给定区域内数据的平均

2、值、最大值或最小值；数理统计功能：统计数据的相关概率分布，回归分析等。6，应用示例1，一个班级的成绩单，如下图所示，有两个学生没有参加课程，需要使用AVERAGE和AVERAGE函数分别计算两个不同的平均值。7，操作步骤，在单元格D3中输入公式“=AVERAGE(B2:B9)”，可以计算参加考试的学生的AVERAGE分数，AVERAGE函数在计算时自动将两个缺考学生的分数视为0。在D6单元格中输入公式“=AVERAGEA(B2:B9)”计算所有学生的平均分数，AVERAGE函数将自动忽略未参加考试的学生的分数。8，应用示例2，一个类的抄本，如下图所示。需要使用FREQUENCY函数来计算每个分

3、数段中分布的学生数量。9，FREQUENCY函数，function:计算指定单元格区域中数据的频率分布。由于函数FREQUENCY返回一个数组，它必须以数组公式的形式输入。语法形式：频率(数据_数组，箱_数组)数据_数组是一个数组或一组数值的引用，用于计算频率。如果data_array不包含任何数值，则函数FRANCE返回一个零数组。Bins_array是一个数组或数组区域的引用，它为data_array的频率计算设置分段点。如果bins _ array不包含任何数值，则函数FRANCE返回data_array元素的数目。10，操作步骤，在单元格E4E7中输入用于频率计算的分段点59、70、8

4、0、90；选择单元格F4F8作为输出统计结果的单元格；在公式栏中输入“=frequency (b2:b11，e4:e8)”，然后按“CtrlShiftEnter”锁定数组公式。要输入数组公式，您必须首先选择要存储结果的单元格区域(可以是一个单元格)，在编辑栏中输入公式，然后按CTRL键锁定数组公式。Excel会自动在公式的两边添加花括号 。11，CORREL函数，它是单元格区域中两个系列之间的相关系数。表达式为CORREL(arr yl，arrny2)。Arrnyl和arrny2代表两个数据列。CORREL函数可以用来区分两个属性之间的关系。12，应用实例3，为了探讨碘缺乏地区母婴TSH水平之

5、间的关系，医生通过免疫放射分析测量了160名孕妇(胎龄1517w)和分娩期间的脐带血TSH水平(/L)。现在，随机选取10对数据，如图2-8所示，试图找出脐带血促甲状腺激素水平和母体血促甲状腺激素水平之间的相关系数。13，操作步骤，选择结果输出单元格B14；在公式栏中输入“=correl (c3:c12，b:b12)”，然后单击公式栏中的按钮。在单元格A14中输入“相关系数”。14，应用示例4，某个地方120名7岁男孩的平均身高为119.41厘米，标准差为4.38厘米。尝试估计身高低于110厘米的7岁男孩的概率，以及身高在110厘米和120厘米之间的概率。如果设定一个健康标准，使90%的7岁男

6、孩达到健康标准，那么健康标准应该设定多少？15，NORMIDIST和NORMINV函数，NORMIDIST函数函数：返回指定均值和标准差的正态分布函数表达式：NORMIDIST(x，均值，标准差，累积)。x是需要计算其分布的值；均值是分布的算术平均值：standard_dev代表分布的标准差；累积是一个逻辑值，表示函数的形式。如果累计值为真，则NORMDIST函数返回累计分布函数，否则返回概率密度函数。这个函数在统计学中被广泛使用。NORMINV函数：返回指定均值和标准差的正态累积分布函数的反函数。表达式：概率是正态分布的概率；平均值是分布的算术平均值；标准偏差代表分布的标准偏差。16，应用例

7、5，某处120名7岁男孩的身高，平均已知身高为119.41厘米，标准差为4.38厘米，如下图所示。试着估计一个7岁男孩身高低于110厘米的概率，以及身高在110厘米到120厘米之间的概率。如果设定一个健康标准，使90%的7岁男孩达到健康标准，那么健康标准应该设定多少？17，操作步骤，在单元格C5中输入公式“=NORMDIST(110，B2，B3，真)”，并计算身高小于110厘米的人的概率为0.015841；在单元格C6中输入公式“=NORMDIST(120，B2，B3，真)-C5”，并计算110厘米到120厘米之间的成功高度概率为0.537736；在单元格C7中输入公式“=NORMINV(0.

8、9，B2，B3)”，并计算出90名7岁男孩的健康标准为125.0232厘米，18，分析结果，=NORMIDIST(110，B1，B2，真)，=NORMIDIST(120，B1，B2，真)-C5，=norminv (0.9，B2，B3)，19，图表，概念：图形表示，清晰易懂，工作表数据生动类别：14图表类型医学数据处理中常用的几种图表：1 .折线图2、柱形图3、饼图4、XY散点图5、条形图6、雷达图20、柱形图，这是Excel的默认图表类型，也是处理医疗数据常用的图表类型之一。通常，每个数据点显示为一个垂直列，其高度对应于数值，用于显示一段时间内数据的变化或描述项目之间的比较关系。分类项目横向组

9、织，数值纵向组织，强调数据随时间的变化。柱形图有七个子图类型：聚类柱形图、堆积柱形图、百分比堆积柱形图、三维聚类柱形图、三维堆积柱形图、三维百分比堆积柱形图和三维柱形图。21，应用实施例5，在药物实验中的应用，从现有的药物止血效果实验中收集的数据表，列A是实验对照组和七种不同的止血药物，分别进行了六种药物止血实验，并且获得了六组凝血时间实验数据(单位：秒)。这七种药物止血作用的差异可以用柱状图生动地描述出来。return，22，操作步骤，(1)选择图表类型，然后单击插入|图表。(2)选择图表源数据。(3)设置标题和图例。(4)确定图表位置。(5)编辑图表区。用户需要编辑的内容包括颜色、填充和边

10、框。(6)编辑数据轴和分类轴。雷达图由同一数据系列的值用虚线连接而成，从中心到外围拍摄多个数值坐标轴，每个指标都有自己的数值坐标轴。雷达图用于比较几个数据系列指标的总体情况，可以比较多个单位的不同指标的数据特征，如最大值和最小值。雷达图的三个子图是雷达图、数据点雷达图和填充雷达图。为了反映医院管理的质量，一个城市抽样调查了几家医院的六个指标(指标值越大，质量越高)，三家医院的调查结果如图2-65所示。请绘制雷达图以反映三家医院的管理质量。应用示例六，25，26，数据透视分析，数据透视表数据透视图表，27，数据透视表，数据透视表是一个交互式表格和图表，可以快速汇总大量数据并建立交叉列表。Exce

11、l50或更高版本提供了此功能，不仅可以转换行和列来查看源数据的不同汇总结果，显示不同的页面来过滤数据，还可以根据需要在区域中显示详细的数据，对于分析和组织复杂的数据非常方便和有用。透视表的最大特点是它的交互性。创建数据透视表后，您可以随意重新排列数据信息，也可以根据自己的习惯对数据进行分组。28，数据透视表布局，基于水平分类的字段，基于垂直分类的字段，基于统计的字段，基于分类显示(筛选)的字段，29，应用示例7，以下图数据为例分析药材的分布和产量。三十岁。若要制作数据透视表，请选择“数据”菜单中的“数据透视表和数据透视图”选项，弹出“数据透视表和数据透视图向导”对话框，并逐步完成。编辑透视表添

12、加、删除分类和统计字段统计字段的更改算法修改、删除透视表更新透视表中的数据，31。透视表，根据透视表3360创建透视表选择透视表中的任意单元格，然后单击透视表工具栏上的透视表按钮，然后单击透视表命令。32，数据透视图，从源数据创建数据透视图：从数据库的源数据创建数据透视图的方法类似于创建数据透视图报表。具体操作步骤如下：(1)点击数据列表，创建数据透视图。(2)在数据菜单中选择透视表和透视表图表，在弹出的对话框中选择透视表图表，逐步完成。返回，33，数据统计与分析，描述性统计检验分析z检验：双样本均值差检验F检验：双样本方差检验：双样本等方差假设检验：配对双样本均值方差分析单因素方差分析无重复

13、双因素分析可重复双因素分析相关系数和回归分析协方差相关系数回归分析，统计检验计算，检验分析报告，34，加载“分析工具库”的基本步骤如下：1。启动Excel2000，执行菜单栏中的“工具”|“加载项”命令，打开“加载项”对话框，并选择“分析工具库”复选框。2.单击确定返回工作表。执行菜单栏中的“工具”|“数据分析”命令，打开“数据分析”对话框，使用各种数据分析工具。注意：EXCEL必须“完全安装”，35。描述性统计是基本的分析工具之一，用于为输入区域的数据生成单变量统计分析报告，并提供关于数据中立性和可变性的信息，这是进一步数据分析的起点，并且可以指示哪个测试适合进一步讨论。描述统计的统计函数通

14、常包括：最大值、最小值、平均值、中值、几何量(GEOMEAN)、最大值(HARMEAN)、平均值(AVEDEV)、标准差(STDEV)、方差(VAR)、最大值(KURT)和偏斜度(SKW)函数，这些函数用于计算方差统计的最大值、最小值、平均值、中值、几何平均值、调和平均值、平均差、标准差、方差峰度和偏斜度。为了解决几个学生学习中存在的问题，一位老师分析了几个学生五次考试的结果。使用描述性统计分析工具分析其数据。返回，37，操作步骤，1。执行菜单栏中的“工具”|“数据分析”命令，打开“描述统计”对话框，在“分析工具”列表框中选择“描述统计”选项，点击“确定”按钮，打开“描述统计”对话框。3.在“

15、输入区域”编辑框中输入三列数据所在的单元格区域参考(4:澳元8澳元)。4.单击“逐行”选项。5.选择“在第一列中标记”选项。6.在“输出选项”下，单击“新建工作表组”选项，并在相应的编辑框中输入新工作表的名称，如“统计结果描述”。38，t检验：对平均值进行成对双样本分析。分析工具“t-检验：平均值的成对双样本分析”及其公式可用于成对双样本t-检验，以确定样本的平均值是否不相等。这个t检验形式并不假设两个总体的方差相等。当样本中出现自然配对观察时，可以使用这种配对测试。例如，一个样本组被测试两次，一次在实验前，一次在实验后。在一家医院用中药治疗了9例再生障碍性贫血，结果如下图所示。尝试“t检验：

16、成对均值分析”工具，分析治疗前后是否有变化。返回，40，操作步骤，(1)在Excel2003中创建新的Excel工作簿，并输入要分析的数据，如图2-108所示；(2)执行菜单栏中的“工具”|“数据分析”命令，打开“数据分析”对话框，选择“分析工具”列表框中的“t检验：成对平均值分析”选项。41，单向方差分析，该分析工具通过简单的方差分析对两个以上样本的平均值进行等式假设检验(样本取自具有相同平均值的样本总体)。该方法是双均值检验(如t检验)的扩展。42，应用例10，某医院采用三种方法治疗某一疾病，结果如下表2-2所示，三组之间的治愈天数有无差异？return，return，43，操作步骤，(1

17、)执行菜单栏中的“工具”|“数据分析”命令，打开“数据分析”对话框，在“分析工具”列表框中选择“方差分析：单向方差分析”选项；(2)点击确定，执行“方差分析：单向方差分析”对话框；44，协方差，“协方差”工具用于测量两个区域中的数据之间的关系，并用于返回每个数据点与其各自数据组的平均值之间的偏差积的平均值。协方差工具可用于确定两个区域中的数据变化是否相关，即一个集合的较大数据是否对应于另一个集合的较大数据(正协方差)；或者一个集合的较小数据是否对应于另一个集合的较大数据(负协方差)；或者两组数据不相关(协方差为零)。协方差对度量单位很敏感。45名和18名儿童的身高、体重和表面积的测量数据如下图所示。尝试用“协方差”分析工具分析身高、体重和表面积之间的关系。应用示例11，return，46，操作步骤，(1)执行菜单栏中的工具 | 数据分析命令，打开数据分析对话框，