工作中最常用的Excel函数公式大全.docx
《工作中最常用的Excel函数公式大全.docx》由会员分享，可在线阅读，更多相关《工作中最常用的Excel函数公式大全.docx（19页珍藏版）》请在冰豆网上搜索。
工作中最常用的Excel函数公式大全
1、取绝对值
二ABS（数字）
2、取整
=INT（数字）
3、四舍五入
二ROUND（数字，小数位数）
二、判断公式
1、把公式产生的错误值显示为空
公式：
C2
=IFERROR（A2/B2,""）
说明：
如果是错误值则显示为空，否则正常显示。
2、IF多条件判断返回值
公式：
C2
=IF（AND（A2<500,B2="未到期"）,"补款",""）
说明：
两个条件同时成立用AND，任一个成立用OR函数。
C2-
A
=IF（AND（A2<500J
A
二BG
C
1
鋼
是否到期
提曜
800
未到期
1
3
200
已到期
1
4
300
未到期
补款
5
二、统计公式
1、统计两个表格重复的内容
公式:
B2
二COUNTIF（Sheet15!
A:
A,A2）
0则不存在。
说明：
如果返回值大于0说明在另一个表中存在,
四、求和公式
1、隔列求和
公式：
H3
二SUMIF（$A$2:
$G$2,H$2,A3:
G3）
或
二SUMPRODUCT（（MOD（COLUMN（B3:
G3）,2）=0）RB3:
G3）
公式：
I
=SUMIF（$A$2:
$G$2,H$2,A3:
G3）
二二一if1丄匿三：
.三二三1；_圧-「三■［日
说明：
如果标题行没有规则用第2个公式
E
C
D
E
FG
H
I
1丸口
1.
冃
2月
3月
l+
2f
实际
计划
实际
计划
实际
计划
实际
计划
3A
1
6
9
3
7
4
17
4B
9
7
6
5
2
3
1/^
5C
6
10
10
7
6
5
2
6
7
8
9
10
11
12
2、单条件求和
公式：
F2
=SUMIF（A:
A,E2,C:
C）
说明：
SUMIF函数的基本用法
A
B
C
D
E
F
G
辛
.L口口！
里价
金额
产品
合计
2
A
3
24
A
88
3
E
4
12
4
C
6
18
5
A
1
8
6
B
4
12
7
A
2
16
公式：
S
B
4
12
9
E
4
12
—
=SlJMir（AAE2r：
C）
10
C
&
13
A
2
16
11
A
12
B
4
12
13
A
3
24
'亠r
佞号；—七三山
丿/.-i勺r勺
14
3、单条件模糊求和
公式：
详见下图
说明：
如果需要进行模糊求和，就需要掌握通配符的使用，其中星
号是表示任意多个字符，如"RAR"就表示a前和后有任意多个字符，即包含A
A
B
c
D
1
产品
单价
金额
2
ABC
3
24
3
ED
4
12
4
CA
6
18
5
6
L包含A的求和42
7
9
2.CU开始求和昭
3.CU幵始求和18
iX.
-為L
i6
五
12
」公式1=5UMHHA2:
A4严A*公式2=SUMIF（A2:
A4」A*・公式3=SUMiF（A2:
A4F^AM
]1Ij
l\C2:
C4）rC2:
C4）rC2:
C4）
4、多条件模糊求和
公式：
C11
二SUMIFS（C2:
C7,A2:
A7,A11"R",B2:
B7,B11）
说明：
在sumifs中可以使用通配符R
A
B
C
D
1
严品
地区
2
电观21寸
郑州
o|
iU:
3
洗衣机5公斤
（洛阳
5
4
电视21寸
I希宁
1
5
洗衣机E公斤
北京
5
6
电视刀寸
郑州
5
7
电视29寸
L郑州
y
9
10
:
产品.；
1地区
数量
11
电视
郑州
9
5、多表相同位置求和
公式：
b2
二SUM（Sheet1:
Sheet19!
B2）
说明：
在表中间删除或添加表后，公式结果会自动更新。
A
D
1
产品
本月累计
2
A
19
1
3
E
0
汇总前何乍工恭義
电
C
0
5
D
0
6
彳彳u血巴辽弧号t邑.竺巴罗昱I
6、按日期和产品求和
公式：
F2
二SUMPRODUCT（（MONTH（$A$2:
$A$25）=F$1）R（$B$2:
$B$25=$E2
）R$C$2:
$C$25）
说明：
SUMPRODUCT可以完成多条件求和
A
E
CD
E
F'
fl
日期
产品销量）
产品
1
2
3
2
2015-1-9
A
5
A
23
50
26
3
2015-1-10
A
18
B
IS
20
0
4
2015-1-11
B
11
C
U
35
20
2015-1-12
B
5
D
0
16
1
2015-2-10
A
18
S
0
11
0
7
2015-2-11
C
19
E
0
20
0
8
2015-2-15
D
6
F
0
2
.0.
9
2015-2-15
C
13
G
0
10
0
10
2015-2-15
s
11
11
2015-2-18
A
1百
五、查找与引用公式
1、单条件查找公式
公式1:
C11
二VLOOKUP（B11,B3:
F7,4,FALSE）
说明：
查找是VLOOKUP最擅长的，基本用法
ABCDEF
2、双向查找公式
公式：
=INDER（C3:
H7,MATCH（B10,B3:
B7,0）,MATCH（C10,C2:
H2,0））
说明：
利用MATCH函数查找位置，用INDER函数取值
AIB丨CIDEIF]G」HI
:
姓名I1月丨2月$3月i
2丨
10
54
4月丨£月fE月J
=・ni-i!
sIin-n-ii-inif,n-・rr・-・r-・r・・=n-i»-nrif=^
54j5
57
21
8
9
姓名
月份
销售量
11
李栋
，月
12
3、查找最后一条符合条件的记录。
公式：
详见下图
说明：
0/（条件）可以把不符合条件的变成错误值，而lookup可以
忽略错误值
A
B
C
D
E
丄
2
例“查找査的置新单价
3
4
入屋时间
产品屯称
入库单价
5
2012-1-2
A
10
6
2012'1-3
B
34
7
2012^1-4
A
19
2012-1-5
E
25
2012-1-6
A
12
10
2012-1-7
匚1
25
11
13
A
12
14
15
C13-LOOKUP（1,0/（05:
010^813）,05:
010）
4、多条件查找
公式:
详见下图
说明:
公式原理同上一个公式
B
'C
D
E
F
G
■E1例z冬条件查我
^41
灭库时间
产品名称
丄阵单价
25
2012-1-2
A
10
26
2012-1-3
B
?
1
27
2012-1-4
A
19
28
2012-1-5
E
25
291
2012-1-6
A
12
30
2012-1-7
C
25
JJL
331
入库时间
2012-1-4
311
产品名称
A
35）
入阵单价
19
36
171
OO
C35-LOOKUP（1,0/（（B25：
B30=C33）*（C251030=034））,025：
030）
5、指定区域最后一个非空值查找公式;详见下图
说明：
略
A
B
C
D
EI
1
例3：
査找最后一钦还款日期
J
3
姓名
张越
李梅雨
胡秋祎
赵大志
4
J1月
4
r&
5
2月
5
6
r3月
34
4
7
硝
8
|5月
6
6
9
B月
5
4
10
7月
11
8月
12
\9月
75
13
40月
5
14
|"月
7
15
12月
16
匱后一次还敦日期
10戶
6月
f9月
门月
17
18Bl^LOOKUPCl,$A2：
（A13）6、按数字区域间取对应的值
公式：
详见下图
公式说明：
VLOOKUP和LOOKUP函数都可以按区间取值，一定要注意，销售量列的数字一定要升序排列。
六、字符串处理公式
1、多单元格字符串合并
公式：
c2
二PHONETIC（A2:
A7）
说明：
Phonetic函数只能对字符型内容合并，数字不可以
2、截取除后3位之外的部分
公式：
=LEFT（D1丄EN（D1）-3）
说明：
LEN计算出总长度丄EFT从左边截总长度-3个
A^LEFT（D1jLEN®U.-3）
C
1D
E
F
1234567
r|
1迈34
3、截取-前的部分
公式:
B2
二Left（A1,FIND（"-",A1）-1）
说明：
用FIND函数查找位置，用LEFT截取。
1
B
1
科目
—级科目
2
银行存款惟行
银行存款
3
血收账款-张=
血收JK款
4
其他应收款-赵志东
其他应收款
5
6
FT
4、截取字符串中任一段的公式
公式:
B1
二TRIM（MID（SUBSTITUTE（$A1,"",REPT（"",20））,20,20））
说明:
公式是利用强插N个空字符的方式进行截取
A
B
C
D
E
F
1
32561761222
32
56
176
12
22
2
5、字符串查找
公式：
B2
=IF（COUNT（FIND（"河南",A2））=0,"否","是"）
说明:
FIND查找成功，返回字符的位置，否则返回错误值，而
COUNT可以统计出数字的个数，这里可以用来判断查找
是否成功。
A
E
11
地址
是否河南
2
辺逋洛阳市
3
山东济南
否
4
道南南阳
是
5
河北石家庄
否「
6、字符串查找一对多
公式：
B2
=IF（COUNT（FIND（{"辽宁","黑龙江","吉林"},A2））=0,"其他","东北
）
说明：
设置FIND第一个参数为常量数组，用COUNT函数统计
FIND查找结果
A
E
C
1
理止
地区
■-
河育洛阳市
其他
3
山东济南
其他
4
辽爭岭
5
河南南阳
其他
&
河北石家庄
其他
T
李北
9
七、日期计算公式
1、两日期相隔的年、月、天数计算
A1是开始日期（20RR-12-1），B1是结束日期（20RR-6-10）。
计算:
相隔多少天？
=datedif（A1,B1,"d"）结果：
557
相隔多少月？
=datedif（A1,B1,"m"）结果：
18
相隔多少年？
=datedif（A1,B1,"R"）结果：
1
不考虑年相隔多少月?
=datedif（A1,B1,"Rm"）结果：
6
不考虑年相隔多少天?
=datedif（A1,B1,"RD"）结果：
192
不考虑年月相隔多少天?
=datedif（A1,B1,"MD"）结果：
9datedif函数第3个参数说明：
"R"时间段中的整年数。
"M"时间段中的整月数。
"D"时间段中的天数。
"MD"天数的差。
忽略日期中的月和年。
"RM"月数的差。
忽略日期中的日和年。
"RD"天数的差。
忽略日期中的年。
2、扣除周末天数的工作日天数
公式：
C2
二NETWORKDARS.INTL（IF（B2
说明：
返回两个日期之间的所有工作日数，使用参数指示
升级会员 