excel數(shù)組--隨心所欲

1、EXCEL函函數(shù)數(shù)常常用用技技巧巧淺淺析析(四四)技技巧巧四四:數(shù)數(shù)組組-隨隨心心所所欲欲數(shù)組的變化可以說是函數(shù)的精髓部分,對于數(shù)組的解析太多前輩高人做過很多的講解過了,我這里只不過把前輩高人講過的東西再重新解讀一下,數(shù)組高手可以飄過。本技巧介紹的都是以CTRL+SHIFT+回車鍵結(jié)束的內(nèi)存數(shù)組公式，所謂內(nèi)存數(shù)組，簡單的講就是在內(nèi)存中保存的或者說用F9鍵在公式欄查看時可以顯示全部結(jié)果。一一:數(shù)數(shù)組組的的基基本本原原理理1:一一個個縱縱向向一一維維數(shù)數(shù)組組與與單單個個元元素素的的運運算算;單個元素:我這里為什么要把他叫做單個元素,因為無論是一個單元格也好,一個常量數(shù)值或文本也好,在數(shù)組的運算中,

2、我們即不能把他看成一個縱向數(shù)組,也不能把他看成一個橫向數(shù)組,因為常常在與一維或二維的運算中,這個元素是具有擴展性的,如果與他運算的數(shù)組是一個五行一列的縱向數(shù)組,那這個元素也會自動擴展成一個五行一列的數(shù)組與之運算,其結(jié)果也必然是五行一列的結(jié)果;縱向一維數(shù)組單個元素自動擴展結(jié)果111222133+13+1444155516公公式式:=B13:B17+12:一一個個橫橫向向一一維維數(shù)數(shù)組組與與單單個個元元素素的的運運算算;同理,如果這個與其運算的數(shù)組是一個一行五列的橫向數(shù)組,那這個元素也會自動擴展為一個一行五列的數(shù)組與之運算,其結(jié)果也必然是一個五行一列的結(jié)果;橫向一維數(shù)組12345單個元素+1可可以

3、以用用F9鍵鍵查查看看結(jié)結(jié)果果的的公公式式欄欄,12345形形成成的的是是內(nèi)內(nèi)存存數(shù)數(shù)組組2,3,4,5,62,3,4,5,6+自動擴展11111結(jié)果23456公公式式:=C23:G23+13:一一個個二二維維數(shù)數(shù)組組與與單單個個元元素素的的運運算算;同樣,一個二維數(shù)組與單個元素進行運算,那這個元素也會自動擴展成與之運算的二維數(shù)組的行列數(shù),其結(jié)果與與其運算的二維數(shù)組一致;二行二列數(shù)組單個元素自動擴展16161127+127113838+11494911510510114:一一個個橫橫向向數(shù)數(shù)組組與與縱縱向向數(shù)數(shù)組組運運算算;一個橫向數(shù)組與一個縱向數(shù)組進行運算,縱向數(shù)組擴展成為與其進行運算的橫向

4、數(shù)組的列數(shù),形成一個行數(shù)與原數(shù)組相同,列數(shù)與縱向數(shù)組相同的二維數(shù)組;橫向數(shù)組擴展成為一個行數(shù)與縱向數(shù)組相同,列數(shù)為原數(shù)組列數(shù)相同;然后兩個新的數(shù)組相對應(yīng)的值進行運算,得到一個行數(shù)與縱向數(shù)組相同,列數(shù)與橫向數(shù)組相同的結(jié)果橫向數(shù)組678910縱向數(shù)組1+2345縱向數(shù)組擴展1111122222333334444455555+橫向數(shù)組擴展678910678910678910678910678910結(jié)果789101189101112 可可以以用用F9鍵鍵查查看看結(jié)結(jié)果果的的公公式式欄欄,910111213 形形成成的的是是內(nèi)內(nèi)存存數(shù)數(shù)組組7,8,9,10,11;7,8,9,10,11;10111213

5、14 8,9,10,11,12;9,10,11,12,13;10,11,12,13,14;11,12,13,14,158,9,10,11,12;9,10,11,12,13;10,11,12,13,14;11,12,13,14,1511121314155:一一維維同同向向數(shù)數(shù)組組運運算算兩個一維同向數(shù)組運算,其中有一個條件就是這兩個數(shù)組的行數(shù)或列數(shù)必須是相等的,其結(jié)果行或列數(shù)與原數(shù)組相同,如果不相等,則不會自動擴展,而會產(chǎn)生錯誤值;數(shù)組一數(shù)組二結(jié)果145數(shù)組對稱2+57369公公式式:=B76:B78+D76:D78數(shù)組一數(shù)組二結(jié)果145數(shù)組不對稱2+573697#N/A8#N/A公公式式:=B

6、81:B83+D81:D856:一一維維數(shù)數(shù)組組與與二二維維數(shù)數(shù)組組運運算算一維數(shù)組與二維數(shù)組的運算與一維同向數(shù)組原理相同,那就是一維數(shù)組的行數(shù)或列數(shù)必須與二維數(shù)組的行數(shù)或列數(shù)相同,一維數(shù)組會自動擴展成二維數(shù)組,其大小與二維數(shù)組一致,否則會產(chǎn)生錯誤,一維數(shù)組二維數(shù)組結(jié)果147582+58710369912公公式式:=B92:B94+D92:E94一維數(shù)組二維數(shù)組結(jié)果14758縱向數(shù)組+2+587103699124#N/A#N/A公公式式:=B98:B101+D98:E1007:二二維維數(shù)數(shù)組組與與二二維維數(shù)數(shù)組組的的運運算算二維數(shù)組與二維數(shù)組的運算,兩個數(shù)組的行列數(shù)必須一致,其結(jié)果行列數(shù)與原數(shù)

7、組相同,否則會產(chǎn)生錯誤.數(shù)組一數(shù)組二結(jié)1014122037111514224812161624數(shù)組一數(shù)組1014182237111519234812162024二二：一一維維轉(zhuǎn)轉(zhuǎn)二二維維ABCD轉(zhuǎn)換成ABCEDEFFGHIGHI思思維維一一：LOOKUP函函數(shù)數(shù)(此此題題出出一一位位前前輩輩高高人人貼貼子子,具具體體貼貼子子沒沒查查到到)LOOKUP函數(shù)一般利用第一參數(shù)來驅(qū)動數(shù)組的結(jié)果，我們來想像一下，我們需要構(gòu)造一個什么樣的數(shù)組才會得到結(jié)果第一步：首先利用ROW函數(shù)構(gòu)造LOOKUP函數(shù)的第二參數(shù)與第三參數(shù)(向量法)1A2B3C4D5E6F7

8、G8H9ILOOKUP(現(xiàn)現(xiàn)在在不不考考慮慮第第一一參參數(shù)數(shù),ROW(1:9),A9:A17)第二步:需要得到的結(jié)果為三行三列,我們的第一參數(shù)也必然是一個三行三列數(shù)組來驅(qū)動函數(shù)的結(jié)果想像一個如果要得到正確的結(jié)果,我們的第一參數(shù)就必須為以下數(shù)組:123456789第三步:在我們要得到正確結(jié)果中的第一參數(shù)中尋找規(guī)律,這個規(guī)律是很好找的,我們會發(fā)現(xiàn)這個數(shù)組直接用行列號就可以構(gòu)造出來的,列號123 公公式式:=(ROW(1:3)-1)*3+COLUMN(A:C)行號112324563789公式解析:首先用ROW(1:3)-1產(chǎn)生一個一列三行縱向數(shù)組,其結(jié)果為0;1;2,再用這個數(shù)組乘以3,其結(jié)果依然為

9、一列三行的縱向數(shù)組0;3;6001 乘以33其原理為縱向數(shù)組的第一個元素乘以單個數(shù)字,其結(jié)果數(shù)組方向不會改變;26=ROW(1:3)-1=(ROW(1:3)-1)*3再用COLUMN(A:J)產(chǎn)生一個一行三列的橫向數(shù)組,其結(jié)果為1,2,3123=COLUMN(A:C)最后用縱向的一維數(shù)組(ROW(1:3)-1)*3加上橫向的一維數(shù)組COLUMN(A:C),其結(jié)果為一個三列三行的二維數(shù)組1,2,3;4,5,6;7,8,91230123 此數(shù)組運算的原理可以理解為:每一個縱向數(shù)組與每一個橫向數(shù)組的元素相加,其結(jié)果擺放在兩個數(shù)組行列相交的位置3456 或者也可以這樣理解:每一個橫向數(shù)組與每一個縱向數(shù)

10、組的元素相加,其結(jié)果擺放在兩個數(shù)組行列相交的位置6789=C60:C62+D59:F59第四步:利用LOOKUP的向量法進行每一個第一參數(shù)的查找,得到結(jié)果的數(shù)組方向與第一參數(shù)一致ABCDEFGHI=LOOKUP(ROW(1:3)-1)*3+COLUMN(A:C),ROW(1:9),B9:B17)思思維維二二:INDIRECT函函數(shù)數(shù)利用INDIRECT函數(shù)對單元格引用的特性也可以實現(xiàn)以上結(jié)果,利用INDIRECT函數(shù)的第一參數(shù)來驅(qū)到數(shù)組的方向,第一步:我們設(shè)想一下我們我們應(yīng)該如果構(gòu)造INDIRECT函數(shù)的第一參數(shù),現(xiàn)我們的原數(shù)據(jù)在B9:B17區(qū)域,那INDIRECT函數(shù)的第一參數(shù)就應(yīng)該如下表:

11、B9B10B11略去B91011B12B13B14121314B15B16B17151617第二步:如何實現(xiàn)以上效果,其實規(guī)律同思維一是一樣的,只是數(shù)字不一樣而已列號91011行號191011 公公式式:=(ROW(1:3)-1)*3+COLUMN(I:K)21213143151617公式解析:同思維一,只是增大了列號第三步:根椐INDIRECT函數(shù)的特性,加上列號,就形成了第一步的第一表B9B10B11B12B13B14B15B16B17第四步:用INDIRECT函數(shù)引用上面得出的單元格地址,但得到的結(jié)果為一個三維結(jié)果,用F9與單元格無法顯示,在本示例中為文本,可以用T函數(shù)轉(zhuǎn)換,如果是數(shù)字,

12、就需要用N函數(shù)來轉(zhuǎn)換,注注意意:T與與N函函數(shù)數(shù)轉(zhuǎn)轉(zhuǎn)換換三三維維后后得得到到的的結(jié)結(jié)果果只只是是每每一一個個三三維維平平面面中中第第一一個個單單元元格格的的結(jié)結(jié)果果,平平面面中中不不是是第第一一個個單單元元格格中中的的內(nèi)內(nèi)容容無無法法取取出出.單個元素:我這里為什么要把他叫做單個元素,因為無論是一個單元格也好,一個常量數(shù)值或文本也好,在數(shù)組的運算中,我們即不能把他看成一個縱向數(shù)組,也不能把他看成一個橫向數(shù)組,因為常常在與一維或二維的運算中,這個元素是具有擴展性的,如果與他運算的數(shù)組是一個五行一列的縱向數(shù)組,那這個元素也會自動擴展成一個五行一列的數(shù)組與之運算,其結(jié)果也必然是五行一列的結(jié)果;#VA

13、LUE!#VALUE!縱向一維數(shù)組如果不加T的結(jié)果為#VALUE!#VALUE!#VALUE!#VALUE!最最后后公公式式:=T(INDIRECT(b&(ROW(1:3)-1)*3+COLUMN(I:K)思思維維三三:OFFSET函函數(shù)數(shù)利用OFFSET函數(shù)的第二參數(shù)對行的偏移,也可以實現(xiàn)以上效果,第二參數(shù)的數(shù)組構(gòu)造與思維一一致,因為OFFSET函數(shù)產(chǎn)生的結(jié)果也是三維的,所以也需要用T函數(shù)來轉(zhuǎn)換ABCDEFGHI公公式式:=T(OFFSET(B8,(ROW(1:3)-1)*3+COLUMN(A:C),)思思維維四四:VLOOKUP函函數(shù)數(shù)因為PINY版主的N(IF(1,)的發(fā)現(xiàn),用V

14、LOOKUP函數(shù)實現(xiàn)上面的內(nèi)存數(shù)組不再是難事情,雖然實現(xiàn)的公式看起來有點繁復(fù),但畢竟我們又多了一把利器。第一步：用N(IF1,)可以實現(xiàn)第一參數(shù)的數(shù)組化,構(gòu)造方法如思維一的第三步;123456789=IF(1,(ROW(1:3)-1)*3+COLUMN(A:C)第二步:構(gòu)造VLOOKUP函數(shù)第二參數(shù),這里可以用IF函數(shù)構(gòu)造一個9行二列的數(shù)組1A =IF(1,0,ROW(1:9),B9:B17)2B3C4D5E6F7G8H9I第三步:進行組裝,VLOOKUP函數(shù)結(jié)果的方向由第一參數(shù)驅(qū)動,ABCDEFGHI公公式式:=VLOOKUP(N(IF(1,(ROW(1:3)-1)*3+COLUMN(A:C

15、),IF(1,0,ROW(1:9),B9:B17),2,0)思思維維五五:INDEX函函數(shù)數(shù)同理,用N(IF(1,)也可以實現(xiàn)INDEX函數(shù)第二參數(shù)的數(shù)組化,而且INDEX函數(shù)結(jié)果的方向也是由第二參數(shù)來驅(qū)動.ABCDEFGHI=INDEX(B9:B17,N(IF(1,(ROW(1:3)-1)*3+COLUMN(A:C)三三:二二維維轉(zhuǎn)轉(zhuǎn)一一維維二維轉(zhuǎn)一維的顯示原數(shù)據(jù)內(nèi)存數(shù)組的辦法不是很多,而且如果待轉(zhuǎn)區(qū)域數(shù)值與文本相混合的話會變得很繁瑣ABCABCD轉(zhuǎn)換成DEFGHEIJKLFGHIJKL思思維維一一:OFFSET函函數(shù)數(shù)基原理利用OFFSET函數(shù)第二參數(shù)與第三參數(shù)相對應(yīng)產(chǎn)生的偏移來形成一個一

16、維數(shù)組.第一步:首先我們應(yīng)該考慮應(yīng)該怎樣來構(gòu)造第二參數(shù)與第三參數(shù),想像一下,我們是否應(yīng)該構(gòu)造成以下對應(yīng)數(shù)組,這里我們以B257單元格為偏移點.第二參數(shù)(行偏移) 第三參數(shù)(列偏移)測試00A01B02C03 現(xiàn)現(xiàn)在在我我們們來來證證明明一一下下如如此此D10 構(gòu)構(gòu)造造第第二二與與第第三三參參數(shù)數(shù)是是否否E11 正正確確F12G13H20I21J22K23L公公式式:=T(OFFSET(B257,B271:B282,C271:C282)結(jié)果證明這種思路是正確的,那剩下的事情就簡單了,我們只要找出構(gòu)造這種數(shù)組的辦法就行了第二步:構(gòu)造第二參數(shù)與第三參數(shù),從上面的猜想我們可以看到第二參數(shù)與第三參數(shù)是有

17、明顯的規(guī)律的,第二參數(shù)的規(guī)律是重復(fù)我們原數(shù)據(jù)的列數(shù)的數(shù)據(jù),可以用INT加除法實現(xiàn),第二參數(shù)重復(fù)從0到原數(shù)據(jù)的列數(shù)減1,直接用MOD函數(shù)就可以得出了第二參數(shù)(行偏移)第三參數(shù)(列偏移)000102031011121320212223公公式式:=INT(ROW(4:15)/4-1)公公式式:=MOD(ROW(4:15),4)第三步:組裝再把構(gòu)造出的參數(shù)代入OFFSET函數(shù),因為OFFSET函數(shù)取出的數(shù)據(jù)是三維平面,而這里又是文本,所有要用T函數(shù)取出ABCDEFGHIJKL公公式式:=T(OFFSET(B257,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)思思維維二二:IN

18、DIRECT函函數(shù)數(shù)INDIRECT函數(shù)的原理與OFFSET函數(shù)差不多,利用INDIRECT函數(shù)的R1C1模式也可以構(gòu)造出同樣的數(shù)組,所謂的R1C1模式就是引用第幾行(R1),第幾列(C1)的單元格第一步:根據(jù)INDIRCT特性分別設(shè)想R1部分與C1部分,現(xiàn)數(shù)據(jù)區(qū)域在R257C2:R259C5中R1R1C1C1測測試試2572A2573B2574再來測試一下我們的設(shè)想C結(jié)果證明我2575是否正確D們的設(shè)想是正確的2582E2583F2584G2585H2592I2593J2594K2595L第二步:構(gòu)造R1與C1其實我們只要仔細(xì)觀察一下,上面的數(shù)組與OFFSET函數(shù)的第二與第三參數(shù)基本上雷同,

19、只是把數(shù)字的大小放大了而已R1R1C1C1257225732574257525822583258425852592259325942595公公式式:=INT(ROW(4:15)/4+256)公公式式:=MOD(ROW(4:15),4)+2第三步:組裝原理與OFFSET函數(shù)第三步一樣ABCDEFGHIJKL公公式式:=T(INDIRECT(R&INT(ROW(4:15)/4+256)&C&MOD(ROW(4:15),4)+2,0)思思維維三三:INDEX函函數(shù)數(shù)INDEX函數(shù)的原理與OFFSET函數(shù)一樣,也是通過構(gòu)造第二參數(shù)與第三參數(shù)來實現(xiàn)二維轉(zhuǎn)一維,不過如果要形成內(nèi)存數(shù)

20、組,就必須加上N(IF1,)結(jié)構(gòu),否則形成的就是多單元格數(shù)組公式,而不是內(nèi)存數(shù)組第一步:根據(jù)INDEX函數(shù)設(shè)想第二參數(shù)與第三參數(shù)索引值區(qū)域索引測試11A12B13C14 現(xiàn)現(xiàn)在在我我們們來來證證明明一一下下如如此此D21 構(gòu)構(gòu)造造第第二二與與第第三三參參數(shù)數(shù)是是否否E22 正正確確F23G24H31I32J33K34L=INDEX(B257:E259,N(IF(1,B374:B385),N(IF(1,C374:C385)第二步:構(gòu)造索引值與區(qū)域索引通過觀察,發(fā)現(xiàn)上面構(gòu)造出來的數(shù)組與OFFSET雷同索引值區(qū)域索引111213142122232431323334=INT(ROW(4:15)/4)=

21、MOD(ROW(4:15),4)+1第三步:組裝這一步要給兩個參數(shù)加上N(IF(1,)結(jié)構(gòu),否則不會形成內(nèi)存數(shù)組,AAB請請通通過過F9鍵鍵查查看看前前后后兩兩個個公公式式的的區(qū)區(qū)別別,BCCDDEEFFGGHHIIJJKKLL=INDEX(B257:E259,N(IF(1,INT(ROW(4:15)/4),N(IF(1,MOD(ROW(4:15),4)+1)=INDEX(B257:E259,INT(ROW(4:15)/4),MOD(ROW(4:15),4)+1)思思維維四四：數(shù)數(shù)字字文文本本混混合合二二維維數(shù)數(shù)組組轉(zhuǎn)轉(zhuǎn)一一維維上面演示的是純文本的轉(zhuǎn)換,用T函數(shù)就可以轉(zhuǎn)化成結(jié)果,那我們也可以舉

22、一反三,如果源數(shù)據(jù)是數(shù)字那可以用N函數(shù)轉(zhuǎn)換,那如果是文本與數(shù)字混合的應(yīng)該怎么辦,這里我介紹三種方法,不做解析,思路還是使用上面三個函數(shù),但都不完美,這里只是起一個拋磚引玉的做用,第一種方法不會形成內(nèi)存數(shù)組,第二三種方法嵌套太多,歡迎各位前輩高人提供更佳思路.A12DEF3HIJKL方方法法一一:結(jié)果結(jié)果A結(jié)結(jié)果果是是出出來來了了,但但用用F9查查看看只只顯顯示示一一個個單單元元格格的的結(jié)結(jié)果果,A套套T也也無無效效,只只是是更更為為糟糟糕糕,不不但但沒沒有有形形成成內(nèi)內(nèi)存存數(shù)數(shù)組組,1 不不會會形形成成內(nèi)內(nèi)存存數(shù)數(shù)組組,1反反而而把把數(shù)數(shù)字字也也轉(zhuǎn)轉(zhuǎn)成成文文本本型型了了22DDEEFF33HH

23、IIJJKKLL=IF(1,OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)=T(IF(1,OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)&)方方法法二二:A1 這這里里是是完完美美轉(zhuǎn)轉(zhuǎn)換換了了,而而且且形形成成內(nèi)內(nèi)存存數(shù)數(shù)組組,2 但但問問題題是是公公式式過過長長, ,嵌嵌套套也也多多了了DEF3HIJKL=IF(T(OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)=,N(OFFSET(B373,INT(ROW(4:15)/4-1),MOD(RO

24、W(4:15),4),T(OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)方方法法三三:A12DEF3HIJKL=INDEX(B427:E429,N(IF(1,INT(ROW(4:15)/4),N(IF(1,MOD(ROW(4:15),4)+1)此此種種方方法法較較佳佳,但但是是嵌嵌套套還還是是太太多多,我我們們后后續(xù)續(xù)處處理理就就不不能能套套太太多多函函數(shù)數(shù)了了此貼為chenhh803原創(chuàng),轉(zhuǎn)貼請注明可可以以用用F9鍵鍵查查看看結(jié)結(jié)果果的的公公式式欄欄,形形成成的的是是內(nèi)內(nèi)存存數(shù)數(shù)組組2;3;4;5;62;3;4;5;6結(jié)果27 可可以以用用F9

25、鍵鍵查查看看結(jié)結(jié)果果的的公公式式欄欄,38 形形成成的的是是內(nèi)內(nèi)存存數(shù)數(shù)組組2,7;3,8;4,9;5,10;6,112,7;3,8;4,9;5,10;6,1149510611EXCEL函函數(shù)數(shù)常常用用技技巧巧淺淺析析(四四)數(shù)組的變化可以說是函數(shù)的精髓部分,對于數(shù)組的解析太多前輩高人做過很多的講解過了,我這里只不過把前輩高人講過的東西再重新解讀一下,數(shù)組高手可以飄過。本技巧介紹的都是以CTRL+SHIFT+回車鍵結(jié)束的內(nèi)存數(shù)組公式，所謂內(nèi)存數(shù)組，簡單的講就是在內(nèi)存中保存的或者說用F9鍵在公式欄查看時可以顯示全部結(jié)果。單個元素:我這里為什么要把他叫做單個元素,因為無論是一個單元格也好,一個常量

26、數(shù)值或文本也好,在數(shù)組的運算中,我們即不能把他看成一個縱向數(shù)組,也不能把他看成一個橫向數(shù)組,因為常常在與一維或二維的運算中,這個元素是具有擴展性的,如果與他運算的數(shù)組是一個五行一列的縱向數(shù)組,那這個元素也會自動擴展成一個五行同理,如果這個與其運算的數(shù)組是一個一行五列的橫向數(shù)組,那這個元素也會自動擴展為一個一行五列的數(shù)組與之運算,其結(jié)果也必然是一個五行一列的結(jié)果;可可以以用用F9鍵鍵查查看看結(jié)結(jié)果果的的公公式式欄欄,形形成成的的是是內(nèi)內(nèi)存存數(shù)數(shù)組組2,3,4,5,62,3,4,5,6同樣,一個二維數(shù)組與單個元素進行運算,那這個元素也會自動擴展成與之運算的二維數(shù)組的行列數(shù),其結(jié)果與與其運算的二維數(shù)

27、組一致;一個橫向數(shù)組與一個縱向數(shù)組進行運算,縱向數(shù)組擴展成為與其進行運算的橫向數(shù)組的列數(shù),形成一個行數(shù)與原數(shù)組相同,列數(shù)與縱向數(shù)組相同的二維數(shù)組;橫向數(shù)組擴展成為一個行數(shù)與縱向數(shù)組相同,列數(shù)為原數(shù)組列數(shù)相同;然后兩個新的數(shù)組相對應(yīng)的值進行運算,得到一個行數(shù)與縱向數(shù)組相同,列數(shù)與橫向數(shù)組相同的結(jié)果行數(shù)相同行數(shù)不同可可以以用用F9鍵鍵查查看看結(jié)結(jié)果果的的公公式式欄欄,形形成成的的是是內(nèi)內(nèi)存存數(shù)數(shù)組組7,8,9,10,11;7,8,9,10,11;8,9,10,11,12;9,10,11,12,13;10,11,12,13,14;11,12,13,14,158,9,10,11,12;9,10,11,

28、12,13;10,11,12,13,14;11,12,13,14,15兩個一維同向數(shù)組運算,其中有一個條件就是這兩個數(shù)組的行數(shù)或列數(shù)必須是相等的,其結(jié)果行或列數(shù)與原數(shù)組相同,如果不相等,則不會自動擴展,而會產(chǎn)生錯誤值;一維數(shù)組與二維數(shù)組的運算與一維同向數(shù)組原理相同,那就是一維數(shù)組的行數(shù)或列數(shù)必須與二維數(shù)組的行數(shù)或列數(shù)相同,一維數(shù)組會自動擴展成二維數(shù)組,其大小與二維數(shù)組一致,行列數(shù)一樣結(jié)果1018#N/A#N/A行數(shù)相同1220#N/A#N/A列數(shù)不同1422#N/A#N/A1624#N/A#N/ALOOKUP函數(shù)一般利用第一參數(shù)來驅(qū)動數(shù)組的結(jié)果，我們來想像一下，我們需要構(gòu)造一個什么樣的數(shù)組才會

29、得到結(jié)果第二步:需要得到的結(jié)果為三行三列,我們的第一參數(shù)也必然是一個三行三列數(shù)組來驅(qū)動函數(shù)的結(jié)果第三步:在我們要得到正確結(jié)果中的第一參數(shù)中尋找規(guī)律,這個規(guī)律是很好找的,我們會發(fā)現(xiàn)這個數(shù)組直接用行列號就可以構(gòu)造出來的,公公式式:=(ROW(1:3)-1)*3+COLUMN(A:C)首先用ROW(1:3)-1產(chǎn)生一個一列三行縱向數(shù)組,其結(jié)果為0;1;2,再用這個數(shù)組乘以3,其結(jié)果依然為一列三行的縱向數(shù)組0;3;6其原理為縱向數(shù)組的第一個元素乘以單個數(shù)字,其結(jié)果數(shù)組方向不會改變;最后用縱向的一維數(shù)組(ROW(1:3)-1)*3加上橫向的一維數(shù)組COLUMN(A:C),其結(jié)果為一個三列三行的二維數(shù)組1

30、,2,3;4,5,6;7,8,9此數(shù)組運算的原理可以理解為:每一個縱向數(shù)組與每一個橫向數(shù)組的元素相加,其結(jié)果擺放在兩個數(shù)組行列相交的位置或者也可以這樣理解:每一個橫向數(shù)組與每一個縱向數(shù)組的元素相加,其結(jié)果擺放在兩個數(shù)組行列相交的位置第四步:利用LOOKUP的向量法進行每一個第一參數(shù)的查找,得到結(jié)果的數(shù)組方向與第一參數(shù)一致利用INDIRECT函數(shù)對單元格引用的特性也可以實現(xiàn)以上結(jié)果,利用INDIRECT函數(shù)的第一參數(shù)來驅(qū)到數(shù)組的方向,公公式式:=(ROW(1:3)-1)*3+COLUMN(I:K)#VALUE!#VALUE!#VALUE!第四步:用INDIRECT函數(shù)引用上面得出的單元格地址,但

31、得到的結(jié)果為一個三維結(jié)果,用F9與單元格無法顯示,在本示例中為文本,可以用T函數(shù)轉(zhuǎn)換,如果是數(shù)字,就需要用N函數(shù)來轉(zhuǎn)換,注注意意:T與與N函函數(shù)數(shù)轉(zhuǎn)轉(zhuǎn)換換三三維維后后得得到到的的結(jié)結(jié)果果只只是是每每一一個個三三維維平平面面中中第第一一個個單單元元格格的的結(jié)結(jié)果果,平平面面中中不不是是第第一一個個單單元元格格中中的的內(nèi)內(nèi)容容無無法法取取出出.利用OFFSET函數(shù)的第二參數(shù)對行的偏移,也可以實現(xiàn)以上效果,第二參數(shù)的數(shù)組構(gòu)造與思維一一致,因為OFFSET函數(shù)產(chǎn)生的結(jié)果也是三維的,所以也需要用T函數(shù)來轉(zhuǎn)換因為PINY版主的N(IF(1,)的發(fā)現(xiàn),用VLOOKUP函數(shù)實現(xiàn)上面的內(nèi)存數(shù)組不再是難事情,雖然

32、實現(xiàn)的公式看起來有點繁復(fù),但畢竟我們又多了一把利器。同理,用N(IF(1,)也可以實現(xiàn)INDEX函數(shù)第二參數(shù)的數(shù)組化,而且INDEX函數(shù)結(jié)果的方向也是由第二參數(shù)來驅(qū)動.第一步:首先我們應(yīng)該考慮應(yīng)該怎樣來構(gòu)造第二參數(shù)與第三參數(shù),想像一下,我們是否應(yīng)該構(gòu)造成以下對應(yīng)數(shù)組,這里我們以B257單元格為偏移點.公公式式:=T(OFFSET(B257,B271:B282,C271:C282)第二參數(shù)的規(guī)律是重復(fù)我們原數(shù)據(jù)的列數(shù)的數(shù)據(jù),可以用INT加除法實現(xiàn),第二參數(shù)重復(fù)從0到原數(shù)據(jù)的列數(shù)減1,直接用MOD函數(shù)就可以得出了再把構(gòu)造出的參數(shù)代入OFFSET函數(shù),因為OFFSET函數(shù)取出的數(shù)據(jù)是三維平面,而這里

33、又是文本,所有要用T函數(shù)取出INDIRECT函數(shù)的原理與OFFSET函數(shù)差不多,利用INDIRECT函數(shù)的R1C1模式也可以構(gòu)造出同樣的數(shù)組,所謂的R1C1模式就是引用第幾行(R1),第幾列(C1)的單元格其實我們只要仔細(xì)觀察一下,上面的數(shù)組與OFFSET函數(shù)的第二與第三參數(shù)基本上雷同,只是把數(shù)字的大小放大了而已INDEX函數(shù)的原理與OFFSET函數(shù)一樣,也是通過構(gòu)造第二參數(shù)與第三參數(shù)來實現(xiàn)二維轉(zhuǎn)一維,不過如果要形成內(nèi)存數(shù)組,就必須加上N(IF1,)結(jié)構(gòu),否則形成的就是=INDEX(B257:E259,N(IF(1,B374:B385),N(IF(1,C374:C385)=INDEX(B257

34、:E259,INT(ROW(4:15)/4),MOD(ROW(4:15),4)+1)上面演示的是純文本的轉(zhuǎn)換,用T函數(shù)就可以轉(zhuǎn)化成結(jié)果,那我們也可以舉一反三,如果源數(shù)據(jù)是數(shù)字那可以用N函數(shù)轉(zhuǎn)換,那如果是文本與數(shù)字混合的應(yīng)該怎么辦,這里我介紹三種方法,不做解析,思路還是使用上面三個函數(shù),但都不完美,這里只是起一個拋磚引玉的做用,第一種方法不會形成內(nèi)存數(shù)組,第二三種方法嵌套太多,套套T也也無無效效,只只是是更更為為糟糟糕糕,不不但但沒沒有有形形成成內(nèi)內(nèi)存存數(shù)數(shù)組組,反反而而把把數(shù)數(shù)字字也也轉(zhuǎn)轉(zhuǎn)成成文文本本型型了了=T(IF(1,OFFSET(B373,INT(ROW(4:15)/4-1),MOD(

35、ROW(4:15),4)&)=IF(T(OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)=,N(OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4),T(OFFSET(B373,INT(ROW(4:15)/4-1),MOD(ROW(4:15),4)技技巧巧解解析析一一：下表為奇偶個數(shù)相同的數(shù)據(jù),現(xiàn)要對數(shù)據(jù)進行奇偶相間排列(不包含負(fù)數(shù))源數(shù)據(jù)結(jié)果235235343722464737224635354734公公式式:=ABS(SMALL(-1A4:A9*A4:A9,(6-ROW(1:6)*MOD(ROW

36、(1:6),2)+INT(ROW(2:7)/2)公公式式解解析析:此公式主要是對SMALL函數(shù)的數(shù)組化構(gòu)造,SMALL與LARGE函數(shù)的兩個參數(shù)都支持?jǐn)?shù)組,在構(gòu)造數(shù)組的時候是用得比較多的.第一步:我們先來看一下SMALL函數(shù)的第一參數(shù)的構(gòu)造過程,先用技巧一里面的-1N的方法來區(qū)分奇偶數(shù),再與源數(shù)據(jù)相乘,這樣結(jié)果就把奇數(shù)轉(zhuǎn)化為負(fù)數(shù),偶數(shù)不變區(qū)分奇偶源數(shù)據(jù)生成數(shù)組-1235-235134341*2462461372372-135-35-147-47=-1A4:A9第二步:根據(jù)第一步生成的結(jié)果,我們就要考慮第二參數(shù)的設(shè)置,可以設(shè)想一下,我們?nèi)绾闻帕胁拍芷媾枷嚅g,從上一步的結(jié)果我們可以看出,如果用SM

37、ALL函數(shù)來構(gòu)造把SMALL函數(shù)的第二參數(shù)轉(zhuǎn)化為以下數(shù)組就可以了.設(shè)想的第二參數(shù)測試63721-23552462-474343-35=SMALL(-1A4:A9*A4:A9,B27:B32)從測試結(jié)果來看,這種辦法是行得通的,即使源數(shù)據(jù)里面有相同數(shù)字,得出的結(jié)果也會是正確的,現(xiàn)在我們就要來考慮如何才能構(gòu)造成這種數(shù)組轉(zhuǎn)轉(zhuǎn)化化過過程程:(6-ROW(1:6)*MOD(ROW(1:6),2)+INT(ROW(2:7)/2)5154003*13+200111000=6-ROW(1:6)=MOD(ROW(1:6),2)=(6-ROW(1:6)*MOD(ROW(1:6),2)第三步:組裝SMALL函數(shù)的第

38、一與第二參數(shù)都構(gòu)造出來了,問題也就簡單了,我們只要把兩個構(gòu)造好的參數(shù)裝入SMALL函數(shù)就好了,再用ABS函數(shù)把負(fù)數(shù)轉(zhuǎn)為正數(shù).結(jié)果372235246473435=ABS(SMALL(-1A4:A9*A4:A9,(6-ROW(1:6)*MOD(ROW(1:6),2)+INT(ROW(2:7)/2)當(dāng)然,實現(xiàn)這種奇偶相間的法辦還有很多,請用其它辦法試解此題,比如換一種思維:=ABS(SMALL(-1A4:A9*A4:A9,MOD(ROW(1:6),2)*3+INT(ROW(2:7)/2)技技巧巧解解析析二二：源數(shù)據(jù)表為某公司三種產(chǎn)品的銷售情況情況,單號中包含了日期信息,例如A62單元格ULK-201

39、10423-CC中的數(shù)字代表,2011年4月23日,源數(shù)據(jù)表每種產(chǎn)品是按日期降序排列的,現(xiàn)在要求按查詢表的數(shù)量查詢從最晚時間到每種產(chǎn)品累計到查詢表的數(shù)量的銷售明細(xì)。（此例最后的結(jié)果是多單元格數(shù)組公式，非內(nèi)存數(shù)組）源數(shù)據(jù)表查詢表單號物料名稱銷售數(shù)量KK-LL-A1GG-LL-B1FF-LL-C1ULK-20110423-CCKK-LL-A112462110341176ULK-20110419-CCKK-LL-A1497ULK-20110415-CCKK-LL-A1637ULK-20110410-CCKK-LL-A1847ULK-20110411-CCGG-LL-B1215ULK-20110407-CCGG-LL-B1819ULK-20110402-CCGG-LL-B1407ULK-20110430-CCFF-LL-C1988ULK-20110425-CCFF-LL-C11