Средневзвешенная величина в excel

Пусть дана таблица продаж партий 1-го продукта (см. файл примера ). В каждой партии обозначено количество проданного продукта (столбец А) и его стоимость (столбец В).

Найдем средневзвешенную стоимость. В отличие от средней цены, вычисляемой по формуле СРЗНАЧ(B2:B8) , в средневзвешенной учитываются «вес» каждой цены (в нашем случае в качестве веса выступают значения из столбца Количество). Т.е. если продали одну крупную партию продукта по весьма низкой стоимости (строчка 2), а остальные маленькие партии по высочайшей, то не глядя, что средняя стоимость будет высочайшей, средневзвешенная стоимость будет смещена в сторону малой стоимости.

Средневзвешенная стоимость рассчитывается по формуле.
=СУММПРОИЗВ(B2:B8;A2:A8)/СУММ(A2:A8)

Если в столбце «весов» (А) будут содержаться однообразные значения, то средняя и средневзвешенная цены совпадут.

Средневзвешенная стоимость с условием

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

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

Формула для вычисления средневзвешенной цены для Поставщика1:

К аргументам функции СУММПРОИЗВ() добавился 3-й аргумент: –($B$7:$B$13=B17)

Если выделить это выражение и надавить F9, то получим массив <1:1:1:1:0:0:0>. Т.е. значение 1 будет лишь в строчках, у каких в столбце поставщик указан Поставщик1. Сейчас сумма произведений не будет учесть цены от другого поставщика, т.к. будут умножены на 0. Сумма весов для Поставщика1 рассчитывается по формуле СУММЕСЛИ($B$7:$B$13;B17;$D$7) .

Решение приведено в файле примера на листе Пример2 .

трюки • приёмы • решения

Функция 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) .

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

Основная мысль

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

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

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) :

Интересно почитать: Формула сложных процентов в excel

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

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

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

Остается поделить одно на другое, но создать это, как бы, обычное математическое действие снутри сводной не так просто. Придется или добавлять в сводную вычисляемое поле (вкладка Анализ – Поля, элементы, наборы – Вычисляемое поле), или считать обыкновенной формулой в примыкающих ячейках либо завлекать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (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 – и будет для вас счастье!

Среднее значение в DAX: функции AVERAGE, AVERAGEA и AVERAGEX в Power BI и Power Pivot

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы побеседуем про расчеты средних арифметических значений на языке DAX в Power BI и Excel (Power pivot). А конкретно, про функции AVERAGE, AVERAGEA и AVERAGEX.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, задачи, а результаты работы формул повсевременно не те, что Вы ждете и Для вас нужна помощь, то записывайтесь в бесплатный экспресс-курс «Резвый старт в языке функций и формул DAX для Power BI и Power Pivot».

Также, подписывайтесь на наши социальные сети. Поэтому что конкретно в их, Для вас будут доступны оперативно и любой денек наши животрепещущие фишки, секреты, выработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функции AVERAGE, AVERAGEA и AVERAGEX в Power BI и Power Pivot

DAX функция AVERAGE () — высчитывает среднее арифметическое значение числовых данных столбца (сумма значений деленное на количество этих значений):
- если в столбце текстовый тип данных, то AVERAGE возвращает пустое значение
- если в столбце пустые ячейки либо логический тип данных, то в расчет среднего они не берутся
- если в столбце находятся ячейки с 0, то такие ячейки учитываются, как обыденные

логические выражения равные TRUE (Правда) обрабатываются как 1
логические выражения равные FALSE (Ересь) обрабатываются как 0
текстовый тип данных обрабатывается как 0

‘Таблица’ — начальная таблица либо табличное выражение, по строчкам которой будет рассчитываться выражение из второго параметра функции
Выражение — хоть какое выражение, которое нужно выполнить по строчкам таблицы, входящей в 1-ый параметр функции

Пример работы формулы на базе AVERAGE

Разглядим практический пример работы DAX формулы с внедрением AVERAGE в программке Power BI Desktop.

Имеется начальная таблица, содержащая числовые значения:

Рассчитаем среднее значения столбца с помощью DAX функции AVERAGE:

Результатом выполнения данной формулы будет значение 2:

Мало усложним формулу нашего примера заменив AVERAGE функцией AVERAGEX:

Интересно почитать: Excel формула процента числа от числа

В этом случае данная DAX формула будет работать в 2 шага. Для начала, AVERAGEX выполнит выражение «‘Таблица'[Столбец] + 2» из второго параметра функции для каждой строчки ‘Таблица’, обозначенной в первом параметре. Приобретенные результаты она запишет во внутреннюю память, в некоторую временную виртуальную таблицу:

И потом, уже на базе приобретенных результатов, записанных во временной памяти, AVERAGEX высчитает среднее арифметическое значение, которое будет равно 4:

На этом, с разбором функций среднего значения AVERAGE в данной статье все. Фурроров Для вас в освоении DAX и построении сквозной аналитики Power BI!

Подробное ВИДЕО «AVERAGE, AVERAGEA и AVERAGEX — DAX функции среднего арифметического значения в Power BI (Power Pivot)»

Ссылки из видео:
1) [Регистрируйтесь в бесплатном экспресс-курсе] Резвый старт в языке функций и формул DAX для Power BI и Power Pivot: зарегистрироваться
2) [Скачивайте PDF] Справочник DAX функций для Power BI и Power Pivot на российском языке: скачать

Пожалуйста, оцените статью:

Фурроров Для вас, друзья!
С почтением, Будуев Антон.
Проект «BI — это просто»

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

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

Приглянулся материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтоб возвратиться к ней снова. Для этого, прямо на данный момент нажмите на клавиатуре комбинацию кнопок Ctrl+D

Что еще поглядеть / почитать?

Как в Power BI высчитать таблицу ТОП-3 не через вычисляемую таблицу, а через меру?

TOTALYTD, TOTALQTD и TOTALMTD — DAX функции группы time intelligence, вычисляющие выражение в текущем периоде в Power BI и Power Pivot

Функции сотворения вычисляемых столбцов и таблиц в DAX: ADDCOLUMNS И DATATABLE в Power BI и Power Pivot

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

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

Понятие

Существует две трактовки средневзвешенной процентной ставки по кредитам (дальше – СПС). Они зависят от уровня, на котором применяется это значение. 1-ый – уровень определенного банка. Тут СПС будет означать совокупную стоимость выданных и приобретенных организацией кредитов. Средневзвешенная ставка в этом случае, на самом деле, – это средняя стоимость определенного кредитного ранца. Зная это значение, компания может правильно оценить эффективность собственной работы.

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

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

Для чего нужен расчет средней цены кредитов

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

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

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

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

ЦБ РФ (Российская Федерация — государство в Восточной Европе и Северной Азии, наша Родина) повсевременно выслеживает объемы межбанковских кредитов, регулирует такие сделки и изменяет проценты по займам. Чтоб Центробанк вовремя и корректно реагировал на конфигурации ликвидности, нужно часто выслеживать размер средневзвешенной ставки и верно рассчитывать этот показатель.

Информация о показателе СПС может понадобиться и физическим лицам тоже. Банки дают весьма много программ с различными процентными ставками, в том числе и под 0%. Почаще всего такие «беспроцентные» кредиты включают огромные комиссии за сервис либо доступны весьма узенькому кругу клиентов. Средневзвешенная ставка дозволяет оценить выгоду по кредитам в рамках 1-го банка.

Виды кредитов

СПС помогает ЦБ РФ (Российская Федерация — государство в Восточной Европе и Северной Азии, наша Родина) проанализировать работу банка. Обыкновенные характеристики, которые охарактеризовывают кредитование, не разрешают провести четкий анализ. Потому для определения средневзвешенной ставки в расчете необходимо учесть все виды кредитов, которые выдаются в банке.

короткосрочные, длительные периоды;
вкладывательные взносы;
средние и короткосрочные займы;
кредиты, выдаваемые в виде обратных активов.

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

Высчитать средневзвешенную процентную ставку можно не только лишь для банков и коммерческих организаций, да и для кредитов физическим лицам. Эти характеристики находятся в открытом доступе, на веб-сайте Центрального Банка РФ (Российская Федерация — государство в Восточной Европе и Северной Азии, наша Родина), и обновляются каждый месяц.

Интересно почитать: Excel адрес ячейки формулой

Формула расчета

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

Где Х – это все имеющиеся процентные ставки, а n – количество ставок.

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

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

СПС = ∑(К*П)/∑К*100

СПС – средневзвешенная процентная ставка;
К – остаток по кредиту, П – проценты по нему;
∑К – сумма задолженностей.

∑ (сигма) – сумма огромного количества однородных величин.

Пример расчетов

Покажем работу формулы на примере расчета по данным характеристикам.

4 миллиона под 10%;
6 миллионов под 7%, при всем этом 1 миллион она уже выплатила, выходит, остается 5 миллионов – они и учитываются при расчете;
3 миллиона под 9%.

С этими данными формула будет смотреться последующим образом:

СПС = (4 000 000 × 10% + 5 000 000 × 7% + 3 000 000 × 9%) / (4 000 000 + 5 000 000 + 3 000 000) × 100 = 1 020 000/12 000 000 × 100 = 0,085 × 100 = 8,5

Выходит, что средневзвешенная ставка по сиим трем займам равна 8,5%.

Но необходимо отметить, что приобретенный в формуле итог – это не фиксированная величина. На СПС влияет огромное количество причин: изменение процентов по какому-либо из выданных займов, порядок расчета по кредиту (аннуитетными либо дифференцированными платежами), получение новейших кредитов, комиссии за преждевременное погашение и т. д. Воздействие имеет и текущий денежный курс ЦБ.

Как понизить среднюю ставку

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

Предприятие обязано строго надзирать свою средневзвешенную ставку. Таковая стратегия дозволит умеренно распределять ресурсы банка и поддерживать его эффективность.

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

Заключение

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

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

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

Что такое средневзвешенная процентная ставка по кредитам?
Виды кредитов
Для чего нужен расчет средней цены кредитов?
Что заходит в активы банков?
Формула для расчета средневзвешенной ставки по кредитам физическим лицам
Пример расчета
Как понизить среднюю ставку по кредитам?

Что такое средневзвешенная процентная ставка по кредитам?

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

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

Виды кредитов

Имеющиеся виды займов:

Банковский.
Потребительский.
Ипотечный.
Коммерческий.
Муниципальный.
Интернациональный.
Межбанковский.
Ломбардный.
Ростовщический.
Возобновляемый.
Синдицированный.
Экспортный.

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

Займы также могут быть короткосрочными, длительными, обратными и вкладывательными. По любому кредиту инсталлируются различные ставки. Средневзвешенная ставка рассчитывается ЦБ РФ (Российская Федерация — государство в Восточной Европе и Северной Азии, наша Родина) раздельно для людей и компаний. Данные находятся в открытом доступе. Ознакомиться с ними можно здесь. Ставка для людей по состоянию на 01.05.2019 год – 15,20%. Показатель установлен по кредитам, которые оформляются на срок от 1 до 3 лет.

Ставка для некоммерческих организаций за таковой же период составляет 16,66%. По кредитам, выданным на срок от 3 лет, показатель существенно ниже (7,73%).

Для чего нужен расчет средней цены кредитов?

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

Расчеты средней процентной ставки по кредитам также важны для коммерческих организаций. Предприятие может выявить высокопроцентные займы и произвести рефинансирование.