Os dados em suas planilhas do Excel estão em constante mudança. Para otimizar seu trabalho, é extremamente útil criar um intervalo nomeado dinâmico, que se ajuste automaticamente, expandindo ou contraindo, de acordo com o tamanho de seu conjunto de dados. Vamos aprender como.
Com um intervalo nomeado dinâmico, você elimina a necessidade de ajustar manualmente as referências em suas fórmulas, gráficos e tabelas dinâmicas sempre que houver alterações nos dados. O ajuste acontece de forma automática.
Existem duas fórmulas principais para criar esses intervalos dinâmicos: DESLOC e ÍNDICE. Este artigo focará no uso da função ÍNDICE, pois ela apresenta uma abordagem mais eficiente. A função DESLOC é considerada volátil e pode causar lentidão em planilhas maiores.
Criação de um Intervalo Nomeado Dinâmico no Excel
Para iniciar, vamos analisar um exemplo com uma lista de dados em uma única coluna, conforme mostrado abaixo.
Nosso objetivo é que esse intervalo seja dinâmico. Isso significa que, se adicionarmos ou removermos países, o intervalo se atualizará sozinho. Queremos que o intervalo seja $A$2:$A$6, excluindo o cabeçalho, mas de forma dinâmica. Para começar, vá em Fórmulas > Definir Nome.
Na caixa “Nome”, insira “países”. Em seguida, na caixa “Refere-se a”, cole a fórmula abaixo:
=A$2:ÍNDICE(A:A;CONT.VALORES(A:A))
Às vezes, é mais rápido e fácil digitar essa equação em uma célula da planilha e depois copiá-la para a caixa Novo Nome.
Como essa fórmula funciona?
A primeira parte da fórmula define a célula inicial do intervalo (A2 neste caso). Em seguida, temos o operador de intervalo (:).
=A$2:
O uso do operador de intervalo força a função ÍNDICE a retornar um intervalo em vez do valor de uma única célula. A função ÍNDICE é usada em conjunto com a função CONT.VALORES. CONT.VALORES calcula o número de células não vazias na coluna A (seis neste exemplo).
ÍNDICE(A:A;CONT.VALORES(A:A))
Essa fórmula instrui a função ÍNDICE a retornar o intervalo da última célula não vazia na coluna A, que neste caso é A6.
O resultado final é A$2:A$6, e, devido à função CONT.VALORES, esse intervalo se torna dinâmico, pois ele se ajustará à última linha preenchida. Agora, você pode usar o nome “países” em uma regra de validação de dados, em uma fórmula, em um gráfico, ou sempre que precisar referenciar a lista de países.
Criação de um Intervalo Nomeado Dinâmico Bidirecional
No primeiro exemplo, a dinamicidade era apenas vertical. Mas com uma pequena alteração e a adição de outra função CONT.VALORES, podemos criar um intervalo que seja dinâmico tanto na altura quanto na largura.
Neste exemplo, utilizaremos os dados mostrados a seguir:
Desta vez, criaremos um intervalo dinâmico que inclua os cabeçalhos. Acesse Fórmulas > Definir Nome.
Digite “vendas” na caixa “Nome” e cole a fórmula abaixo em “Refere-se a”:
=A$1:ÍNDICE(1:1048576;CONT.VALORES(A:A);CONT.VALORES(1:1))
Nesta fórmula, a célula inicial é A1. A função ÍNDICE usa o intervalo de toda a planilha (1:1048576) para buscar e retornar os dados.
Uma das funções CONT.VALORES é utilizada para contar as linhas não vazias, e a outra, para contar as colunas não vazias, tornando o intervalo dinâmico em ambas as direções. Embora esta fórmula comece em A1, você poderia ter especificado qualquer célula inicial.
Agora, o nome “vendas” pode ser usado em fórmulas ou como uma série de dados em gráficos, tornando-os dinâmicos.