专栏首页完美ExcelExcel公式技巧46: 按出现的频率依次提取列表中的数据并排序

Excel公式技巧46: 按出现的频率依次提取列表中的数据并排序

导语:在《Excel公式技巧44:对文本进行排序》中,我们使用COUNTIF函数并结合SMALL/MATCH/INDEX函数对一系列文本进行排序,无论这些文本中是否存在重复值。在《Excel公式技巧45:按出现的频率依次提取列表中的数据》中,我们使用MATCH/ISNA/IF/MODE/INDEX函数组合提取一系列文本中不重复的数据并按出现的频率且按原数据顺序来放置数据。本文将在此基础上,提取不重复的数据,并按出现的次数和字母顺序排序数据。

如下图1所示,列A中是原来的数据,列B中是从列A中提取后的数据,其规则是:提取不重复的数据,并将出现次数最多的放在前面;按字母顺序排列。示例中,“XXX”和“DDD”出现的次数最多,均为3次,并且按字母顺序“DDD”排在“XXX”之前,因此提取的顺序为“DDD、XXX”;而“QQQ”和“AAA”都只出现了1次,排在“DDD、XXX”之后,且按字母顺序排列为“AAA、QQQ”。

图1

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

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

=INDEX(Data,MATCH(MIN(MODE.MULT(IF(NOT(COUNTIF(B$1:B1,Data)),(COUNTIF(Data,"<"& Data)+1)*{1,1}))),COUNTIF(Data,"<"& Data)+{1},0))

下拉至单元格B9。

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

名称:Data

引用位置:=A2:A9

1. COUNTIF(B$1:B1,Data)

在B1:B1中依次查找名称Data代表的单元格区域中的数据,返回一个由数字组成的数组。显然,Data中的每个数据都在B1:B1中找不到,因此返回{0;0;0;0;0;0;0;0}。我们看看在单元格B4中的公式,公式变为COUNTIF(B

2. NOT(COUNTIF(B$1:B1,Data))

当公式下拉至单元格B4时,该部分变化为:NOT(COUNTIF(B$1:B3,Data)),代入上面的中间数组,得到:NOT({0;0;1;1;1;1;1;1}),结果为:{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}。实际上,结果数组对应于输出中不存在的数据。

3. COUNTIF(Data,"<"& Data)+1

COUNTIF(Data,"<" & Data)返回名称Data代表的单元格区域中依次小于每个单元格中的数据的数值,结果是一个数组:{4;0;5;1;5;1;5;1},例如第一单元格中的数据为“QQQ”,在Data中小于“QQQ”的有4个,所以返回4。然后,将该数组加1,得到:{5;1;6;2;6;2;6;2}。之所以要加1,是为了处理COUNTIF返回0的情形,即该数据在单元格区域中最小时,将返回0。我们不希望MIN函数可能返回0,因此将返回的值加1,以确保结果为正确顺序。

4. IF(NOT(COUNTIF(B$1:B1,Data)),(COUNTIF(Data,"<"& Data)+1)*{1,1})

将上述中间结果代入,得到:IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{5,5;1,1;6,6;2,2;6,6;2,2;6,6;2,2}),结果是:{5,5;1,1;6,6;2,2;6,6;2,2;6,6;2,2}。

5. MODE.MULT(IF(NOT(COUNTIF(B$1:B1,Data)),(COUNTIF(Data,"<"& Data)+1)*{1,1}))

MODE.MULT()返回传递给其的参数中出现最多的数字,如果有多个数字出现的次数最多且相同,则将其全部返回。例如,对于数组{1,2,2,3,2,3,4},MODE.MULT返回{2},因为它是数组中出现最频繁的项;对于数组{1,2,2,3,3,4},MODE.MULT返回{2,3},因为它们出现的次数都是最多的;对于数组{1,2,3,4},MODE.MULT返回错误,通过将{1;2;3;4}与{1,1}相乘,得到{1,1;2,2;3,3;4,4},从而创建一些重复项,以防止MODE.MULT错误。

因此,上述公式代入中间结果后为:

MODE.MULT({5,5;1,1;6,6;2,2;6,6;2,2;6,6;2,2})

有两个数字出现的次数最多,返回:

{6;2}

6. 将上述结果传递到MIN函数,即:

MIN({6;2})

结果为:

2

按字母顺序返回排在前面的数据所在的位置。

7. COUNTIF(Data,"<" &Data)+{1}

如前所述,返回数组{5;1;6;2;6;2;6;2}。加{1}将结果强制转换为数组,同时将其每个值增加1。这是为了处理Data仅由一项组成的特殊情况。通过加{1},确保MATCH()函数将其第二个参数作为数组而不是单个值进行处理。

8. MATCH(MIN(MODE.MULT(IF(NOT(COUNTIF(B$1:B1,Data)),(COUNTIF(Data,"<"& Data)+1)*{1,1}))),COUNTIF(Data,"<" & Data)+{1},0)

将上述中间结果代入,得到:MATCH(2, {5;1;6;2;6;2;6;2},0),结果为:4。

9. 此时公式转换为:

=INDEX(A2:A9,4)

得到:

DDD

如果不想出现#N/A,则使用数组公式:

=IFERROR(INDEX(Data,MATCH(MIN(MODE.MULT(IF(NOT(COUNTIF(B$1:B4,Data)),(COUNTIF(Data,"<"& Data)+1)*{1,1}))),COUNTIF(Data,"<" &Data)+{1},0)),"")

很巧妙的公式!多使用“公式求值”和F9键,仔细领会这个公式的运行原理,从而更加熟悉函数的应用技巧。

本文分享自微信公众号 - 完美Excel(excelperfect),作者:fanjy

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-08-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Excel公式练习46: 获取最大年增长率对应的值

    导语:继续研究来自于excelxor.com的案例。这个案例不是很复杂,但解决方案却很巧妙。

    fanjy
  • Excel公式练习63: 求数值中的各个数字之和

    导语:本文的案例在前面的文章中有过介绍,这里给出两个解决方案,进一步巩固所学的知识。

    fanjy
  • Excel应用实践12:在用户窗体中添加、查找和编辑数据记录

    在Excel中,我已经创建了一个输入数据的用户窗体,用于在工作记录工作表中添加新数据记录。最近,老板提出了新的需求,要通过该用户窗体能够编辑数据记录,增强其功能...

    fanjy
  • 继Cloud,Machine Learning之后,OOW2017的第三个关键词

    伴随着秋雨绵绵,我们郑重地向假日告别,从此迎来又一段筑梦的旅程。 刚刚过去的国庆中秋双节,Oracle OpenWorld 2017于美国旧金山隆重开幕,在这场...

    数据和云
  • 美国大学数据科学(Data Science)教育实践

    本文作者:李琦,现任北肯塔基大学(North Kentucky University)计算机系(Computer Science)数据科学(Data Scien...

    叶锦鲤
  • O‘reilly Strata:数据科学部门如何建立

    很多牛逼的公司都宣称在建立数据科学部门,这个部门该如何组建,大家都在摸石头过河。O‘reilly Strata今年 六月份发布了报告 《Analyzing th...

    CDA数据分析师
  • IT公司如何构建数据科学部门?答案在这里

    O‘reilly Strata今年 六月份发布了报告 《Analyzing the Analyzers》,比较清晰的阐述了数据科学部门所需要的不同角色及其技能。...

    华章科技
  • autoware 视频展示3个

    用户1908973
  • Golang之变量去哪儿

    写过C/C++的同学都知道,调用著名的malloc和new函数可以在堆上分配一块内存,这块内存的使用和销毁的责任都在程序员。一不小心,就会发生内存泄露,搞得胆战...

    李海彬
  • Golang内存逃逸是什么?怎么避免内存逃逸?

    C/C++中动态分配的内存需要我们手动释放,导致猿们平时在写程序时,如履薄冰。这样做有他的好处:程序员可以完全掌控内存。但是缺点也是很多的:经常出现忘记释放内存...

    sunsky

扫码关注云+社区

领取腾讯云代金券