Motor Dinâmico de KPI
Deslize para mostrar o menu
A pasta de trabalho já oferece suporte para relatórios, modelagem de cenários e análise de sensibilidade. Neste capítulo, combine esses sistemas em um mecanismo centralizado de KPIs, onde todas as saídas respondem dinamicamente às entradas do usuário.
Estrutura do Mecanismo de KPIs
Um sistema dinâmico de KPIs separa a pasta de trabalho em três camadas:
- Entradas: seleções do usuário;
- Lógica: cálculos e fórmulas;
- Saídas: KPIs exibidos.
Essa estrutura mantém o modelo escalável e fácil de controlar.
Estrutura do SUMIFS e COUNTIFS
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
=COUNTIFS(criteria_range1, criteria1, ...)
Essas funções agregam valores dinamicamente com base nos filtros selecionados.
Lógica do FILTER, MAX e XLOOKUP
Os cálculos de produto principal combinam várias funções:
FILTER → MAX → XLOOKUP
FILTER: isola as linhas correspondentes;MAX: identifica o maior valor;XLOOKUP: recupera o rótulo relacionado.
Dentro da planilha Summary, crie uma nova seção:
Dynamic KPI Panel
Construa a seguinte estrutura de entrada:
| Entrada | Fonte |
|---|---|
| Active Scenario | Scenarios!B7 |
| Selected Region | Reference_Lists dropdown |
| Selected Month | Whole number (1–12) |
Digite:
=SUMIFS(Sales_Data[Revenue],Sales_Data[Region],[SelectedRegion],Sales_Data[Month],[SelectedMonth])
Sales_Data[Region]: filtro de região;Sales_Data[Month]: filtro de mês.
O KPI agora responde dinamicamente a ambas as seleções.
Digite:
=COUNTIFS(Sales_Data[Region],[SelectedRegion],Sales_Data[Month],[SelectedMonth])
O KPI retorna o número de transações correspondentes.
Digite:
=IFERROR([FilteredRevenue]/[FilteredOrders],0)
[FilteredRevenue]: KPI de Receita filtrada;[FilteredOrders]: contagem de pedidos filtrados;0: valor padrão quando não existem pedidos.
Digite:
=XLOOKUP(MAX(FILTER(Sales_Data[Revenue],Sales_Data[Region]=[SelectedRegion])),Sales_Data[Revenue],Sales_Data[Product],"No data")
FILTER(...): isola a região selecionada;MAX(...): identifica o maior valor de Receita;XLOOKUP(...): retorna o produto correspondente.
Digite:
=SUMIFS(Sales_Data[Revenue],Sales_Data[Region],[SelectedRegion],Sales_Data[Month],[SelectedMonth]) * Scenarios!Active Price Multiplier * Scenarios!Active Volume Multiplier
- Receita base: saída transacional filtrada;
- Multiplicador de preço: ajuste do cenário ativo;
- Multiplicador de volume: ajuste do cenário ativo.
O KPI agora modela a Receita projetada dinamicamente.
Altere:
- Região;
- Mês;
- Cenário.
Confirme que:
- A Receita é atualizada automaticamente;
- As contagens de pedidos são atualizadas automaticamente;
- O Valor Médio do Pedido é atualizado automaticamente;
- O Produto Principal é atualizado automaticamente;
- A Receita Projetada é recalculada instantaneamente.
1. Qual é o principal objetivo de um mecanismo de KPIs?
2. Por que o IFERROR é utilizado em cálculos de KPI como Valor Médio do Pedido?
3. Por que o FILTER é utilizado dentro do cálculo de Produto Top?
Obrigado pelo seu feedback!
Pergunte à IA
Pergunte à IA
Pergunte o que quiser ou experimente uma das perguntas sugeridas para iniciar nosso bate-papo
