赵保恒：Excel数据分析可视化高阶训练营

【课程背景】

基于excel应用的广泛性，以及数据对于企业管理的重要性，学好Excel已经不是职场的选修课，而是必修课。无论你今天是职员，还是未来要组建团队，甚至要成为企业的决策者，“数据管理能力”都会持续的影响你，变成你的翅膀或者成为你的掣肘。目前在有很多工作中，简单的Excel技能已经不能满足我们的办公需求，而是需要我们掌握更高阶的Excel实战应用技能。

《Excel数据分析可视化高阶训练营》课程能够帮助数据分析人员进阶Excel实战技术和能力，学习微软为强化自身产品商业智能功能而开发的工具集Excel，掌握高级数据可视化实战技能。我们常说一图胜千言，可视化图表不仅可以清晰、直观地向阅读者传递复杂的数据信息，还可以为阅读者带来视觉上的感官刺激，这种刺激有助于加深阅读者对图表信息的记忆，从而起到过目不忘的效果。

【课程收益】

让Excel实现数据资源到数据价值的转变；不止于Excel技巧，而是探寻Excel数据背后的核心价值。

结合数据分析思路方法，帮助讲着更好的呈现数据背后的秘密，通过数据讲述一个好的商业故事。

通过经典数据分析呈现理念和美学结合并进，深入洞察数据可视化呈现之美。

【课程对象】

本课程适用于公司的市场销售、行政文秘、人力资源、财务会计、仓储物流等与数据接触较多的相关岗位工作人员，需要进一步提升Excel技能和使用效率的所有用户。

【课程时长】

线上训练营，共3天。

【课程大纲】

1. Excel数据规范
2. 软件优化

• 自定义功能区
• 自定义访问工具栏
• 文件自动保存与恢复
• 文件自动备份
• 启用“开发工具”选项卡
• 案例实践：定制专属的Excel界面

1. Excel数据规范（一）

• 表格使用中的那些“坑”
• Excel中的真表格——超级表
  • 超级表的六大优势
  • 区域与表格的转换
  • 二维表转换一维表
• 三大数据规范化工具
  • 快速填充
  • 数据分列
  • 数据验证

1. Excel数据规范（二）

• 用定位解决合并单元格的“后遗症”
• 定义属于你的专有列表
  • 按指定顺序填充
  • 按指定顺序排序
• 案例实战：

三步快速填充上方非空单元格数据

二级联动列表，根据省份选择城市

1. Excel提醒预警
2. Excel条件格式提醒

• 条件格式突出显示
  • 突出显示规则
  • 前后规则
  • 数据条
  • 色阶
  • 图标集
• 用公式规则实现“涨红跌绿”
• 案例实战：合同到期预警

1. Excel数据查询
2. 数据查询计算基础

• 公式追踪器——看清数据来龙去脉
• 文本魔术师TEXT函数
• 日期函数
  • 用EDATE函数计算合同到期日
  • 用DATEDIF函数计算年龄和工龄
• 条件函数IF家族
  • IF函数三段式
  • IF+AND+OR综合判断
  • 不用多层嵌套的IFS
  • 条件最值MAXIFS/MINIFS

1. INDEX+MATCH双剑合璧

• INDEX函数基础
• INDEX多区域引用
• MATCH函数基础
• INDEX+MATCH常规精确查找
• INDEX+MATCH动态引用照片
• INDEX+MATCH多条件查找
• INDEX+MATCH一对多查询
• INDEX+MATCH目标值含有通配符的查找
• INDEX+MATCH返回分数等级

1. 经典查询函数VLOOKUP

• VLOOKUP常规查找
• VLOOKUP文本数字查找
• VLOOKUP通配符查找
• IFERROR+VLOOKUP美化错误值
• VLOOKUP+COLUMN批量查询
• VLOOKUP+MATCH根据字段内容查询
• 用VLOOKUP模糊查询计算销售奖金

1. 向量和数组查询LOOKUP 

• LOOKUP常规查找
• LOOKUP查找最后一个数字
• LOOKUP查找最后一个文本
• LOOKUP混合数据查找
• LOOKUP查询员工最近一次合同签订日期
• LOOKUP函数实现多条件查找

1. INDIRECT与OFFSET函数

• INDIRECT函数基础
• INDIRECT数据区域转置引用
• INDIRECT根据工作表名称跨工作表查询
• OFFSET函数基础
• OFFSET偏移方式
• OFFSET创建动态数据区域

1. 另类查询与案例实战

• 高级筛选常用应用
• 不用函数的查询工具—高级筛选
  • 条件区域及规则
  • 空白字段表达式
• 案例实战

根据身份证号计算距下次生日天数，并提醒

根据人员实现智能变色和汇总

1. Excel数据统计
2. 实战统计函数（一）

• SUM另类用法
  • 累计求和
  • 合并单元格统计
  • 跨工作表求和
• MAX和MIN设置销售提成上下限
• LARGE与SMALL函数
  • 列出前三笔销量
  • 列出前三笔销量对应的日期
• COUNTIF/COUNTIFS条件计数
  • 某单字段同时满足多条件的计数
  • 验证身份证号是否重复
  • 统计非重复数量
  • 中国式排名

1. 实战统计函数（二）

• 最不老实的函数SUMPRODUCT
  • SUMPRODUCT函数基础
  • SUMPRODUCT演讲比赛评分
  • SUMPRODUCT综合销售提成
  • SUMPRODUCT条件统计计算
• 筛选与隐藏状态下的统计与求和
  • SUBTOTAL函数基础
  • SUBTOTAL在筛选状态下的统计
  • SUBTOTAL隐藏行数据统计
  • SUBTOTAL筛选下生成连续序号
    1. Excel数据预测

1. Excel数据预测

• 函数预测法
• 图表预测法
• 工作表预测法
• 实战案例：根据近三年数据预测未来12个月的业绩转换数值

1. Excel数据透视
2. 数据透视表

• 数据透视表刷新
• 数据透视表排序
• 数据透视表切片器
• 数据透视表日期表
• 数据透视表中执行计算
• 数据透视表函数获取数据透视表数据

1. 实战数据透视表分析数据

• 定义名称创建动态的数据透视表
• 使用“超级表”创建动态的数据透视表
• 创建单页字段的数据透视表
• 创建自定义页字段的数据透视表
• 利用Excel数据模型创建多表关联的数据透视表

1. Excel数据分析
2. Excel数据分析工具

• 单变量求解
  • 根据税后计算税前收入
  • 计算要达到指定交易额时的单价
• 模拟运算表
  • 多方案展现
  • 分析汇率变化对外贸交易额的影响
  • 分析美元汇率和交货单价对外贸交易额的影响
  • 使用双变量模拟运算制作九九乘法表
• 规划求解
  • 寻找最优组合方案
  • 投资收益最大化问题
    1. Excel数据呈现

1. Excel 数据可视化图表初探

• 商务图表格式
  • 不同类型图表的展示场合
  • 图表类型更改
  • 图表要素与布局
  • 图表格式快速调用
• 常用三大基础图表
  • 柱状图
  • 折线图
  • 圆饼图
• 柱状图的变化
  • 图片柱状图
  • 目标柱状图
  • 参考线柱状图
  • 双Y轴柱状图
  • 悬浮柱状图
• 新版本新增酷炫图表
  • 树状图
  • 瀑布图
  • 漏斗图
  • 旭日图
  • 直方图
  • 箱形图

1. Excel 数据可视化图表实战

• 散点分布图
  • 毛利与库存分布图
• 柱状展示图
  • 长分类标签图表
• 温度计对比图
  • 柱状温度计对比图
  • 条形温度计对比图
  • 温度计百分图
• 填充式图表
  • 填充式折线图
  • 填充式圆环图
• 另类柱形图
  • 带参考线的柱形图
  • 正负柱形图

1. Excel 动态可视化图表实战

• 切片器动态透视图
• 函数动态图表
• 控件动态图表
  • 动态折线图
  • 动态雷达图