Excel数据分析.ppt

Excel教程数据统计和处理分析学习要求熟悉熟悉Excel使用界面和基本操作使用界面和基本操作了解了解Excel的的常用统计函数常用统计函数掌握使用掌握使用Excel透视表透视表和透视图和透视图进行进行数数据分析和分类汇总据分析和分类汇总学会学会使用使用Excel进行数据进行数据高级高级分析分析Excel背景介绍Excel是微软公司是微软公司office系列办公软件的系列办公软件的组件之一，它是一个功能强大的组件之一，它是一个功能强大的电子表格电子表格程序程序，具有强大的，具有强大的数数据处理和数据分析据处理和数据分析能能力，是个人及办公事务中进行表格处理和力，是个人及办公事务中进行表格处理和数据分析的理想工具之一。数据分析的理想工具之一。Excel不仅可以将整齐而美观的表格呈现不仅可以将整齐而美观的表格呈现给用户，还可以用来进行数据的分析和预给用户，还可以用来进行数据的分析和预测，完成许多复杂的数据运算，帮助使用测，完成许多复杂的数据运算，帮助使用者做出更加有根据的决策。同时它还可以者做出更加有根据的决策。同时它还可以将表格中的数据通过各种各样的图形、图将表格中的数据通过各种各样的图形、图表的形式表现出来，增强表格的表达力和表的形式表现出来，增强表格的表达力和感染力感染力。Excel工作界面标题栏标题栏(工作簿名工作簿名称称)快速访问工快速访问工具栏具栏菜单栏菜单栏功能区功能区编辑编辑栏栏状态栏状态栏全选全选行标行标题题列列标标题题活动单元活动单元格格Excel功能使用统计函数统计数据使用统计函数统计数据按分类汇总统计数据按分类汇总统计数据使用图表统计数据使用图表统计数据数据的假设检验分析数据的假设检验分析数据的描述统计分析数据的描述统计分析规划求解与最优化方案选规划求解与最优化方案选择择使用排序和筛选分析数据使用排序和筛选分析数据数据的预测分析数据的预测分析数据的透视分析数据的透视分析问卷调查分析问卷调查分析使用交互图表分析数据使用交互图表分析数据数据的抽样分析数据的抽样分析课程内容1.Excel应用基础应用基础1.1 Excel1.1 Excel数据录入数据录入 1.2 Excel 1.2 Excel 数据处理数据处理 1.3 1.3 Excel Excel 安全设置安全设置2.Excel函数应用函数应用 2.1 Excel2.1 Excel函数介绍函数介绍 2.2 2.2 常用函数介绍常用函数介绍3.Excel图表应用图表应用 3.1 3.1 图表基础图表基础 3.2 3.2 常用图表常用图表4.数据透视表与数据透视图数据透视表与数据透视图4.1 4.1 数据透视表数据透视表 4.2 4.2 数据透视图数据透视图5.Excel统计分析功能统计分析功能5.1 5.1 描述统计描述统计 5.2 5.2 检验分析检验分析 5.3 5.3 方差分析方差分析5.45.4 回归分析回归分析1.1 Excel数据录入数据类型数据类型数字可进行数学计算【默认右对齐】数值（整数、小数、分数）日期、时间文本用于说明数据的含义，不进行数学计算【默认左对齐】中文汉字、字母数字和文本或符号的组成逻辑值TRUE、FALSE【默认居中对齐】日期默认使用/或 隔开1900-1-1时间默认使用：隔开，24小时制23：59：59日期时间日期时间默认用空格隔开1900-1-123：59：59设置单元格格式1.2 Excel数据处理排序（数据有序化）普通排序（内置序列）|自定义排序（自定义序列）单列/行排序|多列/行排序数据筛选仅显示满足条件的数据，隐藏不满足条件的数据自动筛选自定义筛选高级筛选筛选不重复记录删除重复项分类汇总对数据按类别进行汇总统计分析分类汇总之前一定要先进行排序各列须有列标题（字段名）分类汇总类型单字段分类汇总多字段分类汇总嵌套分类汇总1.3 Excel安全设置对工作表保护只希望对工作簿中的某一两个工作表设定只读或隐藏状态打开 审阅-保护工作表选择允许用户进行的操作输入保护密码对工作簿保护需要对整个工作簿进行安全设置打开 审阅-保护工作簿输入保护密码补充：工作簿和工作表区别？工作簿是处理和存储数据的文件。由于每个工作簿可以包含多张工作表，因此可在一个文件中管理多种类型的相关信息。工作表可以显示和分析数据。用户能够同时在多张工作表上输入并编辑数据，并且可以对不同工作表的数据进行汇总计算。在创建图表之后，既可以将其置于源数据所在的工作表上，也可以放置在单独的图表工作表上。函数定义函数就是预定义的公式。它使用一些参数进行运算并产生返回结果。Excel中有350多个内置函数，分数学与三角函数、财务函数、统计函数、工程函数等11大类，用户还可以使用VBA自定义函数。函数可以分为有参数函数和无参数函数函数可以嵌套调用例：=IF(AVERAGE(A2:A5)50,SUM(B2:B5),0)=IF(A2=10,”科技处”，IF(A2=20,”财务处”，“人事处”)2.1 Excel函数介绍 公式与函数区别？公式和函数使Excel实现数据计算的重要工具公式：是对数据执行计算和操作的等式函数：是预先定义的计算公式，可实现复杂的计算和操作公式：公式：=（A1+A2+A3+A100）/100函数：函数：=AVERAGE（A1：A100）函数组成参数用括号()括起参数类型：无参数型括号必须保留 有参数型1个/固定数量/不确定数量u 必要参数u 可选参数参数形式：引用、表达式、函数、常量函数名称函数名称(参数参数1，参数，参数2，参数参数3,.)SUM(A1，C3:C6,E5/3,SUM(F1:F9),325)2.2 常见函数介绍统计数据的统计数据的个数的函数个数的函数COUNT计算日期和数值的个数COUNTA计算数据的个数COUNTBLANK 计算空白单元格的个数COUNTIF求满足条件的单元格个数求平均值的求平均值的函数函数AVERAGE计算数值数据的平均值AVERAGEA计算所有数据的平均值计算倍数和计算倍数和众数的函数众数的函数MEDIAN计算数据群的中位数MODE计算数据群的众数计算最大值和最计算最大值和最小值的函数小值的函数MAX计算数值的最大值MAXA计算所有数据的最大值MIN计算数值的最小值MINA计算所有数据的最小值计算方差的函数计算方差的函数VAR计算数据群的中位数VARA计算数据群的众数VARP通过数值计算方差VARPA通过所有数据计算方差制作频度的函数制作频度的函数FREQUENCY计算区间里所含数值的个数计算标准偏差的计算标准偏差的函数函数STDEV通过数值推测数据集的标准偏差计算各种总计值计算各种总计值的函数的函数SUBTOTAL计算各种总计值在单元格中插入函数找到公式选项选择插入函数根据函数类别找到插入函数选择处理的数据区域应用举例一某班级的成绩表，如下图所示，其中有两个学生的该门课程缺考，需要分别用到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,69,79,89；选中F4F8单元格,作为输出统计结果的单元格；在公式栏输入“=FREQUENCY(B2:B11,E4:E8)”，然后按下“CtrlShiftEnter”组合键锁定数组公式即可。输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按CtrlShiftEnter组合键锁定数组公式，Excel将在公式两边自动加上花括号“”。CORRELCORREL函数函数功能：计算单元格区域中的两个数列之间的相关系数。语法形式:CORREL（arrnyl，arrny2）arrnyl和arrny2表示的都是两个数据列CORREL函数可以用来分辨两种属性之间的关系。某医生为了探讨缺碘地区母婴TSH水平关系，应用免疫放射分析测定了160名孕妇（孕周1517w）及分娩时脐带血TSH水平（mU/L），现随机抽取10对数据如图所示，试求脐带血TSH水平对母血TSH水平的相关系数。应用举例三【操作步骤】选中结果输出单元格B14；在公式栏输入“=CORREL(C3:C12,B3:B12)”,点击公式栏 按钮；在单元格A14输入“相关系数”即可。链接：相关系数相关系数是研究变量之间线性相关程度的量，一般用字母 r 表示。Cov(X,YCov(X,Y)为为X X与与Y Y的协方差，的协方差，VarXVarX为为X X的方差，的方差，VarYVarY为为Y Y的的方差方差r r（X X，Y Y）是表示变量）是表示变量X X与与Y Y之间紧密程度的量之间紧密程度的量相关系数定量地刻画了 X 和 Y的相关程度，即|xy|越大，相关程度越大；|xy|=0越大，相关程度越大；3.1 图表基础图表介绍图表：是数据的图形化表示，是动态的随对应数据的变 化而自动更新。将数据用图来描述：抽象、枯燥的数据形象直观、易于理解清晰的表现出数据的差异、变化趋势和数据间的关系采用不同的图表类型，可以发现数据不同方面的特点规律 图表放置位置与数据位于相同工作表中嵌入式图表单独占据一个新工作表图表工作表图表组成图表区图表区绘图区绘图区数据系列数据系列(某一行某一行/列列)横坐标横坐标(分类轴分类轴)纵坐标纵坐标(数值轴数值轴)网格线网格线图例图例标题标题(图表标题、横纵坐标标题图表标题、横纵坐标标题)数据标签数据标签3.2 常用图表柱形图：柱形图：显示一段时间内数据变化/各项之间的比较情况折线图：折线图：显示在相等时间间隔下数据的变化趋势饼饼图：图：显示一个数据系列的中各项大小与各项总和的比例条形图：条形图：显示各项之间的比较情况（适于分类轴标签很长的情况）面积面积图：图：强调数量随时间变化的程度/引起对总之趋势 的注意XY散点图：散点图：显示若干数据系列中两个变量之间的关系Excel中常见图表创建图表过程确保数据适合图表选择包含数据的区域插入图表选择图表类型 生成基本图表“图表工具”关联菜单 更改外观/布局、图表数据、图表元素修饰/修改图表为了反映医院管理的质量，某市抽样调查了几所医院的6 项指标（指标值越大表示质量越高），其中三所医院的调查结果如图2-65所示，请绘制雷达图反映三所医院的管理质量。应用举例四数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格。它不仅可以转换行和列行和列以查看源数据的不同汇总结果，显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据，这对分析、组织复杂数据是非常方便有用的。数据透视表最大的特点就是它的交交互性互性。创建一个数据透视表以后，可以任意重新排列数据信息，并且还可以根据习惯将数据分组。4.1 数据透视表数据透视表的优势大量数据的速度汇总多维度的数据分析通过筛选对重点关注内容的专题分析生成动态报表，保持与数据源同步更新值显示方式，可以一步搞定百分比（同比、环比、占比.）一张数据透视表仅靠鼠标移动字段位置，可变换出各种类型的报表创建数据透视表制作数据透制作数据透视表所需要视表所需要的数据的数据选择数据源选择数据源选择透视表选择透视表位置位置数据透视表设置和显示数据透数据透视表视表数据透视表数据透视表设置区域设置区域4.2 数据透视图 数据透视图可以看作是数据透视表和图表的结合，它以图形的形式表示数据透视表中的数据。正像在数据透视表里那样，用户可以更改数据透视图报表的布局和显示的数据。数据透视图具有Excel图表显示数据的所有能力，而且同时又具有数据透视表的方便和灵活等特性。创建数据透视图1、选中数据、选中数据透视表透视表2、点击插入、点击插入3、插入数据、插入数据透视图透视图4、选择插入的、选择插入的透视图透视图数据透视图数据透视表数据透视表数据透视图数据透视图5.1 描述统计描述统计是基础分析工具之一，用于生成对输入区域中数据的统计分析报表，提供有关数据趋中性和易变性的信息，而这些信息是进一步分析数据的起点，可以指示哪一种检验适合进一步的探讨。描述统计的统计函数一般包括：MAXMAX、MINMIN、AVERAGEAVERAGE、MEDIANMEDIAN、GEOMEANGEOMEAN、HARMEANHARMEAN、AVEDEVAVEDEV、STDEVSTDEV、VARVAR、KURTKURT和和SKEWSKEW函数等，他们分别用于计算最大值、最小值、平均值、中位数、几何平均数、调和平均数，以及变异统计的平均差、标准差、方差峰度和偏度。某老师对几名学生的五次考试成绩进行分析，以便对这几个学生在学习方面存在的问题加以解决。使用“描述统计”分析工具对其数据进行分析。应用举例五1、执行菜单栏的“数据”|“数据分析”命令，打开“描述统计”对话框。2、在“输入区域”编辑框中键入三列数据所在的单元格区域引用（$A$4:$F$8）。3、单击“逐行”选项。4、选中“标志位于第一列”选项。5、在“输出选项”下单击“新工作表组”选项，并在对应编辑框中输入新工作表的名称，如：“描述统计结果”。【操作步骤】5.2 检验分析t-检验：“平均值的成对双样本分析”分析工具及其公式可以进行成对双样本t-检验，用来确定样本平均值是否不等。此t-检验窗体并不假设两个总体的方差是相等的。当样本中出现自然配对的观察值时，可以使用这种成对检验。例如，对一个样本组进行了两次检验，抽取实验前的一次和实验后的一次。某医院用中药治疗9例再生障碍性贫血，结果如下图所示。试用“t-检验：平均值的成对二样本分析”工具，分析治疗前后是否有改变？应用举例六【操作步骤】（1）在Excel2016，创建一个新的Excel工作薄，如上图所示输入需要分析的数据；（2）执行菜单栏中的“数据”|“数据分析”命令，打开“数据分析”对话框，选中“分析工具”列表框中的“t-检验：平均值的成对二样本分析”选项。5.3 方差分析单因素方差分析该分析工具通过简单的方差分析，对两个以上样本平均值进行相等性假设检验（样本取自具有相同平均值的样本总体）。此方法是对双平均值检验（如t-检验）的扩充。某医院用三种方法治疗某种疾病，结果如下表所示，试问三组治愈天数有无差别？中药中药37762西药西药9121185中西结中西结合合12467应用举例七【操作步骤】（1）执行菜单栏中的“数据”|“数据分析”命令，打开“数据分析”对话框，选中“分析工具”列表框中的“方差分析：单因素方差分析”选项；（2）单击“确定”按钮，执行“方差分析：单因素方差分析”对话框；协方差“协方差”工具用来度量两个区域中数据的关系，用于返回各数据点与其各自所在数据组的平均值之间的偏差乘积的平均值。使用协方差工具可以用来确定两个区域中数据的变化是否相关，即一个集合的较大数据是否与另一个集合的较大数据相对应（正协方差）；或者一个集合的较小数据是否与另一个集合的较大数据相对应（负协方差）；还是两个集合中的数据互不相关（协方差为零）。协方差对度量单位是敏感的。18名儿童身高、体重、表面积测定数据如下图所示，试利用“协方差”分析工具分析身高、体重、表面积三个指标之间的关系。应用举例八【操作步骤】（1）执行菜单栏中的“数据”|“数据分析”命令，打开“数据分析”对话框，选中“分析工具”列表框中的“协方差”选项；（2）选择数据输入区域和输出区域（3）单击“确定”按钮，执行“协方差”对话框。5.4 回归分析通过对一组观察值使用“最小二乘法”直线拟合进行线性回归分析，可用来分析单个因变量是如何受一个或几个自变量影响的。例如，观察某个运动员的运动成绩与一系列统计因素的关系，如年龄、身高和体重等。可以基于一组已知的成绩统计数据，确定这三个因素分别在运动成绩测试中所占的比重，使用该结果可对尚未进行过测试的运动员的表现做出预测。在实际工作中，相关系数与回归分析是研究现象之间相互依存关系的不可分割的两个方面。一般先进行相关分析，根据相关系数或相关指数的大小对变量进行筛选，剔除不相关或是相关性小的变量，然后再进行回归分析，建立回归模型，进行预测。回归分析步骤1、根据预测目标，确定自变量和因变量2、绘制散点图，确定回归模型3、估计模型参数，建立回归模型4、对回归模型进行检验5、利用回归模型进行预测设有某地10名3岁儿童体重与表面积资料如下图所示，试做相关系数分析和回归分析。应用举例九【操作步骤】（1）首先进行相关系数的分析。执行菜单栏中的“数据”|“数据分析”|“相关系数”选项；执行“相关系数分析”对话框依次完成。（2）由于3岁儿童的体重与体表面积有较强的相关性，因此需要利用回归分析工具进一步建立回归模型。执行菜单栏中的“数据”|“数据分析”|“回归”选项；执行“回归”对话框依次完成。补充：时间序列预测除了回归分析预测，另一种预测方法就是根据时间发展进行预测。时间序列预测法的基本特点：假设事物发展趋势会延伸到未来预测所依据的数据具有不规则性不考虑事物发展之间的因果关系主要包括移动平均法移动平均法、指数平滑法指数平滑法等3.5 移动平均移动平均法是一种改良的算术平均法，它是根据时间序列逐期退役，依次计算包含一定期数的平均值，形成平均值时间序列，以反映事物发展趋势的一种预测方法。移动期数的大小视具体情况而定，移动期数少，能快速地反映变化，但不能反映变化趋势；移动期数多，能反映变化趋势，但预测值带有明显的滞后偏差。移动平均可以消除或减少时间序列数据受偶然性因素干扰而产生的随机变动影响，适合短期预测。移动平均公式Ft=(At-1+At-2+At-3+At-n)/n式中Ft对下一期的预测值；n移动平均的时期个数；At-1前期实际值；At-2,At-3和At-n分别表示前两期、前三期直至前n期的实际值。应用简单移动平均法预测2001年单位面积产量。应用举例十【操作步骤】（1）切换到“数据”卡，单击“数据分析”按钮，在弹出的对话框中选择“移动平均”选项，单击“确定”按钮。（2）在弹出的“移动平均”对话框中，进行相关设置后单击“确定”按钮。（3）生成移动平均结果数据和图形，进行分析并预测下一年的产量。3.5 指数平滑指数平滑法是一种改良的加权平均法，在不舍弃历史数据的前提下，对离预测期较近的历史数据给予较大的权数，权数由近到远按指数规律递减。指数平滑法根据本期的实际值和预测值，借助平滑系数进行加权平均计算，预测下一期的值。它是对时间序列数据给予加权平滑，从而获得其变化规律与趋势。平滑系数、阻尼系数阻尼系数根据时间序列的变化特性来选取。指数平滑公式St=ayt+(1-a)St-1式中，St时间t的平滑值；yt时间t的实际值；St-1时间t-1的平滑值；a平滑常数，其取值范围为0,1对某商场的连续若干个月的营业额进行指数平滑法预测分析。应用举例十一【操作步骤】（1）选择“工具”下拉菜单（2）选择“数据分析”选项，并选择“指数平滑”，然后确定（3）在对话框中输入数据区域，输入阻尼系数（1-）值，（为平滑系数取0.7），然后“确定”。补充：补充：Excel2016 添加数据分析工具步骤添加数据分析工具步骤点击 文件选项加载项选择 分析工具库点击 转到确定在数据选项下找到数据分析选项谢谢