Como criar intervalos nomeados no Excel (um guia passo a passo)

Qual é o nome?

Se você estiver trabalhando com planilhas do Excel, isso pode significar uma grande economia de tempo e eficiência.

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

Intervalos nomeados no Excel - uma introdução

Se alguém tiver que me ligar ou se referir a mim, eles usarão meu nome (em vez de dizer que um homem fica em fulano de tal lugar com fulano de altura e peso).

Certo?

Da mesma forma, no Excel, você pode dar um nome a uma célula ou a um intervalo de células.

Agora, em vez de usar a referência de célula (como A1 ou A1: A10), você pode simplesmente usar o nome que atribuiu a ela.

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

Neste conjunto de dados, se você tiver que se referir ao intervalo que contém a Data, você terá que usar A2: A11 nas fórmulas. Da mesma forma, para Representante de vendas e Vendas, você terá que usar B2: B11 e C2: C11.

Embora esteja tudo bem quando você tem apenas alguns pontos de dados, mas no caso de grandes conjuntos de dados complexos, usar referências de células para se referir aos dados pode ser demorado.

Os intervalos nomeados do Excel facilitam a referência a conjuntos de dados no Excel.

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 chamadas de 'Data', os dados do representante de vendas podem ser chamados de 'SalesRep' e os dados de vendas podem ser chamados de 'Vendas'.

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

Benefícios da criação de intervalos nomeados no Excel

Aqui estão os benefícios de usar intervalos nomeados no Excel.

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

Ao criar intervalos nomeados no Excel, você pode usar esses nomes em vez das referências de células.

Por exemplo, você pode usar = SUM (SALES) em vez de = SUM (C2: C11) para o conjunto de dados acima.

Dê uma olhada nas fórmulas listadas abaixo. Em vez de usar referências de células, usei os intervalos nomeados.

• Número de vendas com valor superior a 500: = CONT.SE (Vendas, ”> 500 ″)
• Soma de todas as vendas feitas por Tom: = SUMIF (SalesRep, ”Tom”, Vendas)
• Comissão ganha por Joe (vendas por Joe multiplicadas pela porcentagem de comissão):
  = SUMIF (SalesRep, ”Joe”, Vendas) * Comissão

Você concorda que essas fórmulas são fáceis de criar e entender (especialmente quando você as compartilha com outra pessoa ou as revisita por conta própria.

Não há necessidade de voltar ao conjunto de dados para selecionar células

Outro benefício significativo de usar intervalos nomeados no Excel é que você não precisa voltar e selecionar os intervalos de células.

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

Intervalos nomeados tornam as fórmulas dinâmicas

Usando intervalos nomeados no Excel, você pode tornar as fórmulas do Excel dinâmicas.

Por exemplo, no caso de comissão de vendas, em vez de usar o valor 2,5%, você pode usar o intervalo nomeado.

Agora, se sua empresa posteriormente decidir aumentar a comissão para 3%, 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

Aqui estão três maneiras de criar intervalos nomeados no Excel:

Método # 1 - Usando Definir Nome

Aqui estão as etapas para criar intervalos nomeados no Excel usando Definir nome:

• Selecione o intervalo para o qual deseja criar um intervalo nomeado no Excel.
• Vá para Fórmulas -> Definir nome.
• Na caixa de diálogo Novo nome, digite o nome que 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 planilha específica, o nome não estará disponível em outras planilhas.
• Clique OK.

Isso criará um intervalo nomeado SALESREP.

Método 2: usando a caixa de nome

• Selecione o intervalo para o qual deseja criar um nome (não selecione cabeçalhos).
• Vá para a Caixa de Nome à esquerda da barra de Fórmula e digite o nome do com o qual deseja criar o Intervalo Nomeado.
• Observe que o Nome criado aqui estará disponível para toda a pasta de trabalho. Se você deseja restringir a uma planilha, use o Método 1.

Método # 3: Usando Criar a partir da Opção de Seleção

Essa é a maneira 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ê deseja criar rapidamente três intervalos nomeados (Data, Sales_Rep e Vendas), você pode usar o método mostrado abaixo.

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

• Selecione todo o conjunto de dados (incluindo os cabeçalhos).
• Vá para Fórmulas -> Criar a partir da Seleção (Atalho de teclado - Control + Shift + F3) Isso abrirá a caixa de diálogo ‘Criar nomes a partir da seleção’.
• Na caixa de diálogo Criar nomes a partir da seleção, marque as opções onde 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 linha superior e na coluna esquerda, poderá escolher ambos. Da mesma forma, se seus dados são organizados quando os cabeçalhos estão apenas na coluna da esquerda, você só marca a opção Coluna da esquerda.

Isso criará três intervalos nomeados - Data, Sales_Rep e Vendas.

Observe que ele coleta automaticamente os nomes dos cabeçalhos. Se houver algum espaço entre as palavras, ele insere um sublinhado (porque você não pode ter espaços em intervalos nomeados).

Convenção de nomenclatura para intervalos nomeados no Excel

Existem certas regras de nomenclatura que você precisa saber ao criar intervalos nomeados no Excel:

• O primeiro caractere de um intervalo nomeado deve ser uma letra e um caractere de sublinhado (_) ou uma barra invertida (\). Se for qualquer outra coisa, ele mostrará um erro. Os caracteres restantes podem ser letras, números, caracteres especiais, ponto ou sublinhado.
• Você não pode usar nomes que também representam referências de células no Excel. Por exemplo, você não pode usar AB1, pois também é uma referência de célula.
• Você não pode usar espaços ao criar intervalos nomeados. Por exemplo, você não pode ter o representante de vendas como um intervalo nomeado. Se você deseja combinar duas palavras e criar um intervalo nomeado, use um sublinhado, ponto ou caracteres maiúsculos para criá-lo. Por exemplo, você pode ter Sales_Rep, SalesRep ou SalesRep.
  • 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.

Muitos intervalos nomeados no Excel? Não se preocupe

Às vezes, em grandes conjuntos de dados e modelos complexos, você pode acabar criando muitos intervalos nomeados no Excel.

E se você não se lembrar do nome do intervalo nomeado que criou?

Não se preocupe - aqui estão algumas dicas úteis.

Obtendo os nomes de todos os intervalos nomeados

Aqui estão as etapas para obter uma lista de todos os intervalos nomeados que você criou:

• Vá para a guia Fórmulas.
• No grupo Nome Definido, clique em Usar na Fórmula.
• Clique em ‘Colar Nomes’.

Isso lhe dará uma lista de todos os intervalos nomeados nessa pasta de trabalho. Para usar um intervalo nomeado (em fórmulas ou uma célula), clique duas vezes nele.

Exibindo os intervalos nomeados correspondentes

• Se você tem alguma ideia sobre o Nome, digite alguns caracteres iniciais e o Excel mostrará uma lista suspensa dos nomes correspondentes.

Como editar intervalos nomeados no Excel

Se você já criou um intervalo nomeado, pode editá-lo usando as seguintes etapas:

• Vá para a guia Fórmulas e clique em Gerenciador de Nomes.
• A caixa de diálogo Gerenciador de nomes listará todos os intervalos nomeados nessa pasta de trabalho. Clique duas vezes no intervalo nomeado que você deseja editar.
• Na caixa de diálogo Editar nome, faça as alterações.
• Clique OK.
• Feche a caixa de diálogo Gerenciador de Nomes.

Atalhos de intervalo nomeados úteis (o poder de F3)

Aqui estão alguns atalhos de teclado úteis que serão úteis quando você estiver trabalhando com intervalos nomeados no Excel:

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

Criação de intervalos nomeados dinâmicos no Excel

Até agora neste tutorial, criamos Named Ranges estáticos.

Isso significa que esses intervalos nomeados sempre se referem ao mesmo conjunto de dados.

Por exemplo, se A1: A10 foi nomeado como ‘Vendas’, sempre se referiria a A1: A10.

Se você adicionar mais dados de vendas, terá que ir manualmente e atualizar a referência no intervalo nomeado.

No mundo de conjuntos de dados em constante expansão, isso pode acabar tomando muito do seu tempo. Cada vez que você obtém novos dados, pode ter que atualizar os intervalos nomeados no Excel.

Para resolver esse problema, podemos criar intervalos nomeados dinâmicos no Excel que contabilizariam automaticamente dados adicionais e os incluiria no intervalo nomeado existente.

Por exemplo, por exemplo, se eu adicionar dois pontos de dados de vendas adicionais, um intervalo nomeado dinâmico se referirá automaticamente a A1: A12.

Este tipo de Intervalo Nomeado Dinâmico pode ser criado usando a função ÍNDICE do Excel. Em vez de especificar as referências de célula ao criar o intervalo nomeado, especificamos a fórmula. A fórmula é atualizada automaticamente quando os dados são adicionados ou excluídos.

Vamos ver como criar intervalos nomeados dinâmicos no Excel.

Suponha que temos os dados de vendas na célula A2: A11.

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

1. 1. Vá para a guia Fórmula e clique em Definir Nome.
   2. Na caixa de diálogo Novo nome, digite o seguinte:
      • Nome: Vendas
      • Escopo: Livro de exercícios
      • Refere-se a: = $ A $ 2: ÍNDICE ($ A $ 2: $ A $ 100, CONT.SE ($ A $ 2: $ A $ 100, ”” & ””))
   3. Clique OK.

Feito!

Agora você tem um intervalo de nome dinâmico com o nome ‘Vendas’. Isso seria atualizado automaticamente sempre que você adicionar dados a ele ou remover dados dele.

Como funcionam os intervalos nomeados dinâmicos?

Para explicar como isso funciona, você precisa saber um pouco mais sobre a função INDEX do Excel.

A maioria das pessoas usa INDEX para retornar um valor de uma lista com base no número da linha e da coluna.

Mas a função INDEX também tem outro lado.

Pode ser usado para retornar uma referência de célula quando é usado 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, "" & ""))

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Esta parte da fórmula deve retornar um valor (que seria o décimo valor da lista, considerando que são dez itens).

No entanto, quando usado na frente de uma referência (=$ A $ 2:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) retorna a referência à célula em vez do valor.

Portanto, aqui ele retorna = $ A $ 2: $ A $ 11

Se adicionarmos dois valores adicionais à coluna de vendas, ela retornará = $ A $ 2: $ A $ 13

Quando você adiciona novos dados à lista, a função CONT.SE do Excel retorna o número de células que não estão em branco nos dados. Este número é usado pela função INDEX para buscar a referência de célula do último item da lista.

Observação:

• Isso só funcionaria se não houvesse células em branco nos dados.
• No exemplo acima, atribuí um grande número de células (A2: A100) para a fórmula de Intervalo Nomeado. Você pode ajustar isso com base em seu conjunto de dados.

Você também pode usar a função OFFSET para criar intervalos nomeados dinâmicos no Excel; no entanto, como a função OFFSET é volátil, ela pode gerar lentidão na pasta de trabalho do Excel. INDEX, por outro lado, é semivolátil, o que o torna a melhor escolha para criar intervalos nomeados dinâmicos no Excel.

Você também pode gostar dos seguintes recursos do Excel:

• Modelos grátis do Excel.
• Treinamento gratuito em Excel online (curso de vídeo online em 7 partes).
• Exemplos úteis de código de macro do Excel.
• 10 Exemplos avançados de VLOOKUP do Excel.
• Criando uma lista suspensa no Excel.
• Criação de um intervalo nomeado no Planilhas Google.
• Como fazer referência a outra planilha ou pasta de trabalho no Excel