excel提取唯一值函数公式(图解excel如何筛选唯一值)-昊阳知识网

excelperfect

本文将综合使用前面系列中学习到的技术，包括布尔逻辑、动态单元格区域、提取满足条件的数据、统计唯一值等，创建出一个解决问题的大型公式。当然，如果你不需要自动动态更新数据，完全可以使用Excel内置的高级筛选或数据透视表功能。

使用辅助列：从单列中提取唯一值列表

借助于辅助列来提取唯一值比使用数组公式更容易。下图1展示用于从单元格区域B2:B9中提取唯一的赛道名的公式。

图1

数组公式：从单列中提取唯一值列表，使用SMALL函数

下图2展示了使用数组公式统计唯一值数量。FREQUENCY函数作为IF函数的参数logical_test值，传递一组数字数组，即：

{=SUM(IF({2;3;1;0;0;0;0;1;0},1))}

其中，非零数值等价于TRUE，零等价于FALSE。而非零数值即为每个唯一值在列表中的数量。IF函数的参数value_if_true的值为1，从而将唯一值转化为1，而1的个数即为唯一值的个数。

图2

下面，需要考虑如何提取唯一值列表。下图3中显示了需要在SMALL函数的参数array中指定的相对位置数组。

图3

可以将上图2公式中IF函数的参数value_if_true的值1替换为ROW($B$2:$B$9)-ROW($B$2) 1，从而得到上图3中的相对位置数组。即，将：

IF(FREQUENCY(IF($B$2:$B$9<>””,MATCH($B$2:$B$9,$B$2:$B$9,0)),ROW($B$2:$B$9)-ROW($B$2) 1),1)

修改为：

IF(FREQUENCY(IF($B$2:$B$9<>””,MATCH($B$2:$B$9,$B$2:$B$9,0)),ROW($B$2:$B$9)-ROW($B$2) 1),ROW($B$2:$B$9)-ROW($B$2) 1)

得到作为SMALL函数的参数array的数组：

{1;2;3;FALSE;FALSE;FALSE;FALSE;8;FALSE}

现在，提取唯一值的数组公式为：

=IF(ROWS(E$5:E5)>$E$2,””,INDEX($B$2:$B$9,SMALL(IF(FREQUENCY(IF($B$2:$B$9<>””,MATCH($B$2:$B$9,$B$2:$B$9,0)),ROW($B$2:$B$9)-ROW($B$2) 1),ROW($B$2:$B$9)-ROW($B$2) 1),ROWS(E$5:E5))))

可以看出，除SMALL函数部分外，公式其余部分与之前讲过的公式相同。结果如下图4所示。

图4

如果将上图4中的空单元格填充值，则结果会自动更新。

数组公式：使用动态单元格区域并从单列中提取唯一值列表

如下图5所示，在公式中使用了定义的名称来统计唯一值的个数。

图5

公式中的名称为：RaceTrack

定义该名称的公式为：

=$B$2:INDEX($B$2:$B$51,MATCH(“Ω”,$B$2:$B$51))

公式假设赛道名称不会超过51个。

注意到，在统计唯一值个数的公式中，使用了ISTEXT函数，这样可以将数字、非文本的单元格排除。

下图6展示了提取唯一值列表的公式，忽略了区域中的数字单元格。

图6

创建唯一值列表作为数据有效性下拉列表项

继续使用前一个示例，将得到的唯一值列表作为数据有效性下拉列表项。从单元格E5:E14中提取文本项，在定义的名称RaceTrackDynamicDataV中，MATCH函数的参数lookup_value值使用了通配符*?，意味着查找的值至少有一个字符，指定参数match_type的值为-1，查找指定区域中长度至少为1的最后一个文本项。然后，将定义的名称运用到数据有效性列表中，如下图7所示。这样，当在列E中有新的项目添加或删除时，列表值会自动更新。

图7

将通配符作为字符

前面我们讲过，如果要将通配符视为字符，需要在前面加上波浪号（~）。如下图8所示，对于MATCH函数来说，在参数lookup_value指定的值前面加上波浪号，在参数lookup_array指定的值后加上空文本字符串。

图8

使用辅助列或数组公式提取满足条件的唯一值

如下图9所示，借助于辅助列提取公司ABC不重复的记录。

图9

也可以使用数组公式，如下图10所示。

图10

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

=SUM(IF(FREQUENCY(IF(C2:C10=F2,MATCH(A2:A10&B2:B10&C2:C10&D2:D10,A2:A10&B2:B10&C2:C10&D2:D10,0)),ROW(A2:A10)-ROW(A2) 1),1))

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

=IF(ROWS(A$15:A15)>$B$12,””,INDEX(A$2:A$10,SMALL(IF(FREQUENCY(IF($C$2:$C$10=$F$2,MATCH($A$2:$A$10&$B$2:$B$10&$C$2:$C$10&$D$2:$D$10,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10&$D$2:$D$10,0)),ROW($A$2:$A$10)-ROW($A$2) 1),ROW($A$2:$A$10)-ROW($A$2) 1),ROWS(A$15:A15))))

复制公式到单元格区域A15:D18。

从交易数据集中提取客户名和小计的动态公式

如下图11所示，根据交易数据集，自动列出所有的客户名和销售额小计。注意，SUMIF函数的参数sum_range指定值为单个单元格，此时该函数基于其用于在range参数中添加的客户区域的高度的单元格数。