the tutorial explains what an Excel name is and shows how to defin a name for a cell, range, constant or formula. Você também vai aprender como editar, filtrar e excluir nomes definidos no Excel.
nomes no Excel são uma coisa paradoxal: sendo uma das características mais úteis, eles são muitas vezes considerados inúteis ou Nerd. A razão é que muito poucos Usuários entendem a essência dos nomes do Excel., Este tutorial não só lhe ensinará como criar uma gama com nome no Excel, mas também mostrará como alavancar esta funcionalidade para tornar as suas fórmulas muito mais fáceis de escrever, ler e reutilizar.,opção
- a Edição de um intervalo com nome
- Filtragem do Excel names
- Excluir nomes definidos no Excel
o Que o nome significa no Excel?,
na vida cotidiana os nomes são amplamente utilizados para se referir a pessoas, objetos e localizações geográficas. Por exemplo, em vez de dizer “a cidade está na latitude 40.7128° n e longitude 74.0059° W, você simplesmente diz “cidade de Nova York”.
Similarly, in Microsoft Excel, you can give a human-readable name to a single cell or a range of cells, and refer to those cells by name rather than by reference.,
Por exemplo, para encontrar o total de vendas (B2:B10) para um item específico (E1), você pode usar a fórmula a seguir:
=SUMIF($A$2:$A$10, $E$1, $B$2:$B$10)
Ou, você pode dar nomes significativos para os intervalos e as células individuais e fornecer os nomes para a fórmula:
=SUMIF(items_list, item, sales)
Olhando o screenshot abaixo, qual das duas fórmulas são mais fácil para você entender?,
Excel name types
In Microsoft Excel, you can create and use two types of names:
Defined name – a name that refers to a single cell, range of cells, constant value, or formula. Por exemplo, quando você define um nome para uma gama de células, ele é chamado de um intervalo nomeado, ou intervalo definido. Estes nomes são assunto do tutorial de hoje.
Nome da tabela-um nome de uma tabela do Excel que é criada automaticamente quando você insere uma tabela numa folha de trabalho (Ctrl + T). Para mais informações sobre tabelas Excel, por favor, veja como fazer e usar uma tabela no Excel.,
como criar uma gama de nomes do Excel
No geral, existem 3 maneiras de definir um nome no Excel: caixa de Nome, definir o botão Nome, e Gestor de nomes do Excel.
digite um nome no campo Nome
o campo Nome no Excel é a forma mais rápida de criar um intervalo nomeado:
- seleccione uma célula ou um intervalo de células que deseja nomear.
- Escreva um nome na caixa de nomes.carregue na tecla Enter.
Voila, uma nova gama de nomes do Excel é criada!,
crie um nome usando a opção Definir o nome
outra maneira de fazer um intervalo nomeado no Excel é esta:
- selecione a(s) célula (s).
- na página fórmulas, no grupo Definir nomes, carregue no botão Definir Nome.
- na janela do novo nome, indique três coisas:
- no campo do nome, escreva o nome do intervalo.
- na lista de escopo, defina o scope (Workbook by default).
- Na Casa referente, assinale a referência e corrija-a, se necessário.
- Carregue em OK para gravar as alterações e fechar a janela.,Nota: Por padrão, o Excel cria um nome com referências absolutas. Se preferir ter um intervalo de nomes relativo, remova o sinal $ da referência (antes de fazer isto, certifique-se que compreende completamente como os nomes relativos se comportam nas folhas de cálculo).
em comparação com o método anterior, usando definir o nome no Excel leva alguns cliques extras, mas também fornece mais algumas opções, como definir o escopo do nome e adicionar um comentário que explica algo sobre o nome., Além disso, a funcionalidade de definição de nome do Excel permite criar um nome para uma constante ou fórmula.
Faça uma gama com nome usando o Gerenciador de nomes do Excel
usualmente, o Gerenciador de nomes no Excel é usado para trabalhar com nomes existentes. No entanto, ele pode ajudá-lo a construir um novo nome também. Aqui está como:
- ir para a página fórmulas > grupo de nomes definidos, carregue no Gestor de nomes. Ou, basta carregar em Ctrl + F3 (A minha forma preferida).,
- no canto superior esquerdo da janela do Gestor de nomes, carregue no botão Novo…:
- isto irá abrir a janela do Novo Nome onde você configura um nome como demonstrado na secção anterior.
Tip. Para testar rapidamente o nome recentemente criado, seleccione-o na lista de nomes. Assim que você liberar o mouse, o intervalo na planilha será selecionado.,como criar um nome Excel para uma constante
além de gamas nomeadas, o Microsoft Excel permite definir um nome sem referência de células que funcionará como uma constante nomeada. Para criar tal nome, use o Excel definir a funcionalidade nome ou nome Gerenciador como explicado acima.
E agora, você pode usar este nome em qualquer lugar em suas fórmulas para converter USD EUR:
assim que as alterações na taxa de câmbio, você pode atualizar o valor apenas em um local central, e todas as suas fórmulas terá recalculada em um único passo!,
Como definir um nome para uma fórmula
Em uma maneira semelhante, você pode dar um nome a uma fórmula do Excel, por exemplo, aquele que retorna a contagem de não-vazio de células na coluna A, excluindo-se a linha de cabeçalho (-1):
=COUNTA(Sheet5!$A:$A)-1
Nota. Se sua fórmula se refere a quaisquer células na folha atual, você não precisa incluir o nome da folha nas referências, Excel irá fazê-lo para você automaticamente., Se estiver a referenciar uma célula ou intervalo noutra folha de trabalho, adicione o nome da folha seguido do ponto de exclamação antes da referência célula/intervalo (como no exemplo de fórmula acima).,Agora, sempre que você quiser saber quantos itens existem na coluna A no Sheet5, não incluindo o cabeçalho da coluna, digite o sinal de igualdade, seguido pelo nome da sua fórmula em qualquer célula, como este: =Items_count
Como nome de colunas no Excel (nomes de seleção)
Se os dados são organizados em forma de tabela, você pode rapidamente criar nomes para cada coluna e/ou linha de base em seus rótulos:
- Selecione a tabela inteira, incluindo os cabeçalhos de coluna e linha.,
- vá para a página fórmulas > Defina o grupo de nomes, e carregue no botão Criar a partir da selecção. Ou, carregue no atalho de teclado Ctrl + Shift + F3.
- de qualquer forma, a caixa de diálogo Criar nomes a partir da seleção irá abrir. Você seleciona a coluna ou linha com cabeçalhos, ou ambos, e clique em OK.,
neste exemplo, temos cabeçalhos na linha superior e esquerda da coluna que, por isso selecionamos estas duas opções:
Como resultado, o Excel criará 7 intervalos nomeados, pegando os nomes dos cabeçalhos automaticamente:
- Maçãs, Bananas, Limões e Laranjas para linhas, e
- Jan, Fev e Mar para as colunas.
Nota. Se existirem espaços entre palavras nas legendas do cabeçalho, os espaços serão substituídos por sublinhados (_).,Excel dynamic named range
em todos os exemplos anteriores, temos lidado com gamas com nomes estáticos que sempre se referem às mesmas células, o que significa que você teria que atualizar a referência de range manualmente sempre que você quiser adicionar novos dados ao range nomeado.
Se você está trabalhando com conjuntos de dados expansíveis, é lógico criar um intervalo dinâmico nomeado que acomoda os dados recém-adicionados automaticamente.,
A orientação detalhada sobre como criar uma dinâmica de intervalo no Excel pode ser encontrada aqui:
- Excel fórmula de compensação para criar um intervalo dinâmico
- fórmula de ÍNDICE para criar um intervalo dinâmico
Excel regras de nomenclatura
Quando criar um nome no Excel, existem algumas regras para lembrar:
Excel escopo de nome
Em termos de Excel nomes, o âmbito é a localização, ou nível, dentro do qual o nome é reconhecido., Pode ser:
- folha de trabalho específica-o nível local da folha de trabalho
- Livro de trabalho – o nível global do livro de trabalho
os nomes dos níveis da folha de trabalho
uma folha de trabalho-o nome do nível da folha de trabalho é reconhecido dentro da folha de trabalho onde ela está localizada. Por exemplo, se você criar um intervalo nomeado e definir seu escopo para Sheet1, ele será reconhecido apenas no Sheet1.
para ser capaz de usar um nome de nível de planilha em outra planilha, você deve prefixar o nome da planilha seguido pelo ponto de exclamação (!), assim:
Sheet1!,items_list
para referenciar um nome de nível de folha de trabalho noutro livro de trabalho, deverá também incluir o nome do livro de trabalho entre parêntesis rectos:
Sheet1!items_list
Se o nome da folha ou o nome do livro de trabalho contiverem espaços, devem ser incluídos em aspas simples:
‘Sheet1’!items_list
nomes de nível de livro de trabalho
um nome de nível de livro de trabalho é reconhecido dentro de todo o livro de trabalho, e você pode se referir a ele simplesmente pelo nome de qualquer folha no mesmo livro de trabalho.,
A use to a workbook-level name in another workbook, preceder the name with the workbook name (including the extension) followed by the exclamation point:
Book1.xlsx!items_list
Scope precedency
a defined name must be unique within its scope. Você pode usar o mesmo nome em diferentes âmbitos, mas isso pode criar um conflito de nomes. Para evitar que isso aconteça, por padrão, o nível da planilha tem precedência sobre o nível do livro de trabalho.,
Se existirem alguns intervalos de nomes idênticos com diferentes âmbitos, e você quiser usar o nome do nível do livro de trabalho, prefixe o nome com o nome do livro de trabalho como se você estivesse se referindo a um nome em outro livro de trabalho, por exemplo: Book1.xlsx!dado. Desta forma, o conflito de nomes pode ser substituído para todas as planilhas, exceto para a primeira folha, que sempre usa o nome do nível da planilha local.,
Excel Name Manager-forma rápida de editar, apagar e filtrar nomes
como o seu nome sugere, o Excel Name Manager é especialmente concebido para gerir nomes: mudar, filtrar ou apagar nomes existentes, bem como criar novos nomes.
Existem duas formas de chegar ao Gestor de nomes no Excel:
- na página fórmulas, no grupo Definir nomes, carregue no Gestor de nomes
- carregue no atalho Ctrl + F3.
de qualquer forma, a janela do Gestor de nomes irá abrir, permitindo-lhe ver todos os nomes no livro de trabalho actual de uma só vez., Agora, você pode selecionar o nome com o qual você deseja trabalhar, e clicar em um dos três botões no topo da janela para executar a ação correspondente: editar, excluir ou filtrar.
How to edit named range in Excel
To change an existing Excel name, open the Name Manager, select the name, and click the Edit… button. Isto irá abrir a janela para editar o nome, onde poderá alterar o nome e a referência. O âmbito do nome não pode ser alterado.
Tip. Para editar uma referência de nome, não precisa de abrir a janela para editar o nome., Basta selecionar o nome de interesse no Gestor de nomes do Excel, e digitar uma nova referência diretamente na caixa Refers to, OU clique no botão à direita e selecione o intervalo desejado na folha. Depois de clicar no botão Fechar, o Excel irá perguntar se você deseja salvar as alterações, e você clicar em Sim.
Como filtro de nomes no Excel
Se você tem um monte de nomes em uma determinada pasta de trabalho, clique no botão de Filtro no canto superior direito do Excel Nome do Gerenciador de janela para ver apenas os nomes relevantes em um determinado momento., Os seguintes filtros estão disponíveis:
- Nomes de escopo planilha ou pasta de trabalho
- Nomes com ou sem erros
- nomes Definidos ou nomes de tabela
Como excluir o nome do intervalo no Excel
Para excluir um intervalo com nome, selecione-o no Gerenciador de Nome e clique no botão Excluir na parte superior.
para apagar vários nomes, carregue no primeiro nome, depois carregue na tecla Ctrl e mantenha-a carregada enquanto carrega nos outros nomes que deseja remover. Em seguida, clique no botão Apagar, e todos os nomes selecionados serão apagados de uma vez.,
para apagar todos os nomes definidos num livro de trabalho, seleccione o primeiro nome na lista, carregue e mantenha a tecla Shift carregada e carregue depois no último nome. Libere a tecla Shift e clique em Apagar.
Como excluir nomes definidos com erros
Se você tiver um número de nomes inválidos com erros de referência, clique no botão de Filtro > Nomes com Erros de filtrá-los:
Após isso, selecione todos os filtrados nomes como explicado acima (usando a tecla Shift), e clique no botão Excluir.Nota:, Se algum de seus nomes do Excel são usados em fórmulas, certifique-se de atualizar as fórmulas antes de excluir nomes, caso contrário suas fórmulas irão retornar #nome? erro.
Top 5 benefícios de usar nomes no Excel
até agora neste tutorial, temos focado principalmente em como-para coisas que cobrem diferentes aspectos da criação e uso de gamas nomeadas no Excel. Mas você pode estar curioso para saber o que é tão especial sobre os nomes do Excel que faz com que eles valham o esforço? As cinco vantagens de usar nomes definidos no Excel seguem abaixo.,
os nomes do Excel tornam as fórmulas mais fáceis de fazer e ler
Você não tem que digitar referências complexas ou ir e voltar selecionando intervalos na folha. Basta começar a digitar o nome que você deseja usar na Fórmula, E o Excel irá mostrar uma lista de nomes correspondentes para você escolher., Faça duplo clique no nome desejado, e o Excel irá inserir na fórmula de imediato:
Excel nomes de permitir a criação expansível fórmulas
usando a dinâmica de intervalos nomeados, você pode criar uma “dinâmica” fórmula que inclui automaticamente novos dados em cálculos sem que você tenha que atualizar todas as referências manualmente.
nomes do Excel tornam as fórmulas mais fáceis de reutilizar
nomes do Excel tornam muito mais fácil copiar uma fórmula para outra folha ou portá-la para um livro de trabalho diferente., Tudo o que você tem a fazer é criar os mesmos nomes no livro de destino, copiar/colar a fórmula como está, e você vai fazê-lo funcionar imediatamente.Tip. Para evitar a criação de novos nomes no Excel, copie a fórmula como texto na barra de fórmulas em vez de copiar a célula de Fórmula.
os intervalos nomeados simplificam a navegação
para chegar rapidamente a um determinado intervalo nomeado, basta clicar no seu nome na caixa de nomes. Se um intervalo nomeado reside em outra folha, Excel irá levá-lo para essa folha automaticamente.
Nota., Gamas com nomes dinâmicos não aparecem na caixa de nomes no Excel. Para ver gamas dinâmicas, abra o Gerenciador de nomes do Excel (Ctrl + F3) que mostra detalhes completos sobre todos os nomes no manual, incluindo seu escopo e referências.os intervalos nomeados permitem a criação de listas drop-down dinâmicas
para construir uma lista expansível e atualizável drop-down, fazer um intervalo dinâmico nomeado primeiro, e, em seguida, criar uma lista de validação de dados com base nesse intervalo. As instruções detalhadas passo a passo podem ser encontradas aqui: como criar uma dropdown dinâmica no Excel.,
Excel named range-tips and tricks
Agora que você sabe o básico de criar e usar nomes no Excel, deixe-me compartilhar mais algumas dicas que podem ser úteis no seu trabalho.
Como obter uma lista de todos os nomes na pasta de trabalho
Para obter mais tangível lista de todos os nomes em uma pasta de trabalho atual, faça o seguinte:
- Seleccione a primeira célula do intervalo onde você deseja que os nomes apareçam.
- Go to the Formulas tab > Define o grupo de nomes, carregue em usar em fórmulas e depois carregue em Colar nomes… ou simplesmente carregue na tecla F3.,
- na janela Colar nomes, carregue na lista Colar.
Isto irá inserir todos os nomes do Excel, juntamente com as suas referências na folha de trabalho actual, começando na célula seleccionada.
nomes Excel absolutos vs. nomes Excel relativos
por padrão, os nomes Excel comportam – se como referências absolutas bloqueadas em células específicas. No entanto, é possível fazer um intervalo nomeado em relação à posição da célula ativa no momento em que o nome é definido., Os nomes relativos comportam – se como referências relativas-muda quando a fórmula é movida ou copiada para outra célula.
na verdade, eu não consigo pensar em nenhuma razão pela qual alguém gostaria de fazer um intervalo de nome relativo, exceto talvez quando um intervalo consiste de uma única célula. Como exemplo, vamos criar um nome relativo que se refere a uma célula uma coluna à esquerda da célula actual, na mesma linha:
- seleccione a célula B1.
- Carregue em Ctrl + F3 para abrir o Gestor de nomes do Excel, e carregue no novo…
- no campo de nomes, escreva o nome desejado, por exemplo, item_ Left.,
- na caixa, tipo
=A1.clique em OK.
Agora, vamos ver o que acontece quando usamos o item_left nome em uma fórmula, por exemplo:
=SOMASE(items_list, item_left, vendas)Onde items_list refere-se a $A$2:$A$10 e vendas refere-se a $B$2:$B$10 na tabela abaixo.,
Quando você insere a fórmula na célula E2 e, em seguida, copiá-lo para baixo a coluna, ele irá calcular o total de vendas para cada produto individualmente, porque item_left é um nome relativo e a sua referência ajusta com base na posição relativa da coluna e linha onde a fórmula é copiada:
Como aplicar Excel nomes para as fórmulas existentes
Se você tiver definido as faixas que já são utilizadas em suas fórmulas, o Excel não altera as referências a nomes adequados automaticamente., No entanto, em vez de substituir referências com nomes à mão, você pode fazer Excel fazer o trabalho para você. Aqui está como:
- seleccione uma ou mais células de fórmula que deseja actualizar.
- Vá para o separador Fórmulas > Definir Nomes de grupo e clique em Definir Nome > Aplicar Nomes…
- Na caixa Aplicar Nomes de caixa de diálogo, clique sobre os nomes que você deseja aplicar e, em seguida, clique em OK., Se o Excel é capaz de corresponder a qualquer um dos nomes existentes com as referências utilizadas em suas fórmulas, os nomes serão selecionados automaticamente para você:
Além disso, mais duas opções estão disponíveis (selecionada por padrão):
- Ignorar Relativo/Absoluto – mantenha esta caixa marcada se você deseja que o Excel aplica apenas os nomes com o mesmo tipo de referência: substituir referências relativas com nomes relativos e absolutos referências com absoluta nomes.,
- Use os nomes das linhas e Colunas – se seleccionado, o Excel irá mudar o nome de todas as referências das células que podem ser identificadas como a intersecção de uma linha com nome e coluna com nome. Para mais opções, clique nas opções
Excel Atalhos de nomes
como é frequentemente o caso no Excel, as funcionalidades mais populares podem ser acessadas de várias formas: através da fita, menu do botão direito e atalhos de teclado. As gamas nomeadas do Excel não são exceção. Aqui estão três atalhos úteis para trabalhar com nomes no Excel:
- Ctrl + F3 para abrir o Gestor de nomes do Excel.,
- Ctrl + Shift + F3 para criar intervalos com nomes a partir da selecção.
- F3 para obter uma lista de todos os nomes do Excel em um livro de trabalho.
erros de nome do Excel (#REF e #NAME)
por padrão, o Microsoft Excel faz o seu melhor para manter os seus nomes definidos consistentes e válidos, ajustando as referências do intervalo automaticamente quando você inserir ou apagar células dentro de um intervalo já existente nomeado. Por exemplo, se você criou um intervalo nomeado para as células A1:A10, e então você insere uma nova linha em qualquer lugar entre as linhas 1 e 10, a referência do intervalo irá mudar para A1:A11., Da mesma forma, se você excluir quaisquer células entre A1 e A10, o seu intervalo nomeado irá contrair em conformidade.
no entanto, se você excluir todas as células que compõem um intervalo com nome Excel, o nome torna-se inválido e exibe um #REF! erro no Gestor de nomes. O mesmo erro irá aparecer em uma fórmula referenciando esse nome:
Se uma fórmula se refere a um nome não-existente (mistipado ou excluído), o #NAME? o erro vai aparecer. Em qualquer dos casos, abra o Gerenciador de nomes do Excel e verifique a validade de seus nomes definidos (a maneira mais rápida é filtrar nomes com erros).,
é assim que você cria e usa nomes no Excel. Agradeço-lhe por ler e espero vê-lo em nosso blog na próxima semana!
- Como criar e utilizar o nome do intervalo no Excel
- Basic do Excel fórmulas & funções com exemplos
- Microsoft Excel fórmulas com exemplos
Deixe uma resposta