Função de transposição no Excel

De tempos em tempos, um usuário do Excel pode ter a tarefa de transformar um intervalo de dados que possui uma estrutura horizontal em uma vertical. Esse processo é chamado de transposição. Esta palavra é nova para a maioria das pessoas, porque no trabalho normal do PC você não precisa usar esta operação. No entanto, quem tem que trabalhar com grandes quantidades de dados precisa saber como fazê-lo. Hoje falaremos com mais detalhes sobre como executá-lo, com qual função e também veremos alguns outros métodos em detalhes.

Função TRANSPOSE – transpõe intervalos de células no Excel

Um dos métodos de transposição de tabelas mais interessantes e funcionais do Excel é a função TRANSP. Com sua ajuda, você pode transformar um intervalo de dados horizontal em um vertical ou realizar a operação inversa. Vamos ver como trabalhar com isso.

Sintaxe da função

A sintaxe para esta função é incrivelmente simples: TRANSPOSE(matriz). Ou seja, precisamos usar apenas um argumento, que é um conjunto de dados que precisa ser convertido para uma visualização horizontal ou vertical, dependendo do que era originalmente.

Transpondo intervalos verticais de células (colunas)

Suponha que tenhamos uma coluna com o intervalo B2:B6. Eles podem conter tanto valores prontos quanto fórmulas que retornam resultados para essas células. Não é tão importante para nós, a transposição é possível em ambos os casos. Depois de aplicar esta função, o comprimento da linha será o mesmo que o comprimento da coluna do intervalo original.

A sequência de passos para usar esta fórmula é a seguinte:

1. Selecione uma linha. No nosso caso, tem um comprimento de cinco células.
2. Depois disso, mova o cursor para a barra de fórmulas e insira a fórmula lá =TRANSP(B2:B6).
3. Pressione a combinação de teclas Ctrl + Shift + Enter.

Naturalmente, no seu caso, você precisa especificar o intervalo típico da sua tabela.

Transpondo intervalos de células horizontais (linhas)

Em princípio, o mecanismo de ação é quase o mesmo do parágrafo anterior. Suponha que tenhamos uma string com as coordenadas inicial e final B10:F10. Também pode conter valores e fórmulas diretamente. Vamos fazer uma coluna com ela, que terá as mesmas dimensões da linha original. A sequência de ações é a seguinte:

1. Selecione esta coluna com o mouse. Você também pode usar as teclas do teclado Ctrl e a seta para baixo, depois de clicar na célula superior desta coluna.
2. Depois escrevemos a fórmula =TRANSPERAÇÃO(B10:F10) na barra de fórmulas.
3. Nós o escrevemos como uma fórmula de matriz usando a combinação de teclas Ctrl + Shift + Enter.

Transpondo com Paste Special

Outra opção de transposição possível é usar a função Colar Especial. Este não é mais um operador para ser usado em fórmulas, mas também é um dos métodos populares para transformar colunas em linhas e vice-versa.

Essa opção está na guia Início. Para acessá-lo, você precisa encontrar o grupo “Clipboard” e lá encontrar o botão “Colar”. Depois disso, abra o menu localizado nesta opção e selecione o item “Transpor”. Antes disso, você precisa selecionar o intervalo que deseja selecionar. Como resultado, obteremos o mesmo intervalo, apenas o oposto é espelhado.

3 maneiras de transpor uma tabela no Excel

Mas, na verdade, existem muito mais maneiras de transformar colunas em linhas e vice-versa. Vamos descrever 3 métodos pelos quais podemos transpor uma tabela no Excel. Discutimos dois deles acima, mas daremos mais alguns exemplos para que você tenha uma ideia melhor de uXNUMXbuXNUMXbcomo realizar este procedimento.

Método 1: Colar Especial

Este método é o mais simples. Basta pressionar alguns botões e o usuário recebe uma versão transposta da tabela. Vamos dar um pequeno exemplo para maior clareza. Suponha que tenhamos uma tabela contendo informações sobre quantos produtos estão atualmente em estoque, bem como quanto custam no total. A tabela em si se parece com isso.

Vemos que temos um cabeçalho e uma coluna com os números dos produtos. Em nosso exemplo, o cabeçalho contém informações sobre qual produto, quanto custa, quanto está em estoque e qual é o custo total de todos os produtos relacionados a esse item que estão em estoque. Obtemos o custo de acordo com a fórmula onde o custo é multiplicado pela quantidade. Para tornar o exemplo mais visual, vamos deixar o cabeçalho verde.

Nossa tarefa é garantir que as informações contidas na tabela estejam localizadas horizontalmente. Ou seja, para que as colunas se tornem linhas. A sequência de ações no nosso caso será a seguinte:

1. Selecione o intervalo de dados que precisamos girar. Depois disso, copiamos esses dados.
2. Coloque o cursor em qualquer lugar na folha. Em seguida, clique com o botão direito do mouse e abra o menu de contexto.
3. Em seguida, clique no botão “Colar especial”.

Após concluir essas etapas, você precisa clicar no botão “Transpor”. Em vez disso, marque a caixa ao lado deste item. Não alteramos outras configurações e, em seguida, clique no botão "OK".

Após realizar essas etapas, ficamos com a mesma tabela, apenas suas linhas e colunas são organizadas de maneira diferente. Observe também que as células que contêm as mesmas informações são destacadas em verde. Pergunta: o que aconteceu com as fórmulas que estavam no intervalo original? A sua localização mudou, mas eles próprios permaneceram. Os endereços das células simplesmente mudaram para aqueles que foram formados após a transposição.

Quase as mesmas ações precisam ser executadas para transpor valores, não fórmulas. Neste caso, você também precisa usar o menu Colar Especial, mas antes disso, selecione o intervalo de dados que contém os valores. Vemos que a janela Colar Especial pode ser chamada de duas maneiras: por meio de um menu especial na faixa de opções ou pelo menu de contexto.

Método 2. Função TRANSP no Excel

Na verdade, esse método não é mais usado tão ativamente como era no início do aparecimento deste programa de planilha. Isso ocorre porque esse método é muito mais complicado do que usar Colar Especial. No entanto, encontra seu uso na automatização da transposição de tabelas.

Além disso, essa função está no Excel, por isso é imperativo conhecê-la, mesmo que não seja mais usada quase. Anteriormente, consideramos o procedimento, como trabalhar com ele. Agora vamos complementar este conhecimento com um exemplo adicional.

1. Primeiro, precisamos selecionar o intervalo de dados que será usado para transpor a tabela. Você só precisa selecionar a área ao contrário. Por exemplo, neste exemplo temos 4 colunas e 6 linhas. Portanto, é necessário selecionar uma área com características opostas: 6 colunas e 4 linhas. A imagem mostra muito bem.
2. Depois disso, começamos imediatamente a preencher esta célula. É importante não remover acidentalmente a seleção. Portanto, você deve especificar a fórmula diretamente na barra de fórmulas.
3. Em seguida, pressione a combinação de teclas Ctrl + Shift + Enter. Lembre-se que esta é uma fórmula de matriz, pois estamos trabalhando com um grande conjunto de dados de uma só vez, que serão transferidos para outro grande conjunto de células.

Depois de inserir os dados, pressionamos a tecla Enter, após o que obtemos o seguinte resultado.

Vemos que a fórmula não foi transferida para a nova tabela. A formatação também foi perdida. Poeto

Tudo isso terá que ser feito manualmente. Além disso, lembre-se que esta tabela está relacionada com a original. Portanto, assim que alguma informação for alterada na faixa original, esses ajustes são feitos automaticamente na tabela transposta.

Portanto, esse método é adequado nos casos em que você precisa garantir que a tabela transposta esteja vinculada à original. Se você usar uma inserção especial, essa possibilidade não será mais.

tabela de resumo

Este é um método fundamentalmente novo, que permite não apenas transpor a tabela, mas também realizar um grande número de ações. É verdade que o mecanismo de transposição será um pouco diferente em comparação com os métodos anteriores. A sequência de ações é a seguinte:

1. Vamos fazer uma tabela dinâmica. Para fazer isso, você precisa selecionar a tabela que precisamos transpor. Depois disso, vá até o item “Inserir” e procure por “Tabela Dinâmica” lá. Uma caixa de diálogo como a desta captura de tela aparecerá.
2. Aqui você pode reatribuir a faixa a partir da qual ela será feita, bem como fazer várias outras configurações. Agora estamos interessados ​​principalmente no lugar da tabela dinâmica – em uma nova folha.
3. Depois disso, o layout da tabela dinâmica será criado automaticamente. É necessário marcar nele os itens que usaremos e, em seguida, eles devem ser movidos para o local correto. No nosso caso, precisamos mover o item “Produto” para “Nomes das Colunas” e “Preço por Peça” para “Valores”.
4. Depois disso, a tabela dinâmica será finalmente criada. Um bônus adicional é o cálculo automático do valor final.
5. Você também pode alterar outras configurações. Por exemplo, desmarque o item “Preço por peça” e marque o item “Custo total”. Como resultado, teremos uma tabela contendo informações de quanto custam os produtos. Este método de transposição é muito mais funcional que os outros. Vamos descrever alguns dos benefícios das tabelas dinâmicas:

1. Automação. Com a ajuda de tabelas dinâmicas, você pode resumir dados automaticamente, bem como alterar a posição de colunas e colunas arbitrariamente. Para fazer isso, você não precisa executar nenhuma etapa adicional.
2. Interatividade. O usuário pode alterar a estrutura da informação quantas vezes precisar para realizar suas tarefas. Por exemplo, você pode alterar a ordem das colunas, bem como agrupar dados de forma arbitrária. Isso pode ser feito quantas vezes o usuário precisar. E leva literalmente menos de um minuto.
3. Dados fáceis de formatar. É muito fácil organizar uma tabela dinâmica da maneira que uma pessoa deseja. Para fazer isso, basta fazer alguns cliques do mouse.
4. Obtendo valores. O grande número de fórmulas usadas para criar relatórios está localizada na acessibilidade direta de uma pessoa e é fácil de integrar em uma tabela dinâmica. São dados como somar, obter a média aritmética, determinar o número de células, multiplicar, encontrar os maiores e menores valores na amostra especificada.
5. Capacidade de criar gráficos de resumo. Se as Tabelas Dinâmicas forem recalculadas, seus gráficos associados serão atualizados automaticamente. É possível criar quantos gráficos você precisar. Todos eles podem ser alterados para uma tarefa específica e não serão interconectados.
6. Capacidade de filtrar dados.
7. É possível construir uma tabela dinâmica com base em mais de um conjunto de informações de origem. Portanto, sua funcionalidade se tornará ainda maior.

É verdade que, ao usar tabelas dinâmicas, as seguintes restrições devem ser levadas em consideração:

1. Nem todas as informações podem ser usadas para gerar tabelas dinâmicas. Antes que eles possam ser usados ​​para esta finalidade, as células devem ser normalizadas. Em palavras simples, acertar. Requisitos obrigatórios: presença de uma linha de cabeçalho, plenitude de todas as linhas, igualdade de formatos de dados.
2. Os dados são atualizados de forma semiautomática. Para obter novas informações na tabela dinâmica, você deve clicar em um botão especial.
3. As tabelas dinâmicas ocupam muito espaço. Isso pode levar a alguma interrupção do computador. Além disso, o arquivo será difícil de enviar por e-mail por causa disso.

Além disso, depois de criar uma tabela dinâmica, o usuário não tem a capacidade de adicionar novas informações.