Dicas e Truques de Nomes Definidos no Excel

Neste artigo você aprenderá 10 dicas e truques de nomes definidos no Excel.

Material gratuito

Planilha de Controle Financeiro no Excel

Tenha um controle bonito e completo do seu financeiro.

Categorias e subcategorias já configuradas
Planilhas separadas por tabela (configuração, lançamentos, resumo)
Personalize com suas próprias categorias e ícones

Definir Nomes Automaticamente

A definição de nomes no Excel permite a organização de intervalos de dados no Excel de uma forma prática e automática.

Para definir nomes selecione um intervalo e clique em Fórmulas->Definir Nome e pode dar um nome que será referenciado nas fórmulas.

Também podem ser definidos automaticamente nomes no Excel.

Para isso é necessário ter um cabeçalho ou um cabeçalho de linha.

Depois clique em Fórmulas->Criar a partir da Seleção e marque as opções Linha superior e Coluna esquerda e clique em OK.

Com isso nós temos os intervalos criados automaticamente com os nomes no topo e nas linhas à esquerda dos intervalos.

Abaixo a lista dos itens gerados automaticamente, como o da linha Setembro.

Abaixo temos também a criação automática da coluna Sul por exemplo.

Nome Definido Indireto

Este é um exemplo de aplicação do Nome Definido Indireto que é alterado conforme o cabeçalho definido das regiões

Para isso usamos a seguinte função =INDIRETO(C7).

Como resultado temos uma lista retornada com os dados da região de nome definido.

Próxima Linha no Excel

Um nome definido pode ser criado para que ao clicar vá para a próxima linha.

Para isso clique em Fórmulas->Definir Nome e usamos a seguinte fórmula: ="'Última linha'!C"&CONT.VALORES(Tabela1[[#Tudo];[Seq.]])+LIN(Tabela1[[#Cabeçalhos];[Seq.]])

Esta fórmula cria um endereço dinâmico conforme a quantidade de linhas que temos na nossa tabela.

Após isso na célula colocamos esta fórmula: =HIPERLINK("#"&_ProximaLinha;"Próxima linha")

Esta fórmula faz com que seja criado um link que ao clicar ele vá para a próxima linha da tabela.

Função Própria com Nomes Definidos no Excel

Uma outra possibilidade de nomes definidos no Excel é a criação de fórmulas utilizando a função LAMBDA do Excel.

No exemplo abaixo usamos a seguinte função de exemplo: =LAMBDA(b;h;(b*h)/2)(C8;C9)

Nela realizamos o cálculo de área de um triângulo.

Para criarmos uma função, nós clicamos em Fórmulas->Definir Nome e colocamos a seguinte função: =LAMBDA(b;h;(b*h)/2) e colocamos um nome definido, como fnVolumeTriangulo.

Após isso podemos usar a função fnVolumeTriangulo e passar os parâmetros nas células, por exemplo: =fnVolumeTriangulo(C8;C9)

E com isso temos o cálculo de área que temos no exemplo.

Validação em Cascata no Excel com Nomes Definidos

Também podem ser realizadas validações em cascata no Excel de forma muito prática.

No exemplo abaixo, criamos uma validação dependente no Excel, aonde temos no topo o nome do grupo e abaixo os itens que chamamos de subgrupo.

Então Receita de Vendas tem: Vendas em dinheiro, vendas em cartão, vendas consignadas e tudo o mais que digitar abaixo.

Para isso, em cada lista ou também usando a técnica de criar intervalos de nomes automaticamente, criamos um intervalo nomeado para cada grupo colocando o nome do grupo no topo e selecionando apenas os itens do subgrupo.

Após isso, em uma tabela que queira adicionar a validação em cascata clique em Dados->Validação de dados e selecione o cabeçalho, contendo os grupos.

E na coluna aonde desejamos o subgrupo, no nosso exemplo que chamamos de Conta e que está ligado ao plano de conta, usamos a função indireto na opção de lista de validação.

Fazendo com que a validação mude conforme o item selecionado na célula que estiver na coluna H, ou Plano de conta.

Para isso usamos a seguinte fórmula: =INDIRETO(SUBSTITUIR(H9;" ";"_"))

Sendo que o substituir tira os espaços, que não são permitidos no gerenciador de nomes, por _.