VBA代码：将水平单元格区域转换成垂直单元格区域

标签：VBA

下图1所示是一个常见的需求，在多个列中放置着每个月份的数据，需要将月份移到单个列中，同时保留报表中的所有描述性信息。

图1

数据显示了有关部门、账户和成本中心的描述性信息，而月度数据显示在许多列中。现在希望看到的是，左侧3列上的数据重复，而财务数据则逐行重复。数据输出如下图2所示。

图2

这可以使用一个简单的VBA程序来实现。首先，需要两个数组，一个将保存原始数据，另一个将新格式化的数据放在其中。

Sub Transpose()
    Dim ws As Worksheet
    Dim sh As Worksheet
    Dim ar
    Dim var()
    Dim i As Long
    Dim n As Long
    Dim k As Integer
    Dim j As Integer
    Set ws = Sheet1 '原始数据
    Set sh = Sheet2 '结果工作表
    sh.[A1].CurrentRegion.Offset(1).ClearContents
    ar = ws.UsedRange
    For i = 2 To UBound(ar, 1)
        For j = 4 To 15
            n = n + 1
            ReDim Preserve var(1 To 5, 1 To n)
            For k = 1 To 3
                var(k, n) = ar(i, k)
            Next k
            var(4, n) = ar(1, j) '日期
            var(5, n) = ar(i, j) '月度数据
        Next j
    Next i
    sh.[A2].Resize(n, 5) = WorksheetFunction.Transpose(var)
End Sub

工作簿中有两个工作表——Data工作表和Output工作表，工作表代码名称分别是Sheet1和Sheet2。

第一个数组变量称为ar，此数组将数据存储在许多列中。它拾取已使用的单元格区域：

ar=ws.UsedRange

Data工作表中的所有数据都存储这个变量中。

在此之后，需要循环遍历15列（3个描述性列和12个数字列）。将ar变量中这15列转换为输出变量var中的5列数据集，然后将数据输出到Output工作表。

For i=2 To UBound(ar，1)

原始循环从第2行开始，因为忽略了标题，这5列的标题位于Output工作表的第1行。UBound语句代表上限，它是变量ar中的行数——数据集中有10行，因此它从2循环到10。第一行包含我们忽略的标题。

下面是将数字垂直翻转的循环。因此，第4列变为第2行，第5列变为第3行，以此类推。

For j=4 To 15

这是第4列（Jan）到第15列（Dec）。

接下来的两行是计数器和使变量var动态的语句。

n=n+1

ReDim Preserve var(1 To 5，1 To n)

第一行中的n是一个计数器，它只是一种跟踪我们要放置数据的行的方法。ReDim行是动态魔术发生的地方。也就是说，变量var的宽度将为5列，并且将从1运行到列表n所表示的长度，因此是一个5 X n的表，其中5表示列，n是这些列的长度。

下面是希望在12个月内重复的数据的循环构造。这将是部门、账户和成本中心。

For k = 1 To 3

var(k, n) = ar(i, k)

Next k

循环从第1列开始，一直转到第3列。这个过程使var等于数组ar中的值，在这种情况下，它将是由ar(i，k)表示的ar(2，1)。第一个实例中的变量i将等于2，因此ar(i=ar(2，第一个实例中变量k将等于1，因此ar(2，1)，其中1是循环第一部分上的k，当循环从1到3时，列将从列1移动到2和3，而行将保持在2。因此，第2行将使用此简单循环填写部门、账户和成本中心数据。

以下应该是困难的部分，但由于数据在列方面是静态的，因此这部分非常简单。

var(4, n) = ar(1, j)

查看日期并将其从第1行转换为所有其他行。变量（var）的第一部分等于var(4，n)，其中4是日期所在的列号，n是从2增长到单元格区域底部的行号。数组变量ar的引用是ar(1，j)，其中行是1，列是j，由列4至15表示。循环将从4开始，每个循环迭代1次，直到达到15。

第二部分是将金额添加到第5列。

var(5, n) = ar(i, j)

var(5，n)是第5列和第n行。n将随着i循环行的每次迭代逐行增长：

n=n+1

这表示n等于自身加1。在第一个实例中，这是1，然后随着i循环的每次迭代，它将增长1。数组ar(i，j)只是对随着两个循环i和j的每次迭代而增长的行i和列j的引用。

运行完所有循环后，该过程就基本完成了。这是一个运行速度非常快的过程。最后一步是转置：

sh.[A2].Resize(n, 5) = WorksheetFunction.Transpose(var)

上面的代码是从第2行开始，并将区域调整为n所在的任何值，因此可能是108行，共有5列。变量var是被转换的区域。

如果你碰到类似的情形，可以结合实际对上述代码稍作调整，以满足特定的需求。

注：本文学习整理自thesmallman.com，有兴趣的朋友可以到该网站下载示例工作簿，也可以到知识星球App完美Excel社群下载示例工作簿。