Listas relacionadas no Excel

Recentemente surgiu a necessidade de relacionar listas em planilhas Excel, então, depois de alguma pesquisa, cheguei a uma solução que acredito ser bem didática.

Como criar listas

Para criar uma lista, a maneira mais fácil é atribuir um nome a um range de células e depois usando a opção “Validação de Dados” da aba “DADOS”, atribuir essa lista a uma determinada célula. Minha recomendação é que todas as listas utilizadas na planilha, sejam mantidas em uma aba específica para elas. Vamos partir portando, de uma planilha já criada, com duas abas: “Principal” e “Listas”. Na aba “Listas” vamos criar duas listas lado a lado, com os salários correspondentes a cada cargo:

0003_Listas_no_Excel_01

Relacionando listas

Na aba “Principal”, vamos escolher uma célula para colocar o nosso seletor. Uma vez selecionada a célula, usamos a opção “Validação de Dados”, e nela especificamos em “Critério de validação” para Permitir: “Lista” e como Fonte: “=cargos”, ou seja, a lista à qual atribuímos o nome “cargos”.

0003_Listas_no_Excel_03

O efeito será o seguinte:

0003_Listas_no_Excel_04

Agora vamos obter o salário correspondente. Para isso, primeiro vamos obter o índice do elemento da lista selecionado, usando a função “CORRESP()”, da seguinte forma:

=CORRESP(CÉL(“contents”;B3);cargos;0), onde:

Usando a função “CÉL()”, recuperamos o conteúdo atual da célula, e usando a função “CORRESP()”, localizamos o índice correspondente àquele conteúdo.

No nosso exemplo, o índice obtido foi “4”.

Usando a função “=ÍNDICE(salarios;4;1)” aplicada à lista “salarios”, vamos obter o valor desejado, que é de: “3.684,25”

0003_Listas_no_Excel_05

Aplicando uma fórmula sobre a outra, vamos obter a fórmula final:

=ÍNDICE(salarios;CORRESP(CÉL(“contents”;B3);cargos;0);1)

0003_Listas_no_Excel_06

Daí por diante, copiar esse arranjo pode ser feito usando o recurso normal de cópia de linhas do Excel, e as características e fórmulas das colunas vão ser transmitidas e atualizadas para a linha copiada.

0003_Listas_no_Excel_07

Atualização (30/04/2016)

Uma opção mais prática e eficiente para lidar com dados relacionados no Excel, é associar colunas de tabelas com seleção de dados de uma coluna e exibição do dado associado em outra coluna.

Para isso, vamos utilizar a função “PROCV()”. A função “PROC()” também poderia ser utilizada, mas ela implica em regras de ordenação que obrigam a um pré-tratamento dos dados.

Como criar tabelas

Para criar uma tabela, a maneira mais fácil é atribuir um nome a um range de células. Continuando no nosso exemplo, na aba “Listas” vamos criar uma tabela chamada “tabela”, juntando os dados das listas anteriormente criadas:

0002_Tabelas_no_Excel_08

O processo de relacionar listas às células, permanece o mesmo. O que se altera é a fórmula para buscar o item relacionado.

Uma vez estabelecido o relacionamento, podemos substituir aquela fórmula complexa para localizar o campo relacionado ao selecionado, por uma referência mais simples à tabela criada e suas colunas.

Vamos, portanto, substituir aquela fórmula complexa:

=ÍNDICE(salarios;CORRESP(CÉL(“contents”;B3);cargos;0);1)

Por esta, bem mais simples:

=PROCV(B9;tabela;2;FALSO)

Onde: B9, é a célula cujo conteúdo deve ser pesquisado; tabela é o nome da tabela onde a pesquisa deve ser efetuada (na primeira coluna); 2 é o número de ordem da coluna da tabela cujo conteúdo correspondente na mesma linha, deve ser retornado e FALSO, indica que os dados da primeira coluna da tabela não precisam estar ordenados.

0002_Tabelas_no_Excel_09

Espero que essa dica seja útil para muitos.

Anúncios

Deixe um comentário

Faça o login usando um destes métodos para comentar:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s