LISTAS EXPANSÍVEIS

ONDE PODEMOS USAR LISTAS EXPANSÍVEIS

É muito comum termos listas diversas no Excel e precisarmos aplicá-las em outros locais, como em uma validação de dados, em um formulário e outras situações.

Porém, se simplesmente selecionamos um intervalo, quando um novo item é adicionado, a lista não se expande.

Este tutorial irá mostrar como podemos criar uma lista expansível no Excel.

EXEMPLO DE UMA LISTA

Veja o exemplo a seguir. Nele, temos uma lista com 3 ítens:

Vamos usá-la em uma validação de dados (menu Dados > Validação de Dados (se quiser saber mais sobre este recurso, vá neste link).

Teremos então, algo assim:

Veja que a listagem mostra os 3 ítens que criamos. Agora, vamos deixá-la com 4 ítens. Veja:

Nossa lista não se expandiu. Desta forma, deveríamos inserir o novo ítem no meio (porque daí o intervalo se expandiria) ou deveríamos reconfigurar a validação de dados.

Ambas as opções são trabalhosos, ainda mais se imaginarmos diversas listas em diversas planilhas.

Mostraremos agora duas opções para criarmos listas expansíveis:

LISTAS EXPANSÍVEIS: OPÇÃO 1, TABELA

O recurso de tabela do Excel converte um intervalo em uma tabela. Desta forma, ao invés de enxergar um monte de células próximas, ele enxerga tudo como um grupo apenas, de nome tabela.

Então, ao criarmos uma tabela dentro de uma listagem, quando o tamanho desta lista aumentar (ou reduzir), a tabela irá se redimensionar, refletindo isto em nossa lista.

Depois de criarmos nossa tabela (em Inserir > Tabela), criamos a mesma validação de dados do exemplo acima. Veja:

Porem, como estamos com uma tabela, e nõ um intervalo, ao adicionarmos m novo ítem em nossa lista, ele irá aparecer na validação:

Se reduzirmos o tamanho da lista, ela também se ajusta:

LISTAS EXPANSÍVEIS: OPÇÃO 2, FUNÇÃO DESLOC

Este método é mais complicado do que o método 1, mas quando usamos a função DESLOC, podemos fazer mais coisas do que o anterior (e não somente relacionado à listas expansíveis).

A função DESLOC retorna um INTERVALO, ao contrário das outras funções (à exceção da função ENDEREÇO), que retornam um valor (número, texto, data, etc).

Isto quer dizer que a “resposta” da função DESLOC é uma área, como por exemplo A1:A3.

No nosso exemplo, temos um intervalo que começa na célula A2 e deve ir até a última célula preenchida daquela coluna. Se tivermos ítens até a linha 10, então teremos que ter um intervalo que vai de A2 até A10.

É exatamente isto o que o DESLOC vai fazer.

Esta função tem 5 argumentos (IMPORTANTE: SE ESTIVER MONTANDO SEU EXEMPLO SIMULTANEAMENTE À LEITURA, LEIA A PARTE SOBRE INTERVALO NOMEADO – A SEGUIR – ANTES DESTE, SENÃO SEU DESLOC NÃO VAI DAR CERTO – ELE NÃO PODE SER COLOCADO EM UMA CÉLULA)

ARGUMENTO 1: Célula de início. No nosso exemplo, pode ser a célula A2, já que nossa lista começa lá, mas a priori poderia ser qualquer célula, B1000, T5, etc., porque os argumentos a seguir (o 2º e o 3º) indicarão o quanto devemos nos deslocar até chegar ao ínicio de nosso intervalo.

Se começarmos na A2, não precisaremos nos deslocar, já que estamos na célula inicial. Se estivéssemos na A1, deveríamos descer uma linha. Se estivéssemos na B3, deveríamos subir uma linha e ir uma coluna para a esquerda.

Mas como já sabemos onde começa, é mais simples já iniciar na célula correta.

ARGUMENTO 2: Representa quantas linhas, a partir da célula inicial, devemos subir ou descer. No nosso exemplo é zero, já que a A2 é a célula inicial. Se tivéssemos informado A1, este argumento deveria ter o valor 1 (pois deveria descer uma linha até a A2). Se tivéssemos começado na A3, deveríamos colocar -1 (para subir uma linha até a A2).

ARGUMENTO 3: São quantas colunas devemos nos mover até o início do intervalo. No nosso exemplo é zero, porque já estamos na coluna certa. Se no argumento 1 tivéssemos colocado B2, deveríamos informar -1, para ele voltar uma coluna até a coluna A.

Não terminamos com a função, mas colocaremos abaixo alguns exemplos cuja resposta será sempre a célula inicial do intervalo (no caso, A2):

DESLOC(A2;0;0)

DESLOC(A10;-8;0)

DESLOC(C1;-2;1)

DESLOC(D15;-13;-3)

Já deu para perceber que a resposta 1 é a mais simples.

ARGUMENTO 4: É a altura do intervalo, isto é, quantas células ele deve ter. No exemplo que está na planilha, nosso intervalo vai da A2 até a A4. Então temos um total de 3 linhas de altura (A2, A3 e A4). Se tivéssemos 10 linhas de altura, o intervalo iria de A2 até A11 (atenção!!!), porque a A2 já é a 1ª linha, então ela é contada.

Mas neste nosso exemplo não podemos digitar 3, porque nossa lista é expansível. Quando colocarmos novos ítens, precisaremos que a lista aumente de tamanho, isto é,a altura aumente.

Para fazer isto vamos CONTAR quantas células preenchidas nós temos, porque se aumentar ou reduzir de tamanho, o intervalo se adaptará.

Para fazer isto, usaremos a função CONT.VALORES para que conte quantas células estão preenchidas. Como não sabemos até onde a lista vai, vamos contar um intervalo grande, de A2 até A1000, A10000.

Então o argumento 4 ficará CONT.VALORES(A2:A1000)

ARGUMENTO 5: É a largura do intervalo, isto é, quantas colunas ele tem. No nosso caso é 1, pois temos apenas a coluna A. Se eventualmente nosso intervalo fosse até a coluna C, este argumento teria o valor 3 (A, B e C).

Nossa fórmula final ficará: DESLOC(A2;0;0;CONT.VALORES(A2:A1000);1)

Porém, se digitarmos isto em uma célula, dará erro, porque esta função retorna um intervalo:

Para que funcione, vamos aplicar esta fórmula em um NOME e em seguida aplicaremos este nome dentro de nossa validação / formulário / etc.

RECURSO NOME

(você pode saber mais sobre este recurso aqui)

Vamos inserir esta função dentro do nome. Para abrir a tela, vamos em Fórmulas > Gerenciador de Nomes  (ou use a tecla de atalho CTRL + F3).

Veremos a seguinte tela:

Quando criamos uma tabela, ele cria um nome relacionado àquela tabela, por isso temos um nome aí.

Para criarmos outro nome, basta clicar em NOVO:

Em NOME, colocamos o nome que armazenará esta fórmula. Pode ser qualquer coisa, menos palavras com espaços (pode usar o underscore, se quiser) e nomes que possam existir, como por exemplo, chamar isto de DESLOC.

No exemplo, usamos ListaExpansivel.

Em ESCOPO informamos em que local esta função estará disponível. Queremos em todo o arquivo, então deixamos PASTA DE TRABALHO

Em COMENTÁRIO podemos colocar qualquer coisa, se quisermos (também pode ficar em branco).

Em REFERE-SE A colocamos nossa fórmula, com um CUIDADO: devemos CLICAR nas células ao invés de digitá-las porque precisamos que ele insira as referências de forma absoluta (com os sinais de $). Se não colocarmos os símbolos de $, a fórmula ficará errada.

Ao final, basta dar OK e retornarmos à planilha.

Agora, basta editar a validação e inserir, ao invés do intervalo, o nome (clique na imagem para vê-la maior):

Se adicionarmos / retirarmos os itens da lista, a a nossa lista se redimensionará.

Outras dicas que podem te interessar