工作中常用的Excel函数公式大全

?

使用率比较高的函数公式整理齐了，居家旅行必备！

强烈建议留意下方小窍门！！！！！

小窍门：在动图上轻滑屏幕后停住，可暂停动图哦！图片较多，建议使用WIFI！

一：数字、字符处理

1、取绝对值

---

=ABS(A1)

A1单元格为数字

2、去空格

---

去单元格内容前后的空格

=TRIM(A1)

去单元格内所有空格

=SUBSTITUTE(A1,' ','')

公式中第一个' '中间为空格

3、多单元格合并

---

=PHONETIC(B2:D2)

PHONETIC只能合并字符，不能对数字合并

4、取整数

---

=INT(A1)

A1单元格为数字

5、四舍五入

---

=ROUND(A1,N)

A1单元格为数字；N为小数位数，譬如N=1,四舍五入1位小数；N=2，四舍五入2位小数

6、取字符数

---

=LEN(A1)

7、取字符串左边或者右边的字符

---

=LEFT(A1,3)

=RIGHT(A1,3)

二：判断公式

1、借方显示正数，贷方显示负数

---

=IF(A2=0,-B2,A2)

A2 B2单元格为数字

2、错误值显示为空白

---

=IFERROR((C2-B2)/B2,'')

PS：也可以根据需要，错误值显示为0或1

=IFERROR((C2-B2)/B2,1)

3、IF多条件判断

---

=IF((B2>=70)*(C2>=70)*(D2>=70),A2,'')

PS:任何一个条件成立 IF+OR函数

=IF(OR(B2<><><>

三：统计公式

1、统计重复次数

---

=COUNTIF(A:A,A2)

PS:多条件统计重复次数

=COUNTIFS(B:B,'>=70',C:C,'>=70',D:D,'>=70')

PS:出现重复值则显示“重复”,IF+COUNTIF函数

=IF(COUNTIF(A:A,A2)>=2,'重复','')

2、统计不重复的总人数

---

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

四：求和

1、单条件求和

---

=SUMIF(A:A,D2,B:B)

2、隔行求和

---

=SUMIF($B$2:$G$2,H$2,$B3:$G3)

PS：如标题行没有规律

=SUMPRODUCT((MOD(COLUMN(B$3:G$3),2)=0)*$B3:$G3)

3、多条件模糊求和

---

=SUMIFS(C:C,A:A,E2&'*',B:B,F2)

公式中使用通配符*

4、按日期和产品求和

---

=SUMPRODUCT((MONTH($B$2:$B$7)=F$1)*($A$2:$A$7=$E2),$C$2:$C$7)

四：查找

1、简单查找

---

=VLOOKUP(E2,A:C,3,0)

根据姓名查找总业绩

2、多条件查找

---

=INDEX(A:D,MATCH(F2,A:A,0),MATCH(G2,1:1,0))名查找总业绩

说明：INDEX(查找区间,行号,列号)

3、查找最后一个符合条件的值

---

=LOOKUP(2,1/(A2:A9=A12),E2:E9)

PS：多条件查找最后符合条件的值

=LOOKUP(2,1/((A2:A9=A12)*(D2:D9=D12)),E2:E9)

4、根据区间取数

---

{=LOOKUP(D9,IF(B$2:B$7=B9,D$2:D$7),C$2:C$7)}

注：大括弧并非手工打上去，数组公式输入完毕同时按CTRL+SHIFT+ENTER