175网络运行分析-常用分析工具-excel

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