九色国产,午夜在线视频,新黄色网址,九九色综合,天天做夜夜做久久做狠狠,天天躁夜夜躁狠狠躁2021a,久久不卡一区二区三区

INDEX+SMALL+IF+ROW函數(shù)組合使用解析

轉(zhuǎn)載▼

很多人在Excel中用函數(shù)公式做查詢的時(shí)候，都必然會(huì)遇到的一個(gè)大問題，那就是一對(duì)多的查找/查詢公式應(yīng)該怎么寫？大多數(shù)人都是從VLOOKUP、INDEX+MATCH中入門的，縱然你把全部的多條件查找方法都學(xué)會(huì)了而且運(yùn)用嫻熟，如VLOOKUP和&、SUMPRODUCT、LOOKUP(1,0/....，但仍然只能對(duì)這種一對(duì)多的查詢望洋興嘆。

這里講的INDEX+SMALL+IF+ROW的函數(shù)組合，就是解決一對(duì)多查詢的一種通式，如果你能掌握，那在Excel里基本上就沒有什么查詢你是實(shí)現(xiàn)不了的了（除了INDIRECT+RC引用）。

下面，我們先來看看示例數(shù)據(jù)和查詢要求：

由于VLOOKUP、INDEX+MATCH、LOOKUP(1,0/都只是一對(duì)一的查詢，有的是只查詢第一個(gè)，有的是只查找最后一個(gè)，所以這種組合對(duì)于我們這里的要求完全無用武之地。所以，你也別把精力都花在這個(gè)上面，雖然也是可以構(gòu)造出來的，但今天我們要講的這個(gè)組合，是最基本，也是最容易理解的通式，所以請(qǐng)把精力花在這上面。

問題1，解答：

=IF(ROW(A1)>COUNTIF($B:$B,"Sam"),"",INDEX(A:A,SMALL(IF($B$2:$B$20="Sam",ROW($2:$20)),ROW(A1))))    [公式一]

 

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$20="Sam",ROW($2:$20)),ROW(A1))),"")   [公式二]

 

=INDEX(A:A,SMALL(IF($B$2:$B$20="Sam",ROW($2:$20),4^8),ROW(A1)))&""   [公式三]

 

首先，這三個(gè)都是數(shù)組公式，什么是“數(shù)組公式”呢？數(shù)組公式最特殊也最直接的表現(xiàn)，就是你在單元格里輸入完公式之后，要按Ctrl+Shift+Enter三鍵結(jié)束，跟“普通公式”只按Enter結(jié)束有明顯的區(qū)別。

為什么有三個(gè)公式呢？其實(shí)這三個(gè)公式的作用和核心是一樣的，只是應(yīng)對(duì)不同Excel版本、不同數(shù)據(jù)類型所用到的屏蔽錯(cuò)誤值的手法有所差異而已。

下面我們著重講講[公式三]，因?yàn)檫@個(gè)結(jié)構(gòu)里函數(shù)要素最齊全，而組合也是非常符合我們標(biāo)題所講到的。

首先我們來簡化和分解一下這個(gè)公式：

從上圖我們不難看出，這個(gè)組合就是由INDEX作為主體函數(shù)，第一參數(shù)就是我們要查詢并返回的數(shù)據(jù)區(qū)域，第二參數(shù)就是由SMALL構(gòu)造的一個(gè)公式，而SMALL構(gòu)造的函數(shù)，無非就是由IF判斷生成的一個(gè)內(nèi)存array。

如果你沒有函數(shù)基礎(chǔ)的話，估計(jì)還是理解不了，那我們先返回IF結(jié)構(gòu)的計(jì)算結(jié)果，也就是判斷B2:B20區(qū)域，如果等于“Sam”，就返回對(duì)應(yīng)的所在行號(hào)，不相等的話，就返回4^8，就是4的8次方冪，即65,536，這在xls格式文檔中，相當(dāng)于最大行號(hào)，在xlsx格式則不然。

OK，就我們圖中的數(shù)據(jù)，抹黑SMALL函數(shù)的array參數(shù)，再按F9，不難返回一個(gè)內(nèi)存數(shù)組如下：

{65536;65536;65536;65536;6;65536;65536;65536;65536;11;65536;13;65536;65536;65536;65536;18;65536;65536}

簡化一下，我們用“極大”來表示65536，那結(jié)果就是：

{極大;極大;極大;極大; 6 ;極大;極大;極大;極大;11 ;極大; 13;極大;極大;極大;極大; 18 ;極大;極大 }

這個(gè)內(nèi)存數(shù)組也就是這個(gè)公式組合里最關(guān)鍵的，你可以看到SMALL函數(shù)的第二參數(shù)是ROW(A1)，這個(gè)是返回A1單元格所在的行號(hào)，也就是1，當(dāng)我們整個(gè)公式下拉填充之后，就可以得到ROW(A2)、ROW(A3)、ROW(A4)這樣的變化，也就是1、2、3、4……這樣的自然數(shù)序列，從而可以把上面簡化了的內(nèi)存數(shù)組里的6、11、13、18給提取出來，因?yàn)?是最小值、11是倒數(shù)第二小、13是倒數(shù)第三小、18是倒數(shù)第四小的值，如果還不明白，那請(qǐng)?jiān)趩卧窭镙斎搿?strong>=SMALL(”然后按F1查閱SMALL函數(shù)的語法和功能說明。

6、11、13、18代表什么，我們提取出來有什么用呢？回過頭去看看IF函數(shù)就明白了，原來這就是那些滿足條件的記錄所在的行號(hào)，這樣一來我們就可以把一對(duì)多的所有符合條件的記錄全都提取出來了。

現(xiàn)在回過頭來，說說這三個(gè)公式都有什么差異和優(yōu)勢(shì)？

從上面的分解過程我們也可以看到，其實(shí)我們只能憑借下拉公式來得到所有滿足條件的所有記錄，但具體有多少記錄我們不清楚，而且不同的條件返回的記錄數(shù)量也是不確定的，所以這個(gè)公式就決定了我們必須要有容錯(cuò)機(jī)制，保證公式下拉之后，不因?yàn)榉祷赜涗洈?shù)量的不同而顯示多余的0值或者錯(cuò)誤值，最常見的如#NUM!。

----------------------------------------------------------

第一個(gè)公式比較長，但公式用了一個(gè)IF，直接用COUNTIF返回滿足條件的記錄數(shù)量，然后只顯示滿足條件的記錄，公式下拉后其余數(shù)量一率用空值表示，而且這里IF函數(shù)的False結(jié)果可以直接省略以返回FALSE；

第二個(gè)公式尤其適用于xlsx格式文檔上，直接省略IF的第三參數(shù)，因?yàn)镮FERROR可以涵蓋所有錯(cuò)誤而不必多費(fèi)心；

第三個(gè)公式只適用在沒有特殊格式的數(shù)據(jù)上，如我們示例數(shù)據(jù)里的日期、數(shù)值，其實(shí)都不適合用這個(gè)公式，因?yàn)槲覀児接幸粋€(gè)4^8的極大值，而且INDEX函數(shù)最后面接了一個(gè)&""，其根本目的是為了避免返回65536行里空值通過公式得到0，但這個(gè)的間接作用就是將數(shù)據(jù)直接轉(zhuǎn)化為文本，所以當(dāng)你要返回的數(shù)據(jù)里有數(shù)值或者日期值，或者其他自定義格式時(shí)，就都會(huì)被打回原形。。。。

到此為止，你應(yīng)該基本上能自己應(yīng)用了吧？如果還不行，那請(qǐng)重讀一遍，熟能生巧嘛~~

下面講講第二、第三個(gè)問題的公式寫法，其實(shí)會(huì)了第一個(gè)，第二個(gè)依瓢畫葫蘆是不成問題的，巧妙的是第三個(gè)問題，由于我們本身就是在SMALL的第一參數(shù)返回一個(gè)內(nèi)存數(shù)組，所以第三個(gè)問題才突顯這個(gè)組合的優(yōu)勢(shì)。

這里就只講公式寫法而不展開討論，公式很容易看明白的，只是內(nèi)在的機(jī)理可能需要先去接觸學(xué)習(xí)一下數(shù)組公式的基礎(chǔ)內(nèi)容，才容易深化。

問題2，解答：

=IFERROR(INDEX(A:A,SMALL(IF($C$2:$C$20%<50,ROW($2:$20)),ROW(A1))),"")

 

=IF(ROW(A1)>COUNTIF($C:$C,"<5000"),"",INDEX(A:A,SMALL(IF($C$2:$C$20<5000,ROW($2:$20)),ROW(A1))))

問題3，解答：

=IFERROR(INDEX(A:A,SMALL(IF(MONTH($D$2:$D$20)=3,ROW($2:$20)),ROW(A1))),"")

 

=IF(ROW(A1)>SUMPRODUCT(N(MONTH($D$2:$D$20)=3)),"",INDEX(A:A,SMALL(IF(MONTH($D$2:$D$20)=3,ROW($2:$20)),ROW(A1))))

然后，而且必須是數(shù)組公式，Ctrl+Shift+Enter三鍵結(jié)束，自己書寫公式的時(shí)候注意絕對(duì)引用與相對(duì)引用的適當(dāng)使用，這又屬于基本功咯，請(qǐng)加油。

另外這種組合里你可能看到INDEX+SMALL+IF+ROW+COUNTIF，COUNTIF就是用在SMALL函數(shù)的第二個(gè)參數(shù)，這個(gè)主要是根據(jù)列出的數(shù)據(jù)的個(gè)數(shù)，提取第幾個(gè)的值，對(duì)于雜序無指條件的重復(fù)值提取，就正好派上用場(chǎng)，具體可以根據(jù)自己的使用情況和需求，消化吸收為自己的知識(shí)。