Excel怎么用公式对数据进行多行多列得转置

如何利用Excel中的公式将单列数据转换为多行多列数据，是一项无法通过简单操作完成的任务。在上一篇文章中，我们讨论了使用特殊粘贴的单个行或列的转置，以及二维表的行和列的转置。但今天我们要介绍的是一对多转置，所以需要用到复合公式或者VBA代码。

这里我们还是用公式来计算。通过对公式的分析，将帮助大家理解和使用一些函数。

下图中，它是英语单词和短语的列表。

我们的目标是将A列中的数据转换为任意数量的列，以便数据可以重新排列到指定的行和列中。如下图所示，A列按照A列原来的数据排列方式转换为6列数据，通过从左到右移动，然后到下一行继续从左到右，来组织并显示所有数据。

要达到如上所示的效果，其实并不复杂。使用许多Excel函数的孩子可能会立即想到一个函数：OFFSET函数。

偏移函数是一种引用函数，用于引用某个单元格或范围内的数据，常用于组合函数。

其语法为：OFFSET(reference,rows,columns,height,width)，可以理解为“(引用单元格，向下移动的行数，向右移动的列数，引用区域的行数，refer到该区域中的列数）”。如果省略最后两个参数，则意味着仅引用单个单元格，而不是范围单元格。

如上图红框所示，公式为“OFFSET($A$1,ROW(A1)*6-6COLUMN(A1)-1,0)”，我们从第一个参数开始分析。

$A$1将单元格A1引用为引用和绝对引用。$符号表示绝对引用。

ROW(A1)*6-6COLUMN(A1)-1，该参数是向下移动的行数，即以A1单元格为基准，向下移动多少行。在这种情况下，row函数返回公式引用的行号，例如row(a1)，它返回单元格a1的行号，即第一行，并返回值1。

列函数返回公式引用的列号。它的使用方式与行函数相同，只不过一个代表行，另一个代表列。那么我们回到前面的公式，ROW(A1)*6-6，等于1*6-6，结果为0COLUMN(A1)-1，等于1-1，结果为0；所以这个参数的结果是00等于0。

再看第三个参数，这里我们直接输入0，表示根据上面提到的三个参数的值，即offset($a$1,0,0)向右移动了0行。，以a1单元格为参考，向下移动0行，向右移动0列相当于不移动，所以公式返回的结果就是a1单元格的值。

我们将公式向右或向下拉动即可看到下图。

公式为“OFFSET($A$1,ROW(B1)*6-6COLUMN(B1)-1,0)”，row(b1)的值仍然是1，column(b1)的值是2，所以以a1开头作为参考，向下移动了一行，即a2，指的是a2的数据。

同样，我们记下公式：

通过前面的解释，孩子们能理解这个公式的含义吗？！

offset函数虽然不像sum、sumif、vlookup等函数使用频率那么高，但是它的功能却很多。当与其他功能结合使用时，可以产生意想不到的效果。

关注作者，一起发现更多关于Excel的知识！

了解更多：

如何使用Excel中的公式比较两个表中数据的差异

Excel、PPT功能区中的设置和调整，所见即所得首先介绍了Excel切片器，切片器是做什么用的，如何设置？