excel公式技巧:根据频率和大小返回唯一的值列表

编辑：宝哥软件园来源：互联网时间：2021-09-27

新天龙八部

角色扮演 | 1.85G

评分

立即下载

有一个包含数字和空格的单元格区域，例如下面图1所示示例中的单元格区域A1:F6。需要生成这些数字的唯一值，并按照数字的频率顺序进行排列，频率高的排名第一，如果几个数字的频率相同，则小的数字排名第一，如图1第一列所示。

图1

先不看答案，自己试试。

公式

单元I1中的数组公式为：

=if(row($ 1:1)$ h $ 1，""，min(IF(Range1 " ")，COUNTIF(Range1，range 1)1/(range1*10^6))=large(if(index(frequency(0 (Range10)，0(range 10))，N(IF(1，columns(range 1)* arr 2-转置(COLUMNS(Range1)-Arry1)))，if(range 1 " "，COUNTIF(Range1，range 1)1/(range1*10^6))),rows($1:1)),range1)))

向下拖动，直到出现一个空单元格。

在单元格H1中，返回的数字由公式给出：

=SUMPRODUCT((Range1 " ")/COUNTIF(range 1，range 1 " "))

公式分析

在公式中，使用了三个名称，它们是：

名称：范围1。

参考位置：=$A$1:$F$6。

名称：Arry1。

参考位置：=行(间接(“1:”列(范围1))

名称：Arry2。

参考位置：=行(间接(“1:”行(范围1))

单元H1中的公式是用于确定单元区域中不同元素数量的标准公式结构。公式：

=SUMPRODUCT((Range1 " ")/COUNTIF(range 1，range 1 " "))

转换为：

=SUMPRODUCT(({1，""，1，""，6，6；1,5,"","","",6;"","",2,2,2,"";4,4,"","","",2;"",3,"",4,"","";5，5，5，5，5，2}"")/COUNTIF(Range1，Range1 " "))

转换为：

=SUMPRODUCT(({TRUE，FALSE，TRUE，FALSE，TRUE，TRUE；真、真、假、假、假、真；假，假，真，真，真，假；真、真、假、假、假、真；假，真，假，真，假，假；真，真，真，真，真，真})/计数(范围1，范围1 " "))

公式中的COUNTIF(Range1，Range1 " ")用于计算Range1中每个元素的出现次数。请注意，countif函数的第二个参数中添加了一个空字符串，主要原因如下：

如果没有添加空字符串；否则：

COUNTIF(范围1，范围1)

Excel将首先解析其第二个参数标准：

COUNTIF(范围1，{1，0，1，0，6，6；1,5,0,0,0,6;0,0,2,2,2,0;4,4,0,0,0,2;0,3,0,4,0,0;5,5,5,5,5,2}))

然后解析它的第一个参数范围：

COUNTIF({1，""，1，"，6，6；1,5,"","","",6;"","",2,2,2,"";4,4,"","","",2;"",3,"",4,"","";5,5,5,5,5,2},{1,0,1,0,6,6;1,5,0,0,0,6;0,0,2,2,2,0;4,4,0,0,0,2;0,3,0,4,0,0;5,5,5,5,5,2}))

由于第一个数组中没有0，结果是：

{3,0,3,0,3,3;3,6,0,0,0,3;0,0,5,5,5,0;3,3,0,0,0,5;0,1,0,3,0,0;6,6,6,6,6,5}

这意味着当它被用作除法的分母时，结果数组将包含#DIV/0！这将导致SUMPRODUCT函数出错。

通过在第二个参数指定的值后添加一个空字符串，Excel将空单元格解析为空字符串而不是0，因此公式为：

COUNTIF(范围1，范围1 " ")

决心：

COUNTIF(范围1，{“1”、“”、“1”、“”、“6”、“6”；"1,"5,"","","","6; "","","2,"2,"2,"";"4,"4,"","","","2; "","3,"","4,"",""; "5,"5,"5,"5,"5,"2})

这样，转换后：

转换为：

=SUMPRODUCT({真、假、真、假、真、真；真、真、假、假、假、真；假，假，真，真，真，假；真、真、假、假、假、真；假，真，假，真，假，假；真，真，真，真，真，真}/{3，15，3，15，3，3；3,6,15,15,15,3;15,15,5,5,5,15;3,3,15,15,15,5;15,1,15,3,15,15;6,6,6,6,6,5})

转换为：

=SUMPRODUCT({0.33333333333333，0，0.3333333333333333333，0，0.33333333333333333，0.3333333333333，0.3333333333333；0.333333333333333,0.166666666666667,0,0,0,0.333333333333333;0,0,0.2,0.2,0.2,0;0.333333333333333,0.333333333333333,0,0,0,0.2;0,1,0,0.333333333333333,0,0;0.166666666666667,0.166666666666667,0.166666666666667,0.166666666666667,0.166666666666667,0.2})

获取结果：

六

因此，当单元格I1中的公式被向下拖动时，超过6个单元格将返回空，这就是公式的开始：

=IF(ROWS($1:1)$H$1，""，

让我们看看公式中的主要结构：

MIN(IF(IF(Range1 " "、COUNTIF(Range1，range 1)1/(range1*10^6))=large(if(index(frequency(0 (Range10)、0(range 10))、N(IF(1，columns(range 1)* arr 2-转置(COLUMNS(Range1)-Arry1))))、IF(Range1 " "、COUNTIF(Range1，range 1)1/(range1*10^6))),rows($1:1)),range1))

其中包括：

县(范围1，范围1) 1/(Range1*10^6)

将为单元格区域中的每个值生成计数数组，这非常重要，因为问题的关键在于根据该区域中值的频率返回该值。使用额外子句的原因是为了给我们提供一种方法来区分两个或多个值在一个区域中以相同频率出现的情况。更重要的是，在这种情况下，这个子句的目的是首先返回一个较小的值。

上面的一些公式被转换成：

{3,0,3,0,3,3;3,6,0,0,0,3;0,0,5,5,5,0;3,3,0,0,0,5;0,1,0,3,0,0;6,6,6,6,6,5} 1/({1000000,0,1000000,0,6000000,6000000;1000000,5000000,0,0,0,6000000;0,0,2000000,2000000,2000000,0;4000000,4000000,0,0,0,2000000;0,3000000,0,4000000,0,0;5000000,5000000,5000000,5000000,5000000,2000000})

请注意，如果区域中有任何空字符串，它将被解析为#VALUE！错误，但是，本节之前的IF条款——IF(范围1 " ")将意味着这些错误值将不被考虑。以上结果转换为：

{3,0,3,0,3,3;3,6,0,0,0,3;0,0,5,5,5,0;3,3,0,0,0,5;0,1,0,3,0,0;6，6，6，6，6，5} {0.000001，#DIV/0！0.000001，#DIV/0！1.66666666666667E-07，1.66666666666666 7e-07；0.000001，0.0000002，#DIV/0！#DIV/0！#DIV/0！1.666666666666667E-07；#DIV/0！#DIV/0！0.0000005，0.0000005，0.0000005，#DIV/0！0.00000025，0.00000025，#DIV/0！#DIV/0！#DIV/0！0.0000005;#DIV/0！3.3333333333333 e-07，#DIV/0！0.00000025，#DIV/0！#DIV/0！0.0000002,0.0000002,0.0000002,0.0000002,0.0000002,0.0000005}

获取：

{3.000001，#DIV/0！3.000001，#DIV/0！3.00000016666667,3.00000016666667;3.000001，6.0000002，#DIV/0！#DIV/0！#DIV/0！3.00000016666667;#DIV/0！#DIV/0！5.0000005，5.0000005，5.0000005，#DIV/0！3.00000025，3.00000025，#DIV/0！#DIV/0！#DIV/0！5.0000005;#DIV/0！1.0000003333333，#DIV/0！3.00000025，#DIV/0！#DIV/0！6.0000002,6.0000002,6.0000002,6.0000002,6.0000002,5.0000005}

同样，任何错误值都将在下面解决：

IF(范围1 " "，COUNTIF(范围1，范围1) 1/(Range1*10^6))

转换为：

{3.000001，FALSE，3.000001，FALSE，3.000000166667，3.00000016666667；3.000001，6.0000002，FALSE，FALSE，FALSE，3.0000001666667；FALSE，FALSE，5.00000005，5.0000005，5.0000005，FALSE；3.00000025，3.00000025，FALSE，FALSE，FALSE，5.0000005；假，1.0000003333333，假，3.0000025，假，假；6.0000002,6.0000002,6.0000002,6.0000002,6.0000002,5.0000005}

例如，在这个数组中，值3.000001、3.000001666667和3.0000025分别代表出现在范围1中的三个值1、6和4，并且可以区分小数部分。

现在，我们需要一种方法来识别数组中的唯一值，并按降序排列它们，即：

6.0000002

5.0000005

3.000001

3.00000025

3.00000016666667

1.00000033333333

然后我们将它们与原始值进行匹配。我们知道上面的值代表5出现6次，2出现5次，1出现3次，4出现3次，6出现3次，3出现1次。

为了将我们的数组限制为只考虑唯一值的数组，公式中使用了以下部分：

频率(0(范围10)，0(范围10))

转换为：

{3;15;0;0;3;0;0;6;0;0;0;0;0;0;5;0;0;0;3;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0}

在上面的数组中，我们突出显示非零值，该值对应于以下数组中突出显示的值(忽略数组维度):

{1,"",1,"",6,6;1,5,"","","",6;"","",2,2,2,"";4,4,"","","",2;"",3,"",4,"","";5,5,5,5,5,2}

也就是说，第一个数组中的非零值与每个不同的值在第二个数组中第一次出现相对应，对于空字符串也是如此。

可以看到，这种情形下使用频率函数，从而将数组简化为每个值在该数组中出现次数的数组。公式中之所以在区域后添加0,是为了将空单元格转换为0。

现在，将频率函数生成的数组传递给如果函数，以使结果数组仅包含不同的数值：

中频(频率(0(范围10)、0(范围10))、中频(范围1 " "、COUNTIF(范围1、范围1) 1/(Range1*10^6)

转换为：

中频({ 3;15;0;0;3;0;0;6;0;0;0;0;0;0;5;0;0;0;3;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0}，{ 3.00000001，FALSE，3.000001，FALSE，3.0000001666667，3.0000166666667；3.000001，6.0000002，FALSE，FALSE，FALSE，3.000000166667；FALSE，FALSE，5.000000005，5.0000005，5.0000005，FALSE；3.00000025，3.00000025，FALSE，FALSE，FALSE，5.0000005；假，1.0000003333333,假，3.0000025,假，假；6.0000002,6.0000002,6.0000002,6.0000002,6.0000002,5.0000005})

结果是：

{3.000001，FALSE，3.000001，FALSE，3.00000016667，3.0000001666667；3.000001，6.0000002，FALSE，FALSE，FALSE，3.000000166667；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，1.0000003333333,假，3.0000025,假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；#不适用，#不适用，#不适用，#不适用，#不适用，#不适用，#不适用；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；#不适用，#不适用，#不适用，#不适用，#不适用，#不适用，#不适用；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；#不适用，#不适用，#不适用，#不适用，#不适用，#不适用，#不适用；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；#不适用，#不适用，#不适用，#不适用，#不适用，#不适用，#不适用；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假}

这并不是我们想要的含有36个元素的数组。其原因是，传递给如果函数的两个数组维度不同，一个是37行一列数组，一个是6行6列的数组

因此，要执行我们想要的比较，必须首先重新将其维度调整为与另一个区域的维度相同。也就是说，这里要将37行一列数组调整为6行6列的数组。

简单地使用指数函数处理由频率函数生成的数组，使用合适大小和值的数组传递给其行数参数，结果数组将是一个由6行6列组成的数组。

这里由频率函数生成的37行一列数组：

{3;15;0;0;3;0;0;6;0;0;0;0;0;0;5;0;0;0;3;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0}

要转换成下面的6行6列数组：

{3,15,0,0,3,0;0,6,0,0,0,0;0,0,5,0,0,0;3,0,0,0,0,0;0,1,0,0,0,0;0,0,0,0,0,0}

这将通过将一个数组传递给指数函数的参数行数来实现，这个作为参数值的数组为：

{1,2,3,4,5,6;7,8,9,10,11,12;13,14,15,16,17,18;19,20,21,22,23,24;25,26,27,28,29,30;31,32,33,34,35,36}

那么，如何生成这个数组呢？

有许多方法，下面是其中的一种：

COLUMNS(范围1)* arr 2-转置(COLUMNS(范围1)-arr 1)

其中，名称Arry1:

=ROW(间接(“1:”COLUMNS(Range 1)))

转换为：

{1;2;3;4;5;6}

名称：Arry2:

=ROW(间接(“1:”ROWS(范围1)))

转换为：

{1;2;3;4;5;6}

将其代入上面的公式中：

COLUMNS(范围1)* { 1;2;3;4;5;6 }-转置(列(范围1)-{ 1；2;3;4;5;6})

由于示例中范围一的列数为6,故公式转换为：

6*{1;2;3;4;5;6 }-转置(6-{ 1;2;3;4;5;6})

转换为：

6*{1;2;3;4;5;6 }-转置({ 5;4;3;2;1;0})

转换为：

6*{1;2;3;4;5;6}-{5,4,3,2,1,0}

转换为：

{6;12;18;24;30;36}-{5,4,3,2,1,0}

正交的两个数组相减，得到：

{1,2,3,4,5,6;7,8,9,10,11,12;13,14,15,16,17,18;19,20,21,22,23,24;25,26,27,28,29,30;31,32,33,34,35,36}

这正是我们需要的。

现在，如上所述，我们将此数组作为参数行数的值传递给指数函数。这里，确保我采用了必要的技术来强制指数对一组值进行操作（更多信息，请参见《Excel公式技巧03：INDEX函数，给公式提供数组》 ),因此：

索引（频率(0(范围10),0(范围10))，N(IF(1，COLUMNS(范围1)* arr 2-转置(COLUMNS(范围1)-Arry1)))

转换为：

INDEX({ 3；15;0;0;3;0;0;6;0;0;0;0;0;0;5;0;0;0;3;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0},{1,2,3,4,5,6;7,8,9,10,11,12;13,14,15,16,17,18;19,20,21,22,23,24;25,26,27,28,29,30;31,32,33,34,35,36})

得到：

{3,15,0,0,3,0;0,6,0,0,0,0;0,0,5,0,0,0;3,0,0,0,0,0;0,1,0,0,0,0;0,0,0,0,0,0}

再回到公式的主要构造：

MIN(IF(范围1)、COUNTIF(范围1、范围1)1/(range1*10^6))=large(if(index(frequency(0(范围10))、0(范围10))、N(IF(1，列(范围1)* arr 2-转置(列(范围1-arr y1))))、IF(范围1、COUNTIF(范围1、范围1)1/(range1*10^6))),rows($1:1)),range1))

将上面生成的中间结果代入：

MIN(IF({ 3.00000001，FALSE，3.000001，FALSE，3.0000001666667，3.0000166666667；3.000001，6.0000002，FALSE，FALSE，FALSE，3.000000166667；FALSE，FALSE，5.000000005，5.0000005，5.0000005，FALSE；3.00000025，3.00000025，FALSE，FALSE，FALSE，5.0000005；假，1.0000003333333,假，3.0000025,假，假；6.0000002，6.0000002，6.0000002，6.0000002，6.0000002，5.0000005}=LARGE(IF({3，15，0，0，3，0；0,6,0,0,0,0;0,0,5,0,0,0;3,0,0,0,0,0;0,1,0,0,0,0;0，0，0，0，0}，{ 3.00000001，FALSE，3.000001，FALSE，3.0000000166667，3.000001666666667；3.000001，6.0000002，FALSE，FALSE，FALSE，3.000000166667；FALSE，FALSE，5.000000005，5.0000005，5.0000005，FALSE；3.00000025，3.00000025，FALSE，FALSE，FALSE，5.0000005；假，1.0000003333333,假，3.0000025,假，假；6.0000002，6.0000002，6.0000002，6.0000002，6.0000002，5.0000005})，ROWS(1:1))，Range1))

转换为：

MIN(IF({ 3.00000001，FALSE，3.000001，FALSE，3.0000001666667，3.0000166666667；3.000001，6.0000002，FALSE，FALSE，FALSE，3.000000166667；FALSE，FALSE，5.000000005，5.0000005，5.0000005，FALSE；3.00000025，3.00000025，FALSE，FALSE，FALSE，5.0000005；假，1.0000003333333,假，3.0000025,假，假；6.0000002，6.0000002，6.0000002，6.0000002，6.0000002，5.0000005 }=LARGE({ 3.00000001，FALSE，FALSE，FALSE，3.000000166667，FALSE；假，6.0000002,假，假，假，假；假，假，5.00000005,假，假，假；3.00000025,假，假，假，假，假；假，1.00000003333333,假，假，假，假；假、假、假、假、假、假}、行($1:1))、范围1))

这里ROWS($1:1)=1，转换为：

转换为：

最小值（如果({假，假，假，假，假，假，假；假，真，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；真、真、真、真、真、假}、范围1))

代入范围1:

最小值（如果({假，假，假，假，假，假，假；假，真，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；真，真，真，真，真，假},{1,0,1,0,6,6;1,5,0,0,0,6;0,0,2,2,2,0;4,4,0,0,0,2;0,3,0,4,0,0;5,5,5,5,5,2}))

转换为：

最小({假，假，假，假，假，假，假；假，5,假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；假，假，假，假，假，假，假；5，5，5，5，5，FALSE})

得到：

小结

把单列数组转换成二维数组的技巧给我留下了深刻的印象，我很好地使用了FREQUENCY函数。