注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

徕卡要钱,蔡司要命

别买副厂镜头!Carl Zeiss除外!

 
 
 

日志

 
 

【转载资料】excel 求唯一值个数公式(详细汇总)  

2013-11-26 11:25:41|  分类: 其他 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

最近工作涉及到一些数据分析,找到一篇有用的资料,转帖供以后参考使用。作者不详,谨致谢意。


excel唯一值个数7种公式,带大括号的为数组公式,需要按CTRL+SHIFT+ENTER三键结束输入

1 {=SUM(1/COUNTIF(B1:B44,B1:B44))}

2 {=SUM(N(MATCH(B1:B44,B1:B44,)=ROW(1:44)))}

3 {=SUMPRODUCT((B1:B44<>"")/COUNTIF(B1:B44,B1:B44&""))}

4 =SUMPRODUCT(1/COUNTIF(B1:B44,B1:B44))

5 {=SUM(N(FREQUENCY(B2:B45,B2:B45)>0))}

6 {=SUM(--(MATCH(B1:B44,B1:B44,)=ROW(1:44)))}

7{ =SUM(--(FREQUENCY(B1:B44,B1:B44)>0))}

Excel 中用公式列出唯一值——模拟高级筛选功能

在 Excel 中处理数据时,我们经常会遇到包含重复数值的列。比如,员工表或客户表的城市一列,又比如商品销售清单的商品名称一栏。数据处理工作的一个内容可能就是列出这些数据的唯一值,看看哪些城市有客户,供应商或员工,看看哪些商品被卖了出去。这个任务用 Excel 的高级筛选功能是非常容易做到的。不过,也许是出于对算法的追求,有一个用公式的解法来列出唯一值。我不是这个公式的原创者,但还是很愿意解释一下这个公式,看一下例子吧。

 ABCD
1城市 城市位置
2上海 上海2
3上海 北京4
4北京 广州5
5广州 南京7
6广州 011
7南京  #N/A#N/A
8北京   
9上海   
10广州   

A 栏是包含了重复值的一列城市名,我们要在 C 栏用公式列出唯一的城市名。示例数据中我们用到了列名“城市”,这是个好的习惯,因为用高级筛选功能也是要求原始数据有列名的。

在 C2 输入 =INDEX(A:A,MATCH(0,COUNTIF(C$1:C1,$A$1:$A$11),)),按 CTRL+SHIFT+ENTER 作为数组公式输入。下面来解释一下这个公式的构造:

要在 C 栏列出唯一值,肯定要在原始数据,即 A 栏中进行查找,这是最外面的 INDEX 公式的用处。现在的问题变成,如何构造一个公式能返回每个值第一次出现时的位置。接下来考察一下 Match 这个公式,在 D2 输入 =MATCH(0,COUNTIF(C$1:C1,$A$1:$A$11),),也是按 CTRL+SHIFT+ENTER 作为数组公式输入,从结果可以看出,D 栏列出了每个唯一值在 A 栏第一次出现时的序号或位置。

这个问题可以这样考虑,如果我们能在 C 栏通过公式列出唯一值,那么假定我们已经列出了一部分唯一值。接下来的那个唯一值,也即 A 栏中将要取出的那个值,在 C 栏已有的唯一值列表中是找不到的。也就是说,将要取出的那个值,在 C 栏已有的唯一值列表中出现次数是0。它上面的那些值都已经在  C 栏已有的唯一值列表中出现过了,否则也不会轮到它。这就是 Match 函数要从一个序列中查找 0 值的意义所在。那么,怎么构建一个序列,对应 A 栏中的数据是否在 C 栏中出现过呢?这个序列应该是类似 {1,0,0,1,1,0,……}这个样子,因为 C 栏是唯一值,对于 A 栏中的每个数据在 C 栏中只可能出现一次,返回一个 1,不出现就返回 0。当这个数据在 A 栏中重复出现时,又会返回一个 1,我们期望的序列是和 A 栏的数据一一对应的,对应关系是——这个值是否在 C 栏已有列表中出现过。

用 CountIf 函数来构造这个序列,我只能说这是基于对数组函数和 CountIf 函数本身的及其熟稔才能做到的。CountIf 函数返回的序列的大小(元素个数)和它的第二个参数的大小是一致的。就这个例子而言,COUNTIF(C$1:C1,$A$1:$A$11),表示对 $A$1:$A$11 中的每个数据在 C$1:C1 中进行查找,返回一个是否存在的 0,1值序列。

这个问题的另一个技巧就是 C$1:C1 的半固定表示法。当公式向下复制的时候,C1 会增长为 C2,C3 等等,永远只在已有的唯一值列表中查找。对于 Excel,这是个递归的计算。我们的逻辑,也是用归纳法推出来的。

  评论这张
 
阅读(120)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017