Всем привет! Сегодня мы будем разбирать более сложную тему, чем обычно, хотя я попробовал разобрать ее как можно подробнее и понятнее на простом примере. Мы рассмотрим формулу средневзвешенного значения в Excel:
=СУММПРОИЗВ(Массив_значений;Массив_веса_значений)/СУММ(Массив_веса_значений)
Обычно средневзвешенное значение используют для более точного определения среднего значения по выбранным весам. Простыми словами: не все числа для нас одинаково важны. Одно значение может влиять на итог сильнее, другое – слабее. Не будем вдаваться в сухие определения и лучше все рассмотрим на конкретных примерах, чтобы вам было понятно.
Если вы пока плохо ориентируетесь в формулах, лучше сначала освежить базу – как работать с формулами в Excel.
Пример 1: Оценки
Представим себе ситуацию, что нам нужно выбрать двух лучших студентов по оценкам для поступления на наш факультет по программированию. У нас есть таблица с именами и оценками.
В 11 строке у нас расположено среднеарифметическое по всем оценкам. Формула в Excel:
=СРЗНАЧ(B2:B10)
Вроде сразу же на ум приходит Петр, у которого средний балл 4,3, но есть нюанс – у него по Алгебре и Информатике (двум профильным предметам нашего факультета) невысокие оценки. То есть обычное среднее тут немного обманывает: оно ставит физкультуру и информатику на один уровень важности.
Что же нам делать в таком случае? Для этого мы будем высчитывать средневзвешенное значение по весу каждого предмета. Для начала нам нужно в табличку добавить вес предмета.
Я на глаз добавил вес для факультета программирования. У нас Информатика и Алгебра имеют самый высокий вес в 9 баллов. Физ-ра, Биология и Пение – всего 1. Вес здесь можно воспринимать как важность предмета. Чем больше вес, тем сильнее этот предмет влияет на итоговый балл.
Средневзвешенное значение по предметам, например, для Дениса будет выглядеть так:
=(5 х 9 + 4 х 8 + 5 х 3 + 4 х 6 + 5 х 4 + 4 х 1 + 3 х 1 + 3 х 1 + 4 х 9) / (9 + 8 + 3 + 6 + 4 + 1 + 1 + 1 + 9)
То есть нам нужно умножить каждое значение между двумя столбцами:
Оценки х Вес оценок
Потом все произведения сложить и поделить на сумму всех весов. Таким образом получается рассчитать средневзвешенное значение. Давайте посмотрим, как это реализовать в Excel. И посмотрим на конечную формулу (для Дениса):
=СУММПРОИЗВ(B2:B10;$H$2:$H$10)/СУММ($H$2:$H$10)
Рассмотрим подробно первую функцию:
СУММПРОИЗВ(Массив_1;Массив_2;Массив_3;…)
Она как раз и делает перемножение по столбцам, а потом считает сумму этих произведений. То есть Excel сам берет B2 и умножает на H2, потом B3 на H3, B4 на H4 и так до конца указанного диапазона. Ну и дальше достаточно поделить это все на сумму всех весов:
СУММ($H$2:$H$10)
Важно! Диапазоны в функции СУММПРОИЗВ должны быть одинакового размера. Например, B2:B10 и H2:H10 подходят, а B2:B10 и H2:H8 уже могут привести к ошибке. Если хотите подробнее разобрать эту функцию отдельно, читаем – СУММПРОИЗВ в Excel.
Обратите внимание, что мы используем знак доллара ($), чтобы закрепить адреса ячеек веса, так как мы должны использовать его для всех остальных участников, чтобы маркер автозаполнения все сделал правильно при копировании в соседние столбцы по горизонтали. Если эти адреса не закрепить, при протягивании формулы диапазон весов уедет в сторону, и результат будет неправильным.
Если вы не до конца понимаете, зачем нужны доллары в адресах ячеек, читаем отдельный урок – как закрепить ячейку в формуле Excel.
Пример 2: Товары
Рассмотрим еще один пример для закрепления расчета средневзвешенного значения в Excel. Пример классический, но не такой интуитивно понятный, как прошлый. Представим себе, что у нас есть таблица товаров с ценой и количеством. Товары продавались в разное время по разной цене. И продано было разное количество этих товаров. Например, апельсины продавались по 200, 300 и 250 в количестве 125, 23 и 143 соответственно.
Как вариант, можно посчитать среднее арифметическое по цене:
=СРЗНАЧ(B2:B4)
Но для бизнеса данное среднее имеет большую погрешность, ведь мы продавали по определенным ценам разное количество товара. Цена, по которой продали 143 апельсина, должна влиять на итог сильнее, чем цена, по которой продали всего 23 штуки. Поэтому в данном примере стоит использовать формулу средневзвешенного.
Что мы будем использовать в качестве веса для цены товара? Обычно используется количество проданного. То есть чем больше продано, тем больший вес будет иметь эта цена. В итоге для апельсинов расчет будет такой:
(200 х 125 + 300 х 23 + 250 х 143) / (125 + 23 + 143)
Попробуйте, не подсматривая на мое решение, самому написать расчетную формулу для данного примера. Можете опираться на пример из прошлой главы. Если у вас это получилось, то можете посмотреть на мое решение, хотя оно будет точно таким же, как и в прошлой главе.
Для апельсинов расчет будет таким:
=СУММПРОИЗВ(B2:B4;C2:C4)/СУММ(C2:C4)
Мы используем те же самые функции СУММПРОИЗВ и СУММ. Рассматривать их подробно не будем, я думаю, вы с ними уже разобрались. Но запомните сам принцип: первый диапазон – это значения, которые мы усредняем, а второй диапазон – это веса этих значений.
Частые ошибки
При расчете средневзвешенного в Excel чаще всего встречаются такие проблемы:
- Диапазон значений и диапазон весов имеют разный размер. В таком случае формула может вернуть ошибку.
- В весах есть текст вместо чисел. Excel может посчитать не так, как вы ожидаете.
- Сумма весов равна нулю. Тогда делить будет не на что, и появится ошибка деления.
- При копировании формулы съехал диапазон весов. Для этого мы и закрепляем его через знак доллара ($).
Если в формуле появляется ошибка деления, дополнительно можете посмотреть материал – формула деления в Excel.
В целом наш урок подошел к концу. Пишите свои дополнения в комментариях. До новых встреч на портале WiFiGiD.RU.
Спасибо вам большое! Как всегда отличный и понятный урок!
то что было нужно, спасиб
Самое сложное это вообще понять что делать, т.е. почему формула именно такая. Собрать то ее не проблема, тут просто математики нужно знать больше.