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

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

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

本书内容全，系统性强，贴近一线，案例真实，非常适合各类数据分析从业者阅读

世界500强企业内训师，Excel Home平台讲师，数据分析师，拥有16年Excel应用与分析经验，5年企业内训师经验，精通Excel函数、图表、建模、透视表和分析系统构建。 他的Excel技术没有华丽的技巧，以实战为主。他推崇的理念是：多用图少用表，让文字等于0；通过数据分析看本质，模型是效率之王。

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

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

1.1.1　发现问题　1

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

【案例2——哪些材料是控制和改善的重点】　2

1.1.2　预测未来　4

【案例3——明年销售预测如何做】　4

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

1.1.3　优化决策　5

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

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

1.2　数据分析的四大误区　6

1.2.1　什么都要分析——贪多　7

【案例7——多多益善的分析结果，领导一定喜欢吗】　7

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

【案例8——想用3个月数据预测全年情况】　7

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

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

1.2.4　不会可视化图表——晕菜　8

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

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

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

第　2章 数据分析流程与分析元素　12

2.1　数据分析流程的五大步骤　12

2.1.1　分析设计：先思考，再动手　12

2.2.2　数据采集：尽量确保原始数据的规范性　12

2.2.3　数据整理：规范化并消除错误　13

2.2.4　数据分析：4个小步骤　13

2.2.5　数据报告：围绕说服力进行撰写　13

2.2　“一句话的任务”案例　14

【案例——一句话的任务，我该怎么办】　14

2.3　分析的5个元素：量价额差率及其组合　16

2.3.1　1分钟了解五大元素　16

2.3.2　单元素分析　17

【案例1——产品1～8月销售量分析】　17

【案例2——产品全年销售额】　17

【案例3——贸易增长率分析】　18

2.3.3　双元素分析　18

【案例4——对产品销量进行“量量”分析】　18

【案例5——网店对当周销量进行“量价”分析】　19

【案例6——机械厂对产品进行“量率”分析】　19

2.3.4　三元素分析　20

【案例7——某公司为考察销售目标完成度进行“量量率”分析】　20

2.3.5　五大元素与其他元素的组合分析　21

2.3.6　确定分析维度的3种原则　21

第3章　数据采集：快速获取有价值的数据　23

3.1　数据采集必须考虑的三者关系　23

3.1.1　诉求者：着重沟通　24

【案例1——沟通不畅导致数据分析返工】　24

3.1.2　分析者：专注分析　24

3.1.3　采集者：满足需求　25

【案例2——我给你一个模板，简单明确效率高】　25

3.2　数据采集获取的3个主要来源　26

3.3　某公司盈利报告在数据采集方面的案例　26

【案例3——多部门数据采集与合并】　26

第4章　数据规范化处理方法与技巧　29

4.1　详解数据规范化处理的目的　29

4.2　快速审核数据的四大方法　29

4.2.1　筛：通过数据筛选，发现数据问题　30

【案例1——筛选出不正常的类别】　30

4.2.2　查：通过索引、查找、定位，发现数据问题　31

【案例2——用VLOOKUP索引查出异常品号】　31

【案例3——用定位（Ctrl G）查出异常价格】　32

4.2.3　排：通过对数据进行排序，判断数据是否存在异常　33

【案例4——通过排序检查产品的重量是否存在问题】　33

4.2.4　验：对数据进行比对或验证，找出数据的异常　34

【案例5——体重数据录入错误，数值结果出现异常】　34

【案例6——用COUNTIF函数发现重复的数据】　34

4.3　数据规范化处理6字秘诀　35

4.3.1　6字秘诀的特点详解　35

4.3.2　快速记住6字秘诀：右手联想法　36

4.3.3　删：删掉问题数据　37

【案例7——筛选出问题数据后进行删除】　37

【案例8——找出重复数据后进行删除】　37

4.3.4　补：补全不完善、缺失的数据　39

【案例9——补上漏掉的打卡数据】　39

4.3.5　替：对不规范、不标准、不统一的数据进行合理替换　40

【案例10——Ctrl H高效替换相同的数据】　40

【案例11——批处理替换无效数据为保底奖励】　41

【案例12——批处理运算高效调整大量数值】　42

【案例13——用REPLACE函数智能替换数据】　43

4.3.6　转：转换数据格式，让数据标准化　44

【案例14——为数据设置统一的格式】　44

4.3.7　拆：拆分数据，剥离出有用的部分　46

【案例15——拆分宽度固定的数据】　47

【案例16——拆分分隔符号固定的数据】　48

【案例17——通过合并操作消除数据隐患】　50

4.3.8　分：将数据分类，让分析更加方便　51

【案例18——利用IF函数为数据分类】　51

4.4　人员信息数据综合处理的案例　52

【案例19——处理表格中数据不准确、不标准、缺失等问题】　52

第5章　数据分析函数和透视技术　60

5.1　的数据分类函数　60

5.1.1　if函数的六大经典用法示例　60

5.1.2　iferror函数的经典用法　62

5.1.3　isna函数的经典用法　63

5.2　的求和与计数函数　63

5.2.1　sumif单条件求和的15种经典用法　63

5.2.2　countif单条件计数的15种经典用法　66

5.2.3　sumifs多条件求和的13种经典用法　69

5.2.4　countifs多条件计数的13种经典用法　71

5.3　的查找与定位函数　72

5.3.1　vlookup函数的7种经典用法　73

5.3.2　hlookup函数的基本用法　77

5.3.3　index函数的基本用法　77

5.3.4　match函数的基本用法　78

5.3.5　offset函数的基本用法　79

5.4　的数据透视表与切片器用法　80

5.4.1　创建数据透视表　80

5.4.2　利用透视表做数据分析　82

5.4.3　利用切片器制作分析模型　84

5.4.4　利用切片器制作可视化分析模型　86

第6章　专业数据可视化与图表变形技术　91

6.1　数据可视化的五大尴尬事　91

6.2　数据可视化图表变形实例讲解　92

6.2.1　一张图让你看懂图表元素　92

6.2.2　数据间的关系：相关性和相对性　94

6.2.3　柱形图与变形记：对比数据分析图表　95

6.2.4　二维柱形图与变形记：对比数据分析图表　100

6.2.5　堆积柱形图与变形记：多元素数据分析图表　106

【案例1——用瀑布图分析数据变化过程】　113

6.2.6　柱线复合图与变形记：多元素比较分析图表　115

【案例2——用帕累托图分析产品质量原因】　120

6.2.7　条形图与变形记：排序法分析图表　122

6.2.8　堆积条形图与变形记：结构对比分析图表　132

6.2.9　折线图与变形记：趋势分析图表　137

6.2.10　饼图与变形记：占比法分析图表　141

6.2.11　散点图和气泡图：相关性分析图表　146

6.2.12　动态数据可视化分析模型原理　153

6.2.13　怎样根据数据选择图表　161

第7章　9种常用数据分析方法与实战案例　165

7.1　分析数据两大原则　165

7.1.1　先总后分，层层拆解　165

7.1.2　由主要到次要　167

7.2　分析数据的9种常用方法　167

7.2.1　比较法：数据打包与切块　168

【案例1——某电子公司产品利润结构分析】　177

7.2.2　排序法：数据排序与比较　178

7.2.3　结构法：数据拆包与分类　181

【案例2——分区域各季度销售情况分析】　182

7.2.4　阶梯法：数据上楼和下楼　184

【案例3——财务利润预实分析】　185

7.2.5　漏斗法：数据收敛与发散　185

【案例4——某网店订单数据分析】　187

7.2.6　关联法：数据因果与关联　188

【案例5——价格与重量关系分析】　189

7.2.7　雷达法：数据无关与协同　190

【案例6——如何选择供应商】　191

7.2.8　象限法：数据定位与象限　192

【案例7——销售量价与利润率分析】　193

7.2.9　趋势法：数据趋势与预测　194

【案例8——预测未来3年销量】　195

7.3　根据数据选择分析方法　196

7.4　两道思考题　196

第8章　利用工具进行高级数据分析　198

8.1　加载并打开分析工具　198

8.2　规划求解　199

8.2.1　单变量求解　199

【案例1——帽子销量必须达多少才能确保目标利润】　201

8.2.2　多变量求解　202

【案例2——规划求解计算保本点】　203

8.3　利用规划求解求出解　205

【案例3——求出利润的产量组合】　205

8.4　回归分析　206

8.4.1　一元回归　207

【案例4——降雨量与树苗生长高度关系分析】　207

8.4.2　多元回归　208

【案例5——某地区人口增长率与总收入、消费、人均GDP关系分析】　211

8.5　相关系数　213

【案例6——各科目考试成绩相关性分析】　214

8.6　直方图　215

【案例7——统计指定区域内销量出现的次数】　215

8.7　指数平滑　217

【案例8——使用不同阻尼系数预测产品销量】　217

8.8　移动平均　219

【案例9——患儿体温检测结果分析】　219

第9章　商务数据的分析案例与模型　222

9.1　发现问题：从备件销售价中发现经销商的“猫腻”　222

9.1.1　上任第 1天发现价格问题　222

9.1.2　通过排序发现两家可疑的经销商　222

9.1.3　为公司挽回130万元损失　223

9.2　预测未来：数据回归推导销售趋势预测模型　223

9.2.1　销售部门做不好预测来求助　223

9.2.2　根据一元回归的拟合度选择预测公式　224

9.2.3　销售部门顺利过关　225

9.3　优化决策：利用回归与相关性分析市占率与客户满意度的关系　225

9.3.1　市占率呈下滑趋势却没有任何应对之策　225

9.3.2　用回归分析市占率与客户满意度的关系　226

9.3.3　用相关系数分析如何从客户满意度着手提升市占率　228

9.3.4　半年后市占率得到了显著提高　228

9.4　商务数据分析指标宝典列表　229

第　10章 财务数据的分析案例与模型　230

10.1　发现问题：巧用保本点管控企业收益率　230

10.1.1　销售额在提升，但利润率在下滑　230

10.1.2　原来是只顾销售不顾结构　231

10.1.3　制定一个合理的保本点确保公司盈利　231

10.2　预测未来：利用回归分析模型预测投资风险　232

10.2.1　朋友想接下一个1700万元的工厂　232

10.2.2　用回归分析模型预测10年销售额　232

10.2.3　根据销售额预测未来10年的利润额　233

10.2.4　结论乐观，决定投资　234

10.3　优化决策：通过项目净现值优化投资　234

10.3.1　3个投资项目，老总拿不准　234

10.3.2　通过净现值比较，结果一目了然　235

10.3.3　结论清晰可靠，老板表示满意　235

10.4　财务数据分析指标宝典列表　235

第　11章 HR数据的分析案例与模型　237

11.1　发现问题：找出高薪还要离职的真正原因　237

11.1.1　质量部离职率出现异常　237

11.1.2　新老两头翘，中间有断层　237

11.1.3　调查原因后开除“老油条”　238

11.2　预测未来：通过回归分析预测个人能力　239

11.2.1　射击队教练遇上三选一难题　239

11.2.2　用回归分析预测运动员发挥水平　239

11.2.3　科学的选择带来的效果　240

11.3　优化决策：通过相关性和回归分析设计薪酬调整方案　240

11.3.1　因员工离职率高而欲改革薪酬体系　240

11.3.2　分析数据薪酬与能力匹配的问题　240

11.3.3　解决方案：工资总额不变，依据能力调薪　242

11.3.4　谨慎实施，效果显著　243

11.4　HR数据分析指标宝典列表　243

11.4.1　HR管理中常用的分析方向及分析建议　243

11.4.2　常用的HR分析公式　243

第　12章 生产数据的分析案例与模型　246

12.1　发现问题：应用数据实时分析监控生产进度　246

12.1.1　交付满足率总是出现问题　246

12.1.2　工人不愿承担换件损失　246

12.1.3　任务分解，层层统计，公开竞赛　247

12.2　预测未来：应用回归分析模型计算安全库存　249

12.2.1　领导要求减少库存缓解资金压力　249

12.2.2　回归分析模型精确估算库存　249

12.2.3　验证半年，领导终于放心　251

12.3　优化决策：利用数据分析化物流成本　252

12.3.1　3车7点的状况　252

12.3.2　各点里程情况分析　252

12.3.3　3种不同的派送方法　253

12.3.4　终的结果　254

12.4　生产数据分析指标宝典列表　255

第　13章 质量数据的分析案例与模型　256

13.1　发现问题：火速降低产品的投诉率　256

13.1.1　产品连续遭到客户投诉　256

13.1.2　分析数据抓住主要问题　256

13.1.3　投诉率得到有效抑制　257

13.2　预测未来：应用回归分析预估质量索赔率　257

13.2.1　产品连续遭到客户投诉　258

13.2.2　回归分析预测8年投诉率　258

13.2.3　按10%的投诉率核算索赔成本　258

13.3　优化决策：利用回归和规划求解计算质量成本　259

13.3.1