精通Excel数组公式022：提取唯一值列表并排序（续）

使用公式对数字进行排序

下图12展示了两个对数字进行排序的公式。使用SMALL/ROWS函数从小到大排序，使用LARGE/ROWS函数从大到小排序。

图12

使用辅助列公式对基于数字列的记录进行排序

如果目的是基于数字排序记录，可以使用辅助列来完成。下图13展示了如何在辅助列中使用RANK和COUNTIF函数。注意，COUNTIF函数用来统计公式所在单元格之前的区域中该单元格值出现的次数，这使得区域中相同的数字根据出现的顺序给出顺序值，而不是RANK函数给出的相同的顺序值。这使得后面使用INDEX/MATCH/ROWS函数提取记录更容易。

图13

使用数组公式对基于数字列的记录进行排序

下面的公式没有借助辅助列，而是使用数组公式对基于数字列的记录排序，如下图14所示。注意，第2个公式中COUNTIF函数解决了重复数字问题。

图14

使用公式提取前3名的成绩以及与这些成绩相关的名字

在商业和运动中经常要提取排在前n位的值及相关的名字。与前面的示例不同，不是排序并显示所有值，而是前几个值。如下图15所示，在单元格A11中的公式确定要显示的记录数。在Excel2010及以上版本中，可使用AGGREGATE函数来提取记录，如果是之前的版本，可以使用SMALL函数。

图15

使用辅助列对基于文本列的记录进行排序

如果想基于文本列排序，可以借助辅助列，如下图16所示。

图16

使用数组公式提取唯一值列表并排序混合数据

下图17展示了从混合数据中提取唯一值并排序的公式。

图17

一个超级长的公式！下面简要讲解该公式。

首先，排序结果基于Excel的排序顺序和ASCII字符，其中对于升序排序来说，Excel排序顺序为数字、文本（包括空文本字符串）、FALSE、TRUE、错误值、空单元格。有255个ASCII字符，相应使用数字1-255表示。

如下图18所示，对于单元格区域A2:A5中的数据，升序排列后的结果显示在单元格区域G2:G5。而单元格区域C2:C5中的数字表示，如果排序该列表，有几个数据在你的前面。例如，对于单元格A2中的数据（54678）来说，处于排序后的列表顶部，没有数据在其前面，因此为0；而数据（SD-987-56）在排序后有3个数据在其前面，因此为3。在公式中需要能够创建出这些数字。

图18

要创建单元格区域C2:C5中的数字，我们先来进行一些运算操作。

选择单元格区域E1:H1，输入数组公式：

=TRANSPOSE(A2:A5)

如下图19所示。

图19

接下来，选择单元格区域E2:H5，输入数组公式：

=A2:A5>E1:H1

如下图20所示。

图20

结果如下图21所示，为由TRUE和FALSE组成的矩形数组，对应着A2:A5中的值与E1:H1中相应的值比较后的结果值。注意到单元格区域E3:H3，有3个TRUE值和1个FALSE值，将3个TRUE值相加，结果为3，与上图18中的数字3相对应，表明该值前面有3个数据。

图21

下面，通过在公式前面加上双减号将TRUE和FALSE值转换为1和0，然后将结果相加，得到想要的数值组成的数组：{0;3;0;2}。如下图22、23、24和25所示。

图22

图23

图24

图25

使用--(A2:A5>TRANSPOSE(A2:A5))代替公式中的E2:H5，得到公式

=MMULT(--(A2:A5>TRANSPOSE(A2:A5)),{1;1;1;1})

使用ROW(A2:A5)^0代替公式中的数组常量：

=MMULT(--(A2:A5>TRANSPOSE(A2:A5)),ROW(A2:A5)^0)

如果不希望空单元格导致公式失败，使用IF(A2:A5<>””,A2:A5)代替公式中的A2:A5：

=MMULT(--(IF(A2:A5<>””,A2:A5)>TRANSPOSE(IF(A2:A5<>””,A2:A5))),ROW(A2:A5)^0)

由于公式要复制到其他单元格，需要将单元格修改为绝对引用：

=MMULT(--(IF($A$2:$A$5<>””,$A$2:$A$5)>TRANSPOSE(IF($A$2:$A$5<>””,$A$2:$A$5))),ROW($A$2:$A$5)^0)

因为上面的公式部分在最终的公式中出现了两次且过长，可以将其定义为名称，然后在公式中使用。如下图26所示，定义名称HMA。

图26

下面，创建公式中的提取数据部分。如下图27所示，在单元格A11中的公式有如下元素：

1.INDEX函数的参数array包含需要查找的数据所在的单元格区域。

2.第一个MATCH函数告诉INDEX要查找的数据项的相对位置。

3.暂时将MATCH函数的参数lookup_value的值留为空。

4.指定MATCH函数的参数lookup_array的值为定义的名称HMA。

5.指定MATCH函数的参数match_type为0，进行精确匹配查找，因为有重复值。

图27

在为MATCH函数指定参数lookup_value之前，必须考虑应该指定什么。有3个唯一值要排序，需要为lookup_value指定3个数字，随着公式向下复制时传递正确的相对位置给INDEX函数：

1.在单元格A11中，MATCH函数需要查找数字0，从定义的名称HMA中报告相对位置1。

2.当公式向下复制到单元格A12中，MATCH函数需要查找数字2，从定义的名称HMA中报告相对位置4.

3.当公式复制到单元格A13中时，MATCH函数需要查找数字3，从定义的名称HMA中报告相对位置2。

如下图28所示，公式中的元素：

MIN(IF(ISNA(MATCH($A$2:$A$5,A$10:A10,0)),HMA))

当公式向下复制时传递合适的最小数值。这是公式中的关键点（排除已经提取的值，取未提取且排名靠前的值），值得细细研究。

图28

添加更多的元素到MIN函数中，避免空单元格影响公式运行，如下图29所示。

图29

下图30展示了最终的公式。

图30

注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。你可以到知识星球App的完美Excel社群下载这本电子书的完整中文版。