excel公式技巧：从单元格区域的字符串中提取唯一值( 三 ) _知识经验

这里为一个10行4列的数组。
下图2展示了MID函数运行的结果。

图2
TRIM函数使上述数组变为：
{“Amaranth”,”Bronze”,”Silver”,””;”Bronze”,””,””,””;””,””,””,””;”Violet”,”Bronze”,”Amaranth”,””;”Red”,””,””,””;”Puce”,”Bronze”,””,””;”Taupe”,”Ochre”,”Bronze”,”Cerise”;”Silver”,”Red”,”Cerise”,”Orange”;””,””,””,””;”Cerise”,””,””,””}
现在，创建了一个由单元格区域Data中所有单个子字符串（或单词）组成的数组，接着可以开始考虑处理该数组中的元素以达到我们的要求。
（2）下面，要考虑从数组中创建唯一值列表。我们有一些从列表中创建唯一值的标准公式，例如下图3所示。

图3
在单元格B2中，计算列表中返回的唯一值个数：
=SUMPRODUCT((A2:A10<>””)/(COUNTIF(A2:A10,A2:A10&””)))
在列D中，使用FREQUENCY函数来获取唯一值列表。在单元格D2中输入数组公式：
=IF(ROWS($1:1)>$B$2,””,INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(IF($A$2:$A$10<>””,MATCH($A$2:$A$10,$A$2:$A$10,0)),ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10))+1),ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10))+1),ROWS($1:1))))
下拉至出现空单元格为止。
在列E中，使用COUNTIF函数来获取唯一值列表。在单元格E2中输入数组公式：
=IF(ROWS($1:1)>$B$2,””,INDEX($A$2:$A$10,MATCH(0,IF($A$2:$A$10<>””,COUNTIF(E$1:E1,$A$2:$A$10&””)),0)))
下拉至出现空单元格为止。
（作者个人倾向于使用第1个公式，更灵活且比COUNTIF版本要更快，特别是，想要从中获得唯一值的数组是从公式中的其他函数生成的数组的情形下。COUNTIF函数的缺点在于传递给它的参数必须是实际的工作表区域引用。）
从上面的示例中可以看出， FREQUENCY函数可以处理单行或单列数组，而我们这里生成的是10行4列数组，那么FREQUENCY函数可以处理这样的二维数组吗？不幸的是，答案是否定的。虽然INDEX、SMALL和FREQUENCY函数可以处理这类数组，但MATCH函数不能，传递给它的lookup_array参数必须是单行或单列。
因此，我们需要采用一种将这里的数组转换成单行或单列数组的技术。
（3）回到前面，现在定义名称Arry3的公式可以转换成：
INDEX({“Amaranth”,”Bronze”,”Silver”,””;”Bronze”,””,””,””;””,””,””,””;”Violet”,”Bronze”,”Amaranth”,””;”Red”,””,””,””;”Puce”,”Bronze”,””,””;”Taupe”,”Ochre”,”Bronze”,”Cerise”;”Silver”,”Red”,”Cerise”,”Orange”;””,””,””,””;”Cerise”,””,””,””},N(IF(1,1+INT((Arry2-1)/MAX(Arry1)))),N(IF(1,1+MOD(Arry2-1,MAX(Arry1)))))

excel公式技巧：从单元格区域的字符串中提取唯一值( 三 )

猜你喜欢