《EXCEL自动化数据处理》

EXCEL自动化数据处理课程


课程背景：
在办公室里的您每天要接触到大量的数据，包括各下级部门分支机构递交的报表；E
RP系统提供的明细数据和统计数据；互联网上的行业数据、股市行情等，如何利用这些
信息做出更好的决策?本课程着眼于数据的分析和统计，教授如何挖掘数据背后的规律和
隐含的信息。通过学习本课程您将可以掌握从数据收集到数据分析的整套高级技能，提
升科学管理的水平，使您在竞争中高人一筹，游刃有余。
本课程由微软OFFICE金牌讲师精心策划，专为希望深入学习数据收集、数据计算、数
据管理、数据分析等相关功能的用户而设计，从全新的角度展现了Excel在不同领域的高
效应用方法，是理论与实际工作相结合的典范。

培训目标：
本课程从数据收集、数据整理、数据计算、数据管理、数据分析和数据呈现六个角度
阐述企业中如何应用Excel进行数据管理和分析。从最容易被人们忽视的设计表格开始讲
起，从设计阶段就保证正确地使用Excel来管理数据。通过深入讲解函数、数据管理工具
、图表、数据透视表等，使学员不仅掌握Excel相关功能，还进一步理解为什么使用这些
功能，当在工作中遇到问题时，能举一反三。

培训对象：
经常需要从系统中导出数据，生成各式报表的人员；
经常需要从同事工作簿中汇总数据，生成各式报表的人员；
经常要为EXCEL报表而加班的人员；
希望EXCEL自动更新数据，自动生成报表的人员；
希望掌握EXCEL更多技巧，提高工作效率的人员；

课程时间：3小时/课　　共6次课
学员基础：经常使用EXCEL制作各类报表；熟练掌握EXCEL基本操作；拥有基本函数计算
及初步数据管理能力


课程内容
第一讲：设计表格和数据收集的策略与技巧
一、EXCEL在数据管理中的角色定位
1、记录
2、计算
3、汇总
4、分析
二、EXCEL使用水平的三个阶段
1、规范化
2、自动化
3、系统化
三、为不同的目标创建表格
1、如何设计为收集用户数据的表单
2、如何设计为整理分析数据的清单
3、如何设计为呈现给他人阅读的报表
四、理解EXCEL的数据类型与数据格式
1、为什么有些数据看起来是数字其实是文本
2、为什么我改了数字格式仍然不能计算
3、如何快速发现数字中夹杂的少量文本
4、如何快速将文本转换为数字进行计算
五、EXCEL编辑技巧
1、如何快速设置多个工作表的页面设置
2、如何快速生成格式相同的多个工作表
3、如何快速在海量数据进行定位操作
4、手工录入数据的技巧
六、数据比对的快速方法
1、如何对比表格中任意两行数据的差异
2、如何对比两个工作表数据的差异
3、如何对比两个工作簿数据的差异

第二讲：公式与函数的使用技巧
一、以应用为导向的函数知识体系构建
二、使用合适的单元格地址进行计算
1、单元格地址中神奇的$
2、我可以直接写“=单价*数量”的公式吗？
3、利用命名地址更高效地实现公式计算
三、对数据进行日期相关计算函数
1、求系统当前日期：Today()、Now()
2、对日期进行分割：Year()、Month()、Day()、Hour()
3、对日期进行拼接：Date()
四、对数据进行文本相关计算函数
1、对文本进行截取的Left()、Right()、Mid()函数
2、数值转文本：Text()
3、对文本进行测量：Len() 、Find()
五、统计常用函数
1、对数据进行截取：Int()、Round()、Roundup()、Rounddown()
2、数据数据进行统计：Countblank()、Large()、Small()

第三讲：函数高级应用及案例
一、条件函数
1、逻辑函数And() 、Or() 、Not()
2、IF()标准用法和多层嵌套
3、SUMIF()、COUNTIF()
4、利用IFERROR()灵活处理出错信息
案例：利用多条件求和函数实现动态汇总数据
二、查找和引用函数
1、VLOOKUP()在不同场景中的应用实例
2、定位函数：OFFSET()、Index( )
案例1：如何查找并比对被同事修改过的数据
案例2：如何实现快速按关键字查询数据
三、如何让您明明白白错在哪里？
1、为什么输入公式后没有计算？
2、为什么公式会出错，如何纠正？
3、跟踪数据的来龙去脉
4、审核数据报表
5、显示公式和公式求值

第四讲：EXCEL数据管理和数据分析工具
一、将各处的数据汇总到EXCEL
1、导入文本文件数据
2、导入英特网上的数据
3、导入数据库的数据
4、汇总不同EXCEL工作簿的数据
二、排出完美的顺序
1、将相同颜色的数据排到一起
2、按笔划多少对数据进行排序
3、按自定义的顺序对数据排序
三、筛选数据
1、快速跟踪最近（本周、本月等）日期的数据
2、多条件交叉筛选数据
四、快速对数据进行分类汇总
1、如何实现多条件的数据汇总
2、如何只复制可见的汇总数据到新表
五、数据管理效率工具
1、利用分列快速将数据拆分
2、利用合并计算实现季度汇总/年度汇总
3、快速搞定重复值
六、使用条件格式让数据自动报警
1、将大于/小于某个值的数据自动标记颜色
2、动态找出表中最大/最小的数据点
3、将条形图做到单元格中展示数据大小
4、用红绿灯表示数据的不同状态
七、利用假设分析工具求解
1、利用单变量求解自动推测变量
2、利用规划求解解决多条件、多变量问题

第五讲：利用图表和数据透视表进行数据分析
一、选择图表的步骤
1、决定你的信息
2、确定相对关系
3、选择图表形式
二、EXCEL图表关系与类型
1、成分相对关系　
2、项目相对关系　
3、时间序列相对关系（条形图）　
4、时间序列相对关系（折线图）　
5、时间序列相对关系（组合图表）　
6、频率分布相对关系　
7、相关性相对关系　
三、高效图表操作技巧
1、向图表追加数据系列
2、向专业图表要“模板”
3、将图表放在单元格中的“迷你图”
四、作图数据的组织技巧
1、原始数据不等于作图数据
2、作图数据的预处理
3、将数据错行和空行组织
五、高级图表制作
1、瀑布图表
2、不等宽柱形图
3、滑珠图
4、动态图表
5、利用控件实现高级图表制作
六、透视表为你打开高效报表制作之门
1、职场白领报表制作要求：快、准、狠
2、用数据标准化思维来规范数据源，开启方便之门
3、用三秒时间创建一张数据分析报表
4、掌握透视表基本心法：拖拖拉拉
七、给数据源插上翅膀—数据源自动扩展，报表自动更新
1、理解数据透视表的刷新机制
2、用定义名称的办法，让数据源自动扩展
3、用“表“的方法，请数据自动扩展
4、打开文件时，自动刷新报表
八、让数据飞一会儿
1、求和、平均、最大、最小样样行
2、对比、环比、百分比通通行
3、排序、筛选、搜索都拿手
九、让报表数据更精准
1、准备高层领导所需的“趋势报表”
2、准备中层领导所需的“汇总报表”
3、准备基层领导所需的“明细报表”
十、打扮数据透视表，让领导更喜欢
1、套用透视表样式快速化妆
2、自定义样式打造个性之美
3、应用条件格式，给领导“颜色”看看
十一、从新的角度查看数据
1、按年、季度、月、周重新出报表
2、按固定间距组合新报表（案例：应收款帐龄分析）
3、按自定义分类进行组合新报表
4、按不同的字段分类查看数据
十二、数据透视表进阶
1、数据透视表的“变形大法”
2、数据透视表的“隐藏大法”
3、数据透视表的“组合大法”

第六讲：数据呈现与数据安全
一、利用EXCEL制作专业的报表系统
1、让你的报表看起来像一个专业的报告
2、体会领导辛苦，将最关键数据快速呈现
3、提高用户体验，轻松控制报表的规范性
二、分享数据，保护权限
1、指定用户在工作表中哪些区域可以编辑/不可编辑
2、控制用户只可以填写规范数据
3、利用EXCEL识别不同用户，并拥有不同权限
三、协同办公，数据共享
1、将EXCEL表格复制到WORD中，并保持计算能力
2、将EXCEL表格复制到WORD中，并保持同步更新
3、将EXCEL表格复制到PPT中，并保持同步更新
1、将EXCEL图表复制到PPT中，并保持同步更新
2、将EXCEL报表轻松转换为PDF版本
四、保护数据安全
1、为机密文档设置不可破解的打开密码
2、删除个人信息，防止泄密
3、将数据设置为最终版本，防止误删改
4、将报表设置为只有指定人员才能编辑指定区域

刘凌峰老师的其它课程