首页 产品介绍 联系我们 新闻资讯 企业文化 成功案例 业务合作 人才招聘 公司简介

新闻资讯

你的位置:海口市一特信百货有限责任公司 > 新闻资讯 > 它确定了小组排序的范围

它确定了小组排序的范围

发布日期:2024-06-25 10:11    点击次数:185

它确定了小组排序的范围

送人玫瑰,手有余香,请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径!

【置顶公众号】或者【设为星标】及时接收更新不迷路

图片

小伙伴们好,今天来和大家分享一道烧脑的排序题目。说这道题目难,是因为它包含有合并单元格,并且,要以合并单元格为分组,进行组内排序。

今天介绍的的这两种方法,是解决这类题目的两个常用手段和技巧。此类型题目在日常工作中也经常会遇到,有心的小伙伴们可要收藏好喽!

原题是这样子的:

图片

将左侧的源数据按分组进行由大到小排序。朋友们有什么好的方法吗?

01

按组排序

所谓按组排序,即是按照小组分别来排序,先排第一组,再依次排序后面的小组。

图片

在单元格H2中输入下列公式,三键回车并向下向右拖曳即可。

=INDEX(C$2:C$13,MOD(MAX(((LOOKUP(ROW($A$2:$A$13),ROW($A$2:$A$13)/($B$2:$B$13<>""))*(1-COUNTIF($H$1:H1,$C$2:$C$13)))/1%%%+$D$2:$D$13/1%+ROW($D$2:$D$13))*(LOOKUP(ROW($B$2:$B$13),ROW($B$2:$B$13)/($B$2:$B$13<>""),$B$2:$B$13)=LOOKUP("座",$B$2:$B2))),100)-1)

公式比较长,我们下面一起来分解一下。

思路:

LOOKUP(ROW($A$2:$A$13),ROW($A$2:$A$13)/($B$2:$B$13<>""))部分,这里使用的LOOKUP函数的一个常用技巧,目的是要用行标来填充空白的单元格。公式的结果为{2;2;2;5;5;5;8;8;8;8;12;12}

(1-COUNTIF($H$1:H1,$C$2:$C$13))部分,目的是将在H列中已经提取到的数据从源数据中屏蔽掉,避免其再次被提取。这样做的原因我们后面再解释

接下来我们开始加权处理。上面两部分的结果相乘,并扩大1百万倍;同时将D列的数据扩大1百倍。这两者相加后再加上对应的行号。这部分的结果是{2086302;2094503;2060704;5072105;5080906;5002607;8019108;8010909;8024110;8013711;12006412;12081813}

 接下来我们看条件。

LOOKUP(ROW($B$2:$B$13),ROW($B$2:$B$13)/($B$2:$B$13<>""),$B$2:$B$13)部分,和上面介绍的一样,利用LOOKUP函数用小组名来填充空白单元格。其结果为{"第一组";"第一组";"第一组";"第二组";"第二组";"第二组";"第三组";"第三组";"第三组";"第三组";"第四组";"第四组"}

LOOKUP("座",$B$2:$B2)部分,随着公式向下拖曳,它依次会返回第一组、第二组、第三组和第四组。这也是利用的LOOKUP函数的特性,“座”字在EXCEL中是一个比较大的汉字,在一个区域内查找“座”,如果查找不到,会返回其找到的最后一个字符

这两部分相比较,就是条件,它确定了小组排序的范围。其结果为{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},前三个TRUE就代表了是第一小组

上面所有这些加权和条件乘在一起,结果如下{2086302;2094503;2060704;0;0;0;0;0;0;0;0;0},你看,非0部分就是第一小组的数据

利用MAX函数提取到最大值。这里不能用LARGE函数。因为LARGE函数有第二参数,第二参数随着公式向下拖曳会按自然数序列增长。而我们小组排序时每个小组都要按第一、第二、第三来提取数据的,因此这里不能使用LARGE函数。而要使用MAX函数,就要确保小组的每一个数值都有机会当上最大值。因此就需要借助(1-COUNTIF($H$1:H1,$C$2:$C$13))来处理。在第二行时,COUNTIF函数的查找范围是$H$1:H1,查找的结果是{0;0;0;0;0;0;0;0;0;0;0;0},萝岗区孔经棉类有限公司1减去其后得到的结果是{1;1;1;1;1;1;1;1;1;1;1;1}, 麻章区粒业香精有限公司相乘后表示选取全部的源数据, 荔蒲县帝机搪瓷有限公司最终MAX函数取最大值前的范围是{2086302;2094503;2060704;0;0;0;0;0;0;0;0;0},取出的最大值是2094503;当公式拖曳到第三行时,COUNTIF函数的查找范围就变成了$H$1:H2,在这个范围内已经包含了第一个查找值“韩雪琳”,因此COUNTIF函数的结果就是{0;1;0;0;0;0;0;0;0;0;0;0},表示源数据中第二行的“韩雪琳”被找到。1减去其后得到的结果是{1;0;1;1;1;1;1;1;1;1;1;1},表示源数据中除第二行外,其余都可以选取。最终MAX函数取最大值前的范围是{2086302;94503;2060704;0;0;0;0;0;0;0;0;0},取出的最大值就是2086302

取到最值后,再利用MOD函数对100求余,得到了行号,最后再利用INDEX函数返回正确答案

02

整体排序

这种方法,我们不去考虑合并单元格了,只看D列的数据。同样,也需要进行加权处理。

图片

首选选中单元格区域H2:H13,并输入下列公式,三键回车确认。

=INDEX(C$2:C$13,MATCH(LARGE((9-SUBTOTAL(3,OFFSET($B2,,,ROW($1:$12))))/1%%+$D2:$D13,ROW($1:$12)),(9-SUBTOTAL(3,OFFSET($B2,,,ROW($1:$12))))/1%%+$D2:$D13,成功案例))

思路:

SUBTOTAL(3,OFFSET($B2,,,ROW($1:$12)))部分,以单元格B2为基点向下偏移,偏移后的行高分别是1、2、…、12行,再利用SUBTOTAL函数统计非空单元格个数。其运算结果为{1;1;1;2;2;2;3;3;3;3;4;4}

9-SUBTOTAL(3,OFFSET($B2,,,ROW($1:$12)))部分,是这道题目的关键。它实现的大小的逆序,结果为{8;8;8;7;7;7;6;6;6;6;5;5}

(9-SUBTOTAL(3,OFFSET($B2,,,ROW($1:$12))))/1%%+$D2:$D13部分,扩大1万倍后,在加上D列的数值。这样就确保了所有数据首先是按照第一组、第二组、第三组和第四组的顺序来排列。其结果为{80863;80945;80607;70721;70809;70026;60191;60109;60241;60137;50064;50818},你看,前三个数据都是第一小组的,但是还没有排序

LARGE((9-SUBTOTAL(3,OFFSET($B2,,,ROW($1:$12))))/1%%+$D2:$D13,ROW($1:$12))部分,利用LARGE函数按照从大到小的顺序排列,其结果为{80945;80863;80607;70809;70721;70026;60241;60191;60137;60109;50818;50064}。你看,前三个数据都是第一小组的,而且也按照从大到小排列了

接下来,利用MATCH函数来查找LARGE函数这部分结果中的每个数据在(9-SUBTOTAL(3,OFFSET($B2,,,ROW($1:$12))))/1%%+$D2:$D13这部分中的位置。其结果为{2;1;3;5;4;6;9;7;10;8;12;11}

最后,得到位置信息后,利用INDEX函数返回正确答案。

03

下面这条函数也是整体排序,同样也是区域数组公式。它可以看做是上面第二个公式的改进版。

图片

朋友们,这条公式你们能够看懂吗?

本期内容练习文件提取方式:

链接:https://pan.baidu.com/s/1Q0Joi9Ik-niBfOrmP1Za3w?pwd=rm8p

提取码:rm8p

好了朋友们,今天和大家分享的内容就是这些了!喜欢我的文章请分享、转发、点赞和收藏吧!如有任何问题可以随时私信我哦!

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

我就知道你“在看”

推荐阅读

变短的下拉清单

依据关键字动态变更下拉清单,你会制作吗?

函数组合中有许多固定搭配,而这一组尤为著名!

【SHIFT】键,想说懂你不容易!

两种方法破解特殊要求的数字提取汇总成功案例,都挺好用的!

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报。