Sammendrag
For å beregne prognose kontra faktisk varians basert på et sett med data, kan du bruke SUMIFS-funksjonen til å samle opp totaler og grunnleggende andre formler for å beregne varians og variansprosent. I eksemplet vist er formelen i G5:
=SUMIFS(amount,type,G$4,group,$F5)
hvor mengden er det navngitte området C5: C14, og typen er det navngitte området D5: D14, og gruppen er det navngitte området B5: B14.
Forklaring
Dette er en ganske standard bruk av SUMIFS-funksjonen. I dette tilfellet må vi summere beløp basert på to kriterier: type (prognose eller faktisk) og gruppe. For å oppsummere etter type er rekkevidden / kriterieparet:
type,G$4
hvor typen er det navngitte området D5: D14, og G4 er en blandet referanse med raden låst for å matche kolonneoverskriften i rad 4 når formelen kopieres ned.
For å oppsummere etter gruppe er rekkevidden / kriterieparet:
group,$F5
hvor gruppen er det navngitte området B5: B14, og F5 er en blandet referanse med kolonnen låst for å matche gruppenavn i kolonne F når formelen kopieres over.
Avviksformler
Avviksformelen i kolonne I trekker ganske enkelt prognosen fra faktisk:
=G5-H5
Variansprosentformelen i kolonne J er:
=(G5-H5)/H5
med anvendt tallformatformat.
Merknader
- Dataene som vises her vil fungere bra i en Excel-tabell, som automatisk utvides til å omfatte nye data. Vi bruker navngitte områder her for å holde formlene så enkle som mulig.
- Pivottabeller kan også brukes til å beregne avvik. Formler gir mer fleksibilitet og kontroll på bekostning av mer kompleksitet.
