Резюме
Щоб розрахувати прогноз проти фактичної дисперсії на основі набору даних, ви можете використовувати функцію 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, яка автоматично розширюватиметься та включатиме нові дані. Тут ми використовуємо іменовані діапазони, щоб максимально спростити формули.
- Зведені таблиці також можна використовувати для обчислення дисперсії. Формули забезпечують більшу гнучкість та контроль за рахунок більшої складності.
