Ir para o conteúdo principal

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


Vídeo: transpor dados no Excel


Alternar colunas para linhas com Colar especial

Nota: Se seus dados estiverem em uma tabela do Excel, a opção Colar Transposição não estará disponível. Você deve converter a tabela em um intervalo primeiro clicando com o botão direito do mouse na tabela e selecionando mesa > Converter para intervalo no menu de contexto.

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!

Nota: Os dados transpostos são estáticos e independentes do conjunto de dados original. Se você fizer alterações nos dados originais, essas alterações não serão refletidas nos dados transpostos. Para vincular as células transpostas às originais, vá para a próxima seção.

(AD) Transponha as dimensões da tabela facilmente com o Kutools

Converter uma tabela cruzada (tabela bidimensional) em uma lista simples (lista unidimensional) ou vice-versa no Excel sempre exige muito tempo e esforço. No entanto, com o Kutools for Excel instalado, é Dimensões da tabela de transposição ferramenta irá ajudá-lo a fazer a conversão de forma rápida e fácil.

Kutools for Excel - Inclui mais de 300 funções práticas, tornando seu trabalho muito mais fácil. Obtenha uma avaliação gratuita de 30 dias com todos os recursos agora!


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:


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)
Observações:
  • Você deveria mudar A1: C4 ao seu intervalo de fonte real que você deseja transpor.
  • Se houver células vazias no intervalo original, o TRANSPOR função converteria as células vazias em 0s (zeros). Para se livrar dos resultados zero e manter as células em branco durante a transposição, você precisa aproveitar o IF função:
  • =TRANSPOSE(IF(A1:C4="","",A1:C4))

Resultado

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

Nota: Se você estiver usando Excel 365 ou Excel 2021, pressionando Entrar chave, o resultado se espalha automaticamente em quantas linhas e colunas forem necessárias. Certifique-se de que o intervalo de derramamento esteja vazio antes de aplicar a fórmula; caso contrário o #DERRAMAR erros são retornados.

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)))

Observações:
  • Você deveria mudar A1 para a célula mais à esquerda do seu intervalo de origem real que você irá transpor e mantenha os cifrões do jeito que estão. O cifrão ($) antes da letra da coluna e do número da linha indica uma referência absoluta, que mantém a letra da coluna e o número da linha inalterados quando você move ou copia a fórmula para outras células.
  • Se houver células vazias no intervalo original, a fórmula converterá as células vazias em 0s (zeros). Para se livrar dos resultados zero e manter as células em branco durante a transposição, você precisa aproveitar o IF função:
  • =IF(INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))=0,"",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.

Nota: A formatação original dos dados não é salva no intervalo transposto, você pode formatar as células manualmente se necessário.

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.

Nota: A formatação original dos dados é perdida; você pode restaurá-lo manualmente. Sinta-se à vontade para excluir o intervalo A6: C9 após o processo.

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.

Observações:
  • Se o intervalo de dados selecionado não estiver em uma tabela, um Criar a tabela caixa de diálogo irá aparecer; clique OK para criar uma tabela para ele.
  • Se você estiver usando o Excel 2013 ou 2010 e não conseguir encontrar Da tabela/intervalo na Data guia, você precisará baixá-lo e instalá-lo a partir do Microsoft Power Query para página do Excel. Uma vez instalado, vá para o Power Query guia, clique em Da tabela no Dados do Excel grupo.

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.

Observações:
  • Cabeçalhos de coluna adicionais são gerados na primeira linha, conforme mostrado acima. Para promover a primeira linha abaixo dos cabeçalhos para cabeçalhos de coluna, selecione uma célula dentro dos dados e clique em pergunta > Editar. Então, selecione Transformar > Use a primeira linha como cabeçalhos. Por fim, selecione Início > Fechar e carregar.
  • Se forem feitas alterações no conjunto de dados original, você pode atualizar os dados transpostos acima com essas alterações clicando no botão revisar ícone ao lado da mesa no Consultas e conexões painel ou clicando no revisar botão no pergunta aba.

(AD) Faixa de transformação com Kutools em poucos cliques

A Faixa de transformação O utilitário no Kutools para Excel pode ajudá-lo a transformar (converter) facilmente uma coluna vertical em várias colunas ou vice-versa, ou converter uma linha em várias linhas ou vice-versa.

Kutools for Excel - Inclui mais de 300 funções práticas, tornando seu trabalho muito mais fácil. Obtenha uma avaliação gratuita de 30 dias com todos os recursos agora!

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations