公众号“Excel基础学习园地”是一个免费发布Excel基础知识、函数应用、操作技巧、学习方法等资讯的公众号,请点击上方“Excel基础学习园地”添加关注,方便我们每天向您推送精彩资讯。
点击文末“阅读原文”,免费阅读《菜鸟的Excel函数修炼手册》!
加老师微信带你入Excel的坑
验证信息:加Excel交流群
Excel交流群免费加入
公众号回复2016,可以获得office2016的下载链接
考勤问题一直是个老大难问题,关于考勤的问题要细分的话也有好多类别,今天分享的是一个加班调休的问题。
在群里有小伙伴求助,如何计算加班时间调休:
看到数据之后,老菜鸟表示一脸懵逼……
先不说这位伙伴的问题是不是表述清晰,单就这个表格来说,估计除了当事人,没几个人能看明白的。
在了解清楚之后,发现问题是由于表格设计不合理,从而为后期统计带来很大的麻烦!
虽然这个案例不具有通用性,但是解决问题的思路确实可以供大家参考,下面就以这个问题为例,说明一下表格设计的一般原则。
这一条是很多朋友在设计表格时最容易触犯的规则。
我们总是习惯将数据源(本例中就是每个人的加班时数和调休时数)和希望看到的统计结果(每个人每个月的未调休时数)放在一起,觉得这样比较方便。
大多数时候好像的确如此,可是这种设计方式一旦出现新的需求,就会引发很多问题:
1、表格设计不美观;
2、公式没法写;
3、数据结构凌乱;
4、统计结果不便查看。
比较规范的做法是将数据源单独存放,并且是以一维表的形式存放。
而后根据实际需要再去进行统计结果的设计,无论使用公式函数还是透视表都很方便。
就本例而言,数据源实际上非常简单,加班相当于收入,调休相当于支出,要统计的结果就是结余工时。
可以这样设计:
数据源只需要最基本的信息:月份、工号、姓名、加班时数和调休时数。
这些数据都是最原始的信息,在此基础上,可以按照需要添加一些计算列,例如结余调休时数:
利用公式:
=SUMIF($C$1:C2,C2,$D$1:D2)-SUMIF($C$1:C2,C2,$E$1:E2)
就能很容易的计算出每个人最新的结余调休时数,关于这个公式的原理,可以看下这篇教程:
【Excel公式应用】统计余额遇上麻烦,SUMIF大发神威!
有了这个结果,要想统计每个人每月的结余调休时数还不是很简单吗?
使用最基础的透视表操作就能完成汇总,看下动画演示吧:
如果你还不会用透视表,可以去公众号底部左边菜单找到透视表专栏:
如果想系统学习数据透视表,可以了解一下这个课程:
Excel小白的第二套课程:Excel数据透视表基础课(15天完成)
使用透视表倒是很方便,但也有弊端,那就是不能完全按照自己的需求去设计汇总结果的呈现方式。
接下来一起看看如何自己设置一个汇总表:
我们仅以最简单的要求为例:
需要按照这种格式呈现汇总结果,那么每个月的具体数据就可以用一个公式来得到:
=SUMIFS(Sheet2!$F:$F,Sheet2!$C:$C,$C3,Sheet2!$A:$A,D$2)
这里是SUMIFS函数最基础的用法,不做解释了,不会这个函数的可以看教程:条件求和的利器SUMIF函数详解(一)SUMIF初体验
我们今天的重点是搞清楚该如何去设计表格。
以上分享的就是一个比较标准的表格设计思路,大家可以发现,虽然我们将数据源单独存放好像是多了一些操作,但是后期统计确实非常方便,甚至对于一些稍有难度的要求,也可以很容易解决。
扫描文章顶部二维码,备注信息:打卡领视频。
小编邀你进入打卡福利群,只要每天学习公众号教程并转发就可攒积分,一天一分。
积分可以兑换到Excel基础视频、Excel数据透视表视频、Excel公式函数视频、Excel图表视频、Excel VBA视频、Excel数据分析课程视频,以及Word和PPT相关学习视频,还有各种模板。
部分视频目录:
加入老菜鸟的班享有更多福利
包年会员,360元每年,可享受以下福利:
1、一年内的答疑服务:工作和学习中遇到的问题都可以解答;
2、一年内的直播课程免费参加,无需另外交钱,直播课程包含Excel相关的所有内容,具体课程是根据学员的需求定制的;
3、每个月可以免费领取一套视频教程,教程包含Excel基础、公式函数、透视表、VBA、图表、数据分析以及Word和PPT方面的内容。
终身会员,888元不限时,可享受以下福利:
1、永久的答疑服务;
联系客服