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

hercules028 2022-06-25 发布于四川

展开全文

excelperfect

标签：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)