excel多条件不重复数据
excel表格是我们工作时常会用到的,那么关于excel多条件不重复数据你又清楚操作吗,以下是本站小编搜集并整理的有关内容,希望在阅读之余对大家能有所帮助!
excel多条件不重复数据使用1/COUNTIF与SUM函数组合统计不重复数据个数。
→ 操作方法
在C2单元格输入以下2个公式之一:
公式1 {=SUM(1/COUNTIF(A2:A16,A2:A16))}
公式2 =SUMPRODUCT(1/COUNTIF(A2:A16,A2:A16))
→ 原理分析
1.使用COUNTIF函数进行条件统计,返回A2:A16单元格区域内每个数据出现次数的数组:
{2;2;1;3;2;1;3;2;1;1;2;1;1;2;3}
被1除后,生成数组:
{1/2;1/2;1;1/3;1/2;1;1/3;1/2;1;1;1/2;1;1;1/2;1/3}
即出现N次重复的,就变成N个1/N,求和就是1,达到重复值只算1次的目的.。
2.最后使用SUM或SUMPRODUCT函数求和即可得到区域内不重复值的个数。
→ 知识扩展
1/COUNTIF函数统计不重复值的优缺点
1.统计区域内不得有空单元格,否则返回#DIV/0!错误。可以使用嵌套IF函数的数组公式解决这个问题,公式如下:
{=SUM(IF(A2:A16<>"",1/COUNTIF(A2:A16,A2:A16)))}
2.因为EXCEL浮点运算可能产生误差而造成答案不正确,即公式返回值比正确值小。对于这种情况,可以用嵌套ROUND函数修正。
TIF函数对数据类型没有要求,文本、数值、逻辑值、错误值均可,每一种错误值算作一个不重复数据。
4.统计区域不限于单行或单列(即一维引用),可以是多行多列的矩形区域,但必须是对单元格区域的引用,而不能是非引用类型的数组。
→ 解决方案2:
使用MATCH=ROW比较判断统计不重复数据个数。
操作方法
在C2单元格输入下列2个公式之一:
公式1 {=SUM(--(MATCH(A2:A16,A2:A16,)=ROW(2:16)-1))}
公式2 =SUMPRODUCT(--(MATCH(A2:A16,A2:A16,)=ROW(2:16)-1))
→ 原理分析
1.使用MATCH函数返回区域内每个数据第一次出现的位置数组:
{1;2;3;4;1;6;4;8;9;10;8;12;13;2;4}
2.然后与其对应的行号位置比较,因为只有第一次出现的位置才会一致,所以统计的是不重复个数。
3.使用减负运算将判断结果返回的逻辑值转换为1、0,最后使用SUM或SUMPRODUCT求和即可得出不重复数据的个数。
→ 知识扩展
MATCH=ROW法统计不重复值的优缺点
1.统计区域内不得有空单元格,否则MATCH函数返回#N/A错误。对于包含空单元格的区域,可以用文本合并进行相应的处理,公式如下:
=SUMPRODUCT((A2:A16<>"")*(MATCH(A2:A16&"",A2:A16&"",0)=ROW(2:16)-1))
其中,(A2:A16<>"")用于防止将空单元格&""后算作一个空文本数据。
2.不会有浮点运算误差。
3.数据类型可以是文本、数值、逻辑值,但不得包含错误值。
4.数据可以是内存数组,也可以是单元格区域的引用,但必须是单行或单列。
→ 解决方案3:
使用FREQUENCY函数统计不重复数字个数。
→ 操作方法
在C2单元格输入下列2个公式之一:
公式1 =COUNT(1/FREQUENCY(A2:A16,A2:A16))
公式2 =SUM(--(FREQUENCY(A2:A16,A2:A16)>0))
→ 原理分析
1.使用FREQUENCY函数返回统计区域内数字的分布频率数组:
{2;2;1;3;0;1;0;2;1;1;0;1;1;0;0;0}
由此可见,第一次出现的数字位置返回数字出现个数,而第2次出现就返回0,因此只要统计非0的个数即可。
2.公式1使用1/FREQUENCY将0转换为#DIV/0!错误值,再利用COUNT函数忽略错误值的特性统计数组中非0数字的个数,公式2使用--(FREQUENCY>0)将数组大于0判断得到的逻辑值转换为数值1、0,再使用SUM函数求和,由此实现统计不重复数据个数。
3.由于FREQUENCY函数默认返回数组,COUNT、SUM函数均将其视为常量数组代入计算,因而无需按
→ 知识扩展
FREQUENCY函数统计不重复值的优缺点
1.统计区域可以有空单元格。因为FREQUENCY函数将忽略空白单元格和文本。
2.使用公式2没有浮点运算误差。
3.数据必须为数值,如果是一维的文本数据,可以借助MATCH函数转换为序列号数值再进行统计。
4.参数不受引用或数组的尺寸范围限制,可以支持多行多列的数值数据的不重复统计。
-
演讲的一些小技巧
演讲的一些小技巧1演讲表达的主要特点是“讲”,对演讲者来说,写好了演讲词,不一定就讲得好,正如作曲家不一定是演唱家一样。有文才,善于写出好的演讲词的人,不一定有口才,不一定能讲得娓娓动听。真正的演讲家,既要善写,还要会讲,即既要有文才又要有口才。从某种意义上说,...
-
《那树》阅读题目和答案分析
但是,这世界上还有别的东西,别的东西延伸得更快,柏油路一里一里铺过来,高压线一千码一千码架过来,公寓楼房一排一排挨过来。所有原来在地面上自然生长的东西都被铲除,被连根拔起。只有那树被一重又一重死鱼般的灰白色包围,连根须都被压路机碾进灰色之下,但树顶仍在雨后...
-
女性穿衣搭配技巧有哪些
女性穿衣搭配技巧有哪些11.由浅入深,穿衣有三层境界:第一层是和谐,第二层是美感,第三层是个性。2.聪明、理智的你买衣服时可以根据下面三个标准选择,不符合其中任何一个的都不要掏出钱包:你喜欢的、你适合的、你需要的。3.经典很重要,时髦也很重要,但切不能忘记的是一...
-
【精华】小学五年级语文试卷分析
在日常学习、工作生活中,我们都可能会接触到试卷,试卷是课程考核统计分析工作的重要组成部分,它包括试卷的信度、效度、区分度、难度四个方面。什么样的试卷才能有效帮助到我们呢?以下是小编整理的小学五年级语文试卷分析,仅供参考,欢迎大家阅读。一、试题分析这次语...