Содержание

Расчет средневзвешенной процентной ставки портфеля в Excel

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

Пример формулы для расчета средневзвешенной процентной ставки в Excel

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

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

В первом аргументе указаны стоимости всех инвестиций, разделенных на их сумму, что дает пять процентных значений, представляющих вес каждой инвестиции в портфеле. На фонд «Pioneer Акции Восточной Европы» приходиться доля 17%, которая была вычислена в результате деления сумм 72021,35 на 423 655,02. Второй аргумент функции содержит процентные ставки доходности по каждой инвестиции. Функция СУММПРОИЗВ умножает каждый элемент с первого аргумента на соответствующий элемент со второго аргумента. Элемент B2/B7 умножается на C2, элемент B3/B7 на C3 и т.д. После перемножения всех пяти элементов функция суммирует результаты.

Если бы для вычисления средней процентной ставки доходности была просто использована функция СРЗНАЧ, в результате ее вычислений мы получили бы значение 5,906%. Это на самом деле меньшее значение чем показатель средневзвешенной процентной ставки портфеля. Например, инвестиция «Фонд Казна Top Brands» имеет большой процент доходности, как и большую долю в инвестиционном портфеле чем другие позиции.



Как рассчитать средневзвешенную процентную ставку в Excel пошагово

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

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

Средневзвешенная стоимость капитала — Википедия

Материал из Википедии — свободной энциклопедии

Средневзвешенная стоимость капитала (англ. weighted average cost of capital, WACC) — это средняя процентная ставка по всем источникам финансирования компании. При расчете учитывается удельный вес каждого источника финансирования в общей стоимости

[1].

Термин «средневзвешенная стоимость капитала» применяется в финансовом анализе и оценке бизнеса. Показатель характеризует относительный уровень общей суммы расходов по обеспечению каждого источника финансирования и представляет собой средневзвешенную стоимость капитала.

Средневзвешенная стоимость капитала может быть рассчитана как:

c = (EK)⋅y + (DK)⋅b(1−t){\displaystyle c\ =\ \left({E \over K}\right)\cdot y\ +\ \left({D \over K}\right)\cdot b(1-t)},

где

 K=D+E{\displaystyle \ K=D+E}.


В таблице прилагаются значения каждого из символов:

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

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

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

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

Рыночная стоимость обыкновенных акций / (Рыночная стоимость обыкновенных акций + Рыночная стоимость привилегированных акций + Рыночная стоимость заёмных средств).
  • Рыночная стоимость собственного капитала (обыкновенных акций) для публичной компании рассчитывается как цена акции, умноженная на их количество в обращении.
  • Рыночная стоимость собственного капитала (привилегированных акций) рассчитывается аналогично.
  • Рыночная стоимость заёмного капитала просто рассчитывается, если компания имеет облигации в обращении. Достаточно часто компании имеют значительные банковские кредиты, стоимость которых оценить сложнее. Тем не менее, если кредитный рейтинг компании не испытывал значительных изменений, рыночная стоимость заимствований должна быть близкой к балансовой стоимости. Поэтому в расчёте WACC допустимо использовать балансовую стоимость заёмных средств.
Оценка стоимости акций - это процесс определения внутренней стоимости акции на основе ожидаемого риска и предполагаемой доходности.
  • Привилегированные акции эквивалентны бесконечному аннуитету, в котором держатель имеет право получать фиксированные платежи неограниченное количество периодов. Таким образом, стоимость определяется отношением периодического платежа к цене привилегированной акции и выражается в процентном измерении.
  • Доходность к погашению по торгующимся облигациям является оценкой их стоимости. В случае отсутствия торгующихся облигаций за оценку стоимости можно принять текущие процентные ставки по банковским кредитам для данной компании или сопоставимых компаний. Поскольку компания может списывать налоги с объёма уплаченных процентов, стоимость заёмного капитала снижается на эффективную ставку налога. Таким образом, стоимость заёмного капитала для компании = (доходность к погашению или ставка процента)  × (1 − ставка налога). В практике, налоговый вычет сохраняют в формуле, не учитывая в стоимости заёмных средств:
WACC = доля обыкновенных акций × стоимость капитала обыкновенных акций
+ доля привилегированных акций × стоимость капитала привилегированных акций
+ доля заёмного капитала × стоимость заёмного капитала × (1 − ставка налога).

Теперь все составляющие можно включить в итоговую формулу WACC.

Экономисты Мертон Миллер и Франко Модильяни продемонстрировали в теореме Модильяни-Миллера, что в идеальной ситуации, при отсутствии налогов, стоимость капитала компании не зависит от структуры капитала (отношения собственного и заемного капитала). Поскольку налоговые системы зачастую позволяют производить налоговый вычет с уплаченных процентов, то заёмное финансирование становится привлекательным (эффект Налогового щита).

  • Джон Теннент. Управление денежными потоками: Как не оказаться на мели = Guide To Cash Management How To Avoid A Business Credit Crunch. — М.: Альпина Паблишер, 2014. — 208 с. — ISBN 978-5-9614-4646-3.

Средневзвешенное значение формула в Excel

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

Расчет средневзвешенной кредитной ставки

Допустим, компания взяла три кредита с процентными ставками: 14, 12 и 16 процентов, если рассчитать обычную среднюю величину всех ставок по кредитам, то получается (14%+16%+12%)/3=14%. Согласно этому расчету среднее значение всех процентных ставок по кредитам составит 14%, но эта цифра не является характеристикой кредитного портфеля компании. Необходимо помнить, что стоимость использования кредита напрямую зависит от его суммы, поэтому у компании, в кредитном портфеле которой находятся кредиты на большую сумму с меньшим процентом, цена кредитов будет значительно меньше. Согласно этому принципу

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

  • iср.вз. — средневзвешенная ставка;
  • Sост — ссудная задолженность или остаток по кредиту;
  • iтек — процентная ставка кредита.

Обычно для расчета средневзвешенной ставки подсчеты выполняют в Excel при помощи функции «СУММПРОИЗВ». Если провести расчет ставки по формуле для приведенного выше примера, то средняя ставка будет не 14%, а 14,38%. Это объясняется тем, что большая часть суммы кредитов обладала ставкой, превышающей среднюю.

СПС может периодически меняться, в случаях, если происходят следующие события:

  1. Изменилась процентная ставка по какому-нибудь кредиту.
  2. Был погашен основной долг.
  3. Компания взяла очередной кредит.

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

  1. Кредиты стоит получать по минимально ставке.
  2. При возможности, сначала нужно гасить кредиты с самыми высокими процентами.
  3. При возможности избавиться от всех кредитов с большими процентами, или заменить их на другие, с более низкой процентной ставкой.
  4. Планировать график погашения всех кредитов так, чтобы в конце остались только кредиты с низкой процентной ставкой.
  5. Сокращать процентные ставки по уже имеющимся кредитам. Можно переговорить с банками и попытаться снизить проценты.

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

Расчет средневзвешенного значения в 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. Затем щелкните мышью в строку формул и введите туда формулу для расчета средневзвешенного:

Расчет средневзвешенного на DAX в Power Pivot

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

  • В 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 - и будет вам счастье!

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

Как определяется средневзвешанная ставка по кредиту?

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

Что такое средневзвешанная ставка по кредиту и её расчет


Кредитный портфель любой компании состоит из разных кредитов.

Они могут быть:

• Долгосрочными;
• Краткосрочными;
• Оборотными;
• Инвестиционными.

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

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

Предположим, предприятие взяло три ссуды: первую со ставкой под 14%, вторую – 12, третью - 16. Средняя величина ставок составляет 14% ((14+12+16)/3), однако данный показатель не считается характеристикой кредитного портфеля предприятия.

Обратите внимание!

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


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

Средневзвешенная ставка вычисляется по формуле:

Что такое средневзвешанная ставка по кредиту?
, где

Sост – остаток по займу,
Iтек – процентная ставка займа.

СПС будет меняться, если:

• Изменится ставка по любому кредиту;
• Будет погашен основной долг;
• Предприятие оформит новую ссуду.

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

1. Кредиты оформлять по минимально возможной ставке;

2. Стараться первыми погашать займы с высокими процентами;

3. Если невозможно избавиться от высокопроцентных кредитов, надо постараться заменить их на займы с более низкими ставками;

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

5. Сокращать ставки по имеющимся кредитам: переговорить с кредитными учреждениями и сделать попытку снизить проценты.

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

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

Материал из Википедии — свободной энциклопедии

Сре́днее арифмети́ческое взве́шенное — математическое понятие, обобщающее среднее арифметическое. Среднее арифметическое взвешенное набора чисел x1,…,xn{\displaystyle x_{1},\ldots ,x_{n}} с весами w1,…,wn{\displaystyle w_{1},\ldots ,w_{n}} определяется как

x¯=∑i=1nwi⋅xi∑i=1nwi{\displaystyle {\bar {x}}={\frac {\sum \limits _{i=1}^{n}w_{i}\cdot x_{i}}{\sum \limits _{i=1}^{n}w_{i}}}}.

Основные числа и веса могут быть и вещественными, и комплексными. При этом сумма весов не может быть 0, но могут быть некоторые, не все веса, равные 0.

Если все веса wi{\displaystyle w_{i}} равны между собой, получается обычное среднее арифметическое. Существуют также взвешенные версии среднего геометрического и среднего гармонического, среднего степенного и их обобщения — среднего по Колмогорову.

Иногда сумма весов равна 1 (например, в голосованиях в процентах как весах), тогда формула упрощается:

x¯=∑i=1nwi⋅xi{\displaystyle {\bar {x}}=\sum \limits _{i=1}^{n}w_{i}\cdot x_{i}}.

В физике[править | править код]

Средняя скорость тела

Если тело в течение промежутка времени t1{\displaystyle t_{1}} движется со скоростью v1{\displaystyle v_{1}}, затем в течение следующего промежутка времени t2{\displaystyle t_{2}} — со скоростью v2{\displaystyle v_{2}} и так далее до последнего промежутка времени tn{\displaystyle t_{n}}, в течение которого оно движется со скоростью vn{\displaystyle v_{n}}, то средняя скорость движения тела за суммарный промежуток времени (t1+t2+…+tn{\displaystyle t_{1}+t_{2}+\ldots +t_{n}}) будет равна взвешенному среднему арифметическому скоростей v1,…,vn{\displaystyle v_{1},\ldots ,v_{n}} с набором весов t1,…,tn{\displaystyle t_{1},\ldots ,t_{n}}:

vcp=∑i=1nti⋅vi∑i=1nti{\displaystyle v_{cp}={\frac {\sum \limits _{i=1}^{n}t_{i}\cdot v_{i}}{\sum \limits _{i=1}^{n}t_{i}}}}.
Центр масс

Другим примером использования данного понятия в физике является центр масс системы материальных точек, который задаётся формулой:

r→c=∑imir→i∑imi,{\displaystyle {\vec {r}}_{c}={\frac {\sum \limits _{i}m_{i}{\vec {r}}_{i}}{\sum \limits _{i}m_{i}}},}

где r→c{\displaystyle {\vec {r}}_{c}} — радиус-вектор центра масс,
r→i{\displaystyle {\vec {r}}_{i}} — радиус-вектор i-й точки системы,
mi{\displaystyle m_{i}} — масса i-й точки.

Температура смеси нескольких порций одной жидкости с разными температурами
tcp=∑i=1nti⋅mi∑i=1nmi{\displaystyle t_{cp}={\frac {\sum \limits _{i=1}^{n}t_{i}\cdot m_{i}}{\sum \limits _{i=1}^{n}m_{i}}}},

где tcp{\displaystyle t_{cp}} — полученная температура смеси,
ti{\displaystyle t_{i}} — температура i-й порции,
mi{\displaystyle m_{i}} — масса i-й порции.

В экономике[править | править код]

Средневзвешенный курс валюты
Ccp=∑i=1nCi⋅bi∑i=1nbi{\displaystyle C_{cp}={\frac {\sum \limits _{i=1}^{n}C_{i}\cdot b_{i}}{\sum \limits _{i=1}^{n}b_{i}}}},

где Ccp{\displaystyle C_{cp}} — средневзвешенный курс,
Ci{\displaystyle C_{i}} — курс оборота по i-му торгу,
bi{\displaystyle b_{i}} — размер i-го торга.

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

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

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

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

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

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

Функция СУММПРОИЗВ в Excel

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

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

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

=СУММПРОИЗВ(
=SUMPRODUCT(

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

=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9

Вторым аргументом будет диапазон ячеек C2:C9, в котором содержатся веса. Между этими аргументами должен стоять разделитель точка с запятой (запятая). Когда все будет готово, закрываем скобки:

=СУММПРОИЗВ(B2:B9;C2:C9)
=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), то увидели, что на самом деле она считает вот что:

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)

К счастью, нам не нужно писать такую длинную формулу, поскольку СУММПРОИЗВ (SUMPRODUCT) делает всё это автоматически.

Сама по себе функция СУММПРОИЗВ (SUMPRODUCT) возвращает нам огромное число – 10450. В этот момент включается вторая часть формулы: /СУММ(C2:C9) или /SUM(C2:C9), которая возвращает результат в нормальный диапазон оценок, давая ответ 83,6.

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

Оцените качество статьи. Нам важно ваше мнение:




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

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