Início / computer / Perguntas / 1519393
Accepted
User1974
Asked: 2020-01-23 14:40:50 +0800 CST

Colunas não dinâmicas/transpostas (sem espaços em branco)

  • 772

Eu tenho uma tabela de hierarquia de ativos no Excel:

insira a descrição da imagem aqui

Para fins de teste, a tabela pode ser copiada/colada no Excel usando o texto abaixo:

Dica: Copie o texto do modo de edição Stack Overflow - em vez de copiar do modo de visualização (o texto do modo de edição será analisado corretamente no 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

Pergunta:

Para cada Caminho de Hierarquia, desejo:

  • Transponha as especificações
  • Preencha o caminho da hierarquia para cada especificação

Observação: o número de especificações varia de acordo com o caminho da hierarquia. Eu gostaria que o número de linhas correspondesse ao número de especificações. Em outras palavras, eu não gostaria de especificações em branco na saída.

Exemplo:

insira a descrição da imagem aqui

Existe uma maneira de fazer isso automaticamente no Excel 2016?

microsoft-excel microsoft-excel-2016

2 respostas

  1. Best Answer

    Você pode usar o Power Query. Em seguida, seus usuários podem atualizar as coisas selecionando a opção Atualizar na guia Dados.

    Em PQ, tudo que você precisa fazer é

    • Selecione a Hierarchy Pathcoluna
    • **UN**pivot other columns
    • Exclua a coluna Atributo resultante

    Todas as etapas podem ser executadas na interface do usuário, mas aqui está o

    Código 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"

    insira a descrição da imagem aqui

    • 4

  2. insira a descrição da imagem aqui

    Macro 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

    Resultado:

    insira a descrição da imagem aqui

    Notas:

    O script acomoda especificações em branco.

    Ele também pode lidar com isso se o usuário adicionar/remover linhas ou colunas.

    • 3

relate perguntas