首页 > Excel专区 > Excel函数 >

excel如何将区域中的数据转化到单行或单列中

Excel函数 2023-01-13

要将一个二维区域中的数据转化到单列或单行中,实现的方法有很多,例如用VBA就可以轻松完成。下面用公式来进行转化,也可以实现目的。

例如有一个13行8列的区域,我们要将其中的姓名转化到一列中,步骤如下:

1.定义名称

将这个区域定义一个名称,以便在公式中引用。选择该区域,在名称框中输入“Data”,按回车键,名称就定义好了。

然后选择需要放置该名单所在列的第一个单元格,用同样方法定义名称为“FirstData”。

2.保持选择该单元格,在其中输入公式:

=OFFSET(Data,MOD(ROW()-ROW(FirstData),ROWS(Data)),TRUNC((ROW()-ROW(FirstData))/ROWS(Data),0),1,1)

3.拖动填充柄向下填充即可。

该公式是按照逐列的方法来提取数据的,用到OFFSET函数,用MOD(ROW()-ROW(FirstData),ROWS(Data))来确定偏移的行数,用TRUNC((ROW()-ROW(FirstData))/ROWS(Data),0)来确定偏移的列数。

也可以按照逐行的方法来提取数据,公式如下:

=OFFSET(Data,TRUNC((ROW()-ROW(FirstData))/COLUMNS(Data),0),MOD(ROW()-ROW(FirstData),COLUMNS(Data)),1,1)

如果要把区域中的数据转化到一行中,可以用下面的两个公式,假设第一个单元格名称定义为“FirstData”。

用逐行提取数据的方法:

=OFFSET(Data,TRUNC((COLUMN()-COLUMN(FirstData))/COLUMNS(Data),0),MOD(COLUMN()-COLUMN(FirstData),COLUMNS(Data)),1,1)

用逐列提取数据的方法:

=OFFSET(Data,MOD(COLUMN()-COLUMN(FirstData),ROWS(Data)),TRUNC((COLUMN()-COLUMN(FirstData))/ROWS(Data),0),1,1)


Copyright © 2016-2023 office学习教程网 office.tqzw.net.cn. All Rights Reserved.