$B$2:$B$13)=L稀土OOKUP("座"

发布日期:2024-05-04 07:15    点击次数:198

$B$2:$B$13)=L稀土OOKUP("座"

送东说念主玫瑰,手多余香,请将著作共享给更多一又友

脱手操作是纯熟掌抓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】键,想说懂你迫害易!

两种神情破解特殊要求的数字提真金不怕火汇总稀土,齐挺好用的!

本站仅提供存储管事,扫数本色均由用户发布,如发现存害或侵权本色,请点击举报。