送人玫瑰，手有余香，請將文章分享給更多朋友

動手操作是熟練掌握EXCEL的最快捷途徑！

【置頂公眾號】或者【設(shè)為星標(biāo)】及時接收更新不迷路

小伙伴們好，今天來和大家分享一道燒腦的排序題目。說這道題目難，是因為它包含有合并單元格，并且，要以合并單元格為分組，進行組內(nèi)排序。

今天介紹的的這兩種方法，是解決這類題目的兩個常用手段和技巧。此類型題目在日常工作中也經(jīng)常會遇到，有心的小伙伴們可要收藏好嘍！

原題是這樣子的：

將左側(cè)的源數(shù)據(jù)按分組進行由大到小排序。朋友們有什么好的方法嗎？

按組排序

所謂按組排序，即是按照小組分別來排序，先排第一組，再依次排序后面的小組。

在單元格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函數(shù)的一個常用技巧，目的是要用行標(biāo)來填充空白的單元格。公式的結(jié)果為{2;2;2;5;5;5;8;8;8;8;12;12}

• (1-COUNTIF($H$1:H1,$C$2:$C$13))部分，目的是將在H列中已經(jīng)提取到的數(shù)據(jù)從源數(shù)據(jù)中屏蔽掉，避免其再次被提取。這樣做的原因我們后面再解釋

• 接下來我們開始加權(quán)處理。上面兩部分的結(jié)果相乘，并擴大1百萬倍；同時將D列的數(shù)據(jù)擴大1百倍。這兩者相加后再加上對應(yīng)的行號。這部分的結(jié)果是{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函數(shù)用小組名來填充空白單元格。其結(jié)果為{"第一組";"第一組";"第一組";"第二組";"第二組";"第二組";"第三組";"第三組";"第三組";"第三組";"第四組";"第四組"}

• LOOKUP("座",$B$2:$B2)部分，隨著公式向下拖曳，它依次會返回第一組、第二組、第三組和第四組。這也是利用的LOOKUP函數(shù)的特性，“座”字在EXCEL中是一個比較大的漢字，在一個區(qū)域內(nèi)查找“座”，如果查找不到，會返回其找到的最后一個字符

• 這兩部分相比較，就是條件，它確定了小組排序的范圍。其結(jié)果為{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}，前三個TRUE就代表了是第一小組

• 上面所有這些加權(quán)和條件乘在一起，結(jié)果如下{2086302;2094503;2060704;0;0;0;0;0;0;0;0;0}，你看，非0部分就是第一小組的數(shù)據(jù)

• 利用MAX函數(shù)提取到最大值。這里不能用LARGE函數(shù)。因為LARGE函數(shù)有第二參數(shù)，第二參數(shù)隨著公式向下拖曳會按自然數(shù)序列增長。而我們小組排序時每個小組都要按第一、第二、第三來提取數(shù)據(jù)的，因此這里不能使用LARGE函數(shù)。而要使用MAX函數(shù)，就要確保小組的每一個數(shù)值都有機會當(dāng)上最大值。因此就需要借助(1-COUNTIF($H$1:H1,$C$2:$C$13))來處理。在第二行時，COUNTIF函數(shù)的查找范圍是$H$1:H1，查找的結(jié)果是{0;0;0;0;0;0;0;0;0;0;0;0}，1減去其后得到的結(jié)果是{1;1;1;1;1;1;1;1;1;1;1;1}，相乘后表示選取全部的源數(shù)據(jù)，最終MAX函數(shù)取最大值前的范圍是{2086302;2094503;2060704;0;0;0;0;0;0;0;0;0}，取出的最大值是2094503；當(dāng)公式拖曳到第三行時，COUNTIF函數(shù)的查找范圍就變成了$H$1:H2，在這個范圍內(nèi)已經(jīng)包含了第一個查找值“韓雪琳”，因此COUNTIF函數(shù)的結(jié)果就是{0;1;0;0;0;0;0;0;0;0;0;0}，表示源數(shù)據(jù)中第二行的“韓雪琳”被找到。1減去其后得到的結(jié)果是{1;0;1;1;1;1;1;1;1;1;1;1}，表示源數(shù)據(jù)中除第二行外，其余都可以選取。最終MAX函數(shù)取最大值前的范圍是{2086302;94503;2060704;0;0;0;0;0;0;0;0;0}，取出的最大值就是2086302

• 取到最值后，再利用MOD函數(shù)對100求余，得到了行號，最后再利用INDEX函數(shù)返回正確答案

整體排序

這種方法，我們不去考慮合并單元格了，只看D列的數(shù)據(jù)。同樣，也需要進行加權(quán)處理。

首選選中單元格區(qū)域H2:H13，并輸入下列公式，三鍵回車確認(rèn)。

=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函數(shù)統(tǒng)計非空單元格個數(shù)。其運算結(jié)果為{1;1;1;2;2;2;3;3;3;3;4;4}

• 9-SUBTOTAL(3,OFFSET($B2,,,ROW($1:$12)))部分，是這道題目的關(guān)鍵。它實現(xiàn)的大小的逆序，結(jié)果為{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列的數(shù)值。這樣就確保了所有數(shù)據(jù)首先是按照第一組、第二組、第三組和第四組的順序來排列。其結(jié)果為{80863;80945;80607;70721;70809;70026;60191;60109;60241;60137;50064;50818}，你看，前三個數(shù)據(jù)都是第一小組的，但是還沒有排序

• LARGE((9-SUBTOTAL(3,OFFSET($B2,,,ROW($1:$12))))/1%%+$D2:$D13,ROW($1:$12))部分，利用LARGE函數(shù)按照從大到小的順序排列，其結(jié)果為{80945;80863;80607;70809;70721;70026;60241;60191;60137;60109;50818;50064}。你看，前三個數(shù)據(jù)都是第一小組的，而且也按照從大到小排列了

• 接下來，利用MATCH函數(shù)來查找LARGE函數(shù)這部分結(jié)果中的每個數(shù)據(jù)在(9-SUBTOTAL(3,OFFSET($B2,,,ROW($1:$12))))/1%%+$D2:$D13這部分中的位置。其結(jié)果為{2;1;3;5;4;6;9;7;10;8;12;11}

• 最后，得到位置信息后，利用INDEX函數(shù)返回正確答案。

下面這條函數(shù)也是整體排序，同樣也是區(qū)域數(shù)組公式。它可以看做是上面第二個公式的改進版。

朋友們，這條公式你們能夠看懂嗎？

本期內(nèi)容練習(xí)文件提取方式：

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

提取碼：rm8p