走进EXCEL办公常用函数

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,走进,EXCEL,办公常用函数,主讲：邹波,主要内容,一、消除学习函数的心理障碍,二、学习,EXCEL,函数需掌握的基本知识,三、使用函数进行逻辑判断,四、用函数统计和处理数据,五、用函数查找数据,六、用函数处理文本,一、消除学习函数的心理障碍,1,、走出误区,1.1,英文差学不好函数,例如：,IF,函数，在上图,3,个参数序列中，完全可以理解为第一个参数、第二个参数、第三个参数，或者根据该函数的含义理解为,“,条件,”,、,“,是则结果,”,、,“,否则结果,”,。,一、消除学习函数的心理障碍,1,、走出误区,1.2,注重排版，忽略制表和计算难度,一、消除学习函数的心理障碍,1,、走出误区,1.3,会背的函数越多越好,EXCEL,一共有,400,多个函数，就办公使用而言也就掌握常用的一些函数，迄今为止，还未听说过有人号称,400,多个函数都会使用。我们通常熟悉一些常用的函数，已经可以处理绝大部分日常工作。例如：,IF,、,SUM,、,COUNT,、,SUMIF,、,VLOOKUP,、,CONCATENATE,（,&,）、,AVERAGE,、,VALUE,、,TEXT,等等,1.4,不使用辅助单元格,辅助单元格指在表外设置一些单元格来作为过渡计算，对最终的结果起到帮助作用，可以避免在一个单元内设置非常复杂的函数进行计算，甚至无法使用函数组合得到想要的结果。,一、消除学习函数的心理障碍,2,、使用函数优点,2.1,统计迅速,例如：在计算各种订单各产品需要生产的数量时，如果项数非常多，上千甚至上万个数据的时候，靠传统的作法一个一个用计算器计算，即使是想求和出来总数所花费的时间可想而知，利用函数进行求和计算可能几秒钟时间就可以完成。,2.2,杜绝错误,假如我们工作中已经用,EXCEL,做好了一份清单，但是要把数量汇总，使用计算器难免输入错误，利用函数可以不用在改变原始数据的情况下进行计算避免出现重新录入错误的情况，但是值得注意的是，编辑公式时需要非常小心，避免要么不错，要么全错的情况发生。,2.3,修改联动,修改联动是,EXCEL,非常好的优点，利用这一个特性可以事先编辑好各种,EXCEL,的试样模版，使后续操作时只需要输入基础数据实现自动计算。,二、学习,EXCEL,函数需掌握的基本知识,1,、,EXCEL,中的数字格式,1.1,数值类的数字格式,数值类的数字格式，是指依然被,EXCEL,承认为,“,数,”,的一类格式，具备,“,大小,”,属性，包含除,“,文本,”,与,“,自定义,”,之外的,10,类格式。包含：常规格式、数值格式、货币格式、会计专用格式、日期格式、时间格式、百分比格式、科学计数格式、分数格式、特殊格式。,1.2,文本数字格式,文本数字格式是指用数字格式中的,“,文本,”,格式，它只用来表示,“,编码,”,的数字格式，不会被,EXCEL,承认为,“,数,”,，没有,“,大小,”,属性，因此也无法用于数据计算。,1.3,自定义数字格式,这是在单元格数字格式中最特殊的数字格式，前,11,种格式实际上都是通过自定义格式中的代码编写得到的。只是前,11,种格式在工作比较常用，所以将其进行了分类，工作中如果需要将单元格格式为设置前,11,种之外的格式可采用自定义的方式。,二、学习,EXCEL,函数需掌握的基本知识,1,、,EXCEL,中的数字格式（以,“,10000,”,举例）,二、学习,EXCEL,函数需掌握的基本知识,2,、不同类型间数字的转化,由于不同格式的特性，导致在处理这类数据时会出现很多问题，例如：不能使用,SUM,对文本数字求和、格式不匹配时无法用查找类函数查找到正确结果等，也不能通过修改单元格格式的方式，实现数值类数据与文本格式数据间的转化。在函数使用中遇到此类问题，可以通过下面的方法解决,2.1,数值类的数字转化为文本类数字,2.2,文本类的数字转化为数值类的数字,二、学习,EXCEL,函数需掌握的基本知识,3,、单元格的引用方式,3.1,单元格相对引用,相对引用指针对单元格相对位置引用，这种引用方式一旦被拖曳复制，相对引用的地址也将根据当前所在单元格的位置进行自动调整。例如：在,I1,单元格输入,=SUM(F2:F6),，在向右拖曳复制时将更改为,=SUM(G2:G6),。,3.2,单元格绝对引用,绝对引用指针对单元格绝对位置的引用，写法为在需要绝对引用的行号或者列好前加,“,$,”,符号，在进行各方向的拖曳复制时保持单元格引用不变。例如在,I2,单元格输入,=SUM($F2:$F6),，向右拖曳复制时单元格的引用位置不会发生变化，仍然为,=SUM($F2:$F6),。,3.3,单元格混合引用,混合引用指既有单元格绝对引用又有单元格的相对引用，可以根据自身需要选择哪些单元格绝对引用哪些相对引用。,3.4,跨工作表与跨工作薄引用,EXCEL,运算不仅仅支持同工作表内数据引用的计算，在不同工作表甚至工作薄中都可以进行引用计算。例如：,=SUM(sheet1!A1:A20),为跨工作表引用，,=SUM(,表,1.XLSsheet1!A1:A20),为跨工作薄引用,跨工作表引用举例,.,xlsx,三、使用函数进行逻辑判断,常用的逻辑函数有,AND,、,OR,、,NOT,、,IF,1,、,AND,函数,AND,函数表述将多个条件结合在一起进行判断，当所有条件都满足时反馈,“,TRUE,”,，其中只要有一个条件不满足反馈,“,FALSE,”,。在计算时,“,TRUE,”,被当做,“,1,”,计算，,“,FALSE,”,被当做,“,0,”,计算。如下图：,2,、,OR,函数,OR,函数也是将多个条件组合在一起进行判断，与,AND,不同的是只要有一个或多个条件满足都反馈,“,TRUE,”,，只有当所有条件都不满足时才反馈,“,FALSE,”,。如下图：,三、使用函数进行逻辑判断,常用的逻辑函数有,AND,、,OR,、,IF,3,、,IF,函数,此函数是逻辑函数的核心函数，它大大提升了得到逻辑判断值之后的可用性，并且判断当条件满足时反馈何种处理方式，不满足时反馈何种处理方式。,其语法可以描述成,“,如果怎么样，就怎么样，否则怎么样,”,，其结构为,IF(,条件判断，满足条件如何处理，不满足条件如何处理）,装配跟踪表,.,xlsx,四、用函数统计和处理数据,1,、,SUMIF,函数,1.1,函数语法,用来对满足条件的单元格求和，一般情况下有三个参数如：,SUMIF(,条件区域、条件、求和区域,),。,1.2,扩展应用,SUMIF,除了使用固定的数字或文本外，还可以有所变化，这种变化通过使用比较符号和通配符来体现。比较符号指大于,”,”,小于,”,“,等于,”,=,“,之类的符号,。通配符有两个其一是,”,*,“,，其代表,0,个或者多个字符，其二是,”,？,“,其代表一个字符，要注意的是这两个符号在,EXCEL,中都必须是英文半角符号。通配符应用举例,:,四、用函数统计和处理数据,1.2,扩展应用,比较符号应用举例,：统计单价大于,35,元,的总金额,四、用函数统计和处理数据,2,、,COUNTIF,函数,此函数用来对满足条件的单元格计数，其结构为,COUNTIF(,条件区域、条件,),COUNTIF,和,SUMIF,很相似，所以在条件区域和条件设置上可以与,SUMIF,设置相同，他们的区别在意,SUM IF,用于条件求和，,COUNTIF,只用于计数，其参数也不一样。,COUNTIF,应用举例,COUNTIF,使用,.,xlsx,3,、其他统计和处理数据的函数,3.1 SUM,有条件求和：,公式结构,SUM(,条件区域,1=,条件,1),*,(,条件区域,2=,条件,2),*,(,条件区域,n=,条件,n),*求和区域,),3.2 SUM,有条件计数：,公式结构,SUM(,条件区域,1=,条件,1),*,(,条件区域,2=,条件,2),*,(,条件区域,n=,条件,n),3.3 AVERAGEIF,此函数与,SUMIF,语法完全相同，用法一样，只是结果一个是求和一个求平均值,五、用函数查找数据,日常在数据汇总中，经常会遇到根据产品编码找产品名称，规格，数量，库存等等数据，使用查找函数就能迅速处理这些数据,1,、,VLOOKUP,函数,1.1,函数语法,VLOOKUP(,查找值、数据区域、返回列数、逻辑值,),查找值：指需要在数据区域的第一列中查找的数值，可以为数值、文本、引用或者是公式计算出的结果。查找值只有在数据区域第一列中存在时，公式才能反馈正确的值，如果数据在第一列中不存在，则会返回错误值,“,#N/A,”,。,数据区域：指包括查找值和返回值在内的区域。,返回列数：在数据区域的第一列找到查找值之后，返回查找值对应的那一列行第几列的单元格内容。,逻辑值：指函数返回时是精确匹配还是近似匹配。当逻辑值为,“,TRUE,”,或者,“,1,”,时，才能查出小于查找值得最大值。此操作必须将查找值所在列按照升序排列才可以找到，这种查找方式为,“,模糊查找,”,。当逻辑值为,“,FALSE,”,或者,“,0,”,时，为精确查找，数据区域第一列也不必排序，只有当数据区域第一列有需要的查找值时才能返回正确数值，否则返回,“,#N/A,”,。,例如：,五、用函数查找数据,1.2,使用,VLOOKUP,函数容易出现的错误,1.2.1,格式不统一导致错误,使用,VLOOKUP,函数查找时必须保持查找的数值格式上统一，数值格式的数据与文本格式的数据是不能对应的，所以查找后反馈的数值是错误值,“,#N/A,”,。只有把格式调整为统一格式方可进行查找。将文本格式转化为数值格式可通过在查找值前加,“,-,”,。例如,“,=VLLOKUP(-D2,A:E,5,0),”,1.2.2,多余字符导致错误,在很多数据库软件中到处的数值，往往在数值后面都隐藏着看不见的字符，这些字符往往是因为,“,空格,”,引起，在,VLOOKUP,计算时也会当做需要查找的字符，从而导致结果错误。处理方式：,a,、可以先用,“,查找和替换,”,的命令将空格去除之后再进行查找。,b,、可采用数据提取方式和逻辑判断方式去除。,=IF(RIGHT(D21,1)=,“,“,LEFT(D21,LEN(D21)-1),D21),。,1.2.3,数据区域中确实没有查找值,这种情况在很多查找结果中都存在，出现错误值,“,#N/A,”,，我们有时候又不想看到这个错误值,“,#N/A,”,，此时可以用以下方式进行转换。,=IF(COUNTIF(D:D,A1)=0,”,VLOOKUP(A1,D:F,3,0),，也可以写成,=IF(COUNTIF(D:D,A1),VLOOKUP(A1,D:F,3,0),”,),。,五、用函数查找数据,1.3,使用,VLOOKUP,进行反向查找,反向查找指需要返回的数值在查找值的左边，即从右向左查找。我们通常使用,VLOOKUP,都是从左到右的查找方式，反向查找时经常需要把返回值列剪切到查找值的右边才行，实际上,VLOOKUP,具有反向查找功能。例如：,=VLOOKUP(H3,IF(1,0,B:B,A:A),2,0),或者,=VLOOKUP(H4,CHOOSE(1,2,B:B,A:A),2,0),这两个公式中,IF(1,0,B:B,A:A),和,CHOOSE(1,2,B:B,A:A),其目的是一样的，利用公式中数组,1,0,和,1,2,重新构建成查找的数据区域，可以理解为按照公式给出的顺序去构建数据区域，其区域为,B:A,。一般的操作方式是得不到反向数据区域的，无论我们用鼠标是从左到右选择还是从右到左选择，得到的数据区域都是按照,ABCD,这样的顺序排列的。因此也就不能形成反向查找功能。说明：,1,0,表示一个数组中有两个元素，一个是,1,，另一个是,0,。,1,在条件判断中表示,TRUE,，,0,表示,FALSE,。在,“,IF(1,0,”,中，根据两个条件，