Guia de transposição do Excel: Converter linhas em colunas e vice-versa

A transposição de dados no Excel permite que você inverta a orientação dos dados, transformando linhas em colunas e colunas em linhas. Essa técnica é particularmente útil quando você precisa reestruturar o conjunto de dados para melhorar a análise, a visualização ou a geração de relatórios.

Se você estiver preparando dados para uma tabela dinâmica, criando uma exibição de resumo ou simplesmente precisando alterar o layout dos dados, o Excel oferece três métodos principais para transpor seus dados:

1. Paste Special - Uma conversão rápida e única

2. TRANSPOSE() função - uma abordagem dinâmica baseada em fórmulas

3. Power Query - Um método avançado para conjuntos de dados maiores

Neste artigo, você aprenderá passo a passo como usar cada método, quando aplicá-los e as considerações importantes que deve ter em mente. Se você é novo no Excel, convidamos você a explorar nosso curso Análise de dados no Excel, onde você pode aprender sobre operações básicas, como tabelas dinâmicas e funções lógicas. 

Resposta rápida

Embora o Excel ofereça várias maneiras de transpor dados, conforme mencionado na introdução, a maneira mais rápida é usar o recurso Colar especial:

• Selecione e copie seus dados (Ctrl + C).

• Clique com o botão direito do mouse na célula de destino onde você deseja que os dados transpostos apareçam.

• Selecione "Colar especial".

• Marque a caixa "Transpose" (Transpor ).

• Clique em OK.

Isso faz com que você troque imediatamente as linhas por colunas e as colunas por linhas, oferecendo uma solução rápida para a maioria das necessidades de transposição.

O que significa transpor no Excel?

Transpor no Excel significa converter linhas em colunas e colunas em linhas. Basicamente, você gira seus dados em 90 graus, alterando sua orientação e preservando todas as informações.

Quando você transpõe dados, a primeira linha se torna a primeira coluna, a segunda linha se torna a segunda coluna e assim por diante.

Por exemplo, se você tiver um conjunto de dados com nomes de produtos em linhas e vendas mensais em colunas, a transposição forneceria os nomes dos produtos em colunas e as vendas mensais em linhas.

Maneiras de transpor no Excel

O Excel oferece três métodos distintos para transpor dados, cada um com suas próprias vantagens e casos de uso ideais. Vamos explorar cada técnica passo a passo para ajudar você a escolher a abordagem correta.

1. Usando paste special para transpor dados

O método Colar especial é a maneira mais simples de transpor dados no Excel. Ele cria uma cópia única de seus dados na orientação transposta. Vamos ver um exemplo. Imagine que você tenha os seguintes dados de vendas regionais em formato vertical: 

Exemplo de dados originais em formato vertical. Imagem do autor. 

Para transpor esses dados de um layout vertical para um horizontal:

1. Selecione todos os dados que você deseja transpor (neste caso, as células A1:B5).
2. Copie os dados (Ctrl+C ou clique com o botão direito do mouse e selecione Copiar).
3. Clique na célula em que você deseja que os dados transpostos apareçam (como a célula D1).
4. Clique com o botão direito do mouse e selecione Colar especial no menu de contexto.

O método Paste Special e a opção Transpose. Imagem do autor. 

5. Na caixa de diálogo Colar especial, marque a caixa Transpor e clique em OK.

O resultado após a transposição é mostrado abaixo:

Resultado após a transposição dos dados originais. Imagem do autor. 

Observe como os dados foram completamente girados - o que antes estava em linhas agora está em colunas e vice-versa. Os nomes das regiões que estavam na coluna A agora estão na linha 1, e os números de vendas que estavam na coluna B agora estão na linha 2.

Quando você deve usar esse método:

• Para transformações rápidas e únicas de dados
• Quando você precisar de uma solução simples que não exija fórmulas
• Para conjuntos de dados menores que não precisarão de atualização frequente

Vantagens: Simples e rápido de aplicar com apenas alguns cliques. 

Limitações: Cria uma cópia estática que não será atualizada se os dados originais forem alterados.

2. Usando a função TRANSPOSE()

A função TRANSPOSE() oferece uma alternativa dinâmica ao recurso Colar especial. Diferentemente do método anterior, que cria uma cópia estática, essa função mantém um link ativo para os dados originais, atualizando automaticamente os resultados transpostos sempre que os dados de origem forem alterados.

Vamos usar os mesmos dados de vendas regionais para demonstrar como isso funciona:

1. Primeiro, você precisa selecionar o intervalo de destino em que os dados transpostos aparecerão. Para o nosso exemplo de dados de vendas regionais, precisamos selecionar células que acomodem nossa estrutura transposta. Como os dados originais têm 5 linhas por 2 colunas, precisaremos selecionar um intervalo com 2 linhas por 5 colunas.

2. Com as células de destino em foco, digite a fórmula TRANSPOSE() na barra de fórmulas:

A função TRANSPOSE() no Excel. Imagem do autor. 

3. No Excel moderno (2019 ou Microsoft 365), basta pressionar Enter. Nas versões mais antigas do Excel, você precisará pressionar Ctrl+Shift+Enter, pois essa é uma fórmula de matriz.

O resultado será semelhante ao nosso exemplo anterior, mas com uma diferença importante: se você alterar qualquer valor nos dados originais, a versão transposta será atualizada automaticamente para refletir essas alterações.

Resultado após o uso da função TRANSPOSE(). Imagem do autor. 

Quando você deve usar esse método:

• Quando seus dados de origem são alterados com frequência
• Quando você precisa manter uma conexão ativa entre os dados originais e transpostos
• Para criar relatórios ou painéis dinâmicos que precisam ser atualizados automaticamente

Vantagens: Cria um link dinâmico que é atualizado automaticamente quando os dados de origem são alterados. 

Limitações: Requer uma seleção cuidadosa do tamanho correto do intervalo de destino; requer compreensão das fórmulas de matriz para versões mais antigas do Excel.

3. Usando o Power Query para transpor dados

O Power Query é a ferramenta de transformação de dados mais poderosa do Excel, perfeita para lidar com conjuntos de dados maiores e transformações complexas. Embora exija mais etapas do que os métodos anteriores, ele oferece uma flexibilidade inigualável.

Vamos usar nossos mesmos dados de vendas regionais para demonstrar como o Power Query pode transpor dados:

1. Comece clicando na guia Dados na faixa de opções do Excel e, em seguida, selecione a opção Obter dados (Power Query).

Como iniciar o Power Query no Excel. Imagem do autor. 

2. Na caixa de diálogo que aparece, você verá várias opções de fonte de dados. Como estamos trabalhando com um pequeno exemplo que já está no Excel, selecione a tabela Blank (Em branco ), que nos permite inserir nossos dados manualmente. No entanto, lembre-se de que a versatilidade das fontes de dados disponíveis para o Power Query é uma de suas principais vantagens.

Optando pela opção de tabela em branco para a fonte de dados. Imagem do autor. 

3. Na próxima tela, insira seus dados (ou cole-os da planilha) na tabela. Certifique-se de marcar Usar a primeira linha como cabeçalhos se seus dados incluírem cabeçalhos de coluna.

Inserção de dados para o Power Query. Imagem do autor. 

4. Depois que você clicar em Next, o Power Query Editor será aberto com os dados carregados. Agora, clique na guia Transformar na faixa de opções e selecione Transpor nas opções de menu.

Transformação de dados por meio do recurso de transposição no Power Query. Imagem do autor. 

5. Você verá imediatamente a transposição dos dados na janela de visualização. Observe como o Power Query inverte automaticamente as linhas e colunas. A barra de fórmulas mostra Table.Transpose(Source) indicando a transformação que está sendo aplicada.

Resultado da transposição no Power Query. Imagem do autor. 

6. Quando você estiver satisfeito com a transformação, clique em Close & Load (Fechar e carregar ) na guia Home (Página inicial) para enviar os dados transpostos de volta à planilha do Excel.

Quando você deve usar esse método:

• Para grandes conjuntos de dados em que os outros métodos podem ser complicados
• Quando você precisar realizar transformações adicionais além da simples transposição
• Como parte de um fluxo de trabalho mais complexo de limpeza e preparação de dados
• Para tarefas repetitivas que você deseja automatizar

Vantagens:

• Ideal para grandes conjuntos de dados e transformações complexas de dados
• Cria uma consulta que pode ser atualizada quando os dados de origem são alterados
• Oferece opções adicionais de limpeza de dados no mesmo fluxo de trabalho
• Pode ser salvo como parte de um processo que pode ser repetido

Limitações:

• Requer mais etapas do que os outros métodos
• Tem uma curva de aprendizado mais acentuada para iniciantes
• Abre uma janela separada do editor

Transposição de colunas, linhas e tabelas

Embora tenhamos explorado os três principais métodos de transposição de dados no Excel, vamos abordar brevemente alguns cenários específicos que você pode encontrar.

Como transpor colunas para linhas no Excel

Quando você tiver dados organizados em colunas e precisar deles em linhas, use qualquer um dos métodos que discutimos:

Usando o recurso Colar especial:

1. Selecione e copie seus dados baseados em colunas(Ctrl + C).
2. Clique com o botão direito do mouse na primeira célula da nova linha onde você deseja que os dados apareçam.
3. Clique em Paste Special e marque Transpose.
4. Clique em OK para ver as colunas transformadas em linhas.

Isso reorganiza instantaneamente os dados, mas cria uma cópia estática.

Usando a função TRANSPOSE():

1. Selecione um intervalo vazio que corresponda ao número de linhas nas colunas originais.

2. Digite =TRANSPOSE(A1:A5) (modifique conforme necessário).

3. Pressione Enter (ou Ctrl + Shift + Enter em versões anteriores).

Isso mantém os dados transpostos conectados às colunas originais, garantindo que sejam atualizados automaticamente.

Como transpor linhas para colunas no Excel

Se os seus dados estiverem estruturados em linhas, mas precisarem ser exibidos em colunas, você poderá usar o recurso Colar especial do Excel para uma conversão rápida e única ou a função TRANSPOSE() para uma abordagem dinâmica.

Usando o recurso Colar especial:

1. Selecione e copie seus dados baseados em linhas(Ctrl + C).
2. Clique com o botão direito do mouse na célula de destino onde você deseja que as colunas apareçam.
3. Clique em Paste Special e marque a caixa Transpose (Transpor ).
4. Clique em OK para ver as linhas transformadas em colunas.

Observe que as etapas são exatamente as mesmas para transpor colunas em linhas. O Excel vê as linhas e colunas como intercambiáveis.

Usando a função TRANSPOSE():

1. Selecione um intervalo vazio com o mesmo número de colunas que as linhas originais.

2. Digite =TRANSPOSE(A1:F1) (ajuste o intervalo conforme necessário).

3. Pressione Enter ou Ctrl + Shift + Enter .

Agora, qualquer alteração na linha original será automaticamente atualizada nas colunas transpostas.

Se você estiver trabalhando com conjuntos de dados maiores que exigem atualizações frequentes, o Power Query é outra maneira eficaz de automatizar transformações de coluna para linha ou de linha para coluna, mantendo os dados vinculados. Na seção anterior, incluí as etapas de como usar o Power Query para fazer a transposição. 

Como transpor tabelas no Excel

Quando você trabalha com tabelas inteiras ou intervalos estruturados:

1. Para tabelas menores, basta selecionar todo o intervalo e usar Colar especial → Transpor, como mostrei nas seções anteriores.
2. Para tabelas maiores com muitas colunas e linhas, o Power Query oferece mais controle e pode lidar com qualquer tamanho de tabela de forma eficiente.
3. Para tabelas em que apenas uma parte precisa ser transposta, você pode precisar dividir sua abordagem - transpondo certas seções enquanto mantém outras em sua orientação original

Lembre-se de que, ao transpor uma tabela, você trocará não apenas os dados, mas também os cabeçalhos. Em muitos casos, você desejará que a primeira linha se torne a primeira coluna (e vice-versa) para manter a rotulagem adequada dos dados.

Coisas adicionais a considerar

Ao transpor dados no Excel, há algumas considerações importantes que podem ajudar você a evitar problemas comuns e aprimorar seu fluxo de trabalho:

Manuseio de células em branco

A função TRANSPOSE() pode substituir espaços em branco por zeros, o que nem sempre é desejável. Para reter valores em branco ao usar essa função, você pode usar uma instrução IF() aninhada:

=TRANSPOSE(IF(A1:F5="","",A1:F5))	

Essa fórmula verifica se as células do intervalo original estão vazias e, em caso afirmativo, as mantém vazias no resultado transposto em vez de convertê-las em zeros.

Manutenção de links para dados de origem

Como já discutimos, os três métodos de transposição tratam os links para os dados de origem de forma diferente:

• O Paste Special cria uma cópia estática sem conexão com os dados originais.

• A função TRANSPOSE() mantém um link dinâmico, atualizando automaticamente quando os dados de origem são alterados.

• O Power Query permite que você atualize a conexão quando necessário, dando a você controle sobre quando as atualizações ocorrem.

Escolha o método apropriado com base na frequência com que seus dados de origem são alterados e se você precisa de atualizações automáticas.

Usando TRANSPOSE() com formatação condicional

Ao usar a função TRANSPOSE(), lembre-se de que a formatação não é transferida. Se os dados originais usarem formatação condicional para destacar valores importantes, você precisará aplicar novas regras de formatação condicional ao intervalo transposto.

Isso se aplica a toda a formatação visual, incluindo cores de células, fontes e bordas. Somente os valores em si são transpostos, não sua apresentação visual.

Conclusão

A transposição de dados no Excel é uma habilidade valiosa que pode melhorar significativamente a organização de dados e os fluxos de trabalho de análise. Cada um dos três métodos que exploramos oferece vantagens distintas, dependendo de suas necessidades específicas.

Para obter técnicas mais avançadas de manipulação de dados no Excel, considere explorar nosso programa de habilidades Excel Fundamentals, que abrange uma série de habilidades para aprimorar seus recursos de análise de dados. Se você estiver interessado em expandir suas habilidades de transformação de dados para além do Excel, confira nosso tutorial sobre Como transpor uma matriz em R para conhecer técnicas semelhantes no ambiente de programação R.