|
图26-8展示的是某单位销售记录表的部分内 容，需要提取不重复的客户代表姓名。在F2单元格中输入以下数组公式，按<Ctrl+Shift+Enter>组合键将公式向下复制到单元格显示空白为止。{=INDEX(C:C,SMALL(IF(MATCH(C$2:C$11,C:C,)=ROW($2:$11),ROW($2:$11),4^8),ROW(A1)))&''}公式中的“MATCH(C$2:C$11,C:C,)”部分利用MATCH函数在C列中依次查找C2:C11单元格区域中每个元素首次出现的位置，结果如下。然后将以上内存数组结果与数据所在行号“ROW($2:$11)”进行比对，如果查找的位置序号与数据自身的位置序号一致，则表示该数据是首次出现；否则是重复出现。当MATCH函数结果与数据自身的位置序号相等时，返回当前数据行号；否则返回65 536。再通过SMALL函数将行号从小到大依次取出，最终由INDEX函数返回该位置的姓名，得到不重复的姓名列表。在F2单元格中输入以下数组公式，按<Ctrl+Shift+Enter>组合键将公式向下复制到单元格显示空白为止。{=INDEX(C:C,1+MATCH(,COUNTIF(F$1:F1,C$2:C$12),))&''}公式中的“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而返回错误值。在H2单元格中输入以下数组公式，按<Ctrl+Shift+Enter>组合键将公式向下复制到单元格显示空白为止。{=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；否则返回对应的行号。以H3单元格为例，由于H2单元格中的姓名已经在C列出现过，因此返回内存数组结果如下。{65536;65536;65536;5;6;7;8;9;10;11}随着公式的向下复制，用MIN函数依次提取尚未出现的姓名的最小行号。最后用INDEX函数得到该行号对应的姓名。图26-9展示的是某公司各部门值班表的部分内容，需要提取不重复的姓名列表。在F2单元格中输入以下数组公式，按<Ctrl+Shift+Enter>组合键将公式向下复制到单元格显示为空白为止。{=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-10展示的是保险公司客户记录表的部分内容，需要根据G1单元格中指定的客户区域，筛选出该区域不重复的客户编号。在F4单元格中输入以下数组公式，按<Ctrl+Shift+Enter>组合键将公式向下复制到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函数返回对应的客户编号记录。 
--------------------------------------------------------------------- 推荐图书 
1. 专家云集：多位身处各行各业，并身怀绝技的微软全球有价值专家与您无私分享。多年对 Excel的研究结果进行揭秘。
2. 知识点全覆盖：详尽而又系统地介绍了 Excel 2016函数与公式的所有技术特点和应用方法，全面覆盖相关知识点，完备知识体系无人能及。
3. 解决实际问题：大量源自实际工作的典型案例，通过细致地讲解，生动地展示各种应用技巧，快速提高读者的办公效率，让读者提前完成手头工作，不用加班。
4. 专业级深度剖析：对常常困扰学习者的功能性特性进行深入剖析，可以让读者既能知其然，又能知其所以然。
|
