excel如何使用公式排序

excel如何使用公式排序

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的排序功能，但使用公式可以實時更新數據。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。