Resumé
For at beregne prognose versus faktisk varians baseret på et sæt data kan du bruge kan bruge SUMIFS-funktionen til at samle totaler og grundlæggende andre formler til at beregne varians og variansprocent. I det viste eksempel er formlen i G5:
=SUMIFS(amount,type,G$4,group,$F5)
hvor mængden er det navngivne område C5: C14, og typen er det navngivne område D5: D14, og gruppen er det navngivne område B5: B14.
Forklaring
Dette er en ret almindelig anvendelse af SUMIFS-funktionen. I dette tilfælde er vi nødt til at summere beløb baseret på to kriterier: type (prognose eller faktisk) og gruppe. For at opsummere efter type er intervallet / kriterieparet:
type,G$4
hvor typen er det navngivne område D5: D14, og G4 er en blandet reference med rækken låst for at matche kolonneoverskriften i række 4, når formlen kopieres ned.
For at opsummere efter gruppe er intervallet / kriterieparet:
group,$F5
hvor gruppen er det navngivne område B5: B14, og F5 er en blandet reference med kolonnen låst for at matche gruppens navne i kolonne F, når formlen kopieres over.
Variansformler
Variansformlen i kolonne I trækker simpelthen prognosen fra den faktiske:
=G5-H5
Formlen for variansprocent i kolonne J er:
=(G5-H5)/H5
med anvendt procenttalstalformat.
Bemærkninger
- Dataene her vises fungerer godt i en Excel-tabel, som automatisk udvides til at omfatte nye data. Vi bruger navngivne områder her for at holde formlerne så enkle som muligt.
- Pivottabeller kan også bruges til at beregne varians. Formler giver mere fleksibilitet og kontrol på bekostning af mere kompleksitet.
