Entrar

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 mais fácil e mais intuitiva para trabalhar com dados de 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 útil porque geralmente alterar os intervalos de dados de tabela e as referências de célula para referências estruturadas ajustam automaticamente. Isso minimiza a necessidade de regravar fórmulas, linhas e colunas são adicionadas e excluídas em uma tabela ou quando dados externos são atualizados.

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

Que esta referência de célula:

=SOMA(VendasDepto[ValVendas])

=Soma(C2:C7)

Neste artigo

O exemplo de tabela do departamento de vendas

Componentes de uma referência estruturada

Nomes de tabela e especificadores de colunas

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 do departamento de vendas

A seguir é um exemplo, referenciado neste artigo, de uma tabela com base nas vendas em um departamento de seis funcionários com as comissões e valores de vendas mais recentes.

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

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 compreender como criar a sintaxe das referências estruturadas quando você estiver criando fórmulas. Os componentes de uma referência estruturada são ilustrados no seguinte exemplo 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ê fornece para fazer referência aos dados de tabela real (excluindo a linha de cabeçalhos e linha de totais, se houver).

2. um especificador de coluna é derivado do cabeçalho da coluna, entre colchetes e referências os dados da coluna (excluindo o cabeçalho de coluna e 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 está entre colchetes após o nome da tabela.

5. uma referência estruturada é a cadeia de caracteres 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 colunas

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

Um nome de tabela se refere a todo o intervalo de dados na tabela excepto o cabeçalho e o total de linhas. O exemplo de tabela do departamento de vendas, o nome da tabela, Deptovendas, refere-se para o intervalo de células a2: E7.

Semelhante aos nomes de tabela, especificadores de colunas representam referências para a coluna inteira de dados excepto o cabeçalho da coluna e 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, [Pctcom], refere-se ao intervalo de células 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:

Qual, no exemplo , é o intervalo de células:

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

= Deptovendas [[SalesPers]: [Valvendas]] Deptovendas [[Região]: [Pctcom]]

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:

Qual, 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

Qualificando referências estruturadas em colunas calculadas

Quando você cria uma coluna calculada, você costuma usar uma referência estruturada para criar a fórmula. Esta referência estruturada pode ser não qualificados ou totalmente qualificado. Por exemplo, para criar a coluna calculada chamada Valcom, 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 combine-as com nomes de tabela e referências de coluna, como mostra o informações a seguir:

Esta referência estruturada:

Refere-se ao seguinte:

Qual, 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

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 os intervalos de dados de tabela freqüentemente mudam, as referências de célula para referências estruturadas ajustam automaticamente. Por exemplo, se você usar um nome de tabela em uma fórmula para contar todas as células de dados na 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ê adicionou, em seguida, uma linha de dados, a referência de célula é automaticamente ajustado 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

Regras da sintaxe das referências estruturadas

A seguir está uma lista de regras de sintaxe que você precisa conhecer ao criar 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    = Vendasdepto [[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 de coluna inteira deve estar entre colchetes. Na verdade, isso significa colchetes duplos são necessários em um especificador de coluna com os seguintes caracteres especiais: espaço, tab, alimentação de linha, retornos de carro, vírgula (,), dois-pontos (:), ponto (.), colchete esquerdo ([), colchete direito (]), sinal de cerquilha (#), aspas simples ('), aspas ("), chave ({), à direita de chave (}), cifrão ($), karet (^), e comercial (&), asterisco (*), além de (+), sinal de igual (=), sinal de subtração (-), sinal de maior (>), menor que símbolo (<) para a 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    = Vendasdepto [[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.



Essas informações foram úteis?

Como podemos melhorá-lo?

Como podemos melhorá-lo?

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!