Podsumowanie
Aby obliczyć prognozę względem rzeczywistej wariancji na podstawie zestawu danych, można użyć funkcji SUMA.WARUNKÓW w celu zebrania sum oraz podstawowych innych formuł do obliczenia wariancji i procentu wariancji. W pokazanym przykładzie formuła w G5 to:
=SUMIFS(amount,type,G$4,group,$F5)
gdzie kwota to nazwany zakres C5: C14, a typ to nazwany zakres D5: D14, a grupa to nazwany zakres B5: B14.
Wyjaśnienie
Jest to całkiem standardowe użycie funkcji SUMA.WARUNKÓW. W takim przypadku musimy sumować kwoty na podstawie dwóch kryteriów: rodzaju (prognozowane lub rzeczywiste) oraz grupy. Podsumowując według typu, para zakres / kryteria to:
type,G$4
gdzie typ to nazwany zakres D5: D14, a G4 to odwołanie mieszane z zablokowanym wierszem w celu dopasowania do nagłówka kolumny w wierszu 4, gdy formuła jest kopiowana w dół.
Podsumowując według grupy, para zakres / kryteria to:
group,$F5
gdzie grupa to nazwany zakres B5: B14, a F5 to odwołanie mieszane z kolumną zablokowaną w celu dopasowania nazw grup w kolumnie F, gdy formuła jest kopiowana w poprzek.
Formuły wariancji
Formuła wariancji w kolumnie I po prostu odejmuje prognozę od rzeczywistej:
=G5-H5
Formuła procentowa wariancji w kolumnie J to:
=(G5-H5)/H5
z zastosowanym formatem liczb procentowych.
Uwagi
- Przedstawione tutaj dane działałyby dobrze w tabeli programu Excel, która automatycznie rozszerzyłaby się o nowe dane. Używamy tutaj nazwanych zakresów, aby formuły były jak najprostsze.
- Tabele przestawne mogą być również używane do obliczania wariancji. Formuły zapewniają większą elastyczność i kontrolę kosztem większej złożoności.
