Excel数据分析&高级功能应用培训-曙海培训

 【课程目标】

本课程为工具类培训，面向所有想学数据分析工具的人员。

本课程的主要目的是，帮助学员掌握Excel数据分析工具，学会数据预处理，简化工作量，提升效率，避免重复工作；学会数据可视化，直观呈现各种业务信息和含义。

通过本课程的学习，达到如下目的：

1、 熟悉Power Query数据预处理操作，能够处理各种数据源

2、 掌握透视表，以及高级的统计函数，实现数据汇总和计算

3、 熟悉Excel高级函数的应用

4、 掌握Excel可视化功能，及制图技巧，制作美观的图形

5、 熟悉模拟运算、规划求解等高级功能

6、 理解动态图表原理，掌握动态图表的制作

【授课时间】

2天时间（每天6个小时）可以根据具体的需求选择部分课程内容

【授课对象】

销售部、营业厅、市场营销部、运营分析部、业务支撑部等业务及应用人员。

本课程由浅入深，结合原理主讲软件工具应用，不需要太深的数学知识，但希望掌握数据分析的相关人员。

【授课方式】

数据整理 + 分析数据 + 数据呈现 + 工具实践操作

采用互动式教学，围绕业务问题，展开数据分析过程，全过程演练操作，让学员在分析、分享、讲授、总结、自我实践过程中获得能力提升。

【课程大纲】

第一部分： Excel数据预处理

1、 传统工具最大问题：重复工作

2、 数据预处理的四大任务

Ø 数据集成、数据清洗、样本处理、变量处理

1、 Power Query高级功能

2、 多数据源读取

演练：从文本/Excel/数据库/Web页获取数据源

3、 数据组合/数据集成

Ø 样本追加：横向合并

Ø 字段合并：纵向合并/连接类型

Ø 表连接的类型（左/右/完全外连接、内连接、左反/右反连接）

演练：多表合并，实现在职、离职、新入职员工名单

4、 数据预处理

Ø 文件夹合并

演练：文件夹下多文件单工作表合并

演练：文件夹下多文件多工作表合并

Ø 多工作表合并

演练：单文件多个规范工作表合并

演练：单文件多个不规范工作表合并

3、 异常数据处理

Ø 重复值处理

Ø 缺失值处理

Ø 无效值/错误值

Ø 离群值处理

4、 Power Query数据预处理实战

Ø 文本字段拆分列/拆分行（按分隔符、字符数、非数字到数字等）

Ø 展开列表为行/分组依据

Ø 去重/排序/跳过行/第一行作标题

Ø 新增列/新增索引/新增条件列

Ø 文本提取/文本移除/文本转数字

演练：销售人员绩效自动排名

演练：借贷凭证数据处理

演练：采购计划处理（多表合并处理）

演练：文本数字混合计算金额

5、 排序和筛选

Ø 排序依据、顺序

演练：学历如何排序（自定义顺序）

Ø 筛选：简单筛选、复合筛选、自定义筛选

第二部分： Excel条件格式

1、 条件格式：根据条件来设置单元格的格式

2、 条件规则类型及条件

Ø 根据值条件、值排名设置条件

Ø 根据内容条件

Ø 根据公式自定义条件

3、 设置格式

Ø 字体、填充、边框、对齐等等

Ø 已有样式：数据条、色阶、图标集

演练：单元格自行添加边框

演练：高亮显示选中内容的单元格

演练：自动添加箭头显示同比/环比变化

4、 表格美化技巧

Ø 突出显示

Ø 前后项突出

Ø 数据条

Ø 色阶

Ø 图标集

Ø 迷你图

5、 自定义格式：<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

6、 Text函数改变数据格式

第三部分： Excel高级函数

1、 Excel常用函数种类

2、 文本函数

a) 查找：Find/Search

b) 替换：Substitue/Replace

c) 提取：Left/Right/Mid/TextBefore/TextAfter

d) 合并：Concat/TextJoin/&

e) 分割：TextSplit

演练：身份证号提取籍贯、出生日期、性别

演练：住址省市区县乡镇街道等拆分

3、 逻辑函数

a) 条件判断：IF/IFS

b) 逻辑运算：AND/OR/NOT

c) 函数计算：Let/Lambda/Map/Reduce/ByCol/ByRow

d) 名称管理器

演练：自定义函数及调用

4、 信息函数

Ø ISBLANK/ISERROR/ISNA

Ø ISNUMBER/ISTEXT/ISFORMULA/ISREF

Ø ISODD/ISEVEN/ISLOGICAL

5、 查找和引用函数

a) 值查找：VLookup/HLookup/XLookup

b) 序号查找：Match/XMatch

c) 条件筛选：Filter

d) 提取数据：Take/Drop/ChooseCols/ChooseRows

e) 引用函数：Index/Offset/Indirect

演练：销量数据查找

演练：销量数据筛选

演练：下拉菜单、多级下拉联动菜单

6、 统计函数

a) 计数：Count/CountA/CountBlank/CountIF/CountIFS

b) 均值：Average/AverageIF/AverageIFS

c) 极值：Max/Min/Large/Small

d) 分类统计：GroupBy/PivotBy

演练：销量统计、分类统计

7、 数学和三角函数

a) 汇总：Sum/SubTotal

b) 条件汇总：SumIF/SumIFS/SumProduct

8、 日期和时间函数

a) 时间提取：Year/Month/Day/Hour/Minute/Second/Weekday

b) 时间间隔：YearFrac/Days/Networkdays/Workday

9、 正则表达式

演练：电话号码提取、费用数字提取

10、 分级显示: 组合/取消组合

第四部分： Excel高级可视化

1、 数据呈现的两种格式：图与表

2、 常用图形类型选择原则及画图技巧

3、 复杂图形画法

Ø 面积图、瀑布图、股价图

Ø 漏斗图、雷达图、树状图

演练：图形绘制

4、 高级图表可视化

Ø 立体柱状图

Ø 系统重叠、间隙宽度

Ø 组合图（主坐标轴与次坐标轴）

演练：复杂图形绘制

5、 图形美化原则

6、 动态图表实现方式

a) 筛选器、切片器（单行、复选）

b) 动态钻取

c) 函数实现（OFFSET/INDEX/MATCH/VLOOKUP/CHOOSE/IF）

d) 控件（单行、复选、下拉框等）

e) 定义名称

演练：贷款金额计算及模拟运算

7、 名称管理器

第五部分： Excel模拟运算

1、 单变量求解

2、 模拟运算表

3、 方案管理器

第六部分： Excel规划求解

1、 规划求解工具介绍

a) 求取极值的最优输入

b) 约束条件使用

c) 全局最优及局部最优

2、 如何实现产品最优定价

演练：利润最大化下的产品生产与分配

演戏：成本最小化下的产品组成配比 

第七部分： 自定义模型

1、 回归建模的本质

2、 规划求解工具简介

3、 自定义回归模型

案例：如何对客流量进行建模预测及模型优化

4、 回归季节预测模型模型

Ø 回归季节模型的原理及应用场景

Ø 加法季节模型

Ø 乘法季节模型

Ø 模型解读

案例：美国航空旅客里程的季节性趋势分析

5、 新产品累计销量的S曲线

Ø S曲线模型的应用场景（最大累计销量及销量增长的拐点）

Ø 珀尔曲线

Ø 龚铂兹曲线

案例：如何预测产品的销售增长拐点，以及销量上限

演练：预测IPad产品的销量

6、 回归模型优化措施：寻找最佳回归拟合线

Ø 如何处理预测离群值（剔除离群值）

Ø 如何剔除不显著因素（剔除不显著因素）

Ø 如何进行非线性关系检验（增加非线性自变量）

Ø 如何进行相互作用检验（增加相互作用自变量）

Ø 如何进行多重共线性检验（剔除共线性自变量）

演练：模型优化演示

结束：课程总结与问题答疑。