Utilizar referências estruturadas com tabelas do Excel

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 na 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:

• Utilize carateres válidos  Comece sempre o nome com uma letra, um caráter de sublinhado (_) ou uma barra invertida (\). Utilize letras, números, pontos finais e carateres de sublinhado para o resto do nome. Não é possível utilizar "C", "c", "L" ou "l", porque estes já se encontram designados como atalhos para selecionar a coluna ou linha da célula ativa quando os introduz na caixa Nome ou na caixa Ir Para.

• Não utilize referências de células  Os 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  Os espaços não podem ser utilizados no nome. Pode utilizar o caráter de sublinhado (_) e o ponto final (.) como separadores de palavras. Por exemplo, DeptVendas, Imposto_Sobre_Vendas ou Primeiro.Trimestre.

• 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 objeto  Se planeia ter uma combinação de tabelas, tabelas dinâmicas e gráficos, é aconselhável atribuir um prefixo aos 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. Esta ação mantém todos os seus nomes numa lista ordenada no Gestor de Nomes.

Regras de sintaxe de referências estruturadas

Também pode introduzir ou alterar manualmente as referências estruturadas na fórmula, mas para tal, ajudará a compreender a sintaxe de referência estruturada. Em seguida, apresentamos-lhe a seguinte fórmula de exemplo:

=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 personalizado. Referencia os dados de tabela, sem cabeçalhos ou linhas totais. Pode utilizar o nome de tabela predefinido, como Tabela1 ou alterá-lo para um nome personalizado.

• O especificador de colunas:    [Montante de Vendas] e [Montante da Comissão] são especificadores de colunas que utilizam os nomes das colunas que representam. Estes referenciam os dados de coluna, sem cabeçalhos de coluna ou linhas totais. Coloque sempre os especificadores entre parênteses, tal como apresentado.

• O especificador    de itens:[#Totals] e [#Data] são especificadores de itens especiais que fazem referência a partes específicas da tabela, como a linha 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:

• Colocar os especificadores entre parênteses    Todos os especificadores de tabelas, colunas e itens especiais têm de estar entre parênteses retos correspondentes ([ ]). Um especificador que contenha outros especificadores necessita de parênteses retos correspondentes externos para delimitar os parênteses retos correspondentes internos dos outros especificadores. Por exemplo: =DeptVendas[[Vendedor]:[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 (/)

• A assinar (@)

• Barra invertida (\)

• Ponto de exclamação (!)

• Parêntese esquerdo (()

• Parêntese direito ())

• Sinal de percentagem (%)

• Ponto de interrogação (?)

• Acento anterior (')

• Ponto e vírgula (;)

• Til (~)

• Caráter de sublinhado (_)

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

Eis a lista de carateres especiais que precisam de um caráter de escape (') na fórmula:

• Parêntese reto esquerdo ([)

• Parêntese reto direito (])

• Sinal de libra (#)

• Plica (')

• A assinar (@)

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.

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.

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:

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.

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.

• Decidir se pretende gerar referências estruturadas para tabelas em semi-seleções    Por predefinição, quando cria uma fórmula, clicar num intervalo de células numa tabela seleciona as células e introduz automaticamente uma referência estruturada em vez do intervalo de células na fórmula. Este comportamento de seleção parcial facilita substancialmente a introdução de uma referência estruturada. Pode ativar ou desativar este comportamento ao selecionar ou desmarcar a caixa de verificação Utilizar nomes de tabela em fórmulas na caixa de diálogo Opções de > de Ficheiros > Fórmulas > Trabalhar com fórmulas.

• Utilizar livros com ligações externas para tabelas do Excel noutros livros    Se um livro contiver uma ligação externa para uma tabela do Excel noutro livro, esse livro de origem ligado tem de estar aberto no Excel para evitar erros de #REF! no livro de destino que contém as ligações. Se abrir primeiro o livro de destino e forem apresentados erros #REF! , estes serão resolvidos se, em seguida, abrir o livro de origem. Se abrir primeiro o livro de origem, não deverá ver 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 os cabeçalhos de coluna    Pode ativar e desativar os cabeçalhos de coluna da tabela no separador Estrutura da tabela > Linha de Cabeçalho. Se desativar os cabeçalhos de coluna da tabela, as referências estruturadas que utilizam nomes de coluna não são afetadas e pode utilizá-las em fórmulas. As referências estruturadas que se referem diretamente aos cabeçalhos de tabela (por exemplo, =DeptVendas[[#Headers],[%Comissão]]) resultarão em #REF.

• Adicionar ou eliminar colunas e linhas à tabela    Uma vez que os intervalos de dados da tabela mudam frequentemente, as referências de células para referências estruturadas ajustam-se automaticamente. Por exemplo, se utilizar o nome de uma tabela numa fórmula para contar todas as células de dados numa tabela, e se adicionar uma linha de dados, a referência de célula ajusta-se 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.

Precisa de mais ajuda?

Pode sempre colocar uma pergunta a um especialista da Comunidade Tecnológica do Excel ou obter suporte nas Comunidades.

Tópicos Relacionados

Descrição geral das tabelas
do ExcelVídeo: Criar e formatar uma tabela
do ExcelTotalizar os dados numa tabela
do ExcelFormatar uma tabela
do ExcelRedimensionar uma tabela ao adicionar ou remover linhas e colunas
Filtrar dados num intervalo ou tabela
Converter uma tabela num intervalo
Problemas de compatibilidade de tabelas
do ExcelExportar uma tabela do Excel para o SharePoint
Descrição geral das fórmulas no Excel