当前位置:首页 > 办公 > Excel教程 > 正文内容

判断两个Excel单元格区域是否有重复值

访客1个月前 (09-06)Excel教程76

如下图所示的工作表,使用公式来判断单元格区域A1:A3与C1:C3中是否有重复值。

从工作表中可以明显看出,这两个区域中都含有“Excel”,因此有重复值。

如何使用公式来判断呢?

先不看答案,自已动手试一试。

公式思路

将单元格区域A1:A3中的每个值与C1:C3中的每个值比较,若都不相同,则没有重复值,否则就存在重复值。

公式解析

在单元格E1中输入下面的数组公式:

=OR(TRANSPOSE(A1:A3)=C1:C3)

其值为TRUE,表明两个区域中存在重复值。如下图所示。

公式中,使用TRANSPOSE函数将A1:A3转置成水平方向的数组,即{“ Excel”,”excelperfect”,”Excel”},然后,C1:C3中的值{“Office”;”Excel”;”Word”}逐一与转置的数组相比较,即C1中的值“Office”与{“ Excel”,”excelperfect”,”Excel”}比较,C2中的值“Excel”与{“ Excel”,”excelperfect”,”Excel”}比较,C3中的值与{“ Excel”,”excelperfect”,”Excel”}比较,总共进行9次比较。若值相等,则返回TRUE,否则为FALSE。最后得到一个3行3列的数组:

{FALSE,FALSE,FALSE;FALSE,FALSE,TRUE;FALSE,FALSE,FALSE}

该数组作为OR函数的参数,获取比较后的结果。只要数组中有一个值为TRUE,OR函数的返回值就为TRUE。本例中,数组中有一个值为TRUE,因此返回TRUE,则表明存在重复值。

应用扩展

扩展1:要判断有无重复值的单元格区域方向不同

上文中判断重复值的单元格区域都在列中,然而如果要判断有无重复值的一个区域在列中,一个区域在行中,如下图所示的工作表,判断单元格区域A1:A3与C1:G1是否有重复值,那么如何编写公式呢?

这种情形下,公式更简单!在单元格C3中输入数组公式:

=OR(A1:A3=C1:G1)

由于这两个区域没有重复值,因此其结果为FALSE,如下图所示。

单元格区域A1:A3中的每个值逐一与单元格区域C1:G1中的每个值相比较,共进行15次比较,得到数组:

{FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE}

作为OR函数的参数。由于数组中全为FALSE,因此结果为FALSE,表明两个区域中没有重复值。

扩展2:要比较的单元格区域具有更多的行列数

上述比较判断两个单元格区域是否具有重复值的方法,要求两个区域的方向不同,一个是逐行,一个是逐列。然而,对于Excel 2003来说,工作表列数为256列,对于Excel 2007及以上版本来说,工作表列数为16384列,若数据量超过这些列数,就不能使用上面的方法了。

此时,可以使用MATCH函数与COUNT函数组合来判断两个单元格区域是否有重复值。

例如,对于前面的例子,可以使用数组公式:

=COUNT(MATCH(A1:A3,C1:G1,0))

如果结果为0,那么表明两个单元格区域没有重复值。如果结果为数字,那么该数字就表示这两个区域具有的重复值个数。

注:可以给要比较的两个区域命名,例如Data1和Data2,然后使用名称代称公式里的区域,例如=COUNT(MATCH(Data1,Data2,0)),这样更灵活。

MATCH函数在区域Data2中查找区域Data1中的值,若没有找到则产生错误值#N/A,若找到则获取该值所在的列号。这样,生成由#N/A或数字组成的数组。将该数组作为COUNT函数的参数,统计数组中的数字。若没有数字,表明没有重复值,则为0。若有数字,则返回数字的个数,也即重复值的个数。

小结

要比较两个区域中的数据,这两个区域的方向应不同。若两个区域的方向相同,应使用TRANSPOSE函数将其中一个区域转置。TRANSPOSE函数的用法详情参见《Excel函数学习23:TRANSPOSE函数》。

上述方法比较的次数,也就是说产生的数组中元素个数取决于两个区域所占的行列数,例如第1个例子比较的次数为3×3=9次,第2个例子比较的次数为3×5=15次。

Excel工作表中列的数据代表数组中的每行,即每个数据后都是分号;而工作表中的行数据代表数组中的每列,即每个数据后都是逗号,最后才是分号。

COUNT函数不会统计#N/A值,只返回非#N/A值的数量。

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/1492.html

标签: excel
分享给朋友:

相关文章

一个excel工作簿多个工作表中很多公式结果一次转为数值

一个excel工作簿多个工作表中很多公式结果一次转为数值

今天一位朋友提出的问题: 他有一个月报表工作簿,里面多个工作表的数据都是用公式从一个数据源引用过来的,他必须在数据源更新之前,把所有的公式结果都保存成数据,才能保证源数据改变不影响到此个月报表工作簿。 问:有没有一次把工...

Excel有哪些最常用又简单的函数公式?

谈起学Excel,就绕不开Excel函数。不过Excel函数有几百个,什么时候能学完?曾经我有一位朋友,立下一段誓言,每天学一个函数,花一年时间学贯东西,最后——他疯了。 坦白的说,没有人能把每个函数都学完,毕竟有些函数...

Excel怎么批量计算加减乘除?Excel批量计算加减乘除方法

Excel怎么批量计算加减乘除?Excel批量计算加减乘除方法

  相信很多新手用户在使用Excel表格软件的时候经常会遇到需要大批量四则运算的单元格,那么我们应该如何针对这些大批量的单元格数据进行运算呢?写下来小编就来分享一下关于Excel批量运...

在excel中如何设置退出删除vba工具栏

在excel中如何设置退出删除vba工具栏

  在excel中如何设置退出删除vba工具栏?有很多用户反映,在excel中使用了vba工具栏后,不知道怎么删除。的确,在excel中使用了vba工具栏后是不会自动删除的。那么问题来...

Excel数组公式(之三)——数组公式典型应用

Excel数组公式(之三)——数组公式典型应用

之前讲了数组公式的基础与初步应用,今天,给大家举几个数组公式典型应用的例子。 1、“绕过”乘积直接求和 如下数据表: 计算应付金额,你会把每种产品的订购数量与单价相乘,然后相加吗? 当然,能运算出结果,但是如果有上百上...

excel图表按地区分类汇总销售总额并分页打印

excel图表按地区分类汇总销售总额并分页打印

问题情境 如下数据样表: 要求保留明细数据的情况下,快速统计每个地区的房价总额,并实现按地区分页打印。 分类汇总可以快速实现。 实现方法第一步:按照地区排序 特别注意:分类汇总的前提是一定要按照分类字段进行排序! 如果...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。