Este tutorial explica os fundamentos das referências estruturadas do Excel e compartilha alguns truques sobre como usá-las em fórmulas no nosso dia a dia.
Um dos recursos mais úteis das tabelas do Excel são as referências estruturadas. Quando você acaba de se deparar com uma sintaxe especial para tabelas de referência, pode parecer chata e confusa, mas depois de experimentar um pouco, você vê que não é nada disso.
Referência estruturada no Excel
Uma referência estruturada, ou referência de tabela, é uma forma especial de fazer referência a tabelas e suas partes, que usa uma combinação de nomes de tabelas e colunas em vez de endereços de células.
Essa sintaxe especial é necessária porque as tabelas do Excel são muito poderosas e resilientes, então as referências de células normais não podem se ajustar dinamicamente à medida que os dados são adicionados ou removidos de uma tabela.
Por exemplo, para somar os valores nas células B2: B5, usa-se a função SOMA com uma referência de intervalo usual:
= SOMA (B2: B5)
Para somar os números na coluna “Vendas” da Tabela 1, usamos uma referência estruturada:
= Soma (Tabela1 [Vendas])
Principais recursos de referências estruturadas
Em comparação com a referência de célula padrão, a referência de tabela têm recursos mais avançados.
Facilmente criado
Para adicionar referências estruturadas à fórmula, basta selecionar as células da tabela às quais deseja fazer referência. O conhecimento de uma sintaxe especial não é necessário.
Resiliente e atualizada automaticamente
Quando se renomeia uma coluna, as referências são atualizadas automaticamente com um novo nome e a fórmula não é quebrada. Além disso, conforme adicionado novas linhas à tabela, elas são imediatamente incluídas nas referências existentes e as fórmulas calculam o conjunto completo de dados.
Portanto, sejam quais forem as manipulações que você fizer com as tabelas do Excel, não será necessário se preocupar em atualizar as referências estruturadas.
Pode ser usado dentro e fora da tabela
As referências estruturadas podem ser usadas em fórmulas dentro e fora de uma tabela do Excel, o que facilita a localização de tabelas em grandes pastas de trabalho.
Preenchimento automático da fórmula
Para realizar o mesmo cálculo em cada linha da tabela, basta inserir uma fórmula em apenas uma célula. Todas as outras células dessa coluna são preenchidas automaticamente.
Como criar uma referência estruturada no Excel
Fazer uma referência estruturada no Excel é muito fácil e intuitivo.
Se estiver trabalhando com intervalos, converta-os primeiro em uma tabela do Excel. Para isso, selecione todos os dados e pressione Ctrl + T. Para mais informações, consulte Como criar uma tabela no Excel.
Para criar uma referência estruturada, é preciso fazer o seguinte:
- Comece a digitar uma fórmula como de costume, inserindo o sinal de igualdade (=).
- Quando se trata da primeira referência, selecione a célula ou intervalo de células correspondentes na tabela. O Excel selecionará os nomes das colunas e criará uma referência estruturada automaticamente.
- Digite o parêntese de fechamento e pressione Enter. Se a fórmula for criada dentro da tabela, o Excel preencherá automaticamente a coluna inteira com a mesma fórmula.
Como exemplo, vamos somar a venda de 3 meses em cada linha da tabela de amostra, chamada “Vendas”. Para isso, digite: = SOMA (.
Em E2, selecione B2: D2, digite o parêntese de fechamento e pressione Enter:
Como resultado, toda a coluna E é preenchida automaticamente com esta fórmula:
= SOMA (Vendas [@ [Jan]: [Mar]])
Embora a fórmula seja a mesma, os dados são calculados em cada linha individualmente. Para entender a mecânica interna, dê uma olhada na sintaxe de referência da tabela.
Se você estiver inserindo uma fórmula fora da tabela e essa fórmula exigir apenas um intervalo de células, a maneira mais rápida de fazer uma referência estruturada é:
- Após o parêntese de abertura, comece a digitar o nome da tabela. Conforme você digita a primeira letra, o Excel mostra todos os nomes correspondentes. Se necessário, digite mais algumas letras para restringir a lista.
- Use as teclas de seta para selecionar o nome da tabela na lista.
- Clique duas vezes no nome selecionado ou pressione a tecla Tab para adicioná-lo à sua fórmula.
- Digite o parêntese de fechamento e pressione Enter.
Por exemplo, para encontrar o maior número na tabela, digite a fórmula MÁXIMO. Após o parêntese de abertura digite “ve”, selecione a tabela Vendas na lista e, pressione Tab ou clique duas vezes no nome.
Como resultado, temos:
= MÁXIMO (Vendas)
Sintaxe de referência estruturada
Como disse anteriormente, não é necessário conhecer a sintaxe das referências estruturadas para incluí-las nas fórmulas, no entanto, isso ajuda a entender o que cada fórmula está realmente fazendo.
Normalmente, uma referência estruturada é representada por uma string que começa com um nome de tabela e termina com um especificador de coluna.
Como exemplo, vamos decompor um fórmula que soma os total das colunas Sul e Norte na tabela denominada Regiões:
A referência inclui três componentes:
- Nome da tabela
- Especificador de item
- Especificadores de coluna
Para ver quais células são realmente calculadas, selecione a célula da fórmula e clique em qualquer lugar na barra de fórmulas. O Excel irá destacar as células da tabela referenciada:
Nome da tabela
O nome da tabela faz referência apenas aos dados da tabela, sem linha de cabeçalho ou total de linhas. Pode ser um nome de tabela padrão como Tabela1 ou um nome personalizado como Regiões. Para dar um nome personalizado a tabela, siga estas etapas.
Se sua fórmula estiver localizada dentro da tabela a que se refere, o nome da tabela geralmente é omitido porque está implícito.
Especificador de coluna
O especificador de coluna faz referência aos dados na coluna correspondente, sem a linha de cabeçalho e a linha de totais. Um especificador de coluna é representado pelo nome da coluna entre colchetes, por exemplo, [Sul].
Para se referir a mais de uma coluna junta, use o operador de intervalo como [[Sul]: [Leste]].
Especificador de item
Para se referir a partes específicas de uma tabela, você pode usar qualquer um dos seguintes especificadores:
| Especificador de item | Refere-se a |
|---|---|
| [#Tudo] | A tabela inteira, incluindo dados da tabela, cabeçalhos e linha de totais |
| [#Dados] | As linhas de dados |
| [#Cabeçalhos] | A linha do cabeçalho (cabeçalhos das colunas) |
| [#Totais] | A linha de totais. Se não houver, ele retorna nulo |
Observe que o sinal de hashtag (#) é usado com todos os especificadores de item, exceto a linha atual. Para se referir às células na mesma linha em que você insere a fórmula, o Excel usa o caractere @ seguido do nome da coluna.
Por exemplo, para adicionar números nas colunas Sul e Sudeste da linha atual, deve ser usado esta fórmula:
= SOMA (Regiões [@Sul]; [@Sudeste])
Operadores de referência estruturados
Os operadores a seguir permitem combinar diferentes especificadores e adicionar ainda mais flexibilidade às suas referências estruturadas.
Operador de intervalo (dois pontos)
Como acontece com as referências de intervalo normal, usa-se dois-pontos (:) para se referir a duas ou mais colunas adjacentes em uma tabela.
Por exemplo, a fórmula abaixo soma os números em todas as colunas entre Sul e Nordeste.
= SOMA (Regiões [[Sul]: [Nordeste]])
Operador de união (ponto e vírgula)
Para se referir as colunas não adjacentes, separe os especificadores de coluna com ponto e vírgula.
Por exemplo, veja como podemos somar as linhas de dados nas colunas Sul e Sudeste.
= SOMA (Regiões [Sul]; Regiões [Sudeste])
Operador de interseção (espaço)
É usado para se referir a uma célula na interseção de uma linha e coluna específica.
Por exemplo, para retornar um valor na interseção da linha Total e da coluna Norte, use esta referência:
= Regiões [#Totais] Regiões [[#Tudo]; [Norte]]
Observe que o especificador [#Tudo] é necessário neste caso porque o especificador de coluna não inclui a linha total. Sem ele, a fórmula retornaria #NULO.
Regras de sintaxe para referência de tabela
Para editar ou fazer referências estruturadas manualmente, siga este passo a passo:
1. Coloque especificadores entre colchetes
Todos os especificadores de coluna e item especial devem ser colocados entre [colchetes].
Um especificador que contém outros especificadores deve ser colocado entre colchetes externos. Por exemplo, Regiões [[Sul]: [Nordeste]].
2. Separe especificadores internos com ponto e vírgula
Se um especificador contém dois ou mais especificadores internos, esses especificadores internos precisam ser separados por ponto e vírgula.
Por exemplo, para retornar o cabeçalho da coluna Sul, deve ser digitado um ponto e vírgula entre [#Cabeçalhos] e [Sul] para incluir toda a construção em um conjunto adicional de colchetes:
= Regiões [[#Cabeçalhos], [Sul]]
3. Não use aspas nos cabeçalhos das colunas
Nas referências de tabelas, os cabeçalhos das colunas não exigem aspas, sejam eles texto, números ou datas.
4. Use aspas simples para alguns caracteres especiais nos cabeçalhos das colunas
Em referências estruturadas, alguns caracteres como colchetes esquerdo e direito, sustenido (#) e aspas simples (‘) têm um significado especial. Se qualquer um dos caracteres acima for incluído em um cabeçalho de coluna, uma aspa simples deve ser usada antes desse caractere em um especificador de coluna.
Por exemplo, para o cabeçalho da coluna “Item #”, o especificador é [Item ‘#].
5. Use espaços para tornar as referências estruturadas mais legíveis
Para melhorar a legibilidade das referências da tabela, você pode inserir espaços entre os especificadores. Normalmente, é considerado uma boa prática usar espaços depois do ponto e vírgula. Por exemplo:
= MÉDIA (Regiões [Sul], Regiões [Sudeste], Regiões [Norte])
Conclusão
Neste artigo vimos como fazer referência estruturada no Excel. Recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas e fórmulas do Excel para que no futuro você possa se tornar um usuário avançado.
O site deste pessoal aqui foi referência para este artigo. O site é muitoooo bacana com várias dicas legais para Excel, mas infelizmente o conteúdo está em inglês, o que não deixa de ser uma boa referência para quem conseguir acompanhar. Eu recomendo.
Portanto, continue lendo mais sobre Excel Básico aqui.
