EXCEL交叉复制方法.doc

EXCEL交叉复制方法在数据计算应用过程中，有时候需要将两组数据合并成一组数据。如下图所示：左边的数字和顶上的字母都是EXCEL的行列号。ABC1数据A数据B数据合成21源数据存储的起始位置，用EXCEL描述为A221334245635784691057111268131479151681017189111920101221221113232412142526131527281416293015171618171918201921202221232224232524262527262827292830293130 在这个表格中，用一个处理公式可以解决该问题： C1= INDEX($A$2:$B$16, (ROW()-2)/2+1,MOD(ROW()-2),2)+1)，然后往下拉即可。下面对表达式进行简单说明：在这种处理过程中，我们需要用到以下EXCEL函数。ROW()，这个函数是用来返回当前单元格所有的行号。也就是上面表格左边的数字MOD()，这是个取余数的除法（模除）。MOD(3,3)结果是0，MOD(4,3)结果为1，MOD（5，3）结果是2.Index()，用于返回表格或区域中的数值或对数值的引用。在这里，我们使用返回区域中的数值。INDEX（数组，行号，列号）。在这里，数据是用户自行定义的。用户把一个区域内的数据定义成一个数组。如上图所示。$A$2:$B$16这就是一个自定义的区域。从A2位置到B16位置的数据被我们自行定义成一个数组。形成的数组表格如下：ABC1数据A数据B数组行号21源数据存储的起始位置，用EXCEL描述为A2,做为数组而言,当前地址是：CARRY00总大小是：CARRY15221334245635784691057111268131479151681017189111920101221221113232412142526131527281416293015通过表格可以清楚看到，我们把A2到B16这个区域定义成了一个二维数组。用数组名定义为：CARRY【15】【2】。这个数组中，15表示行的总个数，2表是列的总个数。在EXCEL中的表示方法为：A2:B16。为保证计算不会出错。我们采用绝对地址的方法来定义这组数据，写成：$A$2:$B$16。注意：这样子写的目的是为了保证下拉后，不会出现数据错误。如果不采用绝对地址的方法。下拉的时候，可以看到公式会发生变化。随着行号的变化。A2也会发生变化。A3,A4这样一直往下走。结果输出的数据自然也就错了。通过上述说明，我们再看看具体的公式。C1= INDEX($A$2:$B$16, (ROW()-2)/2+1,MOD(ROW()-2),2)+1)式中，$A$2:$B$16是为了定义数组区域。(ROW()-2)/2+1： 首先，通过ROW()取出当前的行号。然后，-2则是因为当前行号不是0开始。对于数组的计算来说，数据地址是从0开始的。同理，假设当前的数据行号在23号，那么这个公式中的-2就要变成-23，以此保证数组地址从零开始。接下来：/2的作用是数组为2列。也就是说每一行有2个数据。如果一行有3个数据，定义数组时的数据决定了，例：CARRY【15】【3】。那这个时候，就不是/2，而是/3.最后：+1，由于EXCEL的位置不是从0开始，而是从1开始。所以，必须加1.再来看公式：MOD(ROW()-2),2)+1首先，通过ROW()取出当前的行号。然后，MOD(行号,2)。由于数组是2列，所以，计算时，必须把第2列数据转移到以行为中心的数据上来。行号模除列总个数。可以计算出当前行所对应的列的位置。如果一行有3个数据，定义数组时的数据决定了，例：CARRY【15】【3】。那这个时候，就不是MOD(行号,2)，而是MOD(行号,3)。最后：+1，还是老问题，EXCEL的地址是从1开始。而不是数组地址从0开始。所有必须加1.总成：INDEX(数组,行号,列号)，把数据从EXCEL数组中取出来。然后填充到指定的位置。下面以3列数组做个示范：公式：D2=INDEX($A$2:$C$9,(ROW()-2)/3+1,MOD(ROW()-2),3)+1)ABCD1数据A数据B数据C数据合成21源数据存储的起始位置，用EXCEL描述为A2对数组而言：当前地址为：CARRY00总大小为：CARRY8323134562478935101112