excel如何使用公式排序

Excel教程 2022-01-12 21:49:53

Excel提供了排序功能，可以方便地对选中的列表进行排序。本文给出一个基于公式的排序解决方案，将指定区域内的数据按字母顺序排序。

如下图1所示，在单元格区域A2:A11中是一组未排序的数据，在单元格区域B2:B11中是已排序的数据。

图1

解决方案

在单元格B2中输入公式：

=LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11)),$A$2:$A$11)

向下拉至单元格B11。

工作原理

让我们以单元格B8中的公式为例来分析：

=LOOKUP(1,0/FREQUENCY(ROWS($1:7),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11)),$A$2:$A$11)

与单元格B2中的公式相比，唯一的变化是ROWS函数内由1改成了7。

公式中：

COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11)

对于该区域内的每个字符串，返回一个值数组，对应该区域内按字母顺序位于该字符串之前或等于该字符串的字符串数。因此，上述公式转换为：

{3;4;9;5;7;2;1;6;10;8}

例如，所得到的数组中的第7个元素是1，是单元格B8中的字符串“Belinda”比较后的结果：按字母顺序，在区域内只有一个字符串在该字符串之前或等于该字符串，因此该字符串就是“Belinda”自身。

同样，在所得到的数组中的第2个元素是4，对应单元格B3中的“Laquita”比较后的结果：按字母顺序，在区域内有四个字符串在该字符串之前或等于该字符串，分别是“Belinda”、“Bula”、“Cathy”和“Laquita”自身。

现在，将这个数组作为参数bins_array的值传递给FREQUENCY函数，将公式所在单元格对应行的相对行号（此处为7，由ROWS（$ 1：7）给出）作为参数data_array的值。这样：

FREQUENCY(ROWS($1:7),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11))

转换为：

FREQUENCY(7,{3;4;9;5;7;2;1;6;10;8})

得到：

{0;0;0;0;1;0;0;0;0;0;0}

然后，选择适当的值来调整该数组（这里选择的是0；也可选择1，这样的话lookup_value的值应为2而不是1更保险），此时：

0/FREQUENCY(ROWS($1:7),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11))

转换为：

{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

将其传递给LOOKUP函数，公式：

=LOOKUP(1,0/FREQUENCY(ROWS($1:7),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11)),$A$2:$A$11)

转换为：

LOOKUP(1,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},$A$2:$A$11)

在数组中唯一的数字在第5位，因此可得到结果：

Raymonde

也可以使用下面的公式获得同样的结果：

=INDEX($A$2:$A$11,MATCH(1,FREQUENCY(ROWS($1:7),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11)),0))

小结

虽然可以使用Excel的排序功能，但使用公式可以实时更新数据。