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　质量成本的构成是怎样的　259
13．3．2　质量成本诸要素之间的逻辑关系　259
13．3．3　从采集的数据中求出平衡点　260
13．4　质量数据分析指标宝典列表　262

第　14章 经营管理数据的分析案例与模型　263
14．1　发现问题：多维度透析低收益原因并进行改善　263
14．1．1　总成零件收益为负，总经理发话要解决　263
14．1．2　从设计、采购与销售3个维度进行分析　263
14．1．3　收益由负变正，总经理表示认可　266
14．2　预测未来：应用数据相关性和回归分析做投资计划　267
14．2．1　某行业盈利与多方因素相关　267
14．2．2　多方因素的相关性分析与回归分析　268
14．2．3　董事会通过投资分析计划　270
14．3　优化决策：利用数据简单对比支撑商务决策　270
14．3．1　大客户要求合并产品并重新定价　270
14．3．2　从双方立场进行价格分析　271
14．3．3　与客户协商新价格，确保双赢　272
14．4　经营数据分析指标宝典列表　272

第　15章 构建分析模型与可视化报告　273
15．1　模型是效率之王　273
【案例1——修改表格数据，计算结果没变化，老板不高兴】　273
【案例2——同样的数据不同的分析模型，结果出现天壤之别】　274
15．2　何时需要建模与可视化建模　278
15．3　4种建模类型如何选择　279
15．4　5C法构建数据分析模型　279

第　16章 利用控件定制高级动态分析模型　281
16．1　构思模型：用什么效果来呈现　281
16．2　数据采集：确定数据并规范格式　282
16．3　公式链接：让数据统计和图形绘制同步进行　283
16．3．1　链接C30单元格与5个单选项控件　283
16．3．2　链接图表数据源与基础数据表　284
16．3．3　链接图表数据源与KPI考核项目名称　285
16．3．4　链接图表数据源与当前累计完成率　286
16．3．5　链接图表数据源与预计累计完成率　286
16．3．6　根据数据源绘制图表　286
16．3．7　调整“累计实际”的格式　287
16．3．8　调整“累计预计”的格式　288
16．3．9　调整“当月目标”和“当月实际”的格式　288
16．3．10　调整“当月完成”率的格式　289
16．3．11　调整图表视觉细节　290
16．4　模型检验：采用实际数据进行检验　293
16．5　安全设置：避免他人误操作或恶意修改数据　293
16．5．1　开放允许修改的单元格区域　294
16．5．2　隐藏重要的单元格区域　294
16．5．3　执行数据表保护　295
16．6　模型拓展建议　296

第　17章 PowerBI与超级数据透视　297
17．1　2分钟了解PowerBI组件　297
17．2　在Excel中加载数据分析插件　297
17．3　快速了解Power Query　302
17．4　快速了解Power Pivot　324
17．5　快速了解Power View　328
17．6　快速了解Power Map　328

第　18章 制作高质量的数据分析报告　330
18．1　口头报告的要点　330
18．2　Excel报告的要点　331
18．1．1　边界条件　331
18．1．2　数据结果　331
18．1．3　分析建议　332
18．3　PPT报告的要点　333
18．3．1　PPT报告的逻辑　333
18．3．2　PPT报告的误区　333
18．4　Word报告的要点　334