取消透视/转置列（无空白）

Question

我在 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 中自动执行此操作？

Answer 1

您可以使用 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"

Answer 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

---

结果：

---

笔记：

该脚本可容纳空白规格。

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