Excel公式：从多列中返回唯一且按字母顺序排列的列表 _知识经验

本次的练习是：如下图1所示，单元格区域A2:E5中包含一系列值和空单元格，其中有重复值，要求从该单元格区域中生成按字母顺序排列的不重复值列表，如图1中G列所示。

图1
在单元格G1中编写一个公式，下拉生成所要求的列表。
先不看答案，自已动手试一试。
公式
在单元格G1中的公式为：
=IF(ROWS($1:1)>$H$1,””,INDEX(Arry4,MATCH(SMALL(IF(FREQUENCY(IF(Range1<>””,MATCH(Range1,Arry4,0)),Arry1),COUNTIF(Range1,”<“&Arry4)),ROWS($1:1)),IF(Arry4<>””,COUNTIF(Range1,”<“&Arry4)),0)))
下拉直至出现空单元格为止。
在单元格H1中的公式为：
=SUMPRODUCT((Range1<>””)/COUNTIF(Range1,Range1&””))
公式中使用了5个名称，分别为：
名称：Range1
引用位置：=$A$2:$E$5
名称：Arry1
引用位置：=ROW(INDIRECT(“1:”&COLUMNS(Range1)*ROWS(Range1)))
名称：Arry2
引用位置：=1+INT((Arry1-1)/COLUMNS(Range1))
名称：Arry3
引用位置：=1+MOD(Arry1-1,COLUMNS(Range1))
名称：Arry4
引用位置：=INDEX(Range1,N(IF(1,Arry2)),N(IF(1,Arry3)))
公式解析
1. 在单元格H1中的公式比较直接，是一个获取列表区域唯一值数量的标准公式：
=SUMPRODUCT((Range1<>””)/COUNTIF(Range1,Range1&””))
转换为：
=SUMPRODUCT(({“Due”,””,”Otto”,””,”Otto”;””,””,””,””,”Tre”;”Sei”,”Cinque”,””,”Quattro”,”Otto”;”Due”,””,””,”Quattro”,”Otto”}<>””)/COUNTIF(Range1,Range1&””))
转换为：
=SUMPRODUCT({TRUE,FALSE,TRUE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE;TRUE,TRUE,FALSE,TRUE,TRUE;TRUE,FALSE,FALSE,TRUE,TRUE}/COUNTIF(Range1,Range1&””))
接着解析COUNTIF部分，该部分计算Range1中每个条目在该区域内出现的次数：
=SUMPRODUCT({TRUE,FALSE,TRUE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE;TRUE,TRUE,FALSE,TRUE,TRUE;TRUE,FALSE,FALSE,TRUE,TRUE}/{2,9,4,9,4;9,9,9,9,1;1,1,9,2,4;2,9,9,2,4})
除法运算后：
=SUMPRODUCT({0.5,0,0.25,0,0.25;0,0,0,0,1;1,1,0,0.5,0.25;0.5,0,0,0.5,0.25})
结果为：
6
2. 在单元格G1的主公式中：
=IF(ROWS($1:1)>$H$1,””,
如果公式向下拖拉的行数超过单元格H1中的数值6，则返回空值。
3. 下面重点看看公式中的：
INDEX(Arry4,MATCH(SMALL(IF(FREQUENCY(IF(Range1<>””,MATCH(Range1,Arry4,0)),Arry1),COUNTIF(Range1,”<“&Arry4)),ROWS($1:1)),IF(Arry4<>””,COUNTIF(Range1,”<“&Arry4)),0))
实际上，这是提取唯一且按字母顺序排列的值的标准公式构造，唯一区别是提取值的区域不是单列、一维区域，而是二维区域。然而，在原理上该技术是相同的：首先将二维区域转换成一维区域，然后应用通用的结构来获取我们想要的结果。
上述公式构造中的Arry4为：
INDEX(Range1,N(IF(1,Arry2)),N(IF(1,Arry3)))
这里，只是简单地索引二维区域中的每个元素。然而，我们得到的结果数组将是一维数组且包含的元素与二维区域中的元素完全相同。
为了解构Arry4，我们需要首先查看Arry2和Arry3，它们分别对应着INDEX函数的参数row_num和参数column_num 。而它们都引用了Arry1：
=ROW(INDIRECT(“1:”&COLUMNS(Range1)*ROWS(Range1)))
名称Range1代表的区域有4行5列，因此转换为：

Excel公式：从多列中返回唯一且按字母顺序排列的列表

猜你喜欢