Combinando as funções INDIRETO e CONT.VALORES no Excel, podemos criar um intervalo dinâmico que abrange uma determinada quantidade de células de acordo com o preenchimento de dados em uma coluna. Dessa forma, podemos usar esse intervalo dinâmico como fonte para uma lista suspensa, que é atualizada automaticamente sempre que uma nova opção é adicionada ao intervalo.
Como funciona uma lista suspensa com nome definido padrão
Em um processo de validação de dados, ao criar uma lista suspensa, podemos fazer uso de um nome definido como intervalo de critérios. Isso é muito útil caso precisemos, futuramente, adicionar novos itens à lista suspensa, bastando, para isso, que alteremos apenas a abrangência do nome definido. Você pode se aprofundar um pouco mais nesse assunto conferindo este artigo.
Essa alternativa nos poupa tempo, pois não precisamos editar todas as listas suspensas que façam referência a esse intervalo, no entanto, como dito anteriormente, precisamos editar a abrangência do nome definido se houver novos itens a serem adicionados.
Criando um intervalo dinâmico com INDIRETO e CONT.VALORES
Há uma maneira de fazer com que um intervalo seja dinâmico, ou seja, uma forma de fazer com que sua abrangência seja alterada automaticamente toda vez que novos dados forem inseridos. Para isso, faremos uso de duas funções: INDIRETO e CONT.VALORES.
A função INDIRETO transforma qualquer texto em uma referência, mas também permite que concatenemos referências em texto com resultados de funções ou cálculos, formando, assim, uma referência que se altera conforme a alteração dos resultados desses cálculos ou funções. Você pode aprender mais sobre ela neste artigo.
Já a função CONT.VALORES realiza a contagem de células não vazias em um intervalo, ou seja, a quantidade de células preenchidas. Você também pode aprender um pouco mais sobre essa função neste artigo.
Observe o intervalo abaixo, que contém apenas três itens.
Para criar um nome definido abrangendo essas três categorias, precisaríamos usar a notação B3:B5, ou seja, o intervalo que vai de B3 até B5. Se adicionarmos mais uma categoria, nosso intervalo continuará sendo iniciado em B3, mas será finalizado em B6.
Se aplicarmos a função CONT.VALORES na coluna B inteira, obteremos (mantendo apenas três categorias) o valor 4, porque, dentro da coluna B, temos 4 células preenchidas (as 3 categorias mais o título).
Observe nossa lógica: com 4 células preenchidas, nosso intervalo termina em B5, ou seja, na linha 5. Com 5 células preenchidas, nosso intervalo terminaria em B6, ou seja, na linha 6. Toda vez que adicionamos um item à lista de categorias, a contagem das células preenchidas aumenta em 1. Note que o fim do intervalo de categorias sempre será o resultado da CONT.VALORES mais o número da linha correspondente ao último item.
O intervalo SEMPRE se iniciará em B3, ou seja, temos um início fixo. Já a última célula do intervalo estará SEMPRE na linha representada pelo resultado da contagem de células da coluna B + 1.
- Se houver 3 itens, então o intervalo vai de B3 a B5 (linha 5 = 4+1).
- Se houver 4 itens, então o intervalo vai de B3 a B6 (linha 6 = 5+1).
- Se houver 5 itens, então o intervalo vai de B3 a B7 (linha 7 = 6+1).
Sendo assim, podemos usar a função INDIRETO para montar uma referência de início fixo, mas com fim dinâmico, conforme a quantidade de células preenchidas.
Observe:
=INDIRETO(“Exemplo!$B$3:$B$”&CONT.VALORES(Exemplo!$B:$B)+1)
O texto contido entre aspas é o texto fixo que será transformado em referência pela função. O termo “Exemplo” refere-se ao nome da planilha onde se encontra o intervalo, já que nossa lista suspensa poderá ser incluída em outras planilhas da mesma pasta de trabalho, mas fazendo sempre referência ao mesmo intervalo.
Observe que não temos o número da linha da última célula, apenas a coluna B. O número da linha será obtido através da função CONT.VALORES e concatenado (&) com o termo anterior. O resultado de CONT.VALORES para a coluna B será adicionado ao número 1, conforme vimos anteriormente em nossa lógica.
Sendo assim, a referência gerada pela função INDIRETO será B3:B5 quando a contagem for 4; B3:B6 quando a contagem for 5; e assim por diante.
Basta que criemos um nome definido usando essa função como fonte.
Pronto! Agora temos um nome definido que sempre se iniciará em uma célula, mas terminará de forma dinâmica, conforme a quantidade de células preenchidas em uma coluna.
Usando um intervalo dinâmico como lista suspensa
Por fim, basta adicionar o nome definido recém-criado como fonte para uma lista suspensa.
Versão do Excel utilizada neste tutorial: Microsoft Excel 365 [versão 2303]
- Em cenários muito específicos, você pode usar outras funções de contagem juntamente com INDIRETO, como a função CONT.NÚM, por exemplo.
- Lembre-se de que, ao fazer uso da coluna completa como argumento de contagem da CONT.VALORES, qualquer outro dado que seja inserido em qualquer uma das células dessa coluna será considerado na contagem, fazendo com que você precise alterar a adição ou subtração de contagem na fórmula que compõe o nome definido.
