souhrn
Chcete-li vypočítat prognózu versus skutečnou odchylku na základě sady dat, můžete pomocí funkce SUMIFS shromáždit součty a základní další vzorce pro výpočet odchylky a procenta odchylky. V zobrazeném příkladu je vzorec v G5:
=SUMIFS(amount,type,G$4,group,$F5)
kde částka je pojmenovaný rozsah C5: C14 a typ je pojmenovaný rozsah D5: D14 a skupina je pojmenovaný rozsah B5: B14.
Vysvětlení
Toto je docela standardní použití funkce SUMIFS. V tomto případě musíme částky sečíst na základě dvou kritérií: typu (předpovědi nebo skutečné) a skupiny. Chcete-li součet podle typu, pár rozsah / kritéria je:
type,G$4
kde typ je pojmenovaný rozsah D5: D14 a G4 je smíšený odkaz s uzamčeným řádkem, aby se při kopírování vzorce shodovala s hlavičkou sloupce v řádku 4.
Souhrnně podle skupiny je pár rozsah / kritéria:
group,$F5
kde skupina je pojmenovaný rozsah B5: B14 a F5 je smíšený odkaz se zamčeným sloupcem, aby se při kopírování vzorce shodovaly s názvy skupin ve sloupci F.
Variační vzorce
Vzorec odchylky ve sloupci I jednoduše odečte předpověď od skutečného:
=G5-H5
Vzorec procenta odchylky ve sloupci J je:
=(G5-H5)/H5
s použitým formátem procentního čísla.
Poznámky
- Data zobrazená zde by fungovala dobře v tabulce Excel, která by se automaticky rozšířila o nová data. Používáme zde pojmenované rozsahy, aby vzorce byly co nejjednodušší.
- K výpočtu rozptylu lze také použít kontingenční tabulky. Vzorce poskytují větší flexibilitu a kontrolu za cenu složitosti.
