Excel高级运用和数据分析

1、Excel高级运用和数据分析蒋晓君Contents目目 录录数据分析数据分析 Excel的函数运用的函数运用Excel的图表制作的图表制作 课程开发背景课程开发背景问题一：问题一：个人素质水平分布不均衡，对管理工具的使用个人素质水平分布不均衡，对管理工具的使用程度不一程度不一问题二：问题二：缺乏主动学习和运用管理工具的意识缺乏主动学习和运用管理工具的意识问题三：问题三：有工作热情，却管理水平不高，无法创造高绩有工作热情，却管理水平不高，无法创造高绩效效问题四：问题四：一线管理人员对管理工具的运用不熟练，导致一线管理人员对管理工具的运用不熟练，导致工作繁忙而效率不高工作繁忙而效率不高综述：以上问

2、题的出现，表明员工应加强对管理工具的学综述：以上问题的出现，表明员工应加强对管理工具的学习和运用，常用的管理工具有习和运用，常用的管理工具有Excel，Word等，我们本次等，我们本次课程以讲解课程以讲解Excel为例。为例。ExcelExcel常用函数回顾常用函数回顾我们平时经常使用的我们平时经常使用的ExcelExcel函数有：函数有： SUMSUM：求和函数：求和函数 AVERAGEAVERAGE：平均值函数：平均值函数 COUNTCOUNT：记数函数：记数函数 MAXMAX：求最大值函数：求最大值函数 MINMIN：求最小值函数：求最小值函数 另外，就是平时使用的四则运算另外，就是平时

3、使用的四则运算 但这仅仅是但这仅仅是ExcelExcel功能使用的冰山一角，功能使用的冰山一角，ExcelExcel软件可软件可实现的功能要比这强大很多，只是我们没有去学习和运用实现的功能要比这强大很多，只是我们没有去学习和运用它。它。ExcelExcel实用函数教学实用函数教学函数运用心得函数运用心得 面对一个完全陌生的面对一个完全陌生的ExcelExcel函数时，我们应处于一个平和函数时，我们应处于一个平和的心态。的心态。 面对一个完全陌生的面对一个完全陌生的ExcelExcel函数时，我们应知道在哪可以函数时，我们应知道在哪可以得到帮助，如下图：得到帮助，如下图：先打开一个先打开一个Ex

4、celExcel表格，表格，通过这我们可以查询到通过这我们可以查询到所有函数的信息和说明所有函数的信息和说明ExcelExcel实用函数教学实用函数教学函数运用心得函数运用心得 仔细阅读函数下方的注解，我们可轻而易举的了解每个函仔细阅读函数下方的注解，我们可轻而易举的了解每个函数的用途，如下图：数的用途，如下图： 最后就是通过不断的实践，不断的提高自己的最后就是通过不断的实践，不断的提高自己的ExcelExcel的运的运用能力。用能力。ExcelExcel实用函数教学实用函数教学vlookupvlookup函数（一）函数（一） 函数格式函数格式: :函数由四部分组成函数由四部分组成,(,(第一

5、、建立连接的单元格；第一、建立连接的单元格；第二、数据表范围第二、数据表范围; ;第三、在数据表中需要查找引用的数第三、在数据表中需要查找引用的数据列号据列号; ;第四、顺序判断语句第四、顺序判断语句false,false,若使用中数据按数字若使用中数据按数字大小或按字母排列时可不加大小或按字母排列时可不加false )false ) 举例：举例：VLOOKUPVLOOKUP为查找函数为查找函数, ,本例中须将家庭住址表中的住本例中须将家庭住址表中的住址按姓名引入到班组表中址按姓名引入到班组表中. .如下图：如下图：通过通过vlookupvlookup函数从其他的函数从其他的表格中引入表格中引

6、入“家庭住址家庭住址”ExcelExcel实用函数教学实用函数教学vlookupvlookup函数（二）函数（二）A.A.我们先在同一我们先在同一ExcelExcel文件中建立一个家庭住址表，姓名文件中建立一个家庭住址表，姓名栏按顺序排列，如下图：栏按顺序排列，如下图：ExcelExcel实用函数教学实用函数教学vlookupvlookup函数（三）函数（三）B.B. 函数说明函数说明:VLOOKUP:VLOOKUP为查找函数为查找函数, ,本例中须将家庭本例中须将家庭住址表中的住址按姓名引入到班组表中住址表中的住址按姓名引入到班组表中. .函数格函数格式式: :函数由四部分组成函数由四部分组

7、成,(,(第一、建立连接的单元第一、建立连接的单元格格, ,本例为姓名本例为姓名; ;第二、数据表范围第二、数据表范围, ,本例数据表本例数据表为家庭住址表为家庭住址表A A和和B B列列; ;第三、在数据表中需要查第三、在数据表中需要查找引用的数据列号找引用的数据列号, ,本例须引用第二列家庭住址本例须引用第二列家庭住址, ,故列号为故列号为2;2;第四、顺序判断语句第四、顺序判断语句false,false,若使用若使用中数据按数字大小或按字母排列时可不加中数据按数字大小或按字母排列时可不加false )false )ExcelExcel实用函数教学实用函数教学vlookupvlookup函

8、数（四）函数（四）C.C.在原班组表的在原班组表的“家庭住址家庭住址”栏处，引用栏处，引用vlookupvlookup公式，公式，具体如下图所示：具体如下图所示：= =VLOOKUP(B2,VLOOKUP(B2,家家庭住址庭住址表表!A:B,2,FALSE)!A:B,2,FALSE)D.D.运用范围：当我们要进行较大量的数据查找，但原始的运用范围：当我们要进行较大量的数据查找，但原始的数据资料的排序又异常混乱时，可采用数据资料的排序又异常混乱时，可采用vlookupvlookup函数进函数进行查找。行查找。ExcelExcel实用函数教学实用函数教学sumifsumif函数（一）函数（一） 函

9、数格式函数格式: :函数由三部分组成函数由三部分组成,(,(第一、数据表范围；第二、第一、数据表范围；第二、连接表范围连接表范围; ;第三、取值范围，及要将哪一部分数据求和第三、取值范围，及要将哪一部分数据求和 ) ) 举例：举例： sumifsumif函数为满足条件的求和函数函数为满足条件的求和函数, ,本例中将工号本例中将工号相同的有效产量进行求和相同的有效产量进行求和. .如下图：如下图：将工号相将工号相同的有效同的有效产量求和产量求和ExcelExcel实用函数教学实用函数教学sumifsumif函数（二）函数（二）A.A.我们先在同一我们先在同一ExcelExcel文件中建立一个班组

10、目录表，如下文件中建立一个班组目录表，如下图：图：ExcelExcel实用函数教学实用函数教学sumifsumif函数（三）函数（三）B.B. 函数说明函数说明: : sumifsumif函数为满足条件的求和函数函数为满足条件的求和函数, ,本例中本例中须将须将原数据总表出现相同工号员工的有原数据总表出现相同工号员工的有效产量相加，函数由三部分组成效产量相加，函数由三部分组成,(,(第一、数据第一、数据表范围，即数据总表中工号所在栏；第二、连表范围，即数据总表中工号所在栏；第二、连接表范围，即班组目录表中工号所在栏接表范围，即班组目录表中工号所在栏; ;第三、第三、取值范围，表示要将哪一部分数

11、据求和，本例取值范围，表示要将哪一部分数据求和，本例中取值范围为数据总表中的中取值范围为数据总表中的“有效产量有效产量”栏栏 ) )ExcelExcel实用函数教学实用函数教学sumifsumif函数（四）函数（四）C.C.在原班组目录表的在原班组目录表的“有效产量有效产量”栏处，引用栏处，引用sumifsumif函数，函数，具体如下图所示：具体如下图所示：=SUMIF(=SUMIF(数据总数据总表表!A:A,!A:A,班组目班组目录录!A:A,!A:A,数据总数据总表表!C:C)!C:C)D.D.运用范围：当我们从系统中导出大量的原始数据时，可运用范围：当我们从系统中导出大量的原始数据时，可

12、能出现相同工号多个有效数据，可采用能出现相同工号多个有效数据，可采用vlookupvlookup函数进函数进行求和，得出单一目标的有效数据汇总。一般情况下，行求和，得出单一目标的有效数据汇总。一般情况下，因为导出的数据大多为文本型，多与因为导出的数据大多为文本型，多与valuevalue函数使用。函数使用。ExcelExcel函数运用心得函数运用心得解读顺序（一）解读顺序（一） 当我们遇到复合函数时，我们会被长串的公式给吓倒，从而当我们遇到复合函数时，我们会被长串的公式给吓倒，从而影响我们的使用。如何有效的解读函数，特别是较长的复合函数？影响我们的使用。如何有效的解读函数，特别是较长的复合函数

13、？ 步骤如下：步骤如下：1 1、先确定函数所属的层次先确定函数所属的层次，如：，如：=SUMA5=SUMA5，SUMSUM（A1A1，A3A3）+SUM+SUM（B1B1，B3B3）。根据运算规则，我们知道第一步先操作）。根据运算规则，我们知道第一步先操作SUMSUM（A1A1，A3A3），第二步操作），第二步操作SUMA5SUMA5，SUMSUM（A1A1，A3A3） 和和SUMSUM（B1B1，B3B3），），根据操作的顺序来确定每一层次根据操作的顺序来确定每一层次2 2、在确认层次后，根据操作步骤的顺序，一步步进行操作，如：、在确认层次后，根据操作步骤的顺序，一步步进行操作，如： =SU

14、MA5=SUMA5，SUMSUM（A1A1，A3A3）+SUM+SUM（B1B1，B3B3），先操作），先操作SUMSUM（A1A1，A3A3）ExcelExcel函数运用心得函数运用心得解读顺序（二）解读顺序（二）举例：如下图，这是我们考核信息表中的一部分截图。举例：如下图，这是我们考核信息表中的一部分截图。ExcelExcel函数运用心得函数运用心得解读顺序（三）解读顺序（三）实际上班工时一栏中的公式为：实际上班工时一栏中的公式为：= F2+IF(G2=0,0,G2)-M2-= F2+IF(G2=0,0,G2)-M2-N2-H2-SUMIF(N2-H2-SUMIF(其他假其他假!B:B,!

15、B:B,现场考核汇总表现场考核汇总表!A:A,!A:A,其他其他假假!E:E)+SUMIF(!E:E)+SUMIF(上班工时表明细上班工时表明细!A:A,!A:A,现场考核汇总现场考核汇总表表!A:A,!A:A,上班工时表明细上班工时表明细!G:G)!G:G)，我们只需先划分层次，后，我们只需先划分层次，后按操作的顺序一步步操作即可。按操作的顺序一步步操作即可。Contents目目 录录数据分析数据分析 Excel的函数运用的函数运用Excel的图表制作的图表制作 ExcelExcel的图表制作的图表制作目的目的 ExcelExcel软件除了强大的函数功能外，还有对数据进行显形软件除了强大的函

16、数功能外，还有对数据进行显形化的图表制作功能，如下图：化的图表制作功能，如下图： 图表的类型很多，但主要的目的就是为了将数据更好的通图表的类型很多，但主要的目的就是为了将数据更好的通过图表的方式显形化，使我们更容易对数据进行分析和处过图表的方式显形化，使我们更容易对数据进行分析和处理。理。ExcelExcel的图表制作的图表制作步骤（一）步骤（一） 举例：以柱形图为例，操作步骤如下举例：以柱形图为例，操作步骤如下A A、在、在ExcelExcel表表格上新建立数格上新建立数据表据表B B、选取整个、选取整个数据表后，点数据表后，点击插入图表击插入图表ExcelExcel的图表制作的图表制作步骤

17、（二）步骤（二）C CD D图表类型选择ExcelExcel的图表制作的图表制作步骤（三）步骤（三）E EF F可填写表头、X轴和Y轴的名称ExcelExcel的图表制作的图表制作步骤（四）步骤（四）G GH H 以上几个步骤就是以上几个步骤就是ExcelExcel图表的制作过程，图表的制作过程，这其中还有很多的细微操作都未在课程中这其中还有很多的细微操作都未在课程中讲解，各位可以在平时使用时多加尝试。讲解，各位可以在平时使用时多加尝试。Contents目目 录录数据分析数据分析 Excel的函数运用的函数运用Excel的图表制作的图表制作 v数据的概念v数据分析的概念v呼叫中心数据分析技术v

18、数据分析在呼叫中心运营中的运用数据分析目录数据分析目录 数据不仅仅是指数值型的，也可以数据不仅仅是指数值型的，也可以是非数值型的。比如指标的表现形态既是非数值型的。比如指标的表现形态既可以是数值型的，也可以是非数值型，可以是数值型的，也可以是非数值型，即文字型的。即文字型的。&数值型的数据有数值型的数据有&非数值型的数据有非数值型的数据有数据的定义数据的定义v 按时间状况分类v 依据测量尺度来分类&定类数据&定序数据&定距数据&定比数据v 离散数据和连续数据思考：某天或某一个月的呼入量，我们是怎么分类的呢？ ？数据分类数据分类v 好的报表的要求&

19、amp; 格式上& 内容上& 数据源上v 报表与报告的区别目的用途格式重点内容报告说明事情向阅读者解释事态与计划部分标准化目标与行动计划状态、原因分析、目标、计划报表说明数据向阅读者分辨事态完全标准化获取相关罗列数据值数据、图表数据报表数据报表思考：思考： 梦网短信退订，1000人次； 随意行业务咨询，比上月增长了26%； 客户代表平均每小时接听电话数量，第一名比最后一名多4.3个电话。n数据分析的目的在于发现问题，解释原因和关系，以及寻找可能的解决办法。没有方向，数据分析更是无从下手，没有比较，数据本身毫无意义 数据分析的目的数据分析的目的v设定业务目标v确定KPIvKPI追

20、踪分析v分解KPIv制定行动计划&谁&做什么&需要什么资源&什么时间完成&效果数据分析的流程数据分析的流程 数据预处理 横纵向比较法 均值和离差分析法 趋势分析法 周期分析法 相关分析法 原因和影响分析法 帕雷托分析 六西格玛问题解决模式 SPC过程控制数据分析方法数据分析方法v数据收集v数据甄别v数据审核v数据排序 例如:12580的接入话务量报表和人工子业务话务量之间的关系数据预处理数据预处理横向比较法纵向比较法v案例：选石头案例：选石头v思考：1、拒绝挑石头；2、指出两块石头都是黑色的；3、选一块石头，然后牺牲自己。横纵向比较分析法横纵向比较分析法

21、A1A2A3A4A5A6A7A8A9A10A11A126月4014516151225964484456594532424741955419537753617月5019500849003880482848254817377636103548477644658月4917489150004986374837312954425249553575494849389月340548715104514251285124512229303880510350885071案例分析：案例分析： 可以怎么分析以下的产量数据？可以怎么分析以下的产量数据？ v均值的定义v离差的定义v正态分布及函数n众数：众数：是一组数据当

22、中出现次数最多的变量值。n中位数：中位数：是一组数据按照大小排序后，处于正中间的位置的变量值。n均值：均值：就是全部数据的算术平均值。n方差：方差：是各变量值与其均值的离差平方的平均数。n标准差标准差：方差的平方根就是标准差。n样本的方差：样本的方差：总体的方差是用数据个数或总频数来除离差的平方和，而样本的方差是用样本数据的个数或总频数减去1 去除离差的平方和。几个定义x-dt2suts2122es,ux,F）（）（均值与离差分析法均值与离差分析法v正态分布函数Normdist( x,mean,standard_dev,comulative)v实际频次Frequency(Data_array,

23、bins_array)v均值、中值、标准离差率、标准差、众数均值与离差分析法均值与离差分析法 比如Y=XX的表述方式，描述了数据的均值和离散程度，正态分布是最常见的分布情况，在近似于正态分布情况下：n当X=1S，数据68.3%在XX区间，84.1%小于 XX；n当X=2S，数据95.5%在XX区间，97.7%小于 XX；n当X=3S，数据99.7%在XX区间，99.9%小于 XX。正态分布正态分布案例分析：案例分析： 话务量的正态分布话务量1307171215121798v定义n趋势分析法主要是研究数据随时间变化情况。可分为线性趋势和非线性趋势。v原理n事物的特性沿过去、现在和未来的时间过程延

24、续发展；n影响该事物发展趋势的条件在预测期内是不变的。 趋势分析法趋势分析法v周期性分析法定义周期性分析法定义n通常呼叫中心话务量随着时间而波动，而且每天不同时点、每周不同天数、每月不同天数等波动的周期都有一定的规律性。v分析方法分析方法n先总量n后解剖周期分析法周期分析法月份月份来话量来话量月份百分比月份百分比预测预测2007200420052006117622229721992473193190281314424122529151662222776191236304722027735482142714039201254382102062633171119725432912211271344

25、总计241230233931案例分析：案例分析： 结合趋势分析和周期分析，预测2007年话务量月份月份来话量来话量月份月份百分比百分比预测预测200720042005200611762222970.074234421992473190.081737931902813140.083838942412252910.080837551662222770.071033061912363040.078036272202773540.090942282142714030.094844092012543820.0894415102062633170.0839389111972543290.083338712

26、2112713440.0882409总计2412302339311.00004641y = 759.5x + 1603R2 = 0.987405001000150020002500300035004000450050001234答案答案n相关分析是定量和定性分析的重要统计方法，相关关系（correlation）是指变量之间的不确定的依存关系。相关分析法相关分析法n 问题的特性总是受到一些因素的影响，我们通过脑力激荡找出这些因素，并将它们与特性值一起，按相互关联性整理而成的层次分明、条理清楚，并标出重要因素的图形就叫特性要因图。因其形状如鱼骨，所以又叫鱼骨图（以下称鱼骨图），它是一种透过现象看本

27、质的分析方法。原因与影响分析（鱼骨图的应用）原因与影响分析（鱼骨图的应用）案例：超长话务原因分析案例：超长话务原因分析v定义定义n在分析管理对象在经济或技术上的不同价值，并据此排序和分类，区别重点和一般,以选择最佳管理方式的一种管理方法，又称成本比重分析法，其对不同类的对象投入不同的管理力量，对起决定性影响的主要对象进行重点管理，以提高管理的经济效益。vABC分析思考：如何做一个帕雷托图？网络覆盖边界漫游优惠通话质量漫游互联互通业务使用超长、超短话单其它总计167671448179906951292924992214572662961032帕雷托分析法帕雷托分析法n6 sigma是研究流程变量与流程能力间关系的；n通过连续的测量分析改进控制循环，使流程能力不断提高并最终达到或超过6 sigma水平。六西格玛问题解决模式六西格玛问题解决模式六西格玛问题解决模式六西格玛问题解决模式v 找问题（Select a problem and describe it clearly） v 研究现时生产方法（Study the Present System） v 找出各种原因（Identify Possible causes） v 计划及制定解决方法（Plan and implement a solution） v 检查效果（(Ev