Excel-同时删除重复项和SUM [英] Excel - Remove duplicates and SUM at the same time

问题描述

我有一列带有ID的列，但是它们是重复的；例如:

I have a column with ID's, but they are duplicated; for instance:

"0,0,1,1,1,2,3,3,4,4，..."

"0,0,1,1,1,2,3,3,4,4, ... "

对于每一行，我在其他列中都有给定值，例如:

For each row, I have a given value in the other columns, for instance:

"0-24; 0-36; 1-13; 1-34; 1-23; ..."

"0-24; 0-36; 1-13; 1-34; 1-23;..."

我只希望每个ID保留一行，但是我需要对每个ID的值求和，即，将给定ID(0,1,2，...)的所有列中的所有值求和，其中可能包含几行.

I want to keep only one row with each ID but I need to sum the values of each ID, that is, sum all the values in all columns for a given ID (0,1,2,...), which may include several rows.

是否有使用Excel的简便方法?

Is there a easy way to do this using Excel?

以下是一些示例数据(左表)以及所需的输出(右表).

Here is some sample data (table to the left) together with the desired output (tables to the right).

ID  Value           ID  Value
0   24              0   60
0   36              1   70
1   13              2   16
1   34              3   24
1   23              4   48
2   16                      
3   9                       
3   15                      
4   24                      
4   24      

推荐答案

您可以做的就是复制ID，然后将其粘贴到另一个工作表中.假设原始表位于Sheet1中，并且将所有ID复制到Sheet2中的A列.

What you can do is to copy your IDs and paste them for example in another Sheet. Let's assume your original table is in Sheet1, and you copy all your IDs to column A in Sheet2.

然后您在Sheet2中删除重复的ID:

Then you remove duplicate IDs in Sheet2:

选择A列>数据功能区>数据工具>删除重复项

Select column A > Data Ribbon > Data Tools > Remove Duplicates

然后在B列中输入公式:

In column B, you then put the formula:

=SUMIF(Sheet1!$A:$A, Sheet2!$A2, Sheet1!$B:$B)

注意:上面的公式进入Sheet2的单元格B2中，然后使用 pasteSpecial>复制下来.仅公式.

Note: above formula goes into cell B2 on Sheet2, and you copy it down with pasteSpecial > only formulas.

编辑:如果由于其他列中的信息仍然需要相同数量的行等，只需跳过删除重复项"部分.

if you still want the same number of rows etc because of the information in your other columns, just skip the "Remove duplicates"-part.

这篇关于Excel-同时删除重复项和SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！