EXCEL交叉复制方法

EXCEL交叉复制方法

在数据计算应用过程中，有时候需要将两组数据合并成一组数据。如下图所示：左边的数字和顶上的字母都是EXCEL的行列号。

A B C

1 数据A 数据B 数据合成

2 1 2 1

3 3

4 2

4 5 6 3

5 7 8 4

6 9 10 5

7 11 12 6

8 13 14 7

9 15 16 8

10 17 18 9

11 19 20 10

12 21 22 11

13 23 24 12

14 25 26 13

15 27 28 14

16 29 30 15

17 16

18 17

19 18

20 19

21 20

22 21

23 22

24 23

25 24

26 25

27 26

28 27

29 28

30 29

31 30

在这个表格中，用一个处理公式可以解决该问题：

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位置的数据被我们自行定义成一个数组。形成的数组表格如下：

通过表格可以清楚看到，我们把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)

A B C D

数据合

1 数据A 数据B 数据C

1

2

3

4

5

6

7

8

9

11 10

12 11

13 12

14 13

15 14

16 15

17 16

18 17

19 18

20 19

21 20

22 21

23 22

24 23

25 24