Excel菜鸟职场办公效率提升秘籍PPT课件

1、主讲老师：彭祥峰 YY课堂：15799318 网 址： 2 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 如何提高日常数据处理效率？如何提高日常数据处理效率？ 树立一种正确高效使用Excel的基本理念和思路 掌握实用的Excel技能和技巧 灵活使用常用的函数 了解和掌握一些实用的工具 熟练使用数据透视表 3 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 课课 程程 目目 录录 前言： 第一部分 高效数据处理（让你的表格标准化、规 范化，为汇总分析打基础） 第二部分 日常数据的计算、提取、汇总、逻辑判 断

2、及查询应用 第三部分 高效数据分析工具及应用 4 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 前言（Excel的使用习惯） 你设计表格是否很随意？想怎么弄就怎么弄？ 你是不是输入数据也很随意，而不考虑Excel对数据格式的要求？ 你是不是特别喜欢设计多行表头，还有大量合并单元格？ 你是不是特别喜欢在表格里插入很多合计行和空行？ 你是不是特别喜欢把所有的数据（包括计算结果）都保存在一个工作表， 结果表格有非常多的列？ 你是不是只会简单的加减乘除，而不会使用函数进行汇总计算？ 你是不是只会使用简单的直接单元格链接，而不会使用函数引用数据？ 你是不是不

3、注意保护工作表的计算公式？ 你是不是总在使用笨办法来处理数据？ 5 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 前言（Excel的标准化和规范化） 树立一种正确使用Excel的理念 科学规范的表格结构 科学规范的数据结构 正确区分和处理两类Excel表格 基础表 报告表 正确处理三种Excel数据 文本（纯文本和数字型文本） 日期和时间 数字（纯数字和文本型数字） 6 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 第一部分 高效数据处理 技能1：快速处理非法日期 技能2：非法数字与纯数字之间的相互转换

4、 技能3：快速填充数据（定位填充法的应用） 技能4：删除数据中眼睛看不见的特殊字符 技能5：自定义排序与高级筛选的灵活运用 技能6：数据的快速分列 技能7：设计标准的表格模板，数据有效性应用 技能8：保护工作表指定区域，以免别人破坏或修改 7 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能1：快速处理非法日期 很多人对日期数据的输入是很随意的，有时侯输入的 “日期”并不是真正的日期。 例如：“20140817”、“2014.08.17” 注：日期在Excel中是作为数值储存在单元格中的，永 远是正整数。 正确的日期格式：年月日3个数字之间用减号

5、（-）或者 斜杠（/）隔开的。 案例01：非法日期的快速转换 8 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能2：非法数字与纯数字之间的相互转换 F 在单元格输入文本型数字的方法有两种：在单元格输入文本型数字的方法有两种： 方法1：应先输入单引号“ ”，再输入数字； 方法2：先将单元格设置为文本，然后正常输入数字。 F 文本型数字转换为纯数字文本型数字转换为纯数字 利用智能标记 使用分列工具 选择性粘贴 F 纯数字转换为文本型数字纯数字转换为文本型数字 使用Text() 函数 使用分列工具 案例02：非法数字与纯数字的相互转换 9 Excel

6、菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能3：快速填充数据 数据区域内如果有空白单元格，在使用数据透视表做数 据分析时就会造成一些数据的分析困难，甚至会影响到数据 分析的准确性。 定位填充法步骤：定位填充法步骤： 1、选择整个数据区域 2、使用F5健，把数据区域内的所有空单元格都选中 3、在活动单元格中输入公式，“=活动单元格上一个单元格的地址” 4、按 Ctrl+Enter 组合键 案例03：快速填充数据 10 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能4：删除数据中看不见的特殊字符 从数据库导

7、入的数据往往会含有一些眼睛看不见的特殊字符，为了 能正确的处理分析，必须把这些特殊字符删除。 步骤： 1）复制单元格中看不见的字符 2）打开查找和替换对话框，将特殊字符粘贴进“查找内容”输入框中， “替换为”输入框中不输入任何字符，单击“全部替换”按钮。 案例04 删除数据中看不见的特殊字符 11 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能5：自定义排序与高级筛选的灵活运用 排序排序 如何按照规定的格式进行排序？ 排序的妙用：制作工资条 注意事项（有公式时要小心） 案例05 自定义排序与高级筛选应用 筛选筛选 如何筛选不重复数据？ 如何进行

8、多条件下的高级筛选？ 案例05 自定义排序与高级筛选应用 12 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能6：数据的快速分列 数据分列是在实际工作中经常遇到的问题。 基本方法基本方法 方法1：使用“分列”工具 根据分融符号进行数据分列 根据数据宽度进行数据分列 方法2：利用函数 案例06 数据的快速分列 13 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能7：设计标准的表格模板 使用数据有效性规范控制数据的输入。 数据有效性的应用： 案例07 设计标准规范的管理表单 14 Excel菜鸟职场

9、办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能8：保护工作表指定区域 工作表中的有些地方有时是需要保护或设置修改权限的， 例如：做好的计算公式等。 步聚 Step1：先解除整个工作表或者指定区域的锁定 Step2：选择要保护的公式单元格，锁定或隐藏 Step3：保护工作表 案例08 保护工作表 15 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 第二部分 日常数据的计算、提取、汇总 逻辑判断及查询应用 技能9：日期的计算（日期函数的综合应用） 技能10：从数据字符中提取需要的数据（文本函数综合 应用） 技能11：日常

10、数据的逻辑判断处理 技能12：日常数据的高效求和与汇总 技能13：日常数据的高效查询 16 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能9：日期计算（日期函数的综合应用） 3个重要的日期函数 TODAY 计算当天日期 用法：=TODAY() EDATE 计算到期日 用法：=EDATE(开始日期，总月数) DATEDIF 计算两个日期之间的期限 用法： =DATEDIF(开始日期，截止日期，计算类型) 计算类型：Y时间段中的整年数;M 时间段中的整月数。 问题： 已知合同签订日和期限，如何计算合同到期日？ 已知付款期限，截止日为下月5日，如何计

11、算付款日？ 已知生日，如何计算年龄？ 案列09 常用日期函数的应用 17 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能10：从数据字符中提取需要的数据 F几个注意事项：几个注意事项： 输入文字应该注意的几个问题 正确区别文本型数字和纯数字 将不能计算的数字快速修改为可以计算的纯数字 F常用的几个文本函数常用的几个文本函数 LEN( ) 计算字符串的长度 LENB( ) 计算字符串中字节的长度 LEFT( ) 从左往右取数 RIGHT( ) 从右往左取数 MID() 从指定位置取数 案例10 文本函数的综合应用 18 Excel菜鸟职场办公效率

12、提升秘籍 YY课堂：15799318 电话：4006456365 技能11：日常数据的逻辑判断处理 数据的处理和分析，离不开逻辑判断。 常用的逻辑判断函数 IF，简单的判断函数 =IF(判断表达式，条件成立的结果，条件不成立的结果) 嵌套IF，注意逻辑关系和正确的输入方法 案例11 数据的逻辑判断处理 19 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能12：日常数据的高效求和与汇总 常用的分类汇总函数 SUM： 简单的无条件求和函数 SUMIF：单条件求和函数 =SUMIF(条件判断区域，条件值，实际求和区域) SUMIF函数两种用法： 1.

13、 精确匹配汇总 2. 模糊匹配汇总 注：注：sumif函数模糊匹配汇总，使用通配符合连接符函数模糊匹配汇总，使用通配符合连接符 通配符通配符 * 连接符连接符 & 案例12 数据的高效求和汇总 20 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能13：日常数据的高效查询 查找数据：查找数据： FF 从表格中把满足条件的数据查找出来 FF 常用函数：VLOOKUP =VLOOKUP(查找依据，查找区域，取数的列位置，查找模式) FF 注意事项 列结构表格 单条件查询 从左往右查找 不允许有重复 不区分大小写 案例13 日常数据的高效查询 21 E

14、xcel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 第三部分 高效数据分析工具及应用 技能14：条件格式的实际应用 技能15：数据透视表的快速创建与布局 技能16：数据报表的多角度统计分析 技能17：数据报表的占比分析 技能18：数据报表中日期、地区、年龄、工资的组合统计分 析应用 技能19：数据透视表中新分析指标的添加 技能20：相同结构工作表的快速汇总（多表快速汇总） 技能21：快速核对数据（不同工作表数据的快速核销） 22 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能14：条件格式的实际应用 条件格

15、式条件格式 F 根据指定的条件，来设置单元格格式 F 应用： 跟踪数据变化 合同提前提醒 库存预警 美化表格 F 注意事项： 正确设置公式 正确设置单元格引用方式 案例14 条件格式的实际应用 23 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能15：数据透视表的快速创建与布局 创建数据透视表创建数据透视表 F利用数据透视表向导 按照向导步骤进行操作 缺点：无法实现数据源的动态更新，除非使用动态数据区域名称 F利用导入数据工具制作数据透视表 利用Excel的导入数据工具制作 优点：这种数据透视表是一种动态的数据透视表，如果工作表的 数据的增加或

16、减少了，数据透视表的数据源也随之发生改变 案例15 数据透视表创建与布局 24 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能16：数据报表的多角度统计分析 案例16 数据报表的多角度统计分析 源始数据 多角度分析报表 25 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能17：数据报表的占比分析 设置字段显示方式：对数据进行深层分析 案例17 数据报表的占比分析 26 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能18：数据报表组合统计分析应用

17、组合字段：挖掘深层次分析指标 按日期（年、季度、月）分组查看 按地区分组查看 数字组合（职工年龄、工资） 组合应注意的事项 页字段不能进行组合 字段内有空白项不能进行组合 有非法日期时不能对日期进行组合 某个字段内有不同类型数据时不能组合 案例18 数据的组合应用 27 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能19：数据透视表中新分析指标的添加 添加自定义计算字段 通过自定义计算字段，可以为报表设计新的考察分析指标，对数据进 行多角度和深层次的分析。 案例19 数据透视表中新分析指标的添加 源始数据 新增指标后的分析报表 28 Excel菜鸟职场办公效率提升秘籍 YY课堂：15799318 电话：4006456365 技能20：相同结构工作表的快