Seus dados do Excel mudam com frequência, por isso é útil criar um intervalo definido dinâmico que expande e contrai automaticamente para o tamanho do seu intervalo de dados. Vamos ver como.
Ao usar uma faixa dinâmica definida, você não precisará editar manualmente os intervalos de suas fórmulas, gráficos dinâmicos e tabelas quando os dados mudam. Isso vai acontecer automaticamente.
Duas fórmulas são usadas para criar intervalos dinâmicos: OFFSET e INDEX. Esta postagem focará no uso da função INDEX, uma vez que é uma abordagem mais eficiente. OFFSET é uma função volátil e pode retardar grandes planilhas.
Crie um intervalo dinâmico definido no Excel
Para nosso primeiro exemplo, temos a lista de dados de coluna única vista abaixo.
Precisamos que isso seja dinâmico, portanto, se mais países forem adicionados ou removidos, o alcance é atualizado automaticamente.
Para este exemplo, queremos evitar célula de cabeçalho. Como tal, nós queremos o alcance $ UMA $ 2: $ UMA $ 6, mas dinâmico. Faça isso clicando em Fórmulas> Definir nome.
Escriba “países” no quadro “Nome” e insira a fórmula abaixo na caixa “Se refere a”.
= $ A $ 2:ÍNDICE($UMA:$UMA,CONT.valor($UMA:$UMA))
Digitar esta equação em uma célula da planilha e depois copiá-la para a caixa Novo nome às vezes é mais rápido e fácil.
Como é que isto funciona?
A primeira parte da fórmula especifica a célula inicial do intervalo (A2 no nosso caso) e, em seguida, siga o operador de alcance (:).
= $ 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 COUNT. COUNT conta o número de células que não estão em branco na coluna A (seis no nosso caso).
ÍNDICE($UMA:$UMA,CONT.valor($UMA:$UMA))
Esta fórmula pede à função INDEX para retornar o intervalo da última célula não vazia na coluna A ($ UMA $ 6).
O resultado final é $ UMA $ 2: $ UMA $ 6, e por causa da função COUNT, é dinâmico, uma vez que encontrará a última linha. Agora você pode usar este nome definido por “países” dentro de uma regra de validação de dados, Fórmula, gráfico ou onde precisamos nos referir aos nomes de todos os países.
Crie uma faixa dinâmica bidirecional definida
O primeiro exemplo era apenas dinâmico em altura. Apesar disto, com uma ligeira modificação e outra função COUNT, você pode criar um intervalo que é dinâmico em altura e largura.
Neste exemplo, vamos usar os dados mostrados abaixo.
Desta vez, vamos criar uma faixa dinâmica definida, que inclui os cabeçalhos. Clique em Fórmulas> Definir Nome.
Escriba “vendas” no quadro “Nome” e insira a fórmula abaixo na caixa “Se refere a”.
= $ A $ 1:ÍNDICE($1:$1048576,CONT.valor($UMA:$UMA),CONT.valor($1:$1))
Esta fórmula usa $ UMA $ 1 como célula inicial. Subseqüentemente, A função INDEX usa um intervalo de toda a planilha ($ 1: $ 1048576) para pesquisar e retornar.
Uma das funções CONT.valores é usada para contar as linhas que não estão em branco, e outro é usado para colunas que não estão em branco, o que o torna dinâmico em ambas as direções. Mesmo que esta fórmula tenha começado em A1, 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á-lo dinâmico.
