Aprenda Buscas Dinâmicas e Orientadas por Parâmetros | Sistemas Avançados de Busca e Modelagem Relacional

Deslize para mostrar o menu

A pasta de trabalho já oferece suporte a buscas relacionais e relatórios dinâmicos. Neste capítulo, construa resumos por categoria e introduza lógica orientada por parâmetros que altera os cálculos dinamicamente com base em cenários selecionados pelo usuário.

Estrutura do SUMPRODUCT

=SUMPRODUCT(array1 * array2 * ...)

array1: primeiro array de cálculo;
array2: segundo array de cálculo;
TRUE: convertido em 1;
FALSE: convertido em 0.

Isso permite que condições lógicas e agregações ocorram dentro de uma única fórmula.

Estrutura do INDIRECT

=INDIRECT(ref_text, [a1])

ref_text: texto convertido em uma referência ativa;
[a1]: argumento opcional de estilo de referência.

INDIRECT permite que fórmulas alternem referências dinamicamente com base nos valores das células.

Passo 1 Criar o Resumo por Categoria

Na planilha Summary, adicione os seguintes cabeçalhos:

Category
Total_Revenue
Total_Cost
Total_Profit

Passo 2 Gerar a Lista de Categorias

Na célula A10, digite:

=UNIQUE(Products[Category])

A lista de categorias agora se expande automaticamente conforme novas categorias são adicionadas.

Passo 3 Calcular Receita por Categoria

Na célula B10, digite:

=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*Sales_Data[Revenue])

XLOOKUP(...): recupera os valores de categoria para cada produto;
=A10: verifica se a categoria corresponde;
Sales_Data[Revenue]: valores a serem agregados.

Arraste a fórmula para baixo na coluna.

Passo 4 Calcular Custo por Categoria

Na célula C10, digite:

=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*XLOOKUP(Sales_Data[Product],Products[Product],Products[Cost],0)*Sales_Data[Units])

A fórmula calcula dinamicamente o custo total por categoria.

Passo 5 Calcular Lucro

Na célula D10, digite:

=B10-C10

Arraste a fórmula para baixo e formate todos os valores adequadamente.

Passo 6 Criar o Seletor de Cenário

Na planilha Summary, crie uma célula para:

Active Pricing Scenario

Aplique Validação de Dados usando as seguintes opções:

Pricing_Tiers
Pricing_Tiers_Promo

Passo 7 Criar a Busca Dinâmica de Desconto

Na planilha Sales_Data, substitua a fórmula de desconto anterior por:

=XLOOKUP([@Units],INDIRECT(Summary!$F$9&"[Min_Units]"),INDIRECT(Summary!$F$9&"[Discount_Rate]"),0,-1)

Summary!$F$9: tabela de cenário selecionada;
INDIRECT(...): converte texto em referências de tabela ativas;
-1: modo de correspondência aproximada.

A busca agora alterna dinamicamente entre os cenários de precificação.

Passo 8 Testar a Troca de Cenário

Altere o valor selecionado no menu suspenso de cenários.

Confirme que:

Discount_Rate é atualizado automaticamente;
Discounted_Revenue é atualizado automaticamente;
Todos os cálculos dependentes reagem ao modelo de precificação selecionado.

Tudo estava claro?

Obrigado pelo seu feedback!

Seção 3. Capítulo 5

Pergunte à IA

Pergunte o que quiser ou experimente uma das perguntas sugeridas para iniciar nosso bate-papo

Seção 3. Capítulo 5

Buscas Dinâmicas e Orientadas por Parâmetros

Estrutura do SUMPRODUCT

Estrutura do INDIRECT

1. Qual é o papel do `SUMPRODUCT` nesta lição?

2. Por que o `INDIRECT` é utilizado em modelos orientados por parâmetros?

3. Qual é o principal benefício de usar `UNIQUE` com `SUMPRODUCT` em tabelas de resumo?

Buscas Dinâmicas e Orientadas por Parâmetros

Estrutura do SUMPRODUCT

Estrutura do INDIRECT

1. Qual é o papel do SUMPRODUCT nesta lição?

2. Por que o INDIRECT é utilizado em modelos orientados por parâmetros?

3. Qual é o principal benefício de usar UNIQUE com SUMPRODUCT em tabelas de resumo?

1. Qual é o papel do `SUMPRODUCT` nesta lição?

2. Por que o `INDIRECT` é utilizado em modelos orientados por parâmetros?

3. Qual é o principal benefício de usar `UNIQUE` com `SUMPRODUCT` em tabelas de resumo?