Excel工作表中的9类21个函数公式应用技巧解读.docx
- 文档编号:26598602
- 上传时间:2023-06-20
- 格式:DOCX
- 页数:33
- 大小:30.57KB
Excel工作表中的9类21个函数公式应用技巧解读.docx
《Excel工作表中的9类21个函数公式应用技巧解读.docx》由会员分享,可在线阅读,更多相关《Excel工作表中的9类21个函数公式应用技巧解读.docx(33页珍藏版)》请在冰豆网上搜索。
Excel工作表中的9类21个函数公式应用技巧解读
Excel工作表中最常用的9类21个函数应用
技巧解读
在Excel工作表中,有一个非常重要的工具,那就是函数公式,如果要全部掌握,几乎是不可能的,但是对于一些常用的公式则必须掌握,例如下文的9类21个公式!
一、Excel工作表函数:
求和类。
(—)Sume
功能:
计算指走的单元格区域中所有数值的和。
语法结构:
=Sum(值1,值2……值N)。
目的:
计算总〃月薪〃。
H3■:
A
B
C
D
E
F
G
H1
1
Excel函数公氏:
9^21个公式应用技巧解读
2
No
员工姓名
年龄
件别
婚姻
学历
刀薪
备注
3
1
仔肃
50
男
未婚
初屮
4735
4
2
袁术
50
已姬
初屮
'2722
5
3
刘备
40
5J
未婚
'4095
6
4
许攸
40
男
12婚
大专
2874
7
5
诃q懿
40
男
已婚
初中
'168
8
6
II夫人
40
女
已姬
大本
'4478
9
7
孙尚香
20
女
未婚
•PV
'3978
10
8
袁绍
30
男
12婚
胡1'
2760
11
9
徐庶
30
男
已婚
大专
3762
12
10
30
男
未婚
大专
'4425
13
重点:
=SUM(I*G3:
G12),
Ctrl+Shift+Enter
Excel工作表中最常用的9类21个公式,动图演示,中文解读方法:
在目标单元格中输入公式:
=SUM(1*G3:
G12),并用Ctrl+Shift+Enter填充。
解读:
如果直接用Sum函数进行求和,结果为0,究其原因就在于〃月薪〃为文本型的数值,如果不想调整数据类型,可以给每个参数乘以1将其强制转换为数值类型,然后用Sum函数进行求和。
(二)Sumifo功能:
对满足条件的单元格求和,即单条件求和。
语法结构:
=Sumif(条件范围,条件,[求和范围]),当〃条件范围〃和〃求和范围〃相同时,可以省略〃求和范围〃。
目的:
根据〃性别〃计算总〃月薪〃。
|K3*I|XV
创ABCIDEFGHIJK
Excels数公川9娄21个公式应川技巧解读
2
No
员工姓名
年龄
性别
婚姻
学历
丿1薪
备注
性别
丿]薪
3
1
50
女
未婚
初中
¥4,735.00
4
2
点术
50
12婚
初屮
¥2,722.00
■
3
刘备
40
女
耒婚
大专
¥4,095.00
6
4
许攸
40
男
12婚
九专
¥2,874.00
7
5
“T矗
40
巳婚
初中
¥168.00
8
6
II•夫人
40
女
已婚
大本
¥4,478.00
9
7
孙尚香
20
女
未婚
中专
¥3,978.00
10
8
犬绍
30
男
巳婚
¥2,760.00
11
9
徐應
50
女
12婚
大专
¥3,762.00
12
10
赵公
30
男
未婚
大专
¥4,425.00
靈点:
=SUMIF(D3:
D12J3,G3:
G12)
13
Excel工作表中最常用的9类21个公式z动图演示z中文解读方法:
在目标单元格中输入公式:
=SUMIF(D3:
D12/J3IG3:
G12)O解读:
由于〃条件范围〃和“求和范围〃不相同,所以不能省略参数〃求和范围〃。
(三)Sumifse
功能:
对一组给走条件指走的单元格求和。
语法结构:
=Sumifs俅和范围,条件1范围,条件1,条件2范围,条
件2……)
目的:
根据〃性别"统计相应〃学历"下的总〃月薪〃。
【13・:
K2$]
Excels数公式:
9类21个公式应川技巧解欣
2
No
员工姓名
年龄
性别
婚烟
学历
备注
性別
3
1
50
女
未婚
初屮
¥4.735.00
人本
4
2
袁术
50
L1婚
初屮
¥2.722.00
5
3
刘备
40
女
未婚
人专
¥4,095.00
6
4
许攸
40
7J
L1婚
人专
¥2.874.00
7
5
4(1
男
12婚
初屮
¥168.00
8
6
卄夫人
40
女
12婚
人木
¥4478.0()
9
7
孙尚香
20
女
未婚
中专
¥3,978.00
10
8
点绍
30
12娜
廐屮
¥2.760.00
11
9
30
女
L1婚
¥3.762.00
12
10
30
yj
未婚
人专
¥4.425.00
簸点:
3MilS((;3:
(;12J)3:
1)12J3.I3:
112.K3)
13
Excel工作表中最常用的9类21个公式,动图演示,中文解读方法:
在目标单元格中输入公式:
=SUMIFS(G3:
G12/D3:
D12J3,F3:
F12ZK3)O
解读:
参数〃条件范围〃和〃条件"必须成对出现,否则公式无法正确执行!
二、Excel工作表函数:
判断类。
(—)If。
功能:
判断是否满足某个条件,如果满足返回一个值,如果不满足则返回另一个值。
语法结构:
=If(判断条件,条件为真时的返回值,条件为假时的返回值)。
目的:
判断〃月薪〃的档次,如果>4000,则返回“高〃,如果
>3000,则返回〃中〃,否则返回〃低〃。
H3▼
X2去1
A
B
C
D
E
F
G
H
1
Excels数公式:
9类21个公式应用技巧解读
2
No
员工姓鸽
年龄
性别
婚姻
学历
丿惭
备注
3
1
50
女
未婚
初中
¥4,735.00
4
2
袁术
50
男
12婚
初中
¥2,722.00
5
3
刘备
40
女
未婚
大专
¥4,095.00
6
4
许攸
40
男
已婚
大专
¥2,874.00
7
5
司马懿
40
男
12婚
初中
¥168.00
8
6
II夫人
40
女
LL婚
大木
¥4,478.00
9
7
孙尚香
20
女
未婚
中专
¥3,978.00
10
8
袁绍
30
男
已婚
高屮
¥2,760.00
11
9
徐庶
30
女
已婚
¥3,762.00
12
10
30
男
耒婚
大方
¥4,425.00
13
重点;
=IF(G3>4000畀矗”JF(G3>=3000,”屮H,IF(G3<3000,n低”)))
Excel工作表中最常用的9类21个公式,动图演示,中文解读方法:
在目标单元格中输入公式:
=IF(G3>=4000znB,,,IF(G3>=3000/n中n,IF(G3<3000/低”)))。
解读:
If函数除了单独判断外,还可以嵌套使用,但多级嵌套时,需要理清逻辑关系,否则容易出错!
(二)Ifs。
功能:
判断是否满足一个或多个条件并返回与第一个TRUE条件对应的值。
语法结构:
=Ifs(条件1,返回值1,条件2,返回值2……)。
目的:
判断〃月薪〃的档次,如果>4000,则返回〃高如果
重点:
=IFS(G3>=:
4000J高需G3X3O0OJ屮r\G3<3000,M低T
>3000,则返回〃中〃,否则返回〃低〃。
A
B
C
D
E
F
G
H
1
Excel函数公式:
9类21个公氏应川技巧解读
2
No
M工姓名
年龄
性别
婚姻
学历
刀薪
备注
3
1
魯肃
50
女
未婚
初屮
¥4,735.00
4
2
袁术
50
男
已婚
初屮
¥2,722.00
5
3
刘备
40
女
未婚
大专
¥4,095.00
6
4
许攸
40
男
已婚
大专
¥2,874.00
7
5
司马謎
40
男
巳婚
初中
¥168.00
8
6
it夫人
40
女
12婚
人木
¥4,478.00
9
7
孙尚香
20
女
耒婚
中专
¥3,978.00
10
8
哀绍
30
男
12婚
¥2,760.00
11
9
徐庶
30
女
已婚
大专
¥3,762.00
12
10
赵云
30
男
未婚
大专
¥4,425.00
H3
13
Excel工作表中最常用的9类21个公式,动图演示,中文解读方法:
在目标单元格中输入公式:
=IFS(G3>=4000;*高鳥G3>=3000「中n,G3<3000/"低”)。
解读:
参数中的〃条件〃和〃返回值〃必须成对出现,但该函数仅能应用于16及以上版本的Excel中在WPS高版本中也可以使用哦!
三、Excel工作表函数:
查找类。
(—)Vlookupo
功能:
搜索表区域首列满足条件的元素,确走待检索单元格在区
域中的的序号,再进一步返回选定单元格的值。
语法结构:
"lookup(查询值,数据范围返回值列数查询模式),查询模式分为精准查询和模糊查询。
目的:
根据〃员工姓名〃查询对应的〃月薪"。
K3-:
X
ABCIDEFGHIJK
EXC创南数公式:
9矣21个公氏应川技巧解读
2
\0
员工姓名
年龄
性别
婚姻
学历
J]蒔
备注
员工姓名
3
1
50
女
未婚
初中
¥4.735.00
4
2
袁术
50
男
L2婚
初屮
¥2.722.00
5
3
刘备
40
女
未婚
大专
¥4.095.00
6
4
许攸
40
巳婚
¥2.874.00
7
5
40
巳婚
初中
¥168.00
8
6
II决人
4(1
女
巳婚
大本
¥4,478.00
9
7
孙尚香
20
女
未婚
•I'/;
¥3.97«.(W
10
8
袁紹
30
男
L1婚
rt'l'
¥2,760.00
11
9
徐虑
30
女
巳婚
大专
¥3.762.00
12
10
赵云
30
未婚
kV
¥4425.00
K
ffi点:
=VLOOKUP(J3,B3:
G12.6.0)
13
Excel工作表中最常用的9类21个公式z动图演示z中文解读方法:
在目标单元格中输入公式:
=VLOOKUP(J3/B3:
G12/6/0)o解读:
参数〃返回值列数〃要根据〃数据范围〃来确走,是返回值所在的的相对列数。
(二)Lookupo
功能:
从单行或单列或单数组中直找一个值。
Lookup函数具有两种语法结构:
向量形式和数组形式。
1、向量形式。
功能:
从单行或单列中查找查找指走的值,返回第二个单行或单
列中相同位置的值。
语法结构:
二Lookup(查找值,查找值所在范围,[返回值所在范围]),当〃查找值所在范围〃和’返回值所在范围〃相同时,可以省略〃返回值所在范围〃。
目的:
根据〃员工姓名〃查询对应的〃月薪"。
莎・龙白动袂行
兰冃二廷壬宫合开后居中-
自电义
□E!
気中§式匸用・$枱怡式・
好
对齐方式血砖尺I样h
A2-;XJlNo
JAlBCD£FGHI_JC
1
・・・・|
Exwl^l数公式
:
9类21个公式应川技卩解谟
2
No
员工姓笛
年龄
件别
婚姻
学历
丿1新
备注
员工姓名
丿帰
3
1
50
女
未婚
初屮
¥4,735.00
赵止
4
2
袁术
50
男
初屮
V2J22.00
5
3
刘倚
40
女
未婚
大专
¥4,095.00
6
4
许攸
40
男
巳婚
大专
¥2.874.00
7
5
40
巳婚
初中
¥168.00
8
6
II夫人
40
女
巳婚
人本
¥4.478.00
9
7
孙尚否
20
女
中专
¥3,978.00
10
8
30
C婚
高屮
¥2,76».(10
11
9
徐庇
30
坎
巳婚
大专
¥3,762.00
12
10
赵公
30
9J
未婚
¥4,425.00
13
巫点:
=LOOKlP(J3,B3:
Bl2/;3:
G12)
N
Excel工作表中最常用的9类21个公式z动图演示z中文解读
方法:
1、以〃员工姓名〃为主要关键字进行升序排序。
2、在目标单元格中输入公式:
=LOOKUP(J3zB3:
B12zG3:
G12)o解读:
在使用Lookup函数查询数据时,首次要以〃查询值〃为主要关键字进行升序排序,否则无法得到正确的结果。
2、数组形式。
功能:
从指走的范围第一列或第一行中查询指走的值,返回指走范围中最后一列或最后一行对应位置上的值。
语法:
=Lookup(直找值,查询范围)。
重点解读:
从〃功能〃中可以看出lookup函数的数组形式,直找值必须在直询范围的第一列或第一行中,返回的值必须是查询范围的最后一列或最后一行对应的值。
即:
查找值和返回值在查询范围的〃两XLU//
U而O
目的:
根据〃员工姓名〃查询对应的〃月薪"。
文件
开址1
腹入页面布咼血visaa
PDFIMSQ探作谀明瘦欢
:
X男切盘电咖・
方正"阪宋酥・M•AA
三匡k
9>・ft自动换行
自左义
-
E®爭議
壬廷圉合开竝虽中■
“it
<»1
却柜式匸用好・叙甜式・‘
芙贴板Q
芋休n
如
A2-iX✓No
1
2
ExwIrflSc公式:
9类21个公式应川技耳解读
No
员工姓名
佯龄
性别
婚姻
学历
丿用
备注
员工姓名
丿慚
3
1
50
女
未婚
初屮
¥4,735.00
或术
4
2
袁术
50
男
巳嬉
初屮
¥2.722.00
5
3
刘倚
40
女
未婚
大专
V4XW5.00
6
4
许攸
40
大专
¥2.874.00
7
5
40
巳婚
初中
¥168.00
8
6
II夫人
40
女
巳婚
人本
¥4478.00
9
7
孙尚香
20
女
未婚
中专
¥3.978.00
10
8
袁绍
30
c婚
拓屮
¥2,760.00
11
9
徐庇
30
女
大专
¥3,762.00
12
1()
赵公
30
未婚
大专
¥4,425.0(1
動铁:
=UM)KUP(J3,B3:
G12)卜
13.R
Excel工作表中最常用的9类21个公式,动图演示,中文解读方法:
1、以〃员工姓名〃为主要关键字进行升序排序。
2、在目标单元格中输入公式:
=LOOKUP(J3zB3:
G12)e解读:
直询值必须在数据范围的第一列,返回值必须在数据范围得最后一列。
3、优化形式。
目的:
根据〃员工姓名〃查询对应的〃月薪"。
K3
W
X7
fx
A,
B
C
D
E
F
G
H1
)
K
ExccIHq数公式:
9娄21个公式就用技巧解渎
No
员匸姓名
年龄
性别
婚姻
学历
备注
员工姓名
月瞬
1
50
女
未婚
初屮
¥4,735.00
■
百术
50
巳婚
初中
¥2.722.()0
3
刘备
40
女
¥4.095.00
4
许攸
40
12婚
大专
¥2.874.00
5
40
男
巳婚
初屮
¥168.00
6
II•夫人
40
女
巳婚
大木
¥4,478.00
7
孙尚香
20
女
未婚
中专
¥3.978.00
8
丸绍
30
Ll«
¥2,760.00
9
徐贬
30
女
El婚
大专
¥3,762.(10
1()
赵公
30
男
未婚
大专
¥4.425.00
a
贡点:
=LOOKLP(1.0/(B3:
B12=J3^G3:
G12)
13
Excel工作表中最常用的9类21个公式,动图演示,中文解读方法:
在目标单元格中输入公式:
=LOOKUP(l/0/(B3:
B12=J3)/G3:
G12)o解读:
〃优化形式〃其本质还是向量形式,但在此必须了解Lookup函数的一个特走,就是当查询不到指走的值时,会自动向下匹配,原则为小于当前值的最大值。
如果公式中的条件不成立,则返回错误值,如果公式成立,则返回0,小于查询值的最大值为0,所以返回相应位置的值。
四、Excel工作表函数:
统计类。
(—)Countifo
功能:
计算指走区域中的满足条件的单元格数量,即单条件计数。
语法结构:
二Countif(条件范围,条件)。
目的:
计算〃月薪〃在指定范围内的人数。
2
No
MH姓名
年龄
性别
婚姻
学历
丿怡
备注
丿怡
人数
3
1
50
女
未婚
初屮
¥4,735.00
4000
4
2
克术
50
巳婚
初屮
¥2522.00
5
3
刘备
40
女
未婚
大专
¥4,095.011
6
4
许攸
40
9J
巳婚
大专
¥2.874.00
7
5
40
初屮
¥168.00
8
6
II夫人
40
矢
巳婚
人本
¥4478.00
9
7
孙尚否
2()
女
未娥
中专
¥3.978.00
10
8
30
C婚
為屮
¥2.760.0(1
11
9
徐底
30
女
巳婚
大专
¥3562.00
12
10
赵公
3«
未婚
¥4,425.00
1
K3
/A]BCDEFGHIJK
ExcdHq数公式:
9^21个公式旬川技巧解谟
重点:
=COLMIF(G3:
G12;f>M&J3)
13
Excel工作表中最常用的9类21个公式,动图演示,中文解读方法:
在目标单元格中输入公式:
=COUNTIF(G3:
G12/n>n&J3)o解读:
条件计数函数除了Count讦函数外,还有多条件计数函数
Countifso
(二)Average讦s。
功能:
查找一组给走条件指走的单元格的算术平均值。
语法结构:
=Averageifs(数值范围,条件1范围,条件1,条件2范围,条件2……)
目的:
根据〃性别〃统计相应〃学历〃下的平均〃月薪〃。
L3■
X7fi
BCDEFGH
Excels数公式:
啖21个公式应川技巧解渎
No
员工姓名
性别
婚姻
学历
丿燉
性别
学历
丿惓
1
鲁41
50
女
初屮
¥4,735.00
人专
1
丸术
50
LL姬
初中
¥2.722.00
3
刘笛
40
女
术娥
人专
¥4,095.00
4
40
L1婚
人专
¥2.874.00
5
40
LL4S
初中
¥168.00
6
片夫人
40
女
匕娥
人本
¥4,478.00
7
孙尚祈
20
女
屮专
¥3.978.00
8
点绍
30
庙屮
¥2.760.00
9
徐應
30
女
L1W
¥3,762.00
0
10
30
人专
¥4.425.00
雨点:
=AVER\(;KIKS«;3:
(;12J)3:
l)l2J3J3:
H2,K3i
13
Excel工作表中最常用的9类21个公式,动图演示,中文解读方法:
在目标单元格中输入公式
=AVERAGEIFS(G3:
G12ZD3:
D12/J3/F3:
F12ZK3)O
解读:
五、Excel工作表函数:
提
类。
参数〃条件范围〃和〃条件〃必须成对出现。
(-)Left。
功能:
从一个字符串中的第一个字符开始返回指走个数的字符。
语法结构:
=Left(字符串,[字符长度]),当省略〃字符长度〃时,默认值为lo目的:
提取〃员工姓名〃中的〃姓〃。
H3-:
XJfx
A
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 工作 中的 21 函数 公式 应用 技巧 解读