走进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.XLS]sheet1!A1:A20)为跨工作薄引用跨工作表引用举例.xlsx三、使用函数进行逻辑判断常用的逻辑函数有AND、OR、NOT、IF1、AND函数AND函数表述将多个条件结合在一起进行判断，当所有条件都满足时反馈“TRUE”，其中只要有一个条件不满足反馈“FALSE”。在计算时“TRUE”被当做“1”计算，“FALSE”被当做“0”计算。如下图：2、OR函数OR函数也是将多个条件组合在一起进行判断，与AND不同的是只要有一个或多个条件满足都反馈“TRUE”，只有当所有条件都不满足时才反馈“FALSE”。如下图：三、使用函数进行逻辑判断常用的逻辑函数有AND、OR、IF3、IF函数此函数是逻辑函数的核心函数，它大大提升了得到逻辑判断值之后的可用性，并且判断当条件满足时反馈何种处理方式，不满足时反馈何种处理方式。其语法可以描述成“如果怎么样，就怎么样，否则怎么样”，其结构为IF(条件判断，满足条件如何处理，不满足条件如何处理）装配跟踪表.xlsx四、用函数统计和处理数据1、SUMIF函数1.1函数语法用来对满足条件的单元格求和，一般情况下有三个参数如：SUMIF(条件区域、条件、求和区域)。1.2扩展应用SUMIF除了使用固定的数字或文本外，还可以有所变化，这种变化通过使用比较符号和通配符来体现。比较符号指大于””,小于”“,等于”=“,之类的符号。通配符有两个其一是”*“，其代表0个或者多个字符，其二是”？“其代表一个字符，要注意的是这两个符号在EXCEL中都必须是英文半角符号。通配符应用举例:四、用函数统计和处理数据1.2扩展应用比较符号应用举例：统计单价大于35元的总金额四、用函数统计和处理数据2、COUNTIF函数此函数用来对满足条件的单元格计数，其结构为COUNTIF(条件区域、条件),COUNTIF和SUMIF很相似，所以在条件区域和条件设置上可以与SUMIF设置相同，他们的区别在意SUMIF用于条件求和，COUNTIF只用于计数，其参数也不一样。COUNTIF应用举例COUNTIF使用.xlsx3、其他统计和处理数据的函数3.1SUM有条件求和：公式结构SUM((条件区域1=条件1)*(条件区域2=条件2)……*(条件区域n=条件n)*求和区域)3.2SUM有条件计数：公式结构SUM((条件区域1=条件1)*(条件区域2=条件2)……*(条件区域n=条件n))3.3AVERAGEIF此函数与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}”中，根据两个条件，分别返回IF的两个值，同时把两个结果组成一个数组。{1,2}也是表示数组中的两个元素，1表示选择第一个区域，2表示选择第二个区域，{1,2}表示按顺序选择两个区域构成B:A区域。五、用函数查找数据2、INDEX+MATCH查找除用VLOOKUP查找外，还可以使用INDEX+MATCH进行查找，而且在查找功能上比VLOOKUP更加灵活。2.1MATCH语法MATCH用来查找数据在区域中的位置，而不是查找值本身。其共有3个参数MATCH(查找值、数据区域、逻辑值)查找值：可以使数值、文本或者逻辑值数据区域：是一个连续的单元格区域，可以是1行，或者1列。逻辑值：此值与VLOOKUP相似，为“0”时进行精确查找，为”1”时进行模糊查找。五、用函数查找数据2、INDEX+MATCH查找2.2INDEX语法INDEX函数返回数据区域中制定位置的数值或对数值的引用。这里的数据区域可以1行或者1列，也可以是多行多列。INDEX函数有两种形式，数组形式和引用形式。我们使用较多的是数组形式，引用形式不在此说明。INDEX数组形式语法INDEX(数据区域，行序号或列序号)。意思是返回数据区域中制定单元格的数值,行列号是指纸箱数据区域的某一个单元格，如果指定单元格的位置超出数据区域范围，则返回错误值“#REF!”。例如：五、用函数查找数据2、INDEX+MATCH查找2.3I