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

如下图1所示，列A中是原来的数据，列B中是从列A中提取后的数据，其规则是：提取不重复的数据，并将出现次数最多的放在前面；如果出现的次数相同，则保留原顺序。示例中，“XXX”和“DDD”出现的次数最多，均为3次，但“XXX”在原数据中排在“DDD”之前，因此提取的顺序为“XXX、DDD”。

图1

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

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

=INDEX(Data,MODE(IF(ISNA(MATCH(Data,B$1:B1,0)),MATCH(Data,Data,0)*{1,1})))

下拉至单元格B9。

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

名称：Data

引用位置：=A2:A9

1. MATCH(Data,B$1:B1,0)

当公式下拉至单元格B5时，该部分变化为：MATCH(Data,B$1:B4,0)，即在单元格区域B1:B4中依次查找单元格区域A2:A9中的数据，例如单元格A2中的“QQQ”在B1:B4中的第4行，返回数值4，“AAA”不在B1:B4中，返回错误值#N/A，等等，结果为数组{4;#N/A;2;3;2;3;2;3}。

2. ISNA(MATCH(Data,B$1:B1,0))

当公式下拉至单元格B5时，该部分变化为：ISNA(MATCH(Data,B$1:B4,0))，代入上面的中间数组，得到：ISNA({4;#N/A;2;3;2;3;2;3})，结果为：{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}。可以知道，其作用是跳过已经提取的数据。

注意，公式开始于第2行的单元格B2，设置了对其上方单元格区域的引用。

3. MATCH(Data,Data,0)

返回名称Data代表的单元格区域中每个单元格中的数据在整个区域中最先出现的位置数，例如“XXX”最先出现在第3位，则返回3。得到的结果数组为：{1;2;3;4;3;4;3;4}。这样，就将数据字符串转换成了数字，便于Excel进行处理。

4. MATCH(Data,Data,0)*{1,1}

利用矩阵乘法，将得到的单列数组变成双列数组，即：{1,1;2,2;3,3;4,4;3,3;4,4;3,3;4,4}。这是为了满足MODE函数的要求，该函数需要重复的数值。（如果Data中没有重复项，则MATCH函数会返回一个由顺序号组成的数组，没有重复数，传递给MODE函数会出错）

5. MODE(IF(ISNA(MATCH(Data,B$1:B1,0)),MATCH(Data,Data,0)*{1,1}))

MODE函数返回传递给它的列表中出现次数最多的数字。仍以单元格B5中的公式为例，将上述中间结果代入公式，得到：

MODE(IF({FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{1,1;2,2;3,3;4,4;3,3;4,4;3,3;4,4}))

转换为：

MODE({FALSE,FALSE;2,2;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE})

忽略布尔值，得到结果：

2

6. 传递到INDEX函数，即：

=INDEX(Data,2)

转换为：

=INDEX(A2:A9,2)

结果为：

AAA

如果在找不到值时不显示错误值#N/A，可以使用下面的数组公式：

=IFERROR(INDEX(Data,MODE(IF(ISNA(MATCH(Data,D$1:D1,0)),MATCH(Data,Data,0)*{1,1}))),"")

很巧妙的公式！多使用“公式求值”和F9键，仔细领会这个公式的运行原理。