【原】练习题057：如何用公式将多列的矩形区域转置为二列？

一、题目要求

---

题目：

请编制公式将A1:H3单元格区域转换为J2:K13区域

要求：

J列K列用一个公式横向和纵向下拉填充生成

二、解题思路

---

。

这个题看起来似乎很难，是一个难以完成的任务。

实际上并太难。我们一步步分析：

1、分析要引用的数据的规律

如果不用公式，而是直接用单元格链接，J列、K列的公式应该是这样的：

我们将上图中所引用的单元格地址，用行号列号来表示，

J列要引用的项目单元格：

A1即为第1行第1列，

C1即为第1行第3列

E1即为第1行第5列

......

K列要引用的金额单元格：

B1即为第1行第2列

D1即为第1行第4列

F1即为第1行第6列

......

将所引用单元格的行号列号依次罗列，即为N列和O列、N列和P列所示。

我们分析一下N列O列P列数据有无规律，可以看出，规律很明显：

行号：每重复四个然后递增1；

列号分别是在1、3、5、7循环重复（或在2、4、6、8循环重复）

如果我们能构造出这样的序列，然后用INDEX函数来引用就行了。

INDEX函数就是专门干这活的：取第几行第几列交叉点的值。

比如要取A1:H3单元格区域的C2单元格的"F"，就是取A1:H3区域的第2行第3列，公式为：

=INDEX(A1:H3,2,3)

要取F3单元格的11，就是取A1:H3区域的第3行第6列，其公式为：

=INDEX(A1:H3,3,6)

关键是用什么公式、如何构造出N列、O列、P列三列中的序列？

2、构造序列

这个就要用到高中的数学知识了，高中学了那么多数学，现在终于可以派上用场了。下面让我们穿越到高中数学课堂：

数学老师在讲台上使劲敲黑板，大声喊到：同学们，安静安静，开始做题了，请在十分钟之内提交答案，先做完的先下课：

有一个从1到12的原始序列，请根据此序列，找到一算式，分别计算出下面三个序列

序列一：

1、1、1、1、2、2、2、2、3、3、3、3、4、4......

序列二：

1、3、5、7、1、3、5、7、1、3、5、7、1、3......

序列三：

2、4、6、8、2、4、6、8、2、4、6、8、2、4......

（1）生成序列一：

序列一是每个数字重复四次一递增，那么我们可以将其除以4。为了让其精确从1开始重复四次，因而，将原始序列加3，然后除4再取整数。用Excel公式表示

即为=INT((n+3)/4)

要让公式往下拖动时依次递增，可以用ROW函数直接生成：

=INT((ROW(A1)+3)/4)

注：ROW函数的作用是取行号，ROW（A1）即取A1单元格的行号，即1，公式往下填充时，由于使用的是相对引用，会自动变为

=INT((ROW(A2)+3)/4)

=INT((ROW(A3)+3)/4)

（2）生成序列二

下面我们来看如何生成序列二：

序列二1、3、5、7、1、3、5、7、1、3、5、7、是循环序列。看到这个循环的结构，我们感觉它和取余数有点接近。比如，将1到8分别除以4，取余数，其余数分别为：

1、2、3、0、1、2、3、0.......

因而我们应该首先想到取余数。我们先将原序列减1，除以4，然后再取余数。也就是将0到7，分别除以4，取余数。其余数的序列为：

0、1、2、3、0、1、2、3

然后将上面的序列乘以2，再加1

即，=2*N+1

序列就变成了：

1、3、5、7、1、3、5、7

将上面的过程写成Excel公式，就是：

=2*MOD(N-1,4)+1

将N换成ROW函数，公式为：

=2*MOD(ROW（A1）-1,4)+1

（3）生成序列三

序列三的公式参考序列二的公式，不赘述。

三、编制公式

---

先来看前面已经提到的简单傻瓜化的公式：

=INDEX($A$1:$H$3,x,y)

将序列一的公式INT((ROW(A1)+3)/4)代入到上面公式的x

将序列二的公式2*MOD(ROW（A1）-1,4)+1代入到上面公式的y，

代入后，公式为：

=INDEX($A$1:$H$3,INT((ROW(A1)+3)/4),2*MOD(ROW(A1)-1,4)+1)

此公式往下拖动填充没问题，但是往右填充，无法自动引用相应的数字呢，要手工修改为：

=INDEX($A$1:$H$3,INT((ROW(A1)+3)/4),2*MOD(ROW(A1)-1,4)+2)

为了能用一个公式往下和往右拖动完成数据的引用，将公式修改完善为：

=INDEX($A$1:$H$3,INT((ROW(A1)+3)/4),2*MOD(ROW(A1)-1,4)+COLUMN()-9)

说明：

COLUMN()表示取公式所在单元格的列号。在J列其计算结果为10，在K列其计算结果为11。

四、知识点回顾

---

本案例最关键的知识点就是序列的构造：

1、重复N个递增一的序列（1、1、1、1、2、2、2、2、3、3、3、3）的构造方法

如果构造1、1、1、1、2、2、2、2、3、3、3、3这种每重复N个递增一的序列，可以将其除以N，然后取整，公式为：

=INT((ROW(A1)+N-1)/N)

2、N个连续数字的重复序列（1、2、3、4、1、2、3、4、1、2、3、4）的构造方法

这种序列用取余数的方法来构造，其公式为：

=MOD(ROW(A1)-1,N)+1

－－－－－－－－－－－－－－－－－－－－