Resumo
Para calcular a previsão versus variação real com base em um conjunto de dados, você pode usar a função SUMIFS para reunir totais e outras fórmulas básicas para calcular a variação e a porcentagem da variação. No exemplo mostrado, a fórmula em G5 é:
=SUMIFS(amount,type,G$4,group,$F5)
em que quantidade é o intervalo nomeado C5: C14 e tipo é o intervalo nomeado D5: D14 e grupo é o intervalo nomeado B5: B14.
Explicação
Este é um uso bastante padrão da função SUMIFS. Nesse caso, precisamos somar os valores com base em dois critérios: tipo (previsto ou real) e grupo. Para somar por tipo, o par intervalo / critério é:
type,G$4
onde tipo é o intervalo nomeado D5: D14 e G4 é uma referência mista com a linha bloqueada para corresponder ao cabeçalho da coluna na linha 4 quando a fórmula é copiada.
Para somar por grupo, o par intervalo / critério é:
group,$F5
onde grupo é o intervalo nomeado B5: B14 e F5 é uma referência mista com a coluna bloqueada para corresponder aos nomes dos grupos na coluna F quando a fórmula é copiada.
Fórmulas de variância
A fórmula de variância na coluna I simplesmente subtrai a previsão do real:
=G5-H5
A fórmula de porcentagem de variação na coluna J é:
=(G5-H5)/H5
com formato de número de porcentagem aplicado.
Notas
- Os dados mostrados aqui funcionariam bem em uma tabela do Excel, que se expandiria automaticamente para incluir novos dados. Estamos usando intervalos nomeados aqui para manter as fórmulas o mais simples possível.
- As tabelas dinâmicas também podem ser usadas para calcular a variação. As fórmulas fornecem mais flexibilidade e controle ao custo de mais complexidade.
