Как найти средневзвешенное значение в excel - Сайт, где вы сможете решить свои вопросы

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

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

Свободное место, допустим, только одно, и наша задача – выбрать достойного.

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

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

Средневзвешенное – это среднее с учетом различной ценности (веса, важности) каждого из элементов.

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

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

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

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

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

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

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

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

Обратите внимание, что я преобразовал ее в “умную” таблицу с помощью команды Главная – Форматировать как таблицу (Home – Format as Table) и дал ей на вкладке Конструктор (Design) имя Data.

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

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

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

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

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

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

Теперь можно рядом закинуть в область значений стоимость и количество – и мы получим почти то, что требуется:

Останется поделить одно на другое, но сделать это, вроде бы, простое математическое действие внутри сводной не так просто. Придется либо добавлять в сводную вычисляемое поле (вкладка Анализ – Поля, элементы, наборы – Вычисляемое поле), либо считать обычной формулой в соседних ячейках или привлекать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA), о которой я уже писал. А если завтра изменятся размеры сводной (ассортимент товаров), то все эти формулы придется вручную корректировать.

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

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

Если у вас Excel 2013-2016, то в него встроен супермощный инструмент для анализа данных – надстройка Power Pivot, по сравнению с которой сводные таблицы с их возможностями – как счеты против калькулятора. Если у вас Excel 2010, то эту надстройку можно совершенно бесплатно скачать с сайта Microsoft и тоже себе установить. С помощью Power Pivot расчет средневзвешенного (и других невозможных в обычных сводных штук) очень сильно упрощается.

1. Для начала, загрузим нашу таблицу в Power Pivot. Это можно сделать на вкладке Power Pivot кнопкой Добавить в модель данных (Add to Data Model). Откроется окно Power Pivot и в нем появится наша таблица.

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

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

В Power Pivot есть свой встроенный язык с набором функций, инструментов и определенным синтаксисом, который называется DAX. Так что можно сказать, что эта формула – на языке DAX.
Здесь WA – это название вычисляемого поля (в Power Pivot они еще называются меры), которое вы придумываете сами (я называл WA, имея ввиду Weighted Average – “средневзвешенное” по-английски).
Обратите внимание, что после WA идет не равно, как в обычном Excel, а двоеточие и равно.
При вводе формулы будут выпадать подсказки – используйте их.
После завершения ввода формулы нужно нажать Enter, как и в обычном Excel.

3. Теперь строим сводную. Для этого в окне Power Pivot выберите на вкладке Главная – Сводная таблица (Home – Pivot Table). Вы автоматически вернетесь в окно Excel и увидите привычный интерфейс построения сводной таблицы и список полей на панели справа. Осталось закинуть поле Наименование в область строк, а нашу созданную формулой меру WA в область значений – и задача решена:

Вот так – красиво и изящно.

Общая мораль: если вы много и часто работаете со сводными таблицами и вам их возможности “тесноваты” – копайте в сторону Power Pivot и DAX – и будет вам счастье!

Ссылки по теме

Что умеет функция СУММПРОИЗВ на самом деле
Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Настройка вычислений в сводных таблицах

Источник

Средневзвешенное значение (средняя взвешенная) в отличие от среднего значения учитывает вес (важности) каждого из элементов совокупности.

Как в программе Эксель (Excel) можно сделать расчёт средней взвешенной?

Какую формулу нужно написать?

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

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

В этой формуле

x1, …, xn – являются значениями исследуемых праметров (ценой, курсом валют и пр.), а

w1, …, wn – вес каждого из этих праметров.

Фомула читается следующим образом:

средневзвешенное значение равно частному суммы произведений всех показателей на их вес и суммы всех весов этих показателей.

В программе Excel для подсчета средневзвешенного значения можно использовать две функции:

СУММ И СУММПРОИЗВ.

система выбрала этот ответ лучшим

Ксарфакс
[156K]

3 года назад

Средневзвешенное значение можно посчитать по следующей формуле:

Здесь:

x1, x2, x3, …, xn – это значения (цена, оценка, курс валюты и т.д.).

w1, w2, w3, …, wn – вес каждого значения.

То есть нужно каждое значение умножить на его вес и просуммировать все произведения, а затем разделить всё это на сумму всех весов.

В программе Excel нет специальной функции, с помощью которой можно посчитать средневзвешенное значение.

Для его расчёта можно использовать комбинацию из 2 других функций: СУММПРОИЗВ и СУММ.

Вот несложный пример расчёта средневзвешенного значения в Excel:

Есть несколько товаров, которые имеют разную цену. За 1 день было продано сколько-то штук каждого из товаров. Нужно посчитать средневзвешенную цену продажи.

1) Составляем таблицу.

Здесь всё понятно – пишем название товара, рядом – соответствующее количество и цену.

2) Теперь ставим курсор в ту ячейку, куда нужно записать формулу для средневзвешенного.

Переходим на вкладку панели инструментов “Формулы”, там нажимаем на “Математические” и в списке выбираем “СУММПРОИЗВ”.

В качестве первого аргумента этой функции указываем столбец с количеством (С), в качестве второго аргумента указываем столбец с ценой (D).

3) Теперь в строке формул после функции СУММПРОИЗВ ставим символ деления (/) и добавляем функцию СУММ.

Здесь достаточно указать в 1 аргументе столбец с количеством (С).

4) В результате получится средневзвешенная цена.

Чёрная Луна
[265K]

2 года назад

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

Допустим, на рисунке в ячейках указаны цены на товар за 30 дней. В течении этого срока она изменялась.

В ячейки В15 содержится формула, которая использует функцию

СРЗНАЧ: =СРЗНАЧ(B4:B13)

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

А вот формула, которая находится в ячейке В16 рассчитывает то, что нам необходимо:

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

Vodila
[16.6K]

более года назад

Во – первых нужно вспомнить, а что такое это среднее взвешенное значение – это сумма произведений конкретных значений на соответствующие им веса.

Для того чтобы в программе Excel посчитать средневзвешенное значение можно сделать следующим образом: ввести формулу =СУММПРОИЗВ(Х1;Х2)/СУММ(Х2), где у нас Х1 – это будет диапазон данных с показателем; а Х2 – это вес этого показателя.

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

isa-isa
[73.9K]

3 года назад

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

Выделим группу, в которой ищем средневзвешенное значение и правой кнопкой выберем “счетесли”, далее “диапазон”, далее “критерий”. Закрепляем все. У нас появится средневзвешенное для заданной группы. Осталось протянуть мышкой вниз и получим перечень весов.

Теперь надо рассчитать среднеарифметическое взвешенное, в рядке “итого” выбираем “суммпроизведений”, далее закрепим все интересующие нас массивы данных “массив1”, “массив2”, “массив3” и соответствующие им веса, закрепляем, теперь делим все на сумму весов, ставим знак делить, “сумм” и выделяем нужные веса, получаем средневзвешенное.

Пользоваться формулами особо смысла нет, т.к. все они уже внесены в программу, но если есть желание, то можно по этой формуле считать. В ней x 1,2,3 и т.д. – это значения, а w 1,2,3 и т.д. – это вес.

Смотрите обучающее видео как считать средневзвешенное в Excel прямо на сайте большой вопрос

Krustall
[125K]

более года назад

В Excel довольно часто приходится рассчитывать не просто среднее значение, а средневзвешенное, это средневзвешенное значение представляет собой сумму произведений отдельных значений и их соответствующих весов.

Выбираем группу, в которой ищем средневзвешенное значение и правой кнопкой мыши выбираем “счетесли”, зптем “диапазон”, затем «критерийа». Теперь все сохраняем. У нас будет средневзвешенное значение для данной группы. Осталось перетащить мышь вниз и получить список весов.

Теперь нам нужно вычислить взвешенное среднее арифметическое, в строке “итого” выбираем “суммы произведений”, затем фиксируем все интересующие нас массивы данных “массив1”, “массив2”, “массив3” и соответствующие веса , фиксируем, теперь делим все на сумму весов, ставим знак делим, “суммы” и выбираем нужные веса, получим средневзвешенное.

Nastya Chuk
[6.8K]

3 года назад

добрый день! Актуальный вопрос на самом деле, поскольку математических функций в экселе множество и не всегда знаешь как найти ту или иную формулу. Итак,Чтобы в ексель посчитать нам средневзвешенное значение стоит воспользоваться следующими 2 функциями – это “=СУММПРОИЗВ(Х1;Х2)/СУММ(Х2)”, где х1 – это будет диапазон данных с нашим заданным показателем и

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

-Lava-
[59.3K]

3 года назад

Не смотря на своё удобство в работе программы Эксель, рассчета средневзвешенного значения в программе нет.

Для расчета средневзвешенного значения в Экселе можно воспользоваться двумя функциями СУММ И СУММПРОИЗВ.

Для этого выбираем вкладку “файл”, далее “формулы” и “математические”.

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

В формуле за x берём значениями исследуемых параметров, а w вес каждого параметра.

Марина Вологда
[295K]

9 месяцев назад

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

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

Приведем пример, как высчитать в Эксель с данными в таблице.

Лара Изюминка
[59.8K]

3 года назад

Чтобы в ексель посчитать средневзвешенное значение стоит воспользоваться следующими двумя функциями: =СУММПРОИЗВ(Х1;Х2)/СУММ(Х2), где у нас Х1 – это будет диапазон данных с показателем;

а Х2 – это вес данного показателя. Выделять ячейки лучше мышкой и воспользоваться мастером функций, так будет удобнее, чем набирать вручную.

Знаете ответ?

Источник

Excel для Microsoft 365 для Mac Excel 2021 для Mac Excel 2019 для Mac Excel 2016 для Mac Excel для Mac 2011 Еще…Меньше

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

Чтобы научиться вычислять средние значения, используйте предоставленные образцы данных и описанные ниже процедуры.

Копирование примера данных

Чтобы лучше понять описываемые действия, скопируйте пример данных в ячейку A1 пустого листа.

Создайте пустую книгу или лист.

Выделите приведенный ниже образец данных.

Примечание: Не выделяйте заголовки строк или столбцов (1, 2, 3… A, B, C…) при копировании данных примера на пустой лист.

Выбор примеров данных в справке

Качество изделия	Цена за единицу	Количество заказанных изделий
10	35	500
7	25	750
9	30	200
10	40	300
8	27	400
5	20	200
	Среднее качество изделий
	Средняя цена изделия
	Среднее качество всех изделий с оценкой качества выше 5

Нажмите клавиши +C.
Выделите на листе ячейку A1, а затем нажмите клавиши +V.

Расчет простого среднего значения

Выделите ячейки с A2 по A7 (значения в столбце “Качество изделия”).
На вкладке Формулы щелкните стрелку рядом с кнопкой Автоумма и выберитесреднее значение .

Результат — 8,166666667.

Расчет среднего для несмежных ячеек

Выберите ячейку, в которой должно отображаться среднее значение, например ячейку A8, которая находится слева ячейки с текстом “Среднее качество изделия” в примере данных.
На вкладке Формулы щелкните стрелку рядом с кнопкой Автоумма кнопку Среднее инажмите клавишу RETURN.
Щелкните ячейку, которая содержит только что найденное среднее значение (ячейка A8 в этом примере).

Если используется образец данных, формула отображается в строка формул, =СС00(A2:A7).
В строке формул выделите содержимое между скобками (при использовании примера данных — A2:A7).
Удерживая нажатой клавишу , щелкните ячейки, для чего нужно вычесть среднее значение, и нажмите клавишу RETURN. Например, выберите A2, A4 и A7 и нажмите клавишу RETURN.

Выделенная ссылка на диапазон в функции СРЗНАЧ заменится ссылками на выделенные ячейки. В приведенном примере результат будет равен 8.

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

В приведенном ниже примере рассчитывается средняя цена за изделие по всем заказам, каждый из которых содержит различное количество изделий по разной цене.

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

Совет: Чтобы быстро найти функцию, начните вводить ее имя в поле Поиск функции. Например, начните вводить СУММПРОИЗВ.
Щелкните поле рядом с надписью массив1 и выделите на листе ячейки с B2 по B7 (значения в столбце “Цена за единицу”).
Щелкните поле рядом с надписью массив2 и выделите на листе ячейки с C2 по C7 (значения в столбце “Количество заказанных изделий”).
В строке формул установите курсор справа от закрывающей скобки формулы и введите /

Если строка формул не отображается, в меню Вид выберите пункт Строка формул.
В списке построителя формул дважды щелкните функцию СУММ.
Выделите диапазон в поле число1, нажмите кнопку DELETE и выделите на листе ячейки с C2 по C7 (значения в столбце “Количество изделий”).

Теперь в строке формул должна содержаться следующая формула: =СУММПРОИЗВ(B2:B7;C2:C7)/СУММ(C2:C7).
Нажмите клавишу RETURN.

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

Расчет среднего, исключающего определенные значения

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

Выделите ячейку A10, расположенную слева от ячейки с текстом “Среднее качество всех изделий с оценкой качества выше 5”.
На вкладке Формулы нажмите кнопку Вставить функцию, чтобы открыть панель Построитель формул.
В списке построителя формул дважды щелкните функцию СРЗНАЧЕСЛИ.

Совет: Чтобы быстро найти функцию, начните вводить ее имя в поле Поиск функции. Например, начните вводить СРЗНАЧЕСЛИ.
Щелкните поле рядом с надписью диапазон и выделите на листе ячейки с A2 по A7 (значения в столбце “Цена за единицу”).
Щелкните поле рядом с надписью условие и введите выражение “>5”.
Нажмите клавишу RETURN.

Такая формула исключит значение в ячейке A7 из расчета. В результате будет получено среднее качество изделий, равное 8,8.

Совет: Чтобы использовать функцию СРЗНАЧЕСЛИ для расчета среднего без нулевых значений, введите выражение “<>0” в поле условие.