Em meu artigo recente, falei tudo sobre intervalos nomeados no Excel. Ao explorar intervalos nomeados, o tópico de alcance dinâmico apareceu. Então, neste artigo, vou explicar como você pode fazer Dynamic Range no Excel.
O que é Dynamic Named Range no Excel?
Um intervalo nomeado normal é estático. Se você definir C2: C10 Como Item, Item sempre se referirá a C2: C10, até e a menos que você edite manualmente. Na imagem abaixo, estamos contando os espaços em branco noItem Lista. Ele está mostrando 2. Se fosse dinâmico, teria mostrado 0.
Um intervalo de nomes dinâmico é um intervalo de nomes que se expande e encolhe de acordo com os dados. Por exemplo, se você tiver uma lista de itens no intervalo C2: C10 e nomeá-la Itens, deve se expandir para C2: C11 se você adicionar um novo item no intervalo e deve encolher se você reduzir ao excluir como acima.
Como criar um intervalo de nomes dinâmicos
Criar intervalos nomeados usando tabelas do Excel
Sim, as tabelas do Excel podem criar intervalos nomeados dinâmicos. Eles farão cada coluna em uma tabela chamada intervalo que é altamente dinâmico.
Mas há uma desvantagem de nomes de tabela que você não pode usá-los na validação de dados e formatação condicional. Mas intervalos nomeados específicos podem ser usados lá.
Use fórmula INDIRETA e CONT.valor
Para tornar um intervalo de nomes dinâmico, podemos usar as funções INDIRETO e CONT.valores
. Como? Vamos ver.
Fórmula genérica a ser escrita na seção Refere-se a:
= INDIRETO ("$ StartingCell: $ finishColumnLetter $" & COUNTA ($ columnLetter: $ columnLetter))
A fórmula genérica acima pode parecer complexa, mas na verdade é fácil. Vamos ver por um exemplo.
A ideia básica é determinar a última célula usada.
Exemplo de faixa dinâmica
No exemplo acima, tínhamos um intervalo de nome estático Item no intervalo C2: C10. Vamos torná-lo dinâmico.
-
- Abra o Gerenciador de nomes pressionando CTRL + F3.
- Agora, se já existe um nome no intervalo, clique nele e, em seguida, clique em editar. Caso contrário, clique em Novo.
- Nomeie-o como Item.
- Em Refere-se a: Seção escreva Abaixo da Fórmula.
= INDIRETO ("$ C2: $ C $" & CONTAR.VAL ($ C: $ C))
- Clique no botão OK.
E está feito. Agora, sempre que você digitar Item na caixa de nome ou em qualquer fórmula, ele se referirá a C2 como a última célula usada no intervalo.
Cuidado: Nenhuma célula deve ficar em branco no intervalo. Caso contrário, o intervalo será reduzido pelo número de células em branco.
Como funciona?
Como eu disse, o único problema é encontrar a última célula usada. Para este exemplo, nenhuma célula deve ficar em branco no meio. Porque? Você saberá.
A função INDIRETA no Excel converte um texto em intervalo. = INDIRETO (“$ C $ 2: $ C $ 9”) se referirá ao intervalo absoluto $ C $ 2: $ C $ 10. Precisamos apenas encontrar o número da última linha dinamicamente (9).
Como todas as células têm algum valor no intervalo C2: C10, podemos usar a função CONT.valores para encontrar a última linha.
Então,= INDIRETO("$ C2: $ C $" e esta parte corrige a linha e coluna iniciais e CONT.valor($ C: $ C) dinâmico calcula a última linha usada.
Então, sim, é assim que você pode criar os intervalos nomeados dinâmicos mais eficazes que funcionarão com todas as fórmulas e funcionalidades do Excel. Você não precisa editar o intervalo nomeado novamente ao alterar os dados.
⇬ Fazer download do arquivo:
Intervalos nomeados dinâmicos no ExcelComo usar intervalos nomeados no Excel
17 recursos surpreendentes de tabelas do Excel
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade
Como usar a função PROCV no Excel
Como usar a função CONT.SE no Excel
Como usar a função SUMIF no Excel