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

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

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

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

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

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

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

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

1-ая часть нашей формулы – это функция СУММПРОИЗВ (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. В этот момент врубается 2-ая часть формулы: /СУММ(C2:C9) либо /SUM(C2:C9), которая возвращает итог в обычный спектр оценок, давая ответ 83,6.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Направьте внимание, что я преобразовал ее в «умную» таблицу при помощи команды Основная — Форматировать как таблицу (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. А что если средневзвешенная стоимость? В данной нам статье я представлю способ расчета средневзвешенного значения, также способ расчета средневзвешенного значения при соблюдении определенных критериев в Excel.

Просто группируйте столбцы среднего / сумм / подсчета, если они соответствуют аспектам в другом столбце

Kutools for Excel гордится сильной пользой Расширенные ряды комбинирования который может посодействовать для вас стремительно соединить строчки с аспектами в одном столбце и сразу усреднить / сумму / количество / макс / мин / продукт в остальных столбцах. Полнофункциональная бесплатная 30-дневная пробная версия!

• Повторное внедрение что угодно: Добавляйте более нередко применяемые либо сложные формулы, диаграммы и все другое в избранное и стремительно используйте их в будущем.
• Наиболее 20 текстовых функций: Извлечь число из текстовой строчки; Извлечь либо удалить часть текстов; Преобразование чисел и валют в английские слова.
• Инструменты слияния : Несколько книжек и листов в одну; Соединить несколько ячеек / строк / столбцов без утраты данных; Соедините повторяющиеся строчки и сумму.
• Поделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книжки в несколько файлов Excel, PDF либо CSV; От 1-го столбца к нескольким столбцам.
• Вставить пропуск Сокрытые / отфильтрованные строчки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электрические письма нескольким получателям массово.
• Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к хоть каким листам; Сортировать по неделькам, денькам, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
• Наиболее 300 массивных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Обычное развертывание на вашем предприятии либо в организации.

Умопомрачительный! Внедрение действенных вкладок в Excel, таковых как Chrome, Firefox и Safari!

Сберегайте 50% собственного времени и сокращайте тыщи щелчков мышью любой денек!

Высчитать средневзвешенное значение в Excel

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

Шаг 1. Изберите пустую ячейку, гласит «Ячейка F2», введите формулу =SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18) в него и нажмите Enter ключ.

Внимание: В формуле = СУММПРОИЗВ (C2: C18, D2: D18) / СУММ (C2: C18), C2: C18 столбец «Вес», D2: D18 — это столбец «Стоимость», и вы сможете поменять оба значения в зависимости от ваших потребностей.

Шаг 2. Средневзвешенная стоимость может включать очень много десятичных символов. Чтоб поменять десятичные разряды, вы сможете избрать ячейку, а потом щелкнуть Прирастить десятичную дробь клавиша or Уменьшить десятичный клавиша на Основная меню.

Высчитать средневзвешенное значение при согласовании данным аспектам в Excel

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

Шаг 1. Изберите пустую ячейку, к примеру «Ячейка F8», введите формулу =SUMPRODUCT((B2:B18=»Apple»)*C2:C18*D2:D18)/SUMIF(B2:B18,»Apple»,C2:C18) в него и нажмите Enter ключ.

Внимание: В формуле = СУММПРОИЗВ ((B2: B18 = «Яблоко») * C2: C18 * D2: D18) / СУММЕСЛИ (B2: B18, «Яблоко», C2: C18), B2: B18 столбец «Фрукты», C2: C18 столбец «Вес», D2: D18 столбец «Стоимость» «Apple«- это определенные аспекты, по которым вы желаете высчитать средневзвешенное значение, и вы сможете поменять их в зависимости от ваших потребностей.

Шаг 2. Изберите ячейку с данной нам формулой, а потом щелкните значок Прирастить десятичную дробь клавиша or Уменьшить десятичный клавиша на Основная Вкладка для конфигурации символов опосля запятой средневзвешенной цены яблока.