excel根据单元格区域中出现的频率和大小返回唯一值列表 _知识经验

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

文章插图
图1
先不看答案，自已动手试一试。
【excel根据单元格区域中出现的频率和大小返回唯一值列表】公式
在单元格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根据单元格区域中出现的频率和大小返回唯一值列表

推荐阅读

小米怎么吃的快

男生绿裤子怎么搭配

苦螺的作用苦螺的营养价值

郎酒保质期一般多久郎酒保质期一般多长时间

甲醛|市面上“最坑”的5种鱼，多数人经常买，鱼贩子：我从不给家人吃

鹤冲天是什么茶

水鸡煲汤要放什么材料水鸡煲汤要放哪些材料

竞技二打一的基本杀法——偷梁换柱杀法

爱情公寓外传开心原力大结局

洗黄喉的方法如何清洗黄喉

怎么|多肉植物怎么叶插？多肉植物叶片的选择了解一下

黑曜石的功效与作用

抖音集音符分五亿元怎么玩

玳瑁色布偶猫好吗如何分辨玳瑁色布偶猫

火影忍者的七把鬼刀是哪几把？

长脸脸大适合什么发型女生

拒绝的近义词拒绝的近义词列述

手机停机了充话费还能用吗？

蚕豆冷冻保存方法蚕豆怎么保存冷冻

鸡蛋怎么吃最不健康？