5 maneiras de transpor dados no Excel (Tutorial passo a passo)

Transpor dados significa alterar a orientação de uma matriz convertendo suas linhas em colunas ou vice-versa. Neste tutorial, compartilharemos com você cinco métodos diferentes para mudar a orientação de uma matriz e alcançar o resultado desejado.

Alterne colunas para linhas com Colar Especial

Transponha e vincule os dados à fonte

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

Baixe exemplos de transposição de dados (.xlsx)

Vídeo: Transpor dados no Excel

Alterne colunas para linhas com Colar Especial

Passo 1: Copie o intervalo a ser transposto

Selecione o intervalo de células onde deseja alternar linhas e colunas e pressione "Ctrl" + "C" para copiar o intervalo.

Passo 2: Selecione a opção Colar Transpor

Clique com o botão direito na primeira célula do intervalo de destino e clique no ícone "Transpor" (em "Opções de Colagem") no menu de contexto.

Resultado

Voilà! O intervalo é transposto em pouco tempo!

Transponha e vincule os dados à fonte

Para alternar dinamicamente a orientação de um intervalo (alterar colunas para linhas e vice-versa) e conectar o resultado alternado ao conjunto de dados original, você pode usar qualquer um dos métodos abaixo:

• "Função TRANSPOR" (Fácil de usar, mas os resultados não são editáveis)
• "Funções INDIRETO, ENDEREÇO, COLUNA e LINHA" (Fórmula extensa, mas permite fazer alterações nos resultados)
• "Colar Especial e Localizar e Substituir" (Complexo, mas fácil de entender)
• "Power Query" (Fácil com resultados formatados em uma tabela)

Alterne linhas para colunas usando a função TRANSPOR

Para transformar 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 em branco que o conjunto original de células, mas em outra direção

Dica: Pule esta etapa se estiver usando o Excel 365 ou o Excel 2021.

Suponha que sua tabela original esteja no intervalo A1:C4, o que significa que a tabela tem 4 linhas e 3 colunas. Assim, a tabela transposta terá 3 linhas e 4 colunas. Isso significa que você deve selecionar 3 linhas e 4 colunas de células em branco.

Passo 2: Insira a fórmula TRANSPOR

Digite a fórmula abaixo na barra de fórmulas e pressione "Ctrl" + "Shift" + "Enter" para obter o resultado.

Dica: Pressione "Enter" se você for usuário do Excel 365 ou Excel 2021.

=TRANSPOSE(A1:C4)

Resultado

As linhas são transformadas em colunas, e as colunas são convertidas em linhas.

Gire dados usando as funções INDIRETO, ENDEREÇO, COLUNA e LINHA

Embora a fórmula acima seja bastante fácil de entender e usar, a desvantagem é que você não pode editar ou excluir nenhuma célula na tabela rotacionada. Então, vou introduzir uma fórmula usando as funções "INDIRETO", "ENDEREÇO", "COLUNA" e "LINHA". Suponha que sua tabela original esteja no intervalo A1:C4, para realizar uma transformação de coluna para linha e manter os dados rotacionados conectados ao conjunto de dados de origem, siga os passos abaixo.

Passo 1: Insira a fórmula

Insira a fórmula abaixo na célula superior esquerda do intervalo de destino (A6 no nosso caso) e pressione "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 da fórmula e arraste sua alça de preenchimento (o pequeno quadrado verde no canto inferior direito da célula) para baixo e depois para a direita, conforme o número necessário de linhas e colunas.

Resultado

As colunas e linhas são trocadas imediatamente.

Converta colunas em linhas com Colar Especial e Localizar e Substituir

Mais alguns passos com o método Colar Especial junto com o recurso Localizar e Substituir permitem que você transponha os dados enquanto vincula as células de origem às transpostas.

Passo 1: Copie o intervalo a ser transposto

Selecione o intervalo de células que deseja transpor e pressione "Ctrl" + "C" para copiar o intervalo.

Passo 2: Aplique a opção Colar Vínculo

1. Clique com o botão direito em uma célula em branco e clique em "Colar Especial".

   

2. Clique no botão "Colar Vínculo".

   

Você obterá o resultado conforme mostrado abaixo:

 Passo 3: Localize e substitua os sinais de igual (=) do resultado do Colar Vínculo

1. Selecione o intervalo de resultados (A6:C9) e pressione "Ctrl" + "H", e substitua "=" por "@EO" (ou qualquer caractere(s) que não exista no intervalo selecionado) na caixa de diálogo Localizar e Substituir.

   

2. Clique em "Substituir Tudo" e feche a caixa de diálogo. O seguinte mostra como os dados ficarão.

   

Passo 4: Transponha o resultado substituído do Colar Vínculo

Selecione o intervalo (A6:C9) e pressione "Ctrl" + "C" para copiá-lo. Clique com o botão direito em uma célula em branco (neste caso, selecionei A11) e escolha o ícone "Transpor" nas Opções de Colagem para colar o resultado transposto.

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

1. Mantenha os dados do resultado transposto A11:D13 selecionados e pressione "Ctrl" + "H", e substitua "@EO" por "=" (o oposto do "passo 3").

   

2. Clique em "Substituir Tudo" e feche a caixa de diálogo.

Resultado

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

Transponha e vincule os dados à fonte com Power Query

O Power Query é uma poderosa ferramenta de automação de dados que permite transpor dados facilmente no Excel. Você pode concluir a tarefa seguindo os passos abaixo:

Passo 1: Selecione o intervalo a ser transposto e abra o Editor de Consultas do Power Query

Selecione o intervalo de dados a ser transposto. Em seguida, na guia "Dados", no grupo "Obter & Transformar Dados", clique em "De Tabela/Intervalo".

Passo 2: Converta colunas em linhas com Power Query

1. Vá para a guia "Transformar". No menu suspenso "Usar Primeira Linha como Cabeçalhos", selecione "Usar Cabeçalhos como Primeira Linha".

   

2. Clique em "Transpor".

   

Passo 3: Salve os dados transpostos em uma planilha

Na guia "Arquivo", clique em "Fechar & Carregar" para fechar a janela do "Editor de Consultas" e criar uma nova planilha para carregar os dados transpostos.

Resultado

Os dados transpostos são convertidos em uma tabela em uma nova planilha criada.

Artigos relacionados

• Como transpor intervalos rapidamente e ignorar células em branco no Excel?
• Quando colamos um intervalo como transposto, as células em branco também serão coladas. No entanto, às vezes, queremos apenas transpor os intervalos ignorando as células em branco, conforme mostrado na captura de tela abaixo. Existem maneiras de transpor rapidamente intervalos e ignorar células em branco no Excel?
• Como transpor cada 5 ou n linhas de uma coluna para várias colunas?
• Suponha que você tenha muitos dados na coluna A e agora deseja transpor cada 5 linhas da coluna A para várias colunas, como transpor A1:A5 para C6:G6, A6:A10 para C7:G7, e assim por diante, conforme mostrado na captura de tela abaixo. Como lidar com essa tarefa sem copiar e colar repetidamente no Excel?
• Como transpor/converter colunas e linhas em uma única coluna?
• Ao usar a planilha do Excel, às vezes, você encontrará esse problema: como converter ou transpor um intervalo de dados em uma única coluna? (Veja as capturas de tela abaixo:) Agora, apresento três truques rápidos para resolver esse problema.
• Como transpor/converter colunas e linhas em uma única linha?
• Como juntar várias linhas e colunas em uma única linha longa? Talvez pareça fácil para você, porque você pode copiá-las uma a uma e uni-las em uma linha manualmente. No entanto, isso pode ser demorado e tedioso se houver centenas de linhas e colunas. Aqui falarei sobre alguns truques rápidos para resolver isso.