Se você possui um conjunto de dados e deseja transpô-lo no Excel (Isso significa converter linhas em colunas e colunas em linhas), a execução manual é completamente impossível!
Conteúdo
Transpor dados usando colar especial
Paste Special pode fazer muitas coisas incríveis, uma das quais é transpor dados no Excel.
Suponha que você tenha um conjunto de dados parecido com este:
Os dados têm regiões em colunas e quartos em linhas.
Agora, por algum motivo, se você precisar transpor esses dados, poderá usar colar especial da seguinte maneira:
Isso copiará e colará os dados imediatamente, mas de uma forma que foi transposta.Abaixo está uma demonstração mostrando todo o processo.
As etapas mostradas acima copiam os valores, fórmulas (se houver) e formatação.Se você deseja apenas copiar o valor, selecione "Valor" na caixa de diálogo de colagem especial.
Observe que os dados copiados são estáticos e, se você fizer alterações no conjunto de dados original, essas alterações não serão refletidas nos dados transpostos.
Se você deseja vincular essas células transpostas às células originais, pode combinar o poder de Localizar e substituir com Colar especial.
Transponha dados usando colar especial e localize e substitua
Paste Special sozinho fornece dados estáticos.Isso significa que, se seus dados originais forem alterados e os dados que você deseja transpor também forem atualizados, será necessário transpô-los novamente usando colar especial.
Este é 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 parecido com este:
Aqui estão as etapas para transpor os dados, mas mantenha o link intacto:
- Selecione o conjunto de dados (A1:E5).
- Duplique-o (Control + C ou clique com o botão direito e escolha Duplicar).
- Agora você pode colar os dados transpostos para o novo local.Neste exemplo, quero copiar em G1:K5, então clique com o botão direito do mouse na célula G1 e escolha 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 são vinculadas ao conjunto de dados original (por exemplo, G1 é vinculado a A1, G2 é vinculado a A2, etc.).
- Com esses dados recém-copiados selecionados, pressione Control + H (ou vá para Home -> Edit -> Find and Select -> Replace).Isso abrirá a caixa de diálogo Localizar e substituir.
- Na caixa de diálogo Localizar e substituir, use os seguintes comandos:
- Em Encontrar o que:=
- Substitua por: !@# (observe que usei !@#, pois é uma combinação única de caracteres e é improvável que faça parte de seus dados. Você pode usar qualquer conjunto de caracteres exclusivo).
- Clique em Substituir tudo.Isso substituirá os iguais na 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 do mouse e selecione Colar especial.Neste exemplo, eu colo 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 no local onde foram criados.
- Agora abra a caixa de diálogo Localizar e Substituir novamente e substitua !@# por =.
Isso fornecerá os dados vinculados transpostos.Os dados transpostos serão atualizados automaticamente se você fizer alterações no conjunto de dados original.
Nota: Como A1 está vazio em nossos dados originais, você precisa remover manualmente o 1 em G0.O 0 aparece quando colamos o link, pois um link para uma célula vazia ainda retorna 0.Além disso, você precisa formatar o novo conjunto de dados (você pode simplesmente copiar e colar a formatação do conjunto de dados original).
Transpor dados usando a função TRANSPOSE do Excel
A função Excel TRANSPOSE - como o nome sugere - pode ser usada para transpor dados no Excel.
Suponha que você tenha um conjunto de dados parecido com este:
Aqui estão os passos para transpô-lo:
- Selecione as células nas quais você deseja transpor o conjunto de dados.Observe que você precisa selecionar o número exato de células como dados brutos.Assim, por exemplo, se você tem 2 linhas e 3 colunas, você precisa selecionar as 3 linhas e 2 colunas de células onde deseja transpor os dados.Nesse caso, como são 5 linhas e 5 colunas, você precisa selecionar 5 linhas e 5 colunas.
- Digite =TRANSPOSE(A1:E5) na célula ativa (deve ser a célula superior esquerda selecionada e pressione Control Shift Enter.
Isso irá transpor o conjunto de dados.
Veja o que você precisa saber sobre a função TRANSPOSE:
- É uma função de matriz, então você precisa usar Control-Shift-Enter em vez de apenas Enter.
- Você não pode excluir parte do resultado.Você precisa excluir toda a matriz de valores retornados pela função TRANSPOSE.
- A função de transposição copia apenas o valor, não o formato.
Transpor dados com o Power Query
O Power Query é uma ferramenta poderosa que permite transpor dados rapidamente no Excel.
Consulta de energia SimExcel 2016("Get and Transform" na guia "Data"), mas se você estiver usandoExcel 2013 ou 2010, você precisa instalá-lo como um complemento.
Suponha que você tenha um conjunto de dados parecido com este:
Aqui estão as etapas para transpor esses dados usando o Power Query:
no Excel 2016
Observe que, como a célula superior esquerda do nosso conjunto de dados está vazia, ela recebe um nome genérico Column1 (mostrado abaixo).Você pode remover esta célula dos dados transpostos.
No Excel 2013/2010
No Excel 2013/10, você precisa instalar o Power Query como um suplemento.
Clique aquiBaixe o plugin e obtenha instruções de instalação.
Depois de instalar o Power Query, vá para Power Query -> Dados do Excel -> Da Tabela.
Isso abrirá a caixa de diálogo Criar Tabela.Agora siga as mesmas etapas mostradas no Excel 2016.
Você também pode gostar dos seguintes tutoriais do Excel:
- Multiplique no Excel usando Colar Especial.
- Concatenar intervalos do Excel (com e sem delimitadores).
