Usando referências estruturadas com as tabelas do Excel

Importante    Este artigo foi traduzido por um sistema de tradução automática, leia o aviso de isenção de responsabilidade. Para sua referência, veja a versão em inglês deste artigo aqui.

Referências estruturadas tornam muito mais fácil e intuitivo trabalhar com dados da tabela quando você estiver usando fórmulas que fazem referência a uma tabela – em partes de uma tabela ou a tabela inteira. Eles são especialmente úteis porque geralmente alterar os intervalos de dados de tabela, e as referências de célula para referências estruturadas serão ajustadas automaticamente. Isso minimiza a necessidade de regravar fórmulas como linhas e colunas são adicionadas e excluídas de uma tabela, ou quando dados externos são atualizados.

Esta referência estruturada é mais fácil de entender:

Que essa referência de célula:

=SOMA(VendasDepto[ValVendas])

=Soma(C2:C7)

Neste artigo

O exemplo de tabela de vendas do departamento

Componentes de uma referência estruturada

Nomes de tabela e especificadores de coluna

Operadores de referência

Especificadores de itens especiais

Qualificando referências estruturadas em colunas calculadas

Exemplos do uso de referências estruturadas

Trabalhando com referências estruturadas

Regras da sintaxe das referências estruturadas

O exemplo de tabela de vendas do departamento

O seguinte é um exemplo, mencionado neste artigo, de uma tabela com base nas vendas de um departamento de seis funcionários com os totais de vendas mais recentes e comissões.

Tabela de exemplo do Departamento de Vendas
Uma tabela de vendas do departamento

1. A tabela inteira (A1:E8)

2. Os dados da tabela (A2:E7)

3. Uma coluna e o respectivo cabeçalho (D1:D8)

4. Uma coluna calculada (E1:E8)

5. A linha de Totais (A8:E8)

Início da página

Componentes de uma referência estruturada

Para trabalhar com tabelas e referências estruturadas com eficiência, você precisa entender como criar a sintaxe das referências estruturadas durante a criação de fórmulas. Os componentes de uma referência estruturada são ilustrados no exemplo a seguir de uma fórmula que soma os valores de vendas totais e os valores de comissão:

Uma fórmula com referências estruturadas

1. um nome de tabela é um nome significativo que você fornecer para fazer referência aos dados da tabela real (excluindo a linha de cabeçalhos e a linha de total, se houver).

2. um especificador de coluna é derivado do cabeçalho da coluna, entre colchetes e faz referência aos dados de coluna (exceto o cabeçalho da coluna e o total, se houver).

3. Um especificador de item especial é um meio para se referir a partes específicas da tabela, como a linha de Totais.

4. o especificador de tabela é a parte externa da referência estruturada que é colocada entre colchetes após o nome da tabela.

5. uma referência estruturada é a seqüência inteira, começando com o nome da tabela e terminando com o especificador de tabela.

Início da página

Nomes de tabela e especificadores de coluna

Cada vez que você inserir uma tabela, o Excel cria um nome de tabela padrão (Tabela1, Tabela2 e assim por diante) no escopo ou no nível de pasta de trabalho global. Você pode facilmente alterar o nome para torná-lo mais significativo para você. Por exemplo, para alterar a tabela 1 para DeptSales, você pode usar a caixa de diálogo Editar nome. (Na guia Design, no grupo de Propriedades, editar o nome da tabela na caixa Nome da tabela ).

Um nome de tabela refere-se a todo o intervalo de dados na tabela, com exceção de linhas de cabeçalho e total. O exemplo de tabela do departamento de vendas, o nome da tabela, DeptSales, refere-se ao intervalo de célula A2:E7.

Da mesma forma que os nomes de tabela, especificadores de coluna representam referências para a coluna inteira de dados exceto o cabeçalho da coluna e o total. O exemplo de tabela do departamento de vendas, o especificador de coluna, [Região], refere-se para o intervalo de células B2: B7, e o especificador de coluna [ComPct], refere-se ao intervalo de célula D2:D7.

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 ao seguinte:

Usando:

Que, além de exemplo , é o intervalo de células:

= DeptSales [[SalesPers]: [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

= DeptSales [[SalesPers]: [SaleAmt]] DeptSales [[Região]: [ComPct]]

A interseção de duas ou mais colunas

 (espaço) - operador de interseção

B2:C7

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, além de 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

Qualificando referências estruturadas em colunas calculadas

Quando você criar uma coluna calculada, você normalmente usa uma referência estruturada para criar a fórmula. Esta referência estruturada pode ser totalmente qualificado ou não qualificado. Por exemplo, para criar a coluna calculada chamada, ComAmt, que calcula o valor da comissão em dólares, você pode usar as seguintes fórmulas:

Tipo de referência estruturada

Exemplo

Comentário

Não qualificado

=[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

Exemplos do uso de referências estruturadas

Há várias maneiras que você pode usar esses itens especiais e combiná-los com nomes de tabela e referências de coluna, como mostra a seguinte informação:

Esta referência estruturada:

Refere-se ao seguinte:

Que, além de 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

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.

Pastas de trabalho que contêm links externos para tabelas do Excel em outras pastas de trabalho

Se uma pasta de trabalho contiver um link externo para uma tabela do Excel em outra pasta de trabalho, a pasta de trabalho "de origem" vinculada deverá ser aberta no Excel para evitar códigos de erro #REF! na pasta de trabalho "de destino" que contém os links. Se você abrir a pasta de trabalho de destino primeiro e forem exibidos códigos de erro #REF!, eles serão solucionados se você abrir a pasta de trabalho de origem (se ela for aberta primeiro, os códigos de erro não serão exibidos).

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 Estilos 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 freqüentemente alterar os intervalos de dados de tabela, as referências de célula para referências estruturadas ajustar automaticamente. Por exemplo, se você usar um nome de tabela em uma fórmula que conte todas as células de dados da tabela departamento de vendas, como =COUNTA(DeptSales) no exemplo de tabela do departamento de vendas, o valor retornado é 30 porque o intervalo de dados é A2:E7. Se você então adicionasse uma linha de dados, a referência de célula se ajusta automaticamente para A2:E8 e o novo valor retornado é 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

Regras da sintaxe das referências estruturadas

A seguir está uma lista de regras de sintaxe que você precisa estar ciente de quando você cria e editar referências estruturadas.

Observações   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    = DeptSales [[SalesPers]: [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 tabela contém um dos seguintes caracteres especiais, o cabeçalho da coluna inteira deve ser colocado entre colchetes. Na verdade, isso significa colchetes duplos são necessários em um especificador de coluna com os seguintes caracteres especiais: espaço, tabulação, avanço de linha e retorno de carro, vírgula (,), dois-pontos (:), ponto (.), colchete esquerdo ([), colchete direito (]), sinal de número (#), aspas simples ('), aspas (' '), chaves ({}), à direita de chave (}), cifrão ($), karet (^), e comercial (&), asterisco (*), além de sinal (+) sinal (=), sinal de menos (-), maior que (>) símbolo de igual, menor que (<) de símbolo da esquerda e o 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    = DeptSales [[SalesPers]: [Região]]

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

Início da página

Observação   Aviso de Isenção de Tradução Automática: Este artigo foi traduzido por computador, sem intervenção humana. A Microsoft oferece essas traduções automáticas para ajudar as pessoas que não falam inglês a aproveitar os textos escritos sobre produtos, serviços e tecnologias da Microsoft. Como este artigo foi traduzido automaticamente, é possível que contenha erros de vocabulário, sintaxe ou gramática.

Aplicável a: Excel 2010



Essas informações foram úteis?

Sim Não

Como podemos melhorá-lo?

255 caracteres restantes

Para proteger sua privacidade, não inclua informações de contato em seus comentários. Avalie nosso política de privacidade.

Obrigado por seus comentários!

Alterar idioma