Excel зробив обчислення середнього значення для кількох клітинок дуже простим завданням – просто скористайтеся цією функцією СЕРЕДНЯ (СЕРЕДНЯ). Але що, якщо деякі цінності мають більшу вагу, ніж інші? Наприклад, у багатьох курсах тести мають більшу вагу, ніж завдання. Для таких випадків необхідно розраховувати середнє зважене.
У Excel немає функції для обчислення середньозваженого значення, але є функція, яка виконає більшу частину роботи за вас: SUMPRODUCT (СУМА ДОДАТКУ). І навіть якщо ви ніколи раніше не користувалися цією функцією, до кінця цієї статті ви будете використовувати її як професіонал. Метод, який ми використовуємо, працює в будь-якій версії Excel, а також в інших електронних таблицях, таких як Google Sheets.
Готуємо стіл
Якщо ви збираєтеся обчислити середньозважену величину, вам знадобляться принаймні два стовпці. Перший стовпець (у нашому прикладі стовпець B) містить бали за кожне завдання або тест. Другий стовпець (стовпець C) містить ваги. Більша вага означає більший вплив завдання чи тесту на підсумкову оцінку.
Щоб зрозуміти, що таке вага, ви можете уявити її як відсоток від вашої кінцевої оцінки. Насправді це не так, оскільки в цьому випадку вагові коефіцієнти повинні складати 100%. Формула, яку ми розберемо в цьому уроці, розрахує все правильно і не залежить від суми, яку додають вагові коефіцієнти.
Вводимо формулу
Тепер, коли наша таблиця готова, ми додаємо формулу в клітинку B10 (підійде будь-яка порожня клітинка). Як і будь-яка інша формула в Excel, ми починаємо зі знака рівності (=).
Перша частина нашої формули – це функція SUMPRODUCT (СУМА ДОДАТКУ). Аргументи повинні бути в дужках, тому ми їх відкриваємо:
=СУММПРОИЗВ(
=SUMPRODUCT(
Далі додайте аргументи функції. SUMPRODUCT (SUMPRODUCT) може мати кілька аргументів, але зазвичай використовуються два. У нашому прикладі першим аргументом буде діапазон клітинок. В2: В9A, що містить бали.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Другим аргументом буде діапазон клітинок С2: С9, який містить ваги. Ці аргументи повинні бути розділені крапкою з комою (комою). Коли все готово, закриваємо дужки:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Тепер давайте додамо другу частину нашої формули, яка розділить результат, обчислений функцією SUMPRODUCT (SUMPRODUCT) за сумою ваг. Пізніше ми обговоримо, чому це важливо.
Для виконання операції ділення продовжуємо вже введену формулу символом / (прямий слеш), а потім запишіть функцію SUM (СУМА):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Для функції SUM (SUM) будемо вказувати лише один аргумент – діапазон клітинок С2: С9. Не забудьте закрити круглі дужки після введення аргументу:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Готовий! Після натискання клавіші Що натомість? Створіть віртуальну версію себе у , Excel обчислить середньозважену величину. У нашому прикладі кінцевим результатом буде 83,6.
Як це працює?
Давайте розберемо кожну частину формули, починаючи з функції SUMPRODUCT (SUMPRODUCT), щоб зрозуміти, як це працює. функція SUMPRODUCT (SUMPRODUCT) обчислює добуток оцінки кожного елемента та його ваги, а потім підсумовує всі отримані продукти. Іншими словами, функція знаходить суму добутків, звідки і назва. Тому для Завдання 1 85 помножити на 5, а за Тест помножити 83 на 25.
Якщо вам цікаво, навіщо нам потрібно перемножувати значення в першій частині, уявіть, що чим більша вага завдання, тим більше разів ми повинні враховувати оцінку за нього. Наприклад, Завдання 2 порахували 5 разів і Підсумковий іспит – 45 разів. Ось чому Підсумковий іспит більше впливає на підсумкову оцінку.
Для порівняння, при розрахунку звичайного середнього арифметичного кожне значення враховується тільки один раз, тобто всі значення мають однакову вагу.
Якби ви могли заглянути під капот функції SUMPRODUCT (SUMPRODUCT), ми побачили, що насправді вона вірить у це:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
На щастя, нам не потрібно писати таку довгу формулу, оскільки SUMPRODUCT (SUMPRODUCT) робить усе це автоматично.
Сама по собі функція SUMPRODUCT (SUMPRODUCT) повертає нам величезне число − 10450. На цьому етапі вступає в дію друга частина формули: /СУМ(C2:C9) or /СУМ(C2:C9), який повертає результат до нормального діапазону балів, даючи відповідь 83,6.
Друга частина формули дуже важлива, оскільки дозволяє автоматично коригувати розрахунки. Пам’ятайте, що вагові ваги не повинні складати 100%? Все це завдяки другій частині формули. Наприклад, якщо ми збільшимо одне або кілька значень ваги, друга частина формули буде просто поділена на більше значення, що знову призведе до правильної відповіді. Або ми можемо зробити ваги набагато меншими, наприклад, вказавши такі значення 0,5, 2,5, 3 or 4,5, і формула працюватиме правильно. Це чудово, правда?