Excel公式技巧46： 按出现的频率依次提取列表中的数据并排序

导语：在《Excel公式技巧44：对文本进行排序》中，我们使用COUNTIF函数并结合SMALL/MATCH/INDEX函数对一系列文本进行排序，无论这些文本中是否存在重复值。在《Excel公式技巧45：按出现的频率依次提取列表中的数据》中，我们使用MATCH/ISNA/IF/MODE/INDEX函数组合提取一系列文本中不重复的数据并按出现的频率且按原数据顺序来放置数据。本文将在此基础上，提取不重复的数据，并按出现的次数和字母顺序排序数据。

如下图1所示，列A中是原来的数据，列B中是从列A中提取后的数据，其规则是：提取不重复的数据，并将出现次数最多的放在前面；按字母顺序排列。示例中，“XXX”和“DDD”出现的次数最多，均为3次，并且按字母顺序“DDD”排在“XXX”之前，因此提取的顺序为“DDD、XXX”；而“QQQ”和“AAA”都只出现了1次，排在“DDD、XXX”之后，且按字母顺序排列为“AAA、QQQ”。

图1

下面先给出公式，然后再详细解释。

在单元格B2中输入数组公式：

=INDEX(Data,MATCH(MIN(MODE.MULT(IF(NOT(COUNTIF(B$1:B1,Data)),(COUNTIF(Data,"<"& Data)+1)*{1,1}))),COUNTIF(Data,"<"& Data)+{1},0))

下拉至单元格B9。

公式中的“Data”为定义的名称：

名称：Data

引用位置：=A2:A9

1. COUNTIF(B$1:B1,Data)

在B1:B1中依次查找名称Data代表的单元格区域中的数据，返回一个由数字组成的数组。显然，Data中的每个数据都在B1:B1中找不到，因此返回{0;0;0;0;0;0;0;0}。我们看看在单元格B4中的公式，公式变为COUNTIF(B

2. NOT(COUNTIF(B$1:B1,Data))

当公式下拉至单元格B4时，该部分变化为：NOT(COUNTIF(B$1:B3,Data))，代入上面的中间数组，得到：NOT({0;0;1;1;1;1;1;1})，结果为：{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}。实际上，结果数组对应于输出中不存在的数据。

3. COUNTIF(Data,"<"& Data)+1

COUNTIF(Data,"<" & Data)返回名称Data代表的单元格区域中依次小于每个单元格中的数据的数值，结果是一个数组：{4;0;5;1;5;1;5;1}，例如第一单元格中的数据为“QQQ”，在Data中小于“QQQ”的有4个，所以返回4。然后，将该数组加1，得到：{5;1;6;2;6;2;6;2}。之所以要加1，是为了处理COUNTIF返回0的情形，即该数据在单元格区域中最小时，将返回0。我们不希望MIN函数可能返回0，因此将返回的值加1，以确保结果为正确顺序。

4. IF(NOT(COUNTIF(B$1:B1,Data)),(COUNTIF(Data,"<"& Data)+1)*{1,1})

将上述中间结果代入，得到：IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{5,5;1,1;6,6;2,2;6,6;2,2;6,6;2,2})，结果是：{5,5;1,1;6,6;2,2;6,6;2,2;6,6;2,2}。

5. MODE.MULT(IF(NOT(COUNTIF(B$1:B1,Data)),(COUNTIF(Data,"<"& Data)+1)*{1,1}))

MODE.MULT()返回传递给其的参数中出现最多的数字，如果有多个数字出现的次数最多且相同，则将其全部返回。例如，对于数组{1,2,2,3,2,3,4}，MODE.MULT返回{2}，因为它是数组中出现最频繁的项；对于数组{1,2,2,3,3,4}，MODE.MULT返回{2,3}，因为它们出现的次数都是最多的；对于数组{1,2,3,4}，MODE.MULT返回错误，通过将{1;2;3;4}与{1,1}相乘，得到{1,1;2,2;3,3;4,4}，从而创建一些重复项，以防止MODE.MULT错误。

因此，上述公式代入中间结果后为：

MODE.MULT({5,5;1,1;6,6;2,2;6,6;2,2;6,6;2,2})

有两个数字出现的次数最多，返回：

{6;2}

6. 将上述结果传递到MIN函数，即：

MIN({6;2})

结果为：

2

按字母顺序返回排在前面的数据所在的位置。

7. COUNTIF(Data,"<" &Data)+{1}

如前所述，返回数组{5;1;6;2;6;2;6;2}。加{1}将结果强制转换为数组，同时将其每个值增加1。这是为了处理Data仅由一项组成的特殊情况。通过加{1}，确保MATCH()函数将其第二个参数作为数组而不是单个值进行处理。

8. MATCH(MIN(MODE.MULT(IF(NOT(COUNTIF(B$1:B1,Data)),(COUNTIF(Data,"<"& Data)+1)*{1,1}))),COUNTIF(Data,"<" & Data)+{1},0)

将上述中间结果代入，得到：MATCH(2, {5;1;6;2;6;2;6;2},0)，结果为：4。

9. 此时公式转换为：

=INDEX(A2:A9,4)

得到：

DDD

如果不想出现#N/A，则使用数组公式：

=IFERROR(INDEX(Data,MATCH(MIN(MODE.MULT(IF(NOT(COUNTIF(B$1:B4,Data)),(COUNTIF(Data,"<"& Data)+1)*{1,1}))),COUNTIF(Data,"<" &Data)+{1},0)),"")

很巧妙的公式！多使用“公式求值”和F9键，仔细领会这个公式的运行原理，从而更加熟悉函数的应用技巧。