Excel教程

Excel函数
Excel技巧

【excel函数】如何利用indirect函数快速将一列转为多行多列排版

当前位置:网站首页 > Excel教程

【excel函数】如何利用indirect函数快速将一列转为多行多列排版

* 来源: * 作者: admin * 发表时间: 2019-09-11 15:50:46 * 浏览: 96

HI,大家好哈。

前几天我们有聊了如何将多行多列转一列的话题,今天就顺势来聊聊如何将一列转多行多列

因为,一份表格,如果里面只有一列,不过有一百多行,甚至几百行,

这样子排版,

一方面,不够一目了然,看的时候,还得按住鼠标不停地往下拉;

另一方面,如果万一要打印出来的话,估计得被老板一掌拍扁,因为那得用好几张纸。

因此,我们得寻思着,把它转换为更适合查阅,以及更适合打印的排版方式,即多行多列排版。

当然,如果你不嫌麻烦的话,可以手动复制粘贴啰。

但是,花费的时间估计有点长,不是所有人都有那个耐心哦。

今天,就让我告诉你,如何利用indirect函数快速将一列转为多行多列排版

举例说明:

如下图所示,是一份各行业名称表格:

「Excel技巧」如何利用indirect函数快速将一列转为多行多列排版


表格里内容,除了标题行外,内容是1列60行。

现我们准备把它转为6列。

具体方法:

首先,要写公式前,我们得先找下规律。

1列60行,最后要转成6列,转换后的表格我们先用单元格地址表示出来,如下:

「Excel技巧」如何利用indirect函数快速将一列转为多行多列排版


可以看到,这是一个横向是差为1的等差序列,纵向是差为6的等差数列。

规律有了,知道要用的函数了,就可以开始写公式了。

在C2单元格里输入公式:

=INDIRECT("A"&6*ROW(A1)-5+COLUMN(A1))&""

然后公式往右拉填充,再整体往下拉填充,直到出现空白为止。

「Excel技巧」如何利用indirect函数快速将一列转为多行多列排版


公式解析:

公式里的:6*ROW(A1)-5+COLUMN(A1),

这部分用来计算得出来引用单元格的行值。

ROW(A1):即返回A1的行号1;

COLUMN(A1):即返回A1的列值1。

因此,6*ROW(A1)-5+COLUMN(A1)即是6*1-5+1=2。

当公式从左往右填充,

同一行返回的行号不变,但列值会发生改变,公式里的COLUMN(A1),从左往右依次为COLUMN(A1)、COLUMN(B1)、COLUMN(C1) ……,得到的值分别为:1、2、3……。

当公式从上往下填充,

同一列返回的列值不变,但行号会发生改变,公式里的ROW(A1),从上往下依次为ROW(A1)、ROW(A2)、ROW(A3)……,得到的值分别为1、2、3……

大家可以看到,如果在单元格里写公式:=6*ROW(A1)-5+COLUMN(A1),公式按6列去填充,可以看到表格里公式计算得出来的值,如下:

「Excel技巧」如何利用indirect函数快速将一列转为多行多列排版


2、用连接符“&”将公式6*ROW(A1)-5+COLUMN(A1)计算得出来的值与字符“A”连接,得到单元格地址。

到这里就很明显了,

如果在单元格里填充公式:="A"&6*ROW(A1)-5+COLUMN(A1),按6列填充,得到如下:

「Excel技巧」如何利用indirect函数快速将一列转为多行多列排版


3、得到单元格地址后,再用indirect函数返回文本字符串所指定的引用,得到对应单元格的内容,即公式为:

=INDIRECT("A"&6*ROW(A1)-5+COLUMN(A1))。

补充说明:

indirect函数

含义:返回文本字符串所指定的引用;

语法格式:=INDIRECT(引用区域,引用格式)

引用格式可以为TRUE 或省略。

4、这时候,你肯定会好奇,在公式的最后为什么加上【&""】?这是为了让数据不足时,公式返回出现的0值转成空值显示。

所以,一列转6列,最后的公式就是:

=INDIRECT("A"&6*ROW(A1)-5+COLUMN(A1))&""

如果我想要调整为一列转7列,那公式就是:

=INDIRECT("A"&7*ROW(A1)-6+COLUMN(A1))&""

公式依此类堆下去。

上面说了辣么多内容,即使不理解公式的意思,但看到这个公式,大家应该也懂怎么套用公式了哈。

当然,套用公式只是权宜之计,我们还是得尽可能去理解公式其中的精髓,以后才能对公式运用自如。