gamas nomeadas são uma dessas antigas características do Excel que poucos Usuários entendem. Novos usuários podem achá-los estranhos e assustadores, e até mesmo as mãos velhas podem evitá-los porque eles parecem inúteis e complexos.
mas os intervalos nomeados são na verdade uma característica muito legal. Eles podem fazer fórmulas * muito * mais fáceis de criar, ler e manter. E como bônus, eles tornam as fórmulas mais fáceis de reutilizar (mais portáteis).
Na verdade, eu uso intervalos nomeados o tempo todo quando testando e prototipando fórmulas. Ajudam-me a acelerar as fórmulas., I also use named ranges because i’m lazy, and don’t like typing in complex references:)
The basics of named ranges in Excel
What is a named range?
uma gama nomeada é apenas um nome legível pelo homem para uma gama de células no Excel. Por exemplo, se o nome de intervalo A1:A100 “dados”, eu posso usar o MAX para obter o máximo de valor com uma fórmula simples:
=MAX(data) // max value
A beleza de intervalos nomeados é que você pode usar nomes significativos em suas fórmulas, sem pensar sobre referências de célula., Uma vez que você tem um intervalo nomeado, basta usá-lo apenas como uma referência de célula. Todas essas fórmulas são válidas para o intervalo com nome “data”:
=MAX(data) // max value=MIN(data) // min value=COUNT(data) // total values=AVERAGE(data) // min valueVídeo: Como criar um nome de intervalo
a Criação de um intervalo nomeado é fácil
a Criação de um intervalo nomeado é rápido e fácil. Basta selecionar uma gama de células, e digite um nome na caixa de nomes. Quando carregar em return, o nome é criado:
para testar rapidamente o novo intervalo, escolha o novo nome na lista ao lado do campo Nome. O Excel selecionará o intervalo na planilha.,
o Excel pode criar nomes automaticamente (ctrl + shift + F3)
Se tiver dados bem estruturados com legendas, poderá fazer com que o Excel crie intervalos com nomes para si. Basta seleccionar os dados, juntamente com as legendas, e usar o comando “Criar a partir da selecção” na página fórmulas da Fita:
Pode também usar o atalho de teclado + shift + F3.
Usando esse recurso, podemos criar intervalos nomeados para a população de 12 estados, em um só passo:
Quando você clicar em OK, os nomes são criados., Você encontrará todos os recém-criados nomes na lista drop-down ao lado da caixa de nome:
Com nomes de criado, você pode usá-los em fórmulas como esta
=SUM(MN,WI,MI)Atualização de intervalos nomeados em Nome do Gestor (Control + F3)
uma Vez que você criar um nome de intervalo, use o Gerenciador de Nome (Control + F3) para atualizar, conforme necessário. Seleccione o nome com que deseja trabalhar, depois altere a referência directamente (ou seja, edite “refere-se a”), ou carregue no botão à direita e seleccione um novo intervalo.,
não Há necessidade de clicar no botão Editar para atualizar uma referência. Quando carregar em Fechar, o nome do intervalo será actualizado.
Nota: Se seleccionar um intervalo inteiro com nome numa folha de cálculo, poderá arrastar para um novo local e a referência será actualizada automaticamente. No entanto, eu não sei uma maneira de ajustar as referências de range clicando e arrastando diretamente na planilha. Se sabes uma maneira de fazer isto, toca lá em baixo!
veja todos os intervalos nomeados (controlo + F3)
para ver rapidamente todos os intervalos nomeados num livro de trabalho, use o menu ao lado da caixa de nomes.,
Se você quiser ver mais detalhes, abra o Gerenciador de Nome (Control + F3), que lista todos os nomes com referências, e fornece um filtro bem:
Nota: em um Mac, não há nenhum Gerenciador de Nome, então você verá o diálogo Definir Nome em vez disso.
copiar e colar todos os intervalos nomeados (F3)
Se quiser um registo mais persistente dos intervalos nomeados num livro de trabalho, poderá colar a lista completa dos nomes onde quiser., Ir para Fórmulas > Usar na Fórmula (ou use o atalho F3) e, em seguida, escolha Colar nomes de > Colar de Lista:
Quando você clique em Colar Lista botão, você vai ver os nomes e referências colado na folha de cálculo:
Veja os nomes directamente na folha de cálculo
Se você definir o nível de zoom para menos de 40%, o Excel irá mostrar nomes de intervalo directamente na folha de cálculo:
Obrigado por esta dica, Felipe!,
os nomes têm regras
ao criar intervalos nomeados, siga estas regras:
os intervalos nomeados nas fórmulas
os intervalos nomeados são fáceis de usar nas fórmulas
por exemplo, vamos dizer que você nomeia uma célula no seu livro de trabalho “actualizado”. A ideia é que você pode colocar a data actual na célula (Ctrl + 😉 e referir-se à data noutro local do livro de trabalho.
A fórmula em B8 parecido com este:
="Updated: "& TEXT(updated, "ddd, mmmm d, yyyy")Você pode colar esta fórmula em qualquer lugar na pasta de trabalho e ele irá exibir corretamente., Sempre que você alterar a data em “updated”, a mensagem irá atualizar onde quer que a fórmula é usada. Veja esta página para mais exemplos.
os intervalos nomeados aparecem ao escrever uma fórmula
Uma vez que você criou um intervalo nomeado, ele irá aparecer automaticamente em fórmulas quando você digitar a primeira letra do nome. Carregue na tecla tab para introduzir o nome quando tiver uma correspondência e quiser que o Excel introduza o nome.,
intervalos Nomeados podem trabalhar como constantes
Devido a intervalos nomeados são criados em uma localização central, você pode usá-los como constantes, sem uma referência de célula. Por exemplo, você pode criar nomes como “MPG” (milhas por galão) e “bens de consumo” (custo por galão) e atribuir valores fixos:
em Seguida, você pode usar esses nomes em qualquer lugar que você gosta em fórmulas, e atualizar o seu valor em uma localização central.,
os intervalos nomeados são absolutos por defeito
por defeito, os intervalos nomeados comportam-se como referências absolutas. Por exemplo, nesta folha de cálculo, a fórmula para calcular o combustível seria:
=C5/$D$2
a referência A D2 é absoluta (bloqueado) de modo que a fórmula pode ser copiado sem D2 mudando.,
Se temos o nome D2 “MPG”, a fórmula passa a ser:
=C5/MPG
Desde MPG é absoluta por padrão, a fórmula pode ser copiada para baixo a coluna D como está.
os intervalos nomeados também podem ser relativos
embora os intervalos nomeados sejam absolutos por padrão, eles também podem ser relativos. Um intervalo relativo chamado refere-se a um intervalo que é relativo à posição da célula ativa no momento em que o intervalo é criado. Como resultado, gamas com nomes relativos são fórmulas genéricas de construção úteis que funcionam onde quer que sejam movidas.,
Por exemplo, você pode criar um genérico “CellAbove” intervalo nomeado como este:
- Selecione a célula A2
- Control + F3 para abrir o Gerenciador de Nome
- a Guia em ‘Refere-se a” seção e, em seguida, digite: =A1
CellAbove agora irá recuperar o valor da célula acima de onde ele é usado.
importante: certifique-se que a célula activa está no local correcto antes de criar o nome.
aplicar gamas nomeadas a fórmulas existentes
Se você tem fórmulas existentes que não usam gamas nomeadas, você pode pedir ao Excel para aplicar as gamas nomeadas nas fórmulas para você., Comece por seleccionar as células que contêm fórmulas que deseja actualizar. Em seguida, executar fórmulas > Definir nomes > aplicar nomes.
Pode também aplicar nomes com find e replace:
importante: salve uma cópia de segurança da sua folha de trabalho e seleccione apenas as células que deseja alterar antes de usar a opção Procurar e substituir nas fórmulas.,os principais benefícios dos intervalos nomeados
os intervalos nomeados tornam as fórmulas mais fáceis de ler
o maior benefício único para os intervalos nomeados é que eles tornam as fórmulas mais fáceis de ler e manter. Isto é porque eles substituem referências crípticas por nomes significativos. Por exemplo, considere esta planilha com dados sobre planetas em nosso sistema solar., Sem intervalos nomeados, uma fórmula PROCV para buscar “Posição” da tabela é bastante enigmático:
=VLOOKUP($H$4,$B$3:$E$11,2,0)
no Entanto, com B3:E11 denominado “dados”, e H4 chamado de “planeta”, podemos escrever fórmulas como esta:
=VLOOKUP(planet,data,2,0) // position=VLOOKUP(planet,data,3,0) // diameter=VLOOKUP(planet,data,4,0) // satellites
Em um piscar de olhos, você pode ver a única diferença em fórmulas na coluna do índice.
os intervalos nomeados tornam as fórmulas Portáteis e reutilizáveis
os intervalos nomeados podem tornar muito mais fácil reutilizar uma fórmula numa folha de trabalho diferente., Se você definir os nomes antes do Tempo em uma planilha, você pode colar em uma fórmula que usa esses nomes e ele vai “apenas funcionar”. Esta é uma ótima maneira de fazer funcionar rapidamente uma fórmula.
Por exemplo, esta fórmula conta única de valores em um intervalo de dados numéricos:
=SUM(--(FREQUENCY(data,data)>0))rapidamente “porta” esta fórmula com a sua própria planilha, um intervalo de nome “dados” e colar a fórmula na folha de cálculo. Enquanto “dados” contiverem valores numéricos, a fórmula funcionará imediatamente.,
dica: eu recomendo que você crie os nomes de gama necessários *primeiro* no livro de destino, em seguida, copiar na Fórmula apenas como texto (ou seja, não copiar a célula que contém a fórmula em outra planilha, basta copiar o texto da fórmula). Isto impede o Excel de criar nomes em tempo real e permite que você controle completamente o processo de criação de nomes. Para copiar apenas texto de Fórmula, Copiar texto da barra de fórmula, ou copiar através de outra aplicação (ou seja, navegador, editor de texto, etc.).
os intervalos nomeados podem ser usados para a navegação
os intervalos nomeados são grandes para a navegação rápida., Basta seleccionar o menu ao lado da caixa de nomes e escolher um nome. Quando você soltar o mouse, o intervalo será selecionado. Quando um intervalo nomeado existir em outra folha, você será levado para essa folha automaticamente.
os intervalos nomeados funcionam bem com hiperligações
os intervalos nomeados tornam as hiperligações fáceis. Por exemplo, se você nomear A1 em Sheet1 “home”, você pode criar um hiperlink em algum outro lugar que o leva de volta lá.,
Para usar um intervalo nomeado dentro da função HIPERLIGAÇÃO, adicionar um símbolo de libra na frente do intervalo chamado:
=HYPERLINK("#home","take me home")Nota: estranhamente, você não pode hiperlink para uma tabela como pode um intervalo normal de nome. No entanto, você pode definir um nome igual a uma tabela (i.e. = Table1) e hiperlink para isso. Se alguém sabe como ligar directamente uma mesa, diga!,
os intervalos nomeados para a validação de dados
os intervalos de nomes funcionam bem para a validação de dados, uma vez que lhe permitem usar uma referência logicamente nomeada para validar a entrada com um menu. Abaixo, a gama G4:G8 é chamado de “statuslist” e, em seguida, aplicar a validação de dados com uma Lista ligada, como este:
O resultado é um menu suspenso na coluna E que só permite valores no intervalo com nome:
Dinâmica de Intervalos Nomeados
Nomes de intervalos são extremamente úteis quando eles se ajustam automaticamente para novos dados em uma planilha., Um range configurado desta forma é referido como um “dynamic named range”. Há duas maneiras de fazer uma gama dinâmica: fórmulas e tabelas.
Dynamic named range with a Table
a Table is the easy way to create a dynamic named range. Selecione qualquer célula nos dados, então use o controle de atalhos + T:
Quando você criar uma tabela Excel, um nome é criado automaticamente (por exemplo Table1), mas você pode mudar o nome da tabela como quiser. Uma vez criada uma tabela, ela se expande automaticamente quando os dados são adicionados.,
Dynamic named range with a formula
Pode também criar um dynamic named range with formulas, usando funções como OFFSET e INDEX. Embora estas fórmulas sejam moderadamente complexas, elas fornecem uma solução leve quando você não quer usar uma tabela., Os links a seguir fornecem exemplos com explicações completas:
- Exemplo de faixa dinâmica fórmula com ÍNDICE
- Exemplo de faixa dinâmica fórmula com DESLOCAMENTO
nomes de Tabela em validação de dados
Desde que o Excel Tabelas fornecem uma automática do intervalo dinâmico, que parece ser um ajuste natural para regras de validação de dados, onde o objetivo é validar uma lista que pode ser sempre mudando. No entanto, um problema com tabelas é que você não pode usar referências estruturadas diretamente para criar validação de dados ou regras de formatação condicional., Em outras palavras, você não pode usar um nome de tabela em áreas de formatação condicional ou de validação de dados.
no entanto, como um workaround, você pode definir um intervalo nomeado que aponta para uma tabela, e então usar o intervalo nomeado para a validação de dados ou formatação condicional. O vídeo abaixo executa esta abordagem em detalhe.
Vídeo: Como usar os intervalos com os nomes das tabelas
a apagar os intervalos com nomes
Nota: Se tiver fórmulas que se referem aos intervalos com nomes, poderá querer actualizar as fórmulas primeiro antes de remover os nomes. Caso contrário, vais ver o nome?, erros em fórmulas que ainda se referem aos nomes apagados. Salve sempre sua planilha antes de remover gamas nomeadas no caso de você ter problemas e precisa reverter para o original.
intervalos Nomeados ajustar ao excluir e inserir células
Quando você excluir *parte* de um intervalo nomeado, ou se inserir células/linhas/colunas dentro de um intervalo nomeado, o intervalo de referência ajusta-se automaticamente e permanecem válidos. No entanto, se você excluir todas as células que envolvem um intervalo nomeado, o intervalo nomeado perderá a referência e exibirá um erro #REF., Por exemplo, se eu nome A1 “teste” e, em seguida, excluir A coluna, o nome do gestor irá mostrar “se refere a” como:
=Sheet1!#REF!Excluir nomes com Gerenciador de Nome
Para remover intervalos nomeados a partir de uma pasta de trabalho manualmente, abra o gerenciador de nome, selecione um intervalo, e clique no botão Excluir. Se quiser remover mais do que um nome ao mesmo tempo, poderá mudar + carregue ou Ctrl + Carregue para seleccionar vários nomes e depois apagar num passo.,
Excluir nomes com erros
Se você tem um monte de nomes com erros de referência, você pode usar o botão de filtro no gerenciador de nome para o filtro nomes com erros:
em Seguida, shift+clique para selecionar todos os nomes e excluir.
gamas nomeadas e escopo
gamas nomeadas no Excel têm algo chamado “escopo”, que determina se um intervalo nomeado é local para uma determinada planilha, ou global em todo o livro de trabalho. Os nomes globais têm um escopo de “workbook”, e os nomes locais têm um escopo igual ao nome da folha em que eles existem., Por exemplo, o escopo para um nome local pode ser “Sheet2”.
a finalidade do escopo
os intervalos nomeados com um escopo global são úteis quando você quer que todas as folhas de um livro de trabalho tenham acesso a certos dados, variáveis ou constantes. Por exemplo, você pode usar uma escala global chamada uma assunção de taxa de imposto usada em várias planilhas.
Local scope
Local scope means a name is works only on the sheet it was created on. Isso significa que você pode ter várias planilhas no mesmo livro de trabalho que todos usam o mesmo nome., Por exemplo, talvez você tenha um livro de trabalho com folhas de rastreamento mensais (um por mês) que usam intervalos com nomes com o mesmo nome, todos escopados localmente. Isso pode permitir que você reutilize as mesmas fórmulas em diferentes folhas. O escopo local permite que os nomes em cada folha funcionem corretamente sem colidir com os nomes nas outras folhas.
Para se referir a um nome de âmbito local, você pode prefixar o nome da folha para o nome do intervalo:
Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenuenomes de Intervalo criado com o nome de caixa automaticamente âmbito global., Para anular este comportamento, adicione o nome da folha ao definir o nome:
Sheet3!my_new_nameglobal scope
Global scope means a name will work anywhere in a workbook. Por exemplo, você pode nomear uma célula “last_ update”, digite uma data na célula. Então você pode usar a fórmula abaixo para exibir a data atualizada pela última vez em qualquer planilha.
=last_updateos nomes globais devem ser únicos dentro de um livro de trabalho.
Local scope
localmente scoped named ranges make sense for worksheets that use named ranges for local assumptions only., Por exemplo, talvez você tenha um livro de trabalho com folhas de rastreamento mensais (um por mês) que usam intervalos com nomes com o mesmo nome, todos escopados localmente. O escopo local permite que os nomes em cada folha funcionem corretamente sem colidir com os nomes nas outras folhas.
Gerenciando o range scope
por padrão, os novos nomes criados com o namebox são globais, e você não pode editar o escopo de um range nomeado após ele ser criado. No entanto, como uma solução, você pode excluir e recriar um nome com o escopo desejado.,
Se você quiser mudar vários nomes ao mesmo tempo do global para local, às vezes faz sentido copiar a folha que contém os nomes. Quando você duplica uma planilha que contém gamas nomeadas, o Excel copia as gamas nomeadas para a segunda folha, mudando o escopo para local ao mesmo tempo. Depois de ter a segunda folha com nomes com escopo local, você pode opcionalmente excluir a primeira folha.Jan Karel Pieterse e Charles Williams desenvolveram um utilitário chamado Nome Manager que fornece muitas operações úteis para gamas nomeadas. Você pode baixar o utilitário nome Manager aqui.,
