《办公室office-EXCEL应用技巧大全.ppt》由会员分享，可在线阅读，更多相关《办公室office-EXCEL应用技巧大全.ppt（92页珍藏版）》请在三一办公上搜索。
1、2023/9/17,让工作更轻松,EXCEL应用及技巧,徐豪杰,商 用 销 售 部,前言,在进入正题之前，先轻松一下，听一个真实的故事：有一个老会计，一直都是手工做账、手工制表，有一天公司终于给财务部配上了电脑，老会计也学会了用Excel电子表格工具制表，用上Excel制表后，老会计感慨：“Excel电子表格真是好，以前制一张表格要划半天，划错了一个地方还很不好修改，现在方便多了”。,前言,有一天，他发现他的下属制表从来不去一一加减数字，老会计每次复核时用算盘敲半天，都没发现其计算错误，问其原因才发现Excel还有自动求和的功能，不禁惊叹：“啊？！Excel还有这个功能？！我每次都用算盘敲出结
2、果再填上去！”听完这个故事你是否会心一笑？你是否也曾惊呼：啊？！Excel还有这个功能？！,前言,Excel有太多的功能未曾为我们所认识，更未曾被我们使用，我们已学会使用的功能也许不到Excel功能的10%。在EXCEL中可以这么说,”只有想不到,没有做不到.”当然这样说是太绝对啦,但99%的事,只要你敢想,就可能完成.,前言,本次讲课，不是引导你系统地学习Excel，仅是只鳞片甲、浮光掠影地介绍一些常用的功能或非常实用的技巧。只要你每天学习一点点，就可以更快地掌握Excel的基本功能和学到不少非常实用的技巧。要想系统地学习Excel，本人认为最好的方法就是：没事就多看看Excel的联机帮助、
3、多作作练习题.,EXCEL操作中的12大不良习惯,1、报表文件名称、报表题目、表头不完整或语意不确切,2、报表布局、数据项目顺序不合理；钩稽关系不明确甚至错误；,3、数据缺失、漏填漏报、项目用字每次不能保持一致；,不良习惯,EXCEL操作中的12大不良习惯,4、为了美化格式或由于输入手误，字串中间、前后使用空格；,5、为求得格式好看，大量使用“合并居中”功能，结果可能是“以形害质”；,6、不论是文字数据还是数字数据一概“居中对齐”；,不良习惯,EXCEL操作中的12大不良习惯,7、文字和数字的字体、大小和格式不合适、不统一；,8、行列的宽、高不统一、不适合，或是过宽，或是过窄，以至数字不能正确
4、显示；,9、字体或行列使用多种颜色，且搭配失当、斑驳杂乱；,不良习惯,EXCEL操作中的12大不良习惯,10、时间项目不按照EXCEL系统的标准格式填写，且未能保持统一；,11、标点和符号全角、半角不加区分混合使用（如左右括号的输入；“-”、“”、“”不分）；,12、公式计算结果如为错误值符号，一概保留，不加以清除；使用链接公式后，不断开链接将其转换成数值,不良习惯,EXCEL 操作分类,基 础 操 作,函 数 操 作,图 表 操 作,VBA 编 程,基 础 操 作,基础,单元格格式,复制类,定 义,分类汇总,条件格式,数据透视表,排 序,选 项 应 用,数据有效性,高 级 筛 选,快捷键,真
5、正的Excel高手不只是擅长函数、数组和VBA，也擅长于键盘和鼠标操作，因为这是基本功。善用快捷键和鼠标不但可以大大提高操作效率，还可以唬到Excel菜鸟，菜鸟一见你运指如飞，鼠标狂点，三两下就完成了他要五六下才能搞定的作业，恨不得拜你为师，梦想着成为Excel高手。不用数组、VBA这等高精尖武器就可骗到一个徒弟，何乐而不为呢，哈哈0。学习Excel，就从如何最大限度地发挥键盘和鼠标的作用开始。,键盘高手使用快捷键,“懒人用鼠标，高手用键盘”,大凡高手从来不依赖鼠标,他们更偏爱键盘。在输入数据时尽量少用鼠标多用键盘快捷键将提高你的工作效率。,Ctrl+箭头键 移动到当前数据区域的边缘 Ctrl
6、+Home移动到工作表的开头Alt+=（等号）用 SUM 函数插入“自动求和”公式F4 或 Ctrl+Y 重复上一次操作,懒人懒到底使用鼠标,什么？“懒人用鼠标，高手用键盘”？让那些高手用键盘去吧，懒人的口号是：要懒就懒到底，能用鼠标的地方就不用键盘。但是，懒也要懒得专业，懒得有技术含量，懒就要懒出境界来。以下是使用鼠标的常用技能：,设定合适行高列宽如果某单元格未完全显示，双击该列列标题的右边界，可以设置为“最适合的列宽”。同理，双击某行行标题的下边界，可将此行设置为“最适合的行高”。,懒人懒到底使用鼠标,双剑合璧鼠标键盘组合,双剑合璧鼠标键盘组合,数据输入技巧,复制类操作,复制操作,CTRL
7、+C与CTRL+V,CTRL+R与CTRL+D,CTRL+回车,左键下拉与右键下拉,选择性粘贴,选择性粘贴,选择性粘贴的功能很强大,对于我们实际工作中的帮助也很大,主要功能除了数值与格式的复制外,还能进行对公式,超链接等的取消,格式的转换,数据的行列转换,跳行计算,原始行列计算等等,定位,定位就是Excel替你选定符合你所设定位条件的单元格。此功能的快捷捷为Ctrl+G，打开定位对话框后单击“定位条件”按钮可选择包含特殊字符或条目的对象、批注或单元格。这是一个很实用的功能，大家一般都不太用，即使是经常使用Excel的朋友也不常用。下面介绍一下它的一些常用选项：,定义名称,名称的定义是EXCEL
8、的一基础的技能，可是，如果你掌握了，它将给你带来非常实惠的妙处！建议使用简单易记的名称，不可使用类似A1的名称，因为它会和单元格的引用混淆。还有很多无效的名称，系统会自动提示你。引用位置：可以是工作表中的任意单元格，可以是公式，也可以是文本。,定义名称的妙处,定义名称的妙处,排序,超过三字段排序按行排序按颜色排序按特定顺序排序按笔划排序随机排序,高级筛选,大家对于自动筛选基本都很非常熟悉,而对于高级筛选可能就比较陌生或很少用,其实高级筛选对于数据的筛选和数据导出的功能很实用,可以完成较复杂的数据筛选,奇妙的自定义格式,通过单元格格式设置我们可以对单元格的格式进行改变，达到我们所希望的格式。数字
9、格式是单元格格式中最有用的功能之一，专门对单元格数值进行格式化。无论为单元格应用了何种数字格式，都只会改变单元格的显示形式，而不会改变单元存储的真正内容。反之，用户在工作表上看到的单元格内容，并不一定是其真正的内容，而可能是原始内容经过各种变化后的一种表现形式。如果我们需要在改变格式的同时也改变实际内容，需要借助函数TEXT等来实现。,条件格式,简单概述:利用EXCEL的条件格式功能,用户可以为自己的数据区域设置精美而又宜于阅读的格式.这种设置效果不同于单元格格式,它是完全动态的,无论在数据区域中增加或删除行,列,格式都会自动进行相应地调整,操持原有的风格.主要功能:自动标色,表格线.重要数据
10、的提醒,比较等,神奇的数据有效性,概述:我们常常会编制很多表格需要下面部门或其他人来填写，但报上来的表格总不能达到要求，即使写了很详细的说明，总是有这么一部分人填写的内容出现概念性错误，也许数据有效性就是一种很好控制方法，防止大部分错误，引导填写部门按照自己的要求来填写。功能：对单元格进行始初化，并对单元格的输入内容进行限制。简化输入过程等,分类汇总,主要功能按字段进行数据的汇总,也就是我们常说的小计与合计,对于分类汇总需要注意的是进行分类汇总前要对数据源先进行排序.EXCEL自带的组及分级显示可做为分类汇总的补充对自动分类汇总后的数据进行手工调整或增加删除数据.二者配合使用可以得到最佳的方案
11、或效果.,数据透视表,数据透视表是EXCEL功能为我们提供的现有最强大的数据汇总,分析工具,通过数据透视表我们可以轻松进行各种数据的统计分析,其附带的分析图对数据的对比更是一目了然,而且其计算速度非常的快,基本无延迟.对其操作不会对数据源造成影响.建议大家应能熟练应用,这对大家平时的工作会有很多的帮助.,控件基础知识,我们通过对控件(在EXCEL工作表中实际叫窗体)的操作,达到对公式或其它项目的更改,比如同个条件,不同标的项目进行显示等,选项的应用,函 数 操 作,普通公式,数组公式,易失性函数,分类日 期 函 数信 息 函 数逻 辑 函 数查 找 函 数统 计 函 数,分类文 本 函 数数
12、据 函 数工 程 函 数财 务 函 数数 学 函 数,函数分类,多条件汇总,函数基础知识,公式是对工作表中数值执行计算的等式,函数则是一些预先编写的,按特定顺序或结构执行计算的特殊公式.根据应用领域的不同,EXCEL函数一般可分为:逻辑,信息,日期,时间,数学与三角计算,统计查找与引用,数据库,文本,财务,工程等类别,此外还有EXCEL4.0宏表函数,扩展函数和外部函数等.,函数基础知识,许多朋友在碰到较复杂的函数公式,尤其是函数嵌套公式时,往往不知从何读起,从何理解,其实只要掌握了函数公式的结构等基本知识,就可以像”庖丁解牛”一样把公式进行分段解读(“F9键”公式分段计算键)函数公式是以”=
13、“号开始,后面依次是函数名称,左括号,以半角逗号分隔的参数和右括号=IF(A10,SUM(B1:G1),”),函数基础知识,函数的参数可以是数字,文本,逻辑值数组,错误值或单元格引用,指定的参数都必须为有效的参数.参数也可以是常量,公式或其他函数.有些函数不带参数,比如NOW(),TODAY(),RNGD()等.当函数的参数也是函数时,我们称之为函数的嵌套.=IF(A10,SUM(B1:G1),”),函数基础知识,理解技巧:在函数公式中,最左边的等号使表达式成为公式.括号用于包含参数,括号的配对让一个函数成为完整个体.参数以逗号来分隔,逗号的分隔让每个参数成为完整个体.逗号就像是函数公式的关节
14、.由此入手,就可以清楚地解读公式啦,当然使用F9理解公式是必不可少的.,=IF(ROUND(A11,2)0,无效数值,IF(ROUND(A11,2)=0,零,IF(ROUND(A11,2)1,TEXT(INT(ROUND(A11,2),dbnum2)&元)&IF(INT(ROUND(A11,2)*10)-INT(ROUND(A11,2)*10=0,IF(INT(ROUND(A11,2)*(INT(ROUND(A11,2)*100)-INT(ROUND(A11,2)*10)*10)=0,零),TEXT(INT(ROUND(A11,2)*10)-INT(ROUND(A11,2)*10,dbnum2
15、)&角)&IF(INT(ROUND(A11,2)*100)-INT(ROUND(A11,2)*10)*10)=0,整,TEXT(INT(ROUND(A11,2)*100)-INT(ROUND(A11,2)*10)*10),dbnum2)&分),来个复杂点函数,如何避免错误信息,在Excel中输入公式后，有时不能正确地计算出结果，并在单元格内显示一个错误信息，这些错误的产生，有的是因公式本身产生的，有的不是。下面就介绍一下几种常见的错误信息，并提出避免出错的办法。,错误值类型,错误值：,输入到单元格中的数据太长或单元格公式所产生的结果太大，使结果在单元格中显示不下。或是日期和时间格式的单元格做减
16、法，出现了负值。,增加列的宽度，使结果能够完全显示。如果是由日期或时间相减产生了负值引起的，可以改变单元格的格式，比如改为文本格式，结果为负的时间量,含义：,办法：,错误值：DIV/0!,试图除以0。这个错误的产生通常有下面几种情况：除数为0、在公式中除数使用了空单元格或是包含零值单元格的单元格引用,修改单元格引用，或者在用作除数的单元格中输入不为零的值,含义：,办法：,错误值：VALUE!,输入引用文本项的数学公式。如果使用了不正确的参数或运算符，或者当执行自动更正公式功能时不能更正公式，都将产生错误信息VALUE!。,这时应确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有
17、效的数值。例如，单元格C4中有一个数字或逻辑值，而单元格D4包含文本，则在计算公式=C4D4时，系统不能将文本转换为正确的数据类型，因而返回错误值VALUE!。,含义：,办法：,错误值：N/A,无信息可用于所要执行的计算。在建立模型时，用户可以在单元格中输入#N/A，以表明正在等待数据。任何引用含有#N/A值的单元格都将返回#N/A,在等待数据的单元格内填充上数据。,含义：,办法：,错误值：REF!,删除了被公式引用的单元格范围。,恢复被引用的单元格范围，或是重新设定引用范围。,含义：,办法：,错误值：NAME?,在公式中使用了Excel所不能识别的文本，比如可能是输错了名称，或是输入了一个已
18、删除的名称，如果没有将文字串括在双引号中，也会产生此错误值,如果是使用了不存在的名称而产生这类错误，应确认使用的名称确实存在；如果是名称，函数名拼写错误应就改正过来；将文字串括在双引号中；确认公式中使用的所有区域引用都使用了冒号（：）。例如：SUM（C1：C10）。注意将公式中的文本括在双引号中,含义：,办法：,错误值：NUM!,提供了无效的参数给工作表函数，或是公式的结果太大或太小而无法在工作表中表示。,确认函数中使用的参数类型正确。如果是公式结果太大或太小，就要修改公式，使其结果在-110307和110307之间。,含义：,办法：,错误值：NULL！,在公式中的两个范围之间插入一个空格以表
19、示交叉点，但这两个范围没有公共单元格。比如输入：“=SUM(A1:A10 C1:C10)”，就会产生这种情况。,解决办法：取消两个范围之间的空格。上式可改为“=SUM(A1:A10,C1:C10)”,含义：,办法：,函 数 操 作 _ 区 域 的 引 用,普通引用,普通引用,相对引用,相对引用,绝对引用,绝对引用,完全绝对引用,完全绝对引用,A1,$A1,$A$1,indirect,普通,完全绝对,绝对,相对,不同引用类型的区别,快速切换相对引用,绝对引用,混合引用,相对,绝对,混合引用大大方便了用户复制公式的操作,不必逐个输入公式,一般一个汇总表格或同一个要求下的公式我们只需编辑一个公式,通
20、过对引用的控制解决问题.但是,使用绝对引用 和混合引用时需要手工在相应位置输入美元符号,不太方便,事实上,EXCEL提供了快捷键F4,可在编辑栏对引用单元格的引用类型进行快速切换.如果你能很好的控制单元格的引用,那么你的EXCEL水平就会有很大的提升.,逻辑关系运算,在实际应用中,逻辑值可以直接参与函数公式计算,通常看到如以下实例的解法.例1:查找B1在A1:A10数据区域中是否存在的公式为:例2:统计A1:A10数据区域中大于零的数值个数的公式 为什么公式1中IF的条件数据不用”countif(A1:A10,B1)0”而公式2中为什么要在sum函数中使用”*1”呢?,=if(countif(
21、A1:A10,b1),”存在”,”),=sum(A1:A100)*1),逻辑关系运算(逻辑值与数值的关系),在EXCEL中,逻辑值只有TRUE和FALSE两个,它们与数值的关系为:这两条准则非常重要,在EXCEL函数公式的简化及计算中用途非常广泛.如果你能很好的处理逻辑值,那么你的EXCEL水平将有质的提升.,(1).在数值运算中,TRUE=1,FALSE=0,(1).在逻辑判断中,0=FALSE,所有非0数值=TRUE.,逻辑关系运算,为解释刚才2个例子疑惑,先来了解以下逻辑值之间的运算结果:,逻辑关系运算,了解了以上的运算后再来看公式1.由于COUNTIF函数统计结果只能为非负数(结果=0
22、),那么如果B1存在,COUNTIF函数结果必定0,否则结果=0,所以就不用在添加”0”进行判断.而对于公式2.如果SUM函数的参数是数组,而且这个数组是由逻辑值组成,那么要对这些逻辑值求和,就必须先进行运算(*1或+0),SUM才函数才能正确求和.,=if(countif(A1:A10,b1),”存在”,”),=sum(A1:A100)*1),养成良好的逻辑分析习惯,如果有两科以上不及格(60分为及格)均要补考如果只有一科不及格,而这一科如果又低于40分,要补考如果只有一科不及格,但该科达到40分,允许不补考为鼓励学生昼学好各科,如果只有一科不及格,且在40分以下,但若有4科在70分以上,也
23、不可以不补考.,根据学校的成绩规定来判断学生是否需要补考,函数操作_取交叉值,取数,Match(),Offset(),Hlookup(),Lookup(),Index(),Vlookup(),函 数 操 作 _ 条 件 汇 总,单条件汇总 SUMIF()函数,根据指定条件对若干单元格求和。语法SUMIF(range,criteria,sum_range)Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用 COUNTIF 函数。如果要让公式根据某一条件返回两个数值中的某一值（例如，根据指定销售额返回
24、销售红利），则可使用 IF 函数。,多条件汇总SUMPRODUCT()函数,在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。语法SUMPRODUCT(array1,array2,array3,.)Array1,array2,array3,.为 2 到 30 个数组，其相应元素需要进行相乘并求和。,数组公式,公式(多重计算)相当于A1*B1,A2*B2A5*B5,最后用SUM进行合计.在EXCEL帮助中关于数组公式的说明是:”对一组或多组值执行多重计算,并返回一个或多个结果,数组公式括于大括号中,按CTRL+SHIFT+ENTER可以输入数组公式.”,=SUM(A1:A5*B1*B5
25、),数组公式优缺点,缺点,可读性差。新手不易理解。许多情况下速度略慢参与操作的几个参数必须具有相同的尺寸，这可能导致一些并不需要计算的单元格也参与计算。特别是在多条件求和的数组公式中。,优点,简洁。一个公式就可以代替多行或多列的普通公式功能强大。可减小文件体积（但不一定减小内存占用）可以有一定的保护作用。多单元格数组公式必须选定全部公式所在单元格才能修改，一定程度上防止了新手误操作。,函数的多维立体应用,在很多情况下，我们常要跨表操作、汇总，比如我们按月建立12个月数据，常会放到12个工作表上，或者按型号建立N个工作表做为基础数据，但我们只希望在一个工作表上调用想要的分表数据或汇总N个分表的数
26、据，其实我们可以通过函数很方便的立体调用数据，想要哪个表数据就要哪个表的数据，函数Indirect()做为区域连接是必不可少.窗体做为控件也是一种很好的手段。,认识宏表函数,取得工作表名,GET.DOCUMENT(type_num,name_text)常用的参数有(1,76,88),GET.CELL(type_num,reference)常用的参数有(24,63等),GET.FORMULA(reference),EVALUATE(formula_text),取单元格属性值,取得表达式,算式及计算结果,认识宏表函数,函 数 操 作 _ 易失性函数,NOW,COUNTIF,RAND,OFFSET,
27、易失性函数,使用这些函数后，会引发工作表的重新计算,INDIRECT,易失性函数优缺点,优点:功能强大，有些复杂的计算不得不用易失性函数,利用易失性函数的易失性做为某些特殊函数的辅助参数,易失性函数,缺点:计算复杂，如果使用过多当有单元格的数据改变时，需要重新计算，有时等待时间很长。,使用“高效函数”,刚才我们介绍过易失性函数，如果工作簿中过多的使用这类函数，则一旦用户更新任何一个单元格，这些函数都将重新计算，从而会降低整个工作簿的性能。COUNTIF函数做为条件统计中使用非常广泛，但如果在工作表大量使用该函数，每当对工作表进行操作时，系统都会自动重新计算，这也凸显了这个函数的缺点。因此，在编
28、写公式时必须注意，昼选择一些“高效率”函数来进行编写。,浮点运算,困惑,实例,结论,解决,让我们一起进入EXCEL看看1+1是否真的不等于2,可以用ROUND等函数进行调整,在EXCEL中运算中1+1并不是完全等于2,什么我会这么说呢,在EXCEL的加减运算中结果是等于或非常接近于正确值,图表前言,一图胜千言,一个设计严谨,制作精美的图表,能够使报告中枯燥的数字变得感性和直观,从而更有说服力.EXCEL在此方面的功能非常强大,利用它可以快速创建常见的多种图表,通过适当的设置与DIY可以让您创建出觉得不可能完成的图表.,图表操作,如何做出高难度的漂亮图表呢？,DIY你的图表,通用技巧,精 典 图
29、,其 它 图,动 态 图,图表通用技巧(去除分类轴上的空白日期),去除分类轴上的空白日期在EXCEL图表中使用日期系统列作为分类轴的标志时，如果日期系列是不连续的，但是由于图表的分类 依然按连续的日期显示，会导致在柱形图中形成没有柱体的“缺口”,图表通用技巧(隐藏接近于零的数据标签),在制作饼图时，经常因为数据百分比相差悬殊，或某个数据接近于0，而不能显示其相应的色块，只显示一个“0%”的数据标志。当然，可以直接删除这个“0%”数据标志，但是数据变更时，所有数据标志会重新生成，有没有一劳永逸的办法呢？有，可以通过自定义数据格式的方法来隐藏这样的数据标志,图表通用技巧(在图表中处理负值),上一技
30、巧利用自定义数据格式的方法隐藏了接近于0的数据标志，本技巧将继续用自定义数字格式的方法，用不同的颜色来显示不同区间的数据标志。比如要把数据标志设为：负值为红色，大于100的数为黄色，其它的数据不变为默认色。,图表通用技巧(快速设置图表字体),在实际工作中，经过多次设置，图表上不同区域的字体大小与颜色会变得比较杂乱，如下图所示，标题，绘图区，轴标志与图例等处的字体各不相同，影响了美观。在这种情况下，如果希望各区域的字体、颜色统一，我们不必逐一设置，可以一次进行调整.,图表通用技巧(在图表中使用对数刻度),在制作图时，如果数值轴中各个数据点的值相差悬殊，者往往会使值较小的数据无法在图表中清楚地显示
31、出来，如下图所示。妥善解决这种问题的方法是使用对数刻度,图表通用技巧(利用错误值不显示数据),日常做图中我们的数据大多情况下都是动态的，比如一年的数据，我们在制表中的时候就把整个年度的数据都事先做进了图表里，但后几个月的数据还没有，公式得的值都是0，这时会在图表中会形成重叠于X轴的数据线，非常的不好看，解决的方法我们可以利用图表对错误值“#N/A”不处理的特点来使其不显示数据线。,图表通用技巧(妙用误差线),误差线在图表里是一个非常有用的一个辅助设置，通过对误差线的控制，我们可以虚拟各种特殊需要的线条，最重要的，我们常用误差线来设置虚拟坐标轴，这样我们就可以在一个图表上表达不同指标的数据,图表
32、通用技巧(复合饼图),精典图表,介绍了以上制表的一些小技巧，现在让我们再来介绍几个精典图表的制作方法，从中可以学到更多的制表 的方法,精典图表柏拉图,精典图表甘特图,精典图表双层饼图,精典图表步进图,精典图表波士顿矩阵图,波士顿矩阵图也叫成长份额图，它以矩阵图的形式，将企业的所有产品业务标出来，其中纵坐标为市场成长率，横坐标为各产品的相对市场份额。,精典图表盈亏平衡分析图,盈亏平衡分析也称量本利分析或保本分析，它是对成本，销售收入和销售数量三者之间相互关系的综合分析，来掌握盈亏变化的规律，指导企业选择能够以最小的成本生产出最多产品并可以使企业获得最大利润的经营方案,精典图表比较柱型图,柱型图是EXCEL中最常用的图表之一，但是它无法直观地反映具体的差异值，如果希望从柱形图中直观地查看同一类别下不同项目的差异值，就可以用比较柱型图来很好的解决这个问题,精典图表直方图与正态分布图,直方图（Historgram)是将某期间所收集的计量值数据（如尺寸，重量等）经分组整理成次数的统计表，并使用柱型予以图形化的图表，用以掌握这些数据所代表的情报。正态分布是在统计中使用最频繁的分布，正态分布图描述收集的数据在分组区间内出现的概率。,EXCEL还能为我们做什么?,思考,2023/9/17,让工作更轻松,联系电话:662481,Thank You!,SKYPE:Auxxhj2481,
