【收集】Excel公式:非空非错误值计数+提取不重复值
(2017-12-06 16:20:07)
标签:
excel公式不重复值计数提取 |
分类: 电脑 |
| 源:Excel|【练习题】统计非空非错误值个数 - http://www.exceltip.net/thread-23677-1-1.html
求A1:A10中非空、非错误值个数,纯函数。无名称,无辅助列,无VBA
公式
=COUNT(CODE(A1:A10))
=COUNT(0/LEN(A1:A10))
=COUNT(1/(A1:A10<>""))
=COUNT(IF(A1:A10<>"",1))
=COUNT(0/(LEN(A1:A10)<>0)*1)
=COUNT(-TEXT(A1:A10,"1;;;!1"))
=SUM(--IF(ISERROR(A1:A10),,IF(A1:A10"",,1)))
=COUNT(1/IF(A1:A10<>"",NOT(ISERROR(A1:A10))))
=SUM(1*ISNUMBER(1*(A1:A10>0)),-1*ISERROR(A1:A10))
=COUNTIF(A1:A10,"<>""")-COUNTIF(A1:A10,"")-SUMPRODUCT(N(ISERROR(A1:A10)))
【公式提取不重复值】
| Excel excel提取不重复值(重复的保留一个)- http://club.excelhome.net/thread-1064283-1-1.html
提取A2:A11中的不重复值。
一、INDEX—MATCH=ROW 法
=INDEX(A:A,SMALL(IF(MATCH($A$2:$A$11,$A$2:$A$11,0)=ROW($A$2:$A$11)-1,ROW($2:$11),4^8),ROW(1:1)))&""
分析:
1、对 A 列中的每一项利用 MATCH 函数找出其在 A 列中首次出现的位置,返回相应的行 号。超出列表范围时将返回一个较大的行号,如 65536。
2、利用 SMALL 函数对这些行号数据由小到大排序。
3、利用 INDEX 函数返回与查找到的行号相应的 A 列的值。
4、用 A 列的值与””连接作为最终的值,当行号足够大时,对应的 A 列单元格为空单元格, 空单元格与""连接,依然是空单元格。
最终结果,从 A 列筛选出的没有重复数据的单元格排在表格的上半部分,下半部分为空单 元格,正是我们想要的效果。
二、COUNTIF 法
=INDEX(A:A,MIN(IF(COUNTIF($C$1:C1,$A$2:$A$160)=0,ROW($A$2:$A$160),4^8)))& amp;""
(C2开始输入公式)
这种解法看着有点怪异,一般人是想不到的,除非你是二般人。说它怪,怪在哪儿呢?怪就怪在它的着眼点让人匪夷所思:
1、要查找 A 列中不重复的数据,一般人都会首先考虑怎样处理 A 列中的数据,其它无关的或空白区域是不会上眼的。这种解法的独到之处就是首先在一空白列处查找 A 列中的数据, 如果找不到就返回该数据所在的行号,否则返回一个较大的值 65536。
2、用 MIN 函数据找到 A 列中的数据首次出现的行号。
3、用 INDEX 函数在空白列处返回对应的 A 列中的值。
三、FREQUENCY 法(仅适用于数值)
=INDEX(A:A,SMALL(IF(FREQUENCY($A$2:$A$11,$A$2:$A$11),ROW($A$2:$A$11),4^8), ROW(1:1)))&""
1、用 FREQUENCY 函数判断 A 列中的每一个数据是否在 A 列中出现一次,如果仅出现一 次就返回当前行号,否则返回一个较大的数 65536。
2、用 SMALL 函数对返回的行号从由小到大排序。
3、用 INDEX 函数返回对应的 A 列中的值。
四、COUNTIF 法
=IF(COUNTIF($A$2:A2,A2)>1,"",A2)
1、利用函数 COUNTIF 统计 A 列中的数据是否大于 1,如果大于 1,返回空值,否则返回 当前值。
2、对生成的数据排序以清除空白单元格。
五、其它方法。
公式一:=SUM(1/countif(A1:A10,A1:A10)) 按 Ctrl+Shift+回车键结束
公式二:=COUNT(0/(MATCH(A1:A10,A1:A10)=ROW(1:10))) 按 Ctrl+Shift+回车键结束
公式三:=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))
公式四:=COUNT(0/(COUNTIF(A1:A10,A1:A10)=1)) 按 Ctrl+Shift+回车键结束
公式五:=SUM(N(COUNTIF(A1:A10,A1:A10)=1)) 按 Ctrl+Shift+回车键结束
公式六:=SUM(INT(1/COUNTIF(A1:A10,A1:A10)))按 Ctrl+Shift+回车键结束