excel公式技巧： 獲取指定區(qū)域中2個(gè)及以上連續(xù)數(shù)值并按順序排列

excel公式技巧： 獲取指定區(qū)域中2個(gè)及以上連續(xù)數(shù)值并按順序排列

給定單元格區(qū)域A1:D5（其中每個(gè)單元格中都是整數(shù)，并且在該單元格區(qū)域內(nèi)是唯一的），使用單個(gè)公式生成一個(gè)數(shù)組，該數(shù)組由該區(qū)域中所有連續(xù)的數(shù)值組成，連續(xù)的數(shù)值至少包含兩個(gè)，且返回的數(shù)組中的元素按從小到大的順序排列。

圖1

也就是說(shuō)，可以返回結(jié)果：

{1;2;3;12;13;14;15;16;17;36;37}

也可以返回結(jié)果：

{1,2,3,12,13,14,15,16,17,36,37}

注意，所給出的公式應(yīng)包含最少的字符；在公式中必須同時(shí)包含行列引用，不允許全是列引用（如A:D）或行引用（如1:5）；公式中不允許使用名稱。

先不看答案，自已動(dòng)手試一試。

公式

公式1：

=MODE.MULT(SMALL(A1:D5,ROW(A1:A20)),IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5))

使用了91個(gè)字符。

公式2：

=MODE.MULT(IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},””))

使用了103個(gè)字符。

嘗試通過(guò)同時(shí)評(píng)估兩個(gè)COUNTIF構(gòu)造來(lái)縮短此公式構(gòu)造-并消除了對(duì)IFERROR子句的需要，但最終比上述公式稍長(zhǎng)一些，有105字符，即公式3：

=MODE.MULT(IF(MMULT(COUNTIF(A1:D5,SMALL(A1:D5,ROW(A1:A20))-{1,-1}),{1;1}),{1,1}*SMALL(A1:D5,ROW(A1:A20))))

或者公式4：

=MODE.MULT(IF(MMULT(COUNTIF(A1:D5,SMALL(A1:D5,ROW(A1:A20))-{1,-1}),{1;1}),SMALL(A1:D5,ROW(A1:A20))),A1:D5)

公式解析

由于我們想要從所給區(qū)域中返回一個(gè)數(shù)組，該數(shù)組由區(qū)域內(nèi)至少兩個(gè)連續(xù)的數(shù)值構(gòu)成，因此，執(zhí)行此操作的一種方法是針對(duì)區(qū)域內(nèi)的每個(gè)值檢查是否在該區(qū)域內(nèi)還會(huì)發(fā)現(xiàn)比該值大1或小1的值。

這樣，我們需要查看下面兩種構(gòu)造的結(jié)果：

COUNTIF(A1:D5,A1:D5+1)

和

COUNTIF(A1:D5,A1:D5-1)

由于我們要求對(duì)于每個(gè)值，其兩個(gè)返回值中只有一個(gè)為非零值即可，因此可以通過(guò)簡(jiǎn)單地將上述構(gòu)造加在一起，這等價(jià)于OR操作。于是，公式中的：

COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1)

轉(zhuǎn)換為：

{0,0,1,0;0,0,0,0;1,1,0,1;1,0,1,1;1,0,0,0}+{0,1,1,0;0,0,0,1;0,1,1,1;1,0,0,0;1,0,0,0}

結(jié)果為：

{0,1,2,0;0,0,0,1;1,2,1,2;2,0,1,1;2,0,0,0}

從中可以看出，元素0表示：在區(qū)域中未找到比要查找的值大1或小1的值。同樣，元素1表示：在區(qū)域中找到了比要查找的值大1或小1的值。值2表示在區(qū)域中找到了比要查找的值大1的值和小1的值。因此：

IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5)

轉(zhuǎn)換為：

IF({0,1,2,0;0,0,0,1;1,2,1,2;2,0,1,1;2,0,0,0},A1:D5)

得到：

{FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE}

現(xiàn)在，我們已經(jīng)生成了所需的值。但是，仍然存在兩項(xiàng)工作要解決：按升序?qū)λ鼈冞M(jìn)行排序以及刪除任何非數(shù)字元素。

使用SMALL函數(shù)來(lái)完成排序操作：

SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))

轉(zhuǎn)換為：

SMALL({FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE},ROW(A1:A20))

轉(zhuǎn)換為：

SMALL({FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})

結(jié)果為：

{1;2;3;12;13;14;15;16;17;36;37;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}

通常，我們可能試圖通過(guò)構(gòu)造一個(gè)合適的數(shù)組傳遞給SMALL函數(shù)作為其參數(shù)k的值來(lái)縮小數(shù)組，然而，這里使用了MODE.MULT函數(shù)，更簡(jiǎn)潔。注意，這里使用的縮小數(shù)組的技術(shù)，當(dāng)我們希望返回的元素在被處理的數(shù)組中出現(xiàn)的頻率相等時(shí)才合適。此外，該頻率必須至少為兩倍，因?yàn)槿绻麤](méi)有一個(gè)以上的值出現(xiàn)，那么MODE.MULT不會(huì)返回任何值。

因此，先與數(shù)組{1,1}相乘的原因是，當(dāng)我們將SMALL構(gòu)造（一個(gè)20行1列的數(shù)組）與數(shù)組{1,1}（一個(gè)1行2列的數(shù)組）相乘時(shí)，生成20行2列的數(shù)組，其每行中列的元素都是相同的。這樣：

SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1}

轉(zhuǎn)換為：

{1;2;3;12;13;14;15;16;17;36;37;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}*{1,1}

結(jié)果為：

{1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!}

可以看出，我們已經(jīng)成功地使每個(gè)元素的數(shù)量簡(jiǎn)單地翻了一倍，現(xiàn)在可以安全地將其傳遞給MODE.MULT函數(shù)。但是，我們先要消除這些錯(cuò)誤值。像絕大多數(shù)函數(shù)一樣，MODE.MULT函數(shù)不會(huì)處理包含此類錯(cuò)誤值的數(shù)組。

這樣，公式中的：

IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},””)

轉(zhuǎn)換為：

IFERROR({1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!},””)

得到：

{1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””}

最后，將其傳遞給MODE.MULT函數(shù)：

MODE.MULT(IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},””))

轉(zhuǎn)換為：

MODE.MULT({1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””})

得到：

{1;2;3;12;13;14;15;16;17;36;37}

小結(jié)

MODE.MULT函數(shù)的妙用！

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。