将Excel矩形数据区转换为一行或一列

将工作表中的矩形数据区域转换为行或列。例如，名为“数据”的区域如下图所示：

转换为单列：

或者一行：

先不要看下面的答案，可以先想想怎么实现。

公式思维

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

公式分析

将区域中的数据按行顺序转换为单个列。

在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、" sheet 1 ")

然后下拉到单元格B17，如下图所示。

其中，row(Data)int((row()-row($ b $ 2))/columns(Data))获取数据区域中第一行和第一列的数据所在的行。数学原理：从需要输入数据的区域的当前单元格的行号中减去第一个行号，除以提取的单元格区域的列数，再加上数据区域的起始行号，即数据区域中要放入当前单元格的数据的行号。

Column (Data) mod (row ()-row ($ b $2)，columns (data))获取数据区域中第一行和第一列的数据所在的列。数学原理：利用需要输入数据的区域中当前单元格的行号减去第一个行号的结果，除以提取的单元格区域Data的列号的余数，将得到的结果加上Data区域的初始列号，即为数据区域中当前单元格要放置的数据的列号。

Addres(行号，列号，4，“Sheet1”)返回工作表sheet1中相应行和列号在相对引用中的地址。

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

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

以下情况与上述情况相似，原理相同。这里只给出公式，大家可以自己调试体验。

将区域中的数据按列顺序转换为单个列。

单元格B2中的公式如下，然后拖放到单元格B17。

将区域中的数据按行顺序转换为单行。

“B2”单元格中的公式如下，然后向右拖动到“Q2”单元格。

将区域中的数据按列顺序转换为单行。

“B2”单元格中的公式如下，然后向右拖动到“Q2”单元格。

总结

这个例子的关键是在数据区找到要提取的Data的行号和列号，操作才是本质。首先，我们可以用代表行和列的数字来找到规则，然后用相应的求行号和列号的函数来代替。