Excel菜鸟职场办公效率提升秘籍-文档资料

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

2、高效数据分析工具及应用高效数据分析工具及应用4前言前言（Excel的使用习惯） 你设计表格是否很随意？想怎么弄就怎么弄？ 你是不是输入数据也很随意，而不考虑Excel对数据格式的要求？ 你是不是特别喜欢设计多行表头，还有大量合并单元格？ 你是不是特别喜欢在表格里插入很多合计行和空行？ 你是不是特别喜欢把所有的数据（包括计算结果）都保存在一个工作表，结果表格有非常多的列？ 你是不是只会简单的加减乘除，而不会使用函数进行汇总计算？ 你是不是只会使用简单的直接单元格链接，而不会使用函数引用数据？ 你是不是不注意保护工作表的计算公式？ 你是不是总在使用笨办法来处理数据？5前言前言（Excel的标准化和

3、规范化）树立一种正确使用Excel的理念科学规范的表格结构科学规范的数据结构正确区分和处理两类Excel表格基础表报告表正确处理三种Excel数据 文本（纯文本和数字型文本） 日期和时间 数字（纯数字和文本型数字）6第一部分第一部分 高效数据处理高效数据处理技能1：快速处理非法日期技能2：非法数字与纯数字之间的相互转换技能3：快速填充数据（定位填充法的应用）技能4：删除数据中眼睛看不见的特殊字符技能5：自定义排序与高级筛选的灵活运用技能6：数据的快速分列技能7：设计标准的表格模板，数据有效性应用技能8：保护工作表指定区域，以免别人破坏或修改7技能技能1：快速处理非法日期：快速处理非法日期很多人

4、对日期数据的输入是很随意的，有时侯输入的“日期”并不是真正的日期。例如：“20140817”、“2014.08.17”注：日期在Excel中是作为数值储存在单元格中的，永远是正整数。正确的日期格式：年月日3个数字之间用减号（-）或者斜杠（/）隔开的。案例01：非法日期的快速转换8技能技能2：非法数字与纯数字之间的相互转换：非法数字与纯数字之间的相互转换F在单元格输入文本型数字的方法有两种：在单元格输入文本型数字的方法有两种：方法1：应先输入单引号“ ”，再输入数字；方法2：先将单元格设置为文本，然后正常输入数字。F文本型数字转换为纯数字文本型数字转换为纯数字利用智能标记使用分列工具选择性粘贴F

5、纯数字转换为文本型数字纯数字转换为文本型数字使用Text() 函数使用分列工具案例02：非法数字与纯数字的相互转换9技能技能3：快速填充数据：快速填充数据数据区域内如果有空白单元格，在使用数据透视表做数据分析时就会造成一些数据的分析困难，甚至会影响到数据分析的准确性。定位填充法步骤：定位填充法步骤：1、选择整个数据区域2、使用F5健，把数据区域内的所有空单元格都选中3、在活动单元格中输入公式，“=活动单元格上一个单元格的地址”4、按 Ctrl+Enter 组合键 案例03：快速填充数据10技能技能4：删除数据中：删除数据中看不见的看不见的特殊字符特殊字符从数据库导入的数据往往会含有一些眼睛看不

6、见的特殊字符，为了能正确的处理分析，必须把这些特殊字符删除。步骤：1）复制单元格中看不见的字符2）打开查找和替换对话框，将特殊字符粘贴进“查找内容”输入框中，“替换为”输入框中不输入任何字符，单击“全部替换”按钮。案例04 删除数据中看不见的特殊字符11技能技能5：自定义排序与高级筛选的灵活运用：自定义排序与高级筛选的灵活运用排序排序如何按照规定的格式进行排序？排序的妙用：制作工资条注意事项（有公式时要小心）案例05 自定义排序与高级筛选应用筛选筛选如何筛选不重复数据？如何进行多条件下的高级筛选？案例05 自定义排序与高级筛选应用12技能技能6：数据的快速分列：数据的快速分列数据分列是在实际工

7、作中经常遇到的问题。基本方法基本方法方法1：使用“分列”工具根据分融符号进行数据分列根据数据宽度进行数据分列方法2：利用函数案例06 数据的快速分列13技能技能7：设计标准的表格模板：设计标准的表格模板使用数据有效性规范控制数据的输入。数据有效性的应用：案例07 设计标准规范的管理表单 14技能技能8：保护工作表：保护工作表指定区域指定区域工作表中的有些地方有时是需要保护或设置修改权限的，例如：做好的计算公式等。步聚Step1：先解除整个工作表或者指定区域的锁定Step2：选择要保护的公式单元格，锁定或隐藏Step3：保护工作表案例08 保护工作表15第二部分第二部分 日常数据的计算、提取、汇

8、日常数据的计算、提取、汇总逻辑判断及查询应用总逻辑判断及查询应用技能9：日期的计算（日期函数的综合应用）技能10：从数据字符中提取需要的数据（文本函数综合应用）技能11：日常数据的逻辑判断处理技能12：日常数据的高效求和与汇总技能13：日常数据的高效查询16技能技能9：日期计算（日期函数的综合应用）日期计算（日期函数的综合应用）3个重要的日期函数TODAY 计算当天日期 用法：=TODAY()EDATE 计算到期日 用法：=EDATE(开始日期，总月数)DATEDIF 计算两个日期之间的期限 用法： =DATEDIF(开始日期，截止日期，计算类型)计算类型：Y时间段中的整年数;M 时间段中的整

9、月数。 问题：已知合同签订日和期限，如何计算合同到期日？已知付款期限，截止日为下月5日，如何计算付款日？已知生日，如何计算年龄？案列09 常用日期函数的应用17技能技能10：从数据字符中提取需要的数据：从数据字符中提取需要的数据F几个注意事项：几个注意事项： 输入文字应该注意的几个问题 正确区别文本型数字和纯数字 将不能计算的数字快速修改为可以计算的纯数字F常用的几个文本函数常用的几个文本函数 LEN( ) 计算字符串的长度 LENB( ) 计算字符串中字节的长度 LEFT( ) 从左往右取数 RIGHT( ) 从右往左取数 MID() 从指定位置取数案例10 文本函数的综合应用18技能技能1

10、1：日常数据的逻辑判断处理：日常数据的逻辑判断处理数据的处理和分析，离不开逻辑判断。常用的逻辑判断函数IF，简单的判断函数 =IF(判断表达式，条件成立的结果，条件不成立的结果)嵌套IF，注意逻辑关系和正确的输入方法案例11 数据的逻辑判断处理19技能技能12：日常数据的高效求和与汇总：日常数据的高效求和与汇总 常用的分类汇总函数SUM： 简单的无条件求和函数SUMIF：单条件求和函数=SUMIF(条件判断区域，条件值，实际求和区域)SUMIF函数两种用法：1. 精确匹配汇总 2. 模糊匹配汇总注：注：sumif函数模糊匹配汇总，使用通配符合连接符函数模糊匹配汇总，使用通配符合连接符 通配符通

11、配符 * 连接符连接符 &案例12 数据的高效求和汇总20技能技能13：日常数据的高效查询：日常数据的高效查询查找数据：查找数据：FF 从表格中把满足条件的数据查找出来FF 常用函数：VLOOKUP=VLOOKUP(查找依据，查找区域，取数的列位置，查找模式)FF 注意事项 列结构表格 单条件查询 从左往右查找 不允许有重复 不区分大小写案例13 日常数据的高效查询21第三部分第三部分 高效数据分析工具及应用高效数据分析工具及应用技能14：条件格式的实际应用技能15：数据透视表的快速创建与布局技能16：数据报表的多角度统计分析技能17：数据报表的占比分析技能18：数据报表中日期、地区、

12、年龄、工资的组合统计分析应用技能19：数据透视表中新分析指标的添加技能20：相同结构工作表的快速汇总（多表快速汇总）技能21：快速核对数据（不同工作表数据的快速核销）22技能技能14：条件格式的实际应用：条件格式的实际应用条件格式条件格式F 根据指定的条件，来设置单元格格式F 应用： 跟踪数据变化 合同提前提醒 库存预警 美化表格F 注意事项： 正确设置公式 正确设置单元格引用方式案例14 条件格式的实际应用23技能技能15：数据透视表的快速创建与布局：数据透视表的快速创建与布局创建数据透视表创建数据透视表F利用数据透视表向导 按照向导步骤进行操作缺点：无法实现数据源的动态更新，除非使用动态数

13、据区域名称F利用导入数据工具制作数据透视表 利用Excel的导入数据工具制作优点：这种数据透视表是一种动态的数据透视表，如果工作表的数据的增加或减少了，数据透视表的数据源也随之发生改变案例15 数据透视表创建与布局24技能技能16：数据报表的多角度统计分析：数据报表的多角度统计分析案例16 数据报表的多角度统计分析源始数据多角度分析报表25技能技能17：数据报表的占比分析：数据报表的占比分析设置字段显示方式：对数据进行深层分析案例17 数据报表的占比分析26技能技能18：数据报表组合统计分析应用：数据报表组合统计分析应用组合字段：挖掘深层次分析指标按日期（年、季度、月）分组查看按地区分组查看数字组合（职工年龄、工资） 组合应注意的事项页字段不能进行组合字段内有空白项不能进行组合有非法日期时不能对日期进行组合某个字段内有不同类型数据时不能组合案例18 数据的组合应用27技能技能19：数据透视表中新分析指标的添：数据透视表中新分析指标的添加加添加自定义计算字段通过自定义计算字段，可以为报表设计新的考察分析指标，对数据进行多角度和深层次的分析。案例19 数据透视表中新分析指标的添加源始数据新增指标后的分析报表28技能技能20：相同结构工作表的快速汇总：相同结构工作表的快速汇总 使用合并计算