Резиме
Да бисте израчунали прогнозу у односу на стварну варијансу на основу скупа података, можете да користите функцију СУМИФС за прикупљање укупних вредности и основне друге формуле за израчунавање варијансе и процента варијансе. У приказаном примеру, формула у Г5 је:
=SUMIFS(amount,type,G$4,group,$F5)
где је износ именовани опсег Ц5: Ц14, а тип је именовани опсег Д5: Д14, а група је именовани опсег Б5: Б14.
Објашњење
Ово је прилично стандардна употреба функције СУМИФС. У овом случају морамо сумирати износе на основу два критеријума: врсте (прогнозе или стварне) и групе. Да сумирамо по типу, пар опсега / критеријума је:
type,G$4
где је тип именовани опсег Д5: Д14, а Г4 је мешовита референца са закључаним редом како би се подударало са заглављем колоне у реду 4 када се формула преписује.
Да сумирамо по групама, пар опсега / критеријума је:
group,$F5
где је група именовани опсег Б5: Б14, а Ф5 је мешовита референца са закључаном колоном како би се подударала са именима група у колони Ф када се формула копира преко.
Формуле варијансе
Формула варијансе у колони И једноставно одузима прогнозу од стварне:
=G5-H5
Формула процента одступања у колони Ј је:
=(G5-H5)/H5
са примењеним форматом процентуалног броја.
Напомене
- Овде приказани подаци добро би функционисали у Екцел табели, која би се аутоматски проширила и укључивала нове податке. Овде користимо именоване опсеге како би формуле биле што једноставније.
- Пивот табеле се такође могу користити за израчунавање варијансе. Формуле пружају већу флексибилност и контролу по цену сложености.
