Transpor tabela de estilo matriz para 3 colunas no Excel

Question

Eu tenho uma tabela no estilo matriz no Excel, onde B1: Z1 são títulos de coluna e A2: A99 são títulos de linha. Eu gostaria de converter esta tabela para uma tabela de 3 colunas (título da coluna, cabeçalho da linha, valor da célula). Não importa em que ordem a nova tabela é.

    A   B   C   D            A   B   C            A   B   C   
 1      H1  H2  H3        1  H1  R1  V1        1  H1  R1  V1
 2   R1 V1  V2  V3   =>   2  H1  R2  V4   or   2  H2  R1  V2
 3   R2 V4  V5  V6        3  H1  R3  V7        3  H3  R1  V3
 4   R3 V7  V8  V9        4  H2  R1  V2        4  H1  R2  V4
                          5  H2  R2  V5        5  H2  R2  V5
                          6  H2  R3  V8        6  H3  R2  V6
                          7  H3  R1  V3        7  H1  R3  V7
                          8  H3  R2  V6        8  H2  R3  V8
                          9  H3  R3  V9        9  H3  R3  V8

Eu tenho jogado com a função OFFSET para criar a tabela inteira, mas eu sinto que uma combinação de TRANSPOSE e V / HLOOKUP é necessária.

Obrigado

EDITAR

Eu consegui chegar com as fórmulas corretas. Se os dados estiverem na Planilha1, como no meu exemplo acima, as fórmulas vão para a Planilha2:

[A1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99), OFFSET(Sheet1!$A$1,0,IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"")

[B1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),0),"")

[C1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"")

As fórmulas são limitadas a B1: Z1 para os títulos e A2: A99 para as linhas (estas podem ser aumentadas até o máximo, se necessário). A fórmula COUNTA () retorna o número de células que realmente possuem valores, o que limita o número de linhas retornadas aos títulos * linhas. Caso contrário, as fórmulas poderiam continuar infinitas por causa da função MOD.

    

Answer 1

Eu gosto dessa pergunta:)

experimente essas funções. insira-os em h0, r0, v0 e arraste-os para baixo

   h   r   v
0 (a) (b) (c)
1 | | | | | |
2 \ / \ / \ /
3  v   v   v

onde:
h = rótulo de cabeçalho
r = rótulo de linha
v = rótulo de valor

(a) =OFFSET($A$1,INT(H2/99)+1,MOD(H2,99)+1,1,1)
(b) =OFFSET($A$1,INT(H2/99)+1,0,1,1)
(c) =OFFSET($A$1,0,MOD(H2,99)+1,1,)

em que H2 é uma coluna com valores: 0,1,2,3 ...

    

Answer 2

Eu vi uma versão um pouco mais generalizada da solução fornecida pelo @Dick Kusleika no StackOverflow há algumas semanas: Converte linha com colunas de dados na coluna com várias linhas no Excel 2007 . Parece não ter referências a linhas individuais (usando ROW() ), mas ainda requer que o número de colunas seja codificado.