Excel常用办公技巧.pptx

EXCEL“满载而归”excel工作常用技巧1235目 录CONTENTS常用快捷键数据格式及转换常用公式举例常用工具6学习交流4数据透视表浅析一、常用快捷键第一部分常用快捷键电脑操作：Win+D显示桌面Win+E打开文件资源管理器Alt+Tab切换任务视图Ctrl+Alt+Delete打开任务管理器Ctrl+F/H打开“查找”/“替换”对话框Ctrl+G打开“定位”对话框Alt+D再+P打开“数据透视表向导”对话框工作簿操作：Ctrl+S保存工作簿Ctrl+Z撤销上一步操作Ctrl+Y恢复撤销的操作CtrL+拖动工作表标签复制工作表的副本移动和选择：双击单元格边框移动到工作表中当前数据区域的边缘Shift+双击单元格边框 选取连续的单元格区域Shift+单击选择相邻区域Ctrl+单击选择不相邻的区域Alt+；（分号）只选取显示的行，不选取隐藏行单元格操作：Shift+拖动单元格互换单元格内容Alt+向下箭头根据已输入过的内容自动生成下拉列表F4公式中循环切换绝对和相对引用F9查看公式中局部表达式的结果单元格格式：Ctrl+Shift+1设置为数值格式Ctrl+Shift+3设置为日期格式Ctrl+Shift+5设置为百分比格式批量填充：Ctrl+Enter向选定的单元格区域填充Ctrl+D向下复制填充Ctrl+R向右复制填充Ctrl+E快速智能填充Alt+=用SUM函数插入“自动求和”公式二、数据格式及转换第二部分EXCEL数据类型数据类型：1.EXCEL中数值文本逻辑值，2.逻辑值可参与计算：FALSE=0 TRUE=13.日期和时间是特殊的数值，数值格式可随意转换数字格式类型显示效果短日期格式2018/6/9长日期格式2018年6月9日常规格式43260数值格式43260.00 货币格式43,260.00 百分比4326000.00%文本格式43260日期和时间格式常规格式2018/6/94326011:170.470138889数字和文本的区别：1.在默认状态下输入数据，文本自动靠左对齐，数字靠右对齐2.数字可以进行数学运算，文本不能数字格式可随意转换显示：文本转化为数值：1.单列数据：数据“、”分列“、F2.多列数据：复制空白单元格，选定需转换的数据区域，右键，选择性粘贴（S），加（D）自定义数字格式及常见应用效果预览更多数字格式内置类型调配数字格式设置单元格格式仅改变数字显示形式不改变数字本身自定义数字格式及常见应用1.让日期变得整整齐齐自定义数字格式yyyy-mm-dd数字格式显示效果yyyy/mm/dd2017/05/20yyyy-mm-dd2017-05-20dd-mm-yy20-05-20yyyy年m月d日2017年5月20日aaaa星期六aaa周六d-mmm20-May2.数字单位不用输入，直接“变”出来直接输入提至标题数字格式显示0.00”元”自定义格式中文本字符要用英文双引号括起来自定义数字格式及常见应用3.自动补齐编号前面的0（仅改变显示形式）实际输入显示形式非实际变化0000自定义数字格式类型4.手机号码自动分段000-0000-0000自定义数字格式类型5.巧用数字格式规则标示数值升降蓝色0.00;红色-0.00 自定义数字格式类型自定义数字格式的原理核心规则一：四类数据分别设置格式分号按顺序划分了4种类型的数据，每一格式只对相应的类型起作用正值;负值;零值;文本 蓝色0.00;绿色-0.00;黑色-;红色核心规则二：占位符0和#的区别0：代表强制显示，不管前面的数值还是0；例：代码：“00000”。1234567显示为1234567；123显示为00123 例：代码：“00.000”。100.14显示为100.140；1.1显示为01.100#：只显示有效数字而不显示无意义的零。例：代码：“#.#”。12.1显示为12.1;12.1263显示为：12.13例：代码：“#，#”。“10000”显示为“10，000”核心规则三：文本型字符加英文双引号例：代码：“0.00”元”。“10”显示为“10.00元”核心规则四：附加条件用英文中括号例：红色100例：绿色=60及格例：=1男;=0女核心原理01020304三、常用公式举例第三部分（一）、数据提取1.TEXT作用：将数值转换为按指定数字格式表示的文本语法：=TEXT(需转换的值或单元格，文本形式的数字格式)单元格格式数字值说明Format_textValueTEXT(A,B)0000-00-00198205061982-05-06按所示形式表示日期0000年00月00日1982年05月06日aaaa2014/3/1星期六显示为中文星期几全称000.010.25010.3小数点前面不够三位以0补齐，保留1位小数，不足一位以0补齐#10.0010没用的0一律不显示00.#1.25301.25小数点前不足两位以0补齐，保留两位小数，不足两位不补位=90优秀；=60及格；不及格90优秀大于等于90，显示为“优秀”60及格大于等于60，小于90，显示为“及格”59不及格小于60，显示为“不及格”#!.0,万元1.3万元以万元为单位，保留1位小数（一）、数据提取2.MID 作用：从一个字符串中截取出指定数量的字符语法：=MID(要被截取的字符,从左起第几位开 始截取，要向右截取的长度)3.LEN/LENB作用：返回文本字符串中的字符/字节数语法：=Len(要查找其长度的文本)注：空格将作为字符进行计数补齐工号前面的0（实际改变）语法：=TEXT（单元格，“00000000”）（二）、时间计算1.DATEDIF作用：返回两个日期之间的年月日间隔数语法：=DATEDIF(开始日期，结束日期，返回类型）2.EOMONTH 作用：返回某个月份最后一天的序列号语法：=EOMONTH(开始日期,之前或之后的月份数)月份数为正值将生成未来日期；为负值将生成过去日期。3.NETWORKDAYS 作用：返回两参数之间完整的工作日数值 （不包括周末和专门指定的假期）语法：=NETWORKDAYS(开始日期，结束日期，可选假日区域）返回类型 说明Y 整年数M 整月数D 天数YD 同年间隔天数，忽略年份YM 同年间隔月数，忽略年份MD 同月间隔天数，忽略年份和月份（三）、查找1.VLOOKUP/HLOOKUP作用：按列/行查找语法：=VLOOKUP(要查找的唯一值，要查找的区域，数据在查找区域的第几列数，精确匹配/近似匹配)2.VLOOKUP+COLUMN 作用：用column代替vlookup第3个参数，使公式可向右拖动【COLUMN】查看所选择的某一个单元格在第几列如COLUMN(D3)查看第3行D列的单元格在第几列，结果为43.VLOOKUP+IF1,0作用：逆向查找语法：=VLOOKUP(要查找的值,IF(1,0,查找的区域首列,查找的区域末列),2,0)=VLOOKUP($G3,$A$2:$B$6,COLUMN(B1),0)（三）、查找4.LOOKUP正向/逆向查找语法：=LOOKUP(1,0/(条件区域=条件),结果区域)语法：=LOOKUP(1,0/(条件区域1=条件1)*(条件区域2=条件2),结果区域)5.LOOKUP多条件查找（四）、求和1.SUMIF语法：=SUMIF(条件区域,指定的条件,求和区域)3.SUMIF指定条件求和快捷键功能大于某值SUMIF(条件区域,某数值)小于平均数 SUMIF(条件区域,&AVERAGE()模糊文本SUMIF(条件区域,*文本*,求和区域)忽略错误值 SUMIF(条件区域,某数值)大于某单元格COUNTIF(条件区域,&单元格)身份证重复COUNTIF(条件区域,单元格&*)某列不重复SUMPRODUCT(1/COUNTIF(条件区域,条件区域)2.COUNTIFS语法：=COUNTIFS(统计区域1,指定的条件1，统计区域2,指定的条件2)（六）、序号填写1.Ctrl+单元格右下角的填充柄向下拖动2.ROW函数作用：返回所选择的某一个单元格的行数。如果省略单元格参数，则默认返回ROW函数所在单元格的行数。3.按部门单独填写序号COUNTIF(B$2:B2,B2)4.筛选后保持连续的序号SUBTOTAL(3,B$1:B2)-1第四部分四、数据透视表浅析1.数据透视表基本操作创建数据透视表字段列表和汇总方法字段列表数据源中的所有列标题分类区域(筛选、行区域、列区域)要作为分类标签，就将字段拖入此三个区域统计区域(值区域)添加至此的字段，才会进行计算和统计2.调整数据表的布局和美化-快速改变布局结构数据透视表工具 设计选项卡数据透视表工具 分析选项卡调整布局和外观相关的功能大多在设计选项卡；调整汇总选项和数据的功能大多在分析选项卡。显示所有分类汇总在组的底部显示所有分类汇总分离二级分类单独放在一列中以表格形式显示底部显示汇总行的效果分离二级分类后的效果2.调整数据表的布局和美化-快速调整报表的外观设置字段的数字格式以“元”为单位以“万元”为单位透视表中的数据过大，以万为单位显示数值会更加简洁.l选中任一数值；l单击【字段设置】按钮；l打开【数字格式】窗口；l切换至自定义；l输入代码。0!.0,英文逗号为千位分隔符，叹号为转义字符。代码的含义是，用转义字符强制将小数点显示在千位之前，千位分割符后面的数值不显示。结果就是以万为单位显示。修改字段名称和标签透视表中的文字标签、字段名称均可以直接修改。例如将分类汇总标签修改成小计。直接输入一个【小计】，按Enter键就能将所有同一级别的汇总标签全部修改好修改文字标签无法双击进入标记状态，但可以直接输入覆盖，或者在编辑栏修改。Enter2.调整数据表的布局和美化-技巧1.多级分类，怎样只保留第一级的分类汇总行2.透视表如何调出普通表格的效果右键单击制定的级别标签，就可以取消/显示该级别汇总l取消分类汇总；l以表格形式显示；l重复所有项目标签；l取消行标题样式；l取消折叠按钮。3.同类标签如何合并居中重复显示的标签选项中有更多关于布局、格式及显示效果的属性配置合并且居中排列带标签的单元格合并且居中的标签3.透视表技巧演示1、筛选筛选器切片器2、如何查看某个汇总数据背后的明细数据？3、修改数据源中的数据，怎样使汇总结果同步更新？右键单击数据透视表区域任一位置，选择刷新4、数据源增添记录，如何添加到数据透视表的汇总结果中？更改数据源转智能报表，变成动态数据源5、每次刷新，列宽都变回默认状态，怎样固定？打开透视表设置窗口取消勾选更新时自动调整列宽6、利用数据透视表进行报表拆分。将分类添加到筛选页点击双击汇总数据单元格故对外提供保密数据时，务必注意将透视表粘贴为数值4.计算分析-不重计数&多种汇总方式如何查看表中有多少个人物出场，并且统计各个人物的出场次数？原理：不重复项其实就是分类项目；值区域会根据拖入的数据类型自动选择汇总方式。数据类型 默认汇总方式含义文本计数非空单元格的数量数值求和所有数值之和不重复计数一个字段变出多种汇总方式透视表中的值标签，双击无法进入编辑状态，因为双击标签名称后，会直接打开值字段设置窗口，在该窗口中，可以切换计算类型，从而修改表汇总的方式。2个要点：同一个字段可以多次拖入至区域；双击字段名可以快速切换汇总方式修改标签和显示单位以后的结果5.计算分析-百分比计算占总数的百分比打开值字段设置窗口；切换至值显示方式；选择总计的百分比类型；确定。环比增长率7月环比增长率算式因为环比增长率和同比增长率都属于差额计算，所以在百分比选项中，应选择差异百分比，对于比例中的环比增长：基本字段：月基本项：上一个基本字段和基本项的含义是以“月”为分类，计算当前数据和上一个“月”数据的差异百分比。正好和环比增长率吻合。6.日期分组、数值分段统计事实上，只要日期、时间列中的数据符合系统标准格式，在将标准日期拖入分类字段区域时，就会立即分组。按季度、年、月等周期等自动分组日期自动分组周期（跨度）可以自定义吗？可以，只需要修改【分组选择】中的选项就行。系统日期格式只有年、月、日、时、分、秒的分组方式，如果需要用到按周分组，则需要自定义分组天数。首先选中任一个时间字段，单击分组选择，打开字段组合窗口。选择一个日期标签点击分组选择然后在组合窗口中执行以下操作：取消“步长”一栏中的月、季度、年的默认分组；单击选中日分组方式，输入7天；单击确定，便按7天一组重新分组，即以【周】分组。单击取消各个默认分组单击选择日分组，自定义天数6.日期分组、数值分段统计和日期自定义分组方法类似，数值同样可以设定区间步长，实现自动分段统计。以右表为例，按照以下分数区间，自动统计各个分数段的学生人数。按数值区间自动分组统计分段要求：60为一组6099以10分为1组100单独一组步骤一：统计每个成绩的人数按照统计需求描述和字段需求分析、分别将成绩、姓名字段拖入行和值区域，先得到按成绩分类的人数统计结果。步骤二：配置分组区间和步长选中任一个行标签，例如77；在透视表工具栏中单击分组选择；填写组合属性如下：起始于：60 终止于：99 步 长：10第五部分五、常用工具（一）、下拉列表制作1.一级下拉列表数据，数据验证，允许：序列，来源：菜单区域2.下拉列表联动a:命名对应关系：公式，名称管理器，新建b:设置一级下拉菜单c:设置二级下拉菜单，来源：使用公式=indirect（前一个单元格）（二）、批量合并工作簿批量合并工作簿（表头字段相同）数据选项卡获取数据自文件从工作簿/文件夹组合合并和加载到选择需合并的sheet页名称Office Excel2016及以上版本可用不同版本的按钮位置有细微差别（三）、身份证号、银行卡号校验1.身份证号校验=IF(身份证号单元格=,(IF(MID(10X98765432,MOD(SUMPRODUCT(MID(身份证号单元格,ROW(INDIRECT(1:17),1)*2(18-ROW(INDIRECT(1:17),11)+1,1)=MID(身份证号单元格,18,18),正确,错误)2.银行卡号校验校验正误：=IF(银行卡号单元格=,IF(MOD(SUMPRODUCT(-(0&MID(0&MID(银行卡号单元格,ROW($1:$24),1)*2MOD(ROW($1:$24)+MOD(LEN(银行卡号单元格),2),2),1,2,1),10),错误,正确)所属银行：=VLOOKUP(LEFT(银行卡号单元格),IF(1,0,LEFT(Bin列表!A:A,6),Bin列表!B:B),2,)感谢聆听 期待分享