使用EXCEL进行数据分析

使用EXCEL进行数据分析

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

版本：EXCEL2023完全安装典型安装最小化安装第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页【操作步骤】①在E4~E7单元格输入进行频率计算分段点{59,70,80,90}；②选中F4~F8单元格,作为输出统计成果单元格；③在公式栏输入“=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页数据透视表数据透视表是一种对大量数据迅速汇总和建立交叉列表交互式表格和图表。在Excel5．0以上版本就提供有这一功能，它不但能够转换行和列以查看源数据不一样汇总成果，显示不一样页面以筛选数据，还能够根据需要显示区域中明细数据，这对分析、组织复杂数据是非常方便有用。数据透视表最大特点就是它交互性。创建一种数据透视表后来，能够任意重新排列数据信息，并且还能够根据习惯将数据分组。第28页数据透视表－布局横向分类根据字段纵向分类根据字段统计根据字段分类显示（筛选）根据字段第29页应用举例七以下列图数据为例分析药材分布、产量情况。第30页数据透视表制作选择“数据”菜单中“数据透视表和数据透视图”选项，弹出“数据透视表和数据透视图向导”对话框，按步骤依次完成。

编辑数据透视表添加、删除分类和统计字段变化统计字段算法

修改、删除数据透视表更新数据透视表中数据

第31页数据透视图根据透视表建立透视图:选择数据透视表中任意单元格，然后在“数据透视表”工具栏上单击“数据透视表”按钮，单击“数据透视图”命令，。对应于透视表列字段对应于透视表行字段对应于透视表数据区域对应于透视表页字段第32页数据透视图根据源数据建立数据透视图：根据数据库源数据建立透视图办法类似于创建数据透视表，详细操作步骤如下：(1)单击欲建立数据透视图数据清单。(2)选择“数据”菜单中“数据透视表和数据透视图”选项，在弹出对话框中选择“数据透视图”，按步骤依次完成。返回第33页数据统计与分析

描述统计

检查分析

z-检查：双样本平均差检查

F-检查：双样本方差

t-检查：双样本等方差假设

t-检查：双样本异方差假设

t-检查：平均值成对二样本分析

方差分析

单原因方差分析

无反复双原因分析

可反复双原因分析

有关系数与回归分析

协方差有关系数回归分析统计检查计算检查分析报告第34页“分析工具库”加载其基本步骤如下：1、启动Excel2023，执行菜单栏中“工具”|“加载宏”命令，打开“加载宏”对话框，选中“分析工具库”复选框。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）在Excel2023，创建一种新Excel工作薄，如图2-108所示输入需要分析数据；（2）执行菜单栏中“工具”|“数据分析”命令，打开“数据分析”对话框，选中“分析工具”列表框中“t-检查：平均值成对二样本分析”选项。第41页单原因方差分析该分析工具通过简单方差分析，对两个以上样本平均值进行相等性假设检查（样本取自具有相同平均值样本总体）。此办法是对双平均值检查（如t-检查）扩充。第42页应用举例十某医院用三种办法治疗某种疾病，成果如下表2-2所示，试问三组治愈天数有没有差异？中药37762西药9121185中西结合12467返回返回第43页【操作步骤】（1）执行菜单栏中“工具”|“数据分析”命令，打开“数据分析”对话框，选中“分析工具”列表框中“方差分析：单原因方差分析”选项；（2）单击“确定”按钮，执行“方差分析：单原因方差分析”对话框；第44页协方差“协方差”工具用来度量两个区域中数据关系，用于返回各数据点与其各自所在数据组平均值之间偏差乘积平均值。使用协方差工具能够用来确定两个区域中数据变化是否有关，即一种集合较大数据是否与另一种集合较大数据相对应（正协方差）；或者一种集合较小数据是否与另一种集合较大数据相对应（负协方差）；还是两个集合中数据互不有关（协方差为零）。协方差对度量单位是敏感。第45页18名小朋友身高、体重、表面积测定数据如下列图所示，试利用“协方差”分析工具分析身高、体重、表面积三个指标之间关系。应用举例十一返回第46页【操作步骤】（1）执行菜单栏中“工具”|“数据分析”命令，打开“数据分析”对话框，选中“分析工具”列表框中“协方差”选项；（2）单击“确定”按钮，执行“协方差”对话框。第47页有关系数用于度量两个数据集（能够使用不一样度量单位）之间有关性，与两组数据有关分析和度量单位