如何将一列数据变成多列,excel把一列变成多列
有时我们的Excel表格中只有一列数据。如果我们需要把它打印出来,它看起来像这样:
别浪费纸了,还是丑。你真的敢把打印好的表格给老板看吗?
对于这样的数据,需要将一列均匀分布到多列。通常有两种方法可以实现这一点。我们先来看看操作方法:
一、操作法将一列数据变成多列
第一步:先设计需要拆分多少列,然后手动做标题;
在本例中,计划拆分8列。
第二步:在c2单元格中输入a2,向右拉。Excel会自动给我们填a9。
第三步:在单元格c3中输入a10,向右拉;因为第一行已经到达a9,所以这一行从a10开始,一直填充到a17。
第四步:选择两行,一起下拉。假设有160个数据,每行8个,那么下拉20行就够了;
第五步:按Ctrl h打开搜索替换,搜索内容为A,替换内容=a,如下图所示;
点击全部替换,确定,完成操作。
[!- empirenews.page - ]
第六步:选择性粘贴为值删除公式,数据居中加边框,设置打印预览。效果正是我们需要的;
结论:
1.如果不是每排8个,只需调整第一个右拉的数量即可;
2.操作方法的好处是简单易用,比较适合新手;
3.但是,操作方法也有缺点。如果数据不断增加,每次都要这么做,会相当麻烦。所以,你还是要了解一下公式。
二、公式法将一列数据变成多列
还是用这个例子来说明OFFSET函数通常是用来把一列变成多列的。让我们先来看看这个公式是什么样子的:
=OFFSET($A$1,MOD(列(A1) 7,8)行(A1)*8-7,)
把这个公式拉到右边。
这个OFFSET函数根据偏移量获取一个新的引用(单元格或区域)。格式中有五个参数:
偏移量(起始位置、行偏移量、列偏移量、高度、宽度)。
在本例中,起始位置是单元格A1,因为数据源都在一列,而且都是单元格引用,所以只需要确定行偏移量。您可以看到公式中只使用了两个参数,第二个参数是
MOD(列(A1) 7,8)行(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($A$1,INT(COLUMN(C1)/3) ROW(A1)*3-3,MOD(COLUMN(C1),3))
因为数据源不在同一列中,所以应该考虑行偏移量和列偏移量,所以使用了三个参数。第一个参数从A1开始;
第二个参数INT(COLUMN(C1)/3) ROW(A1)*3-3表示参考数据相对于起始位置A1的行偏移量,效果为:
[!- empirenews.page - ]
你可以清楚地看到变化的规律;
第三个参数MOD(COLUMN(C1),3)表示参考数据相对于起始位置A1的列偏移。单看的效果是:
对这个规律的掌控能力决定了OFFSET函数的使用水平,也正是因为这个原因,OFFSET函数让很多初学者一头雾水,学起来总是毫无意义。
今天我们的主要目的不是学习OFFSET函数,而是学习这个函数的一个应用。对于这个例子,写下公式套路,能够根据你的实际问题修改公式,解决问题就够了。
学好OFFSET的关键在于第二、三参数的构造。
比如上面的问题,数据源是三列,希望是四组(十二列)。第二个参数需要是INT(COLUMN(C1)/3) ROW(A1)*4-4。
对箭头指示的两个位置进行更改。
而第三个参数=MOD(列(C1),3)不需要调整;以此类推,只要数据源中的列数不变,只需要调整上面提到的两个位置。
当数据源变成2列时,第二个参数需要修改为INT(COLUMN(B1)/2) ROW(A1)*3-3。
或者修改两个位置,C改为B,3改为2,第三个参数列的偏移量也修改:
=MOD(列(B1),2)
还有两处修改:C改为B,3改为2。
以此类推,如果数据源变成4列,分别把B改成D,2改成4。
通过以上的解释,我们基本可以了解如何根据实际需要调整公式的相应内容。要彻底理解这里的玄机,数列的构造方法是绕不过去的坎。
让我们一起来学习Excel中那些有趣又实用的技巧吧!