Загальна формула
=SUMPRODUCT(weights,values)/SUM(weights)
Резюме
Для обчислення середньозваженого значення можна використовувати функцію SUMPRODUCT разом із функцією SUM. У наведеному прикладі формула в G5, скопійована вниз, має вигляд:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
де вага - це названий діапазон I5: K5.
Пояснення
Середньозваженим середнім значенням, яке також називають середньозваженим, є середнє значення, коли деякі значення враховують більше, ніж інші. Іншими словами, деякі значення мають більшу "вагу". Ми можемо розрахувати середньозважене, помноживши значення на середнє на відповідні ваги, а потім поділивши суму результатів на суму ваг. В Excel це можна представити загальною формулою нижче, де ваги та значення - це діапазони комірок:
=SUMPRODUCT(weights,values)/SUM(weights)
На показаному робочому аркуші бали за 3 тести відображаються у стовпцях З-Е, а ваги знаходяться в названих вагах діапазону (I5: K5). Формула в комірці G5:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Працюючи зсередини, спочатку ми використовуємо функцію SUMPRODUCT, щоб помножити ваги на відповідні бали та підсумувати результат:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT спочатку множить відповідні елементи двох масивів разом, а потім повертає суму добутку:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Потім результат ділиться на суму ваг:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Оскільки формула копіюється вниз стовпець G, іменований діапазон ваги I5: K5 не змінюється, так як він веде себе як абсолютне посилання. Однак оцінки в C5: E5, введені як відносне посилання, оновлюються в кожному новому рядку. Результат - середньозважене середнє значення для кожного імені у списку, як показано. Середнє значення у стовпці F обчислюється лише для довідки з функцією AVERAGE:
=AVERAGE(C5:E5)
Ваги, які не складають 1
У цьому прикладі ваги налаштовані на додавання до 1, тому дільник завжди дорівнює 1, а результатом є значення, яке повертає SUMPRODUCT. Однак приємною особливістю формули є те, що ваги не потрібно складати до 1.
Наприклад, ми могли б використати вагу 1 для перших двох тестів і вагу 2 для фіналу (оскільки фінал вдвічі важливіший), а середньозважене значення буде однаковим:
У комірці G5 формула вирішується так:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Примітка: значення в фігурних дужках () вище - це діапазони, виражені як масиви.
Транспонування ваг
Функція SUMPRODUCT вимагає, щоб розміри масиву були сумісними. Якщо розміри несумісні, SUMPRODUCT поверне помилку #VALUE. У наведеному нижче прикладі ваги такі ж, як у вихідного прикладу, але вони перераховані у вертикальному діапазоні:
Щоб обчислити середньозважене середнє за тією ж формулою, нам потрібно "перевернути" ваги в горизонтальний масив за допомогою функції ТРАНСПОЗ:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Після запуску TRANSPOSE вертикальний масив:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
стає:
=(0.25,0.25,0.5) // horizontal array
І з цього моменту формула поводиться як раніше.
Детальніше: вертикальні та горизонтальні масиви.
