Como Criar uma Faixa Dinâmica Definida no Excel

Seus dados do Excel mudam com freqüência, por isso é útil criar um intervalo definido dinâmico que se expande e se contrai automaticamente para o tamanho do intervalo de dados. Vamos ver como.

Ao usar um intervalo definido dinâmico, você não precisará editar manualmente os intervalos de suas fórmulas, gráficos e tabelas dinâmicas quando os dados forem alterados. Isso acontecerá automaticamente.

Duas fórmulas são usadas para criar faixas dinâmicas: OFFSET e INDEX. Este artigo se concentrará no uso da função INDEX, pois é uma abordagem mais eficiente. OFFSET é uma função volátil e pode atrasar grandes planilhas.

Crie um intervalo definido dinamicamente no Excel

Para nosso primeiro exemplo, temos a lista de dados de uma única coluna vista abaixo.

Precisamos que isso seja dinâmico para que, se mais países forem adicionados ou removidos, o intervalo seja atualizado automaticamente.

Para este exemplo, queremos evitar a célula do cabeçalho. Como tal, queremos o intervalo $ A $ 2: $ A $ 6, mas dinâmico. Faça isso clicando em Fórmulas> Definir nome.

Digite “países” na caixa “Nome” e, a seguir, insira a fórmula abaixo na caixa “Refere-se a”.

= $ A $ 2: ÍNDICE ($ A: $ A, CONTAGEM ($ A: $ A))

Digitar esta equação em uma célula de planilha e, em seguida, copiá-la para a caixa Novo nome é às vezes mais rápido e fácil.

Como é que isso funciona?

A primeira parte da fórmula especifica a célula inicial do intervalo (A2 em nosso caso) e, em seguida, o operador de intervalo (:) segue.

= $ A $ 2:

Usar o operador de intervalo força a função INDEX a retornar um intervalo em vez do valor de uma célula. A função INDEX é então usada com a função CONT.valores. COUNTA conta o número de células não vazias na coluna A (seis em nosso caso).

ÍNDICE ($ A: $ A, CONTAR.VAL ($ A: $ A))

Esta fórmula pede à função INDEX para retornar o intervalo da última célula não vazia na coluna A ($ A $ 6).

O resultado final é $ A $ 2: $ A $ 6, e por causa da função COUNTA, é dinâmico, pois encontrará a última linha. Agora você pode usar esse nome definido de “países” dentro de uma regra de validação de dados, fórmula, gráfico ou sempre que precisarmos fazer referência aos nomes de todos os países.

Crie um intervalo definido dinâmico bidirecional

O primeiro exemplo era apenas dinâmico em altura. No entanto, com uma ligeira modificação e outra função CONTAR.VAL, você pode criar um intervalo dinâmico por altura e largura.

Neste exemplo, usaremos os dados mostrados abaixo.

Desta vez, criaremos um intervalo definido dinâmico, que inclui os cabeçalhos. Clique em Fórmulas> Definir nome.

Digite ‘”vendas” na caixa “Nome” e insira a fórmula abaixo na caixa “Refere-se a”.

= $ A $ 1: ÍNDICE ($ 1: $ 1.048.576, CONTAR.VAL ($ A: $ A), CONTAR.VAL ($ 1: $ 1))

Esta fórmula usa $ A $ 1 como célula inicial. A função INDEX então usa um intervalo de toda a planilha ($ 1: $ 1048576) para olhar e retornar.

Uma das funções CONTAR.VAL é usada para contar as linhas não vazias e outra é usada para as colunas não vazias, tornando-a dinâmica em ambas as direções. Embora esta fórmula tenha começado em A1, você poderia ter especificado qualquer célula inicial.

Agora você pode usar este nome definido (vendas) em uma fórmula ou como uma série de dados de gráfico para torná-los dinâmicos.