Коли ми обчислюємо просте середнє значення даного набору значень, передбачається, що всі значення мають однакову вагу або важливість.
Наприклад, якщо ви з'являєтесь на іспити і всі іспити мають однакову вагу, то середнє значення ваших загальних балів також буде середньозваженим вашим балам.
Однак у реальному житті це навряд чи так.
Деякі завдання завжди важливіші за інші. Деякі іспити важливіші за інші.
І ось де Середнє зважене входить у картину.
Ось визначення середньозваженого підручника:
Тепер давайте подивимося, як обчислити середньозважену величину в Excel.
Розрахунок середньозваженого в Excel
У цьому посібнику ви дізнаєтесь, як обчислити середньозважену величину в Excel:
- Використання функції SUMPRODUCT.
- Використання функції SUM.
Тож почнемо.
Розрахунок середньозваженого в Excel - функція SUMPRODUCT
Можуть бути різні сценарії, коли вам потрібно обчислити середньозважену величину. Нижче наведені три різні ситуації, коли ви можете використовувати функцію SUMPRODUCT для розрахунку середньозваженого значення в Excel
Нижче наведені три різні ситуації, коли ви можете використовувати функцію SUMPRODUCT для розрахунку середньозваженого значення в Excel
Приклад 1 - Коли ваги додають до 100%
Припустимо, у вас є набір даних із оцінками, набраними студентом на різних іспитах, разом із вагами у відсотках (як показано нижче):
У наведених вище даних учень отримує оцінки в різних оцінках, але, врешті -решт, йому потрібно поставити підсумковий бал або оцінку. Тут не можна обчислити просте середнє значення, оскільки важливість різних оцінок змінюється.
Наприклад, вікторина вагою 10% несе вдвічі більше ваги порівняно із завданням, але одну четверту вагу порівняно з іспитом.
У такому випадку ви можете скористатися функцією SUMPRODUCT, щоб отримати середньозважену оцінку.
Ось формула, яка дасть вам середньозважене значення в Excel:
= ПОВНІННЯ (В2: В8, С2: С8)
Ось як працює ця формула: Функція Excel SUMPRODUCT множить перший елемент першого масиву на перший елемент другого масиву. Потім він помножує другий елемент першого масиву на другий елемент другого масиву. І так далі…
І, нарешті, це додає всі ці значення.
Ось ілюстрація, щоб це було зрозуміло.
Приклад 2 - Коли ваги не додаються до 100%
У наведеному вище випадку ваги були призначені таким чином, що загальна сума склала до 100%. Але в реальних життєвих сценаріях це може бути не завжди так.
Давайте розглянемо один і той же приклад з різною вагою.
У наведеному вище випадку ваги додають до 200%.
Якщо я використовую ту саму формулу SUMPRODUCT, це дасть мені неправильний результат.
У наведеному вище результаті я подвоїв усі ваги, і він повертає середньозважене значення як 153,2. Тепер ми знаємо, що студент не може отримати більше 100 із 100, яким би він не був блискучим.
Причина цього в тому, що ваги не додають до 100%.
Ось формула, за якою це буде відсортовано:
= ПІДПРИЄМСТВО (B2: B8, C2: C8)/SUM (C2: C8)
У наведеній вище формулі результат SUMPRODUCT ділиться на суму всіх ваг. Отже, незважаючи ні на що, вага завжди буде складати до 100%.
Приклад 3 - Коли потрібно розрахувати ваги
У наведеному вище прикладі були вказані ваги. Однак можуть бути випадки, коли ваги недоступні безпосередньо, і вам потрібно спочатку обчислити ваги, а потім обчислити середньозважену вагу.
Припустимо, ви продаєте три різні види продукції, як зазначено нижче:
Ви можете розрахувати середньозважену ціну на продукт за допомогою функції SUMPRODUCT. Ось формула, яку ви можете використовувати:
= СУМПРОДУКТ (B2: B4, C2: C4)/SUM (B2: B4)
Поділ результату SUMPRODUCT на SUM величин гарантує, що ваги (в даному випадку кількість) додають до 100%.
Розрахунок середньозваженого в Excel - функція SUM
Хоча функція SUMPRODUCT є найкращим способом обчислення середньозваженого значення в Excel, ви також можете скористатися функцією SUM.
Щоб обчислити середньозважене значення за допомогою функції SUM, вам потрібно помножити кожен елемент із визначеним значенням у відсотках.
Використовуючи той самий набір даних:
Ось формула, яка дасть вам правильний результат:
= SUM (B2*C2, B3*C3, B4*C4, B5*C5, B6*C6, B7*C7, B8*C8)
Цей метод можна використовувати, якщо у вас є кілька предметів. Але якщо у вас багато предметів і ваг, цей метод може бути громіздким і схильним до помилок. Існує більш короткий і кращий спосіб зробити це за допомогою функції SUM.
Продовжуючи той самий набір даних, ось коротка формула, яка дасть вам середньозважене значення за допомогою функції SUM:
= SUM (B2: B8*C2: C8)
Хитрість під час використання цієї формули полягає у використанні Control + Shift + Enter, замість того, щоб просто натискати Enter. Оскільки функція SUM не може обробляти масиви, вам потрібно використовувати Control + Shift + Enter.
Коли ви натискаєте Control + Shift + Enter, ви побачите, що фігурні дужки автоматично з’являються на початку та в кінці формули (див. Рядок формул на зображенні вище).
Знову ж таки, переконайтеся, що ваги додають до 100%. Якщо це не так, вам потрібно розділити результат на суму ваг (як показано нижче, на прикладі продукту):
