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

2、面有很重要的作用。初初級級一一：生生成成自自然然數數序序號號的的垂垂直直（水水平平）數數組組。初初級級二二：根根據據源源數數組組生生成成對對應應的的數數據據序序號號。初初級級三三：構構造造一一維維等等差差數數列列。中中級級一一：一一維維數數組組取取得得子子數數組組。中中級級二二：等等長長度度變變換換一一維維數數組組。中中級級三三：等等比比例例構構造造一一維維循循環環數數組組。中中級級四四：一一維維數數據據轉轉換換為為二二維維數數組組。高高級級一一：數數值值區區域域數數據據逐逐項項累累加加。高高級級二二：一一維維數數組組數數值值累累加加。高高級級三三：不不固固定定長長度度擴擴展展一一維維數數組組

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

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

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

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

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

8、區域的行號的垂直數組。或=TRANSPOSE(ROW(INDIRECT(1:10)1234567891055數據驗證A1 11 1B2 21 1C3 31 1D4 41 1E5 51 1數據 奇數驗證1112313514715916#N/A17#N/A1求和驗證： 25258191101如果您是Excel初級用戶，那么對INDIRECT函數、ROW函數、COLUMN函數應該不陌生，這幾個函數都是用戶常用于生成內存數組的函數。該應用在數組公式中經常使用，如：生成1N的自然數序列、SMALL函數的第2個參數的自然數序列等等。有些用戶可能會使用ROW(1:10)來生成數組，但如果用戶在110行區域插

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

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

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

12、二維數組。原始數組DATA03=A;B;C;D;E;F;G;H;I轉換后的二維內存數組（3行N列）：=A,B,C;D,E,F;G,H,I二維數組行方向數組：=(ROW(INDIRECT(1:3)-1)*ROWS(DATA03)/3二維數組列方向數組：=COLUMN(INDIRECT(C1:C&ROWS(DATA02)/3,0)轉換公式：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、)公式的關鍵是要重新構造數組作為LOOKUP的查找參數。其中：(ROW(INDIRECT(1:3)-1)*ROWS(DATA03)/3)+(COLUMN(INDIRECT(RC1:RC&ROWS(DATA03)/3,0)為構造自然數序號數組的核心公式。公式結果為：0;3;6+1,2,3=1,2,3;4,5,6;7,8,9通過LOOKUP函數進行查找，最終將一維數組按要求轉換為二維數組。1樓12樓數據數據驗證10#VALUE!1088815#VALUE!1520#VALUE!2025#VALUE!2530#VALUE!3035#VALUE!3540#VALUE!40重復個數行號數據數據驗證311

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

15、類問題的關鍵。=LOOKUP(ROW(INDIRECT(1:&ROWS(DATA02)*$J$21),(ROW(INDIRECT(1:&ROWS(DATA02)-1)*$J$21+1,DATA02)上面的公式是直接引用數據區域來生成的循環數組，在實際運用中通常無數據源引用，則會讓用戶自己來進行構建源數據。=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)為構造自然數序號數組的核心公式。在實際運用中，對于數組（或引用）對各個元素進行逐項累加，這項技術在構建數組、數組變換中起著非常重要的作用，希望用戶盡量了解并掌握。通常情況下，對于是單元格引用的數值累加，我們可以借助COUNTIF函數和SUMIF函數來實現；而對于數據源是數組的數值累

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

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