De-pivot excel planilha

Question

Usando o Excel 2003 Posso ...

Com base em uma planilha desse layout, as permissões são: Read = R, Write = W e Sem permissão = X

  Users     Perm1    Perm2   Perm3 ...    ..... PermX
  User1       R        W        X  ...    .....   R 
  User2       X        W        X  ...    .....   W
  User3       R        R        X  ...    .....   X
  ....
  ....

Pode ser achatado para isso

  Users     Perms    Value
  User1     Perm1      R
  User1     Perm2      W
  User1     Perm3      X
  ....
  ....
  User1     PermX      R
  User2     Perm1      X
  User2     Perm2      W
  User2     Perm3      X
  ....
  ....
  User2     PermX      W
  User3     Perm1      R
  User3     Perm2      R
  User3     Perm3      X
  ....
  ....
  User3     PermX      X

Existe uma maneira fácil de fazer isso?

    

Answer 1

Eu usaria a função Index:

Digamos que sua fonte de dados esteja na planilha1 em A1: D20. Em seguida, na planilha2, use essas fórmulas:

Column A: "Row": =1, =1, =1, =A1+1, drag down
Column B: "Column": =1, =2, =3, =A1, drag down
Column C: "User": =INDEX(Sheet1!$A$2:$A$20,A2)
Column D: "Perms": =INDEX(Sheet1!$B$1:$D$1,B2)
Column E: "Value": INDEX(Sheet1!$B$2:$D$20,A2,B2)

Que deve ficar assim:

Row Column User  Perm  Value 
1   1      User1 Perm1 R     
1   2      User1 Perm2 W     
1   3      User1 Perm3 X     
2   1      User2 Perm1 X     
2   2      User2 Perm2 W     
2   3      User2 Perm3 X     
3   1      User3 Perm1 R     
3   2      User3 Perm2 R     
3   3      User3 Perm3 X     

Espero que isso ajude.

EDITAR:

As duas primeiras colunas são projetadas para percorrer todas as possibilidades de combinação de colunas de linha. Digamos que você tenha 40 tipos de permissão diferentes e 500 usuários:

Row Column
1   1
1   2
1   3
1   4
...
1   39
1   40
2   1
2   2
2   3
2   4
...
2   39
2   40
3   1
...
500 40

Se você quiser usar fórmulas que você pode arrastar até o fim para isso, você pode fazer isso:

Column A: "Count": =1, =A1+1
Column B: "Row": =INT((A1-1)/5)+1
Column C: "Column": =MOD(A1-1,40)+1  (where you change 40 to the number of permissions you have)
Column D: "User": =INDEX(Sheet1!$A$2:$A$20,B2)
Column E: "Perms": =INDEX(Sheet1!$B$1:$D$1,C2)
Column F: "Value": INDEX(Sheet1!$B$2:$D$20,B2,C2)

    

Answer 2

Ok, eu respondi a essa pergunta seis anos depois de ser perguntado ... E agora estou usando o Excel 2013. Tanto quanto eu entendo, este método deve aplicar-se ao Excel 2007, 2010, 2013. Não tenho certeza se ele funciona no Excel 2016 embora.

Primeiro, abra o Assistente de tabela dinâmica e gráfico dinâmico por Alt D P . Está oculto desde o Excel 2007 e só pode ser aberto através das teclas de atalho.

Selecione"Vários intervalos de consolidação" e clique em próximo.

Selecione"Criar um campo de página única para mim" e clique em próximo.

CliqueemIntervaloeselecioneointervaloaser"desmipitado" (por exemplo, Folha1! $ A $ 1: $ D $ 4). Clique em Concluir.

Depois de fechar o assistente, você verá uma tabela dinâmica criada assim:

Cliqueduasvezesnacélula"Grand Total" (ou seja, E8 no meu exemplo) e você receberá uma tabela "De-pivoted" em uma nova planilha como esta:

Não é uma solução perfeita, mas definitivamente é útil sem ter que escrever nenhuma fórmula, e a melhor solução até agora para mim.

    

Answer 3

Eu escrevi um programa unpivot de tabela em javascript. Veja aqui . Explicando o fluxo de trabalho:

1. Converter tabela para o dicionário json usando um fork da ferramenta "Mr.Data Converter" de Shan Carter. (não foi permitido postar mais links aqui)
2. Faz um loop pelo JSON para listar a tabela linearmente.

Veja este gráfico para explicação