返回目录:excel表格制作
只写最接地气的办公干货,加入我们,组队前行。
案例:某集团公司下属5个分公司,总公司规定,每个分公司要在每个月结束前5天将财务报表发送至总公司进行汇总,按期汇报的每次计1分,否则计0分,此项工作纳入年度考核范围。
下面两张图是总公司办公室小张制作的日常考核统计表(模拟数据):
思路分析:上面两个图一共需要补充四项内容,分别是图1的【应汇报日期】和【积分】,图2的【积分汇总】和【排名】
接下来我们一个一个的完成:
1、计算【应汇报日期】
应汇报日期为每月结束前5天,因为每个月的天数不一样,所以汇报的日期也不一样。不过,只要计算出每月的最后一天,那么前5天就能计算出来了。
怎么做呢?其实也很简单,来看演示。
在E3单元格输入公式=DATE(A3,B3+1,0)-5,然后向下填充即可。
函数解释:DATE函数返回参数中代码表示的日期,即DATE(年,月,日),DATE(A3,B3+1,0)表示的就是DATE(2019,2,0),从字面意思就是2019年2月0日,事实上并没有2月0日这一天,这个函数其实构建的就是1月的最后一天,即2019年1月31日,于是DATE(2019,2,0)-5就是2019年1月31日减去5天,即2019年1月26日。
2、【积分】计算
根据积分规则,在G3中输入公式=IF(F3>E3,0,1),向下填充,即可得到结果。
函数解释:If函数是逻辑判断函数,简单解释一下语法,if(条件,满足条件返回的结果,否则返回的结果),具体到本案例if(未按期汇报,返回数值0,否则返回数值1)
3、【积分汇总】与【排名】
分别在K3和L3中输入公式=SUMIF($C:$C,J3,$G:$G)和=RANK(K3,$K$3:$K$7),填充即可得到结果。
函数解释:
SUMIF是条件求和函数,语法为SUMIF(条件所在的区域,求和需满足的条件,对那个区域进行求和),具体到本案例就是=SUMIF(C列即下级代码所在的列,J3即对应的单位代码,G列即积分所在的列),即SUMIF($C:$C,J3,$G:$G)
RANK是排名函数,语法为RANK(对哪个数字进行排名,在哪个区域内排名,排名为降序或升序),如果第三参数为0或省略就是降序排列,分数最高的为第一名。
所以公式=RANK(K3,$K$3:$K$7)的意思就是,计算K3在区域K3:K7的排名。
注意:如果图1中的数据逐月增加后,积分汇总和排名可以自动计算,无需调整公式。
更多排名方法参考:
如需进行图表演示,请参考:
行政人员必会的4种Excel图表创建方法,能用图说明问题的就别废话
今天你学会了吗?希望你能举一反三、灵活掌握。
欢迎在留言区和我们一起讨论交流。
也欢迎写下你的疑问,我们再做进一步解答。
Excel办公精英,希望你每天都有收获。
↓↓↓↓↓往期精彩好文,点击即可查看
只需9秒——将Word中的参会人员名单,按1列导入到Excel里
仅用20秒——完成1000份录取通知书的制作,你还在加班吗?
1招破解Excel单元格合并后,批量填充序号,好用到没有朋友
行政人员必会的4种Excel图表创建方法,能用图说明问题的就别废话
欢迎关注同名微信公众号、今日头条号
【Excel办公精英】