实用Excel技巧分享:如何将一列分成多行多列

时间:2022-12-04
阅读:

有时候我们的表格里只有一列数据,如果需要打印出来的话,就是下面这样子的:

实用Excel技巧分享:如何将一列分成多行多列

浪费纸不说,还很丑。

对于这样的数据,就需要Excel一列分成多行多列,通常有两种方法来实现,先来看看操作的方法:

一、操作法将一长列数据分成多列

步骤1:先设计好需要拆分为几列,然后将标题手动做好;

实用Excel技巧分享:如何将一列分成多行多列

本例中计划拆分8列。

步骤2:在c2单元格输入a2,向右拉,Excel会自动为我们填充到a9。

实用Excel技巧分享:如何将一列分成多行多列

步骤3:在c3单元格输入a10,向右拉;因为第一行已经到a9了,所以这一行就是a10开始,一直填充到a17。

实用Excel技巧分享:如何将一列分成多行多列

步骤4:选中两行一起下拉,假设有160个数据,每行8个,那么下拉20行就够了;

实用Excel技巧分享:如何将一列分成多行多列

步骤5:按Ctrl +h组合键打开查找和替换,查找内容为a,替换内容为=a,如下图所示;

实用Excel技巧分享:如何将一列分成多行多列

点击全部替换,确定,完成操作。

实用Excel技巧分享:如何将一列分成多行多列

步骤6:选择性粘贴为值可删除公式,数据居中加边框,设置打印预览,效果正是我们需要的;

实用Excel技巧分享:如何将一列分成多行多列

结论:

1、如果不是每行8个,只需要调整第一次右拉的个数即可;

2、操作法的优势是容易上手,更适合新手使用;

3、但是操作法也有缺点,如果数据不断增加,那么每次都要这样操作也挺麻烦,因此还是需要了解一下使用公式来实现Excel一列分成多行多列。

二、公式法将一列数据变成多列

还是用这个例子来说明,通常将一列变成多列会用到OFFSET函数,我们先来看看公式是什么样子的:

=OFFSET($A$1,MOD(COLUMN(A1)+7,8)+ROW(A1)*8-7,)

实用Excel技巧分享:如何将一列分成多行多列

只需要将这个公式右拉下拉即可。

OFFSET这个函数根据偏移量来得到一个新的引用(单元格或者区域)一共有五个参数,格式为:

OFFSET(起始位置,行偏移量,列偏移量,高度,宽度)

本例中起始位置为A1单元格,因为数据源都在一列,并且都是单元格的引用,因此只需要确定行偏移量即可,可以看到公式里只用了两个参数,第二参数是MOD(COLUMN(A1)+7,8)+ROW(A1)*8-7

单独将这部分显示出来的效果是这样的:

实用Excel技巧分享:如何将一列分成多行多列

说通俗点,就是A1向下1行、2行……等等的单元格引用。

关于这部分需要一点数列构造的基础,不理解的话可以记住套路,想学习数列的构造方法可以留言,我们会针对如何在公式中构造数列来单独讲解一次。

结论:

1、如果不是每行8个,只需要将第二参数中的数字7、8做对应调整,例如每行是6个数据的话,公式修改为:=OFFSET($A$1,MOD(COLUMN(A1)+5,6)+ROW(A1)*6-5,)

实用Excel技巧分享:如何将一列分成多行多列

2、公式法需要使用者有一定的经验,当数据源发生变化时调整起来非常灵活;拓展性较强。

看到这里,估计有些伙伴会想:如果数据源不是一列,而是两列或者三列,该怎么办?

实际上,两列或者三列的数据源在平时工作中更为常见,例如下面这种情况:

实用Excel技巧分享:如何将一列分成多行多列

数据源有三列,我们希望变成九列(三组),这样打印起来就非常合理。上面介绍的Excel如何将一列数据分成多列,你都学会了吗?

对于这种问题,使用方法1介绍的技巧来实现就比较困难了,还是利用OFFSET函数来处理。

三、对于多列数据源的处理方法

公式为:=OFFSET($A$1,INT(COLUMN(C1)/3)+ROW(A1)*3-3,MOD(COLUMN(C1),3))

实用Excel技巧分享:如何将一列分成多行多列

因为数据源不在同一列了,所以行偏移量和列偏移量都要考虑,因此用到了三个参数,第一参数起始位置还是A1;

第二参数INT(COLUMN(C1)/3)+ROW(A1)*3-3代表引用数据相对于起始位置A1的行偏移量,单独看的话效果为:

实用Excel技巧分享:如何将一列分成多行多列

可以非常清楚的看到变化规律;

第三参数MOD(COLUMN(C1),3)代表引用数据相对于起始位置A1的列偏移量,单独看的效果为:

实用Excel技巧分享:如何将一列分成多行多列

对于这种规律的掌控能力,决定了OFFSET这个函数的使用水平,也正是这个原因使得OFFSET函数令很多初学者摸不着头脑,学习起来始终不得要领。

今天我们的主要目的并不是学习OFFSET函数,只是学习这个函数的一个应用,就本例来说,记下公式的套路,能够根据自己实际的问题去修改公式后解决问题就足够了。

学好OFFSET的关键在于对第二和第三两个参数的构造。

比如上面的问题,数据源是三列,希望变成四组(十二列),第二参数需要变成INT(COLUMN(C1)/3)+ROW(A1)*4-4

实用Excel技巧分享:如何将一列分成多行多列

对箭头所指的两个地方进行修改。

而第三参数=MOD(COLUMN(C1),3)则无需调整;以此类推,只要数据源的列数不变,只需要调整上面提到的两个位置就行了。

当数据源变成2列的时候,第二参数需要修改为INT(COLUMN(B1)/2)+ROW(A1)*3-3

实用Excel技巧分享:如何将一列分成多行多列

还是修改两个位置,C改为B,3改为2,

同时第三参数列偏移量也要修改:

=MOD(COLUMN(B1),2)

实用Excel技巧分享:如何将一列分成多行多列

也是修改两个地方,C改为B,3改为2。

以此类推,如果数据源变成4列,则分别修改B为D,2为4。

通过以上解释,基本可以理解如何根据实际需要去调整公式的对应内容就可以了。要想彻底弄明白这里的奥妙,数列的构造方法是绕不过去的一道坎。希望本文的Excel表格一列变多列的技巧能对你工作有帮助。

返回顶部
顶部