Ao criar uma tabela do Excel, o Excel atribui um nome à tabela e a cada cabeçalho de coluna na tabela. Quando você adiciona fórmulas a uma tabela do Excel, esses nomes podem aparecer automaticamente à medida que você digita a fórmula e seleciona as referências de célula na tabela em vez de inseri-las manualmente. Veja um exemplo do que o Excel pode fazer:
Em vez de usar referências explícitas a células |
O Excel usa nomes de tabelas e colunas |
---|---|
=Soma(C2:C7) |
=SOMA(DeptoVendas[Valor das Vendas]) |
Essa combinação de nomes de tabelas e colunas é chamada de referência estruturada. Os nomes nessa referência se ajustam sempre que houver adição ou remoção de dados na tabela.
As referências estruturadas também aparecem quando você cria uma fórmula fora de uma tabela do Excel que faz referência a dados da tabela. As referências podem facilitar a localização de tabelas em uma pasta de trabalho grande.
Para incluir referências estruturadas em sua fórmula, selecione as células de tabela que você deseja referenciar em vez de digitar sua referência celular na fórmula. Vamos usar o seguinte exemplo de dados para inserir uma fórmula que usa referências estruturadas automaticamente para calcular o valor de uma comissão por vendas.
Vendedor |
Região |
Valor das vendas |
% da comissão |
Valor da comissão |
---|---|---|---|---|
José |
Norte |
260 |
10% |
|
Pedro |
Sul |
660 |
15% |
|
Michele |
Leste |
940 |
15% |
|
Eric |
Oeste |
410 |
12% |
|
Dafna |
Norte |
800 |
15% |
|
Rob |
Sul |
900 |
15% |
-
Copie os dados de exemplo na tabela acima, incluindo os títulos da coluna e cole-os na célula A1 de uma nova planilha do Excel.
-
Para criar a tabela, selecione qualquer célula dentro do intervalo de dados e pressione Ctrl+T.
-
Verifique se a caixa Minha tabela tem cabeçalhos e selecione OK.
-
Na célula E2, digite um sinal igual (=) e selecione a célula C2.
Na barra de fórmulas, a referência estruturada [@[Valor de Vendas]] é exibida após o sinal de igualdade.
-
Digite um asterisco (*) diretamente após o colchete de fechamento e selecione a célula D2.
Na barra de fórmulas, a referência estruturada [@[% de comissão]] é exibida após o asterisco.
-
Pressione Enter.
O Excel cria automaticamente uma coluna calculada e copia a fórmula abaixo de toda a coluna para você, ajustando para cada linha.
O que acontece quando eu uso referências explícitas a células?
Se você inserir referências explícitas a células em uma coluna calculada, poderá ficar mais difícil visualizar o que a fórmula está calculando.
-
Na planilha de exemplo, selecione célula E2
-
Na barra de fórmulas, insira =C2*D2 e pressione Enter.
Observe que, enquanto o Excel copia a fórmula na coluna, ele não usa referências estruturadas. Por exemplo, se você adicionar uma coluna entre as colunas C e D existentes, será necessário revisar a fórmula.
Como eu altero o nome de uma tabela?
Sempre que você cria uma tabela do Excel, ele cria um nome de tabela padrão (Tabela1, Tabela2 etc.). Porém, é possível alterar o nome da tabela para torná-lo mais significativo.
-
Selecione qualquer célula na tabela para mostrar a guia Design da Tabela na faixa de opções.
-
Digite o nome desejado na caixa Nome da Tabela e pressione Enter.
Em nossa planilha de exemplo, usamos o nome DeptoVendas.
Use as seguintes regras para nomes de tabelas:
-
Use caracteres válidos Sempre inicie um nome com uma letra, um caractere de sublinhado (_) ou uma barra invertida (\). Use letras, números, pontos e caracteres de sublinhado para o restante do nome. Não é possível usar "C", "c", "R" ou "r" para o nome, pois eles já são designados como um atalho para seleção da coluna ou linha da célula ativa quando você os insere na caixa Nome ou Ir para.
-
Não use referências de célula Os nomes não podem ser iguais a uma referência de célula, como Z$100 ou R1C1.
-
Não use um espaço para separar palavras Os espaços não podem ser usados no nome. Você pode usar o caractere sublinhado (_) e o período (.) como separadores de palavras. Por exemplo, DeptoVendas, Imposto_Vendas ou Primeiro.Trimestre.
-
Não use mais de 255 caracteres Um nome de tabela pode ter até 255 caracteres.
-
Usar nomes de tabela exclusivos Nomes duplicados não são permitidos. O Excel não distingue entre caracteres maiúsculas e minúsculas em nomes, portanto, se você inserir "Vendas", mas já tiver outro nome chamado "SALES" na mesma pasta de trabalho, você será solicitado a escolher um nome exclusivo.
-
Usar um identificador de objeto Se você planeja ter uma combinação de tabelas, tabelas dinâmicas e gráficos, é uma boa ideia prefixar seus nomes com o tipo de objeto. Por exemplo: tbl_Sales para uma tabela de vendas, pt_Sales para uma Tabela Dinâmica de vendas e chrt_Sales para um gráfico de vendas ou ptchrt_Sales para um Gráfico Dinâmico de vendas. Isso mantém todos os nomes em uma lista ordenada no Gerenciador de Nomes.
Regras da sintaxe das referências estruturadas
Você também pode inserir ou alterar referências estruturadas manualmente na fórmula, mas para fazer isso, isso ajudará a entender a sintaxe de referência estruturada. Vamos examinar o seguinte exemplo de fórmula:
=SOMA(DeptoVendas[[#Totais],[Valor das Vendas]],DeptoVendas[[#Dados],[Valor da Comissão]])
Essa fórmula tem os seguintes componentes de referência estruturada:
-
Nome da tabela: DeptSales é um nome de tabela personalizado. Ele faz referência aos dados da tabela, sem qualquer linha de cabeçalho ou total. Você pode usar um nome de tabela padrão, como Tabela1, ou alterá-lo para usar um nome personalizado.
-
Especificador de coluna: [Valor de Vendas] e [Valor da Comissão] são especificadores de coluna que usam os nomes das colunas que representam. Eles fazem referência aos dados da coluna, sem qualquer linha de cabeçalho ou total. Coloque sempre os especificadores entre colchetes, conforme mostrado.
-
Especificador de item: [#Totals] e [#Data] são especificadores de item especiais que se referem a partes específicas da tabela, como a linha total.
-
Especificador de tabela: [[#Totais], [Valor das vendas]] e [[#Dados], [Valor da comissão]] são especificadores de tabela que representam as partes externas da referência estruturada. As referências externas acompanham o nome da tabela e são colocadas entre colchetes.
-
Referência estruturada: (DeptSales[[#Totals],[Valor de Vendas]] e DeptSales[[#Data],[Valor da Comissão]] são referências estruturadas, representadas por uma cadeia de caracteres que começa com o nome da tabela e termina com o especificador de coluna.
Para criar ou editar referências estruturadas manualmente, use estas regras de sintaxe:
-
Use especificadores entre colchetes Todos os especificadores de tabelas, colunas e itens especiais devem estar entre colchetes ([ ]). Um especificador contendo outros especificadores requer um par de colchetes externos envolvendo os pares de colchetes internos dos outros especificadores. Por exemplo: =DeptVendas[[Vendedor]:[Região]]
-
Todos os cabeçalhos de colunas são cadeias de caractere de texto Mas eles não exigem aspas quando são usados em uma referência estruturada. Números ou datas, como 2014 ou 1/1/2014, também são consideradas cadeias de caracteres de texto. Não é possível usar expressões com cabeçalhos de coluna. Por exemplo, a expressão DeptoDeVendasAFResumo[[2014]:[2012]] não funcionará.
Usar cabeçalhos de coluna entre colchetes com caracteres especiais Quando há caracteres especiais, todo o cabeçalho da coluna deve ser colocado entre colchetes; isso significa que os colchetes duplos são obrigatórios em um especificador de coluna. Por exemplo: =DeptoVendasAFResumo [[Valor Total em R$]]
Aqui está a lista de caracteres especiais que precisam de colchetes extras na fórmula:
-
Tab
-
Alimentação de linha
-
Retornos de carro
-
Vírgula (,)
-
Dois pontos (:)
-
Ponto (.)
-
Colchete esquerdo ([)
-
Colchete direito (])
-
Sinal de cerquilha (#)
-
Aspas simples (')
-
Aspas duplas (")
-
Chave esquerda ({)
-
Chave direita (})
-
Cifrão ($)
-
Acento circunflexo (^)
-
E comercial (&)
-
Asterisco (*)
-
Sinal de adição (+)
-
Sinal de igual (=)
-
Sinal de menos (-)
-
Sinal de maior que (>)
-
Sinal de menor que (<)
-
Sinal de divisão (/)
-
A assinar (@)
-
Barra invertida (\)
-
Ponto de exclamação (!)
-
Parêntese esquerdo (()
-
Parêntese direito ())
-
Sinal de percentagem (%)
-
Ponto de interrogação (?)
-
Acento anterior (')
-
Ponto-e-vírgula (;)
-
Til (~)
-
Caráter de sublinhado (_)
-
Usar um caractere de escape para alguns caracteres especiais nos cabeçalhos de coluna Alguns caracteres têm um significado especial e exigem o uso de aspas simples (') como um caractere de escape. Por exemplo: =DeptoVendasAFResumo['#DeItens]
Eis a lista de carateres especiais que precisam de um caráter de escape (') na fórmula:
-
Colchete esquerdo ([)
-
Colchete direito (])
-
Sinal de cerquilha (#)
-
Aspas simples (')
-
A assinar (@)
Usar o caractere de espaço para melhorar a legibilidade em uma referência estruturada Você pode usar caracteres de espaço para melhorar a legibilidade de uma referência estruturada. Por exemplo: =DeptoVendas[[Vendedor]:[Região]] ou =DeptoVendas[[#Cabeçalhos], [#Dados], [% da Comissão]]
Recomenda-se usar um espaço:
-
Após o primeiro colchete esquerdo ([)
-
Antes do último colchete direito (]).
-
Depois de uma vírgula.
Operadores de referência
Para maior flexibilidade na especificação de intervalos de células, é possível usar os operadores de referência a seguir para combinar especificadores de colunas.
Esta referência estruturada: |
Refere-se ao seguinte: |
Usando: |
Que é o intervalo de células: |
---|---|---|---|
=DeptoVendas[[Vendedor]:[Região]] |
Todas as células em duas ou mais colunas adjacentes |
: (dois-pontos) - operador de intervalo |
A2:B7 |
=DeptoVendas[Valor das vendas],DeptoVendas[Valor da comissão] |
Uma combinação de duas ou mais colunas |
, (vírgula) - operador de união |
C2:C7, E2:E7 |
=DeptoVendas[[Vendedor]:[Valor das vendas]] DeptoVendas[[Região]:[% da comissão]] |
A interseção de duas ou mais colunas |
(espaço) - operador de interseção |
B2:C7 |
Especificadores de itens especiais
Para se referir a partes específicas de uma tabela, como apenas ao total de linhas, você pode usar qualquer um dos seguintes especificadores de itens especiais em suas referências estruturadas.
Este especificador de item especial: |
Refere-se a: |
---|---|
#Tudo |
A tabela inteira, incluindo cabeçalhos de colunas, dados e totais (se houver). |
#Dados |
Somente as linhas de dados. |
#Cabeçalhos |
Somente a linha de cabeçalhos. |
#Totais |
Somente a linha de totais. Se não houver uma, o valor retornado será nulo. |
#Esta linha ou @ ou @[Nome da coluna] |
Apenas as células na mesma linha que a fórmula. Esses especificadores não podem ser combinados com outros especificadores de item especial. Use-os para forçar um comportamento implícito de interseção na referência ou para substituir um comportamento implícito de interseção e referir-se a valores únicos de uma coluna. O Excel altera automaticamente os especificadores #Esta linha para um especificador @ mais curto em tabelas com mais de uma linha de dados. Porém, se a tabela tiver apenas uma linha, o Excel não substituirá o especificador #Esta linha, o que poderá causar resultados de cálculo inesperados ao adicionar mais linhas. Para evitar problemas de cálculo, verifique se você inseriu várias linhas na tabela antes de inserir uma fórmula de referência estruturada. |
Qualificando referências estruturadas em colunas calculadas
Ao criar uma coluna calculada, normalmente você usa uma referência estruturada para criar a fórmula. Essa referência estruturada pode ser não qualificada ou totalmente qualificada. Por exemplo, para criar uma coluna calculada denominada Valor da Comissão em dólares, é possível usar as seguintes fórmulas:
Tipo de referência estruturada |
Exemplo |
Comentário |
---|---|---|
Não qualificado |
=[Valor das vendas]*[% da comissão] |
Multiplica os valores correspondentes na linha atual. |
Totalmente qualificada |
=DeptoVendas[Valor das vendas]*DeptoVendas[% da comissão] |
Multiplica os valores correspondentes para cada linha em ambas as colunas. |
A regra geral a ser seguida é esta: se você estiver usando referências estruturadas em uma tabela, como quando cria uma coluna calculada, poderá usar uma referência estruturada não qualificada; porém, se usar a referência estruturada fora da tabela, você precisará usar uma referência estruturada totalmente qualificada.
Exemplos do uso de referências estruturadas
Veja algumas maneiras de usar referências estruturadas.
Esta referência estruturada: |
Refere-se ao seguinte: |
Que é o intervalo de células: |
---|---|---|
=DeptoVendas[[#Todos],[Valor das vendas]] |
Todas as células na coluna Valor das vendas. |
C1:C8 |
=DeptoVendas[[#Cabeçalhos],[% da comissão]] |
O cabeçalho da coluna % de comissão. |
D1 |
=VendasDepto[[#Totais],[Região]] |
O total da coluna Região. Se não houver uma linha de Totais, retornará nulo. |
B8 |
=DeptoVendas[[#Todos],[Valor das vendas]:[% da comissão]] |
Todas as células em Valor das vendas e % da comissão. |
C1:D8 |
=DeptoVendas[[#Dados],[% da comissão]:[Valor da comissão]] |
Apenas os dados das colunas % da comissão e Valor da comissão. |
D2:E7 |
=DeptoVendas[[#Cabeçalhos],[Região]:[Valor da comissão]] |
Apenas os cabeçalhos das colunas entre Região e Valor da comissão. |
B1:E1 |
=DeptoVendas[[#Totais],[Valor das vendas]:[Valor da comissão]] |
Os totais das colunas de Valor das vendas até Valor da comissão. Se não houver uma linha de Totais, o valor retornado será nulo. |
C8:E8 |
=DeptoVendas[[#Cabeçalhos],[#Dados], [% da comissão]] |
Apenas o cabeçalho e os dados de % da comissão. |
D1:D7 |
=DeptoVendas[[#Esta linha], [Valor da comissão]] ou =DeptoVendas[@Valor da comissão] |
A célula na interseção da linha atual e a coluna Montante da Comissão. Se for utilizado na mesma linha que um cabeçalho ou linha total, será devolvido um erro de #VALUE! . Se você digitar a forma mais longa dessa referência estruturada (#Esta linha) em uma tabela com várias linhas de dados, o Excel a substituirá automaticamente pela forma mais curta (@). Ambas funcionam da mesma forma. |
E5 (se a linha atual for 5) |
Estratégias para trabalhar com referências estruturadas
Considere as questões a seguir ao trabalhar com referências estruturadas.
-
Usar o preenchimento automático de fórmulas O recurso Preenchimento Automático de Fórmulas pode ser muito útil na inserção de referências estruturadas para assegurar o uso da sintaxe correta. Para obter mais informações, consulte Usar o preenchimento automático de fórmula.
-
Decidir se pretende gerar referências estruturadas para tabelas em semi-seleções Por predefinição, quando cria uma fórmula, selecionar um intervalo de células numa tabela seleciona as células e introduz automaticamente uma referência estruturada em vez do intervalo de células na fórmula. Esse comportamento de semisseleção facilita a inserção de uma referência estruturada. Pode ativar ou desativar este comportamento ao selecionar ou desmarcar a caixa de diálogo Utilizar nomes de tabelas em fórmulas marcar na caixa de diálogo Opções de > de Ficheiros > Fórmulas > Trabalhar com fórmulas.
-
Utilizar livros com ligações externas para tabelas do Excel noutros livros Se um livro contiver uma ligação externa para uma tabela do Excel noutro livro, esse livro de origem ligado tem de estar aberto no Excel para evitar erros de #REF! no livro de destino que contém as ligações. Se abrir primeiro o livro de destino e forem apresentados erros #REF! , estes serão resolvidos se, em seguida, abrir o livro de origem. Se abrir primeiro o livro de origem, não deverá ver códigos de erro.
-
Converter um intervalo em uma tabela e uma tabela em um intervalo Ao converter uma tabela em um intervalo, todas as referências de células são alteradas para suas referências absolutas equivalentes do estilo A1. Ao converter um intervalo em um tabela, o Excel não altera automaticamente qualquer referência de célula a esse intervalo para referências estruturadas equivalentes.
-
Desativar os cabeçalhos da coluna Pode ativar e desativar os cabeçalhos de coluna da tabela no separador Estrutura da Tabela > Linha de Cabeçalho. Se desativar os cabeçalhos de coluna da tabela, as referências estruturadas que utilizam nomes de coluna não são afetadas e pode utilizá-las em fórmulas. As referências estruturadas que se referem diretamente aos cabeçalhos de tabela (por exemplo, =DeptVendas[[#Headers],[%Comissão]]) resultarão em #REF.
-
Adicionar ou excluir colunas e linhas em tabelas Uma vez que os intervalos de dados da tabela mudam frequentemente, as referências de células para referências estruturadas ajustam-se automaticamente. Por exemplo, se você usar o nome de uma tabela em uma fórmula que conta todas as células de dados em uma tabela e, em seguida, adicionar uma linha de dados, a referência à célula será automaticamente ajustada.
-
Renomear uma tabela ou coluna Se você renomear uma coluna ou tabela, o Excel alterará automaticamente o uso dessa tabela e do cabeçalho dessa coluna em todas as referências estruturadas utilizadas na pasta de trabalho.
-
Mover, copiar e preencher referências estruturadas Todas as referências estruturadas permanecem iguais quando você copia ou move uma fórmula que usa uma referência estruturada.
Observação: Copiar uma referência estruturada e preencher uma referência estruturada não é a mesma coisa. Quando copia, todas as referências estruturadas permanecem as mesmas, enquanto quando preenche uma fórmula, as referências estruturadas completamente qualificadas ajustam os especificadores de colunas, como uma série, conforme resumido na tabela seguinte.
Se a direção do preenchimento for: |
E quando preencher, pressione: |
Então: |
---|---|---|
Para cima ou para baixo |
Nada |
Não haverá ajuste dos especificadores de colunas. |
Para cima ou para baixo |
Ctrl |
Os especificadores de colunas serão ajustados como uma série. |
Para a direita ou para a esquerda |
Nada |
Os especificadores de colunas serão ajustados como uma série. |
Para cima, para baixo, para a direita ou para a esquerda |
Shift |
Em vez de substituir valores nas células atuais, os valores atuais das células são movidos e os especificadores de colunas são inseridos. |
Precisa de mais ajuda?
Você pode sempre consultar um especialista na Excel Tech Community ou obter suporte nas Comunidades.
Tópicos Relacionados
Descrição geral das tabelas do ExcelVídeo: Criar e formatar uma tabela do ExcelTotalizar os dados numa tabela do ExcelFormatar uma tabela do ExcelRedimensionar uma tabela ao adicionar ou remover linhas e colunasFiltrar dados num intervalo ou tabelaConverter uma tabela num intervaloProblemas de compatibilidade de tabelas do ExcelExportar uma tabela do Excel para o SharePointDescrição geral das fórmulas no Excel