使用EXCEL进行数据分析

使用EXCEL进行数据分析

主讲老师：李玲娟数据分析与数据挖掘数据信息统计分析（报表）数据挖掘（专业工具和算法）掌握EXCEL的常用统计函数；熟悉使用EXCEL透视表进行数据分析和分类汇总；了解使用EXCEL进行数据分析教学要求实验环境

版本：EXCEL2000完全安装典型安装最小化安装讲授内容常用统计函数数据透视分析图表数据透视表(图)数据分析描述统计检验分析z-检验：双样本平均差检验

F-检验：双样本方差

t-检验：双样本等方差假设t-检验：双样本异方差假设t-检验：平均值的成对二样本分析方差分析单因素方差分析无重复双因素分析可重复双因素分析

相关系数与回归分析

协方差相关系数回归分析

常用统计函数一般统计函数：用来统计给定某个区域的数据的平均值、最大值或者最小值等

数理统计函数：对数据进行相关的概率分布的统计，进行回归分析等。应用举例一某班级的成绩表，如下图所示，其中有两个学生的该门课程缺考，需要分别用到AVERAGE和AVERAGEA函数来计算两个不同的平均值。【操作步骤】①在单元格D3中输入公式“=AVERAGE(B2:B9)”，可以计算参加考试学生的平均成绩，函数AVERAGE在计算时对缺考的两个学生的成绩自动认为是0。②在单元格D6中输入公式“=AVERAGEA(B2:B9)”，可以计算全体学生的平均成绩，对缺考的学生成绩，AVERAGE函数将自动忽略。应用举例二某班级的成绩表，如下图所示。需要用到FREQUENCY函数来来统计各个分数段的分布学生人数。FREQUENCY函数功能：统计指定单元格区域中数据的频率分布。由于函数FREQUENCY返回一个数组，必须以数组公式的形式输入。语法形式:FREQUENCY(data_array,bins_array)data_array为一数组或对一组数值的引用，用来计算频率。如果data_array中不包含任何数值，函数FREQUENCY返回零数组。bins_array为一数组或对数组区域的引用，设定对data_array进行频率计算的分段点。如果bins_array中不包含任何数值，函数FREQUENCY返回data_array元素的数目。【操作步骤】①在E4~E7单元格输入进行频率计算的分段点{59,70,80,90}；②选中F4~F8单元格,作为输出统计结果的单元格；③在公式栏输入“=FREQUENCY(B2:B11,E4:E8)”，然后按下“Ctrl＋Shift＋Enter”组合键锁定数组公式即可。输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按Ctrl＋Shift＋Enter组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”。CORREL函数功能是单元格区域中的两个数列之间的相关系数。表达式为CORREL（arrnyl，arrny2）。arrnyl和arrny2表示的都是两个数据列CORREL函数可以用来分辨两种属性之间的关系。应用举例三某医生为了探讨缺碘地区母婴TSH水平关系，应用免疫放射分析测定了160名孕妇（孕周15～17w）及分娩时脐带血TSH水平（mU/L），现随机抽取10对数据如图2-8所示，试求脐带血TSH水平对母血TSH水平的相关系数。【操作步骤】①选中结果输出单元格B14；②在公式栏输入“=CORREL(C3:C12,B3:B12)”,点击公式栏按钮；③在单元格A14输入“相关系数”即可。应用举例四某地120名7岁男童的身高，已知身高均数为119.41cm，标准差为4.38cm。试估计该地7岁男童身高在110cm以下者的概率，身高在110cm～120cm之间的概率。如果要制定一个健康标准使得90％的7岁男童符合健康标准，那么健康标准应该定多少？

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表示分布的标准偏差。应用举例五某地120名7岁男童的身高，已知身高均数为119.41cm，标准差为4.38cm,如下图所示。试估计该地7岁男童身高在110cm以下者的概率，身高在110cm～120cm之间的概率。如果要制定一个健康标准使得90％的7岁男童符合健康标准，那么健康标准应该定多少？【操作步骤】①在单元格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。分析结果=NORMDIST(110,B1,B2,TRUE)=NORMDIST(120,B1,B2,TRUE)-C5=NORMINV(0.9,B2,B3)图表概念：工作表数据的图形化表示、清晰易懂、形象直观。种类：14种图表类型，包含了若干种不同的图表子类型。在处理医药数据中常用的几类图表：1、折线图2、柱形图3、饼图4、XY散点图5、条形图6、雷达图柱形图柱形图是Excel的默认图表类型，也是用来处理医药数据常用的图表类型之一。通常把每个数据点显示为一个垂直柱体，其高度对应于数值，用来显示一段时期内数据的变化或描述各项之间的比较关系。分类项水平组织，数值垂直组织，即强调数据随时间的变化。柱形图共有7种子图表类型：簇状柱形图、堆积柱形图、百分比堆积柱形图、三维簇状柱形图、三维堆积柱形图、三维百分比堆积柱形图和三维柱形图。。应用举例五在药物实验中的应用，现有药物止血效果实验采集的数据表，A列为实验对照组和七种不同的止血药物，分别做了六次药物止血实验，得到六组凝血时间实验数据（单位：秒）。通过柱形图可以形象地描绘了这七种不同药物止血功能的差异。

返回【操作步骤】(1)选择图表类型，单击“插入”|“图表”。

(2)选择图表源数据。

(3)设置标题和图例。

(4)确定图表位置。

(5)编辑图表区。用户需要编辑的内容包括颜色、填充和边框等。(6)编辑数据轴、分类轴。雷达图雷达图是由一个中心向四周射出多条数值坐标轴，每个指标都拥有自己的数值坐标轴，把同一数据序列的值用折线连接起来而形成。雷达图用来比较若干个数据序列指标的总体情况，可比较多个单位不同指标的数据特征，如最大值、最小值等。雷达图的3个子图表类型分别为雷达图、数据点雷达图和填充雷达图。

为了反映医院管理的质量，某市抽样调查了几所医院的6项指标（指标值越大表示质量越高），其中三所医院的调查结果如图2-65所示，请绘制雷达图反映三所医院的管理质量。应用举例六数据透视分析数据透视表数据透视图

数据透视表数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格和图表。在Excel5．0以上版本就提供有这一功能，它不仅可以转换行和列以查看源数据的不同汇总结果，显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据，这对分析、组织复杂数据是非常方便有用的。数据透视表最大的特点就是它的交互性。创建一个数据透视表以后，可以任意重新排列数据信息，并且还可以根据习惯将数据分组。数据透视表－布局横向分类依据的字段纵向分类依据的字段统计依据的字段分类显示（筛选）依据的字段应用举例七以下图数据为例分析药材的分布、产量情况。数据透视表的制作选择“数据”菜单中的“数据透视表和数据透视图”选项，弹出“数据透视表和数据透视图向导”对