Excel高级运用和数据分析

Excel高级运用和数据分析——蒋晓君Contents目录数据分析Excel的函数运用Excel的图表制作课程开发背景员工问题一：个人素质水平分布不均衡，对管理工具的使用程度不一问题二：缺乏主动学习和运用管理工具的意识问题三：有工作热情，却管理水平不高，无法创造高绩效问题四：一线管理人员对管理工具的运用不熟练，导致工作繁忙而效率不高综述：以上问题的出现，表明员工应加强对管理工具的学习和运用，常用的管理工具有Excel，Word等，我们本次课程以讲解Excel为例。Excel常用函数回顾我们平时经常使用的Excel函数有：•SUM：求和函数•AVERAGE：平均值函数•COUNT：记数函数•MAX：求最大值函数•MIN：求最小值函数•另外，就是平时使用的四则运算但这仅仅是Excel功能使用的冰山一角，Excel软件可实现的功能要比这强大很多，只是我们没有去学习和运用它。Excel实用函数教学——函数运用心得•面对一个完全陌生的Excel函数时，我们应处于一个平和的心态。•面对一个完全陌生的Excel函数时，我们应知道在哪可以得到帮助，如下图：先打开一个Excel表格，通过这我们可以查询到所有函数的信息和说明Excel实用函数教学——函数运用心得•仔细阅读函数下方的注解，我们可轻而易举的了解每个函数的用途，如下图：•最后就是通过不断的实践，不断的提高自己的Excel的运用能力。Excel实用函数教学——vlookup函数（一）•函数格式:函数由四部分组成,(第一、建立连接的单元格；第二、数据表范围;第三、在数据表中需要查找引用的数据列号;第四、顺序判断语句false,若使用中数据按数字大小或按字母排列时可不加false)•举例：VLOOKUP为查找函数,本例中须将家庭住址表中的住址按姓名引入到班组表中.如下图：通过vlookup函数从其他的表格中引入“家庭住址”Excel实用函数教学——vlookup函数（二）A.我们先在同一Excel文件中建立一个家庭住址表，姓名栏按顺序排列，如下图：Excel实用函数教学——vlookup函数（三）B.函数说明:VLOOKUP为查找函数,本例中须将家庭住址表中的住址按姓名引入到班组表中.函数格式:函数由四部分组成,(第一、建立连接的单元格,本例为姓名;第二、数据表范围,本例数据表为家庭住址表A和B列;第三、在数据表中需要查找引用的数据列号,本例须引用第二列家庭住址,故列号为2;第四、顺序判断语句false,若使用中数据按数字大小或按字母排列时可不加false)Excel实用函数教学——vlookup函数（四）C.在原班组表的“家庭住址”栏处，引用vlookup公式，具体如下图所示：=VLOOKUP(B2,家庭住址表!A:B,2,FALSE)D.运用范围：当我们要进行较大量的数据查找，但原始的数据资料的排序又异常混乱时，可采用vlookup函数进行查找。Excel实用函数教学——sumif函数（一）•函数格式:函数由三部分组成,(第一、数据表范围；第二、连接表范围;第三、取值范围，及要将哪一部分数据求和)•举例：sumif函数为满足条件的求和函数,本例中将工号相同的有效产量进行求和.如下图：将工号相同的有效产量求和Excel实用函数教学——sumif函数（二）A.我们先在同一Excel文件中建立一个班组目录表，如下图：Excel实用函数教学——sumif函数（三）B.函数说明:sumif函数为满足条件的求和函数,本例中须将原数据总表出现相同工号员工的有效产量相加，函数由三部分组成,(第一、数据表范围，即数据总表中工号所在栏；第二、连接表范围，即班组目录表中工号所在栏;第三、取值范围，表示要将哪一部分数据求和，本例中取值范围为数据总表中的“有效产量”栏)Excel实用函数教学——sumif函数（四）C.在原班组目录表的“有效产量”栏处，引用sumif函数，具体如下图所示：=SUMIF(数据总表!A:A,班组目录!A:A,数据总表!C:C)D.运用范围：当我们从系统中导出大量的原始数据时，可能出现相同工号多个有效数据，可采用vlookup函数进行求和，得出单一目标的有效数据汇总。一般情况下，因为导出的数据大多为文本型，多与value函数使用。Excel函数运用心得——解读顺序（一）当我们遇到复合函数时，我们会被长串的公式给吓倒，从而影响我们的使用。如何有效的解读函数，特别是较长的复合函数？步骤如下：1、先确定函数所属的层次，如：=SUM[A5，SUM（A1，A3）]+SUM（B1，B3）。根据运算规则，我们知道第一步先操作SUM（A1，A3），第二步操作SUM[A5，SUM（A1，A3）]和SUM（B1，B3），根据操作的顺序来确定每一层次．2、在确认层次后，根据操作步骤的顺序，一步步进行操作，如：=SUM[A5，SUM（A1，A3）]+SUM（B1，B3），先操作SUM（A1，A3）．Excel函数运用心得——解读顺序（二）举例：如下图，这是我们考核信息表中的一部分截图。Excel函数运用心得——解读顺序（三）实际上班工时一栏中的公式为：=F2+IF(G2=0,0,G2)-M2-N2-H2-SUMIF(其他假!B:B,现场考核汇总表!A:A,其他假!E:E)+SUMIF(上班工时表明细!A:A,现场考核汇总表!A:A,上班工时表明细!G:G)，我们只需先划分层次，后按操作的顺序一步步操作即可。Contents目录数据分析Excel的函数运用Excel的图表制作Excel的图表制作——目的•Excel软件除了强大的函数功能外，还有对数据进行显形化的图表制作功能，如下图：•图表的类型很多，但主要的目的就是为了将数据更好的通过图表的方式显形化，使我们更容易对数据进行分析和处理。Excel的图表制作——步骤（一）•举例：以柱形图为例，操作步骤如下A、在Excel表格上新建立数据表B、选取整个数据表后，点击插入图表Excel的图表制作——步骤（二）CD图表类型选择Excel的图表制作——步骤（三）EF可填写表头、X轴和Y轴的名称Excel的图表制作——步骤（四）GH•以上几个步骤就是Excel图表的制作过程，这其中还有很多的细微操作都未在课程中讲解，各位可以在平时使用时多加尝试。Contents目录数据分析Excel的函数运用Excel的图表制作数据的概念数据分析的概念呼叫中心数据分析技术数据分析在呼叫中心运营中的运用数据分析目录数据不仅仅是指数值型的，也可以是非数值型的。比如指标的表现形态既可以是数值型的，也可以是非数值型，即文字型的。数值型的数据有……非数值型的数据有……数据的定义按时间状况分类依据测量尺度来分类定类数据定序数据定距数据定比数据离散数据和连续数据思考：某天或某一个月的呼入量，我们是怎么分类的呢？？？？数据分类好的报表的要求格式上内容上数据源上报表与报告的区别目的用途格式重点内容报告说明事情向阅读者解释事态与计划部分标准化目标与行动计划状态、原因分析、目标、计划报表说明数据向阅读者分辨事态完全标准化获取相关罗列数据值数据、图表数据报表思考：梦网短信退订，1000人次；随意行业务咨询，比上月增长了26%；客户代表平均每小时接听电话数量，第一名比最后一名多4.3个电话。数据分析的目的在于发现问题，解释原因和关系，以及寻找可能的解决办法。没有方向，数据分析更是无从下手，没有比较，数据本身毫无意义数据分析的目的设定业务目标确定KPIKPI追踪分析分解KPI制定行动计划谁做什么需要什么资源什么时间完成效果数据分析的流程•数据预处理•横纵向比较法•均值和离差分析法•趋势分析法•周期分析法•相关分析法•原因和影响分析法•帕雷托分析•六西格玛问题解决模式•SPC过程控制数据分析方法数据收集数据甄别数据审核数据排序例如:12580的接入话务量报表和人工子业务话务量之间的关系数据预处理横向比较法纵向比较法案例：选石头思考：1、拒绝挑石头；2、指出两块石头都是黑色的；3、选一块石头，然后牺牲自己。横纵向比较分析法A1A2A3A4A5A6A7A8A9A10A11A12A13A14A156月4014516151225964484456594532424741955419537753614102470741057月5019500849003880482848254817377636103548477644654437408843768月4917489150004986374837312954425249553575494849383302493432169月340548715104514251285124512229303880510350885071372437195048案例分析：可以怎么分析以下的产量数据？均值的定义离差的定义正态分布及函数众数：是一组数据当中出现次数最多的变量值。中位数：是一组数据按照大小排序后，处于正中间的位置的变量值。均值：就是全部数据的算术平均值。方差：是各变量值与其均值的离差平方的平均数。标准差：方差的平方根就是标准差。样本的方差：总体的方差是用数据个数或总频数来除离差的平方和，而样本的方差是用样本数据的个数或总频数减去1去除离差的平方和。几个定义x-dt2suts2122es,ux,F）（π）（均值与离差分析法正态分布函数Normdist(x,mean,standard_dev,comulative)实际频次Frequency(Data_array,bins_array)均值、中值、标准离差率、标准差、众数均值与离差分析法比如Y=X±X’的表述方式，描述了数据的均值和离散程度，正态分布是最常见的分布情况，在近似于正态分布情况下：当X’=1S，数据68.3%在X±X’区间，84.1%小于X±X’；当X’=2S，数据95.5%在X±X’区间，97.7%小于X±X’；当X’=3S，数据99.7%在X±X’区间，99.9%小于X±X’。正态分布案例分析：话务量的正态分布话务量1307171215121798定义趋势分析法主要是研究数据随时间变化情况。可分为线性趋势和非线性趋势。原理事物的特性沿过去、现在和未来的时间过程延续发展；影响该事物发展趋势的条件在预测期内是不变的。趋势分析法周期性分析法定义通常呼叫中心话务量随着时间而波动，而且每天不同时点、每周不同天数、每月不同天数等波动的周期都有一定的规律性。分析方法先总量后解剖周期分析法月份来话量月份百分比预测2007200420052006117622229721992473193190281314424122529151662222776191236304722027735482142714039201254382102062633171119725432912211271344总计241230233931案例分析：结合趋势分析和周期分析，预测2007年话务量月份来话量月份百分比预测200720042005200611762222970.074234421992473190.081737931902813140.083838942412252910.080837551662222770.071033061912363040.078036272202773540.090942282142714030.094844092012543820.0894415102062633170.0839389111972543290.0833387122112713440.0882409总计2412302339311.00004641y=759.5x+1603R2=0.987405001000150020002500300035004000450050001234答案不相关负线性相关正线