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 obter o resultado desejado.

Alternar colunas para linhas com Colar especial

Transpor e vincular dados à fonte

• Com função TRANSPOR
• Com as funções INDIRETO, ENDEREÇO, COLUNA e LINHA
• Com colar especial e localizar e substituir
• Com o Power Query

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

Vídeo: transpor dados no Excel

Alternar 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.

Etapa 2: selecione a opção Colar transposição

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

Resultado

Voilá! O intervalo é transposto em nenhum momento!

Transpor e vincular dados à fonte

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

• Função TRANSPOSE (Fácil de usar, mas os resultados não são editáveis)
• Funções INDIRETO, ENDEREÇO, COLUNA e LINHA (Fórmula grande, 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)

Alterar linhas para colunas usando a função TRANSPOSE

Para transformar linhas em colunas e vice-versa com o TRANSPOR função, faça o seguinte.

Etapa 1: selecione o mesmo número de células em branco que o conjunto original de células, mas em outra direção

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

Digamos 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. O que significa que você deve selecionar 3 linhas e 4 colunas de células em branco.

Etapa 2: insira a fórmula TRANSPOSE

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

Dica: Press Entrar se você for usuário do Excel 365 ou do Excel 2021.

=TRANSPOSE(A1:C4)

Resultado

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

Gire os 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 girada. Então, vou introduzir uma fórmula usando INDIRETO, ENDEREÇO, COLUNA e LINHA funções. Digamos que sua tabela original esteja no intervalo A1: C4, para executar uma transformação de coluna em linha e manter os dados girados conectados ao conjunto de dados de origem, siga as etapas 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 Entrar:

=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 a direita e abaixo das células

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 para a direita até quantas linhas e colunas forem necessárias.

Resultado

As colunas e linhas são trocadas imediatamente.

Converta colunas em linhas com Colar especial e Localizar e substituir

Mais algumas etapas com o método Colar especial junto com o recurso Localizar e substituir permitem que você transponha 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.

Etapa 2: aplique a opção Colar link

1. Clique com o botão direito do mouse em uma célula em branco e clique em colar especial.

   

2. Clique em colar vínculo.

   

Você obterá o resultado conforme mostrado abaixo:

Etapa 3: Localize e substitua os sinais de igual (=) do resultado Colar link

1. Selecione o intervalo de resultados (A6: C9) e pressione Ctrl + He, em seguida, substitua = com @EO (ou qualquer caractere(s) que não exista(m) no intervalo selecionado) no Localizar e substituir diálogo.

   

2. Clique em substituir tudoe, em seguida, feche a caixa de diálogo. A seguir, mostramos como serão os dados.

   

Etapa 4: transpor o resultado de Colar link substituído

Selecione o intervalo (A6: C9) e pressione Ctrl + C para copiá-lo. Clique com o botão direito do mouse em uma célula em branco (aqui selecionei A11) e selecione o Transpor ícone da Opções de colagem para colar o resultado transposto.

Etapa 5: obter sinais de igual (=) de volta para vincular o resultado transposto aos dados originais

1. Manter os dados do resultado transposto A11: D13 selecionado e, em seguida, pressione Ctrl + He substituir @EO com = (o oposto de step 3).

   

2. Clique em substituir tudoe, em seguida, feche a caixa de diálogo.

Resultado

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

Transponha e vincule dados à fonte com Power Query

Power Query é uma poderosa ferramenta de automação de dados que permite transpor facilmente dados no Excel. Você pode realizar o trabalho seguindo as etapas abaixo:

Passo 1: Selecione o intervalo a ser transposto e abra o Power Query editor

Selecione o intervalo de dados a ser transposto. E então, no Data guia, no Obtenha e transforme dados grupo, clique em Da tabela/intervalo.

Etapa 2: converter colunas em linhas com Power Query

1. Vou ao Transformar aba. No Use a primeira linha como cabeçalhos menu suspenso, selecione Usar cabeçalhos como primeira linha.

   

2. Clique em Transpor.

   

Etapa 3: Salve os dados transpostos em uma planilha

No Envie o guia, clique em Fechar e carregar para fechar a janela do Power Editor e criar uma nova planilha para carregar os dados transpostos.

Resultado

Os dados transpostos são convertidos em uma tabela em uma planilha recém-criada.

Artigos relacionados

• Como transpor intervalos rapidamente e pular 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 a captura de tela mostrada abaixo. Existem maneiras de transpor rapidamente os intervalos e pular os espaços em branco no Excel?
• Como transpor a cada 5 ou n linhas de uma coluna para várias colunas?
• Suponha que você tenha dados longos na coluna A e agora queira transpor a 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 como seguinte captura de tela mostrada. Como você poderia 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á este problema: como você poderia converter ou transpor um intervalo de dados em uma única coluna? (Veja as capturas de tela a seguir :) Agora, apresento três truques rápidos para você resolver esse problema.
• Como transpor / converter colunas e linhas em uma única linha?
• Como unir várias linhas e colunas em uma única linha longa? Talvez pareça fácil para você, porque você pode copiá-los um por um e juntá-los em uma linha manualmente. No entanto, isso pode ser demorado e tedioso se houver centenas de linhas e colunas. Aqui vou falar sobre alguns truques rápidos para resolvê-lo.