内存数组之构建数组应用_ByGdliyy

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(