Средневзвешенная скидка формула 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, и формула по-прежнему будет работать правильно. Здорово, правда?
Средневзвешенное значение — формула в Excel
В одной из предыдущих статей мы обсудили три основные функции для вычисления среднего в Excel, которые очень просты и просты в использовании.
Но что, если некоторые значения более важны, чем другие, и, следовательно, вносят больший вклад в конечное среднее значение? Например, если вы участвуете в экзаменах и все экзамены имеют одинаковый вес, они одинаково важны, то для вас важно именно среднее значение оценки.
Однако в реальной жизни это не всегда так. Например, если вы изучаете программирование, то насколько важны для вас оценки по истории и физкультуре по сравнению с оценками по профильным дисциплинам?
Некоторые задачи всегда важнее других. Некоторые экзамены важнее других.
В таких ситуациях вам как раз и необходимо рассчитывать средневзвешенное значение.
Хотя Microsoft Excel не предоставляет специальной функции взвешенного среднего, он предоставляет несколько других, которые окажутся полезными в ваших вычислениях, что показано в следующих примерах.
Что такое средневзвешенное значение?
Оно является своего рода средним арифметическим, в котором некоторые элементы набора данных имеют большую значимость, чем другие. Другими словами, каждому исходному показателю присваивается определенный вес.
В математике и статистике вы вычисляете взвешенное среднее значение, умножая каждое число в наборе на его вес, затем складываете произведения и делите итог сложения произведений на сумму всех весомостей.
В этом примере, чтобы посчитать средневзвешенную итоговую оценку, вы умножаете каждый полученный балл на соответствующий процент (преобразованный в десятичную дробь), складываете эти 5 произведений вместе и делите это число на итог сложения пяти весов:
((91 * 0,1) + (85 * 0,05) + (80 * 0,2) + (73 * 0,25) + (68 * 0,4)) / (0,1 + 0,05 + 0,2 + 0,25 + 0,4) = 74,8
Формулы для средневзвешенного значения в Excel
В Microsoft Excel взвешенное среднее рассчитывается с использованием того же подхода, но с гораздо меньшими усилиями, поскольку функции Excel выполнят большую часть работы за вас.
Пример 1. Функция СУММ.
По сути, он выполняет те же вычисления, что и описанные выше, за исключением того, что вы предоставляете ссылки на ячейки вместо чисел.
Посмотрите на рисунок чуть ниже: формула возвращает точно такой же результат, что и вычисления, которые мы делали минуту назад. Обратите внимание на разницу между нормальным средним, возвращаемым при помощи СРЗНАЧ в C8, и средневзвешенным (C9).
Несмотря на то, что формула эта очень проста и понятна, но она не подходит, если вы хотите усреднить большое количество элементов. Ведь придётся перечислять множество аргументов, что довольно утомительно.
В этом случае вам лучше использовать функцию СУММПРОИЗВ (SUMPRODUCT в английской версии). Об этом – ниже.
Пример 2. Функция СУММПРОИЗВ
Она идеально подходит для нашей задачи, так как предназначена для сложения произведений чисел. А это именно то, что нам нужно.
Таким образом, вместо умножения каждого числа на показатель его значимости по отдельности, вы предоставляете два массива в формуле СУММПРОИЗВ (в этом контексте массив представляет собой непрерывный диапазон ячеек), а затем делите результат на итог сложения весов:
= СУММПРОИЗВ(диапазон_значений ; диапазон_весов ) / СУММ( диапазон_весов )
Итак, формула умножает 1- е число в массиве 1 на 1- е в массиве 2 (в данном примере 91 * 0,1), а затем перемножает 2- е число в массиве 1 на 2- е в массиве 2 (85 * 0,15). в этом примере) и так далее. Когда все умножения выполнены, Эксель складывает произведения. Затем делим полученное на итог весов.
Чтобы убедиться, что функция СУММПРОИЗВ дает правильный результат, сравните ее с формулой СУММ из предыдущего примера, и вы увидите, что числа идентичны.
В нашем случае сложение весов дает 100%. То есть, это просто процент от итога. В таком случае верный результат может быть получен также следующими способами:
Но при использовании функции СУММ или СУММПРОИЗВ веса совершенно не обязательно должны составлять 100%. Однако, они также не должны быть обязательно выражены в процентах.
Например, вы можете составить шкалу приоритета / важности и назначить определенное количество баллов для каждого элемента, что и показано на следующем рисунке:
Видите, в этом случае мы обошлись без процентов.
Пример 3. Средневзвешенная цена.
Еще одна достаточно часто встречающаяся проблема – как рассчитать средневзвешенную цену товара. Предположим, мы получили 5 партий товара от различных поставщиков. Мы будем продавать его по одной единой цене. Но чтобы ее определить, нужно знать среднюю цену закупки. В тот здесь нам и пригодится расчет средневзвешенной цены. Взгляните на этот простой пример. Думаю, вам все понятно.
Итак, средневзвешенная цена значительно отличается от обычной средней. На это повлияли 2 больших партии товара по высокой цене. А формулу применяем такую же, как и при расчете любого взвешенного среднего. Перемножаем цену на количество, складываем эти произведения, а затем делим на общее количество товара.
Ну, это все о формуле средневзвешенного значения в Excel.
Расчет средневзвешенного значения в Excel
Основная идея
Предположим, что мы с вами сидим в приемно-экзаменационной комиссии и оцениваем абитуриентов, которые хотят поступить в наш ВУЗ. Оценки по различным предметам у наших кандидатов следующие:
На первый взгляд кажется, что лучше всех подходит Иван, т.к. у него средний бал максимальный. Но тут мы вовремя вспоминаем, что факультет-то наш называется «Программирование», а у Ивана хорошие оценки только по рисованию, пению и прочей физкультуре, а по математике и информатике как раз не очень. Возникает вопрос: а как присвоить нашим предметам различную важность (ценность), чтобы учитывать ее при расчете среднего? И вот тут на помощь приходит средневзвешенное значение.
В бизнесе средневзвешенное часто используется в таких задачах, как:
Расчет средневзвешенного формулами
Добавим к нашей таблице еще один столбец, где укажем некие безразмерные баллы важности каждого предмета по шкале, например, от 0 до 9 при поступлении на наш факультет программирования. Затем расчитаем средневзвешенный бал для каждого абитурента, т.е. среднее с учетом веса каждого предмета. Нужная нам формула будет выглядеть так:
Так что берем Машу, а Иван пусть поступает в институт физкультуры 😉
Расчет средневзвешенного в сводной таблице
Поднимем ставки и усложним задачу. Допустим, что теперь нам нужно подсчитать средневзвешенное, но не в обычной, а в сводной таблице. Предположим, что у нас есть вот такая таблица с данными по продажам:
Заметьте, что цена на один и тот же товар может различаться. Наша задача: рассчитать средневзвешенные цены для каждого товара. Следуя той же логике, что и в предыдущем пункте, например, для земляники, которая продавалась 3 раза, это должно быть:
=(691*10 + 632*12 + 957*26)/(10+12+26) = 820,33
То есть мы суммируем стоимости всех сделок (цена каждой сделки умножается на количество по сделке) и потом делим получившееся число на общее количество этого товара.
Правда, с реализацией этой нехитрой логики именно в сводной таблице нас ждет небольшой облом. Если вы работали со сводными раньше, то, наверное, помните, что можно легко переключить поле значений сводной в нужную нам функцию, щелкнув по нему правой кнопкой мыши и выбрав команду Итоги по (Summarize Values By) :
В этом списке есть среднее, но нет средневзвешенного 🙁
Можно частично решить проблему, если добавить в исходную таблицу вспомогательный столбец, где будет считаться стоимость каждой сделки:
В общем, как-то все неудобно, трудоемко и нагоняет тоску. Да еще и дополнительный столбец в исходных данных нужно руками делать. Но красивое решение есть.
Расчет средневзвешенного в сводной таблице с помощью Power Pivot и языка DAX
2. Затем щелкните мышью в строку формул и введите туда формулу для расчета средневзвешенного:
Несколько нюансов по формуле:
Расчет средневзвешенного значения в Excel
Основная идея
Предположим, что мы с вами сидим в приемно-экзаменационной комиссии и оцениваем абитуриентов, которые хотят поступить в наш ВУЗ. Оценки по различным предметам у наших кандидатов следующие:
На первый взгляд кажется, что лучше всех подходит Иван, т.к. у него средний бал максимальный. Но тут мы вовремя вспоминаем, что факультет-то наш называется «Программирование», а у Ивана хорошие оценки только по рисованию, пению и прочей физкультуре, а по математике и информатике как раз не очень. Возникает вопрос: а как присвоить нашим предметам различную важность (ценность), чтобы учитывать ее при расчете среднего? И вот тут на помощь приходит средневзвешенное значение.
В бизнесе средневзвешенное часто используется в таких задачах, как:
Расчет средневзвешенного формулами
Добавим к нашей таблице еще один столбец, где укажем некие безразмерные баллы важности каждого предмета по шкале, например, от 0 до 9 при поступлении на наш факультет программирования. Затем расчитаем средневзвешенный бал для каждого абитурента, т.е. среднее с учетом веса каждого предмета. Нужная нам формула будет выглядеть так:
Так что берем Машу, а Иван пусть поступает в институт физкультуры 😉
Расчет средневзвешенного в сводной таблице
Поднимем ставки и усложним задачу. Допустим, что теперь нам нужно подсчитать средневзвешенное, но не в обычной, а в сводной таблице. Предположим, что у нас есть вот такая таблица с данными по продажам:
Заметьте, что цена на один и тот же товар может различаться. Наша задача: рассчитать средневзвешенные цены для каждого товара. Следуя той же логике, что и в предыдущем пункте, например, для земляники, которая продавалась 3 раза, это должно быть:
=(691*10 + 632*12 + 957*26)/(10+12+26) = 820,33
То есть мы суммируем стоимости всех сделок (цена каждой сделки умножается на количество по сделке) и потом делим получившееся число на общее количество этого товара.
Правда, с реализацией этой нехитрой логики именно в сводной таблице нас ждет небольшой облом. Если вы работали со сводными раньше, то, наверное, помните, что можно легко переключить поле значений сводной в нужную нам функцию, щелкнув по нему правой кнопкой мыши и выбрав команду Итоги по (Summarize Values By) :
В этом списке есть среднее, но нет средневзвешенного 🙁
Можно частично решить проблему, если добавить в исходную таблицу вспомогательный столбец, где будет считаться стоимость каждой сделки:
В общем, как-то все неудобно, трудоемко и нагоняет тоску. Да еще и дополнительный столбец в исходных данных нужно руками делать. Но красивое решение есть.
Расчет средневзвешенного в сводной таблице с помощью Power Pivot и языка DAX
2. Затем щелкните мышью в строку формул и введите туда формулу для расчета средневзвешенного:
Несколько нюансов по формуле:
Расчет средневзвешенной процентной ставки портфеля в Excel
Средневзвешенное значение используется для усреднения статистически значений, которые имеют разные (большие или меньшие) значения в наборе данных.
Пример формулы для расчета средневзвешенной процентной ставки в Excel
Допустим нам нужно узнать средневзвешенную процентную ставку инвестиционного портфеля. Ниже на рисунке представлен исходный полный инвестиционный портфель. Для каждой инвестиции указывается ее значение и процентная ставка доходности. Допустим нам необходимо определить общую процентную ставку доходности для всего инвестиционного портфеля. Чтобы определить уровень доходности портфеля в процентах используем следующую формулу:
С целью вычисления средневзвешенной процентной ставки доля для каждого инвестиционного объекта в общей стоимости портфеля умножается на процентную ставку доходности. Функция СУММПРОИЗВ идеально подходит для перемножения двух наборов данных (массивов) с последующим суммированием результатов. Функция может иметь максимальное количество аргументом до 255, разделенных точкой с запятой. Но в данной формуле необходимо использовать только лишь 2 аргумента.
В первом аргументе указаны стоимости всех инвестиций, разделенных на их сумму, что дает пять процентных значений, представляющих вес каждой инвестиции в портфеле. На фонд «Pioneer Акции Восточной Европы» приходиться доля 17%, которая была вычислена в результате деления сумм 72021,35 на 423 655,02. Второй аргумент функции содержит процентные ставки доходности по каждой инвестиции. Функция СУММПРОИЗВ умножает каждый элемент с первого аргумента на соответствующий элемент со второго аргумента. Элемент B2/B7 умножается на C2, элемент B3/B7 на C3 и т.д. После перемножения всех пяти элементов функция суммирует результаты.
Если бы для вычисления средней процентной ставки доходности была просто использована функция СРЗНАЧ, в результате ее вычислений мы получили бы значение 5,906%. Это на самом деле меньшее значение чем показатель средневзвешенной процентной ставки портфеля. Например, инвестиция «Фонд Казна Top Brands» имеет большой процент доходности, как и большую долю в инвестиционном портфеле чем другие позиции.
Как рассчитать средневзвешенную процентную ставку в Excel пошагово
В выше приведенном примере все вычислительные операции реализованы с помощью функции СУММПРОИЗВ в процессе расчета средневзвешенного показателя. Эти вычисления могут быть реализованы и с помощью простых функций, записанных в прилегающих соседних ячейках. Ниже на рисунке изображены те же самые вычисления что и в предыдущем примере, но вместо функции СУММПРОИЗВ используется несколько иной метод. В отдельных ячейках рассчитана доля в портфеле для каждой отдельной инвестиции, после чего вычислено влияние каждого процента доходности на итоговый результат, а в конце все результаты суммируются обычной функцией СУММ.
Программа Excel является самым универсальным аналитическим инструментом, который прекрасно подходит для выполнения статистических анализов данных. Для этого Excel располагает большим арсеналом специальных статистических функций. Далее рассмотрим формулы служащие для анализа статистических данных, например, для их усреднения, сегментирования или вычисления графиков частот.