excel数据拆分:将单列拆分成多列的几种方法

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

浪费纸不说,还很丑,这样打印的表格你真的敢拿给老板看吗?

对于这样的数据,就需要把一列平均分配到多列,通常有两种方法来实现,先来看看操作的方法:

一、操作法将一列数据变成多列 步骤1:先设计好需要拆分为几列,然后将标题手动做好;

本例中计划拆分8列。

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

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

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

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

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

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

结论:

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

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

3、但是操作法也有缺点,如果数据不断增加,那么每次都要这样操作也挺麻烦,因此还是需要了解一下公式的做法。

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

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

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

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

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

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

MOD(COLUMN(A1)+7,8)+ROW(A1)*8-7。

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

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

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

结论:

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

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

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

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

数据源有三列,我们希望变成九列(三组),这样打印起来就非常合理。

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

三、对于多列数据源的处理方法 公式为:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=MOD(COLUMN(B1),2)

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

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

通过以上解释,基本可以理解如何根据实际需要去调整公式的对应内容就可以了。要想彻底弄明白这里的奥妙,数列的构造方法是绕不过去的一道坎。

让我们一起加油学习Excel中那些有趣又实用的技能吧!

****部落窝教育-excel单列拆分成多列****

原创:老菜鸟/部落窝教育

(0)

相关推荐

  • 奇怪的查找

    excelperfect 有时候,用户组织的数据不便于Excel的操作,让我们不得不费一番周折才能得出想要的结果. 这里是chandoo.org上的一个示例,日期不是放在同一行或同一列中,而是间隔放在 ...

  • 再见Ctrl+C!单列数据转换成多列数据,用它只需30秒!

    送你200篇独家Excel精华教程 全套Excel视频,扫码观看! 每天一点小技能 职场打怪不得怂 编按:别再Ctrl+C了!简单的工作做一万遍也会让人心力交瘁!在办公中,我们常常会遇到把单列数据变成 ...

  • 利用VBA拆分单个工作簿成多个工作簿的方法

    提示:在工作中经常要将单个工作簿拆分成多个工作簿,以便发送给其他人,老方法都是用移动或复制工作表,另存为单个工作簿,这个方法目前很多人都在用,如果你想省时省力的话不访也来了解一下方法,说白了也就是套用 ...

  • Excel二维表转换成一维表(2种方法)

    今天大年初四,春节假期还剩三天了,每逢佳节胖三斤,亲们可要注意控制饮食了,要不然春节后无脸见人哟.闲话少说,今日分享如下. 在做数据处理的时候,有的时候为了处理方便我们需要将二维的数据表处理成一维的数 ...

  • 批量汇总Excel数据 | 只要表中的某几列,怎么办?

    小勤:我要汇总的一堆表里,每个表的情况不太一样,但有些列是每个表都有的,我就是要汇总这些列,怎么办啊? 大海:其实,几乎所有特殊的情况处理,都可以在解析出表数据之后,展开合并数据之前按需要进行处理. ...

  • Excel统计满足条件的不重复值个数的8种方法

    问题很常见,但是对新手来说又比较棘手,所以我们来一期,系统讲讲,个人我个人的知识储备,大概写了8种,涵盖Excel的方方面面!最后一种也满足一下大家对于Python的好奇! 友情提醒:应该是目前你所能 ...

  • C# 将多个图片合并成TIFF文件的两种方法

    dotNET跨平台 今天 以下文章来源于WPF UI ,作者Gxy WPF UIWPF UI 设计,WPF教程,MVVM,C#程序设计~ 最近需要用到TIF格式的文件,研究了一段时间,终于有点结果了, ...

  • Excel中通过VLOOKUP函数实现多条件查询的2种方法,简单高效!

    Excel中通过VLOOKUP函数实现多条件查询的2种方法,简单高效!

  • 在Excel中截取字段中的字符,我有3种方法供参考

    在Excel中截取字段中的字符,我有3种方法供参考.在平时的工作中,我们经常会遇到要截取某一个字段中的数字或者是文字.数据量小就无所谓方法,但数据量多的话,方法必须有讲究,同样的工作量,虽然最终结果相 ...

  • excel隐藏工作表界面只显示用户窗体的三种方法

    内容提要:本文介绍excel中通过VBA代码来隐藏工作簿只显示用户窗体的三种情况和方法. 有时候当我们打开excel,没有看到常规的显示工作表界面,而是只显示了用户窗体.这是怎么实现的呢?我们可以通过 ...