Usando referências estruturadas com tabelas do Excel

As referências estruturadas tornam o trabalho com dados de tabela muito mais fácil e intuitivo quando você usa fórmulas que se referem a uma tabela – inteira ou em parte. Elas são especialmente úteis porque muitas vezes os intervalos de dados de tabela são alterados e as referências de células para as referências estruturadas são ajustadas automaticamente. Isso minimiza a necessidade de reescrever fórmulas à medida que linhas e colunas são adicionadas e excluídas em uma tabela ou quando os dados externos são atualizados.

Esta referência estruturada é mais fácil de entender: Do que esta referência de célula:
=SOMA(VendasDepto[ValVendas]) =Soma(C2:C7)
Neste artigo


Exemplo de tabela do Departamento de Vendas

Segue abaixo um exemplo, citado em todo este artigo, de uma tabela baseada nas vendas de um departamento composto por seis funcionários com os mais recentes valores das vendas e das comissões.


Uma tabela do Departamento de Vendas

Tabela de exemplo do Departamento de Vendas

Texto explicativo 1 A tabela inteira (A1:E8)
Texto explicativo 2 Os dados da tabela (A2:E7)
Texto explicativo 3 Uma coluna e o respectivo cabeçalho (D1:D8)
Texto explicativo 4 Uma coluna calculada (E1:E8)
Texto explicativo 5 A linha de Totais (A8:E8)

Início da página Início da página

Componentes de uma referência estruturada

Para trabalhar de forma eficiente com tabelas e referências estruturadas, é preciso entender como criar a sintaxe das referências estruturadas durante a criação de fórmulas. Os componentes de uma referência estruturada estão ilustrados no exemplo abaixo de uma fórmula que soma os valores totais das vendas e os valores das comissões:


Uma fórmula com referências estruturadas

Texto explicativo 1 Um nome de tabela é um nome significativo que você define para se referir aos dados da tabela propriamente ditos (excluindo a linha de cabeçalhos e a de totais, se houver).
Texto explicativo 2 Um especificador de coluna deriva do cabeçalho da coluna, colocado entre colchetes, e faz referência aos dados da coluna (excluindo o cabeçalho da coluna e o total, se houver).
Texto explicativo 3 Um especificador de item especial é um meio para se referir a partes específicas da tabela, como a linha de Totais.
Texto explicativo 4 O especificador de tabela é a parte externa da referência estruturada que está inclusa entre colchetes logo em seguida do nome da tabela.
Texto explicativo 5 Uma referência estruturada é a cadeia de caracteres inteira, começando pelo nome da tabela e terminando pelo especificador da tabela.

Início da página Início da página

Nomes de tabelas e especificadores de colunas

Sempre que for inserida uma tabela, o Excel cria um nome de tabela padrão (Tabela1, Tabela2, etc.) no escopo ou nível global da pasta de trabalho. É possível alterar o nome facilmente para torná-lo mais significativo para o usuário. Por exemplo, para mudar Tabela1 para VendasDepto, você pode usar a caixa de diálogo Editar Nome. (Na guia Design, no grupo Propriedades, edite o nome da tabela na caixa Nome da Tabela.)

O nome da tabela refere-se ao intervalo inteiro de dados na tabela, com exceção das linhas de cabeçalho e total. No exemplo de tabela do Departamento de Vendas, o nome da tabela VendasDepto refere-se ao intervalo de células A2:E7.

De modo semelhante aos nomes de tabelas, os especificadores de colunas representam referências para a coluna inteira de dados, com exceção do total e do cabeçalho da coluna. No exemplo de tabela do Departamento de Vendas, o especificador de coluna [Região] refere-se ao intervalo de células B2:B7 e o especificador de coluna [PctCom] refere-se ao intervalo de células D2:D7.

Início da página Início da página

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 a: Usando: Que, no Exemplo, é o intervalo de células:
=VendasDepto[[PessVendas]:[Região]] Todas as células em duas ou mais colunas adjacentes : (dois-pontos) - operador de intervalo A2:B7
=VendasDepto[ValVendas],VendasDepto[ValCom] Uma combinação de duas ou mais colunas , (vírgula) - operador de união C2:C7, E2:E7
=VendasDepto[[PessVendas]:[ValVendas]] VendasDepto[[Região]:[PctCom]] A interseção de duas ou mais colunas  (espaço) - operador de interseção B2:C7

Início da página Início da página

Especificadores de itens especiais

Para maior conveniência, também é possível usar itens especiais para se referir a diversas partes de uma tabela, como, por exemplo, apenas a linha de Totais, para facilitar a referência a essas partes nas fórmulas. Seguem abaixo os especificadores de itens especiais que podem ser usados em uma referência estruturada:

Este especificador de item especial: Refere-se a: Que, no Exemplo, é o intervalo de células:
=VendasDepto[#Tudo] A tabela inteira, incluindo cabeçalhos de colunas, dados e totais (se houver). A1:E8
=VendasDepto[#Dados] Apenas os dados. A2:E7
=VendasDepto[#Cabeçalhos] Apenas a linha de cabeçalhos. A1:E1
=VendasDepto[#Totais] Apenas a linha de totais. Se não houver uma, retornará nulo. A8:E8
=VendasDepto[#Esta Linha] Apenas a parte das colunas na linha atual. #Esta Linha não pode ser combinado com nenhum outro especificador de item especial. Use-o 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. Para obter mais exemplos, consulte Exemplos do uso de referências estruturadas. A5:E5 (se a linha atual for 5)

Início da página Início da página

Qualificando referências estruturadas em colunas calculadas

Ao criar uma coluna calculada, geralmente 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 ValCom, que calcule o valor da comissão em dólares, é possível usar as seguintes fórmulas:

Tipo de referência estruturada Exemplo Comentário
Não qualificada =[ValVendas]*[PctCom] Multiplica os valores correspondentes na linha atual.
Totalmente qualificada =VendasDepto[ValVendas]*VendasDepto[PctCom] 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.

Início da página Início da página

Exemplos do uso de referências estruturadas

Há várias maneiras de usar esses itens especiais e combiná-los com nomes de tabelas e referências de colunas, como mostram as informações abaixo:

Esta referência estruturada: Refere-se ao seguinte: Que, no Exemplo, é o intervalo de células:
=VendasDepto[[#Tudo],[ValVendas]] Todas as células da coluna ValVendas. C1:C8
=VendasDepto[[#Cabeçalhos],[PctCom]] O cabeçalho da coluna PctCom. C1
=VendasDepto[[#Totais],[Região]] O total da coluna Região. Se não houver uma linha de Totais, retornará nulo. B8
=VendasDepto[[#Tudo],[ValVendas]:[PctCom]] Todas as células em ValVendas e PctCom. C1:D8
=VendasDepto[[#Dados],[PctCom]:[ValCom]] Apenas os dados das colunas PctCom e ValCom. D2:E7
=VendasDepto[[#Cabeçalhos],[Região]:[ValCom]] Apenas os cabeçalhos das colunas entre Região e ValCom. B1:E1
=VendasDepto[[#Totais],[ValVendas]:[ValCom]] Os totais das colunas de ValVendas até ValCom. Se não houver uma linha de Totais, retornará nulo. C8:E8
=VendasDepto[[#Cabeçalhos],[#Dados],[PctCom]] Apenas o cabeçalho e os dados de PctCom. D1:D7
=VendasDepto[[#Esta Linha], [ValCom]] A célula na interseção da linha atual com a coluna ValCom. E5 (se a linha atual for 5)

Início da página Início da página

Trabalhando com referências estruturadas

Considere as questões a seguir ao trabalhar com referências estruturadas.

Usando o Preenchimento Automático de Fórmulas    

O recurso Preenchimento Automático de Fórmulas pode ser muito útil quando você insere referências estruturadas, para assegurar o uso da sintaxe correta.

Para obter mais informações, consulte Usar o Preenchimento Automático de Fórmulas.

Decidindo se é para gerar referências estruturadas para tabelas em semisseleção    

Por padrão, na criação de uma fórmula, quando você clica em um intervalo de células dentro de uma tabela, as células são semisselecionadas; além disso, é automaticamente inserida na fórmula uma referência estruturada, em vez do intervalo de células. Esse comportamento de semisseleção torna muito mais fácil a inserção de uma referência estruturada. É possível ativar ou desativar este comportamento marcando ou desmarcando a caixa de seleção Usar nomes de tabela em fórmulas na seção Trabalhando com fórmulas da categoria Fórmulas na caixa de diálogo Opções do Excel.

Convertendo um intervalo em tabela e uma tabela em intervalo    

Ao converter uma tabela em intervalo, todas as referências de células são alteradas para suas referências equivalentes do estilo A1. Ao converter um intervalo em tabela, o Excel não altera automaticamente nenhuma referência de célula a esse intervalo para os nomes de tabelas e referências de colunas equivalentes.

Desativando cabeçalhos de colunas    

Se você desativar os cabeçalhos das colunas da tabela (na guia Design da tabela, no grupo Opções de Estilo de Tabela, desmarque Linha de Cabeçalho), as referências estruturadas que utilizam esses cabeçalhos não serão afetadas e ainda poderão ser usadas em fórmulas.

Adicionando ou excluindo colunas e linhas de tabelas    

Como os intervalos de dados das tabelas muitas vezes mudam, as referências de células para as referência estruturadas são ajustadas automaticamente. Por exemplo, se você usar um nome de tabela em uma fórmula que conta todas as células de dados na tabela Vendas do Departamento, como =CONT.VALORES(VendasDepto) no exemplo de tabela do Departamento de Vendas, o valor retornado será 30, pois o intervalo de dados é A2:E7. Se você adicionar uma linha de dados, a referência de célula será automaticamente ajustada para A2:E8 e o novo valor retornado será 35.

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

Movendo, copiando e preenchendo referências estruturadas    

Todas as referências estruturadas permanecem iguais quando você copia ou move uma fórmula que usa essas referências.

Quando uma fórmula é preenchida, as referências estruturadas totalmente qualificadas podem ajustar os especificadores de colunas como uma série, conforme resumido na tabela a seguir.

Se a direção do preenchimento for: E, enquanto estiver preenchendo, você pressionar: 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 a direita ou para a esquerda CTRL Não haverá ajuste dos especificadores de colunas.
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.

Início da página Início da página

Regras da sintaxe das referências estruturadas

A seguir encontra-se uma lista das regras de sintaxe que você deve conhecer quando for criar e editar referências estruturadas.

 Observação   Os nomes de tabelas seguem as mesmas regras dos nomes definidos.Para obter mais informações, consulte Definir e usar nomes em fórmulas.

O uso de colchetes nos especificadores    

Todos os especificadores de tabelas, colunas e itens especiais devem estar entre colchetes ([ ]). Um especificador que contenha outros especificadores requer um par de colchetes externos envolvendo os pares de colchetes internos dos outros especificadores.

Exemplo     =VendasDepto[[PessVendas]:[Região]]

Os cabeçalhos de colunas são cadeias de texto    

Todos os cabeçalhos de colunas são cadeias de texto, mas não requerem aspas quando são usados em uma referência estruturada. Se um cabeçalho de coluna contiver números ou datas, como 2004 ou 1/1/2004, esses dados ainda serão considerados cadeias de texto. Como os cabeçalhos de colunas são cadeias de texto, não é possível usar expressões dentro dos colchetes.

Exemplo     =VendasDeptoAFResumo[[2004]:[2002]]

Caracteres especiais nos cabeçalhos de colunas de uma tabela    

Se um cabeçalho de coluna de uma tabela contiver um dos caracteres especiais indicados a seguir, o cabeçalho inteiro deverá ser colocado entre colchetes. Na verdade, isso significa que são necessários colchetes duplos em um especificador de coluna com os seguintes caracteres especiais: espaço, tabulação, alimentação de linha, retorno de carro, vírgula (,), dois-pontos (:), ponto (.), colchete esquerdo ([), colchete direito (]), cerquilha (#), aspas simples ('), aspas duplas ("), chave esquerda ({), chave direita (}), cifrão ($), circunflexo (^), E comercial (&), asterisco (*), sinal de adição (+), sinal de igual (=), sinal de subtração (-), símbolo de maior que (>), símbolo de menor que (<) e sinal de divisão (/).

Exemplo     =VendasDeptoAFResumo[[ValorTotal em $]]

Só há uma exceção a essa regra: se o único caractere especial utilizado for um caractere de espaço.

Exemplo    =VendasDepto[Valor Total]

Caracteres especiais em cabeçalhos de colunas que requerem o uso do caractere de escape    

Os seguintes caracteres têm um significado especial e requerem o uso de aspas simples (') como um caractere de escape: colchete esquerdo ([), colchete direito (]), cerquilha (#) e aspas simples (').

Exemplo     =VendasDeptoAFResumo['#deItens]

Usando 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, da seguinte maneira:

  • Um espaço após o primeiro colchete esquerdo ([) e antes do último colchete direito (]).
  • Um espaço após uma vírgula.

Exemplo     =VendasDepto[ [PessVendas]:[Região] ]

Exemplo     =VendasDepto[[#Cabeçalhos], [#Dados], [PctCom]]

Início da página Início da página

 
 
Aplica-se a:
Excel 2010