Excel: Como transformar duas dimensões verticais em uma dimensão vertical e uma horizontal

1

Eu tenho uma tabela que se parece com isso:

  col1 col2  col3                        table1
---------------
01| 1   A  value1A
02| 1   B  value1B
03| 1   Z  value1Z
04| 2   Z  value2Z
05| 3   A  value3A
06| 3   B  value3B
07| 3   Z  value3Z
08| 4   A  value4A
09| 4   B  value4B
10| 4   Z  value4Z
11| 5   Z  value5Z

E eu gostaria de obter algo assim:

      A       B       Z                  table2
 -------------------------
1| value1A value1B value1Z
2|                 value2Z
3| value3A value3B value3Z
4| value4A value4B value4Z
5|                 value5Z

Onde a fórmula na célula A1 de table2 calcula valueA1 através dos valores localizados nas células A1 e B1 da table1 e onde a segunda coluna na table1 às vezes é preenchida apenas com um valor (sempre Z nesse caso). Se não for preenchido com um único valor, sempre serão 3 valores (A, B e Z).

Até agora não encontrei nada conveniente.

EDIT: como mencionado nos comentários abaixo, você também deve saber que value1Z é, na verdade, a soma de value1A e value1B.

EDIT2: prefiro não usar uma tabela dinâmica. No meu caso muito específico, isso não é muito adequado.

    
por Guillaume 16.11.2017 / 14:48

2 respostas

2

Experimente esta macro curta:

Sub MAIN2()
    Dim r As Range, v, numb, letr
    Dim i As Long, j As Long

    Columns(1).SpecialCells(2).Copy Cells(2, 4)
    Columns(4).RemoveDuplicates Columns:=1, Header:=xlNo
    Columns(2).SpecialCells(2).Copy Cells(2, 5)
    Columns(5).RemoveDuplicates Columns:=1, Header:=xlNo

    Set r = Columns(5).SpecialCells(2)
    r.Copy
    Cells(1, 5).PasteSpecial Transpose:=True
    r.Clear

    For Each r In Columns(3).SpecialCells(2)
        v = r.Value
        numb = r.Offset(0, -2).Value
        lett = r.Offset(0, -1).Value

        For i = 2 To Rows.Count
            If Cells(i, 4) = numb Then Exit For
        Next i
        For j = 5 To Columns.Count
            If Cells(1, j) = lett Then Exit For
        Next j

        Cells(i, j) = v
    Next r

End Sub

Asmacrossãomuitofáceisdeinstalareusar:

  1. ALT-F11exibeajaneladoVBE
  2. ALT-IALT-Mabreumnovomódulo
  3. coleomaterialefecheajaneladoVBE

Sevocêsalvarapastadetrabalho,amacroserásalvacomela.SevocêestiverusandoumaversãodoExcelposteriora2003,deverásalvaroarquivocomo.xlsmemvezde.xlsx

Pararemoveramacro:

  1. abrirajaneladoVBEcomoacima
  2. limpeocódigo
  3. fecheajaneladoVBE

ParausaramacrodoExcel:

  1. ALT-F8
  2. Selecioneamacro
  3. ToqueemEXECUTAR

Parasabermaissobremacrosemgeral,consulte:

link

e

link

As macros devem estar ativadas para que isso funcione!

    
por 16.11.2017 / 16:17
1

Você pode usar apenas uma tabela dinâmica.

Edit: aqui está um exemplo trabalhado. Não tenho certeza porque isso não se adequa a sua exigência? link

Você pode alterar facilmente a ordem das colunas nas tabelas dinâmicas ajustando manualmente ou usando listas personalizadas.

    
por 16.11.2017 / 15:20