干货!史上最全的Excel常见函数用法!

网友投稿 · 2023-08-30 08:08 · 云表格 · 阅读 1194

干货!史上最全的excel常见函数用法!

在计算机二级office考试中，Excel函数是重难点，很多小伙伴都表示无从下手，下面零壹学长就给大家分享一些常考的Excel函数公式，以备不时之需。

ABS()

功能：求绝对值

用法：ABS(number)，求参数number的绝对值。

例子：ABS(-3)的结果为3，ABS(3)的结果为3。

INT()

功能：取整

用法：INT(number)，取一个不大于参数number的最大整数。

例子：INT(3.9)的结果为3，INT(-3.9)的结果为-4。

ROUND()

功能：将数字进行四舍五入

用法：ROUND(number,n),其中n为整数，函数按指定n位数，对number进行四舍五入。当n>0,数字将被四舍五入到指定位数;当n=0,数字将被四舍五入成整数;当n<0,数字将被四舍五入到小数点左边的指定位数。

例子：Round(11.43,1)，Round(11.43,0)，Round(11.43,-1)的结果分别为11.4，11，10。

MOD()

功能：求余数

用法：MOD(number1,number2),返回两数相除的余数，结果的正负号与除数相同，其中number1为被除数,number2为除数

例子：MOD(4,3)等于1，MOD(-4,3)等于2，MOD(4,-3)等于-2，MOD(-4,-3)等于-1。

SQRT()

功能：求平方根

用法：SQRT(number)，求一个正数number的平方根

例子：SQRT(9)的结果为3。

SUM()

功能：求和

用法：SUM(数值区域),将指定的区域求和相加，参数可以是值、单元格引用或者区域

例子：例如=SUM(A2:A9)对A2到A9所有单元格的值进行求和,=SUM(A2:A9,C2:C9)将A2到A9的区域与C2到C9的区域相加进行求和。

SUMIF()

功能：条件求和

用法：SUMIF(区域1，条件，区域1)，区域1满足条件的情况下对区域2进行求和。

例子：例如,公式 =SUMIF(A2:A5,”Tom”,C2:C5)只对区域C2:C5中在区域A2:A5中所对应的单元格等于“Tom”的值求和。

SUMPRODUCT()

功能：乘积之和

用法：SUMPRODUCT(数组1,数组2, …)

例子：SUMPRODUCT(B2:B11,C2:C11)=B2*C2+B3*C3+B4*C4+······+B11*C11

SUMIFS

功能：多条件求和

用法：SUMIFS(求和区域,条件区域1,条件1，[条件区域2，条件2],…)

例子：例如=SUMIFS(A2:A10,B2:B10,B12,C2:C10,A12),表示对单元格A2到A10中的某些值进行求和，其中这些值所在行中B列的值等于B12，C列的值等于A12。

AVERAGE()

功能：求平均值

用法：AVERAGE(number1,number2)，参数可以是数值、单元格或区域。

例子：=AVERAGE(A1:A6)将返回A1到A6这些数字的平均值。

AVERAGEIF()

功能：求给定条件的平均值

用法：AVERAGEIF(列1,值，列2)，列1满足值的情况下，对列2求平均值

例子：假如E3:E17存放员工性别，F3:F17存放员工工资，若要求男性员工的平均工资可以输入公式=AVERAGEIF(E3:E17,”男”,F3:F17)。

AVERAGEIFS()

功能：多条件求平均值

用法：AVERAGEIFS(求平均值区域，区域1，值1，区域2，值2)，找区域1=值1，并且区域2=值2的行，对求平均区域求平均值

例子：假如B2:B9存放性别，C2:C9存放班级，D2：D9存放成绩，若要统计1班所有女生的平均分，则可以输入公式=AVERAGEIFS(D2:D9,B2:B9,”女”,C2:C9,”1班”)。

COUNT()

功能：计数

用法：COUNT(value1,value2,…),对单元格区域中数据的个数进行计数。value1 是必需参数，value2,…为可选参数。

例子：=COUNT(A1:A20)。在这个示例中，如果此区域中有5个单元格包含数字，则答案就为5。

COUNTIF()

功能：条件计数

用法：COUNTIF(区域,条件),统计给定区域中符合指定条件的单元格数目。

例子：=COUNTIF(A1:A10,”>=60″)统计A1到A10区域中大于等于60的单元格数目。

COUNTIFS()

功能：多条件计数

用法：COUNTIF(区域1,条件1，[区域2,条件2],…),多区域计数，计算同时满足各个判断条件的数量。

例子：=COUNTIFS(A1:A10,”>8″,B1:B10,”<20″)表示统计A1到A10大于8且同一行B1到B10中数值小于20的单元格个数。

COUNTA()

功能：统计非空单元格个数

用法：COUNTA(值1, [值2], …)。值1是必需的，值2是可选的。用来计算范围中不为空的单元格的个数。

例子：=COUNTA(A2:A7)表示计算单元格区域 A2 到 A7 中非空单元格的个数。

MAX()

功能：求最大值

用法：MAX(number1,number2,…), 求出一组数中的最大值，number1是必需的后续数字是可选的，参数可以是数值、单元格或区域

例子：如果A2:A6包含12,6,9,26,2，则=MAX(A2:A6,40)区域A2:A6和数值40之中的最大值，结果为40。

MIN()

功能：求最小值

用法：MIN(number1,number2,…), 求出一组数中的最大值，number1是必需的后续数字是可选的，参数可以是数值、单元格或区域。

例子：如果A2:A6包含12,6,9,26,2，则=MIN(A2:A6,40)区域A2:A6和数值40之中的最小值，结果为2。

RANK()

功能：数据排序

用法：RANK(值,序列/区域，排序方式)。求出某一数值在某个区域内的大小排位。排序方式为1指升序，为0指降序。

例子：例如，=RANK(A3,A2:A6,1)返回A3单元格中的数值在A2:A6范围内的排位。

LARGE()

功能：取区域中第N大的值

用法：LARGE(区域,名次)。得到区域里的第N大的值

例子：假如B2:B6存放10,19,21,11,13，则=LARGE(B2:B6,3)表示B2:B6中第3大的值为13

TEXT()

功能：转换成文本

用法：TEXT(数值, 格式)。用于把数字、日期转为文本，转换时要指定格式。

例子：=TEXT(A2,”yyyy年mm月dd日”)就将A2单元格转换成yyyy年mm月dd日的格式。

TRIM()

功能：去掉字符前后空格

用法：TRIM(Text)。Text 为要去掉空格的文本。删除字符前后的空格，字符与字符之间仅保留一个空格。

例子：假如去掉字符串” TRIM函数 “前后的空格，可以输入公式 =TRIM(” TRIM函数 “)，则字符串” TRIM函数 “前后的空格都被去掉。

FIND()

功能：查找字符位置

用法：FIND(查找文本,源文本, [查找开始位置])。

例子：假如A1单元格存放字符串”excel中find函数怎么用”，要从A1单元格查找字符串”怎么”，则可以输入公式=FIND(“怎么”,A1,5)，结果返回13。

LEFT()

功能：截取字符

用法：LEFT(文本字符串,提取的数量)。从文本字符串的第一个字符开始，截取指定个数的字符。

例子：假定A2单元格内容为”ABCDE”，则=LEFT(A2,4)表示截取A2的前4个字符，结果为ABCD。

RIGHT()

功能：截取字符

用法：RIGHT(文本字符串,提取的数量)。从文本字符串的最后一个字符开始，截取指定个数的字符。

例子：假定A2单元格内容为”ABCDE”，则=RIGHT(A2,4)表示截取A2的后4个字符，结果为BCDE。

MID()

功能：取字符串

用法：MID(文本字符串,起始位置,截取的数量)。返回文本字符串中从指定位置开始的特定数目的字符。

例子：假定A2单元格内容为”ABCDE”，则=MID(A2,2,3)表示从第2个位置开始截取A2的3个字符，结果为BCD。

LEN()

功能：统计字符数

用法：LEN(文本字符串)。统计文本字符串中的字符数。

例子：例如=LEN(“北京Beijing”)返回9。

LENB()

功能：统计字节数

用法：LEN(文本字符串)。统计字符串中的字节数。

例子：例如=LEN(“北京Beijing”)返回11。

IF()

功能：条件判断

用法：IF(条件判断，结果为真返回值,结果为假返回值)。根据指定条件来判断其真或假。函数IF最多可以嵌套7层

例子：假如我们要计算出B2单元格中分数超过90的为优秀，80分到90分的是合格，80分以下的是不合格，那么就可以输入公式：=IF(B2>=90,”优秀”,if(B2>=80,”合格”,”不合格”))。

IFERROR()

功能：判定是否是错误值

用法：IFERROR(值，出错时返回的值)。根据给定的值进行检查，如果计算结果返回错误，就返回给定的值

例子：假如A3=13,B3=0，在C3单元格输入公式=IFERROR(A3/B3,”错误”)，因为B3是0，不能做除数，所以会返回“错误”字符。

VLOOKUP()

功能：查找值

用法：VLOOKUP(值,区域,第N列,匹配方式)。在区域首列查找值，得到查找区域第N列那个值，最后一个参数为0则表示精确匹配，为1则表示模糊匹配。

例子：=VLOOKUP(A1,$B$1:$B$10,2,0)意思是在B1:B10中查找A1，找到后返回同行的第2列的值。

LOOKUP()

功能：查找值

用法：LOOKUP(值,查找区域,返回值的范围)。在查找区域中查找值，并返回另一个范围中对应位置的值。

例子：例如：若C2:C8存放成绩，B2:B8存放学生姓名，则=LOOKUP(76,C2:C8,B2:B8)表示得分76的学生的姓名。

" INDEX()

功能：数据定位

用法：INDEX(区域,第N行,第M列)。返回区域中第N行第M列的值。

例子：如图所示，在F2输入=INDEX(A2:D10,4,2)，则会显示出A2:D10区域中第4行第2列的值。

" MATCH()

功能：匹配位置

用法：=MATCH(查找值，查找的范围，查找方式)。确定列表中某个值的位置。查找的范围只能为一列或一行，查找方式为0表示查找等于指定内容的第一个数值，为1则查找小于或等于指定内容的最大值，而且指定区域必须按升序排列，为-1则查找大于或等于指定内容的最小值，而且指定区域必须降序排列

例子：如图所示，在F2输入=MATCH(B5,B2:B10,0)，则结果为4。

" AND()

功能：逻辑与

用法：AND(条件1,条件2, …)。满足条件1且满足条件2，即满足所有判断条件时返回TRUE;当条件中有一项或多项不满足，则返回FALSE

例子：=AND(9-5=4,7>2)为TRUE;

=AND(9-5=4,5>8)为FALSE。

OR()

功能：逻辑或

用法：OR(条件1,条件2, …)。在参数中，任何一个参数逻辑为真，即返回逻辑值TRUE;只有全部为假时，才返回FALSE。

例子：=OR(TRUE,3+5=8)为TRUE;

=OR(1=1=3,2=3=6)为FALSE。

NOT()

功能：求相反值

用法：NOT(判断条件)。

例子：=NOT(FALSE)为TRUE;=NOT(1+4=5)为FALSE。

DATE()

功能：转换日期

用法：DATE(year,month,day)。返回指定日期的序列数

例子：=DATE(2019,9,29)为43737，返回代表2019年9月29日的序列数。

DATEDIF()

功能：计算日期之差

用法：DATEDIF(开始日期,结束日期,unit)。计算两个日期之间的年数、月数、天数。unit为返回结果的代码，”Y”返回整年数;”M”返回整月数;”D”返回整天数;”MD”返回日期时间段中天数的差，忽略年和月;”YD” 返回日期时间段中天数的差，忽略年;”YM” 返回日期时间段中月数的差，忽略日和年。

例子：计算出生日期为1976-4-1人的年龄，可以输入：=DATEDIF(“1976-4-1″,TODAY(),”Y”);

若要计算日期为1976-4-1和当前日期的间隔月份数，可以输入：=DATEDIF(“1976-4-1″,TODAY(),”M”)。

YEAR()

功能：求年份

用法：YEAR(number)。返回某日期的年份

例子：=YEAR(43737)为2019;=YEAR(“2019年9月29日”)返回2019。

MONTH()

功能：求月份

用法：MONTH(number)。返回序列数number相对应的某日期的月份数。

例子：=MONTH(“2019年9月29日”)将返回9。

DAY()

功能：求天数