Kopsavilkums
Lai aprēķinātu prognozi pret faktisko dispersiju, pamatojoties uz datu kopu, varat izmantot funkciju SUMIFS, lai apkopotu kopsummu, un citas pamata formulas, lai aprēķinātu dispersiju un dispersijas procentus. Parādītajā piemērā formula G5 ir:
=SUMIFS(amount,type,G$4,group,$F5)
kur summa ir nosauktais diapazons C5: C14 un tips ir nosauktais diapazons D5: D14, un grupa ir nosauktais diapazons B5: B14.
Paskaidrojums
Tas ir diezgan standarta funkcijas SUMIFS lietojums. Šajā gadījumā mums jāapkopo summas, pamatojoties uz diviem kritērijiem: veidu (prognozējamo vai faktisko) un grupu. Apkopojot pēc veida, diapazona / kritēriju pāris ir:
type,G$4
kur tips ir nosauktais diapazons D5: D14, un G4 ir jaukta atsauce ar bloķētu rindu, lai atbilstu kolonnas galvenei 4. rindā, kad formula tiek nokopēta uz leju.
Apkopojot pa grupām, diapazona / kritēriju pāris ir:
group,$F5
kur grupa ir nosauktais diapazons B5: B14, un F5 ir jaukta atsauce ar kolonnu, kas ir bloķēta, lai grupu nosaukumi sakristu F kolonnā, kad formula tiek kopēta.
Dispersijas formulas
Dispersijas formula I kolonnā vienkārši atņem prognozi no faktiskās:
=G5-H5
J kolonnas dispersijas procentuālā formula ir:
=(G5-H5)/H5
izmantojot procentuālo skaitļu formātu.
Piezīmes
- Šeit parādītie dati labi darbotos Excel tabulā, kas automātiski paplašināsies, iekļaujot jaunus datus. Mēs izmantojam nosauktos diapazonus, lai formulas būtu pēc iespējas vienkāršākas.
- Variantu aprēķināšanai var izmantot arī rakurstabulas. Formulas nodrošina lielāku elastību un kontroli par sarežģītākas izmaksas.
