二维码 购物车
部落窝在线教育欢迎您!

解锁人事、财务常用的20个函数公式,求和、排名、统计全都很简单

 

作者:郅龙来源:部落窝教育发布时间:2021-07-22 16:31:57点击:445

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。


编者按:

各位小伙伴们好!众所周知Excel函数功能非常强大,擅长Excel函数能够迅速提升我们的工作效率。但是Excel函数包含的门类繁多,短时间精通全部函数是天方夜谭。我们可以根据自己的工作需要或者兴趣选择某一部分进行学习、研究。这一次我们为大家奉上了20个工作中常用函数公式,一起去看看吧~

 

职场人不会Excel函数公式怎么行?但是Excel函数公式那么多,又没时间都学一遍,咋办?

别急!这不就为你准备了职场人必须掌握的20个公式吗,涉及到8种工作场景!废话不多说,马上来干货。

 

注:公式都可以直接套用,就不挨个解释原理了,相关的函数教程公众号里以前都讲过的。

 

场景1:带小计的数据怎么算总计

 

以下图中的数据为例,这样的数据非常常见吧。

 

 

每个部门的小计都是算好的,现在要计算总计,给你三个公式:

【公式1=SUM(C2:C26)/2

 

 

 

【公式2=SUMIF(A:A,"小计",C:C)

 

 

【公式3=SUMIF(B:B,"<>",C:C)

 

 

场景2:带单位的数字怎么求和

 

例如,每个人的奖金都是带单位“元”的,如果直接用SUM函数求和,最终结果为零。

 

 

针对这类问题,给你两个公式:

【公式4=SUMPRODUCT(--SUBSTITUTE(C2:C21,"",""))

 

 

【公式5=SUMPRODUCT(--LEFT(C2:C21,LEN(C2:C21)-1))

 

 

场景3:带合并单元格的数据怎么统计

 

以下图为例,有单元格被合并的情况下,要统计部门人数和奖金合计。

 

 

统计部门人数:

【公式6 =COUNTA(B2:B21)-SUM(D3:D22)

 

 

统计奖金合计:

【公式7=SUM(C2:C21)-SUM(E3:E22)

 

 

注意:这两个公式需要先选择合并单元格区域,再输入公式,最后按快捷键“Ctrl+Enter”完成输入。

 

场景4:按部门和费用项目匹配费用金额

 

效果如图所示:



 

针对这类问题,提供三个公式:

【公式8=SUMIFS(C:C,A:A,E2,B:B,F2)

 

 

【公式9=SUMPRODUCT((A2:A21=E2)*(B2:B21=F2)*C2:C21)

 

 

【公式10=LOOKUP(1,0/(A2:A21=E2)/(B2:B21=F2),C2:C21)

 

 

场景5:重复单据号辨别

 

对所有的重复单据号加备注说明。

【公式11=IF(COUNTIF(A:A,A2)>1,"重复","")

 

 

单据号第二次出现才算重复。

【公式12=IF(COUNTIF($A$1:A2,A2)>1,"重复","")

 

 

场景6:排名次

 

对绩效奖金进行排名,所有人都参加。

【公式13=RANK(C2,$C$2:$C$21)

 

 

注意,上面这种排名当出现相同名次的时候,会占用排名,例如有两个5,接下来直接就是76就被占用了。

 

如果想要出现相同名次但是又不占用排名,在F2单元格输入下面这个公式:

【公式14=SUMPRODUCT(($C$2:$C$21>=C2)/COUNTIF($C$2:$C$21,$C$2:$C$21))

 

 

有两个5的时候,还是会有6,名次不会出现空缺。

 

另外还有一种比较常用的就是部门内的排名。

【公式15=SUMPRODUCT(($C$2:$C$21>=C2)*($A$2:$A$21=A2))

 

 

场景7:编序号

 

给员工编序号,同一个部门的员工序号都是从1开始顺延。

【公式16=COUNTIF($B$1:B2,B2)

 

 

对部门编序号,同一个部门序号相同,部门不同时序号递增。

【公式17=N(A1)+(C2<>C1)

 

 

场景8:与手机号有关的三个公式

 

从个人信息中提取手机号

【公式18=RIGHT(A2,11)

 

 

将手机号分段显示

【公式19=TEXT(B2,"0 0000 0000")

 

 

加密显示手机号

【公式20=REPLACE(B2,4,4,"****")

 

 

好了,相信掌握了这20个公式,一定会为你的工作锦上添花。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

说明: IMG_256

相关推荐:

7个Excel小技巧,提高表格查看效率

Excel运用规范1:一个单元格只记录一条信息

快速整理不规范的Excel表格的7个公式

9条最实用的计算excel中关于日期的公式!(建议收藏)

版权申明:

本文作者郅龙;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。