实用EXCEL技能培训课件.pptx

《实用EXCEL技能培训课件.pptx》由会员分享，可在线阅读，更多相关《实用EXCEL技能培训课件.pptx（126页珍藏版）》请在三一办公上搜索。

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

2、案例,常用函数,01.,02.,03.,04.,05.,Excel实用快捷键与常用工具,制表原则篇,01.,3 、缺失关键信息,10、 汇总表手工做,6 、合并单元格,1 、多余的表头,2 、顺序不合理，不符合正常人思维,4 、空行空列分割,5、 多余合计行,9 、同一个表格中记录复合属性,7、 单元格信息空缺,8 、源数据分在不同的工作表,多余的表头,多余合计行,合并单元格,Oh NO,同一个表格中记录复合属性,对应的工作表：源数据表；源数据表或其他辅助工作表;透视表；,Excel工作的步骤：数据的录入（导入）；数据处理;数据分析;,对应的操作：输入（导入）数据；整理数据（函数等技巧）;对数

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

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

5、不要出现空格;6、不要出现批量填充错误;7、不要设置整行整列颜色底纹，设置颜色底纹列设置到标题结束即可，行设置到数据结束即可，预防表格因为太多单元格颜色填充太大打开不了;8、不要设置整行整列边框，边框列设置到标题结束即可，行设置到数据结束即可，预防表格因为太多单元格设置边框太大打开不了;,个人表格式习惯设置（仅供参考）,选中整张源数据表格的所有数据列插入数据透视表,注意:标题部分不能有空格或者标题连续,源数据 数据分析- -透视表分析,源数据 数据分析- -透视表分析,数据透视表生成,源数据 数据分析- -透视表分析,数据透视表字段框中可以选择您要关注的重点字段（字段可以理解是源数据表的每一列

6、的首行标题）,筛选器列字段行字段分类汇总项,系统四区,可以勾选可以将勾选的字段分布根据要达到的目的拖曳到四区,源数据 数据分析- -透视表分析,比如，需要统计2018年年度各个销售员，各个月份的合同金额，选择的字段在四区域的分布如右图所示：,源数据 数据分析- -透视表分析,源数据 数据分析- -透视表分析,源数据 数据分析- -透视表分析,源数据 数据分析- -透视表分析,源数据表更新比较密集时，查看统计数据则注意刷新透视表格,源数据 数据分析- -透视表分析,源数据 数据分析- -透视表分析,源数据刷新后容易改变表格格式，比如列宽。解决办法：去掉 更新时自动调整列宽 前面的勾选,企业的汇总

7、表也许可以公布，但是数据明细表/源数据一定要保密，看看企业的学历组成无伤大雅，但是如果通过双击或者显示详细信息后看到明细，员工的个人资料，企业的产品报价信息就会全部泄露，后果。注意泄露的2个途径：1，透视表中的：显示详细信息2，黏贴统计后的图表为对象会附带源数据防止办法：1，透视表黏贴为链接，数值（下一页）2，黏贴为链接，不在本机，对方就无法链接到源数据,注意保密源数据,源数据 数据分析- -透视表分析,数值,链接,源数据 数据分析- -透视表分析,项目管理制表案例,03.,案例1，项目管理一周周例会表打印表生成,登录项目管理系统，下载数据,将xls 97-2003版本另存为.xlsx 高级版

8、本的电子表格数据,案例1，项目管理一周周例会表打印表生成,打开xlsx格式表，得到系统中全部项目数据，将该数据sheet命名为源数据,案例1，项目管理一周周例会表打印表生成,选中整张源数据表格的所有数据列- -插入- -数据透视表,在此处勾选你需要数据标签，会直接显示在下方的行列处，或者直接将标签拖拽到下面框里（所有的标签都可自由拖拽）,案例1，项目管理一周周例会表打印表生成,案例1，项目管理一周周例会表打印表生成,案例1，项目管理一周周例会表打印表生成,案例1，项目管理一周周例会表打印表生成,案例1，项目管理一周周例会表打印表生成,不同的项目员如果只看自己的项目就可以只选择自己的名字的项目进

9、行显示。透视表是可以进行复制变换的。,案例1，项目管理一周周例会表打印表生成,案例1，项目管理一周周例会表打印表生成,试一下选择性粘贴中的 粘贴格式 和 粘贴列宽,案例1，项目管理一周周例会表打印表生成,想按照自己意愿排序,案例1，项目管理一周周例会表打印表生成,想按照自己意愿排序，自己编写添加就好了。,案例1，项目管理一周周例会表打印表生成,一页打印不下，下一页没有标题了怎办？,案例1，项目管理一周周例会表打印表生成,日期 和页码 一次性设置。,案例1，项目管理一周周例会表打印表生成,标题,日期,页码/共计页数,登录项目管理系统，下载数据,案例2，怎样制作一张销售三维数据表,将xls 97-

10、2003版本另存为.xlsx 高级版本的电子表格数据,案例2，怎样制作一张销售三维数据表,源数据 数据处理- -日期格式整理,案例2，怎样制作一张销售三维数据表,案例2，怎样制作一张销售三维数据表,方法见前文,标签选择：合同号（可选）/合同日期、销售员、合同金额(必选),案例2，怎样制作一张销售三维数据表,(巧妙组合日期),不能有日期格式错误，不能有空白：筛选出空格- -源数据找到修改补充好后，回到透视表一定要到透视表工具分析刷新（非常重要），再重复下面步骤。,？,正确,源数据 数据分析- -透视表分析,筛选出2个空值,补上日期，刷新再试,案例2，怎样制作一张销售三维数据表,回忆前文,源数据表

11、中,s,案例2，怎样制作一张销售三维数据表,变,统计销售金额,再变,再再变,案例2，怎样制作一张销售三维数据表,案例2，怎样制作一张销售三维数据表,记得汇总方式的选择,1,2,3,4,5,其他项目管理工作表功能展示,0-LC_library_all_20180801.xlsx,问题款项整理汇总_辅助表180511.xlsx,对应的工作表：源数据表；源数据表或其他辅助工作表;透视表；,Excel工作的步骤：数据的录入（导入）；数据处理;数据分析;,项目管理系统的构建思维,数据库后台各种算法各种显示界面,如果数据都提供不全，很多功能都会出问题,感谢曾经那么努力的自己,别懒,Excel实用快捷键与常

12、用工具,04.,常用快捷键,Excel常用工具与实用快捷键, 1， 冻结与筛选,冻结：当你希望表头部分一直在视野范围内，不随右方或者下方的滑动键拖动而消失时，可对这部分的内容使用冻结功能。选择需要冻结的区域下一行（或一列），点击点击菜单【视图】下的冻结窗格图标，在下拉菜单中选择【冻结首行】。,这时再上下滚动表格可以发现，表格的标题已经固定不动，只有下面的内容在上下的滚动，这就很方便的查找每一行中的数据。,Excel常用工具与实用快捷键, 1，冻结与筛选,筛选：首先我们选中相关内容后，在【数据】中点击【筛选】：,Excel常用工具与实用快捷键, 1，冻结与筛选,筛选：然后我们可以看到，首行各个单

13、元格都会出现三角形选项按键，点击，会依次显出来升序，降序排列，按颜色排序，按颜色筛选，文本筛选，还有一个可以自定义查找的搜索窗格，下面还会列出这列数据中的条目，如果比如整列数据只有基因的上调up和下调down，还可以通过点击去掉全选，只勾选up的来筛选全部的上调基因。,Excel常用工具与实用快捷键,2，分列,选中要的1列数据，【数据】中选择【分列】：,Excel常用工具与实用快捷键,2，分列,弹出窗口有两个选项，选择分隔符号方式，单击【下一步】：,Excel常用工具与实用快捷键,2，分列,Excel常用工具与实用快捷键,2，分列,结果：,Excel常用工具与实用快捷键,3，排序,Excel常

14、用工具与实用快捷键,3，排序,选择你需要的数据,Excel常用工具与实用快捷键,4，突出显示,Excel常用工具与实用快捷键,4，突出显示,Excel常用工具与实用快捷键,4，突出显示,结果：,Excel常用工具与实用快捷键,5，去重方法一（数据要先突出显示，在去重）,Excel常用工具与实用快捷键,5，去重,Excel常用工具与实用快捷键,5，去重,结果：,未去重前的数据,Excel常用工具与实用快捷键,5，去重方法二（可以试一下F和G列（两列数据）都选中去重）,步骤：选中F和G列删除重复项全选两列数据确定,Excel常用工具与实用快捷键,和只选中G列去重对比一下结果。,5，去重,Excel

15、常用工具与实用快捷键,8，快速填充右下角拖曳,选中可以按照已有规律填充的单元格等待最右下角出现“+”按住鼠标左键直接拖拽，或者邻近列非空时候直接当右下角出现“+”的时候双击鼠标左键快速填充,Excel常用工具与实用快捷键,9，快速填充填充按钮,在第一个单元格中输入数值，选择：编辑填充序列 设置步长值为1，终止值为1000，序列产生在行或列可自选！,Excel常用工具与实用快捷键,Excel常用工具与实用快捷键,8，快速填充定位空值，输入数据、ctrl+enter,Excel常用工具与实用快捷键,8，快速填充定位空值，输入数据，ctrl+enter,Excel常用工具与实用快捷键,8，快速填充定

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

17、运算符号比较运算符逻辑函数符（使用括号可确定运算顺序）,常用函数,1，&和$,&:连接符,相录于函数CONCATENATE,第二 章 常用函数,$:固定地址引用符号,$C2，列固定C$2，行固定$C$2，单元格固定一旦被固定，快速填充后行号或者列号不再是随位置变化而变化的变量,1，&和$,常用函数,2，Abs ，Average，min，max，sum,=Abs(B5)，求B5单元格数值的绝对值=Average(B5:B8)，求B5单元格到B8单元格的平均值=min(B5:B8)，求B5单元格到B8单元格的最小值=max(B5:B8)，求B5单元格到B8单元格的最大值,常用函数,3，Sumif

18、(Sumifs)和Subtotal,Sumif:按条件求和,函数括号中数据格式：符合条件区域，条件，求和区域,常用函数,3，Sumif(Sumifs)和Subtotal,Sumif:按条件求和,函数括号中数据格式：符合条件区域，条件，求和区域,看一种升级版的条件求和，求和区域设置标题等于【数量】,常用函数,3，Sumif(Sumifs)和Subtotal,Subtotal:分类汇总函数,函数括号中数据格式：统计选项，统计数据区域,常用函数,3，Sumif (Sumifs)和Subtotal,Subtotal:分类汇总函数,函数括号中数据格式：统计选项，统计数据区域,常用函数,3，Sumif (

19、Sumifs)和Subtotal,Subtotal:分类汇总函数,函数括号中数据格式：统计选项，统计数据区域,常用函数,4，Rank,Rank:排名函数,函数括号中数据格式：数值，参考区域,常用函数,5，if,函数括号中数据格式：判断条件，符合输出，不符合则输出,常用函数,6，Count，Counta和Countif,Count:统计含数值型数字的单元格的个数Counta:统计非空单元格的个数; Countblank：统计空单元格的个数Countif:按条件统计单元格个数,常用函数,6，Count，Counta和Countif,Countif:按条件统计单元格个数,函数括号中数据格式：计数区域

20、，计数需要满足的条件,常用函数,6，Count，Counta和Countif,Countif:按条件统计单元格个数,函数括号中数据格式：计数区域，计数需要满足的条件,常用函数,7，And和Or,And:与，且的意思。如果其参数都成立就返回True ，有一个不成立否则返回False；Or:或者,其里面有一个参数成立,则返回TRUE,否则返回FALSE,常用函数,8，Len，Lower和upper,Len：返回单元格中的字符个数,常用函数,8，Len，Lower和upper,Lower：大写字母变小写,常用函数,8，Len，Lower和upper,Upper：小写字母变大写,常用函数,9，Left

21、、right、mid,函数括号中数据格式：文本来源，从左端开始提取几个字符,常用函数,9，Left、right、mid,函数括号中数据格式：文本来源，从右端开始提取几个字符,常用函数,9，Left、right、mid,看一个right函数的升级用法,常用函数,9，Left、right、mid,看一个right函数的升级用法,常用函数,函数括号中数据格式：文本来源，从中间第几个字符开始，提取几个字符,9，Left、right、mid,常用函数,10，Column；row,常用函数,10，Column；row,常用函数,10，Column；row,常用函数,10，Column；row,Vlooku

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

23、域，提取查询区域的第几列数据简单版本,=VLOOKUP(K2,A:I,2,0)，K2是提取的条件，A:I表示提取的区域为A到I列，4表示提取区域第1列中为K2相同的对应的第4列值，再加上精确匹配0。,常用函数,Vlookup :查找引用函数,11，Lookup和Vlookup,方便快速填充的升级版用=COLUMN()来求一下列号,常用函数,Vlookup :查找引用函数,11，Lookup和Vlookup,方便快速填充的升级版=VLOOKUP($K185,$A$184:$I$212,COLUMN()-10,0),常用函数,Vlookup :查找引用函数,11，Lookup和Vlookup,方便

24、快速填充的升级版=VLOOKUP($K185,$A$184:$I$212,COLUMN()-10,0),常用函数,Vlookup :查找引用函数,12，Lookup和Vlookup,为了方便快速向右填充，我们的K列会固定。=VLOOKUP($K185,$A$184:$I$212,COLUMN()-10,0)，并且使用了column函数实现提取列数的变量，但是为了方便向下填充，我们的行号不使用$符号固定。,常用函数,13，networkdays,常用函数,是返回起始日期和结束日期之间的工作日数,14，networkdays.intl,常用函数,是返回两个日期之间的所有工作日数,学习资源, 1，Execel Home 微信，腾讯微博，新浪微博公众号, 2，网易云课堂，腾讯课堂, 3，公司买的你早该这样玩Excel系列书籍, 4，练习题详见练习Excel,Enjoy the talks!Questions?,