内存数组之构建数组应用-ByGdliyy.xls

1、内内存存数数组组之之重重新新构构建建数数组组应应用用 通常情况下，用户在内存数组的中级应用中时常需要通过数组公式来构造内存数组，如：生成110的自然数序号应用。 由于此类应用在数组公式中运用特别广泛，而且许多初级用户对这类应用都非常熟悉，但在实际工作中如何去运用，则是必须面对 的一个问题。所以，在这里将由浅入深地对以下几种情况的数组构建进行分析和总结。 如果您是Excel初级用户，那么对INDIRECT函数、ROW函数、COLUMN函数应该不陌生，这几个函数都是用户常用于生成内存数组的函数。 如果您是常用数组公式的中级用户，那对LOOKUP函数、MMULT函数可能比较熟悉，因为这两个函数在生成

2、内存数组方面有很重要的作用。 初初级级一一：生生成成自自然然数数序序号号的的垂垂直直（水水平平）数数组组。 初初级级二二：根根据据源源数数组组生生成成对对应应的的数数据据序序号号。 初初级级三三：构构造造一一维维等等差差数数列列。 中中级级一一：一一维维数数组组取取得得子子数数组组。 中中级级二二：等等长长度度变变换换一一维维数数组组。 中中级级三三：等等比比例例构构造造一一维维循循环环数数组组。 中中级级四四：一一维维数数据据转转换换为为二二维维数数组组。 高高级级一一：数数值值区区域域数数据据逐逐项项累累加加。 高高级级二二：一一维维数数组组数数值值累累加加。 高高级级三三：不不固固定定长

3、长度度扩扩展展一一维维数数组组。 高高级级四四：不不定定长长使使用用循循环环序序列列扩扩展展数数组组。 如如果果大大家家对对后后续续的的解解法法公公式式看看不不明明白白，建建议议使使用用“工工具具公公式式审审核核公公式式求求值值”来来查查看看公公式式的的内内部部运运算算过过程程，以以了了解解各各技技巧巧中中的的解解法法思思路路。 By Gdliyy E 通常情况下，用户在内存数组的中级应用中时常需要通过数组公式来构造内存数组，如：生成110的自然数序号应用。 由于此类应用在数组公式中运用特别广泛，而且许多初级用户对这类应用都非常熟悉，但在实际工作中如何去运用，则是必须面对 如果您是Excel初

4、级用户，那么对INDIRECT函数、ROW函数、COLUMN函数应该不陌生，这几个函数都是用户常用于生成内存数组的函数。 如果您是常用数组公式的中级用户，那对LOOKUP函数、MMULT函数可能比较熟悉，因为这两个函数在生成内存数组方面有很重要的作用。 如如果果大大家家对对后后续续的的解解法法公公式式看看不不明明白白，建建议议使使用用“工工具具公公式式审审核核公公式式求求值值”来来查查看看公公式式的的内内部部运运算算过过程程，以以了了解解各各技技巧巧中中的的解解法法思思路路。 如果您是Excel初级用户，那么对INDIRECT函数、ROW函数、COLUMN函数应该不陌生，这几个函数都是用户常用

5、于生成内存数组的函数。 初初级级一一：生生成成自自然然数数序序号号的的垂垂直直（水水平平）数数组组。 该应用在数组公式中经常使用，如：生成1N的自然数序列、SMALL函数的第2个参数的自然数序列等等。 例：下面公式将生成自然数110的垂直数组。 公式：=ROW(INDIRECT(“1:10“) 有些用户可能会使用ROW(1:10)来生成数组，但如果用户在110行区域插入或删除行，将影响该结果的变化，因此建议用户最好使用ROW(INDIRECT(“1:10“)来产生固定引用。 求和验证：5555其中：INDIRECT(“1:10“)用于产生110行的引用，使用ROW函数来生成该区域的行号的垂直数

6、组。 同理，下面的公式将生成110的水平数组。 公式：=COLUMN(INDIRECT(“C1:C10“,0)或=TRANSPOSE(ROW(INDIRECT(“1:10“) 求和验证：5555123456 注意：使用INDIRECT函数来生成R1C1引用时，第2个参数必须为FALSE（或0），否则公式返回错误。 初初级级二二：根根据据源源数数组组生生成成对对应应的的数数据据序序号号。 前面介绍的是直接生成数组，这个应用中主要介绍通过数据的具体数量来动态生成序号数组。 例：根据右侧数据区域生成动态序号的垂直数组。 公式：=ROW(INDIRECT(“1:“3;5;7;9 求和验证：2525 这

7、里主要为了阐述如何通过公式去构建新数组，而对于此问题，另外一种方法更为灵活。 公式：=SUMPRODUCT(MOD($K$22:$K$31,2),$K$22:$K$31)求和验证：2525 此解利用了奇数对2求余数为1的特性，将偶数进行过滤，最后进行求和得结果。 78910 数据验证 A1 1 B2 2 C3 3 D4 4 E5 5 数据奇数验证 11 23 35 47 59 6#N/A 7#N/A 8 9 10 如果您是Excel初级用户，那么对INDIRECT函数、ROW函数、COLUMN函数应该不陌生，这几个函数都是用户常用于生成内存数组的函数。 有些用户可能会使用ROW(1:10)来生

8、成数组，但如果用户在110行区域插入或删除行，将影响该结果的变化，因此建议用户最好使用ROW(INDIRECT(“1:10“)来产生固定引用。 其中：INDIRECT(“1:10“)用于产生110行的引用，使用ROW函数来生成该区域的行号的垂直数组。 该公式主要根据最大值来重新构建数组，由于INDIRECT函数生成引用时，行号不能包含小数，所以必须用舍入函数进行处理。 注注意意：通常情况下，如果数据源为引用，可以使用OFFSET函数来生成数组；如果数据源为数组，则不能使用OFFSET函数，但可以通过LOOKUP函数来实现， 而且LOOKUP函数使用更为灵活，在论坛中Gvntw和Guoweica

9、o78两位版主的论帖更加详细地进行过介绍，这里不再赘述。 链接一： 公式：=LOOKUP(COLUMN(INDIRECT(“C1:C“1;1;2;2;2;3;3;3;4;4;4N;N;N这样的数组。 下而将介绍利用公式来生成此类变换数组的用法： 例：下面的公式将按指定的数据重复次数来动态变换行号数据。得结果：1;1;1;2;2;2;3;3;3 公式：=ROUNDUP($K$21:$K$29/$J$21,0) 该公式比较简单，主要利用舍入运算，按要求构建等长度重复的序列，在生成工资条应用中很常用。 例：下面的公式将源数组中每个元素按指定要求进行扩展，得结果：“A“;“A“;“A“;“B“;“B“

10、;“B“;“C“;“C“;“C“ 源数组：Data02=“A“;“B“;“C“ 构建序列：=ROW(INDIRECT(“1:“A“;“A“;“B“;“B“;“B“;“C“;“C“;“C“ 中中级级三三：等等比比例例构构造造一一维维循循环环数数组组。 在有些应用中，要求用户对现有的数据序列进行转换，构造新的序列供其他函数使用，循环数组就是其中之一。 例：如在生成工资条的应用中，每隔3行重复取得表头字段，因此要求用户根据自然数来重新构建循环数组。 公式：=MOD($K$35:$K$43-1,$J$35)+1 此类公式比较简单，关键是利用MOD函数的原理，对原数据进行取余数运算，由于当值为MOD函数

11、整数倍时，取余结果为0， 因此，就必须采用先（1）后（1）的方式来实现。 上面的公式是直接引用数据区域来生成的循环数组，在实际运用中通常无数据源引用，则会让用户自己来进行构建源数据。 将上面数组利用前面初级的方法，公式如下： 源数组：DATA02=“A“;“B“;“C“ 公式：=LOOKUP(MOD(ROW(INDIRECT(“1:“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

12、)/3 二维数组列方向数组：=COLUMN(INDIRECT(“C1:C“3;6+1,2,3=1,2,3;4,5,6;7,8,9 通过LOOKUP函数进行查找，最终将一维数组按要求转换为二维数组。 1楼 12楼 数据数据验证 10#VALUE! 15#VALUE! 20#VALUE! 25#VALUE! 30#VALUE! 35#VALUE! 40#VALUE! 重复个数行号数据数据验证 311 21 31 42 52 62 73 83 93 重复个数行号数据验证1验证2 311A 22B 33C 41A 52B 63C 71A 82B 93C 数组结果 ABC 注注意意：通常情况下，如果数据

13、源为引用，可以使用OFFSET函数来生成数组；如果数据源为数组，则不能使用OFFSET函数，但可以通过LOOKUP函数来实现， =LOOKUP(COLUMN(INDIRECT(“C1:C“3;2,下面的公式将实现将各个数值进行逐项累加得出新数组：2;5;7。 内存数组：Data04=COUNTIF(OFFSET($I$8:$K$8,ROW(INDIRECT(“1:“2;3与数组1,2,3逐个比较），再将比较结果转换为数值后， 提供给MMULT矩阵函数将“二维数组”和DATA04生成的“一维数组”进行矩阵运算后，横向加总得出累计结果。 此项技术在对数组中的数值进行累加中应用非常广泛，论坛中的sh

14、uyee朋友对此深有体会，请见以下链接。 链接：制作特殊矩阵 内存数组：Data04=COUNTIF(OFFSET($I$8:$K$8,ROW(INDIRECT(“1:“2;3;1;2;3;1;2;3这样的序列？是的，本技巧即是对高级三的一种思路扩展解法介绍，以便让读者循序渐进地了解。 本技巧沿用高级二的两个内存数组名称进行实例介绍。 例：下面的公式将DATA04中的数值，按其中各个元素值个数使用循环序列进行排列，从而实现不定长扩展该一维数组，结果为：1;2;1;2;3;1;2。 内存数组：Data04=COUNTIF(OFFSET($I$8:$K$8,ROW(INDIRECT(“1:“3;6

15、以外，如果再构造出从分界点起从1开始得出循环序列，则是问题的关键所在。 分界点名称：Data05=MMULT(-(ROW(INDIRECT(“1:“3;2,下面的公式将实现将各个数值进行逐项累加得出新数组：2;5;7。 此解法主要利用了行序号与列序号的比较判断法（数组1;2;3与数组1,2,3逐个比较），再将比较结果转换为数值后， 注意：对单元格的累加，主要是利用COUNTIF函数和SUMIF函数的三维引用来进行；数组的累加，主要是利用MMULT根据行列号大小比较法来实现累加结果。 想必大家还记得“中级二”的等长度变换一维数组吧？这里主要介绍在实际应用中，如何根据不同的重复个数值来构建内存数组

16、。 例：下面的公式将DATA04中的数值，按其中各个元素值个数进行重复排列，从而实现不定长扩展该一维数组，结果为：2;2;3;3;3;2;2。 如果读者仔细观察结果数组，不难发现，该数组的分界点为1、3、6，那么如何来得出这个分界点的数组，就是本技巧的关键。 =MMULT(-(ROW(INDIRECT(“1:“2;3;1;2;3;1;2;3这样的序列？是的，本技巧即是对高级三的一种思路扩展解法介绍，以便让读者循序渐进地了解。 例：下面的公式将DATA04中的数值，按其中各个元素值个数使用循环序列进行排列，从而实现不定长扩展该一维数组，结果为：1;2;1;2;3;1;2。 即然此技巧是上一技巧的

17、扩展，那么除了得到分界点1;3;6以外，如果再构造出从分界点起从1开始得出循环序列，则是问题的关键所在。 =MMULT(-(ROW(INDIRECT(“1:“2;32;3。 RowNo=ROW(INDIRECT(“1:“2;3;4;5;6;7;8;9;10。 RecNum=MMULT(N(ROW(INDIRECT(“1:“5;7。 方方法法一一：自然序号累计数 公式解法：10 =T(OFFSET(INDIRECT(“sheet“0;0;0;0;5;5;7;7;731 COLUMN函数主要利用“初级二”技巧，用于根据各表数据最大值生成水平数组。41 公式结果：=1,2,3,4,551 上述两个数

18、组相加（注意方向不同），则得到10行5列的新数组。66 根据比较判断法，左、右两个公式结果对应比较，得出逻辑值数组。76 最后，根据MMULT函数的运算法则，将比较结果横向汇总得到D48:D57的循环序列。88 MMULT运算结果作为OFFSET函数的行偏移参数，最后得出要求的结果。98 108 最最后后特特别别提提醒醒：由由于于原原始始数数据据涉涉及及到到多多个个分分表表，因因此此无无法法使使用用LOOKUPLOOKUP函函数数来来取取得得结结果果，只只能能通通过过OFFSETOFFSET函函数数来来实实现现。 自然序号累计数最终序列公公式式结结果果数数据据验验证证 101A10 202B

19、303C 404D 50两者比较汇总5E 651F 752G 871H 972I 1073J #N/A 最终序列公公式式结结果果数数据据验验证证 1A10 2B 3C 4D 两者相减5E 1F 2G 1H 2I 3J #N/A 2345 2345 2345 2345 2345 78910 78910 9101112 9101112 9101112 =T(OFFSET(INDIRECT(“sheet“&MATCH(RowNo,RecNum+1)&“!A1“),MMULT(N(RowNo=LOOKUP(RowNo-1,RecNum)+COLUMN(INDIRECT(“c1:c“&MAX(SData),0),ROW(INDIRECT(“1:“&MAX(SData)0),) MMULT(N(RowNo=LOOKUP(RowNo-1,RecNum)+COLUMN(INDIRECT(“c1:c“&MAX(SData),0),ROW(INDIRECT(“1:“&MAX(SData)0) LOOKUP函数段+COLUMN函数段结果 人人员员姓姓名名 A B C D E 人人员员姓姓名名 F G 人人员员姓姓名名 H I J