小伙伴们好啊,今天老祝和大家分享几个常用函数的用法,日积月累,也能提高工作效率 。
1、计算年龄
如下图,要根据B列的员工出生年月,计算年龄 。
C2单元格公式为:
=DATEDIF(B2,TODAY(),”y”)
文章插图
TODAY函数返回系统当前的日期 。
DATEDIF函数用于计算两个日期之间的间隔 。用法为:
DATEDIF(起始日,截止日,间隔的类型)
DATEDIF函数以B2的出生年月作为开始日期,以系统日期作为结束日期,第三参数使用“Y”,表示计算两个日期之间的整年数 。
如果要计算间隔的月数,第三参数可以使用“m” 。
2、计算转正日期
如下图,要根据B列的员工入职日期和C列的试用期月数,计算员工转正日期 。
D2单元格公式为:
=EDATE(B2,C2)
文章插图
EDATE函数用于指定某个日期N月之后或是N月之前的日期 。
用法为:
=EDATE(开始日期,指定月份)
3、按条件求和
如下图所示,要统计不同门店的销售额 。F3单元格公式为:
=SUMIF(B:B,E3,C:C)
文章插图
SUMIF函数常规用法为:
=SUMIF(条件区域,求和条件,求和区域)
如果条件区域中的内容符合指定的求和条件,就对对应的求和区域进行汇总 。
4、筛选状态下的求和
如下图,对B列的部门进行了筛选,使用以下公式可以计算出筛选后的数量之和 。
公式为
=SUBTOTAL(9,D2:D14)
文章插图
SUBTOTAL第一参数用于指定汇总方式,可以是1~11的数值,通过指定不同的第一参数,可以实现平均值、求和、最大、最小、计数等多种计算方式 。
如果第一参数使用101~111,还可以忽略手工隐藏行的数据,小伙伴们有空可以试试 。
5、逆向查询数据
如下图所示,根据姓名查询部门和职务,也就是传说中的逆向查询 。
F3单元格公式为:
=INDEX(A:A,MATCH($E3,$C:$C,))
文章插图
用MATCH函数来定位查询值的位置,再用INDEX函数返回指定区域中指定位置的内容,二者结合,可以实现上下左右全方位的查询 。
MATCH找到E3单元格在C列的精确位置:老IN啊,你要找的那位,在第6间屋呢 。
接下来INDEX根据MATCH提供的线索,从A列找到第6个单元格 。
6、混合内容提取
如下图,要从A列姓名电话中提取出姓名,除了使用高版本的自动填充功能,也可以使用以下公式完成:
=LEFT(A2,LENB(A2)-LEN(A2))
文章插图
LENB函数将每个汉字(双字节字符)的字符数按2计数,LEN函数则对所有的字符都按1计数 。因此“LENB(A2)-LEN(A2)”返回的结果就是文本字符串中的汉字个数 。
LEFT函数从文本字符串的第一个字符开始,返回指定个数的字符,最终提取出员工姓名 。
7、合并带格式的内容
合并带有格式的内容时,Excel默认按常规格式进行合并,但是如果是日期、时间或是其他有格式的数值,结果就会让人大失所望了 。如何才能正确连接出需要的字符串呢?
C2单元格输入以下公式:
=A2&TEXT(B2,” y年m月d日”)
推荐阅读
- 一组常用Excel函数公式,速速收走
- 因为不知道SUMIF函数还能横着用,她一上午的工作被别人一分钟搞定
- 宅着不知学点啥,十个常用函数公式套路送给你
- 软文营销常用的方式,写软文的角度有哪些?
- 这个条件求和函数80%人没见过,它比sumif函数更简单
- 这些Excel函数公式5分钟搞定5小时的工作
- 利用rank函数进行排名,原来这么好用
- 一个rank函数一分钟搞定多表排名
- 使用RANK函数轻松搞定并列排名
- 2分钟学会Excel中的RANK排名函数