Seus dados do Excel mudam com frequência, por isso é útil criar um intervalo definido dinâmico que se expande e se contrai automaticamente para o tamanho de seu intervalo de dados. Vamos ver como.
Usando 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 retardar planilhas grandes.
últimas postagens
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:INDEX($A:$A,COUNTA($A:$A))
Digitar essa 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 (:) vem a seguir.
=$A$2:
O uso do 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).
INDEX($A:$A,COUNTA($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 CONT.valores, é 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 que é dinâmico tanto pela altura quanto pela 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:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($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 essa 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.