excel公式技巧:按公式排序

Excel提供排序功能，可以轻松对选中的列表进行排序。提出了一种基于公式的排序方法，将指定区域的数据按字母顺序进行排序。

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

图1

解决办法

在B2单元格中输入公式：

=LOOKUP(1，0/FREQUENCY(ROWS(1:1美元)，COUNTIF(2:澳元$11澳元)，=" $2:澳元$11澳元))，$ 2:澳元$11澳元)

下拉到单元格B11。

操作原理

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

=LOOKUP(1，0/FREQUENCY(ROWS(1:7美元)，COUNTIF(2:澳元$11澳元)，=" $2:澳元$11澳元))，$ 2:澳元$11澳元)

与单元格B2中的公式相比，唯一的变化是ROWS函数从1更改为7。

在公式中：

COUNTIF(2:澳元11澳元，"=" $ 2:澳元11澳元)

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

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

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

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

现在，将这个数组作为参数bins_array的值传递给FREQUENCY函数，并将公式所在单元格的相应行的相对行号(这里是7，由row($ 1:7)给出)作为参数data_array的值。所以：

FREQUENCY(ROWS(1:7美元)，COUNTIF(2:澳元11美元)，=" $ 2:澳元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(2:澳元$11澳元，"=" $ 2:澳元$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(2:澳元$11澳元)，=" $2:澳元$11澳元))，$ 2:澳元$11澳元)

转换为：

LOOKUP(1，{#DIV/0！#DIV/0！#DIV/0！#DIV/0！0;#DIV/0！#DIV/0！#DIV/0！#DIV/0！#DIV/0！#DIV/0！}，2:澳元11澳元)

数组中唯一的数字在第五位，所以可以得到这样的结果：

雷蒙德

您也可以使用以下公式获得相同的结果：

=INDEX(A $ 2: A $ 11，MATCH(1，FREQUENCY(ROWS(1:7)，COUNTIF(A $ 2: A $ 11)，=" $ A $ 2: $ A $ 11))，0))

总结

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