Intervalos nomeados no Excel (Um guia completo)

Quer aprender a criar intervalos nomeados no Excel? Então você está no lugar certo!

Se você estiver trabalhando com planilhas do Excel, criar intervalos nomeados pode significar muita economia de tempo e eficiência na hora de criar suas fórmulas.

Neste tutorial, você aprenderá como criar intervalos nomeados no Excel e como usá-lo para economizar tempo. Confira!


Já está interessado(a) no que vem por aí? Então compartilhe com sua rede de amigos para que mais pessoas possam aprender a criar e gerenciar intervalos nomeados no Excel:

Facebook

Twitter

Google+

LinkedIn

O que são Intervalos nomeados no Excel?

Se alguém tiver que te ligar ou se referir a você, provavelmente usará o seu nome em vez de dizer, um homem/ mulher com altura X e peso Y. Certo?

Da mesmo modo, no Excel, você pode atribuir um nome a uma célula ou a um intervalo de células. Ou seja, em vez de usar a referência de célula (como A1 ou A1:C10), você pode simplesmente usar um nome atribuído a ela.

Por exemplo, suponha-se que você tenha um conjunto de dados conforme mostrado abaixo:

O que são Intervalos nomeados no Excel

Neste exemplo, se você precisar se referir ao intervalo que tem a datas, você precisará usar A2:A11 em suas fórmulas. Do mesmo modo, para Vendedores e Valor, você terá que usar B2:B11 e C2:C11.

Pode parecer simples quando se tem uma pequena quantidade de dados, mas em casos onde você tem dados mais complexos, o uso de referências de células pode ser demorado e trabalhoso. E é neste momento que os intervalos nomeados do Excel facilitam a referência aos conjuntos de dados.

Você pode criar um intervalo nomeado no Excel para cada categoria de dados e, em seguida, usar esse nome em vez das referências de célula. Por exemplo, as datas podem ser nomeadas como ‘Data‘, os nomes dos vendedores podem ser nomeados ‘Vendedores‘ e os dados de vendas podem ser denominados ‘Valor‘.

exemplo de Intervalos nomeados

Você também pode criar um nome para uma única célula. Por exemplo, se você tiver a porcentagem de comissão de vendas em uma célula, poderá nomear essa célula como ‘Comissão’.

Benefícios do uso de intervalos nomeados no Excel

Abaixo listamos os principais benefícios de usar intervalos nomeados no Excel.

Use nomes em vez de referências de célula

Ao criar intervalos nomeados no Excel, você pode usar esses nomes em vez das referências de célula. Por exemplo, você pode usar =SOMA(VENDAS) em vez de =SOMA(C2:C11).

Veja outros exemplos abaixo onde são usados os intervalos nomeados em vez de referências de células:

  • Número de vendas com valor superior a 500: =CONT.SE (VENDAS;”>500″)
  • Soma de todas as vendas feitas por Tomas: =SOMASE(Vendedores;Tomas;Vendas)
  • Comissão ganha por José (vendas de José multiplicadas por porcentagem de comissão):
    =SOMASE(Vendedores;”José”;Vendas)*Comissão

E convenhamos, essas fórmulas são fáceis de criar e fáceis de entender (especialmente quando você as compartilha com outras pessoas ou as revisa por conta própria).

Não é preciso voltar ao conjunto de dados para selecionar células

Outra vantagem significativa do uso de intervalos nomeados no Excel é que você não precisa voltar e selecionar os intervalos de células.

Você pode digitar apenas algumas letras desse intervalo nomeado e o Excel mostrará os intervalos nomeados correspondentes (conforme mostrado abaixo):

Benefícios dos intervalos nomeados no Excel

Intervalos nomeados tornam as fórmulas dinâmicas

Ao usar intervalos nomeados no Excel, você pode deixar suas fórmulas dinâmicas.

Por exemplo, no caso de comissão de vendas, em vez de usar o valor de 4,5%, você pode usar o intervalo nomeado. Deste modo, se sua empresa posteriormente decidir aumentar a comissão para 5%, você pode simplesmente atualizar o intervalo nomeado, e todo o cálculo será atualizado automaticamente para refletir a nova comissão.

Como criar intervalos nomeados no Excel

Basicamente existem três maneiras de criar intervalos nomeados no Excel. Confira a seguir cada uma delas:

Método 1: Usando “Definir Nome”

1. Selecione o intervalo no qual você deseja criar um intervalo nomeado no Excel.

intervalos nomeados no Excel - Definir Nome

2. Acesse a guia Fórmulas > Definir Nome.

Definir nome no Excel

3. Na caixa de diálogo Novo Nome, digite o nome que você deseja atribuir ao intervalo de dados selecionado. Você pode especificar o escopo como a pasta de trabalho inteira ou uma planilha específica. Se você selecionar uma folha específica, o intervalo nomeado não estará disponível em outras planilhas.

Novo nome Excel4. Clique em OK para finalizar.

Método 2: Usando Caixa de Nome

1. Selecione o intervalo no qual você deseja criar um nome (não selecione cabeçalhos).

2. Vá para a caixa de nome à esquerda da barra de fórmulas e digite o nome no qual você deseja criar o intervalo nomeado e pressione ENTER para salvar o nome.

Caixa de Nome no Excel

Note que o nome criado com esse método estará disponível para toda a pasta de trabalho. Portanto, se você deseja restringir o nome a uma planilha específico, use o método 1.

Método 3: Usando “Criar a partir da Seleção”

Este método é recomendada quando você tem dados em formato tabular e deseja criar um intervalo nomeado para cada coluna / linha.

Por exemplo, no conjunto de dados abaixo, se você quiser criar rapidamente três intervalos nomeados (Data, Vendedores e Valor), poderá usar o método mostrado abaixo.

Criar a partir da Seleção Excel

Aqui estão os passos para criar rapidamente intervalos nomeados a partir de um conjunto de dados:

1. Selecione o conjunto de dados inteiro (incluindo os cabeçalhos).

2. Acesse a guia Fórmulas > Criar a partir da Seleção (Atalho Ctrl+ Shift + F3). O Excel abrirá a caixa de diálogo ‘Criar nomes a partir da seleção’.

Criar a partir da Seleção

3. Na caixa de diálogo Criar nomes da Seleção, marque as opções em que você tem os cabeçalhos. Nesse caso, selecionamos a linha superior apenas porque o cabeçalho está na linha superior. Se você tiver cabeçalhos na coluna superior e na esquerda, poderá escolher os dois. Da mesma forma, se os dados estiverem organizados quando os cabeçalhos estiverem apenas na coluna da esquerda, você só marcará a opção Coluna Esquerda.

Criar a partir da Seleção - Intervalos nomeados

Isso irá criar automaticamente três intervalos nomeados – Data, Vendedores e Valor. Se houver algum espaço entre palavras, o Excel irá inserir um sublinhado (pois não é possível ter espaços em intervalos nomeados).

Como editar intervalos nomeados no Excel

Se você já criou um intervalo nomeado seguindo um dos passos acima e agora precisa editá-lo, basta seguir as etapas abaixo:

1. Acesse a guia Fórmulas e clique em Gerenciador de nomes.

Gerenciador de Nomes

2. Será aberta a caixa de diálogo ‘Gerenciador de nomes’ que listará todos os intervalos nomeados nessa pasta de trabalho. Clique duas vezes no intervalo nomeado que você deseja editar.

Como usar o Gerenciador de Nomes

3. Na caixa de diálogo ‘Editar nome’, faça as alterações.

Editar nomes no Excel

4. Clique OK e feche a caixa de diálogo Gerenciador de nomes.


Como criar intervalos nomeados dinâmicos

Até aqui você aprendeu a criar intervalos nomeados estáticos. Ou seja, se por exemplo, A1:A10 tiver sido nomeado como ‘Vendas’ e você adicionar mais dados a partir de A11, então será necessário atualizar a referência no intervalo nomeado manualmente.

No entanto, se você estiver trabalhando com uma planilha em constante expansão, isso pode acabar consumindo muito do seu tempo, pois sempre que você obter novos dados, será necessário atualizar os intervalos nomeados no Excel.

Para resolver esse problema, é possível criar intervalos nomeados dinâmicos no Excel que automaticamente considerariam dados adicionais e o incluiriam no intervalo nomeado existente.

Por exemplo, se adicionarmos dois dados de vendas adicionais, um intervalo com nome dinâmico passará a se referir automaticamente de A1:A10 para A1:A12.

intervalos nomeados dinâmicos

A seguir estão as etapas para criar intervalos nomeados dinâmicos no Excel:

1. Acesse a guia Fórmula e clique em Definir nome.

Como criar intervalos nomeados dinâmicos

2. Na caixa de diálogo Novo Nome, digite o seguinte:

  • Nome: Vendas
  • Escopo: Workbook
  • Refere-se a: =$A$2:ÍNDICE($A$2:$A$100;CONT.SE($A$2:$A$100;”<>”&””))

intervalos nomeados dinâmicos com o Excel

3. Clique em OK.

Pronto! Agora você tem um intervalo nomeado dinâmico com o nome “Vendas”. Isso será atualizado automaticamente sempre que você adicionar ou remover dados dele.

Como funcionam os intervalos dinâmicos?

Para entender como funciona, você precisa conhecer um pouco mais sobre a função Excel ÍNDICE.

A maioria das pessoas conhece e usa a função ÍNDICE para retornar um valor de uma lista com base no número da linha e da coluna. No entanto, ela pode ser usada para retornar uma referência de célula quando ela é usada como parte de uma referência de célula.

Por exemplo, aqui está a fórmula que usamos para criar um intervalo nomeado dinâmico:

=$A$2:ÍNDICE($A$2:$A$100;CONT.SE($A$2:$A$100;"<>"&""))

ÍNDICE($A$2:$A$100;CONT.SE($A$2:$A$100;”<>”&””)) Espera-se que esta parte da fórmula retorne um valor (que seria o décimo valor do lista, considerando que existem dez itens).

No entanto, quando usado em frente a uma referência (=$A$2: ÍNDICE($A$2:$A$100;CONT.SE($A$2:$A$100;”<>”&””)) retorna a referência para a célula em vez do valor.

Portanto, aqui ela retornará = $A$2:$A$11

Se adicionarmos dois valores adicionais à coluna, a função retornará = $A$2:$A$13

Quando você adiciona novos dados à lista, a função CONT.SE retorna o número de células não vazias nos dados. Esse número é usado pela função ÍNDICE para buscar a referência de célula do último item da lista.

Importante:

  • Isso só funcionaria se não houvesse células em branco nos dados.
  • No exemplo acima, atribuímos um grande número de células (A2:A100) para a fórmula de intervalo nomeado. Você pode ajustar isso conforme a sua necessidade.

Você também pode usar a função DESLOC para criar intervalos dinâmicos no Excel, no entanto, como a função DESLOC é volátil, isso poderá deixar sua planilha lenta. A função ÍNDICE, por outro lado, é semi-volátil, o que o torna uma opção melhor para criar intervalos nomeados dinâmicos no Excel.


Dicas para usar intervalos nomeados no Excel

Exitem certas regras de nomenclatura que você precisa saber ao criar intervalos nomeados no Excel. Vejamos quais são:

  • O primeiro caractere de um intervalo nomeado deve obrigatoriamente ser uma letra, sublinhado (_) ou uma barra invertida (\). Qualquer coisa além disso, será mostrado um erro. Os demais caracteres podem ser letras, números, caracteres especiais, ponto final ou sublinhado.
  • Você não pode usar nomes que também representam referências de célula no Excel. Por exemplo, você não pode usar o AD1, pois também é uma referência de célula.
  • Ao criar intervalos nomeados, não utilize espaços. Por exemplo, você não pode ter o nome “representante de vendas” como um intervalo nomeado. Se você quiser combinar duas palavras, use um sublinhado (_) ou caracteres maiúsculos para criá-lo. Por exemplo, você pode ter Rep_Vendas ou RepVendas.
    • Ao criar intervalos nomeados, o Excel trata maiúsculas e minúsculas da mesma maneira. Por exemplo, se você criar um intervalo nomeado VENDAS, não poderá criar outro intervalo nomeado, como ‘vendas’ ou ‘Vendas’.
  • Um intervalo nomeado pode ter até 255 caracteres.

Atalhos importantes:

  • Para obter uma lista de todos os intervalos nomeados e colá-lo na fórmula: F3
  • Para criar um novo nome usando a caixa de diálogo Gerenciador de nomes:  Ctrl + F3
  • Para criar intervalos nomeados a partir da seleção: Ctrl + Shift + F3

E então, o que achou da explicação sobre intervalos nomeados no Excel? Deixe o seu comentário abaixo, gostaríamos muito de conhecer a sua opinião! Para conferir mais dicas de Excel, continue acompanhando o Excel Easy!