Santrauka
Norėdami apskaičiuoti prognozę ir faktinę dispersiją, remdamiesi duomenų rinkiniu, galite naudoti funkciją SUMIFS, kad surinktumėte sumas, ir pagrindines kitas formules, kad apskaičiuotumėte dispersiją ir dispersijos procentą. Parodytame pavyzdyje formulė G5 yra:
=SUMIFS(amount,type,G$4,group,$F5)
kur suma yra pavadintas diapazonas C5: C14, o tipas - pavadintas diapazonas D5: D14, o grupė - pavadintas diapazonas B5: B14.
Paaiškinimas
Tai gana standartinis funkcijos SUMIFS naudojimas. Tokiu atveju sumas turime susumuoti pagal du kriterijus: tipą (prognozuojamą ar faktinį) ir grupę. Apibendrinant pagal tipą, diapazono / kriterijų pora yra:
type,G$4
kur tipas yra pavadintas diapazonas D5: D14, o G4 yra mišri nuoroda su užrakinta eilute, kad atitiktų stulpelio antraštę 4 eilutėje, kai formulė nukopijuojama žemyn.
Apibendrinant pagal grupes, diapazono / kriterijų pora yra:
group,$F5
kur grupė yra pavadintas diapazonas B5: B14, o F5 yra mišri nuoroda, kai stulpelis užrakintas, kad atitiktų grupių pavadinimus F stulpelyje, kai formulė nukopijuota.
Dispersijos formulės
I stulpelio dispersijos formulė prognozę tiesiog atima iš faktinės:
=G5-H5
J stulpelio dispersijos procentinė formulė yra:
=(G5-H5)/H5
taikant procentinio skaičiaus formatą.
Pastabos
- Čia rodomi duomenys gerai veiktų „Excel“ lentelėje, kuri automatiškai išsiplėstų įtraukiant naujus duomenis. Mes naudojame įvardytus diapazonus, kad formulės būtų kuo paprastesnės.
- Skaičiuojant dispersiją taip pat galima naudoti „Pivot“ lenteles. Formulės suteikia daugiau lankstumo ir kontrolės sudėtingiau.
