Обобщение
За да изчислите прогноза спрямо действителната дисперсия въз основа на набор от данни, можете да използвате можете да използвате функцията SUMIFS за събиране на общи суми и основни други формули за изчисляване на дисперсия и процент на дисперсия. В показания пример формулата в G5 е:
=SUMIFS(amount,type,G$4,group,$F5)
където сумата е наименуваният диапазон C5: C14, а типът е наименуваният диапазон D5: D14, а групата е наименуваният диапазон B5: B14.
Обяснение
Това е доста стандартно използване на функцията SUMIFS. В този случай трябва да сумираме суми въз основа на два критерия: тип (прогнозен или действителен) и групов. За да сумирате по тип, двойката обхват / критерии е:
type,G$4
където type е наименованият диапазон D5: D14, а G4 е смесена препратка със заключения ред, за да съответства на заглавката на колоната в ред 4, когато формулата се копира надолу.
За сумиране по групи двойката обхват / критерии е:
group,$F5
където групата е наименуваният диапазон B5: B14, а F5 е смесена препратка със заключената колона, за да съответства на имената на групи в колона F, когато формулата се копира.
Формули за дисперсия
Формулата на дисперсията в колона I просто изважда прогнозата от действителната:
=G5-H5
Формулата за процент на отклонение в колона J е:
=(G5-H5)/H5
с приложен формат на процентно число.
Бележки
- Показаните тук данни ще работят добре в таблица на Excel, която автоматично ще се разшири, за да включи нови данни. Тук използваме именувани диапазони, за да поддържаме формулите възможно най-прости.
- Осевите таблици също могат да се използват за изчисляване на дисперсията. Формулите осигуряват повече гъвкавост и контрол на цената на по-голяма сложност.
