手把手教你,学会提取不重复值 |
您所在的位置:网站首页 › excel用公式提取不重复数据 › 手把手教你,学会提取不重复值 |
公式中的“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 |