Usando referências estruturadas com tabelas do Excel

Observação:  Desejamos fornecer o conteúdo da Ajuda mais recente no seu idioma da forma mais rápida possível. Esta página foi traduzida de forma automatizada e pode conter imprecisões ou erros gramaticais. Nosso objetivo é que este conteúdo seja útil para você. As informações foram úteis? Dê sua opinião no final desta página. Aqui está o artigo em inglês para facilitar a referência.

Quando você criar uma tabela do Excel, Excel atribui um nome para a tabela e para cada cabeçalho de coluna na tabela. Quando você adicionar fórmulas a uma tabela do Excel, os nomes podem aparecer automaticamente quando você insere a fórmula e selecione as referências de célula na tabela em vez de inseri-los manualmente. Aqui está um exemplo do que o Excel faz:

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, clique nas células da tabela às quais você deseja fazer referência em vez de digitar sua referência de célula 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.

Vendas Pessoa

Região

Valor de vendas

% de 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%

  1. Copie os dados de exemplo na tabela acima, incluindo os títulos de coluna e colá-lo na célula A1 de uma nova planilha do Excel.

  2. Para criar a tabela, selecione qualquer célula no intervalo de dados e pressione Ctrl + T.

  3. Verifique se que a caixa Minha tabela tem cabeçalhos está marcada e clique em Okey.

  4. Na célula E2, digite um sinal de igualdade (=) e clique na célula C2.

    Na barra de fórmulas, a referência estruturada [@[Valor de Vendas]] é exibida após o sinal de igualdade.

  5. Digite um asterisco (*) logo após o colchete de fechamento e clique na célula D2.

    Na barra de fórmulas, a referência estruturada [@[% de comissão]] é exibida após o asterisco.

  6. Pressione Enter.

    O Excel cria automaticamente uma coluna calculada e copia a fórmula na coluna inteira para você, ajustando-a em 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.

  1. Em sua planilha de exemplo, clique na célula E2

  2. Na barra de fórmulas, digite = 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.

  1. Selecione qualquer célula da tabela para mostrar as Ferramentas de tabela > guia Design na faixa de opções.

  2. 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 tabela:

  • Usar caracteres válidos  Sempre iniciar um nome com uma letra, um caractere de sublinhado (_) ou uma barra invertida (\). Use letras, números, pontos e sublinhados para o restante do nome. Não é possível usar "C", "c", "R" ou "r" para o nome, pois elas já estão designadas como um atalho para selecionar a coluna ou linha da célula ativa quando você as digita na caixa nome ou Ir para .

  • Não use referências de célula  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  Espaços não podem ser usados no nome. Você pode usar o caractere de sublinhado (_) e o período (.) como separadores de palavras. Por exemplo, Deptovendas, Imposto_Vendas ou primeiro.

  • Não use mais de 255 caracteres Um nome de tabela pode ter até 255 caracteres.

  • Use nomes de tabela exclusivos Nomes duplicados não são permitidos. O Excel não distingue entre caracteres maiúsculos e minúsculos em nomes. Portanto, se você digitar "Vendas", mas já tiver outro nome "VENDAS" na mesma pasta de trabalho, você receberá uma solicitação para escolher um nome exclusivo.

  • Use um identificador de objeto  Se você planeja ter uma mistura de tabelas, gráficos e tabelas dinâmicas, é uma boa ideia prefixo 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 venda ou ptchrt_Sales de um gráfico dinâmico venda. 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 as referências estruturadas manualmente na fórmula, mas para fazer isso, ajudará a compreender sintaxe de referência estruturada. Vamos falar sobre o exemplo de fórmula a seguir:

=SOMA(DeptoVendas[[#Totais],[Valor das Vendas]],DeptoVendas[[#Dados],[Valor da Comissão]])

Essa fórmula tem os seguintes componentes de referência estruturada:

  • Nome de tabela:   Deptovendas é um nome de tabela personalizada. Ele referências os dados de tabela, sem as linhas de cabeçalho ou total. Você pode usar um nome de tabela padrão, como Table1, ou alterá-lo para usar um nome personalizado.

  • Especificador de coluna:   [Quantidade de vendas]e[Valor da comissão] são especificadores de colunas que usam os nomes das colunas que representam. Elas fazem referência a dados de coluna, sem qualquer linha de cabeçalho ou total de coluna. Sempre coloque especificadores entre colchetes, conforme mostrado.

  • Especificador de item:   [#Totals] e [#Data] são especificadores de itens especiais que fazem referência a partes específicas da tabela, como a linha de totais.

  • 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:   (Deptovendas [[#Totals], [valor de vendas]] e Deptovendas [[#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:

  • Especificadores entre colchetes uso    Todos os especificadores de itens especiais, coluna e tabela precisam ser colocados entre colchetes ([]) de correspondência. Um especificador que contém outros especificadores requer externas colchetes correspondentes coloque interno centrais dos outros especificadores de correspondência. Por exemplo: = Vendasdepto [[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 (/)

  • 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]

Veja a lista de caracteres especiais que precisam de um caractere de escape (‘) na fórmula:

  • Colchete esquerdo ([)

  • Colchete direito (])

  • Sinal de cerquilha (#)

  • Aspas simples (')

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 de valor da comissão. Se usado na mesma linha como um cabeçalho ou uma linha de total, isso retornará um #VALUE! erro.

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 AutoCompletar fórmula    Você pode descobrir que usando o preenchimento automático de fórmula é muito útil quando você insere referências estruturadas e para garantir o uso da sintaxe correta. Para obter mais informações, consulte Usar o recurso AutoCompletar fórmula.

  • Decida se deseja gerar referências estruturadas para tabelas no ponto de seleções    Por padrão, quando você cria uma fórmula, clicando em uma célula das células de intervalo dentro de uma marca de ponto de tabela e insere automaticamente uma referência estruturada em vez do intervalo de células na fórmula. Esse comportamento semisseleção torna muito mais fácil inserir uma referência estruturada. Você pode ativar esse comportamento ou desativar marcando ou desmarcando a caixa de seleção usar nomes em fórmulas de tabela no arquivo > Opções > fórmulas > caixa de diálogo Trabalhando com fórmulas.

  • Pastas de trabalho de uso com links externos para tabelas do Excel em outras pastas de trabalho    Se uma pasta de trabalho contém um link externo a uma tabela do Excel em outra pasta de trabalho, essa pasta de trabalho de origem vinculada deve ser aberta no Excel para evitar #REF! erros da pasta de trabalho de destino que contém os vínculos. Se você abrir a pasta de trabalho de destino primeiro e #REF! erros aparecem, eles serão resolvidos se você abrir a pasta de trabalho de origem. Se você abrir a pasta de trabalho de origem pela primeira vez, você deve ver sem 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 de coluna    Você pode alternar tabela cabeçalhos de coluna e desativar a partir da guia de Design de tabela > Linha de cabeçalho. Se você desativar cabeçalhos de coluna de tabela, as referências estruturadas que usam nomes de coluna não são afetadas e você ainda pode usá-los em fórmulas. Referências que se referem diretamente aos cabeçalhos da tabela estruturadas (por exemplo, = Vendasdepto [[#Headers], [% da comissão]]) resultará em #REF.

  • Adicionar ou excluir colunas e linhas de tabelas    Como os intervalos de dados de tabela frequentemente mudam, referências de célula para referências estruturadas ajustam automaticamente. Por exemplo, se você usa um nome de tabela em uma fórmula para contar todas as células de dados em uma tabela e, em seguida, adicionar uma linha de dados, a referência de célula é automaticamente ajustado.

  • 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 fazer um preenchimento de uma referência estruturada não são a mesma coisa. Quando você copia, todas as referências estruturadas permanecem iguais. Por outro lado, quando você preenche uma fórmula, referências estruturadas totalmente qualificadas ajustam os especificadores de coluna como uma série, conforme resumido na tabela a seguir.

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 os valores nas células atuais, esses valores serão movidos e os especificadores de colunas serão inseridos.

Precisa de mais ajuda?

Você pode sempre consultar um especialista na Excel Tech Community, obter suporte na Comunidade de respostas ou sugerir um novo recurso ou melhoria no User Voice do Excel.

Tópicos Relacionados

Visão geral das tabelas do Excel
vídeo: criar e formatar uma tabela do Excel
totalizar os dados em uma tabela do Excel
Formatar uma tabela do Excel
redimensionar uma tabela adicionando ou removendo linhas e colunas
Filtrar dados em um intervalo ou tabela
converter uma tabela em um intervalo
problemas de compatibilidade de tabelas do Excel
Exportar uma tabela do Excel no SharePoint
Overviews de fórmulas no Excel

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×