Gerenciador de Nomes para Evitar Trancamento no Excel

Nessa publicação vou te mostrar o Gerenciador de Nomes, uma ferramenta muito pouco conhecida que pode ser utilizada para que você nunca mais precise utilizar o Trancamento!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

O que você aprende neste vídeo

Resposta rápida: O vídeo ensina uma alternativa ao trancamento com $ ao arrastar fórmulas no Excel: dar nome a uma célula pela caixa de nome, para que a referência não se mova. No exemplo, uma fórmula de bônus por meta de vendas quebra ao ser arrastada porque referencia células soltas; nomeando-as como bônus1 e bônus2, a fórmula passa a funcionar em qualquer linha.

Neste vídeo (14 min):

  • 1:00 - A planilha tem vendedores com valor de venda e a meta: quem vende R$ 4.000 ou mais ganha 50% de bônus, quem vende menos ganha 10%
  • 2:00 - A fórmula usa SE comparando o valor de venda com a meta: =SE(C3>=4000;C3*50%;C3*10%)
  • 4:00 - Ao arrastar a fórmula para as outras linhas, o resultado vem errado: os valores de bônus calculados não fazem sentido
  • 5:00 - A causa: a fórmula não usava só o percentual direto, e sim as células G3 e G4, que também descem junto quando a fórmula é arrastada
  • 8:00 - Em vez de trancar as células com $, é possível dar um nome a elas pela caixa de nome, à esquerda da barra de fórmulas
  • 9:00 - Renomear G3 para bônus1 e G4 para bônus2 na caixa de nome, apertando Enter para confirmar
  • 10:00 - Refazendo a fórmula com os nomes no lugar das referências de célula: =SE(C3>=4000;C3*bônus1;C3*bônus2)
  • 11:00 - Com os nomes na fórmula, arrastar para baixo não quebra mais nada: bônus1 e bônus2 continuam apontando para as mesmas células fixas
  • 13:00 - Para ver, editar ou excluir os nomes criados: guia Fórmulas, ferramenta Gerenciador de Nomes
  • 13:30 - Renomear um nome já existente pelo Gerenciador de Nomes (de bônus1 para acima, de bônus2 para abaixo) atualiza a fórmula sozinho, sem precisar reescrevê-la

Trechos do vídeo:

  • Em vez de trancar uma célula com $, dar um nome a ela pela caixa de nome faz essa referência ficar fixa em qualquer fórmula, sem se mover quando a fórmula é arrastada
  • Uma fórmula que referencia células soltas, como G3 e G4, quebra ao ser arrastada para baixo, porque essas referências descem junto com a fórmula
  • Depois de nomeada, uma célula pode ser chamada pelo nome dentro de qualquer fórmula, como em =SE(C3>=4000;C3*bônus1;C3*bônus2), no lugar do endereço da célula
  • O Gerenciador de Nomes, na guia Fórmulas, lista todos os nomes criados na planilha e permite editar ou excluir cada um, atualizando automaticamente as fórmulas que os usam

Para baixar a planilha utilizada nessa aula clique aqui!

O que é o Trancamento?

Trancamento é uma forma que temos dentro do Excel para que ao arrastar ou copiar e colar fórmulas o Excel não mude a referência de uma determinada célula ou intervalo. Isso é muito utilizado quando precisamos de um intervalo ou célula fixa dentro de uma fórmula que será aplicada a diversas células.

Para o trancamento utilizamos o símbolo de $ antes da linha e/ou coluna para que seja possível trancar essa referência.

 

Quando utilizar o Gerenciador de Nomes e não o Trancamento?

O trancamento será utilizado sempre que o usuário tiver que replicar uma fórmula e não quer que uma determinada célula ou intervalo sejam modificados ao atribuir a fórmula a diferentes células. Para isso é possível utilizar o trancamento.

 

Ícone ExcelExcel Impressionador

Tudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.

Black Friday · Lote Especial · Lote 1 · Último Lote

De R$ 1.497 56% OFF54% OFF53% OFF

por 12x R$ 6467,5069

Preço do Lote Especial por tempo limitadoPreço do Lote Especial só até amanhã, 13/11, às 23h59Preço do Lote Especial até 13/11, às 23h59Último dia do Lote Especial: o preço vale até hoje, às 23h59Preço do Lote 1 por tempo limitadoPreço do Lote 1 só até amanhã, 20/11, às 23h59Preço do Lote 1 até 20/11, às 23h59Último dia do Lote 1: o preço vale até hoje, às 23h59Preço do Último Lote por tempo limitadoPreço do Último Lote só até amanhã, 27/11, às 23h59Preço do Último Lote até 27/11, às 23h59Último dia do Último Lote: o preço vale até hoje, às 23h59

Começar agoraQuero a oferta de Black FridaySeta para a direita
Fundo ExcelTelas Excel
Luz Excel

Como evitar o uso do Trancamento com o Gerenciador de Nomes?

Por mais que o trancamento seja algo muito importante dentro das fórmulas do Excel, algumas vezes o usuário pode acabar esquecendo de fazer o trancamento e ao replicar a fórmula para as outras células pode ter um erro por conta do não trancamento de uma célula ou intervalo. Por isso temos uma forma de evitar o uso do trancamento.

Antes de entrarmos no assunto propriamente dito é necessário analisar a tabela que temos e criar uma fórmula para que possamos obter os bônus dos vendedores.

 

Tabela inicial para análise

Tabela inicial para análise

 

É possível observar que temos uma tabela de vendas e dependendo do valor de vendas de cada vendedor eles terão um bônus de 10% ou 50%. A fórmula utilizada para o cálculo do bônus está logo abaixo.

 

Fórmula para o cálculo do bônus

Fórmula para o cálculo do bônus

 

Temos que se o vendedor tiver um valor de vendas igual ou superior a R$4.000,00 seu bônus será de 50%, caso contrário terá um bônus de 10%.

As pessoas que conhecem o Excel sabem que se deixarmos a fórmula dessa maneira e replicá-la para as células abaixo teremos o seguinte problema.

 

Atribuindo a fórmula as outras células da tabela

Atribuindo a fórmula as outras células da tabela

 

Isso acontece porque as referências vão descendo juntamente com a fórmula, então se a fórmula foi uma célula para baixo as referências também vão se “mover” uma célula para baixo. Então neste caso teríamos que utilizar o trancamento nas células G3 e G4 para que a referência de bônus permaneça fixa.

No entanto para que não seja necessário colocar os símbolos $ antes da linha e coluna de cada uma dessas células vamos dar um novo nome para cada uma delas para indicar o bônus dentro de cada uma.

Para modificar o nome basta selecionar a célula em questão e mudar o nome dela que fica ao lado esquerdo da barra de fórmulas, vamos mudar o nome para bonus10 e bonus50.

 

Alterando o nome com gerenciador de nomes

Alterando o nome com gerenciador de nomes

 

Feito isso podemos voltar na fórmula e ao invés de selecionar as células vamos escrever o nome delas dentro da fórmula.

 

Reescrevendo a fórmula com os nomes que foram atribuídos as células de bônus

Reescrevendo a fórmula com os nomes que foram atribuídos as células de bônus

 

É possível observar que o próprio Excel já mostra os nomes que foram criados para que o usuário possa selecioná-los. Feito isso podemos arrastar/copiar e colar a fórmula para as outras células.

 

Expandindo a fórmula para as outras células e verificando o resultado correto

Expandindo a fórmula para as outras células e verificando o resultado correto

 

Desta forma não temos mais o erro da mudança de referência e nem foi necessário utilizar o trancamento das células, pois agora que a célula tem um nome o Excel vai utilizar esse nome para fazer referência ao dado que está dentro da célula.

Assim é possível replicar a fórmula sem a necessidade do trancamento. Portanto o usuário não irá precisar se preocupar em relação ao trancamento, pois agora estamos referenciando um nome e esse nome é específico da célula que contém o bônus para cada tipo de venda.

Caso o usuário queira modificar o nome que deu a célula porque mudou de ideia ou algo do gênero terá que ir até a guia Fórmulas e em seguida em Gerenciador de Nomes.

 

Opção de gerenciador de nomes para modificar os nomes já inseridos

Opção de gerenciador de nomes para modificar os nomes já inseridos

 

Feito isso será aberta uma janela para que o usuário possa criar, editar ou excluir um nome.

 

Janela do gerenciador de nomes

Janela do gerenciador de nomes

 

Essa mudança do nome por essa ferramenta é necessária para que ao alterar o nome da célula o Excel irá alterar também esse nome dentro das fórmulas que foram utilizadas, ou seja, não será necessário a alteração manual dentro das fórmulas.

Foi possível aprender uma forma mais fácil de referenciar uma célula sem a necessidade do trancamento e também uma forma fácil de renomear essas células evitando trabalho de ter que substituir dentro da fórmula.

Para acessar outras publicações da Hashtag, clique no link: https://www.hashtagtreinamentos.com/blog

Você conhece as modalidades de curso que a Hashtag Treinamentos oferece? Possuímos uma ampla variedade de cursos, tanto online quanto presenciais! Clique para saber mais!


Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?

O Gerenciador de Nomes é o recurso do Excel (guia Fórmulas) que permite nomear células e intervalos e usar esses nomes nas fórmulas — =SOMA(Vendas) em vez de =SOMA($B$2:$B$100). Ele substitui o trancamento com $ em muitos casos e deixa as fórmulas mais legíveis e fáceis de manter.


Perguntas frequentes

1. Onde fica o Gerenciador de Nomes no Excel?

Na guia Fórmulas, grupo Nomes Definidos. Nele você cria, edita e exclui nomes, vê a referência de cada um e o escopo. Um atalho prático: selecionar o intervalo e digitar o nome direto na Caixa de Nome, à esquerda da barra de fórmulas.

2. Como criar um intervalo nomeado?

Selecione as células, vá em Fórmulas > Definir Nome (ou use a Caixa de Nome), digite um nome sem espaços e confirme. A partir daí, qualquer fórmula pode referenciar o intervalo pelo nome — inclusive em outras abas da mesma pasta de trabalho.

3. Qual a vantagem do nome em relação ao trancamento com $?

O nome já é uma referência absoluta por padrão: dispensa o $ e elimina o erro clássico de esquecer o trancamento ao copiar a fórmula. Além disso, =Preco*Quantidade comunica a intenção do cálculo muito melhor que =$A$2*B2.

4. O nome vale em todas as abas da planilha?

Por padrão, sim: nomes criados com escopo de pasta de trabalho funcionam em qualquer aba. Também dá para restringir o escopo a uma planilha específica ao criar o nome — útil quando abas diferentes têm estruturas repetidas com o mesmo nome.