【Excel】统计不重复数据的个数,设置单元格不允许出现重复数据

前篇文章我们介绍了一下Excel中如何设置数据有效性以及COUNTIF函数的用法,这篇文章主要介绍有重复数据时如何统计不重复数据的个数以及通过数据有效性去设置单元格不允许出现重复数据。

统计单列不重复的个数

如下图所示,要统计B列数据的不重复个数,也就是通过、不适用、未测试、不通过总共4个。应该如何用公式计算呢?

不重复数据统计

我们可以在E6单元格输入公式=SUMPRODUCT(1/COUNTIF(B2:B16,B2:B16)),即可统计出B列B2:A16单元格区域的不重复值的个数。

公式计算不重复个数

公式解释:COUNTIF(B2:B16,B2:B16)将对B2:A16单元格区域中的每个数据进行个数统计,也就是B2:B16单元格区域中B2单元格数值的个数是COUNTIF(B2:B16,B2),B2:B16单元格区域中B3单元格数值的个数是COUNTIF(B2:B16,B3)。。。以此类推,相当于=COUNTIF(B2:B16,{B2;B3;...;B16}),最后的结果就是将每一单元格数值个数组成一个集合{9;2;2;9;...;2)

然后通过1/统计的结果,将个数取倒数,1/COUNTIF(B2:B16,B2:B16),得到的结果是={1/9;0.5;0.5;1/9;...;0.5}举例说明,由于数值"通过",在B2:B16单元格区域中有9个,取倒数后就是1/9,9个1/9相加就等于1。也就是将9个重复的"通过"算作了1个,以此类推其它数值。最后用SUMPRODUCT函数将这些结果相加就得到了总的不重复个数。SUMPRODUCT说白了它的一个简单用法就是对集合中的数值进行求和,比如:SUMPRODUCT({1,2,3})相当1+2+3

单列单元格中不允许输入重复值

工作中有时我们需要限制某列输入重复的数值,我们可以以下方法来实现。

单击要设置的列的列标签全选该列,然后单击“数据”选项卡下的“数据工具”组中的“数据有效性”命令按钮。

不允许重复

在弹出的“数据有效性”对话框下的“设置”选项卡“有效性条件允许”中设置“自定义”,然后在公式区域选择框中输入公式=COUNTIF(A:A,$A1)=1,其中如果活动单元格是A1,就输入公式=COUNTIF(A:A,$A1)=1,活动单元格是A2,就输入公式=COUNTIF(A:A,$A2)=1,以此类推,最后单击“确定”按钮。

不允许重复设置

然后在A列输入重复的数据时将会弹出如下图所示的提示:

不允许重复提示

有点可惜,如果A列原先就有重复的数据,设置时是不会提示的,而且重复的数据不会改变(这种情况可以限制我们改动重复的数据,不过没什么用处)。此外复制相同重复的数据,也是可以的,不会提示输入值非法。

那有没有什么办法解决这个问题呢?其实我们可以通过圈释无效数据方法标记重复的数据。我们可以点击“数据有效性”,然后在选择“圈划无效数据”,然后我们发现,A列有重复的数据都被自动圈划起来,这时我们可以确认是允许重复还是不允许重复输入。

圈释无效数据

下篇文章将会介绍如何去除某列中重复的数据,然后再详细介绍一下SUMPRODUCT函数的用法。

### 回答1: 您可以使用以下公式来统计一列中不重复的值的数量: =SUM(1/COUNTIF(range,range)) 其中,range是您要统计的范围,例如A2:A100。请注意,此公式必须使用数组公式键入,即按下Ctrl + Shift + Enter。 ### 回答2: 要在Excel中使用多条件统计一列不重复个数的公式,可以使用SUMPRODUCT和COUNTIF函数的结合。 假设我们有一个包含数据的列A,我们要统计满足条件的不重复个数。条件可以是多个,例如满足某个数值范围或者满足特定文本。 首先,我们可以使用COUNTIF函数和条件来计算满足条件的总个数。例如,如果要统计满足条件">10"的个数,可以使用COUNTIF(A:A, ">10")。 然后,我们可以使用SUMPRODUCT函数来计算满足条件的不重复个数。公式如下: =SUMPRODUCT(1/COUNTIF(A:A, A:A&"")) 这个公式使用COUNTIF函数来计算每个单元格出现次数,然后使用1除以每个出现次数得到权重。最后,使用SUMPRODUCT函数将这些权重相加,得到不重复个数。 需要注意的是,这个公式中的A:A是指数据所在的列。如果数据在其他列,需要根据实际情况进行修改。 将这个公式输入到任意一个单元格中,就可以得到满足条件的不重复个数。 希望以上回答对您有帮助! ### 回答3: 要实现Excel中多条件统计一列不重复个数的公式,可以使用IF和COUNTIF函数结合。以下是一个示例公式: =SUM(IF((条件1)*(条件2),1/COUNTIF(范围,条件3&条件4))) 其中,条件1和条件2是要满足的条件,可以是数值、文本或逻辑表达式。范围是要统计数据所在的单元格区域,条件3和条件4是要筛选的条件。 此公式的原理是先使用COUNTIF(范围,条件3&条件4)函数统计满足条件3和条件4的数据在范围中出现次数,然后使用1/计数结果得到每个符合条件的数据的权重。最后将所有满足条件的数据的权重相加,得到不重复个数。 请注意,该公式需要使用CTRL+SHIFT+ENTER键进行数组公式输入,以确保公式正确生效。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值