Como “unpivot” ou “reverse pivot” no Excel?

Question

Eu tenho dados parecidos com isto:

Id | Loc1 | Loc2 | Loc3 | Loc4
---+------+------+------+-----
1  | NY   | CA   | TX   | IL
2  | WA   | OR   | NH   | RI

E eu quero convertê-lo para isso:

Id | LocNum | Loc
---+--------+----
1  |   1    | NY
1  |   2    | CA
1  |   3    | TX
1  |   4    | IL
2  |   1    | WA
2  |   2    | OR
2  |   3    | NH
2  |   4    | RI

Qual é a maneira mais fácil de fazer isso no Excel 2007?

    

Answer 1

Você pode fazer isso com uma tabela dinâmica.

1. Crie uma "Tabela Dinâmica de Intervalos de Consolidação Múltipla". (Somente no Assistente de Tabela Dinâmica. Chamada com ALT + D , P no Excel 2007)
2. Selecione "Vou criar meus próprios campos de página".
3. Selecione seus dados.
4. Clique duas vezes no valor total geral - aquele na interseção de Fila Grand e Coluna Grand , todo o caminho no canto inferior direito da tabela dinâmica .

Você deverá ver uma nova planilha contendo todos os dados da tabela dinâmica, transpostos da maneira que você está procurando.

As tecnologias da Datapig fornecem instruções passo a passo que são realmente mais complicados do que o necessário - o exemplo dele transpõe apenas parte do conjunto de dados & usa a técnica de pivô combinada com TextToColumns . Mas tem muitas fotos.

Observe que uma tabela dinâmica agrupará os dados. Se você quiser desagrupar, a única maneira de fazer isso é copiar a tabela dinâmica e "colar especial" como valores. Você pode então preencher os espaços em branco com uma técnica como esta: link

    

Answer 2

Se os seus dados não forem uma Tabela Dinâmica do Excel, mas apenas dados, convém "desvinculá-los" com algum código simples do VBA. O código depende de dois intervalos nomeados, origem e destino. Fonte são os dados que você deseja desmembrar (com exceção dos cabeçalhos de coluna / linha, por exemplo, NY-RI na amostra) e Target é a primeira célula em que você deseja colocar o resultado.

Sub unPivot()
Dim oTarget As Range
Dim oSource As Range
Dim oCell As Range

Set oSource = Names("Source").RefersToRange
Set oTarget = Names("Target").RefersToRange

For Each oCell In oSource
    If oCell.Value <> "" Then
        oTarget.Activate
      ' get the column header
        oTarget.Value = oCell.Offset(-(oCell.Row - oSource.Row + 1), 0).Text 
      ' get the row header
         oTarget.Offset(0, 1).Value = oCell.Offset(0, _
           -(oCell.Column - oSource.Column + 1)).Text 
      ' get the value
        oTarget.Offset(0, 2).Value = oCell.Text 
      ' move the target pointer to the next row
        Set oTarget = oTarget.Offset(1, 0) 
    End If
Next
Beep
End Sub

    

Answer 3

Eu construí um add-in que permite que você faça isso, e isso facilita a adaptação a diferentes situações. Confira aqui: link

    

Answer 4

O melhor que eu já vi até agora é isso:

Id   LocNum  Loc
---------------------------------
1    1       =INDEX(Data,A6,B6)
1    2       =INDEX(Data,A7,B7)
1    3       =INDEX(Data,A8,B8)
1    4       =INDEX(Data,A9,B9)
2    1       =INDEX(Data,A10,B10)
2    2       =INDEX(Data,A11,B11)
2    3       =INDEX(Data,A12,B12)
2    4       =INDEX(Data,A13,B13)

Isso funciona, mas eu tenho que gerar os Id's e LocNum's manualmente. Se houver uma solução mais automatizada (além de escrever uma macro), por favor, deixe-me saber em uma resposta separada.

    

Answer 5

Existe uma boa solução no Excel 2010, só precisa de brincar um pouco com a tabela dinâmica.

Crie uma tabela dinâmica a partir dos seus dados com estas configurações:

• sem subtotais, sem totais gerais
• layout do relatório: formulário tabular, repita todos os marcadores de item
• adicione todas as colunas necessárias, mantenha as colunas que deseja transformar à direita
• para cada coluna que você deseja transformar: configurações de campo aberto - no layout & guia de impressão: selecione "Exibir marcadores de item no formulário de contorno" e marque as duas caixas de seleção abaixo dela
• copie sua tabela para um local separado (apenas valores)
• se você tiver células vazias em seus dados originais, filtre por valores vazios na coluna da extrema direita e exclua essas linhas (não filtre na tabela dinâmica, pois não funcionará como você precisa!)

Answer 6

Se as dimensões dos seus dados forem como na amostra fornecida na sua pergunta, o seguinte conjunto de fórmulas usando OFFSET deverá fornecer o resultado desejado:

Assumindo

1 | NY | CA | TX | IL

2 | WA | OR | NH | RI

estão no intervalo A2: E3, depois digite

=OFFSET($A$2,FLOOR((ROW(A2)-ROW($A$2))/4,1),0)

em F2, digamos e

=MOD(ROW(A2)-ROW($A$2),4)+1

no G2, digamos e

=OFFSET($B$2,FLOOR((ROW(B2)-ROW($B$2))/4,1),MOD(ROW(A2)-ROW($A$2),4))

em H2, digamos.

Copie essas fórmulas até onde for necessário.

Esta é a solução de fórmula mais fácil, pura e integrada que eu consigo imaginar.

    

Answer 7

Parece que você obteve a coluna "loc" (evidenciada pela sua primeira resposta) e agora precisa de ajuda para obter as outras duas colunas.

Sua primeira opção é simplesmente digitar as primeiras (digamos, 12) linhas nessas colunas e arrastar para baixo - acho que o Excel faz a coisa certa nesse caso (eu não tenho excel neste computador para testá-lo para certeza).

Se isso não funcionar, ou se você quiser algo mais programador-y, tente usar a função row (). Algo como "= Floor (row () / 4)" para a coluna ID e "= mod (row (), 4) +1" para a coluna LocNum.

    

Answer 8

Existe um utilitário de conversão de VBA paramétrico para desvincular ou reverter dados dinamizados de volta para uma tabela de banco de dados, consulte

link

    

Answer 9

Aqui está uma ótima ferramenta para dinamizar, normalizar uma Tabela Dinâmica.

Normalisieren von Pivot Tabellen

Espero que ajude.

    

Answer 10

@DaveParillo answer é a melhor resposta para uma única tabela de guias. Eu queria adicionar alguns extras aqui.

Para várias colunas antes das colunas unpivot

Este método não funciona.

O Youtube desinstala dados simples como a pergunta

Este é um vídeo do youtube mostrando como fazer isso de uma maneira simples. Eu pulei a parte sobre como adicionar o atalho e usei o atalho @devuxer que está na resposta do DaveParillo.

link

    

Answer 11

É muito simples do que isso. Basta clicar na opção "Transpor" em "Colar especial ..." para obter a transposição solicitada.