Excel数据分析方法、技术与案例

版权信息

书名：Excel数据分析方法、技术与案例

ISBN：978-7-115-57985-0

本书由人民邮电出版社发行数字版。版权所有，侵权必究。

您购买的人民邮电出版社电子书仅供您个人使用，未经授权，不得以任何方式复制和传播本书内容。

我们愿意相信读者具有这样的良知和觉悟，与我们共同保护知识产权。

如果购买者有侵权行为，我们可能对该用户实施包括但不限于关闭该帐号等维权措施，并可能追究法律责任。

版  权

编  著 羊依军 三 虎 

责任编辑 贾鸿飞

人民邮电出版社出版发行　　北京市丰台区成寿寺路11号

邮编　100164 　电子邮件　315@ptpress.com.cn

网址　http://www.ptpress.com.cn

读者服务热线：(010)81055410

反盗版热线：(010)81055315

内 容 提 要

本书主要讲解使用Excel进行数据分析的思路、方法与案例，以帮助读者系统地建立数据分析思维，快速提高数据处理能力。

本书共分为18章，第1章介绍数据分析的核心目的与常见误区；第2章介绍数据分析流程与分析元素；第3～8章分别介绍数据采集、数据规范化、常用函数、数据可视化与图表变形技术、9种常用数据分析方法与实战案例，以及利用工具进行高级数据分析等内容；第9～14章分别介绍商务、财务、HR、生产、质量、经营管理这6个领域常用的数据分析方法；第15～17章分别介绍构建分析模型、利用控件定制分析模型的方法，以及Power BI的使用方法；第18章介绍多种数据分析报告的撰写要点。

本书内容全面，系统性强，案例真实且贴近实际工作场景，非常适合数据分析从业者阅读。

前  言

当今的社会存在海量数据，每天都有无数数据被制造出来。很多企业意识到数据是一座“金矿”，对数据进行分析、研究，往往可以获得巨大的回报，如业绩提升、成本降低、决策更优等。

不少人已经开始学习数据分析，希望为将来的工作打下更好的基础；也有不少本职工作为销售、采购、生产的人逐渐接触数据分析工作，这也是近年来数据分析方面的学习人数增长较快的主要原因。不过，很多人想要学习数据分析却感到无从下手，因为仅仅学会使用Excel，以及制作简单的透视表并不能很好地完成数据分析工作，初学者缺乏的是系统的知识框架、分析思路、分析方法与实战经验，而这些是难以通过自学快速积累的。

本书作者长期从事数据分析工作，也有较为丰富的教学经验，非常了解初学者的技能短板与学习需求。为了让数据分析初学者能够快速入门，让具有一定基础的读者能够得到进一步提高，作者结合自己丰富的数据分析经验撰写了本书。本书首先介绍常用的数据分析知识，然后由浅到深地详细讲解分析设计、数据采集、规范化处理、图表变形技术和高级分析技术，随后介绍在商务、财务等6个领域中进行数据分析的方法，最后讲解数据建模、控件定制和Power BI分析等技能，让读者可以系统、全面地掌握数据分析的方法，并能将编者积累多年的数据分析经验应用到实际工作中，为公司创造更多财富，同时让自我价值得到极大的提升。

为了让读者能够更加高效地学习，本书使用大量的图片演示操作过程，辅以详细的指示图标，方便读者轻松阅读、快速理解。书中还使用大量的案例，从实战角度讲解数据分析的方法，让读者能够将技能与工作紧密结合起来。此外书中还附有很多小栏目，对关键的知识点和操作要点进行提示，这样能有效提升读者的学习效率。

本书内容全面，知识架构完善，含有大量实战经验，非常适合数据分析初学者阅读学习，同时也能帮助有一定工作经验的职场人员提升数据分析的水平。本书还可作为各类院校及培训机构相关专业的参考书。

由于成书仓促，因此书中难免存在错漏之处，还望读者谅解并不吝指正，可将电子邮件发送至jiahongfei@ptpress.com.cn，编者将竭力回复。

编 者

序章　写在前面的话

本书主要讲解使用Excel进行数据分析的理念、方法与技巧，浓缩了笔者多年的工作与教学经验。本书与市面上纯粹讲解函数用法、数据透视表用法的Excel图书有较大的区别，内容具有一定深度，对掌握了Excel用法，想深入学习数据分析的读者有较大的帮助。

为了让读者能够更好地理解本书的内容，特设置本序章，对数据分析方法的架构、读者的需求等进行简单的探讨。

数据分析方法的架构

不同的数据分析方法可以形成一定的架构，本书将这个架构命名为“353693”，便于读者记忆。这串数字实质上是指数据分析的各个环节所包含的方法、类型等的数量。整个数据分析方法的架构如下图所示。

本书内容亮点

本书在内容上有很多亮点，例如数据处理的实战6字秘诀、5C法建模等，希望能给读者茅塞顿开之感。本书亮点如下表所示。

能满足读者的需求

5年内训经验，让笔者深切知道学员的需求。笔者通过对来自上海、北京、广州、武汉、成都、杭州等21个城市共187位学员的调查统计，根据可重复性多项选择的结果，将学员学习数据分析遇到的问题分为8个阶段，具体如下表所示。

本书正是基于这些问题编写的，内容非常符合学习数据分析的读者的需求。

本书适合人群

本书内容层层递进，适合多种水平的读者，包括以下人群。

（1）数据分析新手，包括学生和职场人士。

（2）只会简单的数据处理，期望学到更多数据分析思路的人。

（3）知道一些数据分析理论，但是缺少实战案例来拓展思路的职场精英。

（4）不会使用数据分析工具，或会用工具但是不知道分析原理的人。

（5）会制作基本图表但是不懂得变通的人。

（6）有一定数据分析基础，急需通过可视化模型来提高工作效率的职场人士。

其他想要深入学习数据分析思维、方法与技巧的读者，也可以阅读本书。

鸣谢

感谢三虎老师在写作过程中给予很多指导和建议；感谢编辑团队的辛勤付出。

感谢家人，没有家人的支持就不会有本书的诞生。

感谢读者的信任，感谢你们购买此书！

结语

本书的内容是我对16年数据分析工作及教学经验的总结，虽然没有提出很多“高大上”的概念，却能帮助读者在数据分析工作中分析问题、预测数据、优化决策。此外本书引入了大量案例，相信能对读者有所启发。

学习知识要会举一反三，只有突破才能蜕变。正所谓“师父领进门，修行在个人”，本书既不是学习的起点，更不是学习的终点。愿本书成为读者在学习数据分析路上的一道美丽风景，更希望读者顺利站上胜利的顶峰。

羊依军

第1章 数据分析的核心目的与常见误区

要点导航

1.1 数据分析的三大核心目的

1.2 数据分析的四大误区

在大数据时代，各行各业都需要分析海量的数据，才能更好地进行决策和经营。企业通过分析数据，可以及时发现运营中存在的问题并进行纠正，可以科学地预测行业走向、产品销量等信息，帮助决策者在多种选择之中找到较优甚至最优的方案，降低运营成本，具备更强的竞争力。在学习数据分析之前，先要了解数据分析的三大核心目的与四大误区，如图1-1所示。

图1-1

1.1 数据分析的三大核心目的

数据分析的目的可以有很多，但究其核心，不外乎发现问题、预测未来与优化决策这3类。这3类目的实际上是从多数企业的需求中提炼出来的，具有较好的广泛适用性。

1.1.1 发现问题

企业在运营中常常会出现各种问题。出现问题并不可怕，但出现问题后如果无法找到其根源并加以解决，就会让企业不断“失血”，从而让企业发展走向颓势。在发现问题以后，如何找到问题产生的根源？靠经验或猜测不能保证准确性，多数情况下只有对数据进行分析，才能找到真正的原因。

【案例1—— 超标开支都到哪里去了】

某公司销售部的费用支出连续3个月超标，领导要求分析开支情况，弄清楚钱都用到哪里去了。管理室将销售部1～3月的开支进行了分类统计，并制作了一张报表，如表1-1所示。

表1-1

从表中可以看出：装卸费和广告费偏高，两项加起来占比约58%，因此这两项费用是控制的重点。当然，这只是一个极简单的数据分析案例，用以说明数据分析的应用场景。在真实情况下，数据可能会很多，数据之间的关系可能非常复杂，并不是一眼就能看出问题根源的。下面就来看一个稍微复杂一些的案例。

【案例2—— 哪些产品是改善的重点】

公司领导很苦恼，一年投入的生产材料超过30000吨，材料成本占总成本的65%，费用高达1.5亿元，利润却没有达到预期，但又找不到改善的方向。于是领导找到财务部门的小唐，要求他对数据进行分析，以找到突破口。小唐先将数据列为表格，如表1-2所示。

表1-2

作为专业人员，小唐能从表格中直接看出问题。不过由于还要让领导能看懂表格，因此必须采用更加直观的模式。于是小唐将数据按投入产出比进行降序排列，如表1-3所示。

表1-3

此外，小唐还将表格数据绘制成图表，这样就显得更加直观，如图1-2所示。

图1-2

从图中可以看出投入产出比最低的是P0002产品和P0005产品，那么是不是就应该把主要精力放在改善这两种产品的生产过程中呢？不一定，这是因为有的产品的原材料投入量本来就较少，改进它的收益不是很大，因此，这里还应该考虑产品的原材料投入量，找到原材料投入量较大而投入产出比较低的产品进行改善。于是，小唐对数据进行深挖，按照投入量和投入产出比这两个数据维度进行多条件组合排序分析。将数据按投入量降序排列的结果如表1-4所示。

表1-4

最后，小唐将表格数据绘制成柱形图，效果如图1-3所示。

从图中可以直观地看出P0005产品和P0006产品的原材料投入量较大而投入产出比较低，因此应该把改善的重点放在P0005产品和P0006产品上。

图1-3

通过对以上两个案例的学习，大家可以知道：对数据进行分析，可以找到问题产生的主要原因并给出解决方案。在分析过程中，将数据可视化呈现，可以让阅读与分析数据更加直观方便。

1.1.2 预测未来

企业常常需要对未来进行预测，如预测明年的行业状况、明年的产品销量等。通过经验或者猜想进行预测，其结果的可靠性是不高的。只有对数据进行合理的分析，如相关性分析、回归分析、假设分析等，找出其中的规律，才能较为准确地对未来进行推测与判断。当然，预测的结果不是必然的，而是一种趋势下的大概率事件。

【案例3—— 明年的销售情况如何预测】

某公司今年的销售数据如表1-5所示。公司领导要求运营部门预测明年的销售情况，并要求预测结果必须有理有据，不能是“空中楼阁”。

表1-5

有人用本年平均值6133进行预测，公司领导不满意，认为没有前瞻性。

有人用上一年的数据进行预测，公司领导也不满意，认为没有基于本年度的数据，是一种敷衍行为。

有人则采用回归分析方法进行预测，结果如图1-4所示。

预测结果如下。

✧ 对数预测法：悲观。

✧ 线性预测法：乐观。

✧ 多项式预测法：中性。

相信如果给领导展示这个结果图，并选择中性的（多项式预测）结果，领导起码不会认为分析结果没有根据。

图1-4

【案例4—— 哪个价格更合适】

生产厂家经常需要向客户提供产品的报价。显而易见，报价并不是越高越好，恰到好处的报价可以兼顾双方的利益，有利于建立长期稳定的合作关系。现在一家公司要向采购方提供电机报价，需要从电机的功率和价格的关系，找出价格与功率的规律，从而预测采购方的目标价格，并根据预测价格给出一个合适的报价。

公司的电机型号及价格如表1-6所示，可以看到其中有一些不是很合理的地方，例如功率不同的电机，其价格却一样，这些数据可使用红色标注。

表1-6

现这家公司要对功率为1400kW的电机进行报价，价格定为多少算相对合理？由于已知数据中并没有功率为1400kW的电机的价格，因此需要进行预测。这里分别采用线性预测法、对数预测法和多项式预测法进行预测，结果如图1-5所示。

预测结果如下。

✧ 线性预测法：15194元。

✧ 对数预测法：13393元。

✧ 多项式预测法：12935元。

图1-5

从预测结果可以看出，最低的目标价格应该在12935元左右，因此报价应定在13300～14000元，这样被砍价的概率比较低，或者说砍价空间不大。这就是一个典型的通过数据分析进行合理预测的案例。

1.1.3 优化决策

在企业的运营过程中，经常会遇到需要从多种方案中进行选择的情况。决策者通过对比数据，可以选择较优方案进行经营。

【案例5—— 哪个价位适合建立库存】

在制造行业，原材料成本在制造成本中的占比非常高，因此企业对原材料的价格比较敏感。影响原材料价格的因素有很多，如国际铁矿石、原油等价格的变化，导致企业无法对原材料的价格进行有效掌控。因此，对企业来说，最好的办法是原材料在低价位时买入，合理增加库存，尽量降低市场行情变化对企业的负面影响。

某公司需要对牌号为“40MnBH”的原材料的价格进行预测，以便在低价位时购入。运营者对该材料历年来每月的价格走势进行了可视化分析，最低价格、最高价格、平均价格（不是最低和最高价格的简单算术平均，而是所有价格的加权算术平均）一目了然，如图1-6所示。

图1-6

根据图1-6，公司建立了一套规则：当原材料价格低于平均价格时，作为监控区；当原材料价格在平均价格与最低价格之间时，选择一个点设立库存储备预警线；当原材料价格接近预警线时，公司就要开会决定是否大批量购进原材料。规则设立以后运行良好，已经为公司节省了3000多万元。

【案例6—— 哪个候选人更适合】

市场部需要从公司内部招聘一名业务经理。经过笔试和面试层层筛选，最后留下3名候选人。领导感觉这3个人都比较优秀，不知道怎么决策。于是领导让人力资源部拿出一个科学有效的评估方案。人力资源部经理从5个维度分析，并赋予分值，将3名候选人的能力数据化，以便领导进行决策。3名候选人的能力评价如图1-7所示。

图1-7

从图中可以直观地看到，小唐5个维度的能力都比较好，最后领导选择了小唐作为业务经理。事实证明，小唐确实很优秀，不仅能够很好地胜任业务经理的工作，还在两年后就被提升为主管。

1.2 数据分析的四大误区

数据分析也存在一些误区。例如，分析时想面面俱到，结果却顾此失彼；或者企图用极少的数据预测很遥远的未来；有时候又因数据量太大而不知道分析的方向；还有的人因为不会做图表，上交的数据表格让领导一头雾水……这些误区总结起来可分为4类。

1.2.1 什么都要分析——贪多

分析人员面对庞杂的数据，需要分析的内容和维度往往较多。但如果总想分析出所有的结果，到最后没有重点，提交的表格就会让人看不明白。

【案例7—— 面面俱到的分析结果，一定招人喜欢吗】

某企业领导将一个销售与成本的数据表格交给财务人员小李，如表1-7所示，然后说了一句“分析一下数据”，也没有告诉小李具体需要什么结果。

表1-7

领导看完分析结果后，对小李说：“我要的是发现问题或有价值的信息，而不是拿这么多结果来让我甄别，我没那么多时间！你要分析数据、发现问题、给出解决方案，而不是写一个看起来面面俱到的报告！”

小李这才意识到，原来自己需要对数据做一些有方向性、目的性的分析，而不是贪多，把所有的分析结果都列出来。小李对数据重新进行了分析，同时对数据进行了归类、合并，通过占比、对比、结构等分析，得出分析结果并提出了整改方案。这一次，领导就能一眼看清楚分析结果，小李的分析工作也才过了关。

1.2.2 用几个数据想分析出“一朵花”—— 做梦

众所周知，在一定范围内，分析时使用的数据量越大，得到的结果越趋于准确与稳定。如果分析时使用的数据量过小，会出现什么问题呢？一是分析的结果可能不会很准确，二是没有办法对将来的情况进行预测。

【案例8—— 想用3个月数据预测下半年的销售情况】

有一次一个学员讲了这么一件事儿：领导交给他3个月的销量数据，想让他预测下半年的销量和利润情况，如表1-8所示。

表1-8

从表中可以看到，数据太少，判断销售是否有季节性、区域性等都没有一定量的数据做支撑。无论是谁，想通过简单的几个数据分析出比数据本身更多的信息，都是不可能的。最后学员说，其实他当时也告诉了领导，数据太少，做出来的预测很不可靠，并不能帮助公司做决策。

1.2.3 数据量大不知道该分析啥——抓瞎

有时候如果分析的数据量过于庞杂，难免存在不规范的数据甚至错误数据，让分析人员感到无从下手。而领导布置任务时，通常又只丢下一句话：“你给分析分析。”面对这样的情况，分析人员通常会感到非常烦恼。其实领导这么说也是有原因的，比如他可能觉得自己在统计分析方面是外行，不便讲太多，否则会干扰专业人员的思维。

【案例9—— 一句话的任务】

成都的一个学员小张说，一次领导传给他一张表格，丢下一句“你把这些数据分析一下”就去出差了，这些数据如表1-9所示。

表1-9

小张一看这张表格就觉得头大，因为里面有很多“问题数据”，例如，有的合同编号多了两位，有的数据中间多了一个空格，有的单元格缺少数据，有的单元格中的数据是错误的……

数据的问题具体表现在两个方面：数据不规范、数据不标准。那么在分析时，不妨按照以下两个步骤进行处理。

（1）数据预处理：先对数据进行整理，使之规范化，然后进行分类，再进行统计，最后做分析。

（2）选对分析方法：分析的方法有很多，常用的有对比、同比、环比、基比、排序、占比等（后续会详细讲解），针对不同的数据、不同的要求，要选择合适的分析方法。

1.2.4 不会可视化数据——晕菜

很多时候，看分析结果的人并不是专业人士，如果把分析结果做成表格，他们在阅读时可能无法快速、深入地理解分析结果，更谈不上及时做出正确的决策。因此分析人员在写分析报告时，一定要考虑到阅读者是否能直观地看懂分析结果，具体的操作就是将表格图形化，并辅以合适的文字进行说明。画图时还要一定程度地考虑美观问题，尽量做到让图表直观、美观，这样才能够打动阅读者，体现数据分析工作的价值。

【案例10——怎样发现哪些产品是明星产品】

某公司需要通过数据分析统计出A01～A08产品的销售量、销售价和利润率，从而弄清楚哪些产品可以减小销售力度，哪些产品要加大销售力度，以便调整经营策略。A01～A08产品的销售情况如表1-10所示。

表1-10

领导看了表格无法得出结论，于是要求数据分析人员对此表格进行分析。数据分析人员研究了表格以后，认为可以采用销售量、销售价、利润率这3个维度的数据做一个气泡图，横坐标为销售量，纵坐标为销售价，气泡大小表示利润率高低。完成后的气泡图如图1-8所示。

图1-8

销量越大、价格越高且利润丰厚的产品就是明星产品。从图中可知，A03、A07、A08产品应该加大销售力度，尤其是A03和A08。如果产品的销售价不是很高，销售量也不是很大，但是它的利润率还不错的话，可以想办法提高销售量，例如A04产品。对于A01产品，销售量不错，但是销售价不高，可以挖掘卖点，迭代升级，提高价格。如果产品的销售价不高、销售量少、利润率也不高，像A05和A06产品，可以考虑减少产量或将其淘汰。其他分析，不再介绍。总之，可以通过可视化分析提高分析和决策效率。

【案例11—— 哪些价格是不合理的】

供应商要对几款产品涨价。采购部部长要求采购人员分析哪些价格是合理的，哪些是不合理的，并制定整体调整方案，把亏损严重的产品适当涨价，把利润高的产品价格适当下调，让整体价格合理化，不能随便应供应商的要求涨价，那样并不符合公司利益。采购人员拿到的产品重量和价格数据如表1-11所示。

表1-11

直接看表格肯定什么都发现不了，当把表格中的数据制成图以后就变得直观了，如图1-9所示。

图1-9

从图中可以直观地看到产品价格分为3个区域：高益区、合理区和重灾区。从采购的角度来讲，处于重灾区的几个产品，其价格应该适当地往上调；处于高益区的一些产品，其价格还有很大的下降空间。

当繁杂的表格数据转化成图以后，其可读性得到了很大的提高，就连外行人都可以直观地发现其中的问题，这就是数据可视化的好处。

1.2.5 机智地回答领导的“灵魂三问”

从前述四大误区的案例可以看出，很多时候领导并不会对数据分析人员提出特别具体的分析目标，因此，数据分析人员经常会被领导问到3个模糊的问题，即“大概什么情况”“有什么问题”“你的建议呢”，这几个问题被大家称为“灵魂三问”。

其实解答“灵魂三问”是有技巧的，大家只要正确解读领导的意图，然后做出正确的应对方案，就可以完美地回答“灵魂三问”，如表1-12所示。

表1-12