Средневзвешенное значение рассчиттывается в Excel как среднее арифметическое, в котором некоторые элементы набора данных имеют большее значение, чем другие. Его формула – это по сути расчет средних значений, взвешенных по разным весам или другим значениям. Другими словами, каждому усредняемому значению присваивается определенный вес. В руководстве рассматриваются два простых способа посчитать средневзвешенное значение в Excel — с помощью функции СУММ или СУММПРОИЗВ, а также при помощи сводной таблицы.
В одной из предыдущих статей мы обсудили основные функции для того, чтобы вычислить среднее значение в Excel, которые очень просты и просты в использовании.
Далее мы рассмотрим, как может быть использована формула средневзвешенного значения в Excel, приведем несколько примеров.
К сожалению, Microsoft Excel не предоставляет специальной функции чтобы посчитать средневзвешенное значение. Но есть несколько других функций, которые окажутся полезными в ваших вычислениях.
Что такое средневзвешенное значение?
Когда вы рассчитываете среднее значение для набора значений, вы обычно работаете со значениями, имеющими одинаковый вес и важность.
Но что, если некоторые значения имеют больший «вес», чем другие, и, следовательно, вносят больший вклад в итоговое среднее значение? В таких ситуациях вам необходимо рассчитать средневзвешенное значение.
В бизнесе средневзвешенное значение часто используется в таких задачах, как:
- оценка портфеля акций, когда у каждой из них своя ценность/рисковость
- оценка прогресса по проекту, когда у задач не равный вес и важность
- оценка персонала по набору навыков (компетенций) с разной значимостью для требуемой должности
Например, оценки учащихся часто рассчитываются с использованием средневзвешенного значения, как будет рассмотрено ниже. Некоторые дисциплины и умения будут более важными. К примеру, нас интересуют те, кто лучше подготовлен к работе в Excel и к программированию. С этой точки зрения каждой дисциплине мы присвоим определенный вес с учетом ее значимости для подготовки нужного нам специалиста.
То есть мы хотим, чтобы формула средней оценки учитывала важность, или вес каждой изученной учебной дисциплины. Ниже мы рассмотрим это на примерах.
Методы расчета будут зависеть от имеющихся исходных данных.
Формулы для средневзвешенного значения в Excel
В Microsoft Excel взвешенное среднее рассчитывается с использованием того же подхода, но с гораздо меньшими усилиями, поскольку функции Excel выполнят большую часть работы за вас.
Пример 1. Сумма весов равна 100%
Предположим, у вас есть набор данных с оценками, полученными студентом на разных экзаменах. Также есть вес каждой дисциплины в процентах.
Учащийся получает оценки по разным учебным дисциплинам, но в результате ему необходимо поставить итоговый балл. Здесь некорректно рассчитать простое среднее значение, поскольку важность различных дисциплин различается.
Например, информатика с весом 30% имеет 6 раз больший вес по сравнению с пением и рисованием, у которых вес только 5%.
Чтобы с точки зрения математики получить средневзвешенное значение итоговой оценки, нужно перемножить все оценки на их веса и полученные результаты сложить.
9*30%+6*10%+8*10%+10*30%+6*5%+4*5%+5*5%+6*5%=8.15
Если у вас есть базовые знания о функции СУММ, приведенная ниже формула вряд ли потребует какого-либо объяснения:
=СУММ(B2*C2; B3*C3; B4*C4; B5*C5; B6*C6; B7*C7; B8*C8; B9*C9)
По сути, здесь мы выполняем те же вычисления, что и были описаны выше, за исключением того, что вместо чисел вы указываете ссылки на ячейки.
Вот короткая формула, которая даст вам средневзвешенное значение с помощью функции СУММ:
=СУММ(B2:B9*C2:C9)
Особенность этой формулы заключается в том, чтобы при ее вводе использовать CTRL + Shift + Enter, а не просто Enter. Поскольку функция СУММ не может обрабатывать массивы, вам необходимо использовать CTRL + Shift + Enter.
При этом обязательно убедитесь, что сумма весов равна 100%. Если это не так, вам нужно разделить результат на сумму весов (как показано в примерах ниже).
Хотя формула СУММ очень проста и понятна, она не является подходящим вариантом для множества данных, если вам нужно посчитать средневзвешенное значение, да и формула массива не слишком удобна.
Если у нас для каждого элемента имеется свой вес в процентах, и сумма этих весов равна 100%, гораздо проще вместо функции СУММ использовать функцию СУММПРОИЗВ, чтобы получить средневзвешенное значение всех оценок.
Вот формула, которая даст вам средневзвешенное значение в Excel для нашего примера:
=СУММПРОИЗВ(B2:B9;C2:C9)
Функция СУММПРОИЗВ Excel идеально подходит для этой задачи, поскольку она как раз и предназначена для суммирования произведений, а это именно то, что нам нужно. Таким образом, вместо того, чтобы умножать каждое значение на его вес по отдельности, вы предоставляете два массива в формуле СУММПРОИЗВ (в этом случае массив представляет собой непрерывный диапазон ячеек):
=СУММПРОИЗВ( диапазон_значений ; диапазон_весов )
Вот как работает эта формула : Функция Excel СУММПРОИЗВ умножает первый элемент первого массива на первый элемент второго массива. Затем она умножает второй элемент первого массива на второй элемент второго массива. И так далее..
И, наконец, она складывает все эти значения.
Как видите, полученный средневзвешенный балл, как бы мы его не рассчитывали, значительно отличается от обычной средней оценки, когда мы просто сложили все значения и разделили на их общее количество.
Пример 2. Когда сумма весов не равна 100 %
В приведенном выше случае веса были присвоены таким образом, чтобы общая сумма составляла 100%. Но в реальных жизненных ситуациях это не всегда так.
Давайте посмотрим пример с разными весами.
В приведенном выше случае сумма весов составляет 160%.
Если использовать формулу СУММПРОИЗВ из предыдущего раздела, она даст неверный результат.
Вот формула, чтобы посчитать средневзвешенное значение в Excel с любыми весами данных:
=СУММПРОИЗВ(B2:B9;$H$2:$H$9)/СУММ($H$2:$H$9)
То есть, необходимо сумму произведений значений и весов разделить на сумму весов.
= СУММПРОИЗВ(диапазон_значений ; диапазон_весов ) / СУММ( диапазон_весов )
А теперь рассмотрим пример расчета средневзвешенного значения, когда нет никаких весов, а их нужно рассчитать.
Пример 3. Когда необходимо рассчитать веса
Как мы уже выяснили, при использовании функции СУММ или СУММПРОИЗВ чтобы посчитать средневзвешенное значение в Excel, сумма весов необязательно должна составлять 100%. Их также необязательно выражать в процентах.
Например, вы можете составить шкалу приоритета / важности и назначить определенное количество баллов для каждого элемента, что и показано на следующем рисунке:
Обратите внимание, формула расчета здесь та же, что и в предыдущем примере:
=СУММПРОИЗВ(B2:B9;$H$2:$H$9)/СУММ($H$2:$H$9)
Видите, в этом случае мы обошлись без процентов.
Пример 4. Расчет средневзвешенной цены товара.
По такому же принципу мы можем рассчитать и средневзвешенную цену товара.
Обычно при подажах мы имеем разные цены для разных покупателей. Вес и значимость конкретной цены будет определяться объемом продаж. Чем больше продано товара по определенной цене, тем более значима эта цена и тем больше будет ее вклад в формирование средневзвешенной цены продажи товара.
Вот формула средневзвешенной цены для нашего условного примера:
=СУММПРОИЗВ(B2:B6;C2:C6)/СУММ(C2:C6)
Результат расчета вы видите на скриншоте ниже.
Разделив результат СУММПРОИЗВ на СУММ количеств, можно гарантировать, что сумма весов (в данном случае количеств) составит 100%.
Расчет средневзвешенной цены в сводной таблице
Как мы уже убедились, средневзвешенное значение в Excel легко вычислить при помощи функций СУММПРОИЗВ и СУММ. Однако, если у нас есть большой объем информации о продажах различных товаров, имеет смысл попробовать использовать для расчетов сводную таблицу.
Давайте рассмотрим пример, как при помощи сводной таблицы мы можем рассчитать средневзвешенную цену сразу по всем имеющимся товарам. Думаю, это значительно удобнее, нежели составлять отдельный расчет по каждому товару, как мы делали это выше.
Итак, рассмотрим условный пример с такими данными о продажах:
Попробуем рассчитать средневзвешенную цену по всем перечисленным в этой таблице товарам.
Создадим сводную таблицу. Вы можете воспользоваться этим руководством: Как сделать сводную таблицу – пошаговая инструкция.
На итоговые значения по полю «Цена» не обращайте внимания, так как простое арифметическое сложение цен не имеет экономического смысла.
Давайте добавим вычисления в нашу таблицу.
К сожалению, вычисляемые поля в сводной таблице не поддерживают функции Excel такие как СУММПРОИЗВ и СУММ. Поэтому мы не сможем использовать формулы, рекомендованные в предыдущих разделах. Но все же решение есть.
Выберите любую ячейку в сводной таблице, чтобы активировать на ленте меню Анализ сводной таблицы.
Выберите пункт Поля, элементы и наборы – Вычисляемое поле. Создайте новое вычисляемое поле, которое можно назвать «Средняя цена». Создайте формулу для этого поля, выбирая имеющиеся поля сводной таблицы и используя математические знаки.
Вы можете это видеть на скриншоте ниже.
Записав таким образом формулу вычисляемого поля, нажмите кнопку «ОК».
Теперь вернемся к сводной таблице и добавим поле Средняя цена в область значений. Как видно на скриншоте ниже, в строке итогов по каждому товару можно увидеть его средневзвешенную цену, а ниже в этом столбце – реальные цены продаж, из которых эта средневзвешенная цена сложилась.
В следующем столбце вы видите соответствующее каждой цене количество товара, которое как раз и было использовано в качестве весов.
Ну, это все о формуле средневзвешенного значения в Excel.
Как видно из рассмотренных нами примеров, рассчитать средневзвешенное значение в Excel несложно, если следовать описанным выше шагам. Этот метод можно применять в различных ситуациях, когда важно учитывать вес каждого значения при расчетах.
Таким образом, умение посчитать средневзвешенное значение в Excel – это важный навык для анализа данных, который поможет вам принимать обоснованные решения на основе ваших данных. Воспользуйтесь этой информацией и улучшите свои навыки работы с Excel!
