transpondo grandes conjuntos de dados com estrutura definida usando fórmula

Question

Estou tentando encontrar uma maneira de transpor grandes conjuntos de dados (a estrutura é mostrada abaixo) de uma determinada maneira e imaginando como implementá-los no Excel usando uma fórmula para evitar transposição copy-paste demorada e propensa a erros!

Eu quero fazer a transformação dos dados em sheet A em uma folha separada ( B ). Eu essencialmente tenho medições triplicadas múltiplas características de diferentes grupos. O número de grupos pode variar, mas as medições serão sempre em triplicado. Obrigado por qualquer ajuda que você possa dar

    

Answer 1

Você pode brincar um pouco com as fórmulas INDEX , COLUMN e ROW e alguma aritmética. INDEX procurará o valor de uma célula por você, dado um número de linha e coluna. Para uma transposição simples, você só precisa do COLUMN da célula atual como a linha e do ROW da célula atual como a coluna. No entanto, sua operação é um pouco mais difícil do que uma simples transposição e, portanto, precisamos de alguma aritmética. Com algumas tentativas e erros, cheguei à fórmula abaixo.

Contanto que a Folha A contenha os dados no canto superior esquerdo (ou seja, B2 contenha o valor 5), esta fórmula na Folha B funcionará:

=INDEX('Sheet A'!$1:$1048576, MOD(COLUMN()+1,3)+3*ROW()-4, 1+(COLUMN()+1)/3)

Os números das colunas começam em 1, então o MOD(COLUMN()+1,3) dá 0 para as colunas A do Grupo n , 1 para B e 2 para C. A parte 3*ROW() corresponde às três linhas para cada Grupo n na Folha A. O 1+(COLUMN()+1)/3 mapeia todas as colunas Cat 1 na Folha B para a coluna 2 na Folha A, Cat 2 para a coluna 3, etc.