✅ Como transpor dados rapidamente no Excel (guia passo a passo)

Índice

Transpor dados usando colar especial
Transpor dados usando Colar especial e Localizar e substituir
Transpor dados usando a função TRANSPOSE do Excel
Transpor dados usando o Power Query

Assista ao vídeo - Como transpor dados no Excel

Se você tem um conjunto de dados e deseja transpor para o Excel (o que significa converter linhas em colunas e colunas em linhas), fazê-lo manualmente é um completo NÃO!

Transpor dados usando colar especial

O Paste Special pode fazer muitas coisas incríveis, e uma delas é transpor dados no Excel.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo:

Esses dados têm as regiões em uma coluna e os trimestres em uma linha.

Agora, por algum motivo, se você precisar transpor esses dados, aqui está como você pode fazer isso usando colar especial:

Selecione o conjunto de dados (neste caso A1: E5).
Copie o conjunto de dados (Control + C) ou clique com o botão direito e selecione copiar.
Agora você pode colar os dados transpostos em um novo local. Neste exemplo, quero copiar em G1: K5, então clique com o botão direito na célula G1 e selecione colar especial.
Na caixa de diálogo colar especial, marque a opção transpor no canto inferior direito.
Clique OK.

Isso copia e cola instantaneamente os dados, mas de forma que eles sejam transpostos. Abaixo está uma demonstração mostrando todo o processo.

As etapas mostradas acima copiam o valor, a fórmula (se houver), bem como o formato. Se você deseja apenas copiar o valor, selecione ‘valor’ na caixa de diálogo de colar especial.

Observe que os dados copiados são estáticos e, se você fizer alguma alteração no conjunto de dados original, essas alterações não serão refletidas nos dados transpostos.

Se você quiser que essas células transpostas sejam vinculadas às células originais, pode combinar o poder de Localizar e Substituir com Colar Especial.

Transpor dados usando Colar especial e Localizar e substituir

Usar Colar especial sozinho fornece dados estáticos. Isso significa que, se os dados originais forem alterados e você quiser que os dados transpostos também sejam atualizados, será necessário usar Colar especial novamente para transpô-los.

Aqui está um truque legal que você pode usar para transpor os dados e ainda vinculá-los às células originais.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo:

Aqui estão as etapas para transpor os dados, mas manter os links intactos:

Selecione o conjunto de dados (A1: E5).
Copie (Control + C ou clique com o botão direito e selecione copiar).
Agora você pode colar os dados transpostos em um novo local. Neste exemplo, quero copiar em G1: K5, então clique com o botão direito na célula G1 e selecione colar especial.
Na caixa de diálogo Colar especial, clique no botão Colar link. Isso lhe dará o mesmo conjunto de dados, mas aqui as células estão vinculadas ao conjunto de dados original (por exemplo, G1 está vinculado a A1 e G2 está vinculado a A2 e assim por diante).
Com esses novos dados copiados selecionados, pressione Control + H (ou vá para Home -> Editing -> Find & Select -> Replace). Isso abrirá a caixa de diálogo Localizar e substituir.
Na caixa de diálogo Localizar e substituir, use o seguinte:
- Em Encontrar o que: =
- Em Substituir por:! @ # (Observe que estou usando! @ #, Pois é uma combinação exclusiva de caracteres que provavelmente não fará parte de seus dados. Você pode usar qualquer um desses conjuntos exclusivos de caracteres).
Clique em Substituir tudo. Isso substituirá o igual a da fórmula e você terá! @ # Seguido pela referência de célula em cada célula.
Clique com o botão direito e copie o conjunto de dados (ou use Control + C).
Selecione um novo local, clique com o botão direito e selecione colar especial. Neste exemplo, estou colando na célula G7. Você pode colá-lo onde quiser.
Na caixa de diálogo Colar especial, selecione Transpor e clique em OK.
Copie e cole esses dados transpostos recém-criados naquele a partir do qual foram criados.
Agora abra a caixa de diálogo Localizar e substituir novamente e substitua! @ # Por =.

Isso lhe dará dados vinculados que foram transpostos. Se você fizer qualquer alteração no conjunto de dados original, os dados transpostos serão atualizados automaticamente.

Nota: Uma vez que nossos dados originais têm A1 vazio, você precisaria excluir manualmente o 0 em G1. O 0 aparece quando colamos os links, já que um link para uma célula vazia ainda retornaria um 0. Além disso, você precisaria formatar o novo conjunto de dados (você pode simplesmente copiar e colar o formato apenas do conjunto de dados original).

Transpor dados usando a função TRANSPOSE do Excel

A função TRANSPOSE do Excel - como o nome sugere - pode ser usada para transpor dados no Excel.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo:

Aqui estão as etapas para transpô-lo:

Selecione as células onde deseja transpor o conjunto de dados. Observe que você precisa selecionar o número exato de células como os dados originais. Portanto, por exemplo, se você tiver 2 linhas e 3 colunas, precisará selecionar 3 linhas e 2 colunas de células onde deseja os dados transpostos. Nesse caso, como existem 5 linhas e 5 colunas, você precisa selecionar 5 linhas e 5 colunas.
Insira = TRANSPOSE (A1: E5) na célula ativa (que deve ser a célula superior esquerda da seleção e pressione Control Shift Enter.

Isso iria transpor o conjunto de dados.

Aqui estão algumas coisas que você precisa saber sobre a função TRANSPOSE:

É uma função de array, então você precisa usar Control-Shift-Enter e não apenas Enter.
Você não pode excluir uma parte do resultado. Você precisa excluir todo o array de valores que a função TRANSPOSE retorna.
A função Transpor copia apenas os valores, não a formatação.

Transpor dados usando o Power Query

O Power Query é uma ferramenta poderosa que permite transpor dados rapidamente no Excel.

O Power Query faz parte do Excel 2016 (Get & Transform na guia Data), mas se você estiver usando o Excel 2013 ou 2010, será necessário instalá-lo como um suplemento.

Suponha que você tenha o conjunto de dados conforme mostrado abaixo:

Aqui estão as etapas para transpor esses dados usando o Power Query:

No Excel 2016

Selecione os dados e vá para Dados -> Obter e Transformar -> Da Tabela.
Na caixa de diálogo Criar Tabela, certifique-se de que o intervalo esteja correto e clique em OK. Isso abrirá a caixa de diálogo Editor de Consultas.
Na caixa de diálogo do editor de consulta, selecione a guia ‘Transformar’.
Na guia Transformar, vá para Tabela -> Usar primeira linha como cabeçalhos -> Usar cabeçalhos como primeira linha. Isso garante que a primeira linha (que contém os cabeçalhos: Q1, Q2, Q3 e Q4) também seja tratada como dados e transposta.
Clique em Transpor. Isso iria transpor os dados instantaneamente.
Clique em Usar primeira linha como cabeçalhos. Isso agora tornaria a primeira linha dos dados transpostos como cabeçalhos.
Vá para Arquivo -> Fechar e Carregar. Isso fechará a janela do Power Editor e criará uma nova planilha que contém os dados transpostos.

Observe que, como a célula superior esquerda de nosso conjunto de dados estava vazia, ela recebe um nome genérico Coluna1 (conforme mostrado abaixo). Você pode excluir esta célula dos dados transpostos.