在多个Excel工作表中查找重复的单元格 [英] Find duplicate cells in multiple excel sheets

问题描述

我有一个带有多个电子表格的Excel工作表.每个电子表格中都有大约1000行的名字"和姓氏"列.我记录了一个宏，该宏以' firstname lastname '的格式显示了重复的行.

I have an excel sheet with multiple spreadsheets. There are columns 'firstname' and 'lastname' of about 1000 rows in each of the spreadsheets. I have recorded a macro which shows me the duplicate rows having in the format 'firstname lastname'.

=IF(SUMPRODUCT(($B$2:$B$1000=B2)*1,($C$2:$C$1000=C2)*1)>1,"Duplicates","No duplicates")

上面的公式可以在一张纸中找到重复的行.我如何调整公式，以便它也可以与其他工作表匹配.公式或VBA也可以.

The above formula can find duplicate rows in one sheet. How do I tweak the formula so that it can match with the other sheets as well. A formula or VBA will also do.

推荐答案

您可以欺骗Excel接受带有 INDIRECT 和数组的3D范围参考作为 COUNTIFS 的参数由 SUMPRODUCT 包装器处理.使用要在Z2:Z4中处理的工作表的名称，这将计算工作表中B和amp;列中的名字和姓氏的重复项.C.

You can fool Excel into accepting a 3D range reference as a parameter of COUNTIFS with INDIRECT and array processing courtesy of a SUMPRODUCT wrapper. With the names of the worksheets to be processed in Z2:Z4 then this will count duplicates across the worksheets for the first and last names in columns B & C.

= SUMPRODUCT(COUNTIFS(INDIRECT('"& $ Z $ 2:$ Z $ 4&'！B:B")，B2，INDIRECT('"& $ Z $ 2:$Z $ 4&'！C:C")，C2))

您应该能够将该标准公式合并到您的 IF()语句中，以进行重复识别.

You should be able to incorporate that standard formula into your IF() statement for duplicate recognition.

请注意， INDIRECT 被认为是 volatile 函数，该函数会随着工作簿中的任何更改而重新计算.根据数据的范围，您可能会遇到一些计算延迟.

Please note that INDIRECT is considered a volatile function that recalculates with any change in the workbook. Depending upon the extent of your data it is likely that you will experience some calculation lag.

这篇关于在多个Excel工作表中查找重复的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！