工作中最常用地excel函数公式大全.docx

工作中最常用的excel函数公式大全
一、数字处理
1、取绝对值=ABS徵字） 2、取整=INT（数字） 3、四舍五入二ROUN嗽字，小数位数）
二、判断公式
1、把公式产生的错误值显示为空
公式：C2=IFERROR（A2/B2,""）
说明：如果是错误值则显示为空，否则正常显示。
2、IF多条件判断返回值公式：
C2=IF（AND（A2<500,B2="未到期"）,"补款",""）
说明：两个条件同时成立用 AND任一个成立用OR®数。
三、统计公式
1、统计两个表格重复的内容
公式：B2=COUNTIF(Sheet15!A:A,A2)
说明：如果返回值大于 0说明在另一个表中存在，0则不存在。
2、统计不重复的总人数
公式：C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
说明：用COUNTI就计出每人的出现次数，用 1除的方式把出现次数 变成分母，然后相加。
四、求和公式
1、隔列求和
公式：H3=SUMIF($A$2:$G$2,H$2,A3:G3)
4g=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
说明：如果标题行没有规则用第 2个公式
2、单条件求和
公式：F2=SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法
A
C
1
产品
单价
金额
2
3
24
芍’
E
4
12
FT"
C
6
18
5
A
I ' 1
8
6
E
4
12
7
A
2
LG
8
E
4
12
9
B
4
12
10
C
G
18
11
A
2
16
12
E
4
12
13
A
3
24
U
公式：
=SUMIF(A：ArE2/C：C)
E F &
产品 合计
A 88
3、单条件模糊求和
公式：详见下图
说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星 号是表示任意多个字符，如“*A*”就表示a前和后有任意多个字符, 即包含A
B
C
D
1
产品
单价
金额
2
ABC
3
24
3
BD
1
12
4
CA
6
18
I I ..
6包含A的求和 42
7以鸟开始求和 24
一
7 \
■
公式1=SUMIF{A2;A4 J *八* r；C2:C4) 国 jtJ 2-SU MIF (A2:A4f "A* ",C2: C4)
3 公曲"UMF{A2M"* A'；C2:C4) H
4、多条件模糊求和
公式：C11=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
明：在sumifs中可以使用通配符*
5、多表相同位置求和
公式：b2=SUM(Sheet1:Sheet19!B2)
说明：在表中间删除或添加表后，公式结果会自动更新。
6、按日期和产品求和
公式：F2=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2B$25=$E2)
*$C$2:$C$25)
说明：SUMPRODUCW完成多条件求和
A
E
C D
E
C
H
1
E期,
lLbbl
产品
1
2
3
~2
2015-1-5
A
5
A
23
50
26
3
2015-1-10
A
IS
E
16
20
0
鼻
201&-1-11
E
11
C
..0
35
20
2015-1-12
B
5
r B
0
16
1
2015-2-10
A
13
s
0
11
Q
7
2015-2-11
C
19
E
0
20
0
3
2015-2-15
D
6
F
0
2
0
9
2015-2-15
C
15
G
Q
10
0
10
—
2015-2-15
S
11
效IMF
JL_
16
五、查找与引用公式
1、单条件查找公式
公式 1: C11=VLOOKUP(B11,B3:F7,4,FALSE)
说明：查找是 VLOOKUP擅长的，基本用法
A B C D E F
姓名
工号
拄界
籍贯
出生年月
张三丰
KT001
亘
北京
107。年8月
学区光
KT002
文