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