祖传秘方,专治各种奇葩表格

作者:Excel之家 2018-01-11阅读:5631次

小伙伴们好啊,老祝经常说,设计一个结构合理的表格,对于以后的数据汇总分析的意义都非常重要。但是日常工作中总免不了遇到各种各样的奇葩表格,今天老祝就和大家分享几个专治各种奇葩表格的祖传秘方。

1、底部有小计的表格

像下面这种表格,手工加入了小计行,如何汇总计算呢?

先按Ctrl+G,定位空值。

然后在【公式】选项卡下点一下【自动求和】按钮,OK了:

祖传秘方,专治各种奇葩表格

2、顶部有小计的表格

下面这种,就更加奇葩了,手工加入的小计行是在每一项的顶部的,也就是要对小计下面的各行进行求和。

先按Ctrl+G,定位空值。

然后在编辑栏输入以下公式,按Ctrl+回车:

=SUM(C3:C16)-SUMIF($A3:$A16,"小计",C3:C16)*2

祖传秘方,专治各种奇葩表格

先用SUM函数计算公式所在行之下的所有总和,然后用SUMIF函数,计算公式所在行之下,所有小计行的总和。

最后用总和减去小计乘以2,得到计算结果。

这个公式的核心,也是错位引用的,后面的公式会被前面的公式再次引用,要多看几次才能领会。

大哥啊,如果按指定的字段汇总,【数据】选项卡下有个功能叫【分类汇总】,你确定不要试试?

祖传秘方,专治各种奇葩表格

奇葩的表格可不止以上这几种,再来看看另外两种比较常见的:

3、合并单元格添加序号

COUNTA函数的作用是统计数据区域内不为空的单元格个数。

使用COUNTA函数,能够实现对带有合并单元格的表格快速添加序号。

祖传秘方,专治各种奇葩表格

同时选中A2:A13单元格区域,编辑栏输入公式按Ctrl+回车:

=COUNTA(B$2:B2)

4、合并单元格计数

如果要在有合并单元格的表格中统计各部门人数,只要将COUNTA与SUM合体即可。

祖传秘方,专治各种奇葩表格

同时选中D2:D13单元格区域,编辑栏输入公式后按Ctrl+回车:

=COUNTA(B2:B13)-SUM(D3:D13)

奇葩的表格衍生出各种奇技淫巧,要想统计汇总不留泪,就要保证设计表格时脑子不进水。

好了,今天老祝给大家分享的内容就是这些,祝小伙伴们一天好心情!