Excel工作表中的9类21个函数公式应用技巧解读.docx
- 文档编号:27148285
- 上传时间:2023-06-27
- 格式:DOCX
- 页数:21
- 大小:1.25MB
Excel工作表中的9类21个函数公式应用技巧解读.docx
《Excel工作表中的9类21个函数公式应用技巧解读.docx》由会员分享,可在线阅读,更多相关《Excel工作表中的9类21个函数公式应用技巧解读.docx(21页珍藏版)》请在冰豆网上搜索。
Excel工作表中的9类21个函数公式应用技巧解读
Excel工作表中最常用的9类21个函数应用
技巧解读
在Excel工作表中,有一个非常重要的工具,那就是函数公式,如果要全部掌握,几乎是不可能的,但是对于一些常用的公式则必须掌握,例如下文的9类21个公式!
一、Excel工作表函数:
求和类。
(一)Sum。
功能:
计算指定的单元格区域中所有数值的和。
语法结构:
=Sum(值1,值2……值N)。
目的:
计算总“月薪”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=SUM(1*G3:
G12),并用Ctrl+Shift+Enter填充。
解读:
如果直接用Sum函数进行求和,结果为0,究其原因就在于“月薪”为文本型的数值,如果不想调整数据类型,可以给每个参数乘以1将其强制转换为数值类型,然后用Sum函数进行求和。
(二)Sumif。
功能:
对满足条件的单元格求和,即单条件求和。
语法结构:
=Sumif(条件范围,条件,[求和范围]),当“条件范围”和“求和范围”相同时,可以省略“求和范围”。
目的:
根据“性别”计算总“月薪”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=SUMIF(D3:
D12,J3,G3:
G12)。
解读:
由于“条件范围”和“求和范围”不相同,所以不能省略参数“求和范围”。
(三)Sumifs。
功能:
对一组给定条件指定的单元格求和。
语法结构:
=Sumifs(求和范围,条件1范围,条件1,条件2范围,条件2……)
目的:
根据“性别”统计相应“学历”下的总“月薪”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=SUMIFS(G3:
G12,D3:
D12,J3,F3:
F12,K3)。
解读:
参数“条件范围”和“条件”必须成对出现,否则公式无法正确执行!
二、Excel工作表函数:
判断类。
(一)If。
功能:
判断是否满足某个条件,如果满足返回一个值,如果不满足则返回另一个值。
语法结构:
=If(判断条件,条件为真时的返回值,条件为假时的返回值)。
目的:
判断“月薪”的档次,如果≥4000,则返回“高”,如果≥3000,则返回“中”,否则返回“低”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=IF(G3>=4000,"高",IF(G3>=3000,"中",IF(G3<3000,"低")))。
解读:
If函数除了单独判断外,还可以嵌套使用,但多级嵌套时,需要理清逻辑关系,否则容易出错!
(二)Ifs。
功能:
判断是否满足一个或多个条件并返回与第一个TRUE条件对应的值。
语法结构:
=Ifs(条件1,返回值1,条件2,返回值2……)。
目的:
判断“月薪”的档次,如果≥4000,则返回“高”,如果≥3000,则返回“中”,否则返回“低”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=IFS(G3>=4000,"高",G3>=3000,"中",G3<3000,"低")。
解读:
参数中的“条件”和“返回值”必须成对出现,但该函数仅能应用于16及以上版本的Excel中,在WPS高版本中也可以使用哦!
三、Excel工作表函数:
查找类。
(一)Vlookup。
功能:
搜索表区域首列满足条件的元素,确定待检索单元格在区域中的的序号,再进一步返回选定单元格的值。
语法结构:
=Vlookup(查询值,数据范围,返回值列数,查询模式),查询模式分为精准查询和模糊查询。
目的:
根据“员工姓名”查询对应的“月薪”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=VLOOKUP(J3,B3:
G12,6,0)。
解读:
参数“返回值列数”要根据“数据范围”来确定,是返回值所在的的相对列数。
(二)Lookup。
功能:
从单行或单列或单数组中查找一个值。
Lookup函数具有两种语法结构:
向量形式和数组形式。
1、向量形式。
功能:
从单行或单列中查找查找指定的值,返回第二个单行或单列中相同位置的值。
语法结构:
=Lookup(查找值,查找值所在范围,[返回值所在范围]),当“查找值所在范围”和“返回值所在范围”相同时,可以省略“返回值所在范围”。
目的:
根据“员工姓名”查询对应的“月薪”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
1、以“员工姓名”为主要关键字进行升序排序。
2、在目标单元格中输入公式:
=LOOKUP(J3,B3:
B12,G3:
G12)。
解读:
在使用Lookup函数查询数据时,首次要以“查询值”为主要关键字进行升序排序,否则无法得到正确的结果。
2、数组形式。
功能:
从指定的范围第一列或第一行中查询指定的值,返回指定范围中最后一列或最后一行对应位置上的值。
语法:
=Lookup(查找值,查询范围)。
重点解读:
从“功能”中可以看出,Lookup函数的数组形式,查找值必须在查询范围的第一列或第一行中,返回的值必须是查询范围的最后一列或最后一行对应的值。
即:
查找值和返回值在查询范围的“两端”。
目的:
根据“员工姓名”查询对应的“月薪”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
1、以“员工姓名”为主要关键字进行升序排序。
2、在目标单元格中输入公式:
=LOOKUP(J3,B3:
G12)。
解读:
查询值必须在数据范围的第一列,返回值必须在数据范围得最后一列。
3、优化形式。
目的:
根据“员工姓名”查询对应的“月薪”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=LOOKUP(1,0/(B3:
B12=J3),G3:
G12)。
解读:
“优化形式”其本质还是向量形式,但在此必须了解Lookup函数的一个特定,就是当查询不到指定的值时,会自动向下匹配,原则为小于当前值的最大值。
如果公式中的条件不成立,则返回错误值,如果公式成立,则返回0,小于查询值的最大值为0,所以返回相应位置的值。
四、Excel工作表函数:
统计类。
(一)Countif。
功能:
计算指定区域中的满足条件的单元格数量,即单条件计数。
语法结构:
=Countif(条件范围,条件)。
目的:
计算“月薪”在指定范围内的人数。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=COUNTIF(G3:
G12,">"&J3)。
解读:
条件计数函数除了Countif函数外,还有多条件计数函数Countifs。
(二)Averageifs。
功能:
查找一组给定条件指定的单元格的算术平均值。
语法结构:
=Averageifs(数值范围,条件1范围,条件1,条件2范围,条件2……)
目的:
根据“性别”统计相应“学历”下的平均“月薪”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=AVERAGEIFS(G3:
G12,D3:
D12,J3,F3:
F12,K3)。
解读:
参数“条件范围”和“条件”必须成对出现。
五、Excel工作表函数:
提取类。
(一)Left。
功能:
从一个字符串中的第一个字符开始返回指定个数的字符。
语法结构:
=Left(字符串,[字符长度]),当省略“字符长度”时,默认值为1。
目的:
提取“员工姓名”中的“姓”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=LEFT(B3,1)。
解读:
也可以使用公式:
=LEFT(B3)。
(二)Mid。
功能:
从指定字符串中的指定位置起返回指定长度的字符。
语法结构:
=Mid(字符串,开始位置,字符长度)。
目的:
返回“月薪”中的第2、3位。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=MID(G3,2,2)。
六、Excel工作表函数:
日期类。
(一)Datedif。
功能:
以指定的方式计算两个日期之间的差值。
语法结构:
=Datedif(开始日期,结束日期,统计方式),常见的统计方式有“Y”、“M”、“D”,即“年”、“月”、“日”。
目的:
计算距离2021年元旦的天数。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=DATEDIF(TODAY(),"2021-1-1","d")。
解读:
用Today函数获取当前日期,计算距离2021年1月1日的天数。
(二)Days。
功能:
返回两个日期之间的天数。
语法结构:
=Days(结束日期,开始日期)。
目的:
计算距离2021年元旦的天数。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=DAYS("2021-1-1",TODAY())。
解读:
Days函数的参数顺序为“结束日期”、“开始日期”,而并不是“开始日期”、“结束日期”,和Datedif函数要区别使用!
七、Excel工作表函数:
数字处理类。
(一)Round。
功能:
按指定的位数对数值四舍五入。
语法结构:
=Round(值或单元格引用,小数位数)。
目的:
对“月薪”四舍五入后保留2位小数。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=ROUND(G3,2)。
(二)Randbetween。
功能:
返回介于指定的数值之间的随机值。
语法结构:
=Randbetween(下限值,上限值)。
目的:
生成1000至2000之间的随机值。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=RANDBETWEEN(1000,2000)。
解读:
如果要更改随机值,按F9即可。
八、Excel工作表函数:
数据库函数。
(一)Dsum。
功能:
求满足给定条件的数据库中记录的字段(列)数据的和。
语法结构:
=Dsum(数据库区域,返回值所在的相对列数(列标题的相对引用、列标题),条件区域)。
注意事项:
1、参数“数据库区域”和“条件区域”必须包含有效的列标题。
2、第二个参数用“列标题”作为返回依据时,其值必须包含在""(英文双引号)中,如“月薪”、“婚姻”等。
目的:
根据“性别”统计“月薪”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=DSUM(B2:
G12,"月薪",J2:
J3)。
(二)Dget。
功能:
从数据库中提取符合指定条件且唯一存在的记录。
语法结构:
=Dget(数据库区域,返回值所在的相对列数(列标题的相对引用、列标题),条件区域)。
注意事项:
1、参数“数据库区域”和“条件区域”必须包含有效的列标题。
2、第二个参数用“列标题”作为返回依据时,其值必须包含在""(英文双引号)中,如“月薪”、“婚姻”等。
目的:
根据“员工姓名”查询对应的“月薪”。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=DGET(B2:
G12,"月薪",J2:
J3)。
九、Excel工作表函数:
其他类。
(一)Len。
功能:
返回文本字符串中的文本个数。
语法结构:
=Len(值或单元格引用)。
目的:
计算“员工姓名”的长度。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=LEN(B3)。
(二)Cell。
功能:
返回引用中第一个单元格的格式,位置或内容的有关信息。
语法结构:
=Cell(返回类型,[单元格区域])。
目的:
显示当前工作表的文件路径。
Excel工作表中最常用的9类21个公式,动图演示,中文解读
方法:
在目标单元格中输入公式:
=CELL("filename")。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 工作 中的 21 函数 公式 应用 技巧 解读
![提示](https://static.bdocx.com/images/bang_tan.gif)