4 métodos eficazes para utilizar referências estruturadas no Microsoft Excel

O uso do Excel geralmente se concentra em identificar relacionamentos entre diferentes conjuntos de dados. No entanto, ao inserir fórmulas complexas, o uso excessivo de referências de células explícitas relativas e absolutas — como “B7” e suas variações — pode levar a uma barra de fórmulas desorganizada e caótica.

Utilizar referências estruturadas no Excel pode aprimorar esse processo permitindo que você atribua nomes a tabelas e seus cabeçalhos. Esses nomes designados servem como referências de células implícitas, permitindo que o Excel recupere e calcule automaticamente os dados estruturados.

Abaixo estão vários métodos eficazes para empregar referências estruturadas no Excel.

1. Executando cálculos dentro de tabelas

Referências estruturadas funcionam exclusivamente com tabelas, então o método ideal para utilizá-las é dentro desses tipos de tabelas.

Por exemplo, crie uma tabela simples variando de B2 a F8 contendo informações de vendas para uma loja. Certifique-se de rotular a tabela como “Vendas” (marque o “Nome da Tabela” no canto superior esquerdo).

Em seguida, calcularemos o total de cada venda individual:

Etapa 1: Selecione a célula F2 (garantindo que o ícone suspenso não seja clicado). Navegue até “Home”, depois “Insert” e escolha “Insert Table Columns to the Right” para incluir automaticamente uma nova coluna na tabela.

Etapa 2: Designe o cabeçalho da nova coluna G como “Total”.

Etapa 3: Na célula G3, digite =[@PricePerUnit]*[@Quantity]e pressione Enter. Ajuste a formatação da célula conforme necessário.

As referências “[@PricePerUnit]” e “[@Quantity]” correspondem aos seus respectivos campos dentro das colunas. O símbolo “@” precedendo os nomes das colunas indica que cada célula de resultado derivará seus valores da mesma linha da tabela.

Para esclarecer, a fórmula =[@PricePerUnit]*[@Quantity]em G3 corresponde efetivamente a =$C3*$D3.

2. Recuperando um intervalo além da tabela

Quando você pretende utilizar uma referência estruturada em uma célula que fica fora da tabela, você deve iniciar a referência com o nome da tabela. Por exemplo, em nosso exemplo anterior, empregar “Sales[Total]” recuperará todo o intervalo de valores sob a coluna “Total” da tabela “Sales”. Isso fornece uma matriz de múltiplos valores para manipulação posterior.

Veja como isso aparece no Excel, especificamente na célula I3, onde há bastante espaço para o intervalo se expandir para baixo.

3. Somando e somando parcialmente uma coluna

Para uma soma rápida de uma coluna inteira, aproveite a caixa de seleção “Total Row” localizada nas configurações de “Table Design” (dentro de “Table Style Options”). Veja como obter os totais para as colunas “Quantity” e “Total”.

Embora a linha ‘Total’ não possa ser realocada e seja posicionada consistentemente no final da tabela (permitindo entradas adicionais), você pode duplicar seu resultado em outro lugar:

Para calcular o total de cada entrada na coluna “Total”, use =SUM(Sales[Total]).
Para obter a soma somente de entradas visíveis, como aquelas que aparecem após filtrar a tabela, aplique =SUBTOTAL(109,Sales[Total]). Esta fórmula espelha o que a opção “Total Row” em Table Format faz para sua linha.

Você também pode calcular uma soma parcial com base em um critério específico encontrado dentro da tabela sem precisar formatá-la. Por exemplo:

Para encontrar o total de vendas atribuído a Mike, use =SUMIF(Sales[Seller]," Mike" ,Sales[Total]). Aqui, “Mike” é uma string inserida manualmente.
Para somar todos os produtos representados pelo Product ID “41230,” você usaria =SUMIF(Sales[ProductID],41230,Sales[Total]). Observe que, como a coluna ProductID é formatada como “General,” você pode inserir o número diretamente.

4. Implementando Validação de Dados de uma Tabela com INDIRECT

Supondo que você esteja trabalhando com a tabela Sales mencionada anteriormente, você pode estabelecer configurações personalizadas de Data Validation para facilitar a busca mais fácil pelos dados. Vamos criar uma tabela menor que permita seleções para Product IDs, Dates ou Sellers, permitindo que você escolha um único item desses conjuntos para exibir o subtotal.

Etapa 1: Na célula B13, configure Validação de Dados (guia Dados > Ferramentas de Dados > Validação de Dados).

Validação de dados de uma tabela via INDIRETO 1

Etapa 2: Na janela pop-up que aparece, selecione “List” no menu suspenso “Allow” e insira os valores da coluna manualmente no campo “Source”, separados por vírgulas. Para este exemplo, inserimos “ProductID, Seller, Date”.

Validação de dados de uma tabela via INDIRETO 2

Etapa 3: Na célula C13, estabeleça outra instância de Validação de Dados. Novamente, selecione “Lista”. Para “Fonte”, inclua a fórmula: =INDIRECT("Sales["&B13&"]").