excel表格常用公式大全

常用的excel函数公式大全

一、数字处理

1.取绝对值

公式：=ABS(数字)

说明：用于求某个数值的绝对值。

2.取整，

公式：=INT(数字)

说明：int是直接取整数,它会直接返回一个整数。

3.四舍五入

公式：=ROUND(数字,小数位数)

说明：返回按指定位数进行四舍五入的数值。roundup向上四舍五入，rounddown向下舍去

4.截取

公式：=trunc（）

说明：trunc函数是指定位数的截取，它可以根据需求进行位数的截取，第二个参数默认为0，“1”表示截取至1位小数。

5.取余

公式：=mod()

说明：mod返回余数,经过算法之后，他只会把余数返回来

二、判断公式

1.把公式产生的错误值显示为空

公式：=IFERROR(A2/B2,"错误")

说明：如果是错误值则显示为“错误”，否则正常显示。

2.IF多条件判断返回值

公式：=IF(AND(E2>240,D2>80),"优秀","")，=IF(OR(E2>240,D2>80),"优秀","")

说明：两个条件同时成立用AND,任一个成立用OR函数。等级1优秀条件为总分大于240，且英语成绩大于80分。等级2优秀条件为总分大于240分或者英语成绩大于80分。

三、统计公式

1.统计两个表格重复的内容

公式:　　=COUNTIF($e$2:$e$13,A2)

说明：如果返回值大于0说明在另一个表中存在，0则不存在。查找e2:e13范围内A列值出现的次数。不同表格之间同样适用。

2.统计不重复的内容

公式：=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))

说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。A出现3次，B出现2次，C出现2次，D出现1次，E出现1次，F出现1次，G出现1次。求解过程为：

=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))

四、求和公式

1.隔列求和

公式：=SUMIF($A$2:$G$2,H$2,A3:G3)

或=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=1)*B3:G3)

说明：如果标题行没有规则用第2个公式，第2个公式中的*表示且的关系，b3:g3,统计被2整除余1的列号对应的值进行相加运算。

2.单条件求和

公式：　　=SUMIF(A:A,E2,C:C)

说明：SUM的基本用法

3.单条件模糊求和

公式：详见下图

说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。

4.多条件模糊求和

公式：=SUMIFS(C2:C7,A2:A7,E2&"*",B2:B7,F2)

说明：在sumifs中可以使用通配符*

5.多工作表相同单元格求和

公式：=SUM(Sheet1:Sheet19!B2)

说明：在表中间删除或添加表后，公式结果会自动更新。

如果只对sheet2表到sheet4、sheet6表到sheet19求和，中间要跳过sheet5呢？ 那么可以输入这样的公式：=SUM(Sheet2:Sheet19!B2)-sheet5!A1

如果想对当前工作簿内除当前工作表以外所有B2单元格求和，可以用的的简便公式：

=sum(‘*‘!B2)

公式：=SUMPRODUCT((MONTH($A$2:$A$15)=F$1)*($B$2:$B$15=$E2)*$C$2:$C$15)

说明：SUMPRODUCT可以完成多条件求和