手把手教你,学会提取不重复值

您所在的位置:网站首页 excel用公式提取不重复数据 手把手教你,学会提取不重复值

手把手教你,学会提取不重复值

2023-06-01 08:02| 来源: 网络整理| 查看: 265

公式中的“COUNTIF(F$1:F1, C$2:C$12)”部分利用COUNTIF函数在公式所在位置上方的单元格区域中,分别查找C$2:C$12单元格区域中每个数据的个数。

COUNTIF函数的第一参数F$1:F1利用绝对引用和相对引用的技巧,形成一个自动扩展的数据范围。当公式向下复制时,查找区域依次变为$F$1:$F2、$F$1:$F3、…、$F$1:$F11。COUNTIF函数返回一个由0和1构成的数组,其中0表示该姓名在公式上方未出现过,1表示该姓名在公式上方已出现过。

然后用MATCH函数在COUNTIF函数返回的数组中查找第一个0的位置,即查找下一个尚未出现的姓名所在的位置。

再利用INDEX函数,根据MATCH函数的结果从C列中返回对应位置的内容。由于数据表有一个标题行,因此,将MATCH函数的结果加1,用于匹配在数据表中的位置。

本例中COUNTIF函数的第二参数C$2:C$12比实际数据区域多出一行,目的是当公式复制的行数超出不重复数据的个数时,得到的内存数组中最后一个元素始终为0,从而避免MATCH函数由于查找不到0而返回错误值。

3. COUNTIF函数和MIN函数结合法

在H2单元格中输入以下数组公式,按组合键将公式向下复制到单元格显示空白为止。

{=INDEX(C:C,MIN(IF(COUNTIF(H$1:H1,C$2:C$11),4^8,ROW($2:$11))))&""}

公式中的“IF(COUNTIF(H$1:H1,C$2:C$11),4^8,ROW($2:$11))”部分表示,如果数据在公式之前的范围中出现过,则返回65536;否则返回对应的行号。

以H2单元格为例,返回内存数组结果如下。

{2;3;4;5;6;7;8;9;10;11}

以H3单元格为例,由于H2单元格中的姓名已经在C列出现过,因此返回内存数组结果如下。

{65536;65536;65536;5;6;7;8;9;10;11}

随着公式的向下复制,用MIN函数依次提取尚未出现的姓名的最小行号。最后用INDEX函数得到该行号对应的姓名。

二维数据表提取不重复记录

示例26-8 在值班表中提取不重复姓名

图26-9展示的是某公司各部门值班表的部分内容,需要提取不重复的姓名列表。

在F2单元格中输入以下数组公式,按组合键将公式向下复制到单元格显示为空白为止。

{=INDIRECT(TEXT(MIN(IF(COUNTIF(F$1:F1,B$2:D$8)=0,ROW($2:$8)*1000+COLUMN(B:D),2^20)),"R0C000"),0)&""}

公式使用COUNTIF函数,判断公式所在行之前的区域中是否包含有B2:D8单元格区域中的姓名。

如果数据列表中的姓名没有出现过,返回对应的行号乘以1 000加列号,否则返回2^20即1 048 576。

行号乘以1000加列号的目的是行号放大1 000倍后再与列号相加,使其后3位为列号,之前的部分为行号,相加时互不干扰,以F2单元格公式为例,此部分的结果如下。

{2002,2003,2004;3002,3003,……,6004;7002,7003,7004;8002,8003,8004}

再使用MIN函数提取出加权计算后的最小行列号组合值2002。TEXT函数将其转换

为“R1C1”引用样式的文本型单元格地址字符串“R2C002”。

INDIRECT函数第二参数使用0,表示以“R1C1”引用样式返回对文本型单元格地址字符串的引用。R2C002就是引用工作表中第二行第二列的单元格,即B2单元格。

提取指定条件的不重复记录

示例26-9 按区域提取不重复客户记录

图26-10展示的是保险公司客户记录表的部分内容,需要根据G1单元格中指定的客户区域,筛选出该区域不重复的客户编号。

在F4单元格中输入以下数组公式,按组合键将公式向下复制到F9单元格。

{=INDEX(B:B,SMALL(IF((MATCH(A$2:A$17&B$2:B$17,A:A&B:B,)=ROW($2:$17))*

(A$2:A$17=G$1),ROW($2:$17),4^8),ROW(A1)))&""}

公式中的“MATCH(A$2:A$17&B$2:B$17,A:A&B:B,)”部分先使用连接符将区域和客户编号两个字段连接形成单列数据,然后用MATCH函数返回连接后的字符串首次出现的位置。

再使用 MATCH 函数的位置结果与序号比较,并结合区域判断条件“ (A$2:A$17=G$1) ”,让符合区域条件且首次出现的客户编号记录返回对应行号,而不符合区域条件或是重复的客户编号记录返回65536。

最后利用SMALL函数从小到大提取出行号,并借助INDEX函数返回对应的客户编号记录。

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

推荐图书

北京大学出版社

《 Excel 2016函数与公式大全》

1. 专家云集:多位身处各行各业,并身怀绝技的微软全球有价值专家与您无私分享。多年对 Excel的研究结果进行揭秘。

2. 知识点全覆盖:详尽而又系统地介绍了 Excel 2016函数与公式的所有技术特点和应用方法,全面覆盖相关知识点,完备知识体系无人能及。

3. 解决实际问题:大量源自实际工作的典型案例,通过细致地讲解,生动地展示各种应用技巧,快速提高读者的办公效率,让读者提前完成手头工作,不用加班。

4. 专业级深度剖析:对常常困扰学习者的功能性特性进行深入剖析,可以让读者既能知其然,又能知其所以然。返回搜狐,查看更多



【本文地址】


今日新闻


推荐新闻


CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3