內(nèi)存數(shù)組之構(gòu)建數(shù)組應(yīng)用_ByGdliyy

1、內(nèi)內(nèi)存存數(shù)數(shù)組組之之重重新新構(gòu)構(gòu)建建數(shù)數(shù)組組應(yīng)應(yīng)用用通常情況下，用戶在內(nèi)存數(shù)組的中級(jí)應(yīng)用中時(shí)常需要通過數(shù)組公式來(lái)構(gòu)造內(nèi)存數(shù)組，如：生成110的自然數(shù)序號(hào)應(yīng)用。由于此類應(yīng)用在數(shù)組公式中運(yùn)用特別廣泛，而且許多初級(jí)用戶對(duì)這類應(yīng)用都非常熟悉，但在實(shí)際工作中如何去運(yùn)用，則是必須面對(duì)的一個(gè)問題。所以，在這里將由淺入深地對(duì)以下幾種情況的數(shù)組構(gòu)建進(jìn)行分析和總結(jié)。如果您是Excel初級(jí)用戶，那么對(duì)INDIRECT函數(shù)、ROW函數(shù)、COLUMN函數(shù)應(yīng)該不陌生，這幾個(gè)函數(shù)都是用戶常用于生成內(nèi)存數(shù)組的函數(shù)。如果您是常用數(shù)組公式的中級(jí)用戶，那對(duì)LOOKUP函數(shù)、MMULT函數(shù)可能比較熟悉，因?yàn)檫@兩個(gè)函數(shù)在生成內(nèi)存數(shù)組方

2、面有很重要的作用。初初級(jí)級(jí)一一：生生成成自自然然數(shù)數(shù)序序號(hào)號(hào)的的垂垂直直（水水平平）數(shù)數(shù)組組。初初級(jí)級(jí)二二：根根據(jù)據(jù)源源數(shù)數(shù)組組生生成成對(duì)對(duì)應(yīng)應(yīng)的的數(shù)數(shù)據(jù)據(jù)序序號(hào)號(hào)。初初級(jí)級(jí)三三：構(gòu)構(gòu)造造一一維維等等差差數(shù)數(shù)列列。中中級(jí)級(jí)一一：一一維維數(shù)數(shù)組組取取得得子子數(shù)數(shù)組組。中中級(jí)級(jí)二二：等等長(zhǎng)長(zhǎng)度度變變換換一一維維數(shù)數(shù)組組。中中級(jí)級(jí)三三：等等比比例例構(gòu)構(gòu)造造一一維維循循環(huán)環(huán)數(shù)數(shù)組組。中中級(jí)級(jí)四四：一一維維數(shù)數(shù)據(jù)據(jù)轉(zhuǎn)轉(zhuǎn)換換為為二二維維數(shù)數(shù)組組。高高級(jí)級(jí)一一：數(shù)數(shù)值值區(qū)區(qū)域域數(shù)數(shù)據(jù)據(jù)逐逐項(xiàng)項(xiàng)累累加加。高高級(jí)級(jí)二二：一一維維數(shù)數(shù)組組數(shù)數(shù)值值累累加加。高高級(jí)級(jí)三三：不不固固定定長(zhǎng)長(zhǎng)度度擴(kuò)擴(kuò)展展一一維維數(shù)數(shù)組組

3、。高高級(jí)級(jí)四四：不不定定長(zhǎng)長(zhǎng)使使用用循循環(huán)環(huán)序序列列擴(kuò)擴(kuò)展展數(shù)數(shù)組組。如如果果大大家家對(duì)對(duì)后后續(xù)續(xù)的的解解法法公公式式看看不不明明白白，建建議議使使用用“工工具具公公式式審審核核公公式式求求值值”來(lái)來(lái)查查看看公公式式的的內(nèi)內(nèi)部部運(yùn)運(yùn)算算過過程程，以以了了解解各各技技巧巧中中的的解解法法思思路路。By GdliyyE250#REF!22通常情況下，用戶在內(nèi)存數(shù)組的中級(jí)應(yīng)用中時(shí)常需要通過數(shù)組公式來(lái)構(gòu)造內(nèi)存數(shù)組，如：生成110的自然數(shù)序號(hào)應(yīng)用。由于此類應(yīng)用在數(shù)組公式中運(yùn)用特別廣泛，而且許多初級(jí)用戶對(duì)這類應(yīng)用都非常熟悉，但在實(shí)際工作中如何去運(yùn)用，則是必須面對(duì)如果您是Excel初級(jí)用戶，那么對(duì)INDIR

4、ECT函數(shù)、ROW函數(shù)、COLUMN函數(shù)應(yīng)該不陌生，這幾個(gè)函數(shù)都是用戶常用于生成內(nèi)存數(shù)組的函數(shù)。如果您是常用數(shù)組公式的中級(jí)用戶，那對(duì)LOOKUP函數(shù)、MMULT函數(shù)可能比較熟悉，因?yàn)檫@兩個(gè)函數(shù)在生成內(nèi)存數(shù)組方面有很重要的作用。如如果果大大家家對(duì)對(duì)后后續(xù)續(xù)的的解解法法公公式式看看不不明明白白，建建議議使使用用“工工具具公公式式審審核核公公式式求求值值”來(lái)來(lái)查查看看公公式式的的內(nèi)內(nèi)部部運(yùn)運(yùn)算算過過程程，以以了了解解各各技技巧巧中中的的解解法法思思路路。如果您是Excel初級(jí)用戶，那么對(duì)INDIRECT函數(shù)、ROW函數(shù)、COLUMN函數(shù)應(yīng)該不陌生，這幾個(gè)函數(shù)都是用戶常用于生成內(nèi)存數(shù)組的函數(shù)。初初級(jí)級(jí)

5、一一：生生成成自自然然數(shù)數(shù)序序號(hào)號(hào)的的垂垂直直（水水平平）數(shù)數(shù)組組。該應(yīng)用在數(shù)組公式中經(jīng)常使用，如：生成1N的自然數(shù)序列、SMALL函數(shù)的第2個(gè)參數(shù)的自然數(shù)序列等等。例：下面公式將生成自然數(shù)110的垂直數(shù)組。公式：=ROW(INDIRECT(1:10)1有些用戶可能會(huì)使用ROW(1:10)來(lái)生成數(shù)組，但如果用戶在110行區(qū)域插入或刪除行，將影響該結(jié)果的變化，因此建議用戶最好使用ROW(INDIRECT(1:10)來(lái)產(chǎn)生固定引用。求和驗(yàn)證：5555同理，下面的公式將生成110的水平數(shù)組。公式：求和驗(yàn)證：5555注意：使用INDIRECT函數(shù)來(lái)生成R1C1引用時(shí)，第2個(gè)參數(shù)必須為FALSE（或0）

6、，否則公式返回錯(cuò)誤。初初級(jí)級(jí)二二：根根據(jù)據(jù)源源數(shù)數(shù)組組生生成成對(duì)對(duì)應(yīng)應(yīng)的的數(shù)數(shù)據(jù)據(jù)序序號(hào)號(hào)。前面介紹的是直接生成數(shù)組，這個(gè)應(yīng)用中主要介紹通過數(shù)據(jù)的具體數(shù)量來(lái)動(dòng)態(tài)生成序號(hào)數(shù)組。例：根據(jù)右側(cè)數(shù)據(jù)區(qū)域生成動(dòng)態(tài)序號(hào)的垂直數(shù)組。公式：=ROW(INDIRECT(1:&COUNTA($K$14:$K$20)該公式主要利用了COUNTA統(tǒng)計(jì)函數(shù)來(lái)取得數(shù)據(jù)個(gè)數(shù)，再根據(jù)上例介紹的方法生成數(shù)組序列。注意：如果數(shù)據(jù)源是水平數(shù)組，原理相同，方法參見初級(jí)一的水平數(shù)組的構(gòu)建方法。初初級(jí)級(jí)三三：構(gòu)構(gòu)造造一一維維等等差差數(shù)數(shù)列列。某些時(shí)候，用戶可能需要從源數(shù)據(jù)中提取某些數(shù)據(jù)提供給其他函數(shù)計(jì)算，那么重新構(gòu)建數(shù)組將起重要的作用。

7、例：從右側(cè)自然數(shù)序列中取出奇數(shù)，即隔行取數(shù)應(yīng)用。取數(shù)公式：該公式主要根據(jù)最大值來(lái)重新構(gòu)建數(shù)組，由于INDIRECT函數(shù)生成引用時(shí)，行號(hào)不能包含小數(shù)，所以必須用舍入函數(shù)進(jìn)行處理。根據(jù)要求取奇數(shù)，則需要利用生成等差數(shù)列原理來(lái)重新構(gòu)造數(shù)組，結(jié)果為：1;3;5;7;9求和驗(yàn)證：2525這里主要為了闡述如何通過公式去構(gòu)建新數(shù)組，而對(duì)于此問題，另外一種方法更為靈活。公式：=SUMPRODUCT(MOD($K$22:$K$31,2),$K$22:$K$31)此解利用了奇數(shù)對(duì)2求余數(shù)為1的特性，將偶數(shù)進(jìn)行過濾，最后進(jìn)行求和得結(jié)果。其中：INDIRECT(1:10)用于產(chǎn)生110行的引用，使用ROW函數(shù)來(lái)生成該

8、區(qū)域的行號(hào)的垂直數(shù)組?；?TRANSPOSE(ROW(INDIRECT(1:10)1234567891055數(shù)據(jù)驗(yàn)證A1 11 1B2 21 1C3 31 1D4 41 1E5 51 1數(shù)據(jù) 奇數(shù)驗(yàn)證1112313514715916#N/A17#N/A1求和驗(yàn)證： 25258191101如果您是Excel初級(jí)用戶，那么對(duì)INDIRECT函數(shù)、ROW函數(shù)、COLUMN函數(shù)應(yīng)該不陌生，這幾個(gè)函數(shù)都是用戶常用于生成內(nèi)存數(shù)組的函數(shù)。該應(yīng)用在數(shù)組公式中經(jīng)常使用，如：生成1N的自然數(shù)序列、SMALL函數(shù)的第2個(gè)參數(shù)的自然數(shù)序列等等。有些用戶可能會(huì)使用ROW(1:10)來(lái)生成數(shù)組，但如果用戶在110行區(qū)域插

9、入或刪除行，將影響該結(jié)果的變化，因此建議用戶最好使用ROW(INDIRECT(1:10)來(lái)產(chǎn)生固定引用。某些時(shí)候，用戶可能需要從源數(shù)據(jù)中提取某些數(shù)據(jù)提供給其他函數(shù)計(jì)算，那么重新構(gòu)建數(shù)組將起重要的作用。該公式主要根據(jù)最大值來(lái)重新構(gòu)建數(shù)組，由于INDIRECT函數(shù)生成引用時(shí)，行號(hào)不能包含小數(shù)，所以必須用舍入函數(shù)進(jìn)行處理。=SUMPRODUCT(MOD($K$22:$K$31,2),$K$22:$K$31)注注意意：通常情況下，如果數(shù)據(jù)源為引用，可以使用OFFSET函數(shù)來(lái)生成數(shù)組；如果數(shù)據(jù)源為數(shù)組，則不能使用OFFSET函數(shù)，但可以通過LOOKUP函數(shù)來(lái)實(shí)現(xiàn)，而且LOOKUP函數(shù)使用更為靈活，在論壇

10、中Gvntw和Guoweicao78兩位版主的論帖更加詳細(xì)地進(jìn)行過介紹，這里不再贅述。鏈接一：http:/ 9A;A;A;B;B;B;C;C;C中中級(jí)級(jí)三三：等等比比例例構(gòu)構(gòu)造造一一維維循循環(huán)環(huán)數(shù)數(shù)組組。在有些應(yīng)用中，要求用戶對(duì)現(xiàn)有的數(shù)據(jù)序列進(jìn)行轉(zhuǎn)換，構(gòu)造新的序列供其他函數(shù)使用，循環(huán)數(shù)組就是其中之一。例：如在生成工資條的應(yīng)用中，每隔3行重復(fù)取得表頭字段，因此要求用戶根據(jù)自然數(shù)來(lái)重新構(gòu)建循環(huán)數(shù)組。公式：=MOD($K$35:$K$43-1,$J$35)+1此類公式比較簡(jiǎn)單，關(guān)鍵是利用MOD函數(shù)的原理，對(duì)原數(shù)據(jù)進(jìn)行取余數(shù)運(yùn)算，由于當(dāng)值為MOD函數(shù)整數(shù)倍時(shí)，取余結(jié)果為0，因此，就必須采用先（1）后（

11、1）的方式來(lái)實(shí)現(xiàn)。上面的公式是直接引用數(shù)據(jù)區(qū)域來(lái)生成的循環(huán)數(shù)組，在實(shí)際運(yùn)用中通常無(wú)數(shù)據(jù)源引用，則會(huì)讓用戶自己來(lái)進(jìn)行構(gòu)建源數(shù)據(jù)。將上面數(shù)組利用前面初級(jí)的方法，公式如下：源數(shù)組：DATA02=A;B;C公式：=LOOKUP(MOD(ROW(INDIRECT(1:&ROWS(Data02)*$J$35)-1,$J$35)+1,ROW(INDIRECT(1:&ROWS(Data02),Data02)結(jié)果驗(yàn)證：9 9中中級(jí)級(jí)四四：一一維維數(shù)數(shù)據(jù)據(jù)轉(zhuǎn)轉(zhuǎn)換換為為二二維維數(shù)數(shù)組組。這類需求一般不太常見，這里主要介紹一種數(shù)組構(gòu)造方法，使指定的原一維數(shù)組（或引用）轉(zhuǎn)換為二維數(shù)組。例：下面的公式將一維常量數(shù)組轉(zhuǎn)換為

12、二維數(shù)組。原始數(shù)組DATA03=A;B;C;D;E;F;G;H;I轉(zhuǎn)換后的二維內(nèi)存數(shù)組（3行N列）：=A,B,C;D,E,F;G,H,I二維數(shù)組行方向數(shù)組：=(ROW(INDIRECT(1:3)-1)*ROWS(DATA03)/3二維數(shù)組列方向數(shù)組：=COLUMN(INDIRECT(C1:C&ROWS(DATA02)/3,0)轉(zhuǎn)換公式：NEWData03=LOOKUP(ROW(INDIRECT(1:3)-1)*ROWS(DATA03)/3)+(COLUMN(INDIRECT(RC1:RC&ROWS(DATA03)/3,0),ROW(INDIRECT(1:&ROWS(DATA03),DATA03

13、)公式的關(guān)鍵是要重新構(gòu)造數(shù)組作為L(zhǎng)OOKUP的查找參數(shù)。其中：(ROW(INDIRECT(1:3)-1)*ROWS(DATA03)/3)+(COLUMN(INDIRECT(RC1:RC&ROWS(DATA03)/3,0)為構(gòu)造自然數(shù)序號(hào)數(shù)組的核心公式。公式結(jié)果為：0;3;6+1,2,3=1,2,3;4,5,6;7,8,9通過LOOKUP函數(shù)進(jìn)行查找，最終將一維數(shù)組按要求轉(zhuǎn)換為二維數(shù)組。1樓12樓數(shù)據(jù)數(shù)據(jù)驗(yàn)證10#VALUE!1088815#VALUE!1520#VALUE!2025#VALUE!2530#VALUE!3035#VALUE!3540#VALUE!40重復(fù)個(gè)數(shù)行號(hào)數(shù)據(jù)數(shù)據(jù)驗(yàn)證311

14、2131425262738393重復(fù)個(gè)數(shù)行號(hào)數(shù)據(jù)驗(yàn)證1驗(yàn)證2311A22B33C41A52B63C71A82B93C數(shù)組結(jié)果ABC注注意意：通常情況下，如果數(shù)據(jù)源為引用，可以使用OFFSET函數(shù)來(lái)生成數(shù)組；如果數(shù)據(jù)源為數(shù)組，則不能使用OFFSET函數(shù)，但可以通過LOOKUP函數(shù)來(lái)實(shí)現(xiàn)，=LOOKUP(COLUMN(INDIRECT(C1:C&ROUND(COUNTA(Data01)/2,0),0)*2-1,ROW(INDIRECT(1:&COUNTA(Data01),Data01)注意：最終生成的數(shù)組方向，取決于LOOKUP函數(shù)的第1個(gè)參數(shù)（構(gòu)造序列）的方向，而根據(jù)要求如何構(gòu)建序號(hào)數(shù)組，則是此

15、類問題的關(guān)鍵。=LOOKUP(ROW(INDIRECT(1:&ROWS(DATA02)*$J$21),(ROW(INDIRECT(1:&ROWS(DATA02)-1)*$J$21+1,DATA02)上面的公式是直接引用數(shù)據(jù)區(qū)域來(lái)生成的循環(huán)數(shù)組，在實(shí)際運(yùn)用中通常無(wú)數(shù)據(jù)源引用，則會(huì)讓用戶自己來(lái)進(jìn)行構(gòu)建源數(shù)據(jù)。=LOOKUP(MOD(ROW(INDIRECT(1:&ROWS(Data02)*$J$35)-1,$J$35)+1,ROW(INDIRECT(1:&ROWS(Data02),Data02)DEFGHI=LOOKUP(ROW(INDIRECT(1:3)-1)*ROWS(DATA03)/3)+(

16、COLUMN(INDIRECT(RC1:RC&ROWS(DATA03)/3,0),ROW(INDIRECT(1:&ROWS(DATA03),DATA03)其中：(ROW(INDIRECT(1:3)-1)*ROWS(DATA03)/3)+(COLUMN(INDIRECT(RC1:RC&ROWS(DATA03)/3,0)為構(gòu)造自然數(shù)序號(hào)數(shù)組的核心公式。在實(shí)際運(yùn)用中，對(duì)于數(shù)組（或引用）對(duì)各個(gè)元素進(jìn)行逐項(xiàng)累加，這項(xiàng)技術(shù)在構(gòu)建數(shù)組、數(shù)組變換中起著非常重要的作用，希望用戶盡量了解并掌握。通常情況下，對(duì)于是單元格引用的數(shù)值累加，我們可以借助COUNTIF函數(shù)和SUMIF函數(shù)來(lái)實(shí)現(xiàn)；而對(duì)于數(shù)據(jù)源是數(shù)組的數(shù)值累

17、加，一般使用MMULT函數(shù)來(lái)實(shí)現(xiàn)。高高級(jí)級(jí)一一：數(shù)數(shù)值值區(qū)區(qū)域域數(shù)數(shù)據(jù)據(jù)逐逐項(xiàng)項(xiàng)累累加加。對(duì)于一維（或二維）引用中對(duì)各個(gè)元素進(jìn)行逐項(xiàng)累加，這項(xiàng)技術(shù)主要利用三維引用來(lái)實(shí)現(xiàn)，對(duì)于三維引用的內(nèi)容在其他很多專題中都有介紹，這里只作簡(jiǎn)單分析。例：下面的公式將實(shí)現(xiàn)把右側(cè)數(shù)據(jù)源中各行數(shù)據(jù)個(gè)數(shù)和數(shù)值進(jìn)行累計(jì)，對(duì)于普通用戶一般使用輔助列來(lái)實(shí)現(xiàn)，而當(dāng)要求運(yùn)算結(jié)果為內(nèi)存數(shù)組時(shí)，就需要使用其他方法來(lái)實(shí)現(xiàn)。個(gè)數(shù)累計(jì)：=COUNTIF(OFFSET($I$9:$K$9,ROW(INDIRECT(1:&COUNTA($H$9:$H$11),)型號(hào)1數(shù)值累計(jì)：=SUMIF(OFFSET($I$9:$K$9,ROW(INDIR

18、ECT(1:&COUNTA($H$9:$H$11),)產(chǎn)品190此類累計(jì)公式主要是使用OFFSET函數(shù)的行高參數(shù)中利用初級(jí)二的方法來(lái)構(gòu)建數(shù)組，產(chǎn)品290再利用OFFSET函數(shù)來(lái)產(chǎn)生三維引用，最后由COUNTIF函數(shù)和SUMIF函數(shù)來(lái)進(jìn)行統(tǒng)計(jì)。產(chǎn)品3這項(xiàng)技術(shù)允許用戶在一維（二維）區(qū)域中進(jìn)行統(tǒng)計(jì)，原理相同。高高級(jí)級(jí)二二：一一維維數(shù)數(shù)組組數(shù)數(shù)值值累累加加。如果用戶的數(shù)據(jù)源是常量數(shù)組（或公式運(yùn)算后的內(nèi)存數(shù)組），需要將各個(gè)數(shù)值進(jìn)行逐項(xiàng)累加，則需要使用下面的公式。例：已知在上例的源數(shù)據(jù)中，用戶已通過COUNTIF函數(shù)取得各行的數(shù)據(jù)個(gè)數(shù)為：2;3;2,下面的公式將實(shí)現(xiàn)將各個(gè)數(shù)值進(jìn)行逐項(xiàng)累加得出新數(shù)組：2;5;7。內(nèi)存數(shù)組：Data04=COUNTIF(OFFSET($I$8:$K$8,ROW(INDIRECT(