在Excel中按总和组合 [英] Group by Sum in Excel

问题描述

我们假设我在Excel中有以下表格

  AB 
项目quantity_sold 
 A 3 
 A 4 
 A 1 
 B 5 
 B 2 
 D 12 
 C 3 
 C 7 
 C 8 
  

，我需要总结按项目分组的quantity_sold，并在每个组中仅在相邻列上打印一次结果，类似于以下

  ABC 
项目quantity_sold SUM_by_item_type 
 A 3 8 
 A 4 
 A 1 
 B 5 7 
 B 2 
 D 12 12 
 C 3 18 
 C 7 
 C 8 
  

有没有办法可以实现这一点，而不使用数据透视表？

解决方案

尝试在C2中复制的公式

= IF（A2 = A1，，SUMIF（A：A，A2，B：B））

这将给每个组的第一行一个总和 - 其他行留空

Let's assume that I have the following table in Excel

A     B
Item  quantity_sold
A     3
A     4
A     1
B     5
B     2
D     12
C     3
C     7
C     8

and I need to sum up quantity_sold grouped by Item and print the results on the adjacent column only once per group, similar to the following

A     B                 C   
Item  quantity_sold    SUM_by_item_type
A     3                 8
A     4
A     1
B     5                 7
B     2
D     12                12
C     3                 18
C     7
C     8

Is there any way I can achieve this without using Pivot Tables?

解决方案

Try this formula in C2 copied down

=IF(A2=A1,"",SUMIF(A:A,A2,B:B))

That will give you a sum on the first row of each group - other rows are left blank

这篇关于在Excel中按总和组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！