Excel数据分析与图表制作

数据分析与图表展现Excel2010高级应用技巧培训甘肃万维程涛2011-7图表分析中国电信甘肃万维2011-7学习大纲统计分析基础获得分析需求的方法了解并掌握基本的分析模型了解Excel数据分析工作流程及关键操作技能如何提高数据收集质量数据的规格化处理使用数据透视表进行统计分析学会图表制作方法打好基础突破Excel传统局限，提高图表表现力了解并学会制作专业图表2图表分析中国电信甘肃万维2011-7需具备的基本知识从事过数据采集、整理和分析工作。用Excel至少成功的做出过1种统计图。了解Excel公式的基本书写规则。用过至少2-3个函数，并且能够理解这些函数。基本熟悉Excel2010版本的界面。3图表分析中国电信甘肃万维2011-7数据分析的目标和分析模型数据分析前的准备工作4Excel图表分析中国电信甘肃万维2011-7确定分析需求要分析什么？希望得到什么结果？分析结果是用来干什么的？5提出问题获取答案解决问题分析是为了发现问题和解决问题，而不是为了Excel功能展现！Excel图表分析中国电信甘肃万维2011-7需求的来源分析是为管理工作服务的——数据说话。分析需求首先来自管理者工作中的关注焦点验证工作成效发现变化业务数据的进一步挖掘和提炼也是数据分析的主要动力。发现变化规律异常捕获挖掘数据间的潜在关系6Excel图表分析中国电信甘肃万维2011-7获取需求的方法数据分析相关人员管理者数据收集和分析人员数据产生和提供人员如何获取需求管理者提出要求数据分析人员与管理者交流数据分析人员尝试各种统计分析模型，发现值得关注的异常现象。7Excel图表分析中国电信甘肃万维2011-7选择恰当的分析模型常用的分析方法——统计图表比较、联系、构成、分布动态、静态常用的数据汇总统计方法——数据透视表汇总、计数、平均、最大最小分类、切片、挖掘、旋转不常用的专业分析模型——加载项线性回归、傅里叶分析、移动平均、直方图分析、规划求解。专业模型侧重预测，结果具有不确定性，并且涉及较多的数理统计知识。对算法理解不透彻，或者应用场景不匹配，其结果容易引起误导。8除非特别必要，并且已经与管理者达成一致，强烈建议不要把这些专业分析模型引入日常工作报告中。图表分析中国电信甘肃万维2011-7数据分析过程及关键操作技能开始进行数据分析9图表分析中国电信甘肃万维2011-7Excel数据分析基本工作流程10收集原始数据规格化统计分析展示结果管理工作验证、指引Excel图表分析中国电信甘肃万维2011-7原始数据的关键特征原始数据是数据分析的基础。原始数据来源于日常工作，是日常工作执行过程中留下来的痕迹——工作记录。原始数据必须是随着工作的执行同步采集，很难事后补充。原始数据的记录质量很大程度上决定了数据分析工作的可操作性，以及结果的可用性。客观性完整性一致性二义性11Excel图表分析中国电信甘肃万维2011-7数据采集过程中存在的普遍性问题采集不足或过度采集；文字描述过多而数据不足，无法做统计分析，只能当做工作记录用——是文档不是数据表。总想记录尽可能多的信息，已备不时之需——无人过问的数据没有价值，只能增加工作量。数据结构经常变动，缺乏持续性；数据结构的设计过于关注管理者的需求，忽视了作业规范。缺乏数据填写标准的定义，或虽然定义了但标准难以把握；满意度定义：很不满意、不满意、比较满意、满意、非常满意，比较满意和满意如何区分？原始记录填写随意，描述方式或格式不一致；甘肃电信、甘肃省电信、中国电信甘肃分公司、省公司，几种叫法实际上指的都是同一个公司，但Excel会把它当成四个不同的公司，张三、张三Excel也会当做两个人。3月10日、10/3、3.10、3月份，都是日期吗？Excel可不会这么想。合同金额：78、10360，计量单位是万元还是元？必填项缺乏强制性，存在空白；对于空白，人为的理解可以是：没有、同上、不知道、忘添了，Excel的理解是：什么也不是（Null）。某些数据可以很轻易的后补，缺乏客观性。上周三我工作了几个小时？上周四晚上9点机房温度是多少？12Excel图表分析中国电信甘肃万维2011-7如何提高原始记录的质量做好基础工作定义数据结构。定义分类标准和解释口径。培训、宣贯，使以上成果成为员工工作标准。使用高效高质的采集手段业务系统信息化。手工记录模板化。数据稽核常态化保持恰当的检查频率，并且首先查数据，培养数据说话的习惯——领导不关注的数据一定是不会认真填报的。主观数据主抓填报及时性和逻辑合理性，但对准确性不要太较真，尽量不要用于考核，如工作量申报。13Excel图表分析中国电信甘肃万维2011-7数据结构定义——需要哪些数据对管理需求进行分解算法的需要——依据采用什么分析模型，以及这些模型需要什么输入。潜在的管理需求——问题发现、原因分析、验证效果、绩效管理。对工作流程/方法进行分析了解可获得的信息。从数据生命周期的角度评估数据采集需求——避免无用数据。从哪来—干什么用—放多长时间—什么时候可以销毁充分考虑数据的可获得性以及采集成本。数据结构一旦定下来，尽量不要有太大变动。14一手的原始数据在成本允许的情况下尽可能多的采集；可通过计算获得数据尽量不要在采集阶段采集。尽量避免重复采集。Excel图表分析中国电信甘肃万维2011-7数据结构定义示例15服务工作的客户满意度？在同一时间段内，客户满意的数量/服务总量。从服务人员处获取服务记录和客户信息，通过电话回访了解每次服务的满意度，并进行记录。管理需求统计方法作业程序•服务时间•客户满意度•回访时间•回访人•回访结果•工单号•受理时间•服务时间•客户名称•服务内容•执行人•服务时长•响应速度•是否是在休息日提供的服务？•服务完成后多长时间做的回访？回访记录服务记录计算结果受理时间服务时间客户服务内容执行人服务用时回访时间回访人回访结果一手数据暂时用不到的数据可以满足潜在需求，尽量保留：•哪些客户的满意度最低？•哪些业务满意度不高？•响应速度是否达到要求？•回访是否及时？有没有回访过晚而导致反馈失效？•哪些员工的服务满意度总是很高，有什么好的经验？•客户通常在什么时间提出申告？•……Excel图表分析中国电信甘肃万维2011-7数据标准定义明确必填项和选填项；每一项数据尽量起一个容易理解的名称，容易引起误解的名称是填写错误的主要根源。关键信息分开填写，不要合并。“专业”的工单号：FW201103001，FW_YJ_0001。需要做分类统计的数据必须定义清晰易掌握的分类标准；名称要保持一致，最好定义一张标准的名称表，例如地名表、客户清单。数量要定义计量单位，并杜绝全角输入。日期要定义统一的书写格式，杜绝用“.”、“，”做日期分隔符，杜绝模糊日期（如3月份，3月底）。特征描述类属性的分类不易过多（3-5个）。不要过于追求数字化，文字描述同样能够达到统计要求。应基于同一标准、同一层级定义分类，16Excel图表分析中国电信甘肃万维2011-7分类定义特征描述类属性的分类不易过多（通常3-5个就够了）过多的分类不易把握边界，填写困难；过多的分类容易稀释数据，导致无法聚焦关键所在。分类边界务必清晰，符合多数人的思维习惯。不要过于追求数字化，文字描述同样能够达到统计要求。例如：满意度评价，打分容易还是定性描述容易？应基于同一标准、同一层级定义分类典型案例：原因分类。同一事物站在不同角度可有多个分类维度，一定要从管理的视角进行分类。可进行多层分类，但不宜过多。层次越深的分类对分析能力要求越高。17Excel图表分析中国电信甘肃万维2011-7数据记录表单模板的设计模板尽量简单，尽量避免多项内容组合到一张工作表中。关键地方和容易引起误解的地方添加必要的注释。注释最好写在填写位置的旁边，并用醒目的颜色。在单独的一页工作表中书写完整详细的填表指南，虽然有必要也很专业，但千万别指望用它来指导使用者，因为没人看。如果用Excel的“注释”功能添加的注释，最好让它始终显示出来，但这样会使整体页面很难看。采用从上到下的原则统一数据标准。公司要求的报表—部门级汇总表—班组级汇总表—个人工作汇总表—工作单/工作日志。能用下拉选单的地方尽量不用手填。适度使用公式——公式总是容易被破坏。使用公式的地方要显著标注出来，防止被删除。不要用跨工作表的公式。不管采用什么办法，新增行并不能很好的继承上一行的公式。尽量不要包含链接。模板中包含分配标准和名称标准列表，并且不允许私自更改。给日期单元格加校验。模板发布的同时最好同时提供样例——再详细的填写指南也比不上样例的作用。18Excel图表分析中国电信甘肃万维2011-7数据的规格化处理为什么要做规格化处理原始数据总会存在缺陷；Excel在做统计分析时对数据格式有严格要求；根据分析模型的要求补充必要的数据。通常需要做哪些规格化处理查找明显填写不正确的数据并进行纠正；与历史数据、关联数据的合并；为方便统计分析添加计算列。19Excel图表分析中国电信甘肃万维2011-7统计分析利器——数据透视表20地区销售人员产品分类金额合同日期合同编号兰州林丹系统集成￥132,000.002005-7-1610248平凉苏术平系统集成￥559,020.002005-7-1010249定西潘金技术服务￥465,780.002005-7-1210250兰州王志东设备采购￥196,218.002005-7-1510251兰州潘金软件开发￥1,079,370.002005-7-1110252点击“插入/数据透视表”Excel图表分析中国电信甘肃万维2011-7统计分析利器——数据透视表21地区销售人员产品分类金额合同日期合同编号兰州林丹系统集成￥132,000.002005-7-1610248平凉苏术平系统集成￥559,020.002005-7-1010249定西潘金技术服务￥465,780.002005-7-1210250兰州王志东设备采购￥196,218.002005-7-1510251兰州潘金软件开发￥1,079,370.002005-7-1110252过去几年各地区的销售额是多少？如何获得按年、月进行汇总的结果•把“合同日期”拖放到行标签区域；•选中合同日期列中的任意单元格，右键单击，在快捷菜单中选择“创建组”；•选择选择步长为“年”；•由于年比较少，可以把行标签拖到列标签，使布局更合理。Excel可以针对日期和数字按照一定的间隔（步长）创建分组，这对日期敏感型的数据分析很有帮助。数字的分组则可以进行分级汇总统计，试一试：按100万为一个等级，统计各地区2006年各等级合同的个数。Excel图表分析中国电信甘肃万维2011-7统计分析利器——数据透视表22大单在哪里？按100为一个等级，对合同金额分级，然后统计各等级合同的个数。对金额创建组可以将系统自动生成的列名更改为任意自己喜欢的名字，只要不和已有字段重名。右键点击排序，强调统计结果。Excel图表分析中国电信甘肃万维2011-7统计分析利器——数据透视表23谁是2007年的销售明星？统计2007年每个销售的签约量、签约总额、贡献占比，并排序。右键选择“值显示方式”可以在汇总结果基础上再做一次计算。Excel图表分析中国电信甘肃万维2011-7统计分析利器——数据透视表综合练习2007年各月的签约额是多少，与去年同期相比，增幅是多少？应用到的技巧：日期的分组筛选行、列布局值显示方式的应用修改列名拓展训练：5月份同比出现负增长，什么原因？——挖掘、钻取24签约总额同比增幅月份2006年2007年2006年2007年1月19,527,13524,992,27427.99%2月9,432,74134,544,634266.22%3月14,254,29