☰

如何将表格格式的数据转换为数据透视格式

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 vba
如何将表格格式的数据转换为数据透视格式

我需要帮助将表格格式转换为数据透视表格式。以下是我拥有的示例数据：

Product     Currency    Value   US   UK   Japan     Ind  Local  MNC
Product 1    USD         200    56%  22%    0%      13%   68%    19%
Product 2    USD         300    67%  18%    15%      0%   85%    15%
Product 3    GBP         400    13%  68%    19%     67%   18%    15%

我需要以下格式的输出：

Product    Currency Value 1 Region   Customer     Value 2   Value 3
Product 1   USD      200    US         Ind           56%    13%
Product 1   USD      200    US         Local         56%    68%
Product 1   USD      200    US         MNC           56%    19%
Product 1   USD      200    UK         Ind           22%    13%
Product 1   USD      200    UK         Local         22%    68%
Product 1   USD      200    UK         MNC           22%    19%
Product 1   USD      200    Japan      Ind            0%    13%
Product 1   USD      200    Japan      Local          0%    68%
Product 1   USD      200    Japan      MNC            0%    19%

如果您看到，对于每一行Product 1，我需要三行国家/地区和三行客户类型。唯一的例外是，如果国家/地区或客户类型是，0%我们可以忽略这些单元格。因此，例如从上面的输出表中，我们可能不需要行，Japan因为Japan对于0%Product 1

同样，对于Product 2，我们可能不需要Individual行，因为它被赋值为零。我不知道如何编写 vb 代码，所以尝试录制宏，但是由于数据量巨大，宏不起作用。

另外，如果有帮助的话

A 部分 - 包括产品、货币和价值
B 部分 - 美国、英国和日本 - 基本上列出了几个国家
C 部分 - 客户类型 - 包括印度、本地、跨国公司

答案1

我写了一个代码，请你尝试一下：

Option Explicit

Public Sub customePivot()
    Call makePivot(Sheets("Sheet1"), Sheets("Sheet2"))
End Sub

Private Sub makePivot(sourceWs As Worksheet, DestWs As Worksheet, Optional startRow As Long = 2)
    Dim i As Long, j As Integer, k As Integer, lastRow As Long
    Dim strTemp As String

    lastRow = startRow
    For i = startRow To sourceWs.Rows.Count
        strTemp = Trim(sourceWs.Cells(i, 1).Value & "")
        If (strTemp = "") Then Exit For
        '
        If (Val(sourceWs.Cells(i, 4).Value & "") <> 0) Or (Val(sourceWs.Cells(i, 7).Value & "") <> 0) Then
            For j = 1 To 3
                For k = 1 To 3
                    Call addNewRow(DestWs, lastRow, _
                                   (sourceWs.Cells(i, 1).Value & ""), (sourceWs.Cells(i, 2).Value & ""), (sourceWs.Cells(i, 3).Value & ""), _
                                   (sourceWs.Cells(1, 3 + j).Value & ""), (sourceWs.Cells(1, 6 + k).Value & ""), _
                                   (sourceWs.Cells(i, 3 + j).Value & ""), (sourceWs.Cells(i, 6 + k).Value & ""))
                    lastRow = lastRow + 1
                Next k
            Next j
        End If
    Next i
End Sub

Private Sub addNewRow(ws As Worksheet, inRow As Long _
                    , productI As String, currencyI As String, valueI As String _
                    , regionI As String, CustomerI As String _
                    , valu2 As String, value3 As String)
    '
    ws.Cells(inRow, 1).Value = productI
    ws.Cells(inRow, 2).Value = currencyI
    ws.Cells(inRow, 3).Value = valueI
    ws.Cells(inRow, 4).Value = regionI
    ws.Cells(inRow, 5).Value = CustomerI
    ws.Cells(inRow, 6).Value = valu2
    ws.Cells(inRow, 7).Value = value3
End Sub

相关内容