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