在多个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屋!