EXCEL常用函数使用方法

基础-EXCEL中区域表示D4由左上A2和右下D2所组成的长方格区域

C2:C4

A2:D4第4行，第D列C2至C4所组成的区域第一页第二页，共21页。基础-特殊符号&：excel用于合并文本内容!：用于引用其他文件表格单元格A123=A1&"@139.com;";A1张三2stock.xlsSheet1如果要在上面表格引用stock.xls文件中的A1，这时应使用"!"='[stock.xls]Sheet1'!A1若文件名为中文或有括号，单引号不能省略张三第二页2023/10/10第三页，共21页。基础-单元格引用ABCD112234356在C1输入=A1+B1时将显示什么？若复制C1单元格到C2将显示3还是7？复制到D1呢？357相对引用复制公式时地址跟着发生变化绝对引用复制公式时地址不会跟着发生变化使用$标识符表示绝对引用C1输入=$A$1+$B$1第三页第四页，共21页。单元格信息提取A1abc黄hjklc23456显示A1单元格从左开始前5个字符显示A1单元格从右开始前4个字符显示A1单元格从第2格字符从左开始前3个字符=LEFT(A1,11)=RIGHT(A1,4)=MID(A1,2,3)=LEFT(A1,5)=LEFT(A1,-1)abc黄hjklcbc黄asd黄hjkc#VALUE!LEFT(字符串,字符个数)RIGHT(字符串,字符个数)MID(字符串,开始位置,字符个数)语法超过总字符数值错误，使用错误参数第四页2023/10/10第五页，共21页。FIND函数A1abc黄hjklc2345语法FIND(要查找的字符串，被查找的字符串，开始位置）返回值为要查找的字符串的位置FIND函数查找A1单元格中字符串"bc"的位置=FIND("bc",A1,1)2查找A1单元格中字符"c"的位置=FIND("c",A1,1)39查找A1单元格中字符"c"的位置=FIND("c",A1,4)查找A1单元格中字符"g"的位置=FIND("g",A1,3)#VALUE!值错误，查找不到字符"g"，返回值错误第五页2023/10/10第六页，共21页。FIND函数AB1100M24M320M目标提取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(字符串)作用：将字符串转换为数值注意：字符串必须只包含数字100420第六页2023/10/10第七页，共21页。FIND函数逻辑判断ABC11-120030=AND(A1,B1)=OR(A2,B2)TRUEFALSE这儿"0"是文本非数字=NOT(A3)#VALUE!EXCEL逻辑判断只有AND、OR、NOT三种EXCEL只将0判定为FALSE逻辑判断不能使用文本第七页2023/10/10第八页，共21页。条件判断IF函数判断是否满足条件，如果满足返回一个值，不满足返回另外个值IF(测试条件,真值,假值)语法AB110026034=IF(A1>=50,"A1>=50","A1<50")=IF(A2>60,"A2>60",)=IF(A2>60,"A2>60")A1>=500FALSE若没有设置参数值内容将返回0若没有设置假值参数将返回FALSE=IF(A1<50,"A",IF(A2>=70,"B",IF(A2>=60,"C","D")))CIF嵌套使用，但嵌套次数过多建议多单元格分开使用第八页2023/10/10第九页，共21页。条件判断IFERROR函数IFERROR(计算值,错误值)如果公式计算结果错误，则返回错误值，否则返回公式计算的值。结果错误类型#N/A#VALUE!#NUM!#NAME?#REF!#NULL!#DIV/0使用错误的参数或类型不对公式被0除公式中没有可用值单元格引用无效函数中的单元格引用不正确（语法）无法识别函数中的文本函数没有返回数值第九页2023/10/10第十页，共21页。数据类型判断如果判断特定的错误类型，或者更进一步，判断单元格内是否为数字、文本或空单元格IS函数集ISNUMBER(值)值为数字ISTEXT(值)值为文本ISBLANK(值)值为空白单元格ISERROR(值)值为任意错误值ISERR(值)值为任意错误值（除#N/A)ISNA(值)值为#N/A如果为右列的内容，则返回TRUE，否则返回FALSE第十页2023/10/10第十一页，共21页。查找与引用查找也是一种逻辑判断——判断单元格内容是否与查找的内容相符引用——根据查找的结果，引用某个单元格信息多条件的判断(软件中的switch)IF多条件嵌套使用报表文档间汇总第十一页2023/10/10第十二页，共21页。查找与引用LOOKUP函数LOOKUP(查找值,查找向量,返回向量)在查找向量近似查找相应的值，返回相应的向量ABC1优秀15%02优良35%15%3良好40%50%4称职9%90%5欠佳1%99%6100%假设某人绩效排名40%=LOOKUP(D1,C1:C6,A1:A6)D0.40优良如果使用IF函数应如何做？=IF(D1>=C5,A1,IF(D1>=C4,A2,IF(D1>=C3,A3,IF(D1>=C2,A4,IF(D1>=C1,A5,"输入数据有误")))));必须升序第十二页2023/10/10第十三页，共21页。查找与引用VLOOKUP函数查找某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。VLOOKUP(查找值,数据表,列序数,[匹配条件])语法查找值：要在数据表第一列中搜索的值数据表：包含数据的单元格区域列序数：查找成功时，返回匹配值所在行相应列号的单元格区域的值匹配条件TRUE:表示精确或近似匹配，要求数据表为升序排列FALSE:表示精确匹配，对数据表排列没有要求第十三页2023/10/10第十四页，共21页。=VLOOKUP(A1,'[stock.xls]Sheet1'!A:C,3,false)说明：在stock.xls中Sheet1的A列中精确搜寻A2(洪濑)，返回其所在行第3列的值ABC1省新422诗山333仑仓1044洪濑76stock.xlsSheet1AB1省新2溪美3洪濑=VLOOKUP(A3,'[stock.xls]Sheet1'!A:C,3,false)26若输入=VLOOKUP(A3,'[stock.xls]Sheet1'!A:B,3,false)将返回什么？将返回#REF!，因为查找数据表只有A列和B列，而公式需要引用C列，产生引用错误查找与引用=VLOOKUP(A2,'[stock.xls]Sheet1'!A:C,3,false)#NAME?第十四页2023/10/10第十五页，共21页。汇总函数求和，求平均，计数这些都是汇总函数，主要应用于统计评估AB1500M219231004285286447异常8#N/A954=SUM(A2:A5)=AVERAGE(A2:A6)=COUNT(A1:A9)=COUNTA(A1:A9)69统计A1到A9含有数字的单元格个数统计A1到A9非空单元格个数A2到A5单元格区域求和A2到A5单元格区域求平均34874.8=SUM(A1:A9)#N/A=COUNTIF(A1:A9,28)统计A1到A9数字为28的单元格个数2第十五页2023/10/10第十六页，共21页。分类汇总有时候需要筛选后的数据进行计数求和或平均，但例如使用前面的COUNTA之类的函数，被筛选的数据也被计入，这时就要使用SUBTOTAL函数SUBTOTAL函数返回列表或数据库中的分类汇总，只针对筛选后的数据计算SUBTOTAL(函数序号,引用1,...)语法函数序号决定SUBTOTAL函数功能，指定使用何种函数进行分类汇总计算1-AVERAGE2-COUNT3-COUNTA4-MAX5-MIN6-PRODUCT7-STDEV8-STDEVB9-SUM10-VAR11-VARP第十六页2023/10/10第十七页，共21页。AB21923100428528644954筛选后的表格分类汇总=SUBTOTAL(3,A1:A9)=COUNTA(A1:A9)函数序号3代表使用COUNTA函数功能96只统计了筛选后的表格AB1500M219231004285286447异常8异常954筛选前的表格函数序号9代表使用SUM函数功能=SUBTOTAL(9,A1:A9)446第十七页2023/10/10第十八页，共21页。其他函数HLOOKUPVLOOKUP的行查找版本MATCH可基于升序或降序近似查找，返回查找值的位置OFFSET通过引用为参考，通过偏移量得到新的引用PRODUCT所有乘积，返回乘积结果SUMIF基于条件求和T判断单元格是否为文本，非文本返回空单元格CELLVLOOKUP的行查找版返回某