5 formas de transpor dados no Excel (tutorial passo a passo)

Transpor dados significa alterar a orientação de uma matriz, convertendo as suas linhas em colunas ou vice-versa. Neste tutorial, partilhamos consigo cinco métodos distintos para inverter a orientação de uma matriz e obter exatamente o resultado que procura.

Converta colunas em linhas com Colar Especial

Transpor e Vincular Dados para a origem

• Utilize a função TRANSPOR
• Utilize as funções INDIRECTO, ENDEREÇO, COLUNA e LINHA
• Utilize o Colar Especial e Localizar e Substituir
• Utilize a função Power Query

Transferir exemplos de dados transpostos (.xlsx)

Vídeo: Transpor dados no Excel

Converta colunas em linhas com Colar Especial

Passo 1: Copie o intervalo a transpor

Selecione o intervalo de células em que pretende inverter linhas e colunas e, em seguida, prima «Ctrl» + «C» para copiar esse intervalo.

Passo 2: Selecione a opção Colar Transposição

Clique com o botão direito do rato na primeira célula do intervalo de destino e, em seguida, selecione o ícone «Transpor» (em «Colar Especial») no menu de contexto.

Resultado

Pronto! O intervalo foi transposto num instante!

Transpor e Vincular Dados à origem

Para inverter dinamicamente a orientação de um intervalo (converter colunas em linhas e vice-versa) e ligar o resultado invertido ao conjunto de dados original, pode utilizar qualquer um dos métodos abaixo:

• «Função TRANSPOR»(Fácil de utilizar, mas os resultados não são editáveis)
• «Funções INDIRECTO, ENDEREÇO, COLUNA e LINHA»(Fórmula extensa, mas permite alterar os resultados)
• «Colar Especial e Localizar e Substituir»(Complexo, mas fácil de compreender)
• «Power Query»(Fácil, com resultados formatados numa tabela)

Converter linhas em colunas utilizando a função TRANSPOR

Para converter linhas em colunas e vice-versa com a função «TRANSPOR», siga os passos abaixo.

Passo 1: Selecione o mesmo número de células vazias que o conjunto original, mas na outra direção

Dica: Ignore este passo se estiver a utilizar o Excel 365 ou o Excel 2021.

Por exemplo, se a sua tabela original estiver no intervalo A1:C4 — ou seja, com 4 linhas e 3 colunas — a tabela transposta terá 3 linhas e 4 colunas. Nesse caso, selecione um bloco de 3 linhas por 4 colunas em células vazias.

Passo 2: Introduza a fórmula TRANSPOR

Introduza a fórmula abaixo na Barra de Fórmulas e, em seguida, prima «Ctrl» + «Shift» + «Enter» para obter o resultado.

Dica: Prima apenas «Enter» se utilizar o Excel 365 ou o Excel 2021.

=TRANSPOSE(A1:C4)

Resultado

As linhas transformam-se em colunas e as colunas convertem-se em linhas.

Rodar dados utilizando as funções INDIRECTO, ENDEREÇO, COLUNA e LINHA

Embora a fórmula anterior seja bastante intuitiva e fácil de usar, tem a desvantagem de não permitir editar ou eliminar quaisquer células na tabela rodada. Por isso, apresentamos uma solução mais flexível, baseada nas funções «INDIRECTO», «ENDEREÇO», «COLUNA» e «LINHA». Suponha que a sua tabela original se encontra no intervalo A1:C4; para transformar colunas em linhas mantendo os dados rodados dinamicamente ligados ao conjunto original de Dados de Origem, siga os passos abaixo.

Passo 1: Introduza a fórmula

Introduza a fórmula abaixo na célula superior esquerda do intervalo de destino (no nosso caso, A6) e prima «Enter»:

=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))

Passo 2: Copie a fórmula para as células à direita e abaixo

Selecione a célula com a fórmula e arraste a alça de preenchimento — o pequeno quadrado verde no canto inferior direito da célula — para baixo e, em seguida, para a direita, até cobrir o número necessário de linhas e colunas.

Resultado

As colunas e linhas são invertidas instantaneamente.

Converter colunas em linhas com Colar Especial e Localizar e Substituir

Alguns passos adicionais com o método Colar Especial, combinado com a funcionalidade Localizar e Substituir, permitem-lhe transpor os dados mantendo as células de origem ligadas às transpostas.

Passo 1: Copie o intervalo a transpor

Selecione o intervalo de células que pretende transpor e, em seguida, prima «Ctrl» + «C» para copiá-lo.

Passo 2: Aplique a opção Colar Ligação

1. Clique com o botão direito do rato numa célula vazia e, em seguida, selecione «Colar Especial».

   

2. Clique no botão «Colar Ligação».

   

Obterá o resultado apresentado abaixo:

Passo 3: Localizar e Substituir os sinais de igual (=) do resultado da Colagem com Ligação

1. Selecione o intervalo de resultados (A6:C9), prima «Ctrl» + «H» e, na caixa de diálogo Localizar e Substituir, substitua «=» por «@EO» (ou por quaisquer caracteres que não existam no intervalo selecionado).

   

2. Clique em «Substituir Tudo» e, em seguida, feche a caixa de diálogo. A seguir, é apresentado o aspeto final dos dados.

   

Passo 4: Transponha o resultado da Colagem com Ligação após substituição

Selecione o intervalo (A6:C9) e prima «Ctrl» + «C» para copiá-lo. Clique com o botão direito do rato numa célula vazia (escolhi A11) e selecione o ícone «Transpor» em Colar Especial para colar o resultado transposto.

Passo 5: Restaure os sinais de igual (=) para ligar o resultado transposto aos dados originais

1. Mantenha os dados resultantes transpostos A11:D13 selecionados e, em seguida, prima «Ctrl» + «H» para substituir «@EO» por «=» (o inverso do passo 3).

   

2. Clique em «Substituir Tudo» e, de seguida, feche a caixa de diálogo.

Resultado

Os dados foram transpostos e vinculados às células originais.

Transpor e Vincular Dados à origem com Power Query

O Power Query é uma poderosa ferramenta de automação de dados que lhe permite transpor facilmente os seus dados no Excel. Basta seguir os passos abaixo para concluir esta tarefa:

Passo 1: Selecione o intervalo a transpor e abra o Editor do Power Query

Selecione o intervalo de dados que pretende transpor. Em seguida, no separador «Dados», no grupo «Obter e Transformar Dados», clique em «A partir de Tabela/Intervalo».

Passo 2: Converta colunas em linhas com Power Query

1. Aceda ao separador «Transformar» e, no menu pendente «Utilizar Primeira Linha como Cabeçalhos», selecione «Utilizar Cabeçalhos como Primeira Linha».

   

2. Clique em «Transpor».

   

Passo 3: Guarde os dados transpostos numa folha

No separador «Ficheiro», clique em «Fechar e Carregar» para fechar a janela do «Editor do Power Query» e criar uma nova folha de cálculo com os dados transpostos.

Resultado

Os dados transpostos são convertidos numa tabela numa nova folha de cálculo.

Artigos relacionados

• Como transpor rapidamente intervalos no Excel, ignorando as células vazias?
• Quando colamos um intervalo como transposto, as células vazias também são coladas. Contudo, por vezes, pretendemos apenas transpor os intervalos ignorando as células vazias, tal como mostrado na imagem seguinte. Existirá alguma forma de transpor rapidamente intervalos e ignorar células vazias no Excel?
• Como transpor cada 5 ou n linhas de uma coluna em múltiplas colunas?
• Imagine que tem dados longos na coluna A e pretende transpor cada bloco de 5 linhas dessa coluna para múltiplas colunas — por exemplo, mover A1:A5 para C6:G6, A6:A10 para C7:G7 e assim sucessivamente, tal como ilustrado na imagem seguinte. Como pode realizar esta tarefa no Excel sem recorrer a cópias e colagens repetidas?
• Como transpor ou converter colunas e linhas numa única coluna?
• Ao trabalhar com uma folha de cálculo do Excel, pode surgir-lhe esta dúvida: como converter ou transpor um conjunto de dados para uma única coluna? (Veja as imagens seguintes.) Apresento-lhe agora três truques rápidos para resolver este desafio.
• Como transpor ou converter colunas e linhas numa única linha?
• Como juntar múltiplas linhas e colunas numa única linha longa? Talvez lhe pareça fácil, pois pode copiá-las uma a uma e juntá-las manualmente numa linha. Contudo, isto pode ser moroso e tedioso se existirem centenas de linhas e colunas. Explicarei agora alguns truques rápidos para resolver esta situação.