excel公式技巧：根据频率和大小返回唯一值列表 _知识经验

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

文章插图
图1
先不看答案，自已动手试一试。
公式
在单元格I1中的数组公式为：
=IF(ROWS($1:1)>$H$1,””,MIN(IF(IF(Range1<>””,COUNTIF(Range1,Range1)+1/(Range1*10^6))=LARGE(IF(INDEX(FREQUENCY(0+(Range1&0),0+(Range1&0)),N(IF(1,COLUMNS(Range1)*Arry2-TRANSPOSE(COLUMNS(Range1)-Arry1)))),IF(Range1<>””,COUNTIF(Range1,Range1)+1/(Range1*10^6))),ROWS($1:1)),Range1)))
向下拖拉至出现空单元格为止。
单元格H1中为返回的数字数量，公式为：
=SUMPRODUCT((Range1<>””)/COUNTIF(Range1,Range1&””))
公式解析
在公式中，使用了3个名称，分别为：
名称：Range1
引用位置：=$A$1:$F$6
名称：Arry1
引用位置：=ROW(INDIRECT(“1:”&COLUMNS(Range1)))
名称：Arry2
引用位置：=ROW(INDIRECT(“1:”&ROWS(Range1)))
单元格H1中的公式是一种用于确定单元格区域内不同元素数量的标准公式结构。公式：
=SUMPRODUCT((Range1<>””)/COUNTIF(Range1,Range1&””))
转换为：
=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;TRUE,TRUE,FALSE,FALSE,FALSE,TRUE;FALSE,FALSE,TRUE,TRUE,TRUE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE,TRUE;FALSE,TRUE,FALSE,TRUE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE})/COUNTIF(Range1,Range1&””))
公式中的COUNTIF(Range1,Range1&””)用来计算Range1区域中每个元素出现的次数，注意到在COUNTIF函数的第2个参数中添加了空字符串，其主要原因详解如下：
假设不添加空字符串，则为：
COUNTIF(Range1,Range1)
Excel首先会解析其第二个参数criteria：
COUNTIF(Range1,{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}))
然后解析其第一个参数range：
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(Range1,Range1&””)
解析为：
COUNTIF(Range1,{“1″,””,”1″,””,”6″,”6″;”1″,”5″,””,””,””,”6″;””,””,”2″,”2″,”2″,””;”4″,”4″,””,””,””,”2″;””,”3″,””,”4″,””,””;”5″,”5″,”5″,”5″,”5″,”2″})

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

推荐阅读

绝地求生usb芯片压枪封号吗？

前后轮胎不同品牌有影响吗？

细小吃什么药最有用

为什么水中加入乙二醇可以防冻

花可以浇可乐吗可乐浇花好不好

在word中如何画图你应该这样做

怎样快速泡腐竹的方法快速泡腐竹的方法

evelom卸妆膏之后需要洗面奶吗

起诉都需要什么流程

汽车上必备物品有哪些？

感怀二十首其六原文、作者

盆栽辣椒蔫了怎么办

宝马是哪个国家的

伤感网名女生简单干净清新伤感网名有哪些

海蟹冷冻保存方法怎么存放海蟹

vlookup两列模糊匹配

乔丹退役了吗？

圣诞节立体贺卡的做法圣诞节立体卡片做法

厦门|怀念湖北菜，可惜厦门难得找到满意的

国内期货是什么意思国内期货的解释