rezumat
Pentru a calcula prognoza versus varianța reală pe baza unui set de date, puteți utiliza funcția SUMIFS pentru a aduna totaluri și alte formule de bază pentru a calcula varianța și procentul de varianță. În exemplul prezentat, formula din G5 este:
=SUMIFS(amount,type,G$4,group,$F5)
unde suma este intervalul numit C5: C14 și tipul este intervalul numit D5: D14, iar grupul este intervalul numit B5: B14.
Explicaţie
Aceasta este o utilizare destul de standardă a funcției SUMIFS. În acest caz, trebuie să sumăm sumele pe baza a două criterii: tip (prognozat sau real) și grup. Pentru a rezuma după tip, perechea gamă / criterii este:
type,G$4
unde tipul este intervalul numit D5: D14 și G4 este o referință mixtă cu rândul blocat pentru a se potrivi cu antetul coloanei din rândul 4 atunci când formula este copiată.
Pentru a rezuma după grup, perechea interval / criterii este:
group,$F5
unde grupul este intervalul numit B5: B14 și F5 este o referință mixtă cu coloana blocată pentru a se potrivi cu numele grupurilor din coloana F atunci când formula este copiată.
Formule de varianță
Formula varianței din coloana I scade pur și simplu prognoza din real:
=G5-H5
Formula procentului de varianță din coloana J este:
=(G5-H5)/H5
cu formatul numărului procentual aplicat.
Note
- Datele prezentate aici ar funcționa bine într-un tabel Excel, care se va extinde automat pentru a include date noi. Aici folosim intervale denumite pentru a menține formulele cât mai simple posibil.
- Tabelele pivot pot fi, de asemenea, utilizate pentru a calcula varianța. Formulele oferă mai multă flexibilitate și control cu prețul unei complexități mai mari.
