Como Transpor Rapidamente Dados em Excel (Guia Passo a Passo)

Vídeo – Como Transpor Dados em Excel

Se tiver um conjunto de dados e quiser transpô-los em Excel (o que significa converter linhas em colunas e colunas em linhas), fazê-lo manualmente é um completo NÃO!

Transpor os dados usando o Especial de Colar

O Especial de Colar pode fazer muitas coisas espantosas, e uma dessas coisas é transpor os dados em Excel.

P>Se tiver um conjunto de dados como mostrado abaixo:

Estes dados têm as regiões numa coluna e os trimestres numa linha.

Agora, por alguma razão, se precisar de transpor estes dados, eis como o pode fazer usando pasta especial:

• Seleccionar o conjunto de dados (neste caso A1:E5).
• Copiar o conjunto de dados (Control + C) ou clique com o botão direito do rato e seleccione copy.
• Agora pode colar os dados transpostos num novo local. Neste exemplo, quero copiar em G1:K5, por isso clique com o botão direito do rato na célula G1 e seleccione paste special.
• Na caixa de diálogo paste special, marque a opção de transposição no canto inferior direito.
• Click OK.

Isso copiaria e colaria instantaneamente os dados, mas de tal forma que estes fossem transpostos. Abaixo está uma demonstração mostrando todo o processo.

Os passos mostrados acima copiam o valor, a fórmula (se houver), assim como o formato. Se quiser apenas copiar o valor, seleccione ‘valor’ na caixa de diálogo colar especial.

Nota que os dados copiados são estáticos, e se fizer quaisquer alterações no conjunto de dados original, essas alterações não se reflectiriam nos dados transpostos.

Se quiser que estas células transpostas sejam ligadas às células originais, pode combinar o poder de Encontrar & Substituir por Paste Special.

Transpor dados usando Paste Special e Encontrar & Substituir

Usar Paste Special sozinho dá-lhe dados estáticos. Isto significa que se os seus dados originais mudarem e quiser que os dados transpostos também sejam actualizados, então precisa de usar novamente o Paste Special para os transpor.

Aqui está um truque fixe que pode usar para transpor os dados e ainda os ter ligados às células originais.

P>Ponha um conjunto de dados como mostrado abaixo:

p>Aqui estão os passos para transpor os dados mas manter as ligações intactas:

• Selecione o conjunto de dados (A1:E5).
• Copiá-lo (Controlo + C, ou clique com o botão direito do rato e seleccione copiar).
• Agora pode colar os dados transpostos num novo local. Neste exemplo, quero copiar em G1:K5, por isso clique com o botão direito do rato na célula G1 e seleccione paste special.
• Na caixa de diálogo Paste Special, clique no botão Paste Link. Isto dar-lhe-á o mesmo conjunto de dados, mas aqui as células estão ligadas ao conjunto de dados original (por exemplo, G1 está ligado a A1, e G2 está ligado a A2, e assim por diante).
• Com estes novos dados copiados seleccionados, Prima Control + H (ou vá a Home -> Editing -> Find & Select -> Replace). Isto abrirá o Localizador & Substituir caixa de diálogo.
• No Localizador & Substituir caixa de diálogo, usar o seguinte:
  • Na Localizar o quê: =
  • Na Substituir por: !@# (note que estou a usar !@# pois é uma combinação única de caracteres que é improvável que faça parte dos seus dados. Pode usar qualquer conjunto único de caracteres).

Clicar em Substituir Tudo. Isto substituirá o igual ao da fórmula e terá !@# seguido da referência da célula em cada célula.

• Clique direito e copie o conjunto de dados (ou use Controlo + C).
• Seleccionar um novo local, clicar com o botão direito do rato e seleccionar colar especial. Neste exemplo, estou a colá-lo na célula G7. Pode colá-lo onde quiser.
• Na caixa de diálogo Colar Especial, seleccione Transpor e clique OK.
• Copiar colar estes dados transpostos recentemente criados para aquele a partir do qual foram criados.
• Agora abra novamente a caixa de diálogo Encontrar e Substituir !@# por =.

Isto dar-lhe-á um dado ligado que foi transposto. Se fizer quaisquer alterações no conjunto de dados original, os dados transpostos serão automaticamente actualizados.

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

Transposição de Dados usando a função TRANSPOSE do Excel

Função TRANSPOSE do Excel – como o nome sugere – pode ser usada para transpor dados em Excel.

P>Ponha um conjunto de dados como mostrado abaixo:

p>Aqui estão os passos para transpor:

• Selecione as células onde pretende transpor o conjunto de dados. Note que é necessário seleccionar o número exacto de células como os dados originais. Assim, por exemplo, se tiver 2 linhas e 3 colunas, precisa de seleccionar 3 linhas e 2 colunas de células onde pretende que os dados transpostos sejam transpostos. Neste caso, uma vez que existem 5 linhas e 5 colunas, é necessário seleccionar 5 linhas e 5 colunas.
• Enter =TRANSPOSE(A1:E5) na célula activa (que deve ser a célula superior esquerda da selecção e premir Control Shift Enter.

Isto transporia o conjunto de dados.

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

• É uma função array, por isso precisa de usar Control-Shift-Enter e não apenas Enter.
• Não é possível apagar uma parte do resultado. É preciso apagar todo o array de valores que a função TRANSPOSE devolve.
• FunçãoTranspose copia apenas os valores, não a formatação.

Transposição de dados usando Power Query

Power Query é uma ferramenta poderosa que lhe permite transpor rapidamente os dados em Excel.

Power Query é uma parte do Excel 2016 (Get & Transformar no separador Dados) mas se estiver a usar o Excel 2013 ou 2010, então precisa de o instalar como um add-in.

P>Ponha que tem o conjunto de dados como mostrado abaixo:

Aqui estão os passos para transpor estes dados usando o Power Query:

No Excel 2016

• Seleccionar os dados e ir para Data -> Obter & Transformar -> Da Tabela.
• Na caixa de diálogo Criar Tabela, certifique-se de que o intervalo está correcto e clique OK. Isto abrirá a caixa de diálogo Query Editor.
• Na caixa de diálogo Query Editor, seleccionar o separador ‘Transformar’.
• Na tabulação Transformar, vá para Tabela -> Usar Primeira Linha como Cabeçalhos -> Usar Cabeçalhos como Primeira Linha. Isto assegura que a primeira linha (que contém cabeçalhos: Q1, Q2, Q3, e Q4) também são tratados como dados e transpostos.
• Click on Transpose. Isto transporia instantaneamente os dados.
• Click on Use First Row as Headers. Isto faria agora a primeira fila dos dados transpostos como cabeçalhos.
• li>Go to File -> Close and Load. Isto fechará a janela do Power Editor e criará uma nova folha que contém os dados transpostos.

Nota que uma vez que a célula superior esquerda do nosso conjunto de dados estava vazia, recebe um nome genérico Coluna1 (como mostrado abaixo). Pode apagar esta célula dos dados transpostos.

No Excel 2013/2010

No Excel 2013/10, é necessário instalar o Power Query como um add-in.

Clique aqui para descarregar o plugin e obter instruções de instalação.

Após ter instalado o Power Query, vá a Power Query -> Dados Excel -> A partir de Tabela.

Isto abrirá a caixa de diálogo criar tabela. Agora siga os mesmos passos mostrados para o Excel 2016.

Pode também gostar dos seguintes Tutoriais Excel:

• Multiplicar no Excel usando Colar Especial.
• CONCATENAR a Gama Excel (com e sem separador).
• Li>Limpar Dados em Folhas de Cálculo Excel.
• Comparar Colunas no Excel
• Excel Text to Columns
• Excel TRIM Function
• Merge Tables in Excel