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”)。

声明:所有白马号原创内容,未经允许禁止任何网站及个人转载、采集等一切非法引用。本站已启用原创保护,有法律保护作用,否则白马号保留一切追究的权利。发布者:白马号,转转请注明出处:https://www.bmhysw.com/article/6175.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫

相关推荐

  • 摩托罗拉消失了很久,不过现在全球首款2亿像素级别骁龙8 Plus由摩托罗拉打造

    3月30日消息,博主@数码闲聊站曾爆料,摩托罗拉正在测试骁龙8 Plus旗舰处理器,这款新旗舰将在今年Q2登场。@数码闲聊站称,摩托罗拉骁龙8 Plus旗舰测试了两款Sensor,一款为2亿像素的三星HP1,一款为1.94亿像素,摩托罗拉有可能会拿到骁龙8 Plus的****权。 对此,联想集团中国区手机业务部总经理陈劲表示,摩托罗拉新旗舰使用的Sensor…

    2022-03-30
    00
  • Win10上帝模式如何开启?Win10开启上帝模式的方法

    Win10上帝模式如何开启?Win10开启上帝模式的方法 什么是Win10上帝模式? Win10上帝模式的好处有哪些? 如何开启Win10上帝模式? 总结 什么是Win10上帝模式? Win10上帝模式是Windows 10操作系统中的一种特殊模式,也被称为管理员模式或超级用户模式。在上帝模式下,用户可以获得更高的系统访问权限,以便执行需要管理员权限的任务。…

    2023-07-21
    00
  • Win10专业版怎么在桌面上显示“此电脑”图标?

    Win10专业版怎么在桌面上显示“此电脑”图标? 步骤一:右键点击桌面空白处 步骤二:选择“个性化” 步骤三:选择“主题” 步骤四:选择“桌面图标设置” 步骤五:勾选“计算机” 步骤六:点击“应用” 步骤一:右键点击桌面空白处 在桌面上任意位置右键点击,弹出快捷菜单。 步骤二:选择“个性化” 在快捷菜单中选择“个性化”选项,进入个性化设置页面。 步骤三:选择…

    2023-07-22
    00
  • Win7共享打印机设置方法,实现打印机共享

    Win7共享打印机设置方法,实现打印机共享 准备工作 共享打印机设置 连接共享打印机 准备工作 在进行共享打印机设置之前,需要确保以下几个条件: 所有需要共享打印机的电脑都连接在同一个局域网内。 所有电脑都已经安装好了打印机驱动程序。 打印机已经连接在一台电脑上,并且已经可以正常使用。 共享打印机设置 按照以下步骤进行共享打印机设置: 在连接了打印机的电脑上…

    2023-06-05
    00
  • 电脑的好处有哪些(大一新生买电脑的五大理由)

    在大一新生开学前,有一个问题一直困扰着历届学生,那就是要不要给学生买电脑的问题,对此大部分家长认为都不需要,害怕学生沉迷网游会耽误学业,因为这一个理由,大部分家庭都不会给学生买电脑,如今的大部分家长也是这样的考虑,如果你也这样想,你的认知和思维就落伍了。 大学的时间很宽松,而且周围网吧很多,学生如果沉迷网游,即便是不给学生买电脑,学生也会去网吧玩,该被耽误照…

    2022-05-23 投稿
    00

联系我们

QQ:183718318

在线咨询: QQ交谈

邮件:183718318@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

关注微信