课程背景：

随着工作中遇到的数据分析数据量越来越大，掌握高效的Excel数据分析技术如今对于职场办公人员来说已是迫在眉睫，对于数据分析结果的呈现要求也越来越高，为此我们专门开设了这门课程，帮助职场办公人员快速提升数据处理效率与数据可视化技术操作水平。

课程收益：

● 灵活运用Excel十大数据分析工具的40余种方法分析数据

● 300个工作表近万条数据整合，只需要1分钟

● 10分钟教你学会海量数据查询、数据拆分及数据转换等关键技术

● 分分钟就可以将抽象的数据表制作成高质量的图表

● 掌握数据分析中5大类常用函数的15种用法

● 结合工作实例讲解，以**短的时间掌握**实用的操作技能，10倍提升工作效率

课程时间：2天，6小时/天

课程对象：办公室、行政、财务、人力资源、数据分析岗位负责人及职员，本课程适用于想利用Excel提升办公效率的所有职场人员

课程方式：现场讲授 案例讲解 实操演练

课程所用软件说明：课程中讲到的PowerQuery工具需要使用office2016专业版及以上版本，其它板块使用office2010以上及WPS均可

课程特点：

★ 操作简单：始终坚持用**简单的方法解决遇到的问题。

★ 实用性强：十余年企业授课经验，案例实用性更强。

★ 附赠视频：课后附赠现场录制视频，更便于回顾复习。

★ 幽默风趣：老师幽默风趣，学习氛围轻松愉快、不枯燥。

课程体系：

课程大纲

**讲：职场办公精英必会的Excel十大数据分析工具

一、排序功能的4个应用

1. 按特定顺序排序实现职务及部门等排序

重点：如何添加自定义排序序列

2. 按颜色排序快速处理签到表

3. 按行列排序规范销售报表

4. 利用排序功能制作工资条

案例演示：签到表、销售统计表、工资表

操作要点：排序-（自定义序列、单元格颜色、字体颜色、行列排序）

二、筛选功能的2个应用

1. 文本筛选的应用-快速找出特定行业记录

2. 高级筛选的应用-找出符合多条件记录

案例演示：客户信息表、绩效考核统计表

操作要点：文本筛选→建立条件区域→选择数据区域与条件区域

三、分列功能的5个应用

1. 利用分列解决数据无法求和问题

2. 利用分列解决数据显示为科学计数问题

3. 利用分列解决数据拆分问题

4. 利用分列从身份证中提取出生日期

5. 利用分列以关键字提取指定内容

案例演示： 会计凭证导出记录、人事档案表、客户信息表

操作要点：分列-（分隔符号or固定宽度）-分别按分隔符或固定宽度拆分-确定分列后每列数据类型

四、数据验证功能的5个应用

1. 利用数据验证实现数据关联智能填充

案例演示：档案信息表中选择省份后，地市一列自动显示相应省份所属地市

操作要点：数据验证、名称管理器、indirect函数

2. 利用数据验证实现限制输入数据类型

案例演示：档案信息表中“日期”列限制录入日期型数据

操作要点：数据验证-自定义-公式、Cell函数

3. 利用数据验证实现关联数据限制输入

案例演示：档案信息表“转正日期”列限制录入条件

操作要点：数据验证-自定义-公式

4. 利用数据验证实现限制输入重复数据

案例演示：费用报销时发票号码录入不允许重复

操作要点：数据验证-自定义-公式、countif函数

5. 利用数据验证实现特定日期录入

案例演示：周末值班表中限制录入值班日期为周末

操作要点：数据验证-自定义-公式、weekday函数

五、条件格式功能的4个应用

1. 突出显示重复数据

2. 测试成绩分段显示

3. 销售排名动态突出显示

4. 合同日期及生日到期提醒

案例演示：客户信息表、销售情况表、人事档案表

操作要点：条件格式-突出显示重复值、新建规则、公式、逻辑函数

六、删除重复值的2个应用

1. 利用删除重复项进行数据对比

2. 利用条件格式删除所有重复值

案例演示：客户信息表

操作要点：删除重复值与条件格式的配合使用

七、合并计算功能的应用

1. 利用合并计算合并多张报表

2. 合并计算数据源的引用

案例演示：销售统计表、费用预算执行情况统计表

操作要点：标签位置、创建指向源数据的链接

八、分类汇总功能的应用

1. 利用分类汇总统计销售报表

2. 多次分类汇总的应用

3. 利用定位功能复制汇总后的数据

案例演示：家电产品销售情况表

操作要点：按分类字段排序、多次分类汇总、汇总后结果复制（定位）

九、数据透视表与透视图的应用

1. 数据透视表

1）数据透视表基础操作

2）数据透视表中切片器的应用

3）数据透视表数据源的自动扩展

2. 数据透视图

1）创建数据透视图的方法

2）数据透视图修改与美化

3. 利用透视表与透视图制作数据分析动态看板

案例演示：服装销售记录明细表、各部门成本费用明细表

操作要点：数据透视表选项、报表布局、切片器、图表格式设置

十、模拟分析与规划求解功能的应用

1. 利用模拟分析轻松测算还贷数据

案例演示：购房贷款还款数据测算表

所用工具：模拟分析-单变量求解

操作要点：计算公式（PMT函数）、单变量求解（目标单元格、目标值、可变单元格）

2. 利用模拟分析测算盈亏平衡点的销量数据

案例演示：面馆收入盈亏平衡测算表

所用工具：模拟分析-单变量求解

操作要点：设置公式、单变量求解（目标单元格、目标值、可变单元格）

3. 利用规划求解计算特定数值构成

案例演示：根据给定数值自动匹配数据源

所用工具：分析-规划求解

操作要点：设置公式（sumproduct）、规划求解-设置约束条件

4. 利用规划求解测算利润**大化的产品构成

案例演示：烘培店产品构成利润**大化测算

所用工具：分析-规划求解

操作要点：设置公式、规划求解-设置约束条件

第二讲：你还应该学会这个数据分析神器-Power Query

1. 将300个工作表快速整合只需一分钟

案例演示：300个门店销售数据记录合并

操作要点：新建查询-从工作簿-转换数据-筛选、整理数据-上载结果

2. 多工作簿多工作表数据整合简直不要这么简单

案例演示：各城市家电销售数据记录合并

操作要点：新建查询-从文件夹-合并并转换数据-数据清洗、整理后-上载结果

3. 轻松实现二维表快速转换成数据记录

案例演示：化妆品订购数据表

操作要点：获取数据-从表格-转换-逆透视列

4. 一招教你将数据拆分至多行

案例演示：疫情防控值班明细表

操作要点：获取数据-从表格-转换-拆分列

5. 将一个工作表拆分成多个工作表

案例演示：服装销售记录明细表

操作要点：利用PowerQuery清洗处理数据-数据透视表-报表筛选页

第三讲：常用图表与数据可视化

一、图表基本知识

1. 图表结构及基本元素

讲解要点：图表的基本构成元素

2. 图表分类及应用场景

讲解要点：15种图表分类及常用图表类型的应用场景

3. 图表设计中的注意事项

讲解要点：选择正确的图表类型、图表配色、长宽比、兼容性

二、15个常用图表制作技术

1. 簇状柱形图

案例演示：A公司2015-2021年销售情况表、家电销售统计表

2. 堆积柱形图

案例演示：各部门工资及办公费用统计表

3. 百分比堆积柱形图

案例演示：家电销售情况表

4. 嵌套柱形图

案例演示：各部门任务完成情况表

5. 条形图

案例演示：各部门工资及办公费用统计表

6. 堆积条形图

案例演示：A公司各产品上半年销售情况统计表

7. 折线图

案例演示：MH公司销售情况统计表

8. 柱形折线图

案例演示：安装任务统计表

9. 面积图

案例演示：销售情况统计表

10. 饼图

案例演示：MH公司人员分布情况

11. 多层饼图

案例演示：2020-2021家电销售情况表

12. 复合饼图

案例演示：2019年各部门收入分布情况

13. 复合条饼图

案例演示：2019年各部门收入分布情况

14. 圆环图

案例演示：各小组安装任务进度情况表

15. 雷达图

案例演示：管理技能考核得分情况表

第四讲：数据分析中的常用函数

一、日期函数在日常管理中的3个应用

1. 合同管理中服务到期日的计算

2. 项目管理中完工日期的计算

3. 项目管理中工作日的统计

案例演示：合同管理统计表、项目管理情况表

操作要点：Date、Edate、workday函数的应用

二、查找引用函数在日常管理中的3个应用

1. 利用查找函数提取数据

2. 利用引用函数比对两列数据差异

3. 利用引用函数实现指定销售数据自动提取

案例演示：人事档案表、销售情况统计表

操作要点：vlookup、match、index函数使用及注意事项

三、条件函数与逻辑函数在日常管理中的2个应用

1. 根据工龄自动计算年假天数及工龄工资

2. 根据给定条件自动判断是否达到退休标准

案例演示：人事档案表

操作要点：if、if与and or函数的应用

四、排名函数在日常管理中的3个应用

1. 常规排名与逆序排名

2. 多列数据参与排名

3. 数据分组排名

案例演示：考核成绩统计表

操作要点：rank函数的多种用法、sumproduct函数的应用

五、统计类函数的3个应用

1. 利用条件计数统计数据重复次数

2. 利用多条件计数统计符合条件的职员数量

3. 利用条件求和统计符合指定条件销售数据

案例演示：员工档案信息表、各品牌车辆库存情况统计表

操作要点：countif、countifs、sumifs函数的应用

课程总结 答疑

课程附赠：现场课堂录屏视频、课堂笔记