手把手教会你制作工资条

训练营有小伙伴问了个问题:图1的原表,如何实现图2的效果?(见黄色填充部分的差异)

(图1)

(图2)

我一看:这不类似于做工资条吗?当然,这个比做工资条要复杂、更难一些。

所以,为了让你能够更清晰地理解,我先从简单的工资条的制作说起。假设你现在是HR,图3是你手里的工资总表,图4是到每个员工手里的工资条。你如何将工资总表变成工资条呢?

(图3)

(图4)

最笨拙的办法,就是你手动去给每一行工资复制粘贴标题行,一行一行地,如果数据量小还好,要是数据量很大,几百、几千个……员工,那你怎么办?加班?不,有简单的办法,可以让你不加班。什么办法呢?你想的是VBA吗?不,不需要,只要Excel的基础操作就够了。

先来说下原理,现在是要让每个人的工资行都有单独的标题行,但现在只有首行一行标题行,所以我们要先增加标题行(因为首行已经有一个标题行,所以增加的标题行数量是员工人数减1)。增加完标题行后,上面一部分是工资行,下面一部分是标题行。那如何把标题行放上去,并且使每一个标题行与每一个工资行连在一块呢?

涉及到不同行的位置变换,我们优先考虑排序来实现。那如何排序,才能实现一行标题一行工资,再一行标题一行工资呢?你可以让一个标题行一个工资行作为一个组合,贴上同一个序号,这样它们排序便可以在一块。

也就是说,上面部分的工资行与下面的标题行,我们要对它们分别添加同一组序号,这样通过序号排序,它们就能分别组合在一块,便能做出工资条了。

好,知道了原理,接着就来说具体的操作。因为理解了原理,所以在操作时可以灵活些。

第一,增加辅助列,生成一列递增的序列;

第二,复制第一步中的序列(最后一行序号不复制),粘贴到辅助列下面的空白单元格

第三,复制标题行,粘贴到第二步中序列左边的空白单元格

第四,调出筛选按钮(快捷键:Ctrl+Shift+L),按升序对辅助列排序

第五,删除辅助列

到这里,工资条就制作好了。接着还是回到文章开头的那个问题。

你现在知道了工资条的做法,再来看文章开头的那个问题,是不是会发现两个问题很类似呢?都是要给不同行的内容加上标题行,所以我们可以用同样的思路,先增加辅助列,然后通过排序来实现。

但仔细一看,文章开头那个问题又不能完全照搬工资条的做法。因为,工资条加标题行的间隔数量是1,比较有规律,添加辅助列的时候,手动输入序列号就可以实现自动填充。而这里的情况是,它的间隔数量是不规律的,有间隔两行、三行……

但是你再仔细观察一下,会发现,它是需要根据D列责任人的属性来添加标题行的。也就是说,根据D列中的每一个责任人,给它增加一个单独的标题行。

所以,现在问题,就变成了,怎样将D列按责任人去编排同一组序号呢?也就是D列每一个责任人(包含汇总)所在的行,都用同一个序号,便于后面排序。

继续思考,因为各汇总行之间是不等间距的,要对它进行编号,只能根据D列责任人的规律来计数,所以考虑用countif函数。

先说下countif函数的通用公式:=countif(条件区域,条件)

那放在这里,条件区域、条件是什么呢?

先来说条件。

因为这里我们只需要按照一组责任人,同一个序号,但D列每一组责任人的规律,都是n行责任人+1行责任人汇总,而责任人的间距无法确定,也就无法作为条件来寻找规律,但是责任人汇总是有规律的,任何一组责任人都会有且仅有一行责任人汇总,所以我们就以责任人汇总来作为计数条件。当然,这里因为汇总前面都有不同的责任人名称,所以不能直接通过“汇总”来作为条件,我们将这些责任人名称用通配符来表示,也就是把汇总的条件写成“*汇总”。也即,公式初步变为:=countif(条件区域,“*汇总”)

再来说,条件区域。

条件是“*汇总”,从第2行到第8行,要计数为1,很明显,第2行到第7行按“*汇总”条件去计数的话,结果会是0,所以我们考虑在countif计算结果的基础上加1,也即,公式继续变为:=countif(条件区域,“*汇总”)+1。(说明:此处及以下涉及到的具体例子,请参看图1)

再进一步来看条件区域,根据条件的判断,条件区域肯定是在D列,那是不是直接把条件区域定为整个D列或者D列有内容的行呢?你可以都尝试一下,结果是每一行的计数结果都是一样的,不得行。

所以,到这里,很显然,条件区域的选择,与我们常规用countif去计数的条件区域不太一样。为什么呢?因为你的需求是特殊的,你的条件也是特殊的。

再回到需求,你是想要根据“*汇总”条件来计数,而且汇总行及其上面的不含“汇总”的行序号要一致,但是根据我们的公式countif计数结果+1,也就意味着汇总行及其上面的不含“汇总”的行,在countif计数下的结果是一样的,这意味着汇总行并没有因为“汇总”条件被计数。

以第一个汇总行第8行来看,这说明,你在第8行的条件区域不能包含第8行,否则第2-7行结果为1,而第8行的结果是2。但是你的条件区域又得在D列。考虑到条件区域是一个区域,具有延续性,你知道,条件区域肯定是从上面开始填充下来的。常规的,我们考虑的是从标题以下的D2单元格出发,这样到第8行的条件区域,就应该是D2:D8。

可是,我们前面刚根据结果推导过,在第8行的条件区域不能包含第8行,所以我们把区域往上挪一下,变成D1:D7。而且,要让第8行还出现D1,那必然是在一开始就把行锁定,这样就变成了D$1:D7。

好,到这里,我们知道了第8行的公式要怎么去写:=countif(D$1:D7,“*汇总”)+1,然后据此,填充其他各行的公式即可。

以上呢,是因为想要跟你说明白条件区域的设置,所以会去做拆解尝试。如果你了解这个原理后,也不用这样去尝试,直接就知道怎么去设置了。

好,说了这么多,我们还只是完成了第一步,就是添加辅助列,并给辅助列用公式填充序列号。

接下来,又回到了与工资条一样的操作,所以不再赘述过多细节、原理,直接来说操作步骤。

第一,复制刚才添加的序列,粘贴到辅助列下面的空白单元格,并删除重复值

第二,复制标题行,粘贴到第二步中序列左边的空白单元格

第三,调出筛选按钮(快捷键:Ctrl+Shift+L),按升序对辅助列排序

第四,删除辅助列

今天的分享就到这里,非常感谢你的耐心阅读,希望今天的分享对你有所启发。

阅读已结束,喜欢的话就点个赞吧
注册坚果云网盘
还有其他问题,可以咨询小坚果咨询小坚果
赞(0)
网站部分内容和图片来源于网络如有侵权请联系我们删除:好用的网盘坚果云 » 手把手教会你制作工资条
分享到: 更多 (0)
手把手教会你制作工资条

本文链接:手把手教会你制作工资条https://content.jianguoyun.com/22513.html

评论 抢沙发

评论前必须登录!

 

坚果云 便捷、安全的网盘产品

免费试用下载客户端