陶泽昱Excel应用技巧大全第25期:数据筛选技术

提取不重复数据主要是指在一个数据表中提取唯一的记录,即重复记录只算1条。这在我们的日常工作中会经常遇到,比如在统计人员名单时,有可能因为重复统计等原因而重复记录,通过本文介绍的方法便可以将不重复部分查找出来,进而进行相关的统计计算。当然,使用Excel的“高级筛选”能够生成不重复记录的结果,但是通过公式如何实现该功能呢?下面主要介绍如何使用函数来实现,能够快速、准确地实现该功能。

一、一维区域取得不重复记录

例1 从企业员工工资表中提取唯一部门

如图1所示,展示了一份企业员工工资表,为了便于统计各部门的应发工资,利用下面的公式将取得唯一部门列表,并统计部门工资总额。

根据MATCH函数查找数据的原理,当查找的位置序号与数据自身的位置序号不一致时,则表示该数据重复,因此I3单元格中提取不重复记录的公式为:

公式中主要利用MATCH函数进行部门名称定位的方法,利用列表查找重复部门名称始终定位首记录的原理,当MATCH函数结果与序号比较相等时,则赋值为当前数据的行号,否则指定一个行号65532(主要为了容错处理,而该行通常是无数据的空单元格),再通过SMALL函数将行号逐个取出来,最后由OFFSET函数生成结果。

取出部门名称结果如图2所示。

最后利用SUMIF函数即可统计出所有部门的工资总额,如K3单元格公式如下:

=SUMIF(D3:D17,J3,H3:H17)

二、多条件提取唯一记录

例2 从商品明细表中提取唯一品牌名称

在如图3所示的商品进货明细表中,当用户指定具体的商品大类,如“空调”后,再筛选其下品牌的不重复品牌记录列表。

F6提取唯一记录公式:

{=INDEX(B:B,MATCH(0,COUNTIF(F$5:F5,$B$2:$B$14)+($A$2:$A$14$F$3)*($A$2:$A$14””),0)+1)&””}

该公式主要利用MATCH函数定位0值的技巧来处理,同时利用COUNTIIF函数结合混合引用统计当前公式坐在的F列中已经提取了那些品牌名称,未提取出来的品牌记录将被计数为0,从而通过定位0来取得记录。

而后添加“+($A$2:$A$14$F$3)*($A$2:$A$14””)”的特殊处理,将不属于指定商品的品牌过滤掉,再进行列表得出结果。

另外,利用INDEX函数结合SMALL函数的常规解法也同样可以实现,如公式修改为:

{=INDEX(B:B,SMALL(IF((A$2:A$13=F$3)*(MATCH(A$2:A$13&B$2:B$13,A$2:A$13&B$2:B$13,0)=ROW(A$2:A$13)-1),ROW(A$2:A$13),4^8),ROW(A1)))&””}

该解法使用常规的MATCH函数定位原理,将多关键字利用字符连接生成单列数据进行比较,最后使用INDEX函数逐个取得结果。

同样地,利用之前统计不重复记录的技术,在此条件下也可以进行不重复品牌的数据,具体的统计公式如下:

{=SUM(($A$2:$A$13=$F$3)/COUNTIFS($A$2:$A$13,$A$2:$A$13,$B$2:$B$13,$B$2:$B$13))}

该解法同样使用了COUNTIF函数统计技术,只是将单个统计条件增加为多个条件,原理完全相同,最终统计出来的“洗衣机”商品其下的品牌数量为4。

三、两维数据表提取不重复数据

在某些时候,企业的销售信息表并不是按照标准的数据库格式存放的,而对于这类多行多列存储的销售信息表,从中取得唯一的客户列表,则需要利用数组公式在二维的数据表中进行筛选。

例3 根据商品和品牌确定唯一品牌名称

如图4所示,展示了一份某期间内全国各商场销售情况明细表,表中按各地理区域进行了划分,下面将使用函数公式从明细表中筛选中唯一的商家名单。

从上表中可以可看到个商家名称排列在一个二维的数据表中,要取得商家名称必须通过条件判断将其单独筛选出来,如在K2单元格输入如下数组公式:

{=INDIRECT(TEXT(MIN(IF((COUNTIF(K$1:K1,$A$2:$H$6)=0)*(MOD(COLUMN($A$1:$H$1),3)=2)*($A$2:$H$6””),ROW($A$2:$H$6)*1000+COLUMN($A$2:$H$6),65536256)),”R0C00”),)&””}

该解法主要按条件筛选出商家名称,根据行列号重新生成序号数组,再后利用TEXT函数和INDIRECT函数生成R1C1引用样式的单元格来生成商家列表。其中解法的关键点有以下3点。

(1)过滤条件公式段:(COUNTIF(K$1:K1,$A$2:$H$6)=0)是去除重复数据的重点公式;(MOD(COLUMN($A$1:$H$1),3)=2)公式段主要是为了过滤其他非商家名称数据列,目的是通过K列的自身筛选去除已经列表的商家名称。

(2)IF(过滤条件,ROW($A$2:$H$6)*1000+COLUMN($A$2:$H$6),65536256)公式中的65536256主要是用于容错处理(为了兼容Excel 2003的用户),通过INDIRECT(“R65536c256”,0)&””,则固定引用整个工作表(基于Excel 2003系统)的最后一个单元格,保证列表外的多余数据均显示为空白,从而省略了常规的错误判断。

(3)而INDERECT(TEXT(MIN公式段,”R0C000”),0)的用法则是一种应用技巧,它能够将行列号组合序号快速转换为单元格引用,从而返回真正的商家列表。

最后筛选结果如图5所示。

  • 发表于:
  • 原文链接:https://kuaibao.qq.com/s/20180814A1TJ5T00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券