盧子：函數(shù)與公式的神奇之處我認(rèn)為在于同一道題目，可以有多種解法，曾經(jīng)我試過查找符合條件的數(shù)據(jù)，寫了30多種組合，不過其中有一半是湊數(shù)用的。在剛開始學(xué)習(xí)的時(shí)候，要盡量了解更多種用法，當(dāng)你熟練以后就選擇你認(rèn)為最適合你的那一種方法就可以，其他可以忽略。每個(gè)時(shí)期你對(duì)同一問題的想法都會(huì)不停的改變，就如我剛開始很喜歡用Vlookup查找，接著發(fā)現(xiàn)Lookup好用，后來發(fā)現(xiàn)Index+Match組合變幻莫測(cè)，感覺很多事情離開這個(gè)組合都很難做到一樣。
網(wǎng)友：既然你這么說了，就學(xué)學(xué)看這個(gè)組合有多經(jīng)典，多認(rèn)識(shí)幾個(gè)函數(shù)也是好事。
盧子：其實(shí)公式就跟小朋友玩的積木一樣，按需要的模型找到合適的小形狀堆積而成。只要你將寫公式當(dāng)成在玩積木，在玩的同時(shí)就不知不覺學(xué)好公式了。下面通過幾個(gè)例子看看公式是怎么堆積而成的。
Match前面我們已經(jīng)知道，作用：獲取項(xiàng)目在區(qū)域中的排位?，F(xiàn)在來看看Index語法，是對(duì)區(qū)域的行列號(hào)交叉值的引用。
=INDEX(區(qū)域,行號(hào),列號(hào))
通過一個(gè)簡(jiǎn)單的小例子來說明Index的用法。
在查詢表中查詢產(chǎn)品的價(jià)格。
型號(hào)所在行號(hào)，為第8行。
=MATCH(B3,D1:D10,0)
規(guī)格所在列號(hào)，為第3列。
=MATCH(B7,D1:G1,0)
產(chǎn)品價(jià)格，就是第8行跟第3列的交叉單元格即70。
=INDEX(D1:G10,B4,B8)
將前面三條公式組合起來，經(jīng)典的組合就這么誕生了。
=INDEX(D1:G10,MATCH(B3,D1:D10,0),MATCH(B7,D1:G1,0))
網(wǎng)友：原來利用這個(gè)組合，多條件查詢這么簡(jiǎn)單。
盧子：現(xiàn)在來了解下這個(gè)組合的擴(kuò)展運(yùn)用。
例子1 對(duì)行列號(hào)匯總。
查找行號(hào)。
=MATCH(C3,E1:E10,0)
行匯總。
=SUM(INDEX(F2:H10,C4,0))
兩個(gè)合并。
=SUM(INDEX(F2:H10,MATCH(C3,E2:E10,0),0))
剛開始不熟練這個(gè)組合，可以先拆開，然后再組合起來，這樣便于理解?？匆幌聨椭恼f明：
注解
如果同時(shí)使用參數(shù) Row_num 和 Column_num，函數(shù) INDEX 返回 Row_num 和 Column_num 交叉處的單元格中的值。
如果將 Row_num 或 Column_num 設(shè)置為 0（零），函數(shù) INDEX 則分別返回整個(gè)列或行的數(shù)組數(shù)值。若要使用以數(shù)組形式返回的值，請(qǐng)將 INDEX 函數(shù)以數(shù)組公式形式輸入，對(duì)于行以水平單元格區(qū)域的形式輸入，對(duì)于列以垂直單元格區(qū)域的形式輸入。若要輸入數(shù)組公式，請(qǐng)按 Ctrl+Shift+Enter。
注釋 在 Excel Web App 中，不能創(chuàng)建數(shù)組公式。
Row_num 和 Column_num 必須指向數(shù)組中的一個(gè)單元格；否則，INDEX 返回 錯(cuò)誤值 #REF!。
如果行列號(hào)或者列號(hào)設(shè)置為0，函數(shù)分別返回整個(gè)列或行的數(shù)組數(shù)值。這也就是說剛才為什么將Index的第3參數(shù)設(shè)置為0，就是為了引用正行的數(shù)據(jù)。
有了前面的基礎(chǔ)，我們現(xiàn)在一步到位求列總計(jì)
=SUM(INDEX(F2:H10,0,MATCH(C8,F1:H1,0)))
例子2 對(duì)區(qū)域匯總
Sum函數(shù)的區(qū)域可以理解為
=SUM(開始單元格:結(jié)束結(jié)束單元格)，如=SUM(G16:H21)
只要將區(qū)域轉(zhuǎn)換成上面的形式就行，知道開始跟結(jié)束單元格，就能匯總。
開始單元格為97。
=INDEX(F16:H24,MATCH(C16,E16:E24,0),MATCH(C18,F15:H15,0))
結(jié)束單元格為79。
=INDEX(F16:H24,MATCH(C17,E16:E24,0),MATCH(C19,F15:H15,0))
匯總的區(qū)域就是這兩個(gè)數(shù)字組成的區(qū)域G17:H21，組合起來。
=SUM(INDEX(F16:H24,MATCH(C16,E16:E24,0),MATCH(C18,F15:H15,0)):INDEX(F16:H24,MATCH(C17,E16:E24,0),MATCH(C19,F15:H15,0)))
其實(shí)公式又好像是牛，光看牛是不知道牛內(nèi)部的結(jié)構(gòu)，只有操刀將牛分解才知道牛的內(nèi)部構(gòu)造。很多時(shí)候我們看到別人寫的公式很長很長，不知道什么意思，就可以用庖丁解牛法解讀。
來看Index跟其他函數(shù)的高級(jí)組合，讓我們一起操刀，當(dāng)一回庖丁，將牛大卸八塊，好好理解下它的內(nèi)部結(jié)構(gòu)。
例子3 將左邊的格式變身成右邊的形式
提取不重復(fù)地市
=INDEX(A:A,SMALL(IF(MATCH($A$2:$A$19,$A$2:$A$19,0)=ROW($A$2:$A$19)-1,ROW($A$2:$A$19),4^8),ROW(A1)))&""
根據(jù)不重復(fù)地市獲取所有區(qū)縣對(duì)應(yīng)值
=INDEX($B:$B,SMALL(IF($A$2:$A$19=$D2,ROW($A$2:$A$19),4^8),COLUMN(A1)))&""
網(wǎng)友：這么長，還沒把他大卸八塊，自己就先暈倒了。
盧子：當(dāng)初我看到這兩條公式也嚇暈了，不過后來轉(zhuǎn)念一想，公式拆開每個(gè)函數(shù)我都會(huì)，組合起來我應(yīng)該也可以弄懂才對(duì)。
網(wǎng)友：也對(duì)哦，不能先被困難嚇倒。
盧子：那我們就來庖丁解牛，呵呵。
先來看看1,2,3，庖丁解牛1。
=MATCH($A$2:$A$19,$A$2:$A$19,0)，得到每個(gè)地市在數(shù)據(jù)源第一次出現(xiàn)的位置，如1
=ROW($A$2:$A$19)-1，獲取1到N的序列號(hào)，如2
=MATCH($A$2:$A$19,$A$2:$A$19,0)=ROW($A$2:$A$19)-1，將第一次出現(xiàn)的問題跟序號(hào)比較，如果一樣就顯示TRUE，否則顯示FALSE，如3
接著看4,5，庖丁解牛2。
為了便于解讀將MATCH($A$2:$A$19,$A$2:$A$19,0)=ROW($A$2:$A$19)-1設(shè)置為牛1
=IF(牛1,ROW($A$2:$A$19),4^8)，通過牛1知道，排位跟序號(hào)相同就是TRUE，不同就是FALSE。通過IF將相同的顯示本身的序號(hào)，不同的顯示4^8即65536，03版允許的最大行數(shù)，這一行通常是沒有數(shù)據(jù)的，也可以將4^8改成任意一個(gè)比較大的數(shù)，如10000。最后獲得由本身行號(hào)跟65536組成的區(qū)域，如4
=SMALL(IF(牛1,ROW($A$2:$A$19),4^8),ROW(A1))，SMAll(區(qū)域,N)，就是將數(shù)據(jù)升序排序，也就是說將第一次出現(xiàn)的地市的序號(hào)放在最前面，如5
經(jīng)過這兩次庖丁解牛，已經(jīng)完成了80%的工作了，只需再解牛一次即可搞定。
最后看6,7，庖丁解牛3。
=INDEX(A:A,牛2)，獲得序號(hào)的對(duì)應(yīng)值，65536因?yàn)槭强諉卧瘢眠^來就是0，如6
=INDEX(A:A,牛2)&””，將引用過來的0轉(zhuǎn)變成空文本，這樣看起來美觀點(diǎn)，如7
本來還想將公式大卸八塊，現(xiàn)在才七塊就搞定了，看來公式還不夠長。
網(wǎng)友：盧子你還真幽默，解牛三次，大切成七塊，厲害。
盧子：有了這次的剖解，下面這條公式就變得簡(jiǎn)單多了，重點(diǎn)看不同的地方即可。
=INDEX($B:$B,SMALL(IF($A$2:$A$19=$D2,ROW($A$2:$A$19),4^8),COLUMN(A1)))&""
$A$2:$A$19=$D2就是區(qū)域$A$2:$A$19跟$D2的比較，返回TRUE跟FASLE
IF($A$2:$A$19=$D2,ROW($A$2:$A$19),4^8)讓符合條件的顯示本身行號(hào)，否則顯示4^8
SMALL(IF($A$2:$A$19=$D2,ROW($A$2:$A$19),4^8),COLUMN(A1))，因?yàn)楣绞窍蛴彝侠?，COLUMN(A1)可以水平獲得序號(hào)，從而得到前N個(gè)最小值
=INDEX($B:$B,SMALL(IF($A$2:$A$19=$D2,ROW($A$2:$A$19),4^8),COLUMN(A1)))&""讓符合條件的值顯示出來，不符合的顯示空
網(wǎng)友：沒想到這么長的公式還能聽懂，真的佩服我自己。
盧子：通過這幾回的講解，公式與函數(shù)常見的用法，跟公式編寫、解讀的技巧都講得差不多，剩下的就靠我們自己靈活運(yùn)用了。要學(xué)會(huì)選擇合適自己的方法。
函數(shù)要學(xué)入門不難，要學(xué)精通真的好難，每個(gè)函數(shù)都有無數(shù)種用法，能堅(jiān)持不放棄的真的很少。
其實(shí)，看微信文章也一樣，堅(jiān)持學(xué)幾天真的很簡(jiǎn)單，堅(jiān)持學(xué)一兩年真的好難，沒多少人能做到。讀者換了一批又一批，能長期堅(jiān)持下來的超不過10人。
作者：盧子，清華暢銷書作者，《Excel效率手冊(cè) 早做完，不加班》系列叢書創(chuàng)始人，個(gè)人公眾號(hào)：Excel不加班（ID：Excelbujiaban）
聯(lián)系客服
