Excel在职场中的高效应用专题培训

模块1 Excel数据表格创建及常用高效数据处理技巧

目标：本模块主要是让学员掌握数据高效录入、规范导入外部数据、制作专业的模板型报表以及安全的分享报表数据，同时结合功能讲解一系列的快捷键，提高日常电子表格使用的效率。

1.1快速输入有规律的序列数据

填充数据序列

填充日期序列

自定义文本序列

结合序列自定义排序表格

结合函数使用自动编号

各类编号输入总结

1.2单元格格式个性设置

运用自定义数字定制日期格式

给数字或字符添加前辍与后辍

通过自定义设置小数点对齐

通过自定义设置万位数据显示

设置单元格的边框

设置斜线表头

1.3数据的高效编辑技巧

使用Ctrl+Enter批量添加、修改数据

使用Ctrl+Enter无格式复制数据、高效复制公式

对单元格的合并区域进行处理

高效率合并相同内容的单元格区域

使用选择性粘贴转置表格行与列

使用选择性粘贴进行数据的运算

使用函数进行动态表格行列列的转置

运用特殊复制方法进行超级链接的取消

1.4数据导入的规范

不规范日期数据如何转换成规范

不规范的数字如何实现计算

超过15位数字的导入与添加

包含特殊字符的字符串处理方法

数值数据如何转换为文本，以便于数据的对比

灵活运用分列功能实现表格的规范呈现

1.5灵活运用数据有效性

使用有效性规范控制数据输入，让别人按照您的要求填写数据

限定数据长度

显示提示信息

采用下拉列表进行数据选择

使用圈释无效数据功能快速查找非法的数据

对比两个表格数据的异同

1.6保护工作的安全

防止无关人员随意更改表格的结构

设置单元格为非锁定状态

设置某些敏感单元格凭密码编辑

保护工作表不允许修改

给文件加密

解除文件的密码

共享工作簿的方法

模块2根据单元格数据内容有条件设置单元格格式

目标：本模块通过条件格式的功能提升数据报表的洞察力，让报表更直观、更专业。

2.1条件格式概述

2.2使用条件格式按单元格数值大小改变单元格格式

2.3使用公式做为条件设置复杂的单元格格式

2.4条件格式的拓展应用

2.5灵活的运用数据条创建迷你图表

2.6灵活的运用色阶标记热点数据

2.7灵活的运用图标集更直观呈现数据的变化与趋势等

模块3规范使用公式和应用函数进行数据的高效处理

目标：本模块主要讲解公式高效复制、保证数据计算的精度、区分单元格地址引用方式、利用工作组高效同步操作表格以及跨表格、跨文件的数据引用计算；同时结合实际案例讲解逻辑、文本、数据检索配对、统计应用以及日期计算方面的函数。

3.1规范公式的应用

相对引用

绝对引用

数组的使用

规范公式的应用

如何设置单元格数据的四舍五入

如何保证运算中四舍五入的准确性（避免计算误差）

快速复制公式的常用技巧

3.2常用函数应用例举

常用的基本函数：

Sum、Average、Count、Max、Min、Product、Today

逻辑运算类函数：

IF、And和Or

统计函数：

Count、Counta、Countblank、Countif、Countifs、Sum、Sumif、Sumifs、Averageif、Averageifs

数据检索函数：

Vlookup、Hlookup、Lookup

排名次函数：

Rank、Subtotal、large、Small

日期间隔计算函数：

Datedif、Networkdays、Workday

模块4数据的分析与管理

目标：本模块主要讲解如何定制化排序报表、自动筛选与高级筛选的应用以及数据的分类汇总统计等。

4.1数据排序的技巧

按主要关键字排序

按多个关键字排序

更改排序选项，可按笔画排序、按行排序

结合自定义列表进行自定义排序

对单独某列数据进行排序

有合并区域时如何排序

防止排序时破坏表格的有效方法

4.2自动筛选与高级筛选

自动筛选与高级筛选功能概述

使用快捷键打开自动筛选

如何按单一条件进行筛选

如何按多重条件进行筛选

高级筛选的基本使用方法

如何区分高级筛选中的并与或的条件

如何将筛选的结果复制到其他工作表中

如何有针对性的选择筛选结果包含的列数据

如何运用高级筛选获取不重复记录

如何结合宏来将高级筛选设置为自动搜索模式

4.3分类汇总与合并计算

创建分类汇总的基本条件

按单一条件进行分类汇总

按多级条件进行分类汇总

复制分类汇总结果如何避免隐藏数据

多张明细表如何快速合并计算生成汇总表

模块5让数据会说话——专业数据图表创建

目标：本模块主要讲解专业数据图表创建的基本理念、图表创建的基本方法、图表创建就避免的细节、各类常见图表制作例举以及常用动态图表创建的基本方法等。

5.1突破常规的作图方法

专业图表的特点分析

有效传递信息，促进沟通

塑造可信度

传递专业、敬业、值得依赖的职业形象

专业图表至少具有以下特点：

专业的外观

简洁的类型

明确的观点

完美的细节

5.2突破Excel的默认颜色

5.3突破Excel的图表布局

Excel默认布局的不足主要有：

标题不够突出，信息量不足

绘图区占据了过大的面积

绘图区的四周浪费了很多地方，空间利用率不高

图例在绘图区右侧，阅读视线往返跳跃，需要长距离检索、翻译

商业图表的布局特点

完整的图表要素

突出的标题区

竖向的构图方式

5.4商业图表的字体选择

5.5突破Excel的作图元素

使用“图表+所有Excel元素”这种方法基本只使用图表对象来制作图表的核心部分，其他部分多使用单元格、文本框、自选图形等其他非图表元素来制作。

5.6成为图表高手的技术准备

作图数据准备

以整理后的数据为源做柱形默认图表

使用各类设置对话框进行格式化

设置数据系列格式

利用其他表格元素完成图表

将图表锚定到单元格

组合图表制作习练

Excel图表缺陷及补救

在Excel中制作条形图时，默认生成图表的条形顺序总是与数据源顺序相反。可以在“设置纵坐标轴格式”中勾选“逆序类别”和选择横坐标交叉“最大分类”

让曲线图从Y轴开始

作图数据的组织技巧

原始数据不等于作图数据

在使用条形图、柱形图、饼图进行分类对比时，除分类名称有特殊顺序要求的情况外，专业人士一般都会先将数据进行降序或升序的排列，做出的图表将呈现排序的效果，便于阅读与比较

5.7高级图表制作——制作动态图表

动态图表也称交互式图表，即图表的内容可以随用户的选择而变化，是图表分析的较高级形式

制作动态图表最常用，也是最直观、易于理解的做法是辅助系列法

5.8选择恰当的图表类型

曲线图：用来反映随时间变化的趋势

柱形图：用来反映分类项目之间的比较，也可以用来反映时间趋势；

条形图：用来反映分类项目之间的比较；

饼图：用来反映构成，即部分占总体的比例；

散点图：用来反映相关性或分布关系；

地图：用来反映区域之间的分类比较。

5.9图表类型选择辨析

柱形图和条形图如何选择？

柱形图和曲线图如何选择？

需要放大波动幅度而使用非零起点坐标时，建议使用曲线图

面积图和曲线图如何选择？

当只有一个数据系列的时候，二者完全等价，都可以使用

当在大型会议室演示时，坐在后排的人很可能无法看清曲线图的线条，而面积图则更易让人看清楚

当比较多个数据系列的趋势时，建议使用曲线图

5.10常见图表类型创建综合例举

模块6数据透视表——数据检索汇总的万花筒

目标：本模块主要讲解如何运用数据透视表进行高效数据分析方法。

6.1数据透视表的概述

数据透视表功能概述

数据透视表创建对数据源的要求

整理数据源为最佳效果

数据透视表字段列表知识

6.2数据透视表的创建基本操作

调整行字段的排列顺序

显示隐藏透视表字段的分类汇总

为一个字段添加多种分类汇总方式

报表布局的三种显示形式及区别

在每个项目后插入空行

合并居中显示项目标签

6.3数据透视表排序与筛选

手动排序

自动排序

应用“其他排序选项”排序

在数据透视表中筛选数据

6.4数据透视表项目组合

运用手动方式组合数据项

对数值型数据进行组合

对日期数据进行组合

借助辅助列进行数据组合

6.5在透视表中执行计算

对同一字段进行多种分类汇总设置

透视表自定义值显示方式

在透视表中添加计算字段

在透视表中使用计算项进行计算

6.6数据透视表的切片区（均用于2013或2016）

切片器的应用概述

创建切片器

更改切片器的布局

模块7使用加载宏完成高效数据处理

目标：本模块主要是讲解一些“干货”内容，让日常常见的报表数据处理提升到一个更高的层次，可以将一天或半天的工作量，提升到分钟级的操作。

1.快速批量复制数据（制作工资条、在一张工作表内以“秒”为单位复制多张表格……）

2.快速批量复制工作表、批量命名工作表（适合于创建年度报告、多地区报表等应用）

3.按条件拆分工作表（按照报表的某列或多列为条件，将报表“秒”拆为多张工作表或多个工作簿文件）

4.拆分工作簿（可以将工作簿中的多张工作表“秒”拆成独立的单个文件）

5.合并工作表（可以将多个工作表进行“秒”合并，然后进行各类统计、数据处理等操作）

6.合并工作簿（可以将多个文件进行高效率的合并至一张工作表或一个工作簿文件中，以便于进行数据的统计等操作）

7.自定义函数（合并文本、转换大写金额、按颜色计数和求和、取身份证号码的相关数据、计算最新的个人所得税等）