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.

Excel 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.

Começar agora

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

É 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

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

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

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

É 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

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

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

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