Excel数据分析处理

火龙果整理uml.org.cn2013年4月9日火龙果整理uml.org.cnMicrosoftExcel是微软公司的办公软件Microsoftoffice的组件之一，是由Microsoft为Windows和AppleMacintosh操作系统的电脑而编写和运行的一款试算表软件。Excel是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。版本：Excel2003、Excel2007、Excel2010、Excel2013火龙果整理uml.org.cn数据的输入数据的运算数据整理排序统计汇总查找与替换筛选基础数据统计与分析数据透视表数据统计分析数据的相关性与回归分析数据的相关性分析回归分析数据的预测分析移动平均法回归法指数平滑火龙果整理uml.org.cn1、数值的输入正数负数小数分数2、文本的输入一般文本特殊文本3、相同数据的输入一般复制法自动填充法Ctrl+Enter组合键4、其他的输入连接符固定格式输入等差序列等比序列使用数据有效性列表选择数据火龙果整理uml.org.cn1、数值的输入正数如“+888”类的数据，可直接输入888，省略+号负数如“-888”类的数据，可直接输入-888小数如0.888类的数据，可直接输入分数表现为”ab/c”的形式，如1/7则需要输入”01/7”，否则会认为是日期火龙果整理uml.org.cn2、文本的输入一般文本可直接输入特殊文本如编号“001”之类的，需要输入“’001”，’为英文中半角单引号，利用自动填充工具可获得“001、002、003…”火龙果整理uml.org.cn3、相同数据的输入一般复制法复制粘贴自动填充法将鼠标移动单元格的右下角，当鼠标变成黑色的+号，向下拖动鼠标即可完成该单元格内容的复制Ctrl+Enter组合键选中单元格区域，输入内容，同时按住Ctrl+Enter组合键，则该区域单元格内容均为输入内容火龙果整理uml.org.cn4、其他输入技巧连接符&默认的Excel数值精度为15位，若超过15位，则用&连接，如=”12345678901111”&”001”或者加入双引号自定义格式输入选中区域单击数字(或者右击选择设置单元格格式)数字选项内的自定义火龙果整理uml.org.cn等差序列给定初始值选中区域开始选项中编辑内的填充序列给定初始的两个值，再选定，然后利用填充工具生成步长为1的等差数列可用给定初始值，利用Ctrl+填充等比数列给定初始值选中区域开始选项中编辑内的填充序列使用数据有效性类表选择数据先选定区域数据选项中的数据工具数据有效性设置允许中选择序列输入序列（注意要用英文半角逗号分隔）火龙果整理uml.org.cn利用公式进行运算利用函数进行运算利用自动求和工具火龙果整理uml.org.cn1、利用公式进行计算什么是公式公式是对工作表中的数值执行计算的等式。公式是以等号(=)开头的。公式可以包含函数、引用、运算符和常量等内容。常用运算符算术运算符+-*/%比较运算符大于小于等于连接运算符&引用运算符A2:B2输入公式以及利用填充工具辅助输入数据火龙果整理uml.org.cn2、利用函数进行计算函数的定义与结构函数是运用一些被称为参数的特定数据值按特定的顺序或结构进行计算的。函数的结构可分为函数名和参数两个部分，其表达式为：函数名(参数1，参数2，参数3，…)函数的参数可为数字、文本、逻辑值、表达式、引用或者其他函数函数的分类统计、财务、数学与三角函数、时间与日期等插入函数公式插入函数选择函数输入函数参数火龙果整理uml.org.cn几个常用的函数IF函数AVERAGE函数SUMCOUNT、COUNTIFSTDEVVARMAXMIN火龙果整理uml.org.cn3、利用自动求和工具火龙果整理uml.org.cn1、排序单列数据排序多关键字排序自定义次序排序2、统计汇总一级分类汇总嵌套分类汇总显示和隐藏明细数据3、查找与替换快速查找替换高级查找与替换4、筛选自动筛选自定义筛选高级筛选火龙果整理uml.org.cn1、排序(1)单列数据排序数据排序和筛选升序、降序火龙果整理uml.org.cn(2)多关键字排序数据排序选择主要关键字与次要关键字火龙果整理uml.org.cn(3)自定义次序排序数据排序次序(选择自定义次序)输入次序火龙果整理uml.org.cn2、统计汇总(1)一级分类汇总先给数据排序单击分类汇总选择分类字段和汇总方式以及汇总项火龙果整理uml.org.cn(2)嵌套分类汇总先多关键字数据排序第一次创建分类汇总（同一级分类汇总）再次创建汇总（注意去掉替换当前分类汇总选项）…火龙果整理uml.org.cn(3)显示和隐藏明细数据点击左边的级别1,2,3…以及利用左侧的+号火龙果整理uml.org.cn火龙果整理uml.org.cn3、查找与替换(1)快速查找开始编辑查找与选择查找(或者快捷键CTRL+F)火龙果整理uml.org.cn(2)替换开始编辑查找与选择替换火龙果整理uml.org.cn(3)高级查找与替换开始编辑查找与选择选择选项（可查找字体、颜色、大小等）火龙果整理uml.org.cn4、筛选(1)自动筛选数据筛选选择条件火龙果整理uml.org.cn(2)自定义筛选数据筛选数字筛选选择筛选条件填写筛选条件内容(数字或文本)火龙果整理uml.org.cn火龙果整理uml.org.cn(3)高级筛选数据排序和筛选高级选择列表区域设置条件区域火龙果整理uml.org.cn1、数据透视表(1)数据透视表概述(2)创建空的数据透视表(3)数据透视表设置(4)组合字段(5)使用排序、筛选、分类汇总分析数据(6)利用公式添加计算区域(7)创建数据透视图动态分析数据2、数据的统计分析(1)集中趋势分析算术平均值几何平均值调和平均值众数中位数(2)离散趋势分析方差、标准差、四分位数差(3)抽样分析(4)概率分布分析(5)数据描述火龙果整理uml.org.cn1、数据透视表(1)数据透视表概述数据透视表是一种可快速汇总大量数据的交互式工具。使用数据透视表可以深入分析数值数据，并且可以回答一些预计不到的数据问题。以多种友好方式查询大量数据。对数值数据进行分类汇总和聚合，创建自定义计算和公式。能进行数据筛选、排序、分组和有条件的设置格式，使用户能够关注所需的信息。提供简明、有吸引力，且带有批注的联机报表或打印报表。火龙果整理uml.org.cn(2)创建空的数据透视表插入数据透视表火龙果整理uml.org.cn(3)数据透视表的设置火龙果整理uml.org.cn(4)组合字段选项将所选内容分组火龙果整理uml.org.cn(5)使用排序、筛选、分类汇总分析数据火龙果整理uml.org.cn火龙果整理uml.org.cn火龙果整理uml.org.cn(6)利用公式添加计算区域选项公式计算字段火龙果整理uml.org.cn(7)创建数据透视图动态分析数据选中数据选项数据透视图火龙果整理uml.org.cn2、数据的统计分析(1)集中趋势分析算术平均值：集合或数组平均值的值。(在单元格中输入=AVERAGE)几何平均值：n个数乘积开n次方根的值。(公式插入函数GEOMEAN)调和平均值：各个数值的倒数的平均数的倒数。(公式插入函数HARMEAN)众数：集合中出现次数最多的数值。(公式插入函数MODE)中位数：是指将统计总体当中的各个变量值按大小顺序排列起来，形成一个数列，处于变量数列中间位置的变量值就称为中位数。当变量值的项数N为奇数时，处于中间位置的变量值即为中位数；当N为偶数时，中位数则为处于中间位置的2个变量值的平均数。(公式插入函数MEDIAN)火龙果整理uml.org.cn(2)离散趋势分析方差(Variance)：方差是各个数据与平均数之差的平方的平均数。在概率论和数理统计中，方差用来度量随机变量和其数学期望（即均值）之间的偏离程度。在许多实际问题中，研究随机变量和均值之间的偏离程度有着很重要的意义。(公式插入函数VAR)标准差(StandardDeviation)：也称均方差(meansquareerror)，是各数据偏离平均数的距离的平均数，它是离均差平方和平均后的方根，用σ表示。标准差是方差的算术平方根。标准差能反映一个数据集的离散程度。平均数相同的，标准差未必相同。(公式插入函数STDEV)四分位数差：是上四分位数与下四分位数之差,用四分位数间距可反映变异程度的大小。QUARTILE(xx:xx,3)-QUARTILE(xx:xx,1)QUARTILE(xx:xx,1)表示下四分位QUARTILE(xx:xx,2)表示中位数QUARTILE(xx:xx,3)表示上四分位火龙果整理uml.org.cn巧妙使用描述统计分析工具数据数据分析描述统计火龙果整理uml.org.cn(3)抽样分析准备工作：由于默认的Excel数据分析工具没有安装，所以要在Excel选项加载项Excel加载项(转到)选择分析工具库确定火龙果整理uml.org.cn抽样分析即是对抽样调查结果进行分析，抽样调查是指从调查对象总体中，按照随机原则选取一部分对象作为样本进行调查分析，以此推论总体状况的一种调查方式，在一定程度上反映了总体的数据分布趋势。数据数据分析抽样参数设置确定再进行相关统计分析火龙果整理uml.org.cn如右图所示，设置输入区域，需要随机产生的样本数以及输出区域。在抽样得到的样本可以进行进一步的数理统计，如用AVERVAGE,STDEV,以及利用组合函数COUNTIF(xx:xx,”condition”)/COUNT(xx:xx)求出比例等。火龙果整理uml.org.cn(4)概率分布分析概率分布是概率论的基本概念之一，用以表述随机变量取值的概率规律。二项分布：即重复n次的伯努利试验。在每次试验中只有两种可能的结果，而且是互相对立的，是独立的，与其它各次试验结果无关，结果事件发生的概率在整个系列试验中保持不变，则这一系列试验称为伯努力试验。概率公式：(公式插入函数BINOMDIST)火龙果整理uml.org.cn泊松分布：泊松分布适合于描述单位时间（或空间）内随机事件发生的次数。如某一服务设施在一定时间内到达的人数，电话交换机接到呼叫的次数，汽车站台的候客人数，机器出现的故障数，自然灾害发生的次数，一块产品上的缺陷数，显微镜下单位分区内的细菌分布数等等。概率公式：(公式插入函数POISSON)火龙果整理uml.org.cn(5)数据描述使用直方图分析数据的频率分布数据数据分析直方图火龙果整理uml.org.cn1、数据的相关性分析(1)散点图法分析(2)协方差法分析(3)相关系数法分析2、回归分析(1)回归概述(2)一元回归分析(3)多元回归分析(4)非线性回归分析火龙果整理uml.org.cn1、数据的相关性分析分析变量之间关系程度的大小和方向的相互走向联系，就是数据之间的相关性问题分析。(1)散点图法分析先排序插入图表(散点图)火龙果整理uml.org.cn(2)协方差法分析协方差为每对数据点的偏差乘积的平均数，利用协方差可以决定两个数据集之间的关系，协方差越大，表明两组数据间的相关性越大。下面利用协方差工具分析数据分析协方差火龙果整理uml.org.cn(3)相关系数法分析