Средневзвешенный показатель как рассчитать

Средневзвешенное значение — формула в 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.

Источник

Как вычислить среднее арифметическое взвешенное

Среднее арифметическое — это статистический показатель, иллюстрирующий среднее значение набора данных, который рассчитывается как сумма всех значений, деленная на их количество. Это важный коэффициент, получивший широкое распространение в прикладных науках.

Что такое среднее арифметическое

Суть данного показателя проще всего продемонстрировать на примере. Торговец продает на рынке яблоки. В понедельник ему удалось продать 54 кг, во вторник — 47 кг, а в среду — 61 кг. Торговец хочет спланировать свои продажи и ему интересно, сколько килограмм фруктов он продает в среднем за день. Вот здесь на сцену и выходит среднее арифметическое. Для его определения необходимо суммировать значения показателей и разделить на их количество.

Среднее = (54 + 47 + 61) / 3 = 54 кг

Торговец выяснил, что в среднем он продает 54 кг яблок в день и может грамотно спрогнозировать свои затраты на покупку товара. Так как килограмм яблок стоит 10 рублей, то в день наш коммерсант зарабатывает в среднем 540 рублей.

Среднее арифметическое широко используется в статистике в случаях, если рассматривается набор однородных данных. В нашем случае это были только яблоки. Однако если торговец расширит свой ассортимент и добавит на прилавки еще и апельсины? Как изменится средняя стоимость одной единицы товара?

Пусть в понедельник торговец реализовал 23 кг апельсинов, во вторник — 28 кг, а в среду — 21 кг. Средний уровень продаж апельсинов составит:

Среднее = (28 + 21 + 23) / 3 = 24 кг.

Так как цена одного килограмма цитрусов составляет 20 рублей, то средний доход от продажи апельсинов составит 480 рублей в день.

Теперь решим простую на первый взгляд задачу. Какова средняя стоимость реализации одного килограмма любого товара? Неискушенный аналитик решит эту задачу как:

Среднее = (20 + 10) / 2 = 15 рублей,

за килограмм апельсинов или яблок. Однако не все так просто. В случае с разнородными данными важно учитывать их вес.

Среднее арифметическое взвешенное

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

В данном случае у нас есть средний вес каждого вида фруктов и его необходимо учесть для определения средней цены одного килограмма товара. На практике это выглядит так:

Среднее взвешенное = (20 × 24 + 10 × 54) / (24 + 54) = 13,07.

Естественно, 13,07 не сильно отличается от 15, однако чем больше слагаемых и больший разброс весов, тем сильнее отличается среднее взвешенное от простого среднего арифметического. Также очевидно, что если все веса равны единице, то среднее взвешенное становится равным среднему арифметическому.

Что выбрать?

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

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

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

Примеры из реальной жизни

Подсчет средней зарплаты

Пусть на вашем предприятии числятся сотрудники, каждый из которых получает зарплату в соответчики с должностью. Для рекламного проспекта о поиске персонала вы хотите уточнить, на какую среднюю зарплату может рассчитывать соискатель. Вы знаете, что:

Для вычисления средней зарплаты на предприятии вам потребуется ввести в ячейки значений размер зарплаты, а в ячейки весов — количество сотрудников, которые ее получают. Калькулятор мгновенно выдаст вам результат, что средняя зарплата равна 19 540 рублей. Без учета веса значений среднее арифметическое было бы равно 22 666 рублей, что уже значительно отличается от взвешенного значения.

Средняя скорость автомобиля

Допустим, водитель ехал по городскому проспекту со скоростью 60 км/ч в течение одного часа. Затем он выехал на автомагистраль, увеличил скорость до 120 км/ч и ехал так три часа, после чего свернул на проселочную дорогу и со скоростью 40 км/ч ехал еще два часа. С какой средней скоростью ехал водитель? В этой задаче в качестве веса выступает время езды, а значений — скорость. Выглядит это так:

Введем эти данные в форму калькулятора и получим ответ: средняя скорость автомобиля составляет 83,3 км/ч. Без учета времени средняя скорость была бы равна 73,3 км/ч, что неверно.

Заключение

Расчет среднего арифметического взвешенного широко используется в прикладных науках. Этот параметр популярен не только в статистике, но и в физике, экономике или финансах. Используйте наши калькуляторы в качестве помощника для решения практических и теоретических задач.

Источник

Расчет средневзвешенного значения в Excel

Основная идея

Предположим, что мы с вами сидим в приемно-экзаменационной комиссии и оцениваем абитуриентов, которые хотят поступить в наш ВУЗ. Оценки по различным предметам у наших кандидатов следующие:

Средневзвешенный показатель как рассчитать. Смотреть фото Средневзвешенный показатель как рассчитать. Смотреть картинку Средневзвешенный показатель как рассчитать. Картинка про Средневзвешенный показатель как рассчитать. Фото Средневзвешенный показатель как рассчитать

На первый взгляд кажется, что лучше всех подходит Иван, т.к. у него средний бал максимальный. Но тут мы вовремя вспоминаем, что факультет-то наш называется «Программирование», а у Ивана хорошие оценки только по рисованию, пению и прочей физкультуре, а по математике и информатике как раз не очень. Возникает вопрос: а как присвоить нашим предметам различную важность (ценность), чтобы учитывать ее при расчете среднего? И вот тут на помощь приходит средневзвешенное значение.

В бизнесе средневзвешенное часто используется в таких задачах, как:

Расчет средневзвешенного формулами

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

Средневзвешенный показатель как рассчитать. Смотреть фото Средневзвешенный показатель как рассчитать. Смотреть картинку Средневзвешенный показатель как рассчитать. Картинка про Средневзвешенный показатель как рассчитать. Фото Средневзвешенный показатель как рассчитать

Так что берем Машу, а Иван пусть поступает в институт физкультуры 😉

Расчет средневзвешенного в сводной таблице

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

Средневзвешенный показатель как рассчитать. Смотреть фото Средневзвешенный показатель как рассчитать. Смотреть картинку Средневзвешенный показатель как рассчитать. Картинка про Средневзвешенный показатель как рассчитать. Фото Средневзвешенный показатель как рассчитать

Заметьте, что цена на один и тот же товар может различаться. Наша задача: рассчитать средневзвешенные цены для каждого товара. Следуя той же логике, что и в предыдущем пункте, например, для земляники, которая продавалась 3 раза, это должно быть:

=(691*10 + 632*12 + 957*26)/(10+12+26) = 820,33

То есть мы суммируем стоимости всех сделок (цена каждой сделки умножается на количество по сделке) и потом делим получившееся число на общее количество этого товара.

Правда, с реализацией этой нехитрой логики именно в сводной таблице нас ждет небольшой облом. Если вы работали со сводными раньше, то, наверное, помните, что можно легко переключить поле значений сводной в нужную нам функцию, щелкнув по нему правой кнопкой мыши и выбрав команду Итоги по (Summarize Values By) :

Средневзвешенный показатель как рассчитать. Смотреть фото Средневзвешенный показатель как рассчитать. Смотреть картинку Средневзвешенный показатель как рассчитать. Картинка про Средневзвешенный показатель как рассчитать. Фото Средневзвешенный показатель как рассчитать

В этом списке есть среднее, но нет средневзвешенного 🙁

Можно частично решить проблему, если добавить в исходную таблицу вспомогательный столбец, где будет считаться стоимость каждой сделки:

Средневзвешенный показатель как рассчитать. Смотреть фото Средневзвешенный показатель как рассчитать. Смотреть картинку Средневзвешенный показатель как рассчитать. Картинка про Средневзвешенный показатель как рассчитать. Фото Средневзвешенный показатель как рассчитать

Средневзвешенный показатель как рассчитать. Смотреть фото Средневзвешенный показатель как рассчитать. Смотреть картинку Средневзвешенный показатель как рассчитать. Картинка про Средневзвешенный показатель как рассчитать. Фото Средневзвешенный показатель как рассчитать

В общем, как-то все неудобно, трудоемко и нагоняет тоску. Да еще и дополнительный столбец в исходных данных нужно руками делать. Но красивое решение есть.

Расчет средневзвешенного в сводной таблице с помощью Power Pivot и языка DAX

2. Затем щелкните мышью в строку формул и введите туда формулу для расчета средневзвешенного:

Средневзвешенный показатель как рассчитать. Смотреть фото Средневзвешенный показатель как рассчитать. Смотреть картинку Средневзвешенный показатель как рассчитать. Картинка про Средневзвешенный показатель как рассчитать. Фото Средневзвешенный показатель как рассчитать

Несколько нюансов по формуле:

Средневзвешенный показатель как рассчитать. Смотреть фото Средневзвешенный показатель как рассчитать. Смотреть картинку Средневзвешенный показатель как рассчитать. Картинка про Средневзвешенный показатель как рассчитать. Фото Средневзвешенный показатель как рассчитать

Источник

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *