Ir para o conteúdo principal
Note: The other languages of the website are Google-translated. Back to English

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 mesa facilmente com Kutools

Converter uma tabela cruzada (tabela bidimensional) em uma lista plana (lista unidimensional) ou vice-versa no Excel sempre leva muito tempo e esforço. No entanto, com 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. obter uma 30teste gratuito completo de um dia 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)
notas:
  • 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)))

notas:
  • 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 o Power Query

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

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

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.

notas:
  • 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 no Data guia, você precisará baixá-lo e instalá-lo a partir do Página do Microsoft Power Query para Excel. Uma vez instalado, vá para o Consulta de energia guia, clique em Da tabela no Dados do Excel grupo.

Etapa 2: converter colunas em linhas com o Power Query

  1. Vou ao Transforme 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.

notas:
  • 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 Transforme > 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 utilidade em Kutools for 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. obter uma 30teste gratuito completo de um dia agora!

Comentários (0)
Ainda não há classificações. Seja o primeiro a avaliar!
Não há comentários postados aqui ainda