常用函数公式运用

常用函数公式运用1、查找引用自动填充=lookup（，0/（（条件1）*（条件2）*（条件3或更多）），查找区域列）2、【提取函数】有：left （从左到右提取）right（从右到左提取）mid （从中间提取）find （精确提取某字符串中某个字符的位置）3、len【计算单元格字符长度（个数），包括空格】公式=len(A1)4、text【转换文本格式】5、value【文本转换为数字格式】6、substitute【替换或查找某字符出现的次数】替换公式：(要替换的文本,旧文本,新文本,[替换第几个])例1：titute（B2,"二班","一班")；例2：=SUBSTITUTE(B2,MID(B2,4,5),"*****")Replace替换函数，公式=Replace（A,4,4,”****”）指：要替换的单元格，从什么地方开始（第4），替换多少（4），替换成什么（****）。

7、iserr【作用是检查一个值是否为#N/A以外的错误值，返回true或者false，值可以是单元格，公式或者是数值名称】iferror【返回错误值为空】8、hyperlink【将打开存储在链接位置中的文件或跳转到指定的单元格位置】9、查找重复内容=IF(COUNTIF(A:A,A2)>1,"重复","")10、根据出生年月计算年龄=DATEDIF(A2,TODAY(),"y")或=DATEDIF(E3,"2018-08-31","y")11、统计一列中个数：A列数值个数：=COUNT(A:A) (只统计是数字的单元格，会自动忽略文本、错误值(#DIV/0!等)、空白单元格、逻辑值(true和false)。

)COUNTA (对包含任何类型信息的单元格进行计数，这些信息包括错误值和空文本)例如：=counta（a1:a6)；又如：=COUNTA(A1:A6,B1:B6)其中lookup（）为查找公式。

（）内有三个参数，用“，”号隔开，第一个“，”前为参数1，没有任何内容，第二个“，”前为参数2，查找条件。

最后第三个参数为查找区域，一般为需查找的某列数据区域。

12、成绩排名（重复名次后，不再有下个名次）=RANK(A2,A$2:A$7)相同成绩不占用名次=SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+113、统计某分数人数=COUNTIF(B1:B7,">90")统计分数段人数=FREQUENCY(B2:B7,{70;80;90})同时选中E2:E5，输入以下公式，按Shift+Ctrl+Enter=FREQUENCY(B2:B7,{70;80;90})14、countif函数的用途作用:根据条件统计个数. countif函数是根据条件在另一个区域进行个数的统计，一方面它可以完成符合条件的统计计算。

另一方面由此扩展出它可以进行重复值的查找我表格的核对。

countif的基本语法:COUNTIF(单元格引用, 条件) 即：COUNTIF（A:A,”x”）参数说明:第一个参数只能是单元格引用方式，不能使用内存数组；第二个参数是条件，条件可以是值，可以是字符串构成的复合条件，可以使用通配符进行模糊统计，可以使用内存数组。

应用示例:例1:统计在A列是“公司A”的个数，公式=Countif(A:A,"公司A")例2:统计A列包含“公司A”的个数，公式=Countif(A:A,"*公司A*")注:这里使用通配*表示字符前后有任意个字符。

例3:统计C列成绩大于60的学生个数，公式=Countif(C:C,">60") 注:这里是用运算对比符号和数字构成的条件例4:统计大于C1单元格数值的C列个数。

公式:=Countif(c:c,">" & c1)注:这里是用&插入了一个变量，即C1的值。

例5:统计C列大于平均数的个数，公式:=Countif(c:c,">" & average(c:c)) 注:这里是使用了平均值函数average先计算平均值，然后作为条件。

例6:统计A列为“公司A”或“公司B”的个数，公式:{=Sum(Countif(A:A,{"公司A","公司B"})) } 注:这里在第二个参数里加入了常量数组，使用countif的结果是分别按两个公司名称统计的结果，也是一个数组假如是{3,4}，得到数组后用sum函数对两个数进行求和，得到总的个数，这个公式是数组公式，所以一定要输入公式后把光标放在公式最后，按ctrl+shift，然后按enter键结束输入。

15、countifs函数的用途作用：用于对某一区域内满足多重条件的单元格进行计数（多条件计数）。

语法格式=countifs(criteria_range1,criteria1,criteria_range2,criteria2,…)（第一个条件区，第一个对应的条件，第二个条件区，第二个对应的条件，第N个条件区，第N个条件对应的条件）。

即：=countifs （A2：A100,”A2”,B2:B100,”B2”）参数——条件，它的形式可以为数字、表达式或文本。

当它是文本和表达式时，注意要使用双引号。

且引号在英文状态下输入。

16、sumproduct函数的用途作用：多条件求和与计数的函数例1：统计大于1000，小于3000的数字个数=sumprodcut((a1:a100>1000)*(a1:a100<3000))例2：=SUMPRODUCT((B2:B17="女")*1,(C2:C17="销售部")*1)公式解析：在sumproduct 函数中包含两个数组。

第一个数组判断区域B2:B17中的值是否为“女”，第二个数组判断区域C2:C17 中的值是否为“销售部”，判断结果为包含逻辑值的数组。

为了让这两个数组可参加运算，需要将每个数组都乘以1，将其转换为包含1 和0 的数组。

公式中A2:A10条件区域，即部门这列的所有部门情况。

条件是指1部，即满足部门为1部的部门，B2:B10是对应实际计算平均值的区域。

(3) averageifs（win2007等高版本用）例：求出第一次测验成绩在70-90分之间的人数的平均分是多少？=AVERAGEIFS(B2:B5, B2:B5, ">70", B2:B5, "<90")又例：计算所有学生第二次测验成绩超过75分的分数的平均值。

=AVERAGEIFS(C2:C5, C2:C5, ">75")再例：计算所有学生最终测验成绩超过80 分的分数的平均值(87.5)。

标记为“不及格”的分数不计算在内，因为它不是一个数字值。

=AVERAGEIFS(D2:D5, D2:D5, "<>不及格", D2:D5, ">80")(4) averagea 计算非空单元格平均值。

= averagea（A1:A100）。

文本、字符等都当作0或1参与计算。

17、iferror函数的用途（win2007用，win2003用iserr）用途：公式的计算结果为错误时要返回的值。

计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或#NULL!。

公式：=iferror(( ),””) 说明：如果（）是错误的值，就返回为””(空)。

例：=IFERROR((F7+I7+L7+O7),"")，如果F7+I7+L7+O7，是错误的如#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或#NULL!，就返回空白。

18、and、or函数的使用方法判断，然后返回一个值。

正确就返回，反之返回另一个。

都常与“if”配合使用。

如图And表示“和”，or表示“或”。

上图，如果用“OR”，就表示只要1年>120，就是“优秀”。

公式中可用“< > = ”，输入了文本、文字，就用””.如B2=”男”。

19、求和的函数的使用方法（1）sum（2）sumif 公式=sumif(A1:A100,”>85”),前者为范围，后者为条件。

又如：=sumif(A1:D100,”>=60”) （3）sumproduct（见前排名、计数使用）（4）round（四舍五入）、roundup（按指定位数向上取整数）、rounddown（按指定位数向下取整数）、int（将数值向下取整为最接近的整数）。

公式= round（A1，0）；= roundup（A1，0）= rounddown（A1，0）；int(A1)（5）abs（对单元格数值进行绝对值处理）公式=abs(A1)（6）mod（求余函数）（见前，身份号码中提取“性别”）20、IF函数的使用方法作用：逻辑函数，表达的意思是当满足某条件时，返回一个值，否则返回另一个值。

例1：=IF(B2<60,"不及格","及格")21、subtotal函数对筛选后的数据进行统计，它是Excel中唯一一个能统计用户可见单元格的函数。