Como criar uma tabela e nomear a tabela no Excel

Neste artigo, aprenderemos como criar uma tabela e nomear a tabela no Excel.

Cenário:

Enquanto trabalha no Excel, você deve ter ouvido falar sobre intervalos nomeados no Excel. Talvez de um amigo, colega ou algum tutorial online. Já mencionei isso muitas vezes em meus artigos. Neste artigo, aprenderemos sobre Intervalo Nomeado no Excel e exploraremos cada um de seus aspectos.

O que são tabelas nomeadas no Excel?

Bem, os intervalos nomeados nada mais são do que alguns intervalos do Excel que recebem algum nome significativo. Por exemplo, se você tem uma célula B1, contendo alvos diários, você pode nomear essa célula especificamente como “Alvo”. Agora você pode usar “Alvo” para se referir a A1 em vez de escrever B1.

Em suma, intervalo nomeado é apenas nomear intervalos.

Como nomear um intervalo no Excel?

Defina o nome manualmente:

Para definir um nome para um intervalo, você pode usar um atalho CTRL + F3. Ou você pode seguir estas etapas.

Vamos para Fórmula Aba
Localize a seção Nomes definidos e clique em Definir nomes. Isso irá abrir o Name Manger.
Clique em Novo.
Digite o nome.
Selecione o escopo (pasta de trabalho ou planilha)
Escreva um comentário se quiser.
Na caixa Refere-se a, escreva a referência ou selecione um intervalo usando o mouse.
Clique em OK. Está feito.

Agora você pode se referir a ele apenas digitando seu nome.

Existem algumas regras a seguir ao criar nomes. Aqui estão alguns.

Os nomes não devem começar com dígitos ou caracteres especiais diferentes de sublinhado (_) e barra invertida (\).
Os nomes não podem ter espaços e quaisquer caracteres especiais, exceto _ e \.
O intervalo não deve ser nomeado como referência de célula. Por exemplo A1, B1 ou AZ100 etc.
Você não pode nomear um intervalo como “r” e “c” porque eles são reservados para referências de linhas e colunas.
Dois intervalos nomeados não podem ter o mesmo nome em uma pasta de trabalho.
O mesmo intervalo pode ter vários nomes.

Definir o nome automaticamente

Bem, na maioria das vezes você trabalhará com tabelas de dados estruturados. Eles terão colunas e linhas com títulos de coluna e títulos de linha. E na maioria das vezes esses nomes são significativos para os dados e você gostaria de nomear seu intervalo como esses títulos de coluna. O Excel fornece uma ferramenta para nomear automaticamente os intervalos usando cabeçalhos. Siga esses passos.

Os intervalos que você deseja nomear como títulos
pressione CTRL + SHIFT + F3, ou Localize a seção Nomes definidos na guia Fórmula e clique em Criar a partir da seleção.
A caixa de opção abaixo aparecerá. Selecionei apenas a linha superior, pois quero nomear esses intervalos como o título e não quero nomear as linhas.
Clique OK.

Agora, cada coluna é nomeada como seu cabeçalho. Sempre que você digitar uma fórmula, esses nomes serão listados como opções que podem ser acessadas.

Intervalo de nomenclatura usando tabelas do Excel

Quando tabulamos dados no Excel usando CTRL + T, o título da coluna é automaticamente atribuído como o nome da respectiva coluna. Você deve explorar as tabelas do Excel e seus benefícios.

Como ver todos os intervalos nomeados?

Bem, haverá momentos em que você gostaria de ver todos os intervalos nomeados disponíveis na pasta de trabalho. Para ver todos os intervalos de nomes, pressione CTRL + F3. Ou você pode ir para Guia de Fórmula > Gerente de Nomes. Isso listará todos os intervalos nomeados disponíveis na pasta de trabalho. Você pode editar os intervalos nomeados disponíveis, excluí-los e adicionar novos nomes.

Um intervalo de vários nomes

O Excel permite que os usuários nomeiem o mesmo intervalo com nomes diferentes. Por exemplo, o intervalo A2: A10 pode ser denominado 'Clientes' e 'Clientes' ao mesmo tempo. Ambos os nomes se referem ao mesmo intervalo A2: A10.

Mas você não pode ter os mesmos nomes para dois intervalos diferentes. Isso é bom. Isso elimina a chance de ambigüidade.

Obter lista de intervalos nomeados na planilha

Portanto, se você deseja ter uma lista de intervalos nomeados e os intervalos que eles estão cobrindo, você pode usar este atalho para colá-los no lugar em uma planilha.

Selecione uma célula onde deseja obter uma lista de intervalos nomeados.
Pressione F3. Isso abrirá um colar lista caixa de diálogo.
Clique na pasta Lista botão.
A lista será colada nas células selecionadas e assim por diante.

Se você clicar duas vezes no nome dos intervalos nomeados na caixa de colar nome, eles serão escritos como fórmulas na célula. Tente.

Atualizar intervalos nomeados manualmente

Bem, quando você insere uma célula dentro de um intervalo nomeado, ele se atualiza automaticamente e o expande. Mas se você adicionar dados no final da tabela, precisará atualizar o intervalo nomeado. Para atualizar intervalos nomeados, siga estas etapas.

pressione CTRL + F3 para abrir o gerente de nome.
Clique no intervalo nomeado que você deseja editar. Clique em Editar.
Na coluna Refere-se à, digite o intervalo para o qual deseja expandir e clique em OK.

E está feito. Esta é a atualização manual de intervalos nomeados. No entanto, podemos torná-lo dinâmico usando algumas fórmulas.

Atualizar intervalos nomeados dinamicamente

É aconselhável tornar seus intervalos nomeados dinâmicos para que você não precise editá-los sempre que seus dados ultrapassarem o intervalo predefinido.

Eu cobri isso em um artigo separado chamado Intervalos nomeados dinâmicos. Você pode aprender e compreender os benefícios em detalhes aqui.

Excluindo intervalos nomeados

Quando você exclui a parte da soma do intervalo nomeado, ele ajusta automaticamente seu intervalo. Mas, quando você exclui, todo o intervalo de nomes desaparece da lista de nomes. Qualquer fórmula, dependendo desses intervalos, exibirá o erro #REF ou dará saída incorreta (funções de contagem).

Por qualquer motivo, se você deseja excluir os intervalos nomeados, basta seguir estas etapas.

Pressione CTRL + F3. O gerenciador de nomes será aberto.
Selecione Intervalos nomeados que você deseja excluir.
Clique no botão Excluir ou pressione o botão Excluir no teclado.

Cuidado: Antes de excluir os intervalos nomeados, certifique-se de que nenhuma fórmula dependa desses nomes. Se houver, converta-os primeiro em intervalos. Caso contrário, você verá o erro #REF.

Exclusão de nomes com erros

O Excel fornece ferramentas para excluir apenas nomes que contêm erros. Você não precisa identificar cada um deles sozinho. Para excluir nomes com erros, siga estas etapas:

Abra o Gerenciador de nomes (CTRL + F3).
Clique na lista suspensa Filtro no canto superior direito.
Selecione “Nome com erros”
Selecione Tudo e aperte o botão Excluir.

E eles se foram. Todos os nomes com erros serão excluídos do registro imediatamente. Intervalos nomeados com fórmulas

O melhor uso de intervalos nomeados é explorado com fórmulas. As fórmulas ficam realmente flexíveis e legíveis com intervalos nomeados. Vamos ver como.

Fórmulas fáceis de escrever Agora, digamos que você nomeou um intervalo como “Itens”. Agora a lista de itens que você deseja contar “Lápis”. Com nomes, é fácil escrever esta fórmula CONT.SE. Apenas escreva

= CONT.SE (Item,"Lápis")
Assim que você escrever o parêntese de abertura da fórmula a lista de intervalos nomeados disponíveis iráaparecer

Sem o nome, você escreveria um intervalo para a função CONT.SE do Excel, para a qual você pode ter que olhar para o intervalo primeiro e, em seguida, selecionar o intervalo ou digitá-lo na fórmula. O Excel atende aos intervalos de nomes disponíveis.

Os intervalos nomeados são mostrados como sugestões quando você digita qualquer letra. Como o Excel mostra a lista de fórmulas. Por exemplo, se você digitar = u, cada fórmula e intervalo nomeado serão exibidos começando com u, para que você possa usá-los facilmente.

Faça constantes usando intervalos nomeados

Até agora, aprendemos sobre intervalos de nomenclatura, mas você também pode nomear valores. Por exemplo, se o nome do seu cliente for Sunder Pichai, então você pode criar um nome “Cliente” e ele se refere a escrever “Sundar Pichai”. Agora, sempre que você escrever = Cliente em qualquer célula, aparecerá Sundar Pichai.

Não apenas texto, mas você também pode atribuir números como constantes para trabalhar. Por exemplo, você define um alvo. Ou o valor de algo que não mudará.

Referência absoluta e relativa com intervalos nomeados

A referência com intervalos nomeados em é muito flexível. Por exemplo, se você escrever o nome de um intervalo nomeado em uma célula relativa ao intervalo nomeado, ele se comportará como uma referência relativa. Veja a imagem abaixo.

Mas quando você o usa com fórmulas, ele se comportará como absoluto. Bem, na maioria das vezes você os usará com fórmulas, então você pode dizer que eles são, por padrão, absolutos mas na verdade eles são flexíveis.

Mas podemos torná-los relativos também.

Como fazer intervalos nomeados relativos no Excel?

Digamos que se eu queira nomear um intervalo como "Befor", que se refere à célula à esquerda de onde está escrito. Como faço isso? Siga esses passos:

Pressione CTRL + F3
Clique em Novo
Digite “Befor” na seção ‘Nome’.
Na seção ‘Refere-se a:’ escreva o endereço da célula à esquerda. Por exemplo, se você estiver na célula B1, escreva “= A2” na seção ‘Refere-se a:’. Certifique-se de que não tenha o sinal $.

Agora, onde quer que você escreva o “Befor” na fórmula, ele se referirá à célula à esquerda dela.

Aqui, eu usei isso antes na função COLUMN. A fórmula retorna o número da coluna da célula esquerda onde está escrita. Para minha surpresa, A1 mostra o número da última coluna. O que significa que a folha está circulando. Achei que iria mostrar um erro #REF.

Dar nome a fórmulas usadas com frequência?

Agora, este é incrível. Muitas vezes você usa a mesma fórmula repetidamente em uma planilha. Por exemplo, você pode querer verificar se um nome está em sua lista de clientes ou não. E essa necessidade pode ocorrer muitas vezes. Para isso, você escreverá sempre a mesma fórmula complexa.

= SE (CONT.SE (Cliente, I3), "Na lista", "Fora da lista")

Que tal, se você apenas digitar ‘= IsInCustomer’ em uma célula e será mostrado se o valor na célula esquerda está na lista de clientes ou não?

Por exemplo, preparei uma mesa aqui. Agora eu só quero digitar “= IsInCustomer” no J5 e gostaria de ver se o valor em I5 está na lista de clientes ou não. Para fazer isso, siga estas etapas.

Pressione CTRL + F3
Clique em Novo
Em Nome, escreva ‘IsInCustomer”

Em ‘Refere-se a’ escreva sua fórmula. = SE (CONT.SE (Cliente, I5), "Na lista", "Fora da lista")

Clique no botão OK.

Agora, onde quer que você digite ‘IsInCustomer’, o valor será verificado na célula esquerda da lista de clientes.

Isso impede que você se repita continuamente.

Aplicar intervalos nomeados às fórmulas

Muitas vezes, definimos nomes para nossos intervalos depois de já termos escrito fórmulas com base em intervalos. Por exemplo, tenho Preço Total como Células = E2 * F2. Como podemos mudar para Unidades * Unit_Cost.

Selecione as fórmulas.
Vá para a guia de fórmula. Clique na lista suspensa Definir nome.
Clique em Aplicar nomes.
Uma lista de todos os intervalos nomeados aparecerá. Escolha os nomes certos e clique em ok.

E os nomes agora são aplicados. Você pode ver isso na barra de fórmulas.

Fórmulas fáceis de ler com intervalos nomeados

Como você viu, os intervalos nomeados facilitam a leitura das fórmulas. Se eu escrever = CONTAR.SE (“A2: A100”, B2), ninguém vai entender o que estou tentando contar, até que veja os dados ou alguém explique para eles.

Mas se eu escrever = COUNTIF (região, 'leste'), a maioria dos usuários entenderá imediatamente que estamos contando ocorrências de 'leste' na região chamada intervalo.

Fórmulas Portáteis

Os intervalos nomeados tornam muito fácil copiar e colar fórmulas sem se preocupar em alterar as referências. E você pode levar uma fórmula de uma pasta de trabalho para outra e funcionará bem até e a menos que a pasta de trabalho de destino tenha o mesmo nome.

Por exemplo, se você tiver uma Fórmula = CONT.SE (região, leste) na Tabela de Distribuição e você tem outra pasta de trabalho dizendo clientes que também tem um intervalo denominado “Região”. Agora, se você copiar esta fórmula diretamente para qualquer lugar da pasta de trabalho, ela mostrará as informações corretas. A estrutura dos dados não importa. Não importa onde diabos está essa coluna em sua pasta de trabalho. Isso funcionará corretamente.

Na imagem acima, usei exatamente a mesma fórmula em dois arquivos diferentes para contar o número ou leste ocorrendo na lista de regiões. Agora eles estão em colunas diferentes, mas como ambos são nomeados como regiões, isso funcionará perfeitamente.

Navegue facilmente na pasta de trabalho

Fica mais fácil navegar em uma pasta de trabalho com intervalos nomeados. Você só precisa digitar o nome do nome na caixa de nome. O Excel o levará ao intervalo, não importa onde você esteja na pasta de trabalho. Dado que o intervalo nomeado é do escopo da pasta de trabalho.

Por exemplo, se você está na planilha 10 e deseja obter uma lista de clientes, mas não sabe em qual planilha ela está. Basta ir para a caixa de nome e digitar ‘cliente’. Você será direcionado para o intervalo nomeado em uma fração de segundo.

Isso reduzirá o esforço de lembrar os intervalos.

Navegar usando hiperlinks com intervalo nomeado

Quando sua planilha é grande e você costuma ir de um ponto a outro, você gosta de usar hiperlinks para navegar facilmente. Intervalos bem nomeados podem funcionar perfeitamente com hiperlinks. Para adicionar hiperlinks usando intervalos nomeados, siga estas etapas.

Escolha uma célula onde deseja hiperlink
Pressione CTRL + K ou vá para a guia Inserir> HyperLink para abrir a caixa de diálogo Inserir hiperlink.
Clique em Colocar neste Documento.
Role para baixo para ver os intervalos nomeados disponíveis em nomes definidos
Selecione o intervalo nomeado para inserir um hiperlink para esse intervalo.

E está feito. Você tem o seu hiperlink para o intervalo nomeado escolhido. Usando isso, você pode criar um índice de intervalos nomeados que você pode ver e clicar para navegar diretamente para eles. Isso tornará sua pasta de trabalho realmente amigável.

Intervalo Nomeado e Validação de Dados

Intervalos nomeados e validação de dados são feitos um para o outro. Intervalos nomeados tornam a validação de dados altamente personalizável. É muito mais fácil adicionar uma validação de uma lista usando um intervalo nomeado. Vamos ver como …

Vá para a guia Dados
Clique em Validação de Dados
Selecione a lista na seção ‘Permitir:’
Na seção ‘Fonte:’, digite “= Cliente” (escreva qualquer intervalo nomeado que você tenha)
Clique em OK

Agora, essa célula terá nomes de clientes que fazem parte do intervalo denominado Cliente. Fácil, não é?

Validação de dados dependentes ou em cascata com intervalos nomeados

E se você quiser uma validação de dados em cascata ou dependente. Por exemplo, se você deseja uma lista suspensa que contenha as categorias Frutas e Legumes. Agora, se você escolher frutas, outra lista suspensa deverá mostrar apenas a opção de frutas e se você escolher Vegetais, apenas vegetais.

Isso pode ser facilmente alcançado usando intervalos nomeados. Aprenda como.

Dependente suspenso usando intervalo nomeado
Outras maneiras de validação de dados em cascata

Sem Validação de Dados com Nomes de Dados Tabelados

Embora as tabelas do Excel forneçam nomes estruturados, elas não podem ser usadas com validação de dados e formatação condicional. Eu não sei por que o Excel não permite isso.

Mas isso não significa que não possa ser feito. Você pode nomear intervalos em uma tabela e usá-los para validação. O Excel não tem nenhum problema com isso.

Escopo de intervalos nomeados

Até agora, falamos sobre intervalos nomeados que tinham o escopo da pasta de trabalho. O que? Nós não discutimos isso? Ok, então vamos entender rapidamente o que é o escopo dos intervalos nomeados.

Qual é o escopo de um intervalo de nomes?

O escopo do poço define onde um intervalo de nomes pode ser reconhecido. Qualquer nome não pode ser reconhecido fora de seu escopo. Por exemplo, um nome na pasta de trabalho1 não pode ser reconhecido em pastas de trabalho diferentes. O Excel fornece duas opções para o escopo de intervalos nomeados, planilha e pasta de trabalho.

Como definir um escopo de intervalo nomeado?

Ao criar um novo intervalo de nomes, você pode ver uma seção ‘Escopo:’. Clique na lista suspensa e escolha o escopo do seu intervalo de nomes. Você não pode alterar o escopo depois de criar um intervalo nomeado. Então é melhor fazer isso antes. Por padrão, é uma pasta de trabalho.

Espero que este artigo sobre como criar uma tabela e nomear a tabela no Excel seja explicativo. Encontre mais artigos sobre como calcular valores e fórmulas Excel relacionadas aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail.

Como usar o atalho para alternar entre referências absolutas e relativas no Excel : F4 atalho para converter referência absoluta em relativa e mesmo uso de atalho para vice-versa no Excel.

Como usar as teclas de atalho para mesclar e centralizar no Excel : Usar Alt e então siga h, m e c para mesclar e centralizar células no Excel.

Como selecionar colunas e linhas inteiras usando atalhos de teclado no Excel : Usar Ctrl + Espaço para selecionar a coluna inteira e Mudança + Espaço para selecionar a linha inteira usando o atalho do teclado no Excel

Colar atalho especial no Mac e Windows : No Windows, o atalho de teclado para colar especial é Ctrl + Alt + V. Enquanto no Mac, use Ctrl + COMANDO + V combinação de teclas para abrir o colar especial diálogo no Excel.

Como inserir um atalho de linha no Excel : Usar Ctrl + Mudança + = para abrir o Inserir caixa de diálogo onde você pode inserir linha, coluna ou células no Excel.

Artigos populares :

Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.

Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.

Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.

Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.