2018-实用EXCEL技能培训课件

1、实用电子表格技能技术交流,制表原则,透视表技巧,常用快捷键技巧,常用函数,运营部：张雨萍 金纯枝,时间：2018.8,操作练习,2018-实用EXCEL技能培训,培训说明,本次培训ppt与实操指南基于office Excel 2013以上版本； 2010版本与2013版本之间界面的变化较大； 但是培训教材中所有的设置选项均可以在较旧的版本中找到； 极少部分高级的选择在2013以下版本中找不到； 建议大家安装最新的Excel软件，目前版本office Excel最新版本为2016； 不排除各种学习途径上流传的比本ppt培训更方便快捷的方法，希望善于钻研学习的学员不断升级自身水平,2018-实用E

2、XCEL技能培训,目 录,CONTENTS,制表原则,透视表技巧,项目管理制表案例,常用函数,01,02,03,04,05,Excel实用快捷键与常用工具,2018-实用EXCEL技能培训,2018-实用EXCEL技能培训,2018-实用EXCEL技能培训,制表原则篇,01,2018-实用EXCEL技能培训,3 、缺失关键信息,10、 汇总表手工做,6 、合并单元格,1 、多余的表头,2 、顺序不合理，不符合正常人思维,4 、空行空列分割,5、 多余合计行,9 、同一个表格中记录复合属性,7、 单元格信息空缺,8 、源数据分在不同的工作表,2018-实用EXCEL技能培训,多余的表头,2018

3、-实用EXCEL技能培训,多余合计行,2018-实用EXCEL技能培训,合并单元格,2018-实用EXCEL技能培训,Oh NO,同一个表格中记录复合属性,2018-实用EXCEL技能培训,对应的工作表： 源数据表； 源数据表或其他辅助工作表; 透视表,Excel工作的步骤： 数据的录入（导入）； 数据处理; 数据分析,对应的操作： 输入（导入）数据； 整理数据（函数等技巧）; 对数据进行分类汇总,2018-实用EXCEL技能培训,2018-实用EXCEL技能培训,透视表篇,02,2018-实用EXCEL技能培训,日期正确格式：2018-01-12； 2018/7/23； 2018年7月23日

4、 记不清楚统一为： 2018/7/23（占地最窄, 快捷输入方法ctrl+;,格式正确与否的检查方法：选中自己的日期列数据，数据- -筛选- -标题行右侧倒三角点击- -excel自动按照年，月，日分层级即为正确。错误示例见右侧截图示例,错误示例,源数据 数据处理- -日期格式整理,2018-实用EXCEL技能培训,正确格式,源数据 数据处理- -日期格式整理,2018-实用EXCEL技能培训,无法识别的日期格式,网络或者系统中下载的xls格式数据，格式是正确的，但是有时候电子表格却无法识别，可以把”-”改为”,源数据 数据处理- -日期格式整理,2018-实用EXCEL技能培训,左侧显示检查

5、出错误的标志的数值，选中区域第一个单元格必须为出现错误的单元格，然后点击左侧出现的 黄色菱形标志，选择转换为数字,错误格式，只会计数,正确格式，不仅会计数，还会自动求和，求平均,源数据 数据处理-数值 格式整理,2018-实用EXCEL技能培训,表格格式： 1、除首行外，行高设置11，字体大小8，字体选择微软雅黑就好；视力不佳者的可以通过表格右下角百分比调节来查看。2、每列宽度合适，现有列宽不要变更表格内容： 1、不允许个人修改模板的列标题与顺序；2、内容要详细，表头/行标题中用批注注明必填内容；3、不要设置合并单元格，改用颜色与方框实现效果；4、使用的公式都要弄懂，每次都做一下例行检查；5、

6、单元格内容首尾不要出现空格;6、不要出现批量填充错误;7、不要设置整行整列颜色底纹，设置颜色底纹列设置到标题结束即可，行设置到数据结束即可，预防表格因为太多单元格颜色填充太大打开不了;8、不要设置整行整列边框，边框列设置到标题结束即可，行设置到数据结束即可，预防表格因为太多单元格设置边框太大打开不了,个人表格式习惯设置（仅供参考,2018-实用EXCEL技能培训,选中整张源数据表格的所有数据列插入数据透视表,注意:标题部分不能有空格或者标题连续,源数据 数据分析- -透视表分析,2018-实用EXCEL技能培训,源数据 数据分析- -透视表分析,2018-实用EXCEL技能培训,数据透视表生成

7、,源数据 数据分析- -透视表分析,2018-实用EXCEL技能培训,数据透视表字段框中可以选择您要关注的重点字段（字段可以理解是源数据表的每一列的首行标题,筛选器 列字段 行字段 分类汇总项,系统四区,可以勾选 可以将勾选的字段分布根据要达到的目的拖曳到四区,源数据 数据分析- -透视表分析,2018-实用EXCEL技能培训,比如，需要统计2018年年度各个销售员，各个月份的合同金额，选择的字段在四区域的分布如右图所示,源数据 数据分析- -透视表分析,2018-实用EXCEL技能培训,源数据 数据分析- -透视表分析,2018-实用EXCEL技能培训,源数据 数据分析- -透视表分析,20

8、18-实用EXCEL技能培训,源数据 数据分析- -透视表分析,2018-实用EXCEL技能培训,源数据表更新比较密集时，查看统计数据则注意刷新透视表格,源数据 数据分析- -透视表分析,2018-实用EXCEL技能培训,源数据 数据分析- -透视表分析,源数据刷新后容易改变表格格式，比如列宽。解决办法： 去掉 更新时自动调整列宽 前面的勾选,2018-实用EXCEL技能培训,企业的汇总表也许可以公布，但是数据明细表/源数据一定要保密，看看企业的学历组成无伤大雅，但是如果通过双击或者显示详细信息后看到明细，员工的个人资料，企业的产品报价信息就会全部泄露，后果。 注意泄露的2个途径： 1，透视表

9、中的：显示详细信息 2，黏贴统计后的图表为对象会附带源数据 防止办法： 1，透视表黏贴为链接，数值（下一页） 2，黏贴为链接，不在本机，对方就无法链接到源数据,注意保密源数据,源数据 数据分析- -透视表分析,2018-实用EXCEL技能培训,数值,链接,源数据 数据分析- -透视表分析,2018-实用EXCEL技能培训,项目管理制表案例,03,2018-实用EXCEL技能培训,案例1，项目管理一周周例会表打印表生成,登录项目管理系统，下载数据,2018-实用EXCEL技能培训,将xls 97-2003版本另存为.xlsx 高级版本的电子表格数据,案例1，项目管理一周周例会表打印表生成,201

10、8-实用EXCEL技能培训,打开xlsx格式表，得到系统中全部项目数据，将该数据sheet命名为源数据,案例1，项目管理一周周例会表打印表生成,选中整张源数据表格的所有数据列- -插入- -数据透视表,2018-实用EXCEL技能培训,在此处勾选你需要数据标签，会直接显示在下方的行列处，或者直接将标签拖拽到下面框里（所有的标签都可自由拖拽,案例1，项目管理一周周例会表打印表生成,2018-实用EXCEL技能培训,案例1，项目管理一周周例会表打印表生成,2018-实用EXCEL技能培训,案例1，项目管理一周周例会表打印表生成,2018-实用EXCEL技能培训,案例1，项目管理一周周例会表打印表生

11、成,2018-实用EXCEL技能培训,案例1，项目管理一周周例会表打印表生成,2018-实用EXCEL技能培训,不同的项目员如果只看自己的项目就可以只选择自己的名字的项目进行显示。 透视表是可以进行复制变换的,案例1，项目管理一周周例会表打印表生成,2018-实用EXCEL技能培训,案例1，项目管理一周周例会表打印表生成,试一下选择性粘贴中的 粘贴格式 和 粘贴列宽,2018-实用EXCEL技能培训,案例1，项目管理一周周例会表打印表生成,想按照自己意愿排序,2018-实用EXCEL技能培训,案例1，项目管理一周周例会表打印表生成,想按照自己意愿排序，自己编写添加就好了,2018-实用EXCE

12、L技能培训,案例1，项目管理一周周例会表打印表生成,一页打印不下，下一页没有标题了怎办,2018-实用EXCEL技能培训,案例1，项目管理一周周例会表打印表生成,日期 和页码 一次性设置,2018-实用EXCEL技能培训,案例1，项目管理一周周例会表打印表生成,标题,日期,页码/共计页数,2018-实用EXCEL技能培训,登录项目管理系统，下载数据,案例2，怎样制作一张销售三维数据表,2018-实用EXCEL技能培训,将xls 97-2003版本另存为.xlsx 高级版本的电子表格数据,案例2，怎样制作一张销售三维数据表,2018-实用EXCEL技能培训,源数据 数据处理- -日期格式整理,案

13、例2，怎样制作一张销售三维数据表,2018-实用EXCEL技能培训,案例2，怎样制作一张销售三维数据表,方法见前文,2018-实用EXCEL技能培训,标签选择：合同号（可选）/合同日期、销售员、合同金额(必选,案例2，怎样制作一张销售三维数据表,2018-实用EXCEL技能培训,巧妙组合日期,不能有日期格式错误，不能有空白：筛选出空格- -源数据找到修改补充好后，回到透视表一定要到透视表工具分析刷新（非常重要），再重复下面步骤,正确,源数据 数据分析- -透视表分析,筛选出2个空值,补上日期，刷新再试,案例2，怎样制作一张销售三维数据表,回忆前文,源数据表中,2018-实用EXCEL技能培训,

14、s,案例2，怎样制作一张销售三维数据表,变,2018-实用EXCEL技能培训,统计销售金额,再变,再 再变,案例2，怎样制作一张销售三维数据表,2018-实用EXCEL技能培训,案例2，怎样制作一张销售三维数据表,记得汇总方式的选择,1,2,3,4,5,2018-实用EXCEL技能培训,其他项目管理工作表功能展示,0-LC_library_all_20180801.xlsx,问题款项整理汇总_辅助表180511.xlsx,2018-实用EXCEL技能培训,对应的工作表： 源数据表； 源数据表或其他辅助工作表; 透视表,Excel工作的步骤： 数据的录入（导入）； 数据处理; 数据分析,项目管理

15、系统的构建思维,数据库 后台各种算法 各种显示界面,如果数据都提供不全，很多功能都会出问题,感谢曾经那么努力的自己,别懒,2018-实用EXCEL技能培训,Excel实用快捷键与常用工具,04,2018-实用EXCEL技能培训,常用快捷键,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,1， 冻结与筛选,冻结：当你希望表头部分一直在视野范围内，不随右方或者下方的滑动键拖动而消失时，可对这部分的内容使用冻结功能。 选择需要冻结的区域下一行（或一列），点击点击菜单【视图】下的冻结窗格图标，在下拉菜单中选择【冻结首行,这时再上下滚动表格可以发现，表格的标题已经固定不动，只有下面的内

16、容在上下的滚动，这就很方便的查找每一行中的数据,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,1，冻结与筛选,筛选：首先我们选中相关内容后，在【数据】中点击【筛选,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,1，冻结与筛选,筛选：然后我们可以看到，首行各个单元格都会出现三角形选项按键，点击，会依次显出来升序，降序排列，按颜色排序，按颜色筛选，文本筛选，还有一个可以自定义查找的搜索窗格，下面还会列出这列数据中的条目，如果比如整列数据只有基因的上调up和下调down，还可以通过点击去掉全选，只勾选up的来筛选全部的上调基因,2018-实用EXCEL技能培训

17、,Excel常用工具与实用快捷键,2，分列,选中要的1列数据，【数据】中选择【分列,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,2，分列,弹出窗口有两个选项，选择分隔符号方式，单击【下一步,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,2，分列,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,2，分列,结果,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,3，排序,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,3，排序,选择你需要的数据,2018-实用EXCEL技能培训,Excel常用工具与实用快

18、捷键,4，突出显示,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,4，突出显示,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,4，突出显示,结果,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,5，去重方法一（数据要先突出显示，在去重,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,5，去重,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,5，去重,结果,未去重前的数据,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,5，去重方法二（可以试一下F和G列（两列数据）都选中去重,步骤：选

19、中F和G列删除重复项全选两列数据确定,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,和只选中G列去重对比一下结果,5，去重,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,8，快速填充 右下角拖曳,选中可以按照已有规律填充的单元格等待最右下角出现“+”按住鼠标左键直接拖拽，或者邻近列非空时候直接当右下角出现“+”的时候双击鼠标左键快速填充,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,9，快速填充 填充按钮,在第一个单元格中输入数值，选择：编辑填充序列 设置步长值为1，终止值为1000，序列产生在行或列可自选,2018-实用EXCEL技

20、能培训,Excel常用工具与实用快捷键,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,8，快速填充 定位空值，输入数据、ctrl+enter,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,8，快速填充 定位空值，输入数据，ctrl+enter,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,8，快速填充 定位空值，输入数据，ctrl+enter,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,8，快速填充 定位空值，输入数据，ctrl+enter,2018-实用EXCEL技能培训,Excel常用工具与实用快捷键,9，快

21、速填充 定位空值，右下角拖拽,2018-实用EXCEL技能培训,常用函数,05,2018-实用EXCEL技能培训,常用函数,常用函数分类,1,日期与时间函数,2,数学与三角函数,3,统计数据,4,查找与应用函数,5,文本函数,6,逻辑函数,7,信息函数,8,数据库函数,共九部分,函数分类,9,财务函数,2018-实用EXCEL技能培训,常用函数,常用函数注意事项,公式以等号（=）开头,公式中可以包括：数值和字符、单元格地址、区域、区域名字、函数等,不要随意包含空格,公式中的字符要用引号（“”）括起来,公式中优先顺序：算术运算符号字符运算符号比较运算符逻辑函数符（使用括号可确定运算顺序,2018

22、-实用EXCEL技能培训,常用函数,1， Countblank：统计空单元格的个数 Countif:按条件统计单元格个数,2018-实用EXCEL技能培训,常用函数,6，Count，Counta和Countif,Countif:按条件统计单元格个数,函数括号中数据格式：计数区域，计数需要满足的条件,2018-实用EXCEL技能培训,常用函数,6，Count，Counta和Countif,Countif:按条件统计单元格个数,函数括号中数据格式：计数区域，计数需要满足的条件,2018-实用EXCEL技能培训,常用函数,7，And和Or,And:与，且的意思。如果其参数都成立就返回True ，有一

23、个不成立否则返回False； Or:或者,其里面有一个参数成立,则返回TRUE,否则返回FALSE,2018-实用EXCEL技能培训,常用函数,8，Len，Lower和upper,Len：返回单元格中的字符个数,2018-实用EXCEL技能培训,常用函数,8，Len，Lower和upper,Lower：大写字母变小写,2018-实用EXCEL技能培训,常用函数,8，Len，Lower和upper,Upper：小写字母变大写,2018-实用EXCEL技能培训,常用函数,9，Left、right、mid,函数括号中数据格式：文本来源，从左端开始提取几个字符,2018-实用EXCEL技能培训,常用函

24、数,9，Left、right、mid,函数括号中数据格式：文本来源，从右端开始提取几个字符,2018-实用EXCEL技能培训,常用函数,9，Left、right、mid,看一个right函数的升级用法,2018-实用EXCEL技能培训,常用函数,9，Left、right、mid,看一个right函数的升级用法,2018-实用EXCEL技能培训,常用函数,函数括号中数据格式：文本来源，从中间第几个字符开始，提取几个字符,9，Left、right、mid,2018-实用EXCEL技能培训,常用函数,10，Column；row,2018-实用EXCEL技能培训,常用函数,10，Column；row,

25、2018-实用EXCEL技能培训,常用函数,10，Column；row,2018-实用EXCEL技能培训,常用函数,10，Column；row,2018-实用EXCEL技能培训,Vlookup :查找引用函数,11，Lookup和Vlookup,函数括号中数据格式：条件值来源，查询区域，提取查询区域的第几列数据，精确匹配FALSE或0/模糊匹配TURE或者1,常用函数,2018-实用EXCEL技能培训,Vlookup :查找引用函数,11，Lookup和Vlookup,函数括号中数据格式：条件值来源，查询区域，提取查询区域的第几列数据 简单版本,VLOOKUP(K2,A:I,2)，K2是提取的条件，A:I表示提取的区域为A到I列，4表示提取区域第1列中为K2相同的对应的第4列值，再加上精确匹配0,常用函数,2018-实用EXCEL技能培训,Vlookup :查找引用函数,11，Lookup和Vlookup,函数括号中数据格式：条件值来源，查询区域，提取查询区域的第几列数据 简单版本,