EXCEL常用函数使用方法PPT课件

-,1,EXCEL常用函数使用方法,全业务支撑室,-,2,基础-EXCEL中区域表示,D4,由左上A2和右下D2所组成的长方格区域,C2:C4,A2:D4,第4行，第D列,C2至C4所组成的区域,2020/5/8,-,3,基础-特殊符号,stock.xlsSheet1,如果要在上面表格引用stock.xls文件中的A1，这时应使用!,=stock.xlsSheet1!A1,若文件名为中文或有括号，单引号不能省略,张三,-,4,基础-单元格引用,在C1输入=A1+B1时将显示什么？若复制C1单元格到C2将显示3还是7？复制到D1呢？,3,5,7,相对引用复制公式时地址跟着发生变化,绝对引用复制公式时地址不会跟着发生变化,使用$标识符表示绝对引用C1输入=$A$1+$B$1,2020/5/8,-,5,单元格信息提取,显示A1单元格从左开始前5个字符,显示A1单元格从右开始前4个字符,显示A1单元格从第2格字符从左开始前3个字符,=LEFT(A1,11),=RIGHT(A1,4),=MID(A1,2,3),=LEFT(A1,5),=LEFT(A1,-1),abc黄h,jklc,bc黄,asd黄hjkc,#VALUE!,LEFT(字符串,字符个数)RIGHT(字符串,字符个数)MID(字符串,开始位置,字符个数),语法,超过总字符数值错误，使用错误参数,2020/5/8,-,6,FIND函数,语法,FIND(要查找的字符串，被查找的字符串，开始位置）返回值为要查找的字符串的位置,FIND函数,查找A1单元格中字符串bc的位置,=FIND(bc,A1,1),2,查找A1单元格中字符c的位置,=FIND(c,A1,1),3,9,查找A1单元格中字符c的位置,=FIND(c,A1,4),查找A1单元格中字符g的位置,=FIND(g,A1,3),#VALUE!,值错误，查找不到字符g，返回值错误,2020/5/8,-,7,FIND函数,目标,提取A列中的数字并将其转换为数值步骤：查找数字位置根据位置提取转换为数值,LEFT与FIND组合使用,=VALUE(LEFT(A1,FIND(M,A1,1)-1),=VALUE(LEFT(A2,FIND(M,A1,1)-1),=VALUE(LEFT(A3,FIND(M,A1,1)-1),VALUE(字符串)作用：将字符串转换为数值注意：字符串必须只包含数字,100,4,20,2020/5/8,-,8,FIND函数,逻辑判断,=AND(A1,B1),=OR(A2,B2),TRUE,FALSE,这儿0是文本非数字,=NOT(A3),#VALUE!,EXCEL逻辑判断只有AND、OR、NOT三种,EXCEL只将0判定为FALSE,逻辑判断不能使用文本,2020/5/8,-,9,条件判断,IF函数判断是否满足条件，如果满足返回一个值，不满足返回另外个值,IF(测试条件,真值,假值),语法,=IF(A1=50,A1=50,A160,A260,),=IF(A260,A260),A1=50,0,FALSE,若没有设置参数值内容将返回0,若没有设置假值参数将返回FALSE,=IF(A1=70,B,IF(A2=60,C,D),C,IF嵌套使用，但嵌套次数过多建议多单元格分开使用,2020/5/8,-,10,条件判断,IFERROR函数IFERROR(计算值,错误值)如果公式计算结果错误，则返回错误值，否则返回公式计算的值。,结果错误类型,使用错误的参数或类型不对,公式被0除,公式中没有可用值,单元格引用无效,函数中的单元格引用不正确（语法）,无法识别函数中的文本,函数没有返回数值,2020/5/8,-,11,数据类型判断,如果判断特定的错误类型，或者更进一步，判断单元格内是否为数字、文本或空单元格,IS函数集,如果为右列的内容，则返回TRUE，否则返回FALSE,2020/5/8,-,12,查找与引用,查找也是一种逻辑判断判断单元格内容是否与查找的内容相符,引用根据查找的结果，引用某个单元格信息,多条件的判断(软件中的switch),IF多条件嵌套使用,报表文档间汇总,2020/5/8,-,13,查找与引用,LOOKUP函数LOOKUP(查找值,查找向量,返回向量)在查找向量近似查找相应的值，返回相应的向量,假设某人绩效排名40%,=LOOKUP(D1,C1:C6,A1:A6),优良,如果使用IF函数应如何做？,=IF(D1=C5,A1,IF(D1=C4,A2,IF(D1=C3,A3,IF(D1=C2,A4,IF(D1=C1,A5,输入数据有误);,必须升序,2020/5/8,-,14,查找与引用,VLOOKUP函数,查找某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。,VLOOKUP(查找值,数据表,列序数,匹配条件),语法,查找值：要在数据表第一列中搜索的值数据表：包含数据的单元格区域列序数：查找成功时，返回匹配值所在行相应列号的单元格区域的值,匹配条件,TRUE:表示精确或近似匹配，要求数据表为升序排列FALSE:表示精确匹配，对数据表排列没有要求,2020/5/8,-,15,=VLOOKUP(A1,stock.xlsSheet1!A:C,3,false),说明：在stock.xls中Sheet1的A列中精确搜寻A2(洪濑)，返回其所在行第3列的值,stock.xlsSheet1,=VLOOKUP(A3,stock.xlsSheet1!A:C,3,false),2,6,若输入=VLOOKUP(A3,stock.xlsSheet1!A:B,3,false)将返回什么？,将返回#REF!，因为查找数据表只有A列和B列，而公式需要引用C列，产生引用错误,查找与引用,=VLOOKUP(A2,stock.xlsSheet1!A:C,3,false),#NAME?,2020/5/8,-,16,汇总函数,求和，求平均，计数这些都是汇总函数，主要应用于统计评估,=SUM(A2:A5),=AVERAGE(A2:A6),=COUNT(A1:A9),=COUNTA(A1:A9),6,9,统计A1到A9含有数字的单元格个数,统计A1到A9非空单元格个数,A2到A5单元格区域求和,A2到A5单元格区域求平均,348,74.8,=SUM(A1:A9),#N/A,=COUNTIF(A1:A9,28),统计A1到A9数字为28的单元格个数,2,2020/5/8,-,17,分类汇总,有时候需要筛选后的数据进行计数求和或平均，但例如使用前面的COUNTA之类的函数，被筛选的数据也被计入，这时就要使用SUBTOTAL函数,SUBTOTAL函数返回列表或数据库中的分类汇总，只针对筛选后的数据计算,SUBTOTAL(函数序号,引用1,.),语法,函数序号决定SUBTOTAL函数功能，指定使用何种函数进行分类汇总计算,1-AVERAGE2-COUNT3-COUNTA4-MAX5-MIN6-PRODUCT7-STDEV8-STDEVB9-SUM10-VAR11-VARP,2020/5/8,-,18,筛选后的表格,分类汇总,=SUBTOTAL(3,A1:A9),=COUNTA(A1:A9),函数序号3代表使用COUNTA函数功能,9,6,只统计了筛选后的表格,筛选前的表格,函数序号9代表使用SUM函数功能,=SUBTOTAL(9,A1:A9),446,2020/5/8,-,19,其他函数,2020/5/8,-,20,自助查找,EXCEL一共有11类超过200个函数，涵盖工程，财务两大领域。,OFFICE自带帮助，全面详细规范,网络搜索，知道所需操作而不清楚相关函数使用，心得体会,2020/5