如何在Excel中合并需要重复保留几个列的重复项 [英] How to consolidate duplicates in Excel where several columns need to remain uncosolidated

问题描述

我正在尝试通过产品商店进行存货盘点，但其中有几个零件编号被列出两次(A列).当我尝试合并这些错误时，我会收到错误没有合并数据"，我认为是由B-F列引起的(如果A列的值相同，它将始终相同).

I am trying to do a stock count through product stores but have several part numbers that are listed twice (Column A). When I try to consolidate these I recieve the error 'no data consolidated' which I think is caused by Columns B-F (which will always be the same if Column A value is the same).

我想将A-F列相同的行合并为一个单行，其中G列代表所有重复行的F列小计.

I want to consolidate the rows where columns A-F are the same, into a singular row with Column G representing the subtotal of Column F for all duplicate rows.

相应电子表格的屏幕截图

我已经搜索了该站点，尽管有些人遇到类似的问题，但没有一个答案适用于我的确切数据.我无法使用数据透视表，因为零件存储在太多地方，以致最终无法读取(请参见第二张图片).

I have searched the site and though there are people with similar problems, none of the answers have applied to my exact data. I can't use a pivot table because the parts are stored in so many places that it ends up being unreadable (see second attached picture).

数据透视表混乱的屏幕截图

推荐答案

SUMIFS()应该在这里解决您的问题.如果必须匹配A-E，则可以使用类似=SUMIFS(F:F,A:A,A2,B:B,B2,C:C,C2,D:D,D2,E:E,E2)的名称.这将为您提供Column G的结果，但是这种方式还将为您提供多行.

SUMIFS() Should solve your issue here. You can use something like =SUMIFS(F:F,A:A,A2,B:B,B2,C:C,C2,D:D,D2,E:E,E2) if it is essential that A-E match. This will give you a Column G with the result you are after however this way will also give you multiple rows.

如果B-E对于Column A值始终相同，那么您始终可以将唯一的Column A值粘贴到单独的工作表中，然后使用=SUMIFS(Sheet1!F:F,Sheet1!A:A,Sheet2!A2)

IF B-E are always the same for a Column A value then you can always paste the unique Column A values in a separate sheet and then use =SUMIFS(Sheet1!F:F,Sheet1!A:A,Sheet2!A2)

并显示类似这样的内容

这篇关于如何在Excel中合并需要重复保留几个列的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！