Расчет средневзвешенного значения в Excel (с внедрением формул)
Расчет средневзвешенного значения в Excel (с внедрением формул)
Когда мы вычисляем обычное среднее для данного набора значений, предполагается, что все значения имеют однообразный вес либо значимость.
К примеру, если вы явитесь на экзамены, и все экзамены имеют однообразный вес, то среднее значение ваших общих оценок также будет средневзвешенным значением ваших оценок.
Но в настоящей жизни это не так.
Некие задачки постоянно важнее остальных. Некие экзамены важнее остальных.
И вот где Средневзвешенное заходит в картину.
Вот определение средневзвешенного значения из учебника:
Сейчас давайте поглядим, как рассчитать средневзвешенное значение в Excel.
Расчет средневзвешенного значения в Excel
В этом руководстве вы узнаете, как рассчитать средневзвешенное значение в Excel:
- Внедрение функции СУММПРОИЗВ.
- При помощи функции СУММ.
Расчет средневзвешенного значения в Excel — функция СУММПРОИЗВ
Могут быть различные сценарии, когда для вас необходимо рассчитать средневзвешенное значение. Ниже приведены три разных ситуации, в которых вы сможете употреблять функцию СУММПРОИЗВ для расчета средневзвешенного значения в Excel.
Ниже приведены три разных ситуации, в которых вы сможете употреблять функцию СУММПРОИЗВ для расчета средневзвешенного значения в Excel.
Пример 1 — Когда веса в сумме составляют 100%
Представим, у вас есть набор данных с оценками, приобретенными студентом на различных экзаменах, также весами в процентах (как показано ниже):
В приведенных выше данных учащийся получает оценки по различным оценкам, но, в конце концов, ему необходимо отдать окончательный балл либо оценку. Тут нереально рассчитать обычное среднее значение, так как значимость различных оценок различается.
К примеру, викторина с весом 10% имеет в два раза больший вес по сопоставлению с заданием, но на одну четверть веса по сопоставлению с экзаменом.
В таком случае вы сможете употреблять функцию СУММПРОИЗВ, чтоб получить средневзвешенное значение оценки.
Вот формула, которая даст для вас средневзвешенное значение в Excel:
Ах так работает эта формула: Функция СУММПРОИЗВ в Excel множит 1-ый элемент первого массива на 1-ый элемент второго массива. Потом он множит 2-ой элемент первого массива на 2-ой элемент второго массива. И так дальше…
И, в конце концов, он складывает все эти значения.
Вот иллюстрация, чтоб прояснить это.
Пример 2 — Когда веса не добавляют до 100%
В приведенном выше случае веса были присвоены таковым образом, чтоб общая сумма составляла 100%. Но в настоящих актуальных сценариях это быть может не постоянно.
Давайте поглядим на один и этот же пример с различным весом.
В приведенном выше случае веса в сумме составляют 200%.
Если я воспользуюсь той же формулой СУММПРОИЗВ, итог будет неправильным.
В приведенном выше итоге я удвоил все веса, и он возвращает средневзвешенное значение как 153,2. Сейчас мы знаем, что студент не может набрать больше 100 баллов из 100, независимо от того, как он неплох.
Причина в том, что веса не составляют в сумме 100%.
Вот формула, по которой это будет отсортировано:
В приведенной выше формуле итог СУММПРОИЗВ делится на сумму всех весов. Как следует, невзирая ни на что, вес постоянно будет составлять 100%.
Одним из практических примеров разных весов является расчет предприятиями средневзвешенной цены капитала. К примеру, если компания завлекла капитал при помощи заемных средств, собственного капитала и привилегированных акций, то они будут обслуживаться по иной стоимости. Потом бухгалтерия компании рассчитывает средневзвешенную стоимость капитала, которая представляет собой стоимость капитала для всей компании.
Пример 3 — Когда нужно рассчитать веса
В рассмотренном выше примере веса были указаны. Но могут быть случаи, когда веса не доступны впрямую, и для вас необходимо поначалу вычислить веса, а потом вычислить средневзвешенное значение.
Представим, вы реализуете три различных типа товаров, как обозначено ниже:
Вы сможете рассчитать средневзвешенную стоимость за продукт при помощи функции СУММПРОИЗВ. Вот формула, которую вы сможете употреблять:
Разделение результата СУММПРОИЗВ на СУММ количеств гарантирует, что вес (в данном случае количества) в сумме составит 100%.
Расчет средневзвешенного значения в Excel — функция СУММ
Хотя функция СУММПРОИЗВ является наилучшим методом вычисления средневзвешенного значения в Excel, вы также сможете употреблять функцию СУММ.
Чтоб вычислить средневзвешенное значение при помощи функции СУММ, для вас нужно помножить любой элемент на присвоенную ему значимость в процентах.
Используя этот же набор данных:
Вот формула, которая даст для вас верный итог:
Этот способ можно употреблять, когда у вас есть пара предметов. Но когда у вас много предметов и весов, этот способ быть может массивным и подверженным ошибкам. Есть наиболее маленький и наилучший метод создать это при помощи функции СУММ.
Продолжая с этим же набором данных, вот маленькая формула, которая даст для вас средневзвешенное значение с внедрением функции СУММ:
Уловка при использовании данной для нас формулы состоит в том, чтоб употреблять Control + Shift + Enter, а не попросту Enter. Так как функция СУММ не может обрабатывать массивы, для вас необходимо употреблять Control + Shift + Enter.
Когда вы нажмете Control + Shift + Enter, вы увидите, что фигурные скобки автоматом покажутся в начале и в конце формулы (см. Строчку формул на изображении выше).
Снова же, удостоверьтесь, что вес составляет 100%. Если это не так, для вас необходимо поделить итог на сумму весов (как показано ниже на примере продукта):
Как в excel рассчитать средневзвешенное значение
Функция Excel СРЗНАЧ вычисляет среднее значение набора данных. Нередко юзерам необходимо рассчитать средневзвешенное. Вы сможете издержать целый денек на поиски, но не отыщите функцию, позволяющую создать таковой расчет. Можно, но, рассчитывать средневзвешенные, создав формулу, которая употребляет функции СУММПРОИЗВ и СУММ.
На рис. 96.1 показан обыденный лист, который содержит цены на бензин в течение 30 дней. К примеру, стоимость составляла $2,48 1-ые 5 дней. Потом она снизилась до $2,41 в течение 2-ух дней. Дальше стоимость еще снизилась до $2,39 в течение 3-х дней и т. д.
Рис. 96.1. Формула в ячейке В16 вычисляет средневзвешенную стоимость на бензин
Ячейка В15 содержит формулу, которая употребляет функцию СРЗНАЧ: =СРЗНАЧ(B4:B13) . Если вы проанализируете ее, то увидите, что формула не возвращает четкий итог. Быстрее, цены должны быть взвешены по фактору количества дней, в течение которых любая стоимость была в силе. Иными словами, средневзвешенное является подходящим типом расчета. Последующая формула, обозначенная в ячейке В16 , делает то, что необходимо: =СУММПРОИЗВ(B4:B13;C4:C13)/СУММ(C4:C13) .
Эта формула множит каждую стоимость на соответственное количество дней, а потом складывает все, что вышло. Итог делится на количество дней. Вы сможете просто переработать эту формулу для остальных видов расчета средневзвешенного.
Взвешенное среднее значение
В этом курсе:
Примечание: Мы стараемся как можно оперативнее обеспечивать вас животрепещущими справочными материалами на вашем языке. Эта страничка переведена автоматом, потому ее текст может содержать некорректности и грамматические ошибки. Для нас принципиально, чтоб эта статья была для вас полезна. Просим вас уделить пару секунд и сказать, посодействовала ли она для вас, при помощи клавиш понизу странички. Для удобства также приводим ссылку на оригинал (на британском языке).
Обычно при вычислении среднего значения все числа задаются равным значимости; числа добавляются сразу, а потом поделяются на количество чисел. При использовании взвешенного среднего значения одному либо нескольким числам присваивается наиболее важное значение либо толщина.
Вычисление среднего взвешенного значения
Используйте функции СУММПРОИЗВ и сумм, чтоб отыскать взвешенное среднее значение, которое зависит от веса, примененного к значениям.
К примеру, отгрузка 10 вариантов Карандашов составляет 20 центов на любой вариант. Но 2-ая отгрузка 40 баксов на один вариант, так как карандашы имеют высочайший спрос. Если средние издержки для каждой отгрузки таковым образом (0,20 + 0.30)/2 = 0,25, итог будет неточным.
Математический ввод не учитывает, что количество коробок составляет наиболее 30 центов, чем 20 центов. Чтоб получить правильное среднее значение, используйте эту формулу для получения результата (28 центов на каждую отгрузку).
Формула делает деление общей цены 2-ух заказов на полное количество заказанных коробок.
Средневзвешенная стоимость в MS EXCEL
Рассчитаем средневзвешенную стоимость продукта без использования вспомогательных столбцов. Также рассчитаем средневзвешенную стоимость с условием.
Пусть дана таблица продаж партий 1-го продукта (см. файл примера ). В каждой партии обозначено количество проданного продукта (столбец А) и его стоимость (столбец В).
Найдем средневзвешенную стоимость. В отличие от средней цены, вычисляемой по формуле СРЗНАЧ(B2:B8) , в средневзвешенной учитываются «вес» каждой цены (в нашем случае в качестве веса выступают значения из столбца Количество). Т.е. если продали одну крупную партию продукта по весьма низкой стоимости (строчка 2), а остальные маленькие партии по высочайшей, то не глядя, что средняя стоимость будет высочайшей, средневзвешенная стоимость будет смещена в сторону малой стоимости.
Средневзвешенная стоимость рассчитывается по формуле.
=СУММПРОИЗВ(B2:B8;A2:A8)/СУММ(A2:A8)
Если в столбце «весов» (А) будут содержаться схожие значения, то средняя и средневзвешенная цены совпадут.
Средневзвешенная стоимость с условием
Если требуется отыскать средневзвешенную стоимость не для всего массива данных, а для определенной группы значений, то формула усложнится. Покажем на примере.
Пусть имеется таблица партий продукта от различных поставщиков.
Формула для вычисления средневзвешенной цены для Поставщика1:
К аргументам функции СУММПРОИЗВ() добавился 3-й аргумент: —($B$7:$B$13=B17)
Если выделить это выражение и надавить F9, то получим массив . Т.е. значение 1 будет лишь в строчках, у каких в столбце поставщик указан Поставщик1. Сейчас сумма произведений не будет учесть цены от другого поставщика, т.к. будут умножены на 0. Сумма весов для Поставщика1 рассчитывается по формуле СУММЕСЛИ($B$7:$B$13;B17;$D$7) .
Решение приведено в файле примера на листе Пример2 .
Как посчитать средневзвешенное значение в Excel? Какая нужна формула?
Средневзвешенное значение (средняя взвешенная) в отличие от среднего значения учитывает вес (значимости) всякого из частей совокупы.
Как в программке Эксель (Excel) можно создать расчёт средней взвешенной?
Какую формулу необходимо написать?
Средневзвешенное значение можно посчитать по последующей формуле:
x1, x2, x3, . xn — это значения (стоимость, оценка, курс валюты и т.д.).
w1, w2, w3, . wn — вес всякого значения.
Другими словами необходимо каждое значение помножить на его вес и просуммировать все произведения, а потом поделить всё это на сумму всех весов.
В программке Excel нет специальной функции, при помощи которой можно посчитать средневзвешенное значение.
Для его расчёта можно употреблять комбинацию из 2 остальных функций: СУММПРОИЗВ и СУММ.
Вот легкий пример расчёта средневзвешенного значения в Excel:
Есть несколько продуктов, которые имеют разную стоимость. За 1 денек было продано сколько-то штук всякого из продуктов. Необходимо посчитать средневзвешенную стоимость реализации.
1) Составляем таблицу.
Тут всё понятно — пишем заглавие продукта, рядом — соответственное количество и стоимость.
2) Сейчас ставим курсор в ту ячейку, куда необходимо записать формулу для средневзвешенного.
Перебегаем на вкладку панели инструментов «Формулы», там жмем на «Математические» и в перечне избираем «СУММПРОИЗВ».
В качестве первого аргумента данной для нас функции указываем столбец с количеством (С), в качестве второго аргумента указываем столбец с ценой (D).
3) Сейчас в строке формул опосля функции СУММПРОИЗВ ставим знак деления (/) и добавляем функцию СУММ.
Тут довольно указать в 1 аргументе столбец с количеством (С).
4) В итоге получится средневзвешенная стоимость.
Расчет средневзвешенной процентной ставки ранца в Excel
Средневзвешенное значение употребляется для усреднения статистически значений, которые имеют различные (огромные либо наименьшие) значения в наборе данных.
Пример формулы для расчета средневзвешенной процентной ставки в Excel
Допустим нам необходимо выяснить средневзвешенную процентную ставку вкладывательного ранца. Ниже на рисунке представлен начальный полный вкладывательный портфель. Для каждой инвестиции указывается ее значение и процентная ставка доходности. Допустим нам нужно найти общую процентную ставку доходности для всего вкладывательного ранца. Чтоб найти уровень доходности ранца в процентах используем последующую формулу:
С целью вычисления средневзвешенной процентной ставки толика для всякого вкладывательного объекта в общей цены ранца множится на процентную ставку доходности. Функция СУММПРОИЗВ совершенно подступает для перемножения 2-ух наборов данных (массивов) с следующим суммированием результатов. Функция может иметь наибольшее количество аргументом до 255, разбитых точкой с запятой. Но в данной формуле нужно употреблять лишь только 2 аргумента.
В первом аргументе указаны цены всех инвестиций, разбитых на их сумму, что дает 5 процентных значений, представляющих вес каждой инвестиции в ранце. На фонд «Pioneer Акции Восточной Европы» приходиться толика 17%, которая была вычислена в итоге деления сумм 72021,35 на 423 655,02. 2-ой аргумент функции содержит процентные ставки доходности по каждой инвестиции. Функция СУММПРОИЗВ множит любой элемент с первого аргумента на соответственный элемент со второго аргумента. Элемент B2/B7 множится на C2, элемент B3/B7 на C3 и т.д. Опосля перемножения всех 5 частей функция суммирует результаты.
Если б для вычисления средней процентной ставки доходности была просто применена функция СРЗНАЧ, в итоге ее вычислений мы получили бы значение 5,906%. Это по сути наименьшее значение чем показатель средневзвешенной процентной ставки ранца. К примеру, инвестиция «Фонд Казна Top Brands» имеет большенный процент доходности, как и огромную долю в вкладывательном ранце чем остальные позиции.
Как рассчитать средневзвешенную процентную ставку в Excel пошагово
В выше приведенном примере все вычислительные операции реализованы при помощи функции СУММПРОИЗВ в процессе расчета средневзвешенного показателя. Эти вычисления могут быть реализованы и при помощи обычных функций, записанных в прилегающих примыкающих ячейках. Ниже на рисунке изображены те же самые вычисления что и в прошлом примере, но заместо функции СУММПРОИЗВ употребляется несколько другой способ. В отдельных ячейках рассчитана толика в ранце для каждой отдельной инвестиции, опосля что вычислено воздействие всякого процента доходности на итоговый итог, а в конце все результаты суммируются обыкновенной функцией СУММ.
Программка Excel является самым всепригодным аналитическим инвентарем, который отлично подступает для выполнения статистических анализов данных. Для этого Excel располагает огромным арсеналом особых статистических функций. Дальше разглядим формулы служащие для анализа статистических данных, к примеру, для их усреднения, сегментирования либо вычисления графиков частот.
