Utilizar referências estruturadas com tabelas do Excel

Nota: Queremos fornecer-lhe os conteúdos de ajuda mais recentes o mais rapidamente possível e no seu idioma. Esta página foi traduzida automaticamente e pode conter erros gramaticais ou imprecisões. O nosso objetivo é que estes conteúdos lhe sejam úteis. Pode informar-nos se as informações foram úteis no final desta página? Eis o artigo em inglês para referência.

Quando criar uma tabela do Excel, Excel atribui um nome para a tabela e para cada cabeçalho de coluna na tabela. Quando adiciona fórmulas para uma tabela do Excel, os nomes podem aparecer automaticamente à medida que introduza a fórmula e selecione as referências de célula na tabela em vez de introduzi-los manualmente. Eis um exemplo do que faz o Excel:

Em vez de utilizar referências explícitas às células

O Excel usa os nomes das tabelas e das colunas

=SOMA(C2:C7)

=SOMA(DeptVendas[Montante de Vendas])

Essa combinação de nomes de tabelas e colunas chama-se referência estruturada. Os nomes nas referências estruturadas ajustam-se sempre que adiciona ou remove dados da tabela.

As referências estruturadas também surgem quando cria uma fórmula fora de uma tabela Excel que faz referência a dados da tabela. As referências podem fazer com que seja mais fácil localizar tabelas num livro grande.

Para incluir referências estruturadas na fórmula, clique nas células da tabela que pretende referenciar em vez de escrever a referência de célula na fórmula. Neste exemplo, irá introduzir uma fórmula que utiliza automaticamente as referências estruturadas para calcular a quantidade de uma comissão de vendas.

Vendas Pessoa

Região

Montante de Vendas

% Comissão

Montante da Comissão

João

Norte

260

10%

Bruno

Sul

660

15%

Estrela

Oriente

940

15%

Afonso

Ocidente

410

12%

Júlia

Norte

800

15%

Luís

Sul

900

15%

  1. Copie os dados de exemplo na tabela acima, incluindo os cabeçalhos de coluna e colá-la a célula A1 de uma nova folha de cálculo do Excel.

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

  3. Certifique-se que a caixa a minha tabela tem cabeçalhos está selecionada e clique em OK.

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

    Na barra de fórmulas, a referência estruturada [@[Montante de Vendas]] aparece a seguir ao sinal de igual.

  5. Escreva um asterisco (*) imediatamente após o parêntese de fecho e clique na célula D2.

    Na barra de fórmulas, a referência estruturada [@[% Comissão]] aparece a seguir ao asterisco.

  6. Prima Enter.

    O Excel cria automaticamente uma coluna calculada e copia a fórmula ao longo de toda a coluna automaticamente, ajustando-a em cada linha.

O que acontece quando utilizo referências explícitas a células?

Se introduzir referências explícitas a células numa coluna calculada, pode ser mais difícil ver o que é que a fórmula está a calcular.

  1. Na sua folha de cálculo de exemplo, clique na célula E2

  2. Na barra de fórmulas, introduza = C2 * D2 e prima Enter.

Repare que, apesar de o Excel copiar a sua fórmula em toda a coluna, não utiliza referências estruturadas. Se, por exemplo, adicionar uma coluna entre as colunas C e D já existentes, terá de rever a fórmula.

Como posso alterar o nome de uma tabela?

Ao criar uma tabela, o Excel cria um nome de tabela predefinido (Tabela1, Tabela2 e assim sucessivamente), no entanto, pode alterar facilmente o nome, de modo a torná-lo mais significativo.

  1. Selecione qualquer célula da tabela para mostrar o separador Ferramentas de tabela > estrutura no Friso.

  2. Escreva o nome que pretende na caixa Nome da tabela e prima Enter.

Nos nossos dados de exemplo, utilizámos o nomeDeptVendas..

Utilize as seguintes regras para os nomes de tabelas:

  • Utilizar caracteres válidos  Iniciar sempre um nome com uma carta, um caráter de sublinhado (_) ou uma barra invertida (\). Utilizar letras, números, períodos e caracteres para o resto do nome de sublinhado. Não é possível utilizar "C"; "c", "R" ou "r" para o nome, uma vez que já estão designados um atalho para selecionar a coluna ou linha para a célula ativa quando introduzir na caixa nome ou Ir para .

  • Não utilize referências de célula  Nomes não podem ser iguais a uma referência de célula, tal como Z$ 100 ou R1C1.

  • Não utilize um espaço para separar palavras  Não podem ser utilizados espaços no nome. Pode utilizar o caráter de sublinhado (_) e o período (.) como separadores do word. Por exemplo, Deptvendas, Sales_Tax ou First.Quarter.

  • Não utilize mais de 255 carateres O nome de uma tabela pode ter até 255 carateres.

  • Utilize nomes de tabela únicos Não é possível utilizar nomes duplicados. O Excel não distingue entre carateres maiúsculos e minúsculos nos nomes, portanto se introduzir "Vendas" e já tiver outro nome designado como "VENDAS" no mesmo livro, ser-lhe-á pedido para escolher um nome exclusivo.

  • Utilizar um identificador de objecto  Se planeia ter uma mistura de tabelas, tabelas dinâmicas e gráficos, é aconselhável prefixo os 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. Isto mantém todos os seus nomes de uma lista ordenada no Gestor de nomes.

Regras de sintaxe de referências estruturadas

Também pode introduzir ou alterar as referências estruturadas manualmente na fórmula, mas para o fazer, irá ajudá para compreender sintaxe de referências estruturadas. Vamos abordar o exemplo seguinte fórmula:

=SOMA(DeptVendas[[#Totais],[Montante de Vendas]],DeptVendas[#Dados],[Montante da Comissão]])

Esta fórmula tem os seguintes componentes da referência estruturada:

  • Nome da tabela:   Deptvendas é um nome de tabela personalizados. Referencia-os dados da tabela, sem as linhas de cabeçalho ou o total. Pode utilizar um nome de tabela predefinido, tal como tabela1, ou altere para utilizar um nome personalizado.

  • Especificador de coluna:   [Montante de vendas]e[Montante da Comissão] são especificadores de colunas que utilizam os nomes das colunas que representam. Os dados da coluna, sem qualquer linha de cabeçalho ou o total da coluna que fazem referência. Coloque sempre os especificadores entre parênteses conforme mostrado.

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

  • Especificador de tabela:   [[#Totais],[Montante de Vendas]] e [[#Dados],[Montante da Comissão]] são especificadores de tabela que representam as porções exteriores da referência estruturada. As referências exteriores são colocadas entre parênteses retos a seguir ao nome da tabela.

  • Referência estruturada:   (Deptvendas [[#Totals], [montante de vendas]] e Deptvendas [[#Data], [montante da Comissão]] são referências estruturadas, representadas por uma cadeia que começa com o nome da tabela e termina com o especificador de coluna.

Ao criar ou editar manualmente referências estruturadas, utilize estas regras de sintaxe:

  • Especificadores entre parênteses de utilização    Todos os tabela, coluna e especificadores de itens especiais tem de estar entre correspondência de parênteses Retos ([]). Um especificador que contenha outros especificadores requer externas parênteses correspondentes a incluir interiores correspondência de parênteses dos outros especificadores. Por exemplo: = Vendasdepart [[representante de vendas]: [Região]]

  • Todos os cabeçalhos de coluna são cadeias de texto    Porém não necessitam de aspas quando utilizadas numa referência estruturada. Números ou datas, como 2014 ou 01/01/2014, também são considerados cadeias de texto. Não é possível utilizar expressões com os cabeçalhos de coluna. Por exemplo, a expressão ResumoAFDeptVendas[[2014]:[2012]] não funcionará.

Utilizar os cabeçalhos de coluna entre parênteses com carateres especiais    Caso existam carateres especiais, todo o cabeçalho de coluna tem de estar entre parênteses, o que significa que são necessários parênteses duplos num especificador de coluna. Por exemplo: =ResumoAFDeptVendas[[Total $ Montante]]

Em seguida, encontra-se uma lista de carateres especiais que necessitam de parênteses extra na fórmula:

  • TECLA DE TABULAÇÃO

  • Separação de linhas

  • Símbolo de retorno

  • Vírgula (,)

  • Dois pontos (:)

  • Ponto (.)

  • Parêntese reto esquerdo ([)

  • Parêntese reto direito (])

  • Sinal de libra (#)

  • Plica (')

  • Aspas (")

  • Chaveta esquerda ({)

  • Chaveta direita (})

  • Cifrão ($)

  • Acento circunflexo (^)

  • E comercial (&)

  • Asterisco (*)

  • Sinal de adição (+)

  • Sinal de igual (=)

  • Sinal de subtração (-)

  • Símbolo maior que (>)

  • Símbolo menor que (<)

  • Sinal de divisão (/)

  • Utilizar um caráter de escape para alguns carateres especiais nos cabeçalhos de coluna    Alguns carateres têm significados especiais e necessitam de uma plica (') como um caráter de escape. Por exemplo: =ResumoAFDeptVendas['#DeItens]

Segue-se uma lista de carateres especiais que necessitam de um caráter de escape (‘) na fórmula:

  • Parêntese reto esquerdo ([)

  • Parêntese reto direito (])

  • Sinal de libra (#)

  • Plica (')

Utilizar o caráter de espaço para melhorar a legibilidade numa referência estruturada    Pode utilizar carateres de espaço para melhorar a legibilidade de uma referência estruturada. Por exemplo: =DeptVendas[ [Representante de Vendas]:[Região] ] ou =DeptVendas[[#Cabeçalhos], [#Dados], [% Comissão]]

É recomendado utilizar um espaço:

  • Após o primeiro parêntese reto esquerdo ([)

  • Antes do último parêntese reto direito (]).

  • Após uma vírgula.

Operadores de referência

Para obter mais flexibilidade na especificação de intervalos de células, pode utilizar os seguintes operadores de referência para combinar especificadores de colunas.

Esta referência estruturada:

Refere-se a:

Utilizando:

Que corresponde ao intervalo de células:

=DeptVendas[[Representante de Vendas]:[Região]]

Todas as células em duas ou mais colunas adjacentes

: (dois pontos) - operador de intervalo

A2:B7

=DeptVendas[Montante de Vendas],DeptVendas[Montante das Comissões]

Uma combinação de duas ou mais colunas

; (ponto e vírgula) - operador de união

C2:C7; E2:E7

=DeptVendas[[Representante de Vendas]:[Montante de Vendas]] DeptVendas[[Região]:[% Comissão]]

A interseção de duas ou mais colunas

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

B2:C7

Especificadores de itens especiais

Para fazer referência a partes de uma tabela, como apenas a linha de totais, pode utilizar qualquer um dos seguintes especificadores de itens especiais nas suas referências estruturadas.

Este especificador de itens especiais:

Refere-se a:

#Tudo

A tabela inteira, incluindo cabeçalhos de coluna, dados e totais (se existirem).

#Dados

Apenas as linhas de dados.

#Cabeçalhos

Apenas a linha de cabeçalho.

#Totais

Apenas a linha de total. Se não existir nenhuma, é devolvido um valor nulo.

#Esta linha

ou

@

ou

@[Nome da Coluna]

Apenas as células na mesma linha da fórmula. Estes especificadores não podem ser combinados com outro especificadores de itens especiais. Utilize-os para forçar o comportamento de interseção implícita da referência ou para ignorar o comportamento de interseção implícita e fazer referência a valores únicos de uma coluna.

O Excel muda automaticamente os especificadores #Esta Linha para o especificador @ mais curto nas tabelas que têm agora mais do que uma linha de dados. Porém, caso a tabela tenha apenas uma linha, o Excel não substitui o especificador #Esta Linha, o que pode provocar resultados de cálculo inesperados ao adicionar mais linhas. Para evitar problemas de cálculo, certifique-se de que introduz várias linhas na tabela antes de introduzir fórmulas de referência estruturada.

Qualificar referências estruturadas em colunas calculadas

Quando cria uma coluna calculada, utiliza normalmente uma referência estruturada para criar a fórmula. Esta referência estruturada pode ser não qualificada ou totalmente qualificada. Por exemplo, para criar a coluna calculada, designada por Montante das Comissões, que calcula o valor da comissão em euros, pode utilizar as seguintes fórmulas:

Tipo de referência estruturada

Exemplo

Comentário

Não qualificada

=[Montante de Vendas]*[% Comissão]

Multiplica os valores correspondentes da linha atual.

Totalmente qualificada

=DeptVendas[Montante de Vendas]*DeptVendas[% Comissão]

Multiplica os valores correspondentes de cada linha para ambas as colunas.

A regra geral a seguir é a seguinte: se estiver a utilizar referências estruturadas numa tabela, por exemplo quando cria uma coluna calculada, pode utilizar uma referência estruturada não qualificada, mas se utilizar a referência estruturada fora da tabela, necessita de utilizar uma referência estruturada totalmente qualificada.

Exemplos de utilização de referências estruturadas

Aqui estão algumas formas de utilizar referências estruturadas.

Esta referência estruturada:

Refere-se a:

Que corresponde ao intervalo de células:

=DeptVendas[[#Tudo],[Montante de Vendas]]

Todas as células na coluna Montante de Vendas

C1:C8

=DeptVendas[[#Cabeçalhos],[% Comissão]

O cabeçalho da coluna % Comissão.

D1

=VendasDepart[[#Totais];[Região]]

O total da coluna Região. Se não existir nenhuma linha de totais, é devolvido um valor nulo.

B8

=DeptVendas[[#Tudo],[Montante de Vendas]:[% Comissão]]

Todas as células em Montante de Vendas e em % Comissão.

C1:D8

=DeptVendas[[#Dados],[% Comissão]:[Montante da Comissão]]

Apenas os dados das colunas % Comissão e Montante da Comissão.

D2:E7

=DeptVendas[[#Cabeçalhos],[Região]:[Montante da Comissão]]

Apenas os cabeçalhos das colunas entre Região e Montante da Comissão.

B1:E1

=DeptVendas[[#Totais],[Montante de Vendas]:[Montante da Comissão]]

Os totais das colunas Montante de Vendas até Montante da Comissão. Se não existir nenhuma linha de Totais, é devolvido um valor nulo.

C8:E8

=DeptVendas[[#Cabeçalhos],[#Dados],[% Comissão]]

Apenas o cabeçalho e os dados de % Comissão.

D1:D7

=DeptVendas[[#Esta Linha], [Montante da Comissão]

ou

=DeptVendas[@Montante da Comissão]

A célula na interseção da linha atual e a coluna de montante da Comissão. Se for utilizado na mesma linha como cabeçalho ou linha de total, isto irá devolver uma #VALUE! erro.

Caso escreva a forma longa desta referência estruturada (#Esta Linha) numa tabela com várias linhas de dados, o Excel substitui-a pela forma curta (@). Ambas funcionam da mesma forma.

E5 (se a linha atual for a 5)

Estratégias para trabalhar com referências estruturadas

Considere o seguinte quando trabalhar com referências estruturadas.

  • Utilizar a Conclusão Automática de Fórmulas    Poderá considerar a utilização da Conclusão Automática de Fórmulas bastante útil ao introduzir referências estruturadas e para garantir a utilização da sintaxe correta. Para obter mais informações, consulte o artigo Utilizar a Conclusão Automática de Fórmulas.

  • Decida se pretende gerar referências estruturadas para tabelas no ponto e seleções    Por predefinição, quando cria uma fórmula, clicar numa célula o intervalo de células dentro de uma seleciona ponto e de tabela e introduz automaticamente uma referência estruturada em vez do intervalo de células na fórmula. Este comportamento de seleção parcial torna mais fácil introduzir uma referência estruturada. Pode ativar este comportamento ou desativar marcando ou desmarcando a caixa de verificação utilizar nomes de tabela em fórmulas doficheiro > > fórmulas > trabalhar com fórmulas caixa de diálogo Opções.

  • Livros do uso com ligações externas a tabelas do Excel noutros livros    Se um livro contém uma ligação externa para uma tabela do Excel noutro livro, esse livro de origem ligada tem de ser aberto no Excel para evitar #REF! erros no livro de destino que contém as ligações. Se abrir o livro de destino pela primeira vez e #REF! forem apresentados erros, vai ser resolvidos se, em seguida, abra o livro de origem. Se abrir o livro de origem em primeiro lugar, deverá ver sem códigos de erro.

  • Converter um intervalo numa tabela e uma tabela num intervalo    Quando converte uma tabela num intervalo, todas as referências de célula mudam para as referências de estilo A1 absolutas e equivalentes. Quando converte um intervalo numa tabela, o Excel não altera automaticamente quaisquer referências de célula deste intervalo para as respetivas referências estruturadas equivalentes.

  • Desativar cabeçalhos de coluna    Pode mostrar ou ocultar tabela cabeçalhos de coluna e desativar a partir do > de separador de estrutura de tabela Linha de cabeçalho. Se desativar os cabeçalhos de coluna de tabela, referências estruturadas que utilize nomes de coluna não são afetadas e pode utilizá-los em fórmulas. Referências que se referem diretamente aos cabeçalhos da tabela estruturadas (por exemplo, = Vendasdepart [[#Headers], [% Comissão]]) irá resultar em #REF.

  • Adicionar ou eliminar colunas e linhas na tabela    Uma vez que muitas vezes alterar intervalos de dados de tabela, referências de célula para as referências estruturadas movem-se automaticamente. Por exemplo, se utilizar o nome de uma tabela numa fórmula para contar o número de todas as células de dados numa tabela e, em seguida, adicione uma linha de dados, a referência de célula é ajustado automaticamente.

  • Mudar o nome de uma tabela ou coluna    Se mudar o nome a uma coluna ou tabela, o Excel muda automaticamente a utilização do cabeçalho dessa tabela ou coluna em todas as referências estruturadas utilizadas no livro.

  • Mover, copiar e preencher referências estruturadas    Todas as referências estruturadas permanecem inalteradas quando copia ou move uma fórmula que utiliza uma referência estruturada.

    Nota: Copiar uma referência estruturada e preencher uma referência estruturada não é a mesma coisa. Quando copia, todas as referências estruturadas permanecem iguais, mas quando preenche uma fórmula, as referências estruturadas totalmente qualificadas ajustam os especificadores de colunas como uma série, conforme resumido na tabela seguinte.

Se a direção de preenchimento for:

E, ao preencher, premir:

Acontecerá o seguinte:

Para cima ou para baixo

Nada

Não existe nenhum ajuste de especificadores de colunas.

Para cima ou para baixo

Ctrl

Os especificadores de colunas ajustam-se como uma série.

Para a direita ou para a esquerda

Nada

Os especificadores de colunas ajustam-se como uma série.

Para cima, baixo, direita ou esquerda

Shift

Em vez de substituir valores nas células atuais, os valores das células atuais são movidos e são inseridos especificadores de colunas.

Precisa de mais ajuda?

Pode sempre perguntar a um especialista na Comunidade Tecnológica do Excel, obter suporte na Comunidade de Respostas ou sugerir uma nova funcionalidade ou melhoria no UserVoice do Excel.

Tópicos Relacionados

Descrição geral de tabelas do Excel
vídeo: criar e formatar uma tabela do Excel
Total os dados numa tabela do Excel
Formatar uma tabela do Excel
redimensionar uma tabela adicionando ou removendo linhas e colunas
Filtrar dados num intervalo ou tabela
converter uma tabela num intervalo
problemas de compatibilidade de tabelas do Excel
Exportar uma tabela do Excel no SharePoint
descrições das fórmulas no Excel

Aumente os seus conhecimentos do Office
Explore as formações
Seja o primeiro a obter novas funcionalidades
Adira ao Office Insider

As informações foram úteis?

Obrigado pelos seus comentários!

Obrigado pelo seu feedback! Parece que poderá ser benéfico reencaminhá-lo para um dos nossos agentes de suporte do Office.

×