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

我是一片叶筋脉是森林我是一滴水魂魄是海洋

爱你们,所有的人,祝福你们,所有的人。Love all of you,guys

 
 
 

日志

 
 

EXCEL--计算异值数量、提取不重复项  

2013-05-08 11:22:19|  分类: 软件教程/Excel中 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

EXCEL--计算异值数量、提取不重复项 - 愛онd承諾 - 我是一片叶筋脉是森林我是一滴水魂魄是海洋

 

引言:

利用 Excel 的内置功能, 如:高级筛选 “筛选不重复的记录”(2003 版)和数据菜单中的“删除重复项”(2007 版)。 Excel 中的这些内置功能虽然操作简便、快捷,但操作步骤繁多,很多时候还需要增加辅助 列,不像用公式那样一个公式往下一拖就“OK”那样爽,不能满足广大公式、函数爱好者的 需求。这里是一些从网上收集的经典之作,贴出来与大家共同分享。

注:下方为详细说明,为网友节省分析时间。   

 一、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 列筛选出的没有重复数据的单元格排在表格的上半部分,下半部分为空单 元格,正是我们想要的效果。
二、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 列中的值。
三、INDEX—COUNTIF 法=INDEX(A:A,MIN(IF(COUNTIF($C$1:C1,$A$2:$A$160)=0,ROW($A$2:$A$160),4^8)))&""(C2开始输入公式)这种解法看着有点怪异,一般人是想不到的,除非你是二般人。说它怪,怪在哪儿呢?怪就 怪在它的着眼点让人匪夷所思:
1、要查找 A 列中不重复的数据,一般人都会首先考虑怎样处理 A 列中的数据,其它无关的 或空白区域是不会上眼的。这种解法的独到之处就是首先在一空白列处查找 A 列中的数据, 如果找不到就返回该数据所在的行号,否则返回一个较大的值 65536。 2、用 MIN 函数据找到 A 列中的数据首次出现的行号。 3、用 INDEX 函数在空白列处返回对应的 A 列中的值。
计算包含不同数据总数:
公式一:=SUM(1/COUNTIF(A1:A10,A1:A10))按 Ctrl+Shift+回车键结束 , 按 Ctrl+Shift+回车键结束
公式二:=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))同SUM函数
公式三:=SUM(INT(1/COUNTIF(A1:A10,A1:A10)))按 Ctrl+Shift+回车键结束 ,同SUM函数
公式四:=COUNT(0/(MATCH(A1:A10,A1:A10)=ROW(1:10))) 按 Ctrl+Shift+回车键结束(0/true=0;0/false=错误;总数为所有异值总数)

公式五:=SUM(N(MATCH(A1:A10,A1:A10)=ROW(1:10)) 按 Ctrl+Shift+回车键结束(N(true)=1,N(false)=0)

--------------------------------------------------------------------------

详细说明:单个数组-第一个单元格Ctrl+Shift+回车键,其余下拉填充;整体数组-选中所有单元格,输入公式,Ctrl+Shift+回车键。

一、INDEX—MATCH=ROW 法(适用于数字和文字)

 

工衣  结果

数组

(公式1)

数组

(公式2)

数组

(公式3)

数组

(公式4)

数组

(公式5)

单个数组

 公式(6-①)

整体数组

公式(6-②)

单个数组

公式(7-①)

整体数组

公式(7-②)

XL  XL 1 2 1 TRUE 2 2 2 XL XL
L  L 2 3 2 TRUE 3 3 3 L L
L  M 2 4 3 FALSE 65536 7 7 M M
XL   1 5 4 FALSE 65536 65536 65536    
XL   1 6 5 FALSE 65536 65536 65536    
M   6 7 6 TRUE 7 65536 65536    
M   6 8 7 FALSE 65536 65536 65536    
L   2 9 8 FALSE 65536 65536 65536    
XL   1 10 9 FALSE 65536 65536 65536    

        INDEX(A:A,SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-1,ROW($2:$10),4^8),ROW(1:10)),)&""                                                                       

INDEX(A:A,SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-1,ROW($2:$10),4^8),ROW(1:1)),)&""                                                                        

1.MATCH($A$2:$A$10,$A$2:$A$10,0)                                                                                                       

对该列区域每个数据由上之下进行1、2、3...排序,所有的重复数据将被“强行标识”为第一次出现时的序号(Match公式特性,注意:非行号、列号)                    

2.ROW($A$2:$A$10)                                                                                                     

列出对象区域每个数据的行号                                                                                                     

3.ROW($A$2:$A$10)-1                                                                                                  

将所有数据的行号减去一个数(最小行号-1),使数据行号变为从1开始的排列序号。(其实就是对所有数据从上到下进行1、2、3、4、排序,其他排序公式也可以)

4.MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-1                                                                                                        

通过判断,将重复数据第一次出现时标记为TURE,其他重复数据标记为FALSE                                                                                                     

5.IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-1,ROW($2:$10),4^8)                                                                                                   

将重复数据第一次出现时的标记其行号,其余非第一次出现的重复数据“强行标记”为一个超大值(4^8),便于下步对非第一次出现的重复数据进行排除。          

6.①SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-1,ROW($2:$10),4^8),ROW(1:10))                                                                                                 

②SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-1,ROW($2:$10),4^8),ROW(1:1))                                                                                             

对所有标识的数据按由大到小进行排序;                                                                                                        

①中数据源-数组,结果-数组:选中要将结果显示到的位置区域,Ctrl+Shift+Enter。                                                                                                     

②中数据源-数组,结果-非数组:选中要将结果显示到的位置区域的第一个单元格,Ctrl+Shift+Enter,得出一个值,之后再下拉填充,得出区域数值。          

7.①INDEX(A:A,SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-1,ROW($2:$10),4^8),ROW(1:10)),)                                                                            

②INDEX(A:A,SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-1,ROW($2:$10),4^8),ROW(1:1)),)                                                                       根据排序后的数据,从第一列查找对应值,被标识的超大值4^8,将得第4^8=65536行的空白区值。                                                                                                  

①中数据源-数组,结果-数组:选中要将结果显示到的位置区域,Ctrl+Shift+Enter。                                                                                                     

②中数据源-数组,结果-非数组:选中要将结果显示到的位置区域的第一个单元格,Ctrl+Shift+Enter,得出一个值,之后再下拉填充,得出区域数值。                        

8.①INDEX(A:A,SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-1,ROW($2:$10),4^8),ROW(1:10)),)&""                                                                     

②INDEX(A:A,SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-1,ROW($2:$10),4^8),ROW(1:1)),)&""                                                                     

后缀增加&"",将得出的数字型结果“0”强行转化为文本型空值。  

二、FREQUENCY 法(仅适用于数值)

 

仅适用于数字
数组 数组 单个数组 整体数组 单个数组 整体数组
公式1 公式2 公式3-① 公式3-② 公式4-① 公式4-②
59 3 2 2 2 59 59
59 0 65536 4 4 75 75
75 2 4 6 6 25 25
75 0 65536 9 9 58 58
25 2 6 65536 65536    
25 0 65536 65536 65536    
59 0 65536 65536 65536    
58 1 9 65536 65536  
 

INDEX(A:A,SMALL(IF(FREQUENCY($A$2:$A$9,$A$2:$A$9)>0,ROW($A$2:$A$9),4^8), ROW(1:1)))&""                                                               

1     FREQUENCY($A$2:$A$9,$A$2:$A$9)                                 

       用Frequnency函数,将各个数据做为分段点,从而计算出重复数据出现的频率,并将第一次出现时标识上频数,其余重复值计为0.                                  

2     IF(FREQUENCY($A$2:$A$11,$A$2:$A$11)>0,ROW($A$2:$A$11),4^8)                                 

       将重复数据第一次出现时的标记其行号,其余非第一次出现的重复数据“强行标记”为一个超大值(4^8),便于下步对非第一次出现的重复数据进行排除。          

3     ①SMALL(IF(FREQUENCY($A$2:$A$9,$A$2:$A$9)>0,ROW($A$2:$A$9),4^8),ROW(1:10))                               

       ②SMALL(IF(FREQUENCY($A$2:$A$9,$A$2:$A$9)>0,ROW($A$2:$A$9),4^8),ROW(1:1))                                

       对所有标识的数据按由大到小进行排序;                                  

       ①中数据源-数组,结果-数组:选中要将结果显示到的位置区域,Ctrl+Shift+Enter。                                

       ②中数据源-数组,结果-非数组:选中要将结果显示到的位置区域的第一个单元格,Ctrl+Shift+Enter,得出一个值,之后再下拉填充,得出区域数值。                    

4     ①INDEX(A:A,SMALL(IF(FREQUENCY($A$2:$A$9,$A$2:$A$9)>0,ROW($A$2:$A$9),4^8), ROW(1:1)))&""                                 

       ②INDEX(A:A,SMALL(IF(FREQUENCY($A$2:$A$9,$A$2:$A$9)>0,ROW($A$2:$A$9),4^8), ROW(1:10)))&""                               

       根据排序后的数据,从第一列查找对应值,被标识的超大值4^8,将得第4^8=65536行的空白区值;后缀增加&"",将得出的数字型结果“0”强行转化为文本型空值。

       ①中数据源-数组,结果-数组:选中要将结果显示到的位置区域,Ctrl+Shift+Enter。                                

       ②中数据源-数组,结果-非数组:选中要将结果显示到的位置区域的第一个单元格,Ctrl+Shift+Enter,得出一个值,之后再下拉填充,得出区域数值。

第三、INDEX—COUNTIF(适用于数字和文字)

 

工衣尺码  结果 公式1 公式2 公式3 公式4
XL XL 0 2 2 XL
L L 0 3    
L M 0 4    
XL   0 5    
XL   0 6    
M   0 7    
M   0 8    
L   0 9    
公式5 公式6 公式7 公式8
1 65536 3 XL
0 3   L
0 4    
1 65536    
1 65536    
0 7    
0 8    
0 9    

 

       INDEX(A:A,MIN(IF(COUNTIF($C$1:C1,$A$2:$A$160)=0,ROW($A$2:$A$160),4^8)))&""                                        

1     C2开始输入公式,此公式中数据源-数组,结果-非数组:选中要将结果显示到的位置区域的第一个单元格,Ctrl+Shift+Enter,得出一个值,之后再下拉填充,得出区域数值。                                      

       注意此公式,下拉填充会发现,下一个单元格的公式中使用了上一个单元格的数据,因此不能整体分析,需分别对上下单元格的公式进行分析对比,才能得出公式原理。                                      

2     COUNTIF($C$1:C1,$A$2:$A$9)                                        

       数组公式,从C1单元格分别查找包含A2.A3..A9单元格内容的个数,并得出数组{0;0;0;0;0;0;0;0}(目的就是提取并使用第一个数)。                                       

3     IF(COUNTIF($C$1:C1,$A$2:$A$9)=0,ROW($A$2:$A$9),4^8)                                         

       当计算结果为0,将显示行号,否则显示一个超大值4^8=65536,第一个将显示每个单元格行号{2;3;4;5;6;7;8;9}                                       

4     MIN(IF(COUNTIF($C$1:C1,$A$2:$A$9)=0,ROW($A$2:$A$9),4^8))                                       

       该单元格的公式将提取最小的行号,即“2”                                    

5     INDEX(A:A,MIN(IF(COUNTIF($C$1:C1,$A$2:$A$9)=0,ROW($A$2:$A$9),4^8)))&""                                        

       从A列查找返回第2行的数据“XL”,&“”是得出的数字型结果“0”强行转化为文本型空值。                                         

       C2单元格得到值                                    

6     COUNTIF($C$1:C2,$A$2:$A$9)                                        

       数组公式,从C1和C2单元格组成的区域中分别查找包含A2.A3..A9单元格内容的个数,并得出数组{1;0;0;1;1;0;0;0}。                                         

7     IF(COUNTIF($C$1:C2,$A$2:$A$9)=0,ROW($A$2:$A$9),4^8)                                         

       当计算结果为0,将显示行号,否则显示一个超大值4^8=65536,将显示{65536;3;4;65536;65536;7;8;9}(将和C1、C2单元格内容不相同的行提取出来)          

8     MIN(IF(COUNTIF($C$1:C2,$A$2:$A$9)=0,ROW($A$2:$A$9),4^8))                                       

       该单元格的公式将提取最小的行号,也就是提取出和C1:C2区域不相同的所有行中最小行号3                                       

9     INDEX(A:A,MIN(IF(COUNTIF($C$1:C2,$A$2:$A$9)=0,ROW($A$2:$A$9),4^8)))&""                                        

       从A列查找返回第4行的数据“L”,&“”是得出的数字型结果“0”强行转化为文本型空值。                                    

       C3单元格得到值                                    

10    同理                                         

       INDEX(A:A,MIN(IF(COUNTIF($C$1:C3,$A$2:$A$9)=0,ROW($A$2:$A$9),4^8)))&""                                        

       提取出和C1:C2区域不相同的最小行中的值,C4将显示与之前不同的第3个值                                      

11    以此类推                                         

       INDEX(A:A,MIN(IF(COUNTIF($C$1:C4,$A$2:$A$9)=0,ROW($A$2:$A$9),4^8)))&""                                        

       INDEX(A:A,MIN(IF(COUNTIF($C$1:C5,$A$2:$A$9)=0,ROW($A$2:$A$9),4^8)))&""                                        

       INDEX(A:A,MIN(IF(COUNTIF($C$1:C5,$A$2:$A$9)=0,ROW($A$2:$A$9),4^8)))&""                                        

       C5、C6、C7 等将显示与之前不同的值,直到找不到异值,将开始显示为空值  

------------------------------------------------------------------------------------------------------------------------------

     计算包含不同数据总数:

第一、SUM-COUNTIF法

工衣尺码  结果 公式① 公式② 公式③
XL  3 3 0.333333 3
L   3 0.333333  
L   3 0.333333  
XL   3 0.333333  
XL   3 0.333333  
M   2 0.5  
M   2 0.5  
L   3 0.333333  
 

SUM(1/COUNTIF(A1:A10,A1:A10)),按 Ctrl+Shift+回车键结束               

原理:(1/m×m)+(1/n×n)+(1/p×p)=3              

公式①    MATCH($A$2:$A$9,$A$2:$A$9,0)           

公式②    1/COUNTIF($A$2:$A$9,$A$2:$A$9)        

公式③    SUM(1/COUNTIF($A$2:$A$9,$A$2:$A$9)),按 Ctrl+Shift+回车键结束

第二、COUNT-MATCH法

工衣尺码  结果 公式① 公式② 公式③ 公式④ 公式⑤
XL  3 1 1 TRUE 0 3
L   2 2 TRUE 0  
L   2 3 FALSE #DIV/0!  
XL   1 4 FALSE #DIV/0!  
XL   1 5 FALSE #DIV/0!  
M   6 6 TRUE 0  
M   6 7 FALSE #DIV/0!  
L   2 8 FALSE #DIV/0!  
 

COUNT(0/(MATCH(A2:A9,A2:A9)=ROW(1:10))),按 Ctrl+Shift+回车键结束                               

原理:(0/true=0;0/false=错误;count()包含数字单元格“0”的个数)                                

公式①    MATCH($A$2:$A$9,$A$2:$A$9,0)                         

公式②    ROW(1:8)                          

公式③    MATCH($A$2:$A$9,$A$2:$A$9,0)=ROW(1:8)                      

公式④    0/(MATCH($A$2:$A$9,$A$2:$A$9,0)=ROW(1:8))                        

公式⑤    COUNT(0/(MATCH(A2:A9,A2:A9)=ROW(1:10))),按 Ctrl+Shift+回车键结束                                                                                                                                                                                                              

第三、其余

其余公式和以上公式原理相似,可很容易类比分析出。 

 

结束语:

相信通过以上详细解释,网友已经能够很好地利用excel函数进行异值计算和不重复值的提取。

EXCEL--计算异值数量、提取不重复项 - 愛онd承諾 - 我是一片叶筋脉是森林我是一滴水魂魄是海洋

 

 

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

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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