Excel UDF 到表中的数据块的逆透视（融化、反向透视、展平、标准化）

excel

excel UDF 到列表中的数据块的逆透视（融化、反向透视、展平、标准化）

在excel中，透视表是一种非常强大的工具，可以帮助我们对大量数据进行汇总和分析。然而，在某些情况下，我们可能需要将透视表中的数据重新排列成列表的形式，这就需要使用excel的UDF（用户定义函数）来实现逆透视。

逆透视是指将透视表中的数据块重新排列成列表的过程。具体来说，逆透视可以实现数据的融化、反向透视、展平和标准化等操作。下面我们来逐一介绍这些操作，并给出相应的案例代码。

1. 数据融化

数据融化是指将透视表中的数据块重新排列成两列的形式，其中一列是原始数据的标签，另一列是对应的值。这样可以方便我们进行进一步的分析和处理。

例如，我们有一个透视表如下：

| 姓名 | 语文 | 数学 | 英语 |

|-----|-----|-----|-----|

| 张三 | 80 | 90 | 85 |

| 李四 | 70 | 75 | 80 |

| 王五 | 85 | 95 | 90 |

使用以下的UDF代码可以将透视表中的数据融化成列表的形式：

VBA
Function MeltData(rng As Range) As Variant
    Dim data() As Variant
    Dim i As Long, j As Long
    Dim numRows As Long, numCols As Long
    
    numRows = rng.Rows.Count - 1
    numCols = rng.Columns.Count - 1
    
    ReDim data(1 To numRows * numCols, 1 To 2)
    
    For i = 1 To numRows
        For j = 1 To numCols
            data((i - 1) * numCols + j, 1) = rng.Cells(i + 1, 1)
            data((i - 1) * numCols + j, 2) = rng.Cells(1, j + 1)
        Next j
    Next i
    
    MeltData = data
End Function

使用该UDF函数，我们可以将上述的透视表融化成如下的列表：

| 姓名 | 科目 | 分数 |

|-----|-----|-----|

| 张三 | 语文 | 80 |

| 张三 | 数学 | 90 |

| 张三 | 英语 | 85 |

| 李四 | 语文 | 70 |

| 李四 | 数学 | 75 |

| 李四 | 英语 | 80 |

| 王五 | 语文 | 85 |

| 王五 | 数学 | 95 |

| 王五 | 英语 | 90 |

2. 反向透视

反向透视是指将透视表中的数据块重新排列成原始数据的形式。这样可以方便我们对数据进行进一步的处理和分析。

例如，我们有一个透视表如下：

| 姓名 | 科目 | 分数 |

|-----|-----|-----|

| 张三 | 语文 | 80 |

| 张三 | 数学 | 90 |

| 张三 | 英语 | 85 |

| 李四 | 语文 | 70 |

| 李四 | 数学 | 75 |

| 李四 | 英语 | 80 |

| 王五 | 语文 | 85 |

| 王五 | 数学 | 95 |

| 王五 | 英语 | 90 |

使用以下的UDF代码可以将透视表中的数据反向透视成原始数据的形式：

VBA
Function ReversePivot(rng As Range) As Variant
    Dim data() As Variant
    Dim i As Long, j As Long
    Dim numRows As Long, numCols As Long
    
    numRows = rng.Rows.Count
    numCols = rng.Columns.Count
    
    ReDim data(1 To numRows - 1, 1 To numCols - 1)
    
    For i = 1 To numRows - 1
        For j = 1 To numCols - 1
            data(i, j) = rng.Cells(i + 1, j + 1)
        Next j
    Next i
    
    ReversePivot = data
End Function

使用该UDF函数，我们可以将上述的透视表反向透视成如下的原始数据：

| 姓名 | 语文 | 数学 | 英语 |

|-----|-----|-----|-----|

| 张三 | 80 | 90 | 85 |

| 李四 | 70 | 75 | 80 |

| 王五 | 85 | 95 | 90 |

3. 展平

展平是指将透视表中的多级标签展开成一级标签的过程。这样可以方便我们对数据进行汇总和分析。

例如，我们有一个透视表如下：

| 姓名 | 学期 | 科目 | 分数 |

|-----|-----|-----|-----|

| 张三 | 第一学期 | 语文 | 80 |

| 张三 | 第一学期 | 数学 | 90 |

| 张三 | 第一学期 | 英语 | 85 |

| 张三 | 第二学期 | 语文 | 85 |

| 张三 | 第二学期 | 数学 | 95 |

| 张三 | 第二学期 | 英语 | 90 |

| 李四 | 第一学期 | 语文 | 70 |

| 李四 | 第一学期 | 数学 | 75 |

| 李四 | 第一学期 | 英语 | 80 |

| 李四 | 第二学期 | 语文 | 80 |

| 李四 | 第二学期 | 数学 | 85 |

| 李四 | 第二学期 | 英语 | 90 |

| 王五 | 第一学期 | 语文 | 85 |

| 王五 | 第一学期 | 数学 | 95 |

| 王五 | 第一学期 | 英语 | 90 |

| 王五 | 第二学期 | 语文 | 90 |

| 王五 | 第二学期 | 数学 | 100 |

| 王五 | 第二学期 | 英语 | 95 |

使用以下的UDF代码可以将透视表中的数据展平成一级标签的形式：

VBA
Function FlattenData(rng As Range) As Variant
    Dim data() As Variant
    Dim i As Long, j As Long
    Dim numRows As Long, numCols As Long
    
    numRows = rng.Rows.Count - 1
    numCols = rng.Columns.Count - 2
    
    ReDim data(1 To numRows, 1 To numCols * 2 + 1)
    
    For i = 1 To numRows
        data(i, 1) = rng.Cells(i + 1, 1)
        data(i, 2) = rng.Cells(i + 1, 2)
        For j = 1 To numCols
            data(i, j * 2 + 1) = rng.Cells(i + 1, j * 3)
            data(i, j * 2 + 2) = rng.Cells(i + 1, j * 3 + 1)
        Next j
    Next i
    
    FlattenData = data
End Function

使用该UDF函数，我们可以将上述的透视表展平成如下的一级标签的形式：

| 姓名 | 学期 | 语文 | 数学 | 英语 |

|-----|-----|-----|-----|-----|

| 张三 | 第一学期 | 80 | 90 | 85 |

| 张三 | 第二学期 | 85 | 95 | 90 |

| 李四 | 第一学期 | 70 | 75 | 80 |

| 李四 | 第二学期 | 80 | 85 | 90 |

| 王五 | 第一学期 | 85 | 95 | 90 |

| 王五 | 第二学期 | 90 | 100 | 95 |

4. 标准化

标准化是指将透视表中的数据进行归一化处理，使其符合一定的标准。这样可以方便我们对数据进行比较和分析。

例如，我们有一个透视表如下：

| 姓名 | 语文 | 数学 | 英语 |

|-----|-----|-----|-----|

| 张三 | 80 | 90 | 85 |

| 李四 | 70 | 75 | 80 |

| 王五 | 85 | 95 | 90 |

使用以下的UDF代码可以将透视表中的数据进行标准化处理：

VBA
Function NormalizeData(rng As Range) As Variant
    Dim data() As Variant
    Dim i As Long, j As Long
    Dim numRows As Long, numCols As Long
    Dim maxValue As Double, minValue As Double
    
    numRows = rng.Rows.Count - 1
    numCols = rng.Columns.Count - 1
    
    ReDim data(1 To numRows, 1 To numCols)
    
    maxValue = WorksheetFunction.Max(rng)
    minValue = WorksheetFunction.Min(rng)
    
    For i = 1 To numRows
        For j = 1 To numCols
            data(i, j) = (rng.Cells(i + 1, j + 1) - minValue) / (maxValue - minValue)
        Next j
    Next i
    
    NormalizeData = data
End Function

使用该UDF函数，我们可以将上述的透视表进行标准化处理，使得数据符合0到1之间的范围：

| 姓名 | 语文 | 数学 | 英语 |

|-----|-----|-----|-----|

| 张三 | 0.25 | 0.75 | 0.5 |

| 李四 | 0 | 0.25 | 0 |

| 王五 | 1 | 1 | 1 |

逆透视是一种将透视表中的数据重新排列成列表的操作，可以通过excel的UDF函数来实现。本文介绍了数据融化、反向透视、展平和标准化这四种常见的逆透视操作，并提供了相应的案例代码。通过逆透视，我们可以更方便地对数据进行分析和处理，提高工作效率。