Uma introdução a Dynamic Ranges
A função VLOOKUP é freqüentemente usada para localizar informações armazenadas em tabelas no Excel. Por exemplo, se tivermos uma lista de nomes e idades de pessoas:
E então podemos em uma célula próxima usar a função VLOOKUP para determinar a idade de Paul:
Até agora, este é um padrão razoável. Mas o que acontece se precisarmos adicionar mais alguns nomes à lista? O pensamento óbvio seria modificar o intervalo em VLOOKUP. Porém, em um modelo realmente complexo, pode haver várias referências ao VLOOKUP. Isso significa que teríamos que alterar cada referência - supondo que soubéssemos onde elas estavam.
No entanto, o Excel oferece uma forma alternativa - chamada de intervalo DINÂMICO. Este é um intervalo que expande as atualizações automaticamente. Isso é perfeito se suas listas estão sempre se expandindo (por exemplo, dados de vendas mês a mês).
Para configurar um intervalo dinâmico, precisamos ter um nome de intervalo - então chamaremos nosso de AGE_DATA. A abordagem para configurar intervalos dinâmicos difere entre o Excel 2007 e versões anteriores do Excel:
No Excel 2007, clique em “Definir nome” nas fórmulas:
Em versões anteriores do Excel, clique em “Inserir” e depois em Nomes.
Na caixa pop-up, digite o nome de nossa faixa dinâmica - que é “AGE DATA”:
Na caixa com o rótulo “Refere-se a”, precisamos inserir o intervalo de nossos dados. Isso será obtido usado por uma função OFFSET. Isso tem 5 argumentos:
= OFFSET (Referência, Linhas, Cols, Altura, Largura)
- A referência é o endereço do canto SUPERIOR ESQUERDO do nosso intervalo - neste caso, a célula B5
- As linhas são o número de linhas da PARTE SUPERIOR ESQUERDA que queremos que o intervalo seja - que será 0 neste caso
- O Cols é o número de linhas da PARTE SUPERIOR ESQUERDA que queremos que o intervalo seja - que será 0 neste caso
- A altura do intervalo - veja abaixo para isso
- A largura do intervalo - isto é 2 se temos DUAS colunas em nosso intervalo (o nome da pessoa e sua idade)
Agora, a altura do intervalo terá que variar dependendo do número de entradas em nossa tabela (que atualmente é 7).
É claro que queremos uma maneira de contar as linhas em nossa tabela que seja atualizada automaticamente - então, uma maneira de fazer isso é usar a função CONT.valores. Isso apenas conta o número de células não vazias em um intervalo. Como nossos nomes estão na coluna B, o número de entradas em nossos dados é CONT.valores (B: B).
Observe que se você colocasse isso em uma célula, obteria o valor 8 - pois inclui os nomes de cabeçalho. No entanto, isso é imaterial.
Portanto, na caixa "Refere-se a", colocamos:
= OFFSET ($ B $ 5,0,0, contagens (B: B), 2)
E clique no botão OK. Nossa faixa dinâmica foi criada.
Agora retorne às fórmulas VLOOKUP e substitua o intervalo $ B: 4: $ C11 pelo nome do nosso novo intervalo dinâmico AGE_DATA para termos:
Até agora nada mudou. No entanto, se adicionarmos mais alguns nomes à nossa tabela:
E na célula onde tínhamos Paul, substitua por um novo nome como Pedro (que não estava na lista original):
E vemos que o Excel retornou automaticamente a idade de Pedro - embora não tenhamos alterado as fórmulas VLOOKUP. Em vez disso, o escopo do intervalo dinâmico aumentou para incluir os nomes extras.
Os intervalos dinâmicos são muito úteis quando temos volumes crescentes de dados - especialmente quando as tabelas VLOOKUP e PIVOT são necessárias.
