Средневзвешенная формула в excel — Мир ПК

Вычисляем средневзвешенные значения при помощи СУММПРОИЗВ

Excel превратил вычисление среднего арифметического нескольких ячеек в очень простую задачу – просто используйте функцию СРЗНАЧ (AVERAGE). Но что делать, если некоторые значения имеют больший вес, чем другие? Например, на многих курсах тесты имеют больший вес, чем задания. Для таких случаев необходимо рассчитывать среднее взвешенное.

В Excel нет функции для расчёта средневзвешенного значения, зато есть функция, которая сделает за Вас большую часть работы: СУММПРОИЗВ (SUMPRODUCT). И даже, если Вы никогда не использовали эту функцию раньше, то к концу этой статьи будете работать с ней как профи. Метод, который мы используем, работает в любой версии Excel, а также в других электронных таблицах, таких как Google Sheets.

Подготавливаем таблицу

Если Вы собираетесь вычислять среднее взвешенное, Вам потребуется минимум два столбца. Первый столбец (в нашем примере – столбец B) содержит оценки для каждого задания или теста. Второй столбец (столбец C) содержит веса. Больший вес означает большее влияние задания или теста на итоговую оценку.

Чтобы понять, что такое вес, Вы можете представить его, как процент от итоговой оценки. На самом деле это не так, поскольку в таком случае веса в сумме должны составлять 100%. Формула, которую мы разберем в этом уроке, будет подсчитывать все правильно и не зависеть от суммы, в которую складываются веса.

Вводим формулу

Теперь, когда наша таблица готова, мы добавляем формулу в ячейку B10 (подойдёт любая пустая ячейка). Как и с любой другой формулой в Excel, начинаем со знака равенства (=).

Первая часть нашей формулы – это функция СУММПРОИЗВ (SUMPRODUCT). Аргументы должны быть заключены в скобки, поэтому открываем их:

Далее, добавляем аргументы функции. СУММПРОИЗВ (SUMPRODUCT) может иметь несколько аргументов, но обычно используют два. В нашем примере, первым аргументом будет диапазон ячеек B2:B9, который содержит оценки.

Вторым аргументом будет диапазон ячеек C2:C9, в котором содержатся веса. Между этими аргументами должен стоять разделитель точка с запятой (запятая). Когда все будет готово, закрываем скобки:

Теперь добавим вторую часть нашей формулы, которая поделит результат вычисляемый функцией СУММПРОИЗВ (SUMPRODUCT) на сумму весов. Позже мы обсудим, почему это важно.

Чтобы выполнить операцию деления, продолжаем уже введённую формулу символом / (прямой слеш), а далее записываем функцию СУММ (SUM):

=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(

Для функции SUM (СУММ) мы укажем только один аргумент – диапазон ячеек C2:C9. Не забудьте после ввода аргумента закрыть скобки:

=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)

Готово! После нажатия клавиши Enter, Excel рассчитает среднее взвешенное значение. В нашем примере итоговый результат будет равен 83,6.

Как это работает

Давайте разберем каждую часть формулы, начиная с функции СУММПРОИЗВ (SUMPRODUCT), чтобы понять, как она работает. Функция СУММПРОИЗВ (SUMPRODUCT) вычисляет произведение оценки каждого задания на его вес, а затем суммирует все полученные произведения. Другими словами, функция находит сумму произведений (sum of the products), отсюда она и получила своё название. Итак, для Задания 1 умножаем 85 на 5, а для Теста умножаем 83 на 25.

Если Вас удивляет, зачем перемножать значения в первой части, представьте, что чем больше вес у задания, тем большее число раз мы должны учитывать оценку за него. Например, Задание 2 посчитано 5 раз, а Итоговый экзамен – 45 раз. Вот почему Итоговый экзамен имеет большее влияние на итоговую оценку.

Для сравнения, при вычислении обычного среднеарифметического, каждое значение учитывается только один раз, то есть все значения имеют равный вес.

Если бы Вы могли заглянуть под капот функции СУММПРОИЗВ (SUMPRODUCT), то увидели, что на самом деле она считает вот что:

К счастью, нам не нужно писать такую длинную формулу, поскольку СУММПРОИЗВ (SUMPRODUCT) делает всё это автоматически.

Сама по себе функция СУММПРОИЗВ (SUMPRODUCT) возвращает нам огромное число – 10450. В этот момент включается вторая часть формулы: /СУММ(C2:C9) или /SUM(C2:C9), которая возвращает результат в нормальный диапазон оценок, давая ответ 83,6.

Вторая часть формулы очень важна, т.к. позволяет автоматически корректировать вычисления. Помните, что веса не обязаны складываться в сумму 100%? Все это благодаря второй части формулы. Например, если мы увеличиваем одно или несколько значений весов, вторая часть формулы просто выполнит деление на большее значение, вновь приводя к правильному ответу. Или же мы можем сделать веса намного меньше, например, указать такие значения как 0,5, 2,5, 3 или 4,5, и формула по-прежнему будет работать правильно. Здорово, правда?

СРЕДНЕВЗВЕШ

Данная функция является частью надстройки MulTEx

Расчет средневзвешенного значения, в том числе на основании критерия

Функция рассчитывает средневзвешенное значение. Средневзвешенное значение, это значение, которое определяет «важность» того или иного значения на основании числовой шкалы. Т.е. чем большее число шкалы указано для значения — тем больший вес(ценность) при расчетах это значение имеет. Например, при оценке знаний слушателя можно использовать промежуточные тесты и итоговые экзамены. Экзамены при этом имеют большее значение, чем промежуточные тесты. Если не брать в расчет вес экзамена или теста, то можно применить вычисление обычного среднего при помощи встроенной в Excel СРЗНАЧ (AVERAGE) . Но по сути в данном случае те слушатели, которые сдали на отлично тесты могут одновременно полностью провалить экзамены. А экзамены для нас куда важнее и фактически при использовании обычных средних итогов мы получим неверную картину. Для примера возьмем следующую таблицу слушателей и их оценок по тестам и экзаменам:

Если верить средним показателям самый «одаренный» слушатель — Андрей. Но если присмотреться видно, что Андрей не сдал ни одного экзамена даже на 4. Это значит, что он по факту нам не подходит. В то же время на основании средневзвешенного значения Сергей наиболее подходящий кандидат. И если изучить его результаты, то видно, что какие-то тесты он хоть и сдавал на 3, но экзамены сданы минимум на 4. А в столбце Вес у нас указано, что экзамены имеют куда большее значение, нежели тесты.
Так же средневзвешенное можно применять для общей оценки компетенций сотрудников или кандидатов на определенные должности.
Стандартных функций для расчета средневзвешенного в Excel нет. Функция СРЕДНЕВЗВЕШ позволяет без лишних ухищрений вычислить средневзвешенное значение для таблиц.

Вызов команды через стандартный диалог:

Мастер функций—Категория «MulTEx»— СРЕДНЕВЗВЕШ

Вызов с панели MulTEx:

Сумма/Поиск/Функции — Математические — СРЕДНЕВЗВЕШ

Синтаксис:
=СРЕДНЕВЗВЕШ( C2:C9 ; $B$2:$B$9 )
=СРЕДНЕВЗВЕШ( $B$2:$B$20 ; $C$2:$C$20 ; A3 ; $A$2:$A$20 )
=СРЕДНЕВЗВЕШ( $B$2:$B$20 ; $C$2:$C$20 ;»*блок*»; $A$2:$A$20 )

Значения ( $B$2:$B$20 ) — указывается диапазон со значениями, среднее для которых необходимо подсчитать с учетом веса каждого показателя.

Вес( $C$2:$C$20 ) — указывается ссылка на диапазон, в котором указана значимость каждого показателя. Должен состоять только из одного столбца и должен быть равен по количеству строк диапазону Значения.

Критерий( A3 ) — необязательный. Ссылка на ячейку с критерием или непосредственно критерий в текстовом представлении. В критерии допускается указание специальных символов подстановки — * и ? . Т.е. указав в качестве Критерия » *затрат* » средневзвешенное будет рассчитано для всех ячеек, в столбце критериев которых встречается слово » затрат «(затраты, данные по затратам и т.п.).
Так же данный аргумент может принимать в качестве критерия символы сравнения( , =, <>, ):

• «>0» — будут рассчитаны средневзвешенные для всех ячеек в столбце значений, критерии для которых в диапазоне критериев больше нуля;
• «>=2» — будут рассчитаны средневзвешенные для всех ячеек в столбце значений, критерии для которых в диапазоне критериев больше или равно двум;
• » — будут рассчитаны средневзвешенные для всех ячеек в столбце значений, критерии для которых в диапазоне критериев меньше нуля;
• » — будут рассчитаны средневзвешенные для всех ячеек в столбце значений, критерии для которых в диапазоне критериев меньше или равно 60;
• «<>0″ — будут рассчитаны средневзвешенные для всех ячеек в столбце значений, критерии для которых в диапазоне критериев не равно нулю;
• «<>« — будут рассчитаны средневзвешенные для всех ячеек в столбце значений, критерии для которых в диапазоне критериев не пустые

Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением: «<>«& D$1

ДиапазонКритерия( $A$2:$A$20 ) — необязательный. Указывается ссылка на диапазон значений, среди которых просматривать критерии. Обязателен к указанию, если указан аргумент критерий . Должен состоять только из одного столбца и должен быть равен по количеству строк диапазону Значения .

Если с простой таблицей(с расчетом средневзвешенных оценок) все выглядит достаточно логично и понятно, то с критериями чуть сложнее. Для примера возьмем таблицу товаров, продаваемых за разные периоды. Цена товара в разные дни могла изменяться и нам необходимо определить средневзвешенную цену на каждый товар, в зависимости от его продаваемости по той или иной цене:

Т.е. по сути критерием веса для каждого товара у нас является общая стоимость продаж каждого товара. Стандартными функциями пришлось бы сначала количество каждого товара умножить на цену(определив общую стоимость), далее просуммировать полученную общую стоимость по каждому отдельному товару и её разделить на общее количество этого товара. Звучит не очень просто, а вычислять руками еще сложнее. Функция СРЕДНЕВЗВЕШ делает это опять же самостоятельно — надо просто дополнительно указать критерий(наименование товара) и столбец, в котором этот критерий искать(столбец наименований товара):

Здесь функция для каждого отдельного наименования товара вычислила средневзвешенное. Т.е. для всех строк, где в столбце А записано «Сканер» записано одно и то же значение средневзвешенного. Так же можно сначала в отдельном столбце создать список уникальных наименований товаров(при помощи функции MulTEx —Диапазоны —Работа с дубликатами) и к этим данным применить формулу СРЕДНЕВЗВЕШ:

Расчет средневзвешенной процентной ставки портфеля в Excel

Средневзвешенное значение используется для усреднения статистически значений, которые имеют разные (большие или меньшие) значения в наборе данных.

Пример формулы для расчета средневзвешенной процентной ставки в Excel

Допустим нам нужно узнать средневзвешенную процентную ставку инвестиционного портфеля. Ниже на рисунке представлен исходный полный инвестиционный портфель. Для каждой инвестиции указывается ее значение и процентная ставка доходности. Допустим нам необходимо определить общую процентную ставку доходности для всего инвестиционного портфеля. Чтобы определить уровень доходности портфеля в процентах используем следующую формулу:

С целью вычисления средневзвешенной процентной ставки доля для каждого инвестиционного объекта в общей стоимости портфеля умножается на процентную ставку доходности. Функция СУММПРОИЗВ идеально подходит для перемножения двух наборов данных (массивов) с последующим суммированием результатов. Функция может иметь максимальное количество аргументом до 255, разделенных точкой с запятой. Но в данной формуле необходимо использовать только лишь 2 аргумента.

В первом аргументе указаны стоимости всех инвестиций, разделенных на их сумму, что дает пять процентных значений, представляющих вес каждой инвестиции в портфеле. На фонд «Pioneer Акции Восточной Европы» приходиться доля 17%, которая была вычислена в результате деления сумм 72021,35 на 423 655,02. Второй аргумент функции содержит процентные ставки доходности по каждой инвестиции. Функция СУММПРОИЗВ умножает каждый элемент с первого аргумента на соответствующий элемент со второго аргумента. Элемент B2/B7 умножается на C2, элемент B3/B7 на C3 и т.д. После перемножения всех пяти элементов функция суммирует результаты.

Если бы для вычисления средней процентной ставки доходности была просто использована функция СРЗНАЧ, в результате ее вычислений мы получили бы значение 5,906%. Это на самом деле меньшее значение чем показатель средневзвешенной процентной ставки портфеля. Например, инвестиция «Фонд Казна Top Brands» имеет большой процент доходности, как и большую долю в инвестиционном портфеле чем другие позиции.

Как рассчитать средневзвешенную процентную ставку в Excel пошагово

В выше приведенном примере все вычислительные операции реализованы с помощью функции СУММПРОИЗВ в процессе расчета средневзвешенного показателя. Эти вычисления могут быть реализованы и с помощью простых функций, записанных в прилегающих соседних ячейках. Ниже на рисунке изображены те же самые вычисления что и в предыдущем примере, но вместо функции СУММПРОИЗВ используется несколько иной метод. В отдельных ячейках рассчитана доля в портфеле для каждой отдельной инвестиции, после чего вычислено влияние каждого процента доходности на итоговый результат, а в конце все результаты суммируются обычной функцией СУММ.

Программа Excel является самым универсальным аналитическим инструментом, который прекрасно подходит для выполнения статистических анализов данных. Для этого Excel располагает большим арсеналом специальных статистических функций. Далее рассмотрим формулы служащие для анализа статистических данных, например, для их усреднения, сегментирования или вычисления графиков частот.

Расчет средневзвешенной процентной ставки портфеля в Excel

Средневзвешенное значение используется для усреднения статистически значений, которые имеют разные (большие или меньшие) значения в наборе данных.

Пример формулы для расчета средневзвешенной процентной ставки в Excel

Допустим нам нужно узнать средневзвешенную процентную ставку инвестиционного портфеля. Ниже на рисунке представлен исходный полный инвестиционный портфель. Для каждой инвестиции указывается ее значение и процентная ставка доходности. Допустим нам необходимо определить общую процентную ставку доходности для всего инвестиционного портфеля. Чтобы определить уровень доходности портфеля в процентах используем следующую формулу:

С целью вычисления средневзвешенной процентной ставки доля для каждого инвестиционного объекта в общей стоимости портфеля умножается на процентную ставку доходности. Функция СУММПРОИЗВ идеально подходит для перемножения двух наборов данных (массивов) с последующим суммированием результатов. Функция может иметь максимальное количество аргументом до 255, разделенных точкой с запятой. Но в данной формуле необходимо использовать только лишь 2 аргумента.

В первом аргументе указаны стоимости всех инвестиций, разделенных на их сумму, что дает пять процентных значений, представляющих вес каждой инвестиции в портфеле. На фонд «Pioneer Акции Восточной Европы» приходиться доля 17%, которая была вычислена в результате деления сумм 72021,35 на 423 655,02. Второй аргумент функции содержит процентные ставки доходности по каждой инвестиции. Функция СУММПРОИЗВ умножает каждый элемент с первого аргумента на соответствующий элемент со второго аргумента. Элемент B2/B7 умножается на C2, элемент B3/B7 на C3 и т.д. После перемножения всех пяти элементов функция суммирует результаты.

Если бы для вычисления средней процентной ставки доходности была просто использована функция СРЗНАЧ, в результате ее вычислений мы получили бы значение 5,906%. Это на самом деле меньшее значение чем показатель средневзвешенной процентной ставки портфеля. Например, инвестиция «Фонд Казна Top Brands» имеет большой процент доходности, как и большую долю в инвестиционном портфеле чем другие позиции.

Как рассчитать средневзвешенную процентную ставку в Excel пошагово

В выше приведенном примере все вычислительные операции реализованы с помощью функции СУММПРОИЗВ в процессе расчета средневзвешенного показателя. Эти вычисления могут быть реализованы и с помощью простых функций, записанных в прилегающих соседних ячейках. Ниже на рисунке изображены те же самые вычисления что и в предыдущем примере, но вместо функции СУММПРОИЗВ используется несколько иной метод. В отдельных ячейках рассчитана доля в портфеле для каждой отдельной инвестиции, после чего вычислено влияние каждого процента доходности на итоговый результат, а в конце все результаты суммируются обычной функцией СУММ.

Программа Excel является самым универсальным аналитическим инструментом, который прекрасно подходит для выполнения статистических анализов данных. Для этого Excel располагает большим арсеналом специальных статистических функций. Далее рассмотрим формулы служащие для анализа статистических данных, например, для их усреднения, сегментирования или вычисления графиков частот.