当前位置:首页 > 办公 > Excel教程 > 正文内容

将Excel矩形数据区域转换成一行或者一列

访客2个月前 (09-06)Excel教程78

将工作表中矩形数据区域转换成一行或者一列。例如将下图所示的名为Data的区域:

转换成单独的一列:

或者单独的一行:

先不要看下面的答案,自已可以先想一想如何实现?

公式思路

获取数据所在的行号和列号,然后提取行列号所在单元格中的数据。

公式解析

按行顺序将区域中的数据转换成单独的一列

在单元格B2中输入公式:

=IF(INDIRECT(ADDRESS(ROW(Data)+INT((ROW()-ROW($B$2))/COLUMNS(Data)),COLUMN(Data)+MOD(ROW()-ROW($B$2),COLUMNS(Data)),4,,”Sheet1″))=””,””,INDIRECT(ADDRESS(ROW(Data)+INT((ROW()-ROW($B$2))/COLUMNS(Data)),COLUMN(Data)+MOD(ROW()-ROW($B$2),COLUMNS(Data)),4,,”Sheet1″)))

然后下拉至单元格B17,如下图所示。

其中,ROW(Data)+INT((ROW()-ROW($B$2))/COLUMNS(Data))获取Data区域中第1行第1列的数据所在的行。数学原理:用需要输入数据的区域中当前单元格所在行的行号减去首行行号的结果,除以被提取单元格区域Data的列数,所得结果加上Data区域起始行号,即为要在当前单元格中放置的数据在Data区域中的行号。

COLUMN(Data)+MOD(ROW()-ROW($B$2),COLUMNS(Data))获取Data区域中第1行第1列的数据所在的列。数学原理:用需要输入数据的区域中当前单元格所在行的行号减去首行行号的结果,除以被提取单元格区域Data的列数的余数,用所得结果加上Data区域起始列号,即为要在当前单元格中放置的数据在Data区域中的列号。

Addrees(行号,列号,4,,”Sheet1”)以相对引用方式返回工作表Sheet1中相应行列号的地址。

INDIRECT(引用地址)返回引用地址所在单元格的数据。

使用IF函数让结果为空的单元格仍然返回为空,而不是0。

下面的几种情形与上文介绍的情形相似,原理相同,这里只给出公式,你可以自已调试体会。

按列顺序将区域中的数据转换成单独的一列

在单元格B2中的公式如下,然后拖动下拉至单元格B17。

按行顺序将区域中的数据转换成单独的一行

在单元格B2中的公式如下,然后向右拖动拉至单元格Q2。

按列顺序将区域中的数据转换成单独的一行

在单元格B2中的公式如下,然后向右拖动拉至单元格Q2。

小结

本例的关键在于求出需要提取的数据在Data区域中的行列号,其中的运算是精华所在。可以先用代表行列的数字找到规律,再用相应求得行号列号的函数代替。

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/1498.html

标签: excel
分享给朋友:

相关文章

OFFSET函数使用示例:动态区域中查找指定数据

OFFSET函数使用示例:动态区域中查找指定数据

查找公式中,表示区域的方法是:OFFSET(D5,0,0,500,500),表示以D5为起始单元格的500行500列的区域。 有位朋友马上提出问题: 如果区域不是500行500列,而是不断扩大的动态区域怎么办? 解决方法...

打印Excel表格时怎么去除底纹和颜色?

打印Excel表格时怎么去除底纹和颜色?

  通常在Excel表格中,重点部分都会填充一些颜色,能够比较明显的区分。而且Excel文档中也会有表格线,也就是底纹,那么在打印的时候不想打印出底纹和颜色,该怎么去除底纹和颜色呢?下...

Vlookup函数最经典的12种用法!太太太太太太太有用了

Vlookup函数最经典的12种用法!太太太太太太太有用了

Vlookup函数的教程网上已非常多了,而贴近工作用的Vlookup函数应用示例却很少。今天给同学们带来一期Vlookup函数示例大全,希望能给大家的工作带来帮助。 一 【例1】查找个人信息 =VLOOKUP($B18,...

Excel如何统计某个数据出现次数?

Excel如何统计某个数据出现次数?

  Excel表格经常被用于数据的统计和记录,可能有时候你需要统计一下表格中某个数据(关键词)的出现频率,也就是Excel统计某个数据出现次数,笔者整理了两种方法来“筛选&...

43个Excel函数,数据分析必备!

43个Excel函数,数据分析必备!

Excel是我们工作中经常使用的一种工具,对于数据分析来说,这也是处理数据最基础的工具。很多传统行业的数据分析师甚至只要掌握Excel和SQL即可。 对于初学者而言,有时候并不需要急于苦学R语言等专业工具(当然,学会了就...

条件求和Sumif函数才是NO.1,一个函数完成80%工作量,不要说你还没见过!

条件求和Sumif函数才是NO.1,一个函数完成80%工作量,不要说你还没见过!

Excel条件求和函数相信大家首先就会想到Sumif函数,sumif条件求和函数在Excel中使用的频率非常高,作为单条件求和函数,他在进行数据条件求和操作时,函数使用起来非常的高效。今天我们就来学习一下sumif条件求...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。