VBA实现快速逆透视

taller_2000

已于 2024-02-25 09:19:22 修改

阅读量479

点赞数 3

分类专栏：数据清洗 VBA 数组文章标签：逆透视数据转换行列转换多维度交叉表二维表

于 2024-02-25 04:14:00 首次发布

本文链接：https://blog.csdn.net/taller_2000/article/details/136278402

版权

VBA 同时被 3 个专栏收录

315 篇文章

订阅专栏

数据清洗

86 篇文章

订阅专栏

数组

58 篇文章

订阅专栏

本文详细解释了如何使用VBA在Excel中编写SubUnpivotTable函数，将工作表中的多维度交叉数据，特别是对日期进行逆透视操作，转化为新的表格格式。代码展示了如何处理固定列、标题字段和数值字段的转换过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实例需求：将工作表中的数据（多维度交叉），对日期进行逆透视，转换为下表的格式。
在这里插入图片描述
示例代码如下。

Sub UnpivotTable()
    Dim oSht As Worksheet
    Dim inLastRow As Long, inLastCol As Long
    Dim outLastRow As Long, outCol As Long
    Dim iRow As Long, iCol As Long
    Const FIX_COLS = 4
    Const HEADER_FLD = "Date"
    Const VALUE_FLD = "Value"
    Set oSht = Sheet("Data")
    With oSht
        inLastRow = .Cells(.Rows.Count, "A").End(xlUp).row
        inLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        outLastRow = inLastRow + 2
        .Cells(outLastRow, 1).Resize(1, FIX_COLS).Value = .Cells(1, 1).Resize(1, FIX_COLS).Value
        .Cells(outLastRow, FIX_COLS + 1).Value = HEADER_FLD
        .Cells(outLastRow, FIX_COLS + 2).Value = VALUE_FLD
        For iRow = 2 To inLastRow
            For iCol = 5 To inLastCol
                outLastRow = outLastRow + 1
                .Cells(outLastRow, 1).Resize(1, FIX_COLS).Value = .Cells(iRow, 1).Resize(1, FIX_COLS).Value
                .Cells(outLastRow, FIX_COLS + 1) = .Cells(1, iCol)
                .Cells(outLastRow, FIX_COLS + 2) = .Cells(iRow, iCol)
            Next iCol
        Next iRow
    End With
End Sub

【代码解析】
第6~8行代码定义常量，其含义如下。

常量	含义
FIX_COLS	数据表中固定列的数量，这些列将包含在输出表的每行数据中
HEADER_FLD	被转置标题字段名称
VALUE_FLD	数值字段名称

第9行代码获取源数据工作表对象引用。
第11~12行代码获取数据区域的最后行和列位置。

微软参考文档:

Range.End 属性 (Excel)

第13行代码设置数据输出位置为最后数据行之下两行。
第14行代码复制固定列标题行。
第15~16行代码更新标题字段（日期Date）和值字段标题（数值Value）。
第17~24行循环变量源数据表格。
第19行输出数据表的行号递增1。
第20行复制固定列数据。
第21行填写Date列数据。
第22行填写Value列数据。