主页 / computer / 问题 / 1519393
Accepted
User1974
Asked: 2020-01-23 14:40:50 +0800 CST

Unpivot/Transpose 列（无空格）

  • 772

我在 Excel 中有一个资产层次结构表：

在此处输入图像描述

出于测试目的，可以使用以下文本将该表格复制/粘贴到 Excel 中：

提示：从 Stack Overflow编辑模式复制文本——而不是从预览模式复制（编辑模式的文本将在 Excel 中正确解析）。

Hierarchy Path  Spec 1  Spec 2  Spec 3  Spec 4  Spec 5  Spec 6  Spec 7  Spec 8  Spec 9  Spec 10
Passenger Car   A   B   C   D   E   F   G   H   I   J
Sport Utility Vehicle   H   I   J   K   L   M   N   O   P   
1/2 Ton Pick-Up Truck   Q   R   S   T   U   V   W   X       
3/4 Ton Pick-Up Truck   Y   Z   A   B   C   D   E           
Compact Van F   G   H   I   J   K               
Cargo Van   L   M   N   O   P                   
Light Duty Truck    Q   R   S   T

问题：

对于每个层次结构路径，我想：

  • 转置规格
  • 填写每个规范的层次结构路径

注意：规范的数量因层次结构路径而异。我希望行数与规格数相匹配。换句话说，我不希望输出中有空白规范。

例子：

在此处输入图像描述

有没有办法在 Excel 2016 中自动执行此操作？

microsoft-excel microsoft-excel-2016

2 个回答

  1. Best Answer

    您可以使用 Power Query。然后，您的用户可以通过选择“数据”选项卡上的“刷新”选项来更新内容。

    中PQ，你需要做的就是

    • 选择Hierarchy Path列
    • **UN**pivot other columns
    • 删除生成的属性列

    所有步骤都可以从 UI 完成，但这里是

    M-代码

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"    Hierarchy Path", type text}, {"Spec 1", type text}, {"Spec 2", type text}, {"Spec 3", type text}, {"Spec 4", type text}, {"Spec 5", type text}, {"Spec 6", type text}, {"Spec 7", type text}, {"Spec 8", type text}, {"Spec 9", type text}, {"Spec 10", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"    Hierarchy Path"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

    在此处输入图像描述

    • 4

  2. 在此处输入图像描述

    VBA宏：

    Sub TransposeRows()
    Dim wb As Workbook
    Dim Row As Integer, LastRowInput As Integer, LastColumnInput As Integer, LastRowOutput As Integer
    Dim Path As String, Spec As String

    Set wb = ThisWorkbook
    Set InputData = wb.Sheets("Input")
    Set OutputData = wb.Sheets("Output")

    LastRowInput = InputData.UsedRange.Rows.Count
    LastColumnInput = InputData.UsedRange.Columns.Count

    OutputData.Range("A:Z").Clear
    OutputData.Range("A1").Value = "Hierarchy Path"
    OutputData.Range("B1").Value = "Spec"

    For Row = 2 To LastRowInput
        Path = InputData.Cells(Row, 1).Value
        For Col = 2 To LastColumnInput
            Spec = InputData.Cells(Row, Col).Value
            If Spec <> "" Then
                LastRowOutput = OutputData.UsedRange.Rows.Count + 1
                OutputData.Cells(LastRowOutput, 1).Value = Path
                OutputData.Cells(LastRowOutput, 2).Value = Spec
            End If
        Next
    Next

    OutputData.Select
End Sub

    结果：

    在此处输入图像描述

    笔记：

    该脚本适用于空白规范。

    如果用户添加/删除行或列，它也可以处理它。

    • 3

相关问题