Tudo sobre intervalos nomeados do Excel - Nome do intervalo do Excel

Anonim

Enquanto trabalha no Excel, você deve ter ouvido falar sobre intervalos nomeados no Excel. Talvez de um amigo, colega ou algum tutorial online. Até eu já mencionei isso muitas vezes em meus artigos. Neste artigo, aprenderemos sobre os intervalos nomeados no Excel e exploraremos todos os seus aspectos.

O que são intervalos nomeados no Excel?

Bem, os intervalos nomeados nada mais são do que alguns intervalos do Excel que são marcados com algum nome significativo. Por exemplo, se você tem uma célula que diz B1, contém o alvo diário, você pode nomear essa célula como especificamente “Alvo”. Agora você pode usar “Alvo” para se referir a A1 em vez de escrever B1.
Em suma, intervalo nomeado é apenas nomear intervalos.

Como nomear um intervalo no Excel?

Defina o nome manualmente:

Para definir um nome para um intervalo, você pode usar o atalho CTRL + F3. Ou você pode seguir estas etapas.

    • Vá para a guia Fórmula
    • Localize a seção Nomes definidos e clique em Definir nomes. Ele abrirá o Gerenciador de nomes.

    • Clique em Novo.
    • Digite o nome.

  • Selecione o escopo (pasta de trabalho ou planilha)
  • Escreva um comentário se quiser.
  • Na caixa Refere-se a, escreva a referência ou selecione um intervalo usando o mouse.
  • Clique em OK. Está feito.

Agora você pode se referir a ele apenas digitando seu nome.
Existem algumas regras a seguir ao criar nomes. Eles são

  1. Os nomes não devem começar com dígitos ou caracteres especiais diferentes de sublinhado (_) e barra invertida (\).
  2. Os nomes não podem ter espaços e quaisquer caracteres especiais, exceto _ e \.
  3. O intervalo não deve ser nomeado como referência de célula. Por exemplo, os nomes A1, B1 ou AZ100 etc. são inválidos.
  4. Você não pode nomear um intervalo como “r” e “c” porque eles são reservados para referências de linhas e colunas.
  5. Dois intervalos nomeados não podem ter o mesmo nome em uma pasta de trabalho.
  6. O mesmo intervalo pode ter vários nomes.

Definir o nome automaticamente

Bem, na maior parte do tempo você trabalhará com uma tabela de dados estruturados. Eles terão colunas e linhas com títulos de coluna e títulos de linha. E na maioria das vezes esses nomes são significativos para os dados, e você gostaria de nomear seu intervalo como esses títulos de coluna. O Excel fornece uma ferramenta para nomear intervalos usando títulos automaticamente. Siga esses passos.

    • Os intervalos que você deseja nomear como títulos
    • pressione CTRL + SHIFT + F3, ou Localize a seção Nomes definidos na guia Fórmula e clique em Criar a partir da seleção.

    • A caixa de opção abaixo aparecerá. Selecionei a linha superior apenas porque quero nomear esses intervalos como o título e não quero nomear as linhas.

  • Clique OK.

Agora, cada coluna é nomeada como seu cabeçalho. Sempre que você digitar uma fórmula, esses nomes serão listados na opção a ser usada.

Intervalo de nomenclatura usando tabelas do Excel

Quando organizamos dados como uma tabela no Excel usando CTRL + T, os títulos das colunas são atribuídos automaticamente como o nome da respectiva coluna. Você deve explorar as tabelas do Excel e seus benefícios.

Como ver todos os intervalos nomeados?

Bem, haverá momentos em que você gostaria de ver todos os intervalos nomeados disponíveis na pasta de trabalho. Para ver todos os intervalos de nomes, pressione CTRL + F3. Ou você pode ir para Guia Fórmula> Gerenciador de nomes. Ele listará todos os intervalos nomeados disponíveis na pasta de trabalho. Você pode editar os intervalos nomeados disponíveis, excluí-los e adicionar novos nomes.

Um intervalo de vários nomes

O Excel permite que os usuários nomeiem o mesmo intervalo com nomes diferentes. Por exemplo, o intervalo A2: A10 pode ser denominado 'Clientes' e 'Clientes' ao mesmo tempo. Ambos os nomes se referem ao mesmo intervalo A2: A10.
Mas você não pode ter os mesmos nomes para dois intervalos diferentes. Isso elimina a chance de ambigüidade.

Obter lista de intervalos nomeados na planilha

Portanto, se você deseja ter uma lista de intervalos nomeados e os intervalos que eles estão cobrindo, você pode usar este atalho para colá-los em um local em uma planilha.

    • Selecione uma célula onde deseja obter a lista de intervalos nomeados.
    • Pressione F3. Isso abrirá um Paste nome caixa de diálogo.
    • Clique em colar lista botão.

  • A lista será colada na célula selecionada e assim por diante.


Se você clicar duas vezes no nome dos intervalos nomeados na caixa de colar nome, eles serão escritos como fórmulas na célula. Tente.

Atualizar intervalos nomeados manualmente

Bem, quando você insere uma célula dentro de um intervalo nomeado, ele é atualizado automaticamente e o expande. Mas se você adicionar dados no final da tabela, precisará atualizar o campo nomeado. Para atualizar intervalos nomeados, siga estas etapas.

  • pressione CTRL + F3, para abrir o gerenciador de nomes.
  • Clique no intervalo nomeado que você deseja editar. Clique em Editar.
  • Na coluna Refere-se à, digite o intervalo para o qual deseja expandir e clique em OK.

E está feito. Esta é a atualização manual de intervalos nomeados. No entanto, podemos torná-lo dinâmico usando algumas fórmulas.

Atualizar intervalos nomeados dinamicamente

É aconselhável tornar seus intervalos nomeados dinâmicos para que você não precise editá-los sempre que seus dados ultrapassarem o intervalo predefinido.
Eu cobri isso em um artigo separado chamado Intervalos nomeados dinâmicos. Você pode aprender e compreender os benefícios em detalhes aqui.

Excluindo intervalos nomeados

Quando você exclui alguma parte do intervalo nomeado, ele ajusta automaticamente seu intervalo. Mas quando você exclui todo o intervalo de nomes, o desaparece da lista de nomes. Qualquer fórmula, dependendo desses intervalos, exibirá o erro #REF ou fornecerá uma saída incorreta (funções de contagem).
Por qualquer motivo, se desejar excluir intervalos nomeados, siga estas etapas.

  • Pressione CTRL + F3. O gerenciador de nomes será aberto.
  • Selecione Intervalos nomeados que você deseja excluir.
  • Clique no botão Excluir ou pressione o botão Excluir no teclado.


Cuidado: Antes de excluir os intervalos nomeados, certifique-se de que nenhuma fórmula dependa desses nomes. Se houver, converta-os primeiro em intervalos. Caso contrário, você verá o erro #REF.

Exclusão de nomes com erros

O Excel fornece uma ferramenta para remover apenas nomes que contêm erros. Você não precisa identificar cada um deles sozinho. Para excluir nomes com erros, siga estas etapas:

    • Abra o Gerenciador de nomes (CTRL + F3).
    • Clique na lista suspensa Filtro no canto superior direito.
    • Selecione “Nome com erros”

  • Selecione Tudo e aperte o botão Excluir.

E eles se foram. Todos os nomes com erros serão excluídos do registro imediatamente.

Intervalos nomeados com fórmulas

O melhor uso de intervalos nomeados é experimentado com fórmulas. As fórmulas ficam mais flexíveis e legíveis com intervalos nomeados. Vamos ver como.

Fórmulas fáceis de escrever

Agora, digamos que você nomeou um intervalo como “Itens”. Agora a lista de itens que você deseja contar “Lápis”. Com o nome, é fácil escrever esta fórmula CONT.SE. Apenas escreva

= CONT.SE (Item, "Lápis")

Assim que você escrever o parêntese de abertura da fórmula, a lista de intervalos nomeados disponíveis aparecerá. Sem um nome, você escreveria uma função gi CONT.SE do Excel com intervalos, para a qual você pode ter que olhar para o intervalo primeiro e então selecionar o intervalo ou digitá-lo na fórmula.

O Excel atende aos intervalos de nomes disponíveis.

Os nomes dos intervalos são mostrados como sugestões quando você digita qualquer letra após o sinal =. Da mesma forma que o Excel mostra a lista de fórmulas. Por exemplo, se você digitar = u, cada método e intervalo nomeado será exibido começando com u, para que você possa usá-los facilmente.

Faça constantes usando intervalos nomeados

Até agora, aprendemos sobre intervalos de nomenclatura, mas você também pode nomear valores. Por exemplo, se o nome do seu cliente for Sunder Pichai, então você pode fazer um nome “Cliente” e ele se refere a escrever “Sundar Pichai”. Agora, sempre que você escrever = Cliente em qualquer célula, aparecerá Sundar Pichai.


Não apenas texto, mas você também pode atribuir um número como constante para trabalhar. Por exemplo, você define um alvo. Ou o valor de algo que não mudará.
Referência absoluta e relativa com intervalos nomeados

A referência com intervalos nomeados em é muito flexível. Por exemplo, se você escrever o nome de um intervalo nomeado em uma célula relativa, ele se comportará como uma referência correspondente. Veja a imagem abaixo.

Mas quando você o usa com fórmulas, ele se comporta como absoluto. Bem, na maioria das vezes você os usará com fórmulas, então você pode dizer que eles são, por padrão, absolutos, mas na verdade eles são flexíveis.

Mas podemos torná-los relativos também.

Como fazer intervalos nomeados relativos no Excel?

Digamos que se eu queira nomear um intervalo "Antes", que se refere à célula à esquerda de onde está escrito. Como faço isso? Siga esses passos:

  • Pressione CTRL + F3
  • Clique em Novo
  • Digite “Befor” na seção ‘Nome’.
  • Na seção ‘Refere-se a:’ escreva o endereço da célula à esquerda. Por exemplo, se você estiver na célula B1, escreva “= A2” na seção ‘Refere-se a:’. Certifique-se de que ele não tenha o sinal $.


Agora, onde quer que você escreva “Befor” na fórmula, ele se referirá à célula à esquerda dela.

Aqui, eu usei antes na função COLUMN. A fórmula retorna o número da coluna da célula esquerda onde está escrita. Para minha surpresa, A1 mostra o número da última coluna. O que significa que a folha está circulando. Achei que iria mostrar um erro #REF.

Dar nome a fórmulas usadas com frequência?

Agora, este é incrível. Muitas vezes você usa a mesma fórmula repetidamente em uma planilha. Por exemplo, você pode querer verificar se um nome está em sua lista de clientes ou não. E essa necessidade pode ocorrer muitas vezes. Para isso, você escreverá sempre a mesma fórmula complexa.

= IF (CONT.SE (Cliente, I3), "Na lista", "Fora da lista")

Que tal, se você apenas digitar ‘= IsInCustomer’ em uma célula e será mostrado se o valor na célula esquerda está na lista de clientes ou não?

Por exemplo, preparei uma mesa aqui. Agora eu só quero digitar “= IsInCustomer” no J5 e gostaria de ver se o valor em I5 está na lista de clientes ou não. Para fazer isso, siga estas etapas.

    • Pressione CTRL + F3
    • Clique em Novo
    • Em Nome, escreva ‘IsInCustomer”
    • Em ‘Refere-se a’ escreva sua fórmula. = IF (CONT.SE (Cliente, I5), "Na lista", "Fora da lista")

  • Clique no botão OK.

Agora, onde quer que você digite ‘IsInCustomer’, ele verificará o valor na célula esquerda da lista de clientes.

Isso impede que você se repita continuamente.

Aplicar intervalos nomeados às fórmulas

Muitas vezes, definimos nomes para nossos intervalos depois de já termos escrito fórmulas com base em intervalos. Por exemplo, tenho Preço Total como Células = E2 * F2. Como podemos mudar para Unidades * Unit_Cost.

    • Selecione as fórmulas.
    • Vá para a guia de fórmula. Clique na lista suspensa Definir nome.

  • Clique em Aplicar nomes.
  • Uma lista de todos os intervalos nomeados aparecerá. Escolha os nomes certos e clique em ok.


E os nomes agora são aplicados. Você pode ver isso na barra de fórmulas.

Fórmulas fáceis de ler com intervalos nomeados

Como você viu, os intervalos nomeados facilitam a leitura das fórmulas. Se eu escrever = CONTAR.SE (“A2: A100”, B2), ninguém vai entender o que estou tentando contar, até que veja os dados ou alguém explique para eles.

Mas se eu escrever = COUNTIF (região, 'leste'), a maioria dos usuários entenderá imediatamente que estamos contando a ocorrência de 'leste' na região chamada intervalo.

Fórmulas Portáteis

Os intervalos nomeados tornam muito fácil copiar e colar fórmulas sem se preocupar em alterar as referências. E você pode levar uma fórmula de uma pasta de trabalho para outra e funcionará bem até e a menos que a pasta de trabalho de destino tenha os mesmos nomes.

Por exemplo, se você tem uma fórmula = CONT.SE (região, leste) na Tabela de Distribuição e você tem outra pasta de trabalho, diz clientes que também tem um intervalo denominado “Região”. Agora, se você copiar esta fórmula diretamente para qualquer lugar da pasta de trabalho, ela mostrará as informações corretas. A estrutura dos dados não importa. Não importa onde diabos está essa coluna em sua pasta de trabalho. Isso funcionará corretamente.

Na imagem acima, usei exatamente a mesma fórmula em dois arquivos diferentes para contar o número ou o leste ocorrendo na lista de regiões. Agora eles estão em colunas diferentes, mas como ambas são nomeadas como regiões, isso funcionará perfeitamente.

Navegue facilmente na pasta de trabalho

Fica mais fácil navegar na pasta de trabalho com intervalos nomeados. Você só precisa digitar o nome do nomeado na caixa de nome. O Excel o levará ao intervalo, não importa onde você esteja na pasta de trabalho. Dado que o intervalo nomeado é do escopo da pasta de trabalho.
Por exemplo, se você está na planilha 10 e deseja obter a lista de clientes, mas não sabe em qual planilha ela está. Basta ir para a caixa de nome e digitar ‘cliente’. Você será direcionado para o intervalo nomeado em uma fração de segundo.


Isso reduzirá o esforço de lembrar os intervalos.

Navegar usando hiperlinks com intervalo nomeado

Quando sua planilha é grande e você costuma ir de um ponto a outro, você gosta de usar hiperlinks para navegar facilmente. Intervalos bem nomeados podem funcionar perfeitamente com hiperlinks. Para adicionar hiperlinks usando intervalos nomeados, siga estas etapas.

    • Escolha uma célula onde deseja hiperlink
    • Pressione CTRL + K ou vá para a guia Inserir> HyperLink para abrir a caixa de diálogo Inserir hiperlink.

    • Clique em Colocar neste Documento.
    • Role para baixo para ver os intervalos nomeados disponíveis em nomes definidos

  • Selecione o intervalo nomeado para inserir um hiperlink para esse intervalo.

E está feito. Você tem o seu hiperlink para o intervalo nomeado escolhido. Usando isso, você pode criar um índice de intervalos nomeados que você pode ver e clicar para navegar diretamente para eles. Isso tornará sua pasta de trabalho realmente amigável.

Intervalo Nomeado e Validação de Dados

Intervalos nomeados e validação de dados são feitos um para o outro. Intervalos nomeados tornam a validação de dados altamente personalizável. É muito mais fácil adicionar uma validação de uma lista usando um intervalo nomeado. Vamos ver como …

    • Vá para a guia Dados
    • Clique em Validação de Dados
    • Selecione a lista na seção ‘Permitir:’
    • Na seção ‘Fonte:’, digite “= Cliente” (escreva qualquer intervalo nomeado que você tenha)

  • Clique em OK

Agora, essa célula terá nomes de clientes que fazem parte do intervalo de nomes do cliente. Fácil, não é?

Validação de dados dependentes ou em cascata com intervalos nomeados

E se você quiser uma validação de dados em cascata ou dependente. Por exemplo, se você deseja uma lista suspensa com as categorias Frutas e Legumes. Agora, se você escolher frutas, outra lista suspensa deverá mostrar apenas a opção de frutas e se você escolher Vegetais, apenas vegetais.

Isso pode ser facilmente alcançado usando intervalos nomeados. Aprenda como.

  • Dependente suspenso usando intervalo nomeado
  • Outras maneiras de validação de dados em cascata

Sem Validação de Dados com Nomes de Dados Tabelados

Embora as tabelas do Excel forneçam nomes estruturados, elas não podem ser usadas com validação de dados e formatação condicional. Eu não sei por que o Excel não permite isso.
Mas isso não significa que não possa ser feito. Você pode nomear intervalos em uma tabela e usá-los para validação. O Excel não tem nenhum problema com isso.

Escopo de intervalos nomeados

Até agora, falamos sobre intervalos nomeados que tinham o escopo da pasta de trabalho. O que? Nós não discutimos isso? Ok, então vamos entender rapidamente o que é o escopo dos intervalos nomeados.

O que é o escopo de um intervalo de nomes?

O escopo do poço define onde um intervalo de nomes pode ser reconhecido. Qualquer nome não pode ser reconhecido fora de seu escopo. Por exemplo, um nome na pasta de trabalho1 não pode ser reconhecido em outra pasta de trabalho. O Excel fornece duas opções para o escopo de intervalos nomeados, planilha e pasta de trabalho.

Como definir um escopo de intervalo nomeado?

Ao criar um novo intervalo de nomes, você pode ver uma seção ‘Escopo:’. Clique na lista suspensa e escolha o escopo do seu intervalo de nomes. Você não pode alterar o escopo depois de criar um intervalo nomeado. Então é melhor fazer isso antes. Por padrão, é uma pasta de trabalho.

Escopo da pasta de trabalho

Este é o escopo padrão para um intervalo nomeado. Um nome definido com o escopo da pasta de trabalho pode ser usado em toda a pasta de trabalho na qual está definido (não em outras pastas de trabalho).
Todos os exemplos acima tiveram o escopo da pasta de trabalho.

Escopo da planilha

Um nome que é definido com um escopo de planilha só pode ser usado em definir planilha. Por exemplo, se eu definir 'Total' para uma célula total com o escopo da planilha1. Então, o total será reconhecido apenas na folha 1. Outras folhas não reconhecerão.

Eu quero um escopo Excel

O Excel não tem Escopo Global ou, digamos, Excel. Na verdade, gostaria de definir alguns nomes que podem ser reconhecidos em todas as pastas de trabalho do meu sistema. Se alguém souber como podemos fazer isso, me avise.

Editando o escopo após a criação de nomes

Você não pode. O Excel não permite que você edite o escopo de um intervalo nomeado depois de criá-lo. Como todos os intervalos nomeados em uma planilha são, por padrão, com escopo de pasta de trabalho, você pode querer alterar seu escopo para uma planilha.
Para fazer isso, basta fazer uma cópia dessa planilha e o Excel tornará cada nome nessa planilha local para evitar ambigüidades. Agora você pode excluir a folha original, se desejar.

Cortar faixa de nome de pasta

Quando você recorta e cola um intervalo nomeado de um destino para outro, a referência muda para um novo local. Por exemplo, se você tiver um intervalo nomeado “Cliente” em A2: A10 e recortar e colar em B2: B10, o nome do cliente se referirá ao novo local B2: B10.

Intervalos nomeados dinâmicos 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