使用EXCEL进行数据分析 主讲老师：李玲娟数据分析与数据挖掘数 据信 息统计分析（报表）数据挖掘（专业工具和算法） 掌握EXCEL的常用统计函数； 熟悉使用EXCEL透视表进行数据分析 和分类汇总； 了解使用EXCEL进行数据分析实验环境版本：EXCEL 2000 完全安装 典型安装 最小化安装讲授内容 常用统计函数 数据透视分析 图表 数据透视表(图) 数据分析 描述统计 检验分析 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 元素的数目。【操作步骤】 在E4E7单元格输入进行频率计算的 分段点59,70,80,90； 选中F4F8单元格,作为输出统计结果 的单元格； 在公式栏输入 “=FREQUENCY(B2:B11,E4:E8)”，然 后按下“CtrlShiftEnter” 组合键锁定 数组公式即可。 输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个 单元格），在编辑栏输入公式，然后按CtrlShiftEnter组合键锁定 数组公式，Excel将在公式两边自动加上花括号“ ”。 CORREL函数 功能是单元格区域中的两个数列之间的相 关系数。 表达式为CORREL（arrnyl，arrny2） 。 arrnyl和arrny2表示的都是两个数据列 CORREL函数可以用来分辨两种属性之间的 关系。应用举例三 某医生为了探讨缺碘地区母婴TSH水平关系 ，应用免疫放射分析测定了160名孕妇（孕 周1517w）及分娩时脐带血TSH水平（ mU/L），现随机抽取10对数据如图2-8所 示，试求脐带血TSH水平对母血TSH水平 的相关系数。【操作步骤】选中结果输出单元格B14； 在公式栏输入 “=CORREL(C3:C12,B3:B12)”,点击公 式栏 按钮； 在单元格A14输入“相关系数”即可。应用举例四 某地120名7岁男童的身高，已知身高均数为 119.41cm，标准差为4.38cm。试估计该地7岁男童 身高在110cm以下者的概率，身高在110cm120cm 之间的概率。如果要制定一个健康标准使得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”，计 算成功身高在110cm120cm之间的概率为 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所示，请绘制雷达图反映三 所医院的管理质量。应用举例六应用举例六数据透视分析 数据透视表 数据透视图 数据透视表 数据透视表是一种对大量数据快速汇总和建立 交叉列表的交互式表格和图表。 在Excel 50以上版本就提供有这一功能，它 不仅可以转换行和列以查看源数据的不同汇总 结果，显示不同页面以筛选数据，还可以根据 需要显示区域中的明细数据，这对分析、组织 复杂数据是非常方便有用的。 数据透视表最大的特点就是它的交互性。创建 一个数据透视表以后，可以任意重新排列数据 信息，并且还可以根据习惯将数据分组。数据透视表布局横向分类依 据的字段 纵向分类依 据的字段 统计依据的 字段 分类显示（筛选） 依据的字段 应用举例七以下图数据为例分析药材的分布、产量情况。数据透视表的制作选择“数据”菜单中的“数据透视表和数据 透视图” 选项，弹出 “数据透视表和数据 透视图向导”对话框，按步骤依次完成。 编辑数据透视表 添加、删除分类和统计字段 改变统计字段的算法 修改、删除数据透视表 更新数据透视表中的数据 数据透视图根据透视表建立透视图:选择数据透视表 中的任意单元格，然后在“数据透视表”工 具栏上单击“数据透视表”按钮，单击“数 据透视图”命令，。 对应于透视 表的列字段对应于透视 表的行字段对应于透视表 的数据区域对应于透视 表的页字段数据透视图根据源数据建立数据透视图：根据数据库 的源数据建立透视图的方法类似于创建数 据透视表，具体操作步骤如下： (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、单击“逐行