Excel公式:从多列中返回唯一的字母列表

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

图1

在单元格G1中写一个公式，然后下拉以生成所需的列表。

先不看答案，自己试试。

公式

单元格G1中的公式为：

=IF(ROWS($1:1)$H$1，""，INDEX(Arry4，MATCH(SMALL)(IF(FREQUENCY(IF(range 1 " ")、MATCH(Range1，Arry4，0))、Arry1)、COUNTIF(range 1 " " arry 4))、ROWS($1:1))、IF(Arry4 " "、COUNTIF(range 1 " " arry 4))、0))

向下拉，直到出现一个空单元格。

单元格H1中的公式为：

=SUMPRODUCT((Range1 " ")/COUNTIF(range 1，range 1 " "))

公式中使用了五个名称，它们是：

名称：范围1。

参考位置：=$ 2:澳元$5澳元。

名称：Arry1。

参考位置：=行(间接(“1:”列(范围1) *行(范围1))。

名称：Arry2。

参考位置：=1 INT((Arry1-1)/COLUMNS(Range1))

名称：Arry3。

参考位置：=1 MOD(Arry1-1，COLUMNS(Range1))

名称：Arry4。

参考位置：=索引(范围1，n (if (1，arry2))，n (if (1，arry3))

公式分析

1.单元格H1中的公式很简单，它是获取列表区域中唯一值数量的标准公式：

=SUMPRODUCT((Range1 " ")/COUNTIF(range 1，range 1 " "))

转换为：

=SUMPRODUCT(({“Due”、“”、“Otto”、“”、“Otto”；“”、“”、“”、“”、“”、“Tre”；“Sei”、“Cinque”、“Quattro”、“Otto”；“到期”、“”、“”、“Quattro”、“Otto”}”)/COUNTIF(Range1，range 1“))

转换为：

=SUMPRODUCT({真、假、真、假、真；假，假，假，假，真；真，真，假，真，真；真、假、假、真、真}/计数(范围1，范围1 " "))

然后分析COUNTIF部分，该部分计算该区域范围1中每个条目的出现次数：

=SUMPRODUCT({真、假、真、假、真；假，假，假，假，真；真，真，假，真，真；真、假、假、真、真}/{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})

结果是：

六

2.在细胞G1的主要公式中：

=IF(ROWS($1:1)$H$1，""，

如果公式向下拖动的行数超过单元格H1中的数字6，则返回空值。

3.关注下面的公式：

INDEX(arr 4，MATCH(SMALL(IF(FREQUENCY(IF(range 1 " ")，MATCH(Range1，arr 4，0)))，Arry1)，COUNTIF(Range1，" " arr 4 "))，ROWS(1:1))，IF(arr 4 " "，COUNTIF(Range1，" " arr 4))，0))

事实上，这是一个标准的公式构造，用于提取唯一的和按字母顺序排列的值。唯一不同的是，从中提取值的区域不是单个列，一维区域，而是二维区域。但是，原则上技术是一样的：首先将二维区域转化为一维区域，然后应用一般的结构来获得期望的结果。

上式结构中的Arry4为：

索引(范围1，N(IF(1，Arry2))，N(IF(1，Arry3))

这里，简单地索引二维区域中的每个元素。然而，我们得到的结果数组将是一维数组，包含与二维区域完全相同的元素。

要解构Arry4，我们需要看Arry2和Arry3，它们分别对应于INDEX函数的参数row_num和参数column_num。它们都指Arry1:

=ROW(间接(" 1: " COLUMNS(range 1)* ROWS(range 1)))

名称范围1代表一个有4行5列的区域，因此它被转换为：

行(间接(“1:”5 * 4))

获取：

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}

再看看Arry2:

=1 INT((Arry1-1)/COLUMNS(Range1))

转换为：

1 INT(({ 1；2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}-1)/5)

转换为：

1 INT({ 0；1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}/5)

转换为：

1 INT({ 0；0.2;0.4;0.6;0.8;1;1.2;1.4;1.6;1.8;2;2.2;2.4;2.6;2.8;3;3.2;3.4;3.6;3.8})

转换为：

1 {0;0;0;0;0;1;1;1;1;1;2;2;2;2;2;3;3;3;3;3}

获取：

{1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4}

然后看看Arry3:

=1 MOD(Arry1-1，COLUMNS(Range1))

转换为：

1 MOD({ 0；1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19},5)

转换为：

1 {0;1;2;3;4;0;1;2;3;4;0;1;2;3;4;0;1;2;3;4}

得到：

{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5}

再回到Arry4。可以转换为：

索引（范围1，N(如果(1,{ 1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4}))，N(IF(1，{ 1；2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5})))

这里使用了强制指数返回数组的技术，详情可参阅《Excel公式技巧03：INDEX函数，给公式提供数组》 。上述公式可转换为：

INDEX(范围1,{ 1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4},{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5})

现在应该可以看清楚为指数函数的每个参数传递数组的原因了，因为上述公式等价于执行下列每个公式：

索引（范围1,1,1)

索引（范围1,1,2)

索引（范围1,1,3)

索引（范围1,1,4)

索引（范围1,1,5)

索引（范围1,2,1)

索引（范围1,2,2)

…

索引（范围1,4,5)

因此，Arry4的结果为：

{"到期";"";"奥托";"";"奥托";"";"";"";"";“Tre”；“Sei”；《五重奏》;"";“Quattro”；"奥托";"到期";"";"";“Quattro”；"奥托"

而超过将范围一解析为：

{"到期"、"到期"、"奥托"、"奥托";“”、“”、“”、“”、“Tre”；“Sei”、“Cinque”、“Quattro”、“Otto”；"到期日"、"到期日"、"到期日"、"到期日"、"到期日"、"到期日"

我们可以看到这两个数组中的值没有任何区别。唯一不同的是，范围一包含一个四行5列的二维数组，而Arry4是通过简单地将范围一中的每个元素进行索引而得出的，实际上是20行一列的一维区域。

好了，现在就可以使用我们掌握的常用的适用于一维区域的技术来操作该数组了！

4.再看看主公式中的：

INDEX(arr 4，MATCH(SMALL(IF(FREQUENCY，IF(range 1 " ")，MATCH(Range1，arr 4，0)))，arr 1)，COUNTIF(Range1，" " arr 4 "))，ROWS(1:1))，IF(arr 4 " "，COUNTIF(Range1，" " arr 4))，0))

先看看这部分：

中频（范围1 " ",匹配（范围1，Arry4，0))

转换为：

如果({真，假，真，假，真；假，假，假，假，真；真，真，假，真，真；真、假、假、真、真}、匹配（范围1，Arry4，0))

使用范围一和Arry4替换，得到：

如果({真，假，真，假，真；假，假，假，假，真；真，真，假，真，真；真、假、假、真、真}、匹配({"到期"、"奥托"、"奥托";“”、“”、“”、“”、“Tre”；“Sei”、“Cinque”、“Quattro”、“Otto”；"到期"、"到期"、"到期"、" Quattro "、" Otto"}、{ "到期";"";"奥托";"";"奥托";"";"";"";"";“Tre”；“Sei”；《五重奏》;"";“Quattro”；"奥托";"到期";"";"";“Quattro”；“Otto”}，0))

可转换为：

如果({真，假，真，假，真；假，假，假，假，真；真，真，假，真，真；真，假，假，真，真},{1,#不适用，3,#不适用，3;#不适用，#不适用，#不适用，#不适用，#不适用，10;11,12,#不适用，14,3;1,#不适用，#不适用，14,3})

得到：

{1，FALSE，3，FALSE，3；假，假，假，假，10;11，12，FALSE，14，3；1,假，假，14,3}

这个数组是频率函数的第一个参数，而Arry1是其第二个参数：

频率（中频（范围1 " ",匹配（范围1,数组4,0)),数组1)

可转换为：

频率({1,假，3,假，3;假，假，假，假，10;11，12，FALSE，14，3；1,假，假，14，3}，Arry1)

将Arry1代入：

频率({1,假，3,假，3;假，假，假，假，10;11，12，FALSE，14，3；1，FALSE，FALSE，14，3}，{ 1；2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})

生成数组：

{2;0;4;0;0;0;0;0;0;1;1;1;0;2;0;0;0;0;0;0;0}

这是我们使用的相当标准的技术：上述数组中非零值的位置表示在该区域内每个不同值在该数组中的首次出现，因此提供了一种仅返回唯一值的方法。将该数组作为如果函数的条件：

中频（频率（中频（范围1 " ",匹配（范围1,数组4,0)),数组1),计数中频（范围1,",数组4))

转换为：

IF({ 2；0;4;0;0;0;0;0;0;1;1;1;0;2;0;0;0;0;0;0;0}，COUNTIF(范围1，" " Arry4))

条件统计函数用于确定字母排序：

IF({ 2；0;4;0;0;0;0;0;0;1;1;1;0;2;0;0;0;0;0;0;0},{1;0;3;0;3;0;0;0;0;10;9;0;0;7;3;1;0;0;7;3})

结果为：

{1;假；3;假；假；假；假；假；假；10;9;0;假；7;假；假；假；假；假；假；FALSE}

这样，INDEX函数部分现在变成：

INDEX(Arry4，MATCH(SMALL({ 1；假；3;假；假；假；假；假；假；10;9;0;假；7;假；假；假；假；假；假；FALSE}、row($ 1:1))、IF(arr 4 " "、COUNTIF(范围1 " " arr 4))、0))

对于小的函数，其参数k的值由行(1:1美元)指定，在单元格G1中为1,因此上述公式转换为：

索引(arr 4，MATCH(0，IF(arr 4 " "，COUNTIF(Range1 " "，arr 4))，0))

转换为：

索引(Arry4，MATCH(0)，IF(Arry4 " "，1；0;3;0;3;0;0;0;0;10;9;0;0;7;3;1;0;0;7;3},0))

转换为：

INDEX(Arry4，MATCH(0，{ 1；假；3;假；3;假；假；假；假；10;9;0;假；7;3;1;假；假；7;3},0))

转换为：

索引(Arry4，12)

将Arry4代入：

索引({"到期";"";"奥托";"";"奥托";"";"";"";"";“Tre”；“Sei”；《五重奏》;"";“Quattro”；"奥托";"到期";"";"";“Quattro”；《奥托》},12)

得到结果：

骰子上的五点

小结：

本文至少复习/使用了以下公式技术：

1.统计列表区域中唯一值数量。

2.将二维区域转换成一维区域。

3.强制指数返回数组。

4.确定字母排序。

5.提取唯一值并按字母排序。