EXCEL中多行多列数据与一行或一列数据的互相转换

       在平常所用数据中,会出现多行多列数据,但是实际又需要一行或一列形式的数据,或者相反者,那么这篇文章将教会你如何在excel中对多行多列数据与一行一列数据的相互转换、或者将行数据变为列数据、列数据变为行数据。

下面将解决这几个问题:

将运用到的excel函数:
①OFFSET函数的语法结构为:OFFSET(reference,rows,cols,[height],[width])
        即:OFFSET(引用区域,行号,列号,【高度】,【宽度】)
       reference:作为偏移量参照系的引用区域,必须为对单元格或者相连单元格区域的引用,否则,函数会返回错误值。
        rows:相对于偏移量参照系的左上角单元格,向上或者向下偏移的行数,rows可以是正数或者负数,正数是指向下偏移的行数,负数指向上偏移的行数。
       cols:相对于偏移量参照系的左上角单元格,向左或者向右偏移的列数,cols可以是正数或者负数,正数指向右偏移的列数,负数指向左偏移的列数。
       height:高度,即所要返回的引用区域的行数。
       width:宽度,即所要返回的引用区域的列数。
当height、width参数省略时,默认以第1个参数reference的高度和宽度为准;当指定height、width参数时,则以指定的高度、宽度值为准。

②COLUMN函数的含义:返回所选择的某一个单元格的列数。
COLUMN函数的语法格式
=COLUMN(reference)
如果省略reference,则默认返回函数COLUMN所在单元格的列数。

③ROW函数的含义:返回所选择的某一个单元格的行数。
ROW函数的语法格式
=ROW(reference)
如果省略reference,则默认返回ROW函数所在单元格的行数。

1、多行多列数据转换为一列数据

多行n列的行数据转换为一列数据

空白处填充下列公式,然后下拉填充即可(数据不够填充空格):
=OFFSET($A$1,ROW(An)/n-1,MOD(ROW(An),n))&””

如将4行5列数据转换为一列数据:
=OFFSET($A$1,ROW(A5)/5-1,MOD(ROW(A5),5))&””
如图:
《EXCEL中多行多列数据与一行或一列数据的互相转换》

n行多列的行数据转换为一列数据

空白处填充下列公式,然后下拉填充即可:
=OFFSET($A$1,MOD(ROW(An),n),ROW(An)/n-1,)&””

如将4行5列数据转换为一列数据:
=OFFSET($A$1,MOD(ROW(A4),4),ROW(A4)/4-1,)&””
如图:
《EXCEL中多行多列数据与一行或一列数据的互相转换》

2、一行或一列数据转换为多行多列数据

一列数据转换为n列多行

可在任意空白处填充如下公式:
=OFFSET($A$1,COLUMN(A1)-1+(ROW(A1)-1)*n,)&””

如将数据转换为5列多行,任意空白处填充如下公式:
=OFFSET($A$1,COLUMN(A1)-1+(ROW(A1)-1)*5,)&””
然后横向拉填充五个单元格,在纵向拉填充,直到出现空白
如图:《EXCEL中多行多列数据与一行或一列数据的互相转换》

一列数据转换为n行多列

若想将一列数据转换为n行多列,则可用下列公式:
=OFFSET($A$1,ROW(A1)-1+COLUMN(A1)*n-n,)&””

如将一列数据转换为5行多列,任意空白处填充如下公式:
=OFFSET($A$1,ROW(A1)-1+COLUMN(A1)*5-5,)&””
然后纵向拉填充五个单元格,在横向拉填充,直到出现空白
如图:

《EXCEL中多行多列数据与一行或一列数据的互相转换》

一行数据转换为n列多行

若想将一行数据转换为n列多行,则可用下列公式:
=OFFSET($A$1,COLUMN(A1)-1+(ROW(A1)-1)*n,)&””

如将一行数据转换为5列多行,任意空白处填充如下公式:
=OFFSET($A$1,COLUMN(A1)-1+(ROW(A1)-1)*5,)&””
然后横向拉填充五个单元格,在纵向拉填充,直到出现空白

一行数据转换为n行多列

若想将一行数据转换为n行多列,则可用下列公式:
=OFFSET($A$1,(ROW(A1)-1)+(COLUMN(A1)-1)*n,)&””

如将一列数据转换为5行多列,任意空白处填充如下公式:
=OFFSET($A$1,(ROW(A1)-1)+(COLUMN(A1)-1)*5,)&””
然后纵向拉填充五个单元格,在横向拉填充,直到出现空白

如图:
《EXCEL中多行多列数据与一行或一列数据的互相转换》

3、行数据转换为列数据,或列数据转换为行数据

①复制数据
《EXCEL中多行多列数据与一行或一列数据的互相转换》

②任意选择一个空白区域,但选择的区域行、列数必须大于等于原表的列、行数
《EXCEL中多行多列数据与一行或一列数据的互相转换》

③鼠标右击,然后【选择性粘贴】
《EXCEL中多行多列数据与一行或一列数据的互相转换》

④点击转置图标,如图

《EXCEL中多行多列数据与一行或一列数据的互相转换》
⑤即可得到结果
《EXCEL中多行多列数据与一行或一列数据的互相转换》

    原文作者:淋了一场雨
    原文地址: https://blog.csdn.net/My_daily_life/article/details/121140517
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞