Intervalos nomeados dinâmicos no Microsoft Excel

Este post foi originalmente publicado em  http://officevbavsto.blogspot.com/2011/09/intervalo-nomeado-dinamico.html.

Hoje, escrevo sobre como utilizar uma Função PROCV buscando a origem de dados a partir de um intervalo nomeado dinâmico.

Neste exemplo, eu seleciono um intervalo de células em uma planilha e as dou um nome qualquer (“base_de_clientes”, por exemplo). Em seguida, eu utilizo a função PROCV para buscar os dados a partir deste intervalo células referenciando o nome que eu dei. Adicionalmente, ensino a tornar dinâmico este intervalo de células nomeado. Assim você não precisa redefinir o intervalo todas as vezes que sua base de dados aumentar ou diminuir.

O que é um intervalo nomeado de células?

Um intervalo nomeado é um rótulo ou uma abreviação com algum significado específico, o qual facilita o entendimento do propósito de uma célula, constante, fórmula ou tabela, os quais podem ser difíceis de entender a primeira vista [1], mas com a ajuda do Intervalo Nomeado se tornam de fácil compreensão. Uma vez que o Intervalo Nomeado é definido, você pode usá-lo em qualquer local onde você normalmente utilizaria o endereço da célula [2].

A fórmula do “Cálculo Final” seria “=B5*B2”. Utilizando o Intervalo Nomeado eu poderia reescrever a fórmula como “=Rendimento*AliquotaIR”, o que facilitaria em muito o entendimento e manutenção desta fórmula.

Criando Intervalos Nomeados (Excel 2007 e 2010):

1. Com o mouse clique selecionando o intervalo de células “B2:G27”;
2. Clique na aba "Fórmulas";
3. No grupo "Nomes Definidos", clique em "Definir Nome";

4. Na tela "Novo Nome", digite "BaseDados" no campo "Nome";

5. Clique em OK.

Pronto! O intervalo nomeado está criado!

Em qualquer lugar da planilha digite a seguinte fórmula:

=PROCV("Bon app'";BaseDados;3;0)

Ao invés de eu digitar na função PROCV o intervalor "Plan1!B2:G27", eu digito o nome do Intervalo Nomeado “BaseDados”. Desta maneira, minhas fórmulas ficam muito mais fáceis de entender e manter.

A fórmula ficaria assim se não usássemos o intervalo nomeado:

=PROCV("Bon app'";Plan1!B2:G27;3;0)

O nome "BaseDados" é bem genérico. Você poderia escrever "BaseDadosVendas", ou "BaseDadosMarketing", "BaseDadosFuncionarios"  e etc.

Agora, e se a minha base de dados aumentar? O intervalo que definimos vai até a linha 27. Se a base de dados aumentar, a Fórmula que escrevemos buscará os dados somente até a linha 27 da planilha. Para ajustar isso eu teria que alterar manualmente os limites do Intervalo Nomeado “BaseDados”. É ai que entra o Intervalo Nomeado Dinâmico.

Vamos usar a função DESLOC(ref;lins;cols;altura;largura)...

A função DESLOC retorna uma referência para um intervalo, que é um número especificado de linhas e colunas de uma célula ou intervalo de células [3].

Vamos a alguns exemplos de uso. Na planilha que estou usando como exemplo, digite a seguinte fórmula:

=DESLOC(Plan1!B3;0;0)

O que ele fez? Absolutamente nada a não ser retornar o valor da célula B3.

Vamos ao exemplo seguinte:

=DESLOC(Plan1!B3;1;0)

O que ele fez? Deslocou uma linha abaixo da célula B3, então ele retornou o valor da célula B4.

Outro:

=DESLOC(Plan1!B3;0;1)

O que ele fez? Deslocou uma coluna à direita da célula B3, ou seja, retornou o valor da célula C3.

Você pode usar valores negativos nos parâmetros lins e cols, o que fará retornar uma linha anterior e uma coluna à esquerda.

Agora, o que nos interessa são os dois últimos parâmetros de DESLOC: [altura] e [largura]. Basicamente ele nos diz: quantas linhas/colunas você quer retornar?

Veja o exemplo abaixo:

=DESLOC(Plan1!B3;0;0;2)

O que está acontecendo? Estou simplesmente dizendo para retornar a partir da célula B3, mais uma linha, então está sendo retornado o intervalo “B3:B4”. Se você usar a fórmula da função acima diretamente na célula, você receberá o erro #VALOR!.

Onde podemos usar este exemplo? Numa PROCV, por exemplo. Veja abaixo:

=PROCV("Bon app'";DESLOC(Plan1!B3;0;0;25);1;0)

Vejam que aumentei intervalo de procura a partir da célula B3 para 25 linhas, então minha PROCV está procurando o valor "Bon app'" no intervalo “B3:B27”.

Outro exemplo, mas aumentando o intervalo de colunas:

=PROCV("Bon app'";DESLOC(Plan1!B3;0;0;25;6);5;0)

Agora, mas vamos pensar um pouco: Como deixar esse intervalo dinâmico?Use a função CONT.VALORES(valor1, [valor2], ...). A função CONT.VALORES conta o número de células que não estão vazias em um intervalo [4].

Então nossa fórmula ficaria assim:

=PROCV("Bon app'";DESLOC(Plan1!B3;0;0;CONT.VALORES(Plan1!B:B);CONT.VALORES(Plan1!2:2));5;0)

Vamos definir um nome para o intervalo o qual a PROCV está pesquisando.

Refazendo os mesmos passos descritos anteriormente para criar intervalo nomeado, na tela de "Novo Nome", digite toda a fórmula DESLOC na Caixa de Texto "Refere-se a:":

=DESLOC(Plan1!$B$3;0;0;CONT.VALORES(Plan1!$B:$B);CONT.VALORES(Plan1!$2:$2))

Deve ficar conforme Figura 12 (abaixo):

Importante: No caso do intervalo nomeado deixe as células fixas com o símbolo $.

E agora, reescrevemos nossa fórmula anterior:

=PROCV("Bon app'";BaseDadosDinamica;5;0)

Pronto!

Experimente acrescentar novos registros na sua base de dados e veja que o intervalo nomeado dinâmico acompanhará o crescimento.

Crédito

Photo by Negative Space from Pexels

Referência Bibliográfica

1. names, using. "Define and use names in formulas - Excel - Office.com." Office - Office.com. N.p., n.d. Web. 13 July 2013. http://office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010147120.aspx;
2. default, and in the Names dialog.. " Defined Names." Excel Redirect. N.p., n.d. Web. 13 July 2013. http://www.cpearson.com/excel/DefinedNames.aspx;
3. "DESLOC - Excel - Office.com."  Office - Office.com. N.p., n.d. Web. 13 July 2013. http://office.microsoft.com/pt-br/excel-help/desloc-HP005209208.aspx;
4. "Função CONT.VALORES - Excel - Office.com."  Office - Office.com. N.p., n.d. Web. 13 July 2013. http://office.microsoft.com/pt-br/excel-help/funcao-cont-valores-HP010062493.aspx.