Como usar nomes em fórmulas no Excel

Anonim

Neste artigo, aprenderemos como usar nomes em uma fórmula no Excel.

Cenário:

Enquanto trabalha no Excel, você deve ter ouvido falar sobre intervalos nomeados no Excel. Talvez de um amigo, colega ou algum tutorial online. Já mencionei isso muitas vezes em meus artigos. Neste artigo, aprenderemos sobre Intervalo Nomeado no Excel e exploraremos cada um de seus aspectos.

Intervalos nomeados no Excel

Fórmulas -> Gerenciador de Nomes -> Definir Nome

Bem, os intervalos nomeados nada mais são do que alguns intervalos do Excel que recebem algum nome significativo. Por exemplo, se você tem uma célula B1, contendo alvos diários, você pode nomear essa célula especificamente como “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 um atalho CTRL + F3. Ou você pode seguir estas etapas.

  • Vamos para Fórmula Aba
  • Localize a seção Nomes definidos e clique em Definir nomes. Isso irá abrir o Name Manger.
  • 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. Aqui estão alguns.

  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 A1, B1 ou AZ100 etc.
  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.

Bem, na maioria das vezes você trabalhará com tabelas 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 automaticamente os intervalos usando cabeçalhos. 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 apenas a linha superior, pois 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 como opções que podem ser acessadas.

Quando tabulamos dados no Excel usando CTRL + T, o título da coluna é automaticamente atribuído como o nome da respectiva coluna. Você deve explorar as tabelas do Excel e seus benefícios.

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 de Fórmula > Gerente de Nomes. Isso 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 é bom. 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 no lugar em uma planilha.

  • Selecione uma célula onde deseja obter uma lista de intervalos nomeados.
  • Pressione F3. Isso abrirá um colar lista caixa de diálogo.
  • Clique na pasta Lista botão.
  • A lista será colada nas células selecionadas 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 se atualiza automaticamente e o expande. Mas se você adicionar dados no final da tabela, precisará atualizar o intervalo nomeado. Para atualizar intervalos nomeados, siga estas etapas.

  • pressione CTRL + F3 para abrir o gerente de nome.
  • 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 a parte da soma do intervalo nomeado, ele ajusta automaticamente seu intervalo. Mas, quando você exclui, todo o intervalo de nomes desaparece da lista de nomes. Qualquer fórmula, dependendo desses intervalos, exibirá o erro #REF ou dará saída incorreta (funções de contagem).

Por qualquer motivo, se você deseja excluir os intervalos nomeados, basta seguir 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 ferramentas para excluir 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 é explorado com fórmulas. As fórmulas ficam realmente 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 nomes, é 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 iráaparecer

Sem o nome, você escreveria um intervalo para a função CONT.SE do Excel, 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 intervalos nomeados são mostrados como sugestões quando você digita qualquer letra. Como o Excel mostra a lista de fórmulas. Por exemplo, se você digitar = u, cada fórmula e intervalo nomeado serão exibidos 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 criar 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 números como constantes 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 ao intervalo nomeado, ele se comportará como uma referência relativa. Veja a imagem abaixo.

Mas quando você o usa com fórmulas, ele se comportará 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 como "Befor", 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 não tenha o sinal $.

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

Aqui, eu usei isso 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.

= SE (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. = SE (CONT.SE (Cliente, I5), "Na lista", "Fora da lista")
  • Clique no botão OK.

Agora, onde quer que você digite ‘IsInCustomer’, o valor será verificado 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 ocorrências 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 o mesmo nome.

Por exemplo, se você tiver uma Fórmula = CONT.SE (região, leste) na Tabela de Distribuição e você tem outra pasta de trabalho dizendo 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 leste ocorrendo na lista de regiões. Agora eles estão em colunas diferentes, mas como ambos são nomeados como regiões, isso funcionará perfeitamente.

Navegue facilmente na pasta de trabalho

Fica mais fácil navegar em uma pasta de trabalho com intervalos nomeados. Você só precisa digitar o nome do nome 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 uma 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 denominado 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 que contenha 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

Espero que este artigo sobre como usar nomes em uma fórmula no Excel seja explicativo. Encontre mais artigos sobre intervalos de nomenclatura e fórmulas Excel relacionadas aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail.

A caixa de nome no Excel : A Caixa de Nome do Excel nada mais é do que uma pequena área de exibição no canto superior esquerdo da planilha do Excel que mostra o nome da célula ativa ou intervalos no Excel. Você pode renomear uma célula ou matriz para referências.

Como obter o nome da planilha no Excel : A função CELL no Excel fornece informações sobre qualquer planilha, como col, conteúdo, nome do arquivo, etc. Aprenda como obter o nome da planilha usando a função CELL aqui.

Como obter o número da linha sequencial no Excel: Às vezes, precisamos obter um número de linha sequencial em uma tabela, pode ser um número de série ou qualquer outra coisa. Neste artigo, aprenderemos como numerar linhas no Excel desde o início dos dados.

Incremente um número em uma string de texto no Excel: Se você tiver uma lista grande de itens e precisar aumentar o último número do texto do texto antigo no excel, você precisará da ajuda das duas funções TEXTO e DIREITO.

Artigos populares :

Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.

Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.

Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.

Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.