1、实用电子表格技能技术交流制表原则透视表技巧 常用快捷键技巧常用函数运营部：张雨萍 金纯枝时间：2018.8操作练习培训说明1.本次培训ppt与实操指南基于office Excel 2013以上版本；2.2010版本与2013版本之间界面的变化较大；3.但是培训教材中所有的设置选项均可以在较旧的版本中找到；4.极少部分高级的选择在2013以下版本中找不到；5.建议大家安装最新的Excel软件，目前版本office Excel最新版本为2016；6.不排除各种学习途径上流传的比本ppt培训更方便快捷的方法，希望善于钻研学习的学员不断升级自身水平目 录CONTENTS制表原则透视表技巧项目管理制表案
2、例常用函数01.02.03.04.05.Excel实用快捷键与常用工具 一张优秀的源数据表，应该具备描述整洁，字段完整，逻辑关系清晰。虽然不同行业，数据内容不同，但不影响表格的设计以及数据记录的方式。所以天下只有一张表。天下第一表的源数据条件：一维数数据唯一顶端标题行没有合并单元格连续的数据准确的数据内存天下只有一张表懒人眼中的Excel一张汇聚天下优点的表宗旨：以“变应万变和以不变生万变授权E表，坐享其成不变的追求：牵一发而动全身妙招生花，事半百倍制表原则篇01.3 、缺失关键信息10、 汇总表手工做6 、合并单元格1 、多余的表头十宗罪一张完美的表格不应出现以下10这种症状：2 、顺序不合
3、理，不符合正常人思维4 、空行空列分割5、 多余合计行9 、同一个表格中记录复合属性7、 单元格信息空缺8 、源数据分在不同的工作表 多余的表头多余合计行合并单元格Oh NO同一个表格中记录复合属性对应的工作表：源数据表；源数据表或其他辅助工作表;透视表； 规范操作是成功的关键Excel工作的步骤：数据的录入（导入）；数据处理;数据分析; 对应的操作：输入（导入）数据；整理数据（函数等技巧）;对数据进行分类汇总； Excel工作的步骤对应的工作表对应的操作标题文字添加天下第一表的源数据条件： 一维数据； 唯一标题行； 字段分类清晰； 数据属性完整； 数据连续； 无合并单元格； 无合计行； 无分
4、隔行/列; 数据区域中无空白单元格； 单元格内容禁用短语或句子;正确的源数据表应该满足以下条件:懒人眼中的Excel一张汇聚天下优点的表透视表篇02.日期正确格式：2018-01-12； 2018/7/23； 2018年7月23日记不清楚统一为： 2018/7/23（占地最窄, 快捷输入方法ctrl+;）格式正确与否的检查方法：选中自己的日期列数据，数据- -筛选- -标题行右侧倒三角点击- -excel自动按照年，月，日分层级即为正确。错误示例见右侧截图示例。错误示例源数据 数据处理- -日期格式整理正确格式源数据 数据处理- -日期格式整理无法识别的日期格式网络或者系统中下载的xls格式数
5、据，格式是正确的，但是有时候电子表格却无法识别，可以把”-”改为”/“。源数据 数据处理- -日期格式整理左侧显示检查出错误的标志的数值，选中区域第一个单元格必须为出现错误的单元格，然后点击左侧出现的 黄色菱形标志，选择转换为数字错误格式，只会计数正确格式，不仅会计数，还会自动求和，求平均源数据 数据处理-数值 格式整理表格格式： 1、除首行外，行高设置11，字体大小8，字体选择微软雅黑就好；视力不佳者的可以通过表格右下角百分比调节来查看。 2、每列宽度合适，现有列宽不要变更 表格内容： 1、不允许个人修改模板的列标题与顺序； 2、内容要详细，表头/行标题中用批注注明必填内容； 3、不要设置合
6、并单元格，改用颜色与方框实现效果； 4、使用的公式都要弄懂，每次都做一下例行检查； 5、单元格内容首尾不要出现空格; 6、不要出现批量填充错误; 7、不要设置整行整列颜色底纹，设置颜色底纹列设置到标题结束即可，行设置到数据结束即可，预防表格因为太多单元格颜色填充太大打开不了; 8、不要设置整行整列边框，边框列设置到标题结束即可，行设置到数据结束即可，预防表格因为太多单元格设置边框太大打开不了; 个人表格式习惯设置（仅供参考）选中整张源数据表格的所有数据列插入数据透视表注意:标题部分不能有空格或者标题连续源数据 数据分析- -透视表分析源数据 数据分析- -透视表分析各项目核酸样品质量要求质检Q
7、C报告样品递交单样本接收异常信息表筛选 区域列标题行标题统计区域标题选择区域数据透视表生成源数据 数据分析- -透视表分析数据透视表字段框中可以选择您要关注的重点字段（字段可以理解是源数据表的每一列的首行标题）筛选器列字段行字段分类汇总项系统四区l 可以勾选l 可以将勾选的字段分布根据要达到的目的拖曳到四区源数据 数据分析- -透视表分析比如，需要统计2018年年度各个销售员，各个月份的合同金额，选择的字段在四区域的分布如右图所示：源数据 数据分析- -透视表分析源数据 数据分析- -透视表分析源数据 数据分析- -透视表分析源数据 数据分析- -透视表分析源数据表更新比较密集时，查看统计数据
8、则注意刷新透视表格源数据 数据分析- -透视表分析源数据 数据分析- -透视表分析源数据刷新后容易改变表格格式，比如列宽。解决办法：u去掉 更新时自动调整列宽 前面的勾选企业的汇总表也许可以公布，但是数据明细表/源数据一定要保密，看看企业的学历组成无伤大雅，但是如果通过双击或者显示详细信息后看到明细，员工的个人资料，企业的产品报价信息就会全部泄露，后果。注意泄露的2个途径：1，透视表中的：显示详细信息2，黏贴统计后的图表为对象会附带源数据防止办法：1，透视表黏贴为链接，数值（下一页）2，黏贴为链接，不在本机，对方就无法链接到源数据注意保密源数据源数据 数据分析- -透视表分析数值链接源数据 数
9、据分析- -透视表分析项目管理制表案例03.案例1，项目管理一周周例会表打印表生成登录项目管理系统，下载数据将xls 97-2003版本另存为.xlsx 高级版本的电子表格数据案例1，项目管理一周周例会表打印表生成打开xlsx格式表，得到系统中全部项目数据，将该数据sheet命名为源数据案例1，项目管理一周周例会表打印表生成选中整张源数据表格的所有数据列- -插入- -数据透视表在此处勾选你需要数据标签，会直接显示在下方的行列处，或者直接将标签拖拽到下面框里（所有的标签都可自由拖拽）案例1，项目管理一周周例会表打印表生成案例1，项目管理一周周例会表打印表生成案例1，项目管理一周周例会表打印表生
10、成案例1，项目管理一周周例会表打印表生成案例1，项目管理一周周例会表打印表生成不同的项目员如果只看自己的项目就可以只选择自己的名字的项目进行显示。透视表是可以进行复制变换的。案例1，项目管理一周周例会表打印表生成案例1，项目管理一周周例会表打印表生成试一下选择性粘贴中的 粘贴格式 和 粘贴列宽案例1，项目管理一周周例会表打印表生成想按照自己意愿排序案例1，项目管理一周周例会表打印表生成想按照自己意愿排序，自己编写添加就好了。案例1，项目管理一周周例会表打印表生成一页打印不下，下一页没有标题了怎办？案例1，项目管理一周周例会表打印表生成日期 和页码 一次性设置。案例1，项目管理一周周例会表打印表
11、生成标题日期页码/共计页数登录项目管理系统，下载数据案例2，怎样制作一张销售三维数据表将xls 97-2003版本另存为.xlsx 高级版本的电子表格数据案例2，怎样制作一张销售三维数据表源数据 数据处理- -日期格式整理案例2，怎样制作一张销售三维数据表点击后，选择转换为数字案例2，怎样制作一张销售三维数据表方法见前文标签选择：合同号（可选）/合同日期、销售员、合同金额(必选)案例2，怎样制作一张销售三维数据表(巧妙组合日期)不能有日期格式错误，不能有空白：筛选出空格- -源数据找到修改补充好后，回到透视表一定要到透视表工具分析刷新（非常重要），再重复下面步骤。？正确源数据 数据分析- -透
12、视表分析筛选出2个空值补上日期，刷新再试案例2，怎样制作一张销售三维数据表回忆前文源数据表中s案例2，怎样制作一张销售三维数据表变统计销售金额再变再再变案例2，怎样制作一张销售三维数据表案例2，怎样制作一张销售三维数据表记得汇总方式的选择12345其他项目管理工作表功能展示0-LC_library_all_20180801.xlsx问题款项整理汇总_辅助表180511.xlsx对应的工作表：源数据表；源数据表或其他辅助工作表;透视表； 规范操作是成功的关键Excel工作的步骤：数据的录入（导入）；数据处理;数据分析; Excel工作的步骤对应的工作表对应的管理系统结构标题文字添加项目管理系统的
13、构建思维数据库后台各种算法各种显示界面别懒Excel实用快捷键与常用工具04.常用快捷键快捷键功能Shift+Ctrl+/选中连续非空单元格Ctrl+enter批量录入键Ctrl+1调出设置单元格窗口ctrl+A（左上角选中整个表格的单击键）在选项卡和命令行下；调出函数参数面板选中整列，鼠标移至边缘，Shift按住，鼠标挪移鼠标挪移整行整列：Ctrl+R复制左列快捷键功能Ctrl+D复制上行alt+enter单元格中的换行ctrl+;当前年/月/日shift+ctrl+；当前时间：9:24Excel常用工具与实用快捷键 1， 冻结与筛选冻结：当你希望表头部分一直在视野范围内，不随右方或者下方的
14、滑动键拖动而消失时，可对这部分的内容使用冻结功能。选择需要冻结的区域下一行（或一列），点击点击菜单【视图】下的冻结窗格图标，在下拉菜单中选择【冻结首行】。这时再上下滚动表格可以发现，表格的标题已经固定不动，只有下面的内容在上下的滚动，这就很方便的查找每一行中的数据。Excel常用工具与实用快捷键 1，冻结与筛选筛选：首先我们选中相关内容后，在【数据】中点击【筛选】：Excel常用工具与实用快捷键 1，冻结与筛选筛选：然后我们可以看到，首行各个单元格都会出现三角形选项按键，点击，会依次显出来升序，降序排列，按颜色排序，按颜色筛选，文本筛选，还有一个可以自定义查找的搜索窗格，下面还会列出这列数据中
15、的条目，如果比如整列数据只有基因的上调up和下调down，还可以通过点击去掉全选，只勾选up的来筛选全部的上调基因。Excel常用工具与实用快捷键n 2，分列选中要的1列数据，【数据】中选择【分列】：Excel常用工具与实用快捷键n 2，分列弹出窗口有两个选项，选择分隔符号方式，单击【下一步】：Excel常用工具与实用快捷键n 2，分列Excel常用工具与实用快捷键n 2，分列结果：Excel常用工具与实用快捷键n 3，排序Excel常用工具与实用快捷键n 3，排序选择你需要的数据Excel常用工具与实用快捷键n 4，突出显示Excel常用工具与实用快捷键n 4，突出显示Excel常用工具与实
16、用快捷键n 4，突出显示结果：Excel常用工具与实用快捷键n 5，去重方法一（数据要先突出显示，在去重）Excel常用工具与实用快捷键n 5，去重Excel常用工具与实用快捷键n 5，去重结果：未去重前的数据Excel常用工具与实用快捷键n 5，去重方法二（可以试一下F和G列（两列数据）都选中去重）步骤：选中F和G列删除重复项全选两列数据确定Excel常用工具与实用快捷键和只选中G列去重对比一下结果。n 5，去重Excel常用工具与实用快捷键n 8，快速填充 右下角拖曳选中可以按照已有规律填充的单元格等待最右下角出现“+”按住鼠标左键直接拖拽，或者邻近列非空时候直接当右下角出现“+”的时候双
17、击鼠标左键快速填充Excel常用工具与实用快捷键n 9，快速填充 填充按钮在第一个单元格中输入数值，选择：编辑填充序列 设置步长值为1，终止值为1000，序列产生在行或列可自选！Excel常用工具与实用快捷键Excel常用工具与实用快捷键n 8，快速填充 定位空值，输入数据、ctrl+enterExcel常用工具与实用快捷键n 8，快速填充 定位空值，输入数据，ctrl+enterExcel常用工具与实用快捷键n 8，快速填充 定位空值，输入数据，ctrl+enterExcel常用工具与实用快捷键n 8，快速填充 定位空值，输入数据，ctrl+enterExcel常用工具与实用快捷键n 9，快
18、速填充 定位空值，右下角拖拽 常用函数05.常用函数1日期与时间函数2数学与三角函数3统计数据4查找与应用函数5文本函数6逻辑函数7信息函数8数据库函数共九部分函数分类9财务函数常用函数公式以等号（=）开头公式中可以包括：数值和字符、单元格地址、区域、区域名字、函数等不要随意包含空格公式中的字符要用引号（“”）括起来公式中优先顺序：算术运算符号字符运算符号比较运算符逻辑函数符（使用括号可确定运算顺序）常用函数n 1，&和$ &:连接符,相录于函数CONCATENATE 第二 章 常用函数 $:固定地址引用符号$C2，列固定C$2，行固定$C$2，单元格固定一旦被固定，快速填充后行号或者列号不再
19、是随位置变化而变化的变量n 1，&和$常用函数n 2，Abs ，Average，min，max，sum=Abs(B5)，求B5单元格数值的绝对值=Average(B5:B8)，求B5单元格到B8单元格的平均值=min(B5:B8)，求B5单元格到B8单元格的最小值=max(B5:B8)，求B5单元格到B8单元格的最大值常用函数n 3，Sumif (Sumifs)和Subtotal Sumif:按条件求和函数括号中数据格式：符合条件区域，条件，求和区域常用函数n 3，Sumif(Sumifs)和Subtotal Sumif:按条件求和函数括号中数据格式：符合条件区域，条件，求和区域看一种升级版的
20、条件求和，求和区域设置标题等于【数量】常用函数n 3，Sumif(Sumifs)和Subtotal Subtotal:分类汇总函数函数括号中数据格式：统计选项，统计数据区域常用函数n 3，Sumif (Sumifs)和Subtotal Subtotal:分类汇总函数函数括号中数据格式：统计选项，统计数据区域常用函数n 3，Sumif (Sumifs)和Subtotal Subtotal:分类汇总函数函数括号中数据格式：统计选项，统计数据区域常用函数n 4，Rank Rank:排名函数函数括号中数据格式：数值，参考区域常用函数n 5，if函数括号中数据格式：判断条件，符合输出，不符合则输出常用函
21、数n 6，Count，Counta和Countif Count:统计含数值型数字的单元格的个数 Counta:统计非空单元格的个数; Countblank：统计空单元格的个数 Countif:按条件统计单元格个数常用函数n 6，Count，Counta和Countif Countif:按条件统计单元格个数函数括号中数据格式：计数区域，计数需要满足的条件常用函数n 6，Count，Counta和Countif Countif:按条件统计单元格个数函数括号中数据格式：计数区域，计数需要满足的条件常用函数n 7，And和Or And:与，且的意思。如果其参数都成立就返回True ，有一个不成立否则返
22、回False； Or:或者,其里面有一个参数成立,则返回TRUE,否则返回FALSE常用函数n 8，Len，Lower和upper Len：返回单元格中的字符个数常用函数n 8，Len，Lower和upper Lower：大写字母变小写常用函数n 8，Len，Lower和upper Upper：小写字母变大写常用函数n 9，Left、right、mid函数括号中数据格式：文本来源，从左端开始提取几个字符常用函数n 9，Left、right、mid函数括号中数据格式：文本来源，从右端开始提取几个字符常用函数n 9，Left、right、mid看一个right函数的升级用法常用函数n 9，Left
23、、right、mid看一个right函数的升级用法常用函数函数括号中数据格式：文本来源，从中间第几个字符开始，提取几个字符n 9，Left、right、mid常用函数n 10，Column；row常用函数n 10，Column；row常用函数n 10，Column；row常用函数n 10，Column；row Vlookup :查找引用函数n 11，Lookup和Vlookup函数括号中数据格式：条件值来源，查询区域，提取查询区域的第几列数据，精确匹配FALSE或0/模糊匹配TURE或者1常用函数 Vlookup :查找引用函数n 11，Lookup和Vlookup函数括号中数据格式：条件值来
24、源，查询区域，提取查询区域的第几列数据简单版本=VLOOKUP(K2,A:I,2)，K2是提取的条件，A:I表示提取的区域为A到I列，4表示提取区域第1列中为K2相同的对应的第4列值，再加上精确匹配0。常用函数 Vlookup :查找引用函数n 11，Lookup和Vlookup函数括号中数据格式：条件值来源，查询区域，提取查询区域的第几列数据简单版本=VLOOKUP(K2,A:I,2,0)，K2是提取的条件，A:I表示提取的区域为A到I列，4表示提取区域第1列中为K2相同的对应的第4列值，再加上精确匹配0。常用函数 Vlookup :查找引用函数n 11，Lookup和Vlookup方便快速
25、填充的升级版用=COLUMN()来求一下列号常用函数 Vlookup :查找引用函数n 11，Lookup和Vlookup方便快速填充的升级版=VLOOKUP($K185,$A$184:$I$212,COLUMN()-10,0)常用函数 Vlookup :查找引用函数n 11，Lookup和Vlookup方便快速填充的升级版=VLOOKUP($K185,$A$184:$I$212,COLUMN()-10,0)常用函数 Vlookup :查找引用函数n 12，Lookup和Vlookup为了方便快速向右填充，我们的K列会固定。=VLOOKUP($K185,$A$184:$I$212,COLUMN()-10,0)，并且使用了column函数实现提取列数的变量，但是为了方便向下填充，我们的行号不使用$符号固定。常用函数n 13，networkdays常用函数常用函数是返回起始日期和结束日期之间的工作日数n 14，networkdays.intl常用函数是返回两个日期之间的所有工作日数学习资源 1，Execel Home 微信，腾讯微博，新浪微博公众号 2，网易云课堂，腾讯课堂 3，公司买的你早该这样玩Excel系列书籍 4，练习题详见练习ExcelEnjoy the talks!Questions?
