Neste artigo você aprenderá como desempilhar dados em várias colunas no Excel passo-a-passo.
1. Dados desestruturados – apenas uma coluna
Não é incomum obtermos dados desestruturados que ocupam apenas uma coluna. Eles são apresentados de maneira empilhada, sem a sua devida separação nas suas diversas colunas.
Isto é típico em dados copiados manualmente de PDFs para planilhas, por exemplo, sendo um “prato cheio” para o Power Query.
No entanto, este artigo objetiva elencar algumas opções para o tratamento destes dados usando fórmulas, contemplando funções antigas e recentes no Excel.
As soluções propostas serão baseadas no seguinte conjunto de dados:
A cada 3 linhas temos 1 registro. Note na imagem a seguir o resultado desejado: os dados devem ser dispostos em 3 colunas.
Perceba que temos 21 células originalmente, o que implica em termos 7 linhas, visto que temos 3 colunas. A seguir são propostas algumas fórmulas para o ajuste nos dados.
P.S: considere o cabeçalho fixo, inserido manualmente.
2. Soluções com fórmulas
2.1 – Usando as funções ÍNDICE, SEQUÊNCIA e LINS
Um requisito é sabermos o número de colunas contidas nos dados para conseguimos realizar a distribuição dos dados e obter a separação em linhas com esta quantidade de colunas. Em nosso caso sabemos que há 3 colunas. Este valor servirá como parâmetro para as nossas fórmulas.
Inicialmente precisamos gerar um vetor com a função SEQUÊNCIA, iniciando em 1 e incrementado de 1 em 1 até 21, visto que esta é a quantidade de elementos nos dados. O primeiro argumento desta função deve conter o número de elementos, ou seja: a quantidade de células originalmente nos dados, o que pode ser obtido com a função LINS (melhor do que CONT.VALORES, visto que poderá haver células vazias nos dados por diversos motivos).
Usamos a função LINS para contar o número de linhas no intervalo, que em nosso caso resulta em 21. Em seguida, este valor é inserido como primeiro argumento de SEQUÊNCIA, resultando no que pode ser visto na imagem seguinte.
Note que este aninhamento entre SEQUÊNCIA e LINS resulta em uma faixa de valores de 1 a 21, porém em apenas 1 coluna. Precisamos quebrar para que estes mesmos valores sejam dispostos em 3 colunas. Para isso, alteramos a função SEQUÊNCIA, que no primeiro argumento receberá o resultado de LINS dividido por 3 (resultando em 7). Além disso, no segundo argumento, informamos o valor 3 (constante), que é o número de colunas já conhecido:
Para finalizar, precisamos apenas da função ÍNDICE. Esta função receberá em seu primeiro argumento o conjunto de dados e em seu segundo argumento a fórmula que obtivemos até então, contendo SEQUÊNCIA e LINS.
A fórmula final inserida foi: =ÍNDICE(B2:B22;SEQUÊNCIA(LINS(B2:B22)/3;3)).
2.2 – Usando a função QUEBRARCOLS
A fórmula com esta função é mais simples. Além de informar o intervalo de dados nesta função (primeiro argumento), precisamos de indicar o número de linhas a distribuir. Para informarmos dinamicamente este número de linhas, usaremos a função LINS de modo idêntico ao exemplo anterior, e dividida por 3 (LINS(B2:B22)/3).
A fórmula final inserida foi: =QUEBRARCOLS(B2:B22;LINS(B2:B22)/3).
2.3 – Usando a função QUEBRARLINS para Desempilhar do Excel
Está solução é a melhor e mais simples de todas aqui apresentadas. Ao usarmos a função QUEBRARLINS, precisamos informar além do intervalo de dados apenas o número de colunas a dividir (3).
A fórmula final inserida foi: =QUEBRARLINS(B2:B22;3).
Download da Planilha de Desempilhar Excel
Clique no botão abaixo para realizar o download do arquivo de exemplo:
