![取消透视/转置列(无空白)](https://linux22.com/image/1614462/%E5%8F%96%E6%B6%88%E9%80%8F%E8%A7%86%2F%E8%BD%AC%E7%BD%AE%E5%88%97%EF%BC%88%E6%97%A0%E7%A9%BA%E7%99%BD%EF%BC%89.png)
我在 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 中自动执行此操作?
答案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"
答案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
结果:
笔记:
该脚本可容纳空白规格。
如果用户添加/删除行或列,它也可以处理。