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 KPI
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 valor mais alto;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 |
|---|---|
| Cenário Ativo | Scenarios!B7 |
| Região Selecionada | Dropdown Reference_Lists |
| Mês Selecionado | Número inteiro (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 de fallback 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 KPI?
2. Por que o IFERROR é utilizado em cálculos de KPI como Valor Médio do Pedido?
3. Por que o FILTER é utilizado no 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
