工作2年,我整理了40个函数公式(最新版),职场必备,建议收藏!
哈喽,大家好呀!今天是周五,周五愉快~~~小福利,最后再来给大家整理一期常用的函数公式!包括求和类、查找类、判断类、统计类、拆分类!注:此篇文章是最新版哦,涉及到一些新函数,建议收藏。
一、求和公式 (包括单条件求和、多条件求和、按日期区间求和、合并单元格求和、按月份求和、动态求和)
1、单条件求和
计算出西瓜的总销量数据,在F2单元格写入公式=SUMIF(B:B,E2,C:C),回车确定。
2、多条件求和
在G2单元格中写入公式=SUMIFS(C:C,B:B,F2,A:A,E2)
3、按照日期区间求和
在H2单元格中写入公式=SUMIFS(C:C,A:A,E2,A:A,F2,B:B,G2)
4、利用通配符模糊求和
我们需要查找地区是“*西部”的对应利润,实际地区中有“西部”、“中西部”。
在B10单元格中写入公式=SUMIF(A2:A7,A10,B2:B7)
5、合并单元格的求和
6、按月份求和
输入公式 =SUM((MONTH(A2:A19)=D2)*B2:B19)
7、根据月份自动汇总1-N月份之和
选取1月,显示1月份的值
选取5月,汇总1-5月之和
选取12月,汇总1-12月之和
输入公式=SUM(OFFSET($B2,,,,MATCH($N$1,$B$1:$M$1,0)))
二、查找类公式
(屏蔽错误值查找、反向查找、多条件查找、借用COLUMN、MATCH实现多个查找,多表查询)
1、VLOOKUP IFERROR——消除查找错误值的影响
如果不想看到查找错误值,就可以借助IFERROR函数来隐藏错误值。
公式为:=IFERROR(VLOOKUP($K2,$A:$I,3,0),"工号有误")
2、反向查找数据
通过姓名来匹配公号,可以输入公式:=INDEX(A:A,MATCH(D2,B:B,0))
3、多条件查找
VLOOKUP的第一参数还支持用&连接多个单元格的内容,用于多条件查询:
4、借用COLUMN,搞定有序变化的返回列
如图所示,要找出A、B、D产品在1、2、3月的销量。
=VLOOKUP($A14,$A$1:$G$10,COLUMN(B2),FALSE)
5、MATCH——自动识别返回列
如果是1月、3月、5月的序列。
=VLOOKUP($A14,$A$2:$G$10,MATCH(B$13,$A$1:$G$1,0),FALSE)
6、借助INDIRECT函数可以实现多表查询数据
如下图所示:
三、判断类公式
(单条件判断、多条件判断、)
1、单条件判断
当商品库存大于等于30时,在“库存提示”处显示“充足”,小于“30”时,显示“补货”。
=IF($D5>=30,"充足","补货")
2、多条件判断
年会摸奖,摸到红色条,奖励微波炉,摸到绿色条奖励自行车,摸到蓝色条奖励数码相机。
C2中输入公式:
=IF(B2="红色","微波炉",IF(B2="绿色","自行车","数码相机"))
3、且字判断
成为优秀青年有两个并列的条件:年龄小于30,成绩大于90。这个时候我们就需要使用AND函数把两个条件并列在一起。
在D2中输入公式:
=IF(AND(B2<30,C2>90),"优秀青年","")
4、或字判断
譬如:根据销量或盈利金额判定产品是否畅销。只有销量大于1000或者盈利大于10000的产品属于畅销产品。
用OR函数把条件组在一起。D2中输入公式:
=IF(OR(B2>1000,C2>10000),"畅销","不畅销")
四、时间类函数
1、根据出生日期计算年龄
公式:=DATEDIF(D2,TODAY(),"y")
2、根据身份证号码计算年龄
3、根据入职日期计算员工工龄
4、转换日期格式
输入公式:=TEXT(C2,"e年mm月dd日 aaaa")
5、计算指定日期所在月份的工作日天数(不含周末)
输入公式=NETWORKDAYS(EOMONTH(A2,-1) 1,EOMONTH(A2,0))
6、计算员工转正日期
在D2单元格中输入公式=EDATE(B2,C2),并向下复制到D10单元格。
五、统计类公式
1、基本的统计公式
如下图所示:
2、通配符模糊统计
比如,当需要查找王姓员工数量,可以输入公式=COUNTIF(A2:A14,"王*"),然后回车。
如果要查找姓名为三个字的员工数量,可以输入公式=COUNTIF(A2:A14,"???"),然后回车。
3、条件中可以嵌入函数公式
比如统计业绩大于平均值的人数,输入公式:
=COUNTIF(D2:D14,">="&AVERAGE(D2:D14))即可。
4、多个条件进行统计
如上图所示,{"销售一部","销售二部"}是数组,作为COUNTIF的条件,返回两个值{5,4},然后再用SUM函数进行相加,得到结果。
5、按类别或组别编号
譬如按部门编号,如下:
6、多条件计数
譬如统计业绩>5000,<10000的人数,输入公式=SUM(COUNTIF(D24:D36,{">5000",">10000"})*{1,-1})即可。
六、提取函数
(TEXTBEFORE,TEXTAFTER)
1、按符号提取个人信息的姓名和手机号
冒号前的内容就是姓名,公式为=TEXTBEFORE(A2,":");
冒号后的内容就是手机号,公式为=TEXTAFTER(A2,":")。
2、TEXTBEFORE和TEXTAFTER组合提取
提取QQ号的公式=TEXTBEFORE(TEXTAFTER(A2,":"),"@")
3、启动第六参数,替换掉错误值
=TEXTBEFORE(B2,"g",,1,,"非克计量")
写在最后:感谢大家一直以来的支持啦,祝你们升职加薪,前程似锦!
今天才知道,微信红包和微信转账区别这么大?叮嘱家人别再乱用了
朋友们大家好,我是小俊,微信可以说我们每天都会使用,我们常常会用来给好友转账和发红包,但是转账和发红包,其实是有区别的,大家知道吗?那今天小俊就给大家详细来聊聊,微信转账和红包之间的区别,大家了解之后,就不会再用错了,不知道的朋友,建议点赞收藏加关注,方便以后需要的时候,可以快速找到,接下来就跟着小俊一起来学习他的知识!第一个区别,上限不同!站长网2023-07-29 10:00:470000这个函数,比VLOOKUP好用10倍,不限版本,简单易懂
哈喽,大家好。许多人都看到过这对函数组合,却不知它比想象中更加强大!下面,就用八种经典应用来给大家介绍一下这个函数吧!此篇是入门级,适合新手查看。INDEX语法:(数组或区域,行号,列号)1、调取一列数据中的第几个数字公式=INDEX(B:B,6)可以得到B列的第六个数据;注解:5月不是这列的第五个,因为有个表头。站长网2023-07-28 14:20:250000一文搞定Excel日期类求和,按年/季度/月/周/天,建议收藏!
Excel中如何用函数公式按季度求和?如何按周求和?无论条件如何变化,它的本质就是以日期为条件的求和,我们都可以用一个函数来搞定,那就是SUMIFS!下面,跟着我们一起来看看吧。1.用函数公式按季度求和方法第一种:把求和条件具化为起止日期进行多条件求和站长网2023-07-28 13:11:070000Photoshop(简称PS)是一款常用的图像处理软件,其中收缩选区是一项非常实用的功能。
本文将介绍收缩选区的操作步骤及功能。一、收缩选区的操作步骤打开需要操作的图像,在工具栏中选择“套索工具”(或“魔术棒工具”)。使用套索工具(或魔术棒工具)选中需要收缩的区域。在菜单栏中选择“选择”->“收缩”,弹出“收缩选区”对话框。在对话框中输入需要收缩的像素数,点击“确定”即可完成收缩选区的操作。二、收缩选区的功能站长网2023-07-28 10:41:520000如何使用图案生成器的方法?
使用图案生成器的具体步骤可能因为不同的图案生成器而略有不同,但是大致流程如下:选择一个合适的图案生成器。可以在互联网上搜索相关的图案生成器,也可以使用自己的设计软件(如Photoshop、Illustrator等)中的图案生成器工具。打开图案生成器并选择一个基础图案或模板。有些图案生成器会提供默认的基础图案或模板,有些则需要使用者自己上传或选择。站长网2023-07-27 11:56:090000