Sammanfattning
För att beräkna prognos kontra faktisk varians baserat på en uppsättning data kan du använda kan använda SUMIFS-funktionen för att samla upp totalsummor och grundläggande andra formler för att beräkna varians- och variansprocent. I exemplet som visas är formeln i G5:
=SUMIFS(amount,type,G$4,group,$F5)
där mängden är det namngivna området C5: C14, och typen är det namngivna området D5: D14, och gruppen är det namngivna området B5: B14.
Förklaring
Detta är en ganska vanlig användning av SUMIFS-funktionen. I det här fallet måste vi summera belopp baserat på två kriterier: typ (prognos eller faktisk) och grupp. För att summera efter typ är intervallet / kriterieparet:
type,G$4
där typen är det namngivna intervallet D5: D14 och G4 är en blandad referens med raden låst för att matcha kolumnrubriken i rad 4 när formeln kopieras.
För att summera per grupp är intervallet / kriterieparet:
group,$F5
där gruppen är det namngivna området B5: B14 och F5 är en blandad referens med kolumnen låst för att matcha gruppnamn i kolumn F när formeln kopieras över.
Variansformler
Variansformeln i kolumn I subtraherar helt enkelt prognosen från faktiska:
=G5-H5
Formel för variansprocent i kolumn J är:
=(G5-H5)/H5
med tillämpat procenttal.
Anteckningar
- Data som visas här fungerar bra i en Excel-tabell, som automatiskt utvidgas till att innehålla nya data. Vi använder namngivna intervall här för att hålla formlerna så enkla som möjligt.
- Pivottabeller kan också användas för att beräkna varians. Formler ger mer flexibilitet och kontroll på bekostnad av mer komplexitet.
