二维码 购物车
部落窝在线教育欢迎您!

如何在单元格顶部按分组求和?这2种方法最简单!

 

作者:阿硕来源:部落窝教育发布时间:2021-06-28 11:42:14点击:12698

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

分组求和在Excel应用中,是很常见的实用技巧。我们一般会有很多方法去实现它,用数据统计表更是省时省心。但是如果要在单元格顶部按分组求和,只是把显示的位置从下面换到了上方,却难倒了很多人……其实只需要用用下面2个方法,比平时的方法多做1、2步,就可以轻松解决这个问题!

 

 

有小伙伴问了这样一个问题:在Excel中,如何对数据按分组求和,并且将求和的结果放在每一组数据上方?

 

大家先来看一下数据。如下图所示,A列中的数据为员工姓名,B列中的数据为员工所在的部门,C列中的数据为员工的加班时长。

 

 

这个小伙伴想要到达的效果是对各部门的加班时长进行求和,并将求和结果存放在每个部门第一行数据的上一行。今天,我们就来学习两种解决此类问题的方法。

 

方法一、借助分类汇总实现单元格顶部按分组求和

首先,单击A1:C16区域中的任意一个单元格,然后依次点击“数据”-“分类汇总”,在弹出的“分类汇总”对话框中,将“分类字段”由“姓名”更改为“部门”,取消勾选“汇总结果显示在数据下方”,其他选项保持不变。

 

图形用户界面, 文本, 应用程序

描述已自动生成

 

点击“确定”后,可以看到,在第B3B9B13B18单元格中,出现了“市场部 汇总”、“行政部 汇总”、“财务部 汇总”、“企划部 汇总”;在C3C9C13C18单元格中,出现了各部门的加班时长总和。同时,在B2C2单元格中,出现了“总计”以及所有部门的加班时长总和,在行号的左侧出现了分级显示的目录。

 

表格

描述已自动生成

 

接下来,整理表格。依次点击“数据”-“取消组合”-“清除分级显示”,就可以将分级显示删掉了。然后,选中第二行后,点击鼠标右键,选择“删除”,就可以将“总计”这一行删除了。

经过这两步操作之后,得到的结果如下图所示。

 

 

大家可以看到,A2A8A12A17单元格是空的,为了得到更好的显示效果,可以把“部门”字段显示在其中,这该如何操作呢?

 

step.1大家选中A1:A20区域,按下Ctrl+G,弹出“定位”对话框,选择“空值”,如下图所示。

 

 

Step.2点击“确定”后,A2A8A12A17这四个空单元格即被选中( A2的颜色为白色,表示当前所在的单元格为A2)。保持A2A8A12A17的选中状态不变,直接输入“=B3,然后按“Ctrl+回车”键,这时公式就批量填充到A2A8A12A17,这些单元格中的公式分别变成了“=B3”、“=B9”、“=B13”、“=B18”,得到的结果如下图所示。

 

表格

描述已自动生成

 

这里,解释一下批量填充的逻辑。在A2中,大家输入的是“=B3”,由于使用的是相对引用,所以,A2单元格引用的是B3的数据,所以以此类推,A8单元格引用的是B9的数据……

 

补充说明:

大家可能还有一点好奇:C2C8C12C17单元格是通过分类汇总计算出来的,那么,它们里面有函数公式吗?

C8单元格为例,查看一下就知道啦!如下图所示,C8中的公式为“=SUBTOTAL(9,C9:C11)”。咱们公众号以前的文章里面,有讲过SUBTOTAL函数的用法,感兴趣的小伙伴可以在咱们公众号内搜索一下有关文章,此处笔者就不细讲了。

 

 

方法二、借助SUMIF函数实现单元格顶部按分组求和

SUMIF函数也可以实现单元格顶部按分组求和的功能。在本例中,由于原始数据中没有用于放置求和结果的空白单元格,所以要先在各分组顶部批量插入空白单元格,然后再通过批量填充公式的办法来进行求和以及完善表格。

 

1.批量插入空白单元格

首先,选中B列,点击鼠标右键,选择“插入”,在“姓名”和“部门”之间会插入一个空白列,选中C2:C16,将其复制并粘贴到B3:B17。此处一定要注意,在粘贴的时候,要向下错开一行。

 

图形用户界面, 表格

描述已自动生成

 

选中B2:C16按下“Ctrl+”键,则可选中B2:C16区域中同一行中内容有差异的单元格,结果如下图所示,可见,C2C7C10C14均被选中。

 

表格

描述已自动生成

 

现在,把鼠标放在C2单元格,点击鼠标右键,依次选择“插入”-“整行”-“确定”,即可在C2C7C10C14的上方批量插入空白单元格。得到的结果如下图所示。

 

表格

描述已自动生成

 

此时,大家将B列(新添加的辅助列)删掉即可。

 

表格

描述已自动生成

 

2.利用SUMIF函数按分组顶部计算

选中C2:C20区域,按下“Ctrl+G”键,依次选择“定位条件”-“空值”-“确定”,即可批量选中空白的单元格;输入“= SUMIF(B:B,B3,C:C) ”,然后按下“Ctrl+回车”键,即可将公式批量填充到C2C8C12C17单元格。

 

表格

描述已自动生成

 

这里是SUMIF按条件求和公式,其中使用的是相对引用。在经过批量填充,SUMIF函数的第二参数在C2C8C12C17中分别变成了B3B9B13B18,即对应“市场部”、“行政部”、“财务部”、“企划部”,所以,SUMIF函数的意思就是对B列中部门为“市场部”、“行政部”、“财务部”、“企划部”的数据,在C列中对应的位置求和。

 

3.进一步完善表格,对AB两列中的空白单元格进行批量填充

选中A2:A20,按下“Ctrl+G”键,依次点击“定位条件”-“空值”-“确定”,输入“=B3”,然后按下“Ctrl+回车”键;选中B2:B20,按下“Ctrl+G”键,依次点击“定位条件”-“空值”-“确定”,输入“=B3&" 汇总"”,然后按下“Ctrl+回车”键,即可。

最终得到的结果,如下图所示。(第二行可再细调一下文字格式和对齐方式,此处略。)

表格

描述已自动生成



好了,今天就讲到这里,大家学会了吗?

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

条件求和后查找最大值,还在用SUMIF或透视表?试试分类求和法吧

多条件求和下sumifs和sumif分别怎么用

忽略错误值求和,包你一看就会!

DSUM,最简单的条件求和函数!你知道不?

版权申明:

本文作者阿硕;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。