Sažetak
Da biste izračunali prognozu naspram stvarne varijance na temelju skupa podataka, možete koristiti funkciju SUMIFS za prikupljanje ukupnih vrijednosti i osnovne druge formule za izračunavanje varijance i postotka varijance. U prikazanom primjeru formula u G5 je:
=SUMIFS(amount,type,G$4,group,$F5)
gdje je iznos imenovani raspon C5: C14, a vrsta imenovani raspon D5: D14, a grupa je imenovani raspon B5: B14.
Obrazloženje
Ovo je prilično standardna upotreba funkcije SUMIFS. U ovom slučaju moramo zbrojiti iznose na temelju dva kriterija: vrste (prognoze ili stvarne) i grupe. Za sumiranje prema tipu, par raspona / kriterija je:
type,G$4
gdje je tip imenovani raspon D5: D14, a G4 je mješovita referenca s zaključanim retkom kako bi se podudaralo sa zaglavljem stupca u retku 4 kada se formula kopira dolje.
Za sumiranje po skupinama, par raspona / kriterija je:
group,$F5
gdje je grupa imenovani raspon B5: B14, a F5 je mješovita referenca sa zaključanim stupcem kako bi se podudarala s imenima grupa u stupcu F kada se formula kopira preko.
Formule varijance
Formula varijance u stupcu I jednostavno oduzima prognozu od stvarne:
=G5-H5
Formula postotka varijance u stupcu J je:
=(G5-H5)/H5
s primijenjenim formatom broja postotaka.
Bilješke
- Ovdje prikazani podaci dobro bi funkcionirali u Excel tablici, koja bi se automatski proširila i uključivala nove podatke. Ovdje koristimo imenovane opsege kako bi formule bile što jednostavnije.
- Zaokretne tablice također se mogu koristiti za izračunavanje varijance. Formule pružaju veću fleksibilnost i kontrolu po cijenu složenosti.
