Zhrnutie
Ak chcete vypočítať predpoveď oproti skutočnej odchýlke na základe súboru údajov, môžete použiť funkciu SUMIFS na zhromaždenie súčtov a základné ďalšie vzorce na výpočet odchýlky a percenta odchýlky. V zobrazenom príklade je vzorec v G5:
=SUMIFS(amount,type,G$4,group,$F5)
kde suma je pomenovaný rozsah C5: C14 a typ je pomenovaný rozsah D5: D14 a skupina je pomenovaný rozsah B5: B14.
Vysvetlenie
Toto je celkom štandardné použitie funkcie SUMIFS. V takom prípade musíme sumy spočítať na základe dvoch kritérií: typu (prognóza alebo skutočné) a skupiny. Súčet podľa typu je dvojica rozsah / kritériá:
type,G$4
kde typ je pomenovaný rozsah D5: D14 a G4 je zmiešaná referencia s uzamknutým riadkom, aby sa pri kopírovaní vzorca zhodovala s hlavičkou stĺpca v riadku 4.
Ak to zhrnieme podľa skupiny, dvojica rozsah / kritériá je:
group,$F5
kde skupina je pomenovaný rozsah B5: B14 a F5 je zmiešaná referencia so zamknutým stĺpcom, aby sa pri kopírovaní vzorca zhodovali s názvami skupín v stĺpci F.
Variačné vzorce
Vzorec odchýlky v stĺpci I jednoducho odčíta prognózu od skutočnej:
=G5-H5
Vzorec percenta odchýlky v stĺpci J je:
=(G5-H5)/H5
s použitým formátom percentuálneho čísla.
Poznámky
- Tu zobrazené údaje by fungovali dobre v tabuľke Excel, ktorá by sa automaticky rozšírila o nové údaje. Používame tu pomenované rozsahy, aby boli vzorce čo najjednoduchšie.
- Na výpočet odchýlky možno použiť aj kontingenčné tabuľky. Vzorce poskytujú väčšiu flexibilitu a kontrolu za cenu zložitosti.
