通常我们将某一列的连续若干行合并成一个单元格,表示一个归类。

比如人员数据共10行,我们把其中 分组 列的123行合并为一个单元格,表示第一个小组,同理,45为第二个小组,6789为第3个小组,10分第4个小组。这时 分组 列后面有一列 人数 列,表示每个小组的人数。

如下:

人员	分组	人数
A	1	3
B		
C		
D	2	2
E		
F	3	4
G		
H		
I		
J	4	1

在数据量过多的情况下,我们显然不可能手动填入人数。通过查阅资料,我们得知,选中C2到C11单元格(由于合并了部分单元格,这里只有4行),使用excel表格公式,人数=COUNTA(A2:A11)-SUM(C3:C11),然后ctrl+enter即可算得结果

解释一下:

COUNTA(A2:A11),表示人员A2单元格到A11单元格的非空单元格个数,由于每行都表示一个人员,是非空的,所以这个值固定为10

SUM(C3:C11),表示C3单元格到C11单元格之间所有数字之和

  • 对于第一组:人数=COUNTA(A2:A11)-SUM(C3:C11)
  • 对于第二组:人数=COUNTA(A5:A14)-SUM(C6:C14)
  • 对于第三组:人数=COUNTA(A7:A16)-SUM(C8:C16)
  • 对于第四组:人数=COUNTA(A11:A20)-SUM(C12:C20)

我们需要知道,对于列方向合并单元格,合并后的单元格坐标为该列的第一行;同理,对于行方向合并单元格,合并后的单元格坐标为该行的第一列。

对号入座:

  • 对于第一组:人数=总人数 - 除第一组以外的组的所有人数(由于第一组表示人数的单元格是合并的单元格,其坐标为C2,如果从C3到C11计算数字单元格之和,则不会算到第一组的人数C2,下同理)
  • 对于第二组:人数=除第一组的所有人数(由于填充效果,第二组人数单元格比第一组人数单元格多3,所以COUNTA从A5到A14,即统计不到第一组的A2到A4,而A12到A14均为空白单元格,所以COUNTA不会统计在内,下同理) - 除第一二组以外所有人数(由于填充效果,SUM从C6到C14,第二组的人数从C5开始,从C6到C14显然不会统计到第一二组,C12到C14为空白单元格不会被统计在SUM中,下同理)
  • 对于第三组:人数=除第一二组的所有人数 - 除一二三组所有人数
  • 对于第四组:人数=除第一二三组的所有人数 - 除一二三四组所有人数

注意:

  1. 上面的方式必须把所有组的一起计算才有效,公式放在单个一组计算是没有作用的,因为counta可以计算第n组(含)之后的所有组人数,但是sum计算第n组(不含)之后的人数时,由于第n-1组没有计算,为0,所以只会得到第n组(含)之后所有组的人数,类似递归的性质

  2. 选择列对应单元格后直接输入公式然后ctrl+enter,而不要单击,否则无法填充

另外这个问题有个不方便的地方是不能下拉填充,因为合并的单元行数不一定相同,但是ctrl+enter却可以操作,所以最后我们说一下ctrl+enter的作用。

官方解释:使用当前输入填充选定的单元格区域。

个人理解下拉填充不支持计算合并单元格行数来填充,而直接选择然后ctrl+enter则可以支持

文末彩蛋:

https://support.office.com/zh-cn/article/Excel-for-Windows-%E4%B8%AD%E7%9A%84%E9%94%AE%E7%9B%98%E5%BF%AB%E6%8D%B7%E6%96%B9%E5%BC%8F-1798d9d5-842a-42b8-9c99-9b7213f0040f#PickTab=Newer_versions