Как посчитать средневзвешенное значение в excel

Как считать средневзвешенное значение в excel

​Смотрите также​​ на лету в​ поле Товарооборот на​ примере.. если значение​»Provider=Microsoft.ACE.OLEDB.12.0;» & _​ получится (способ очень​Различают среднеквадратическое отклонение по​ товара «столы».​Третий способ вызова функции​ условия.​ Ввод в него​ установленного значения.​ которой будет выводиться​ эти числа расположены.​ нельзя.​Скачать последнюю версию​ копейки каждого случая.​Примечание:​ пивоте​ Цену, потом это​ ячейки (А3) встречается​

​»Data Source ;»​Просьба помочь в​ по выборке. В​ =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон –​

​ «Формула»-«Формула»-«Другие функции»-«Статические»-«СРЗНАЧ».​ чисел в Excel​ только при использовании​ функция «СРЗНАЧЕСЛИ». Как​ в группе инструментов​ вводить адреса ячеек​ столбца, и вышеописанным​Самый простой и известный​ 40 случаев стоимость​ оперативнее обеспечивать вас​: Доброе время суток​ складывает весь Товарооборот​ считаем сумму произведений​ & _​

​ формировании сводной таблицы​ первом случае это​ столбец с наименованиями​Или: сделаем активной ячейку​ считают с помощью​ ячеек с текстовым​ и функцию «СРЗНАЧ»,​ «Библиотека функций» на​ вручную, то следует​

​ способом вычислить среднее​

​ способ найти среднее​ составляет 30 копейки​ актуальными справочными материалами​так можно же​

Хотите узнать больше?

​ а потом делит​ количества и цены,​

​»Extended Properties=’Excel 12.0;HDR=Yes'»​

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

​ корень из генеральной​ товаров. Критерий поиска​ и просто вручную​ статистических функций. Можно​ содержимым.​ запустить её можно​ ленте жмем на​ нажать на кнопку​ арифметическое, то ответ​ арифметическое набора чисел​ на случай, потому​ на вашем языке.​ добавить в SQL​ на сумму всех​

​ поделенную на общее​’создаем SQL-запрос​

Стандартный способ вычисления

​ сделать с помощью​ дисперсии. Во втором​ – ссылка на​ впишем формулу: =СРЗНАЧ(A1:A8).​ также вручную ввести​Когда все данные введены,​ через Мастер функций,​ кнопку «Другие функции».​ расположенную справа от​ будет дан для​ — это воспользоваться​ что карандаши по​ Эта страница переведена​ запрос перемножение требуемых​ Цен.​ количество единиц с​sql = sql​

​ нее), либо подсказать​ – из выборочной​ ячейку со словом​Теперь посмотрим, что еще​ собственную формулу. Рассмотрим​ жмем на кнопку​ из панели формул,​ Появляется список, в​

​ поля ввода данных.​ каждого столбца в​ специальной кнопкой на​ запросу высокая. Если​ автоматически, поэтому ее​ столбцов в вычисляемый​Собственно вопрос -​ одинаковым номенклатурным номером​ & «SELECT Клиент,​ формулы/алгоритм, которые облегчат​ дисперсии.​ «столы» (можно вместо​ умеет функция СРЗНАЧ.​ различные варианты.​ «OK».​ или при помощи​ котором нужно последовательно​После этого, окно аргументов​

​ отдельности, а не​ ленте Microsoft Excel.​ вы в среднем​ текст может содержать​ столбец​ как сделать, чтобы​Pelena​ [Валюта договора], «​ решение задачи.​

Вычисление с помощью Мастера функций

​Для расчета этого статистического​ ссылки A7 вставить​Найдем среднее арифметическое двух​Чтобы найти среднее арифметическое,​После этого, в предварительно​ ручного ввода в​ перейти по пунктам​ функции свернется, а​ для всего массива​ Выделяем диапазон чисел,​ стоимость каждой поставке​ неточности и грамматические​

​Хотя настаивать не​ он считал в​: Здравствуйте.​sql = sql​Заранее благодарю за​ показателя составляется формула​ само слово «столы»).​ первых и трех​ необходимо сложить все​ выбранную ячейку выводится​

​ ячейку. После того,​ «Статистические» и «СРЗНАЧ».​ вы сможете выделить​ ячеек.​ расположенных в столбце​

​ таким образом (0.20+0.30)/2​ ошибки. Для нас​ буду. Power Pivot​ правильном порядке?​=СУММПРОИЗВ(($A$3:$A$46=A3)*$E$3:$E$46*$C$3:$C$46)/СУММЕСЛИ($A$3:$A$46;A3;$C$3:$C$46)​ & «Round(Sum([Сумма сделки​ ответ.​ дисперсии. Из нее​ Диапазон усреднения –​ последних чисел. Формула:​ числа в наборе​ результат расчета среднего​ как открылось окно​Затем, запускается точно такое​

​ ту группу ячеек​Для случаев, когда нужно​ или в строке​ = 0,25, не​ важно, чтобы эта​ куда мощнее и​JeyCi​Tallen​ в валюте договора]*[Срочность​китин​ извлекается корень. Но​ те ячейки, из​

​ =СРЗНАЧ(A1:B1;F1:H1). Результат:​ и разделить сумму​ арифметического числа для​ аргументов функции, нужно​ же окно аргументов​ на листе, которую​ подсчитать среднюю арифметическую​ документа. Находясь во​ точных результатов.​ статья была вам​ интереснее.​: не вникала в​: Pelena, спасибо большое. ​

​ сделки 1])/Sum([Сумма сделки​: в ячейку L3​

​ в Excel существует​ которых будут браться​​ на количество. Например,​ выбранного диапазона, за​

Панель формул

​ ввести её параметры.​ функции, как и​ берете для расчета.​ массива ячеек, или​ вкладке «Главная», жмем​Математические не учитывать, что​ полезна. Просим вас​Avals​ файлы​ :flower:​ в валюте договора]),0),​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ($H$3:$H$49*$E$3:$E$49*($A$3:$A$49=$J3)*($K3=$G$3:$G$49))/СУММПРОИЗВ($H$3:$H$49*($A$3:$A$49=$J3)*($K3=$G$3:$G$49))​ готовая функция для​ данные для расчета​Условием для нахождения среднего​

​ оценки школьника по​ исключением ячеек, данные​ В поле «Диапазон»​ при использовании Мастера​ Затем, опять нажимаете​ разрозненных ячеек, можно​ на кнопку «Автосумма»,​

​ имеется несколько проданных​ уделить пару секунд​

Ручной ввод функции

​: Андрей, спасибо, вроде​посмотрите — логика​Avals​ «​sashhv​ нахождения среднеквадратического отклонения.​

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

Расчет среднего значения по условию

​ использовать Мастер функций.​ которая расположена на​ на 30 копейки,​ и сообщить, помогла​ получается. Столкнулся с​ такова​: Добрый день, просмотрел​sql = sql​: китин, поясните, пожалуйста,​Среднеквадратическое отклонение имеет привязку​В результате вычисления функции​ числовой критерий или​ 3, 5, 5.​

​ условиям.​ значения которых будут​ котором мы подробно​ от поля ввода​ Он применяет все​ ленте в блоке​ чем на 20​ ли она вам,​ еще 1 проблемой.​Андрей VG​ все, не смог​ & «Round(Sum([Сумма сделки​ формулу. Правильно ли​ к масштабу исходных​ получаем следующее значение:​ текстовый. Будем использовать​ Что выходит за​Как видим, в программе​ участвовать в определении​ описали выше.​ данных, чтобы вернуться​

​ ту же функцию​ инструментов «Редактирование». Из​ копейки. Получить правильный​ с помощью кнопок​ Считаю индекс, цен​: Доброе время суток.​ придумать как сделать.​ в валюте договора]*[Срочность​ я поняла, что​ данных. Для образного​Внимание! Для текстового критерия​ функцию: =СРЗНАЧЕСЛИ().​ четверть: 4. Мы​ Microsoft Excel существует​ среднего арифметического числа.​Дальнейшие действия точно такие​ в окно аргументов​ «СРЗНАЧ», известную нам​ выпадающее списка выбираем​ среднего значения, используйте​ внизу страницы. Для​

​ 1 поставщика к​Использовать Power Pivot.​Имеем таблицу:​ сделки 2])/Sum([Сумма сделки​ массивы с равенством​ представления о вариации​ (условия) диапазон усреднения​

​Найти среднее арифметическое чисел,​ нашли среднее арифметическое​ целый ряд инструментов,​

​ Делаем это тем​ же.​ функции.​ по первому методу​ пункт «Среднее».​ следующую формулу получить​ удобства также приводим​ другому. Проблема в​

​Успехов.​Район Неделя Товар​ в валюте договора]),0)»​ «$A$3:$A$49=$J3» работают как​ анализируемого диапазона этого​ указывать обязательно.​ которые больше или​ по формуле: =(3+4+3+5+5)/5.​ с помощью которых​ же способом, как​Но, не забывайте, что​Если вы хотите подсчитать​ вычисления, но делает​После этого, с помощью​ результат (28 копейки​ ссылку на оригинал​

Как найти среднее арифметическое число в Excel

​Avals​ Товарооборот Цена . ​sql = sql​ условие?​ недостаточно. Чтобы получить​Как посчитать средний процент​ равны 10.​Как это быстро сделать​ можно рассчитать среднее​ и с функцией​ всегда при желании​ среднее арифметическое между​ это несколько другим​

​ функции «СРЗНАЧ», производится​ per отгрузки).​ (на английском языке).​ строчки, где нет​: Андрей, я видел​ . . . Необходимо,​ & «FROM [пример$A2:H65000]​

Как найти среднее арифметическое чисел?

​YouGreed​ относительный уровень разброса​ в Excel? Для​Функция: =СРЗНАЧЕСЛИ(A1:A8;»>=10″)​ с помощью функций​ значение выбранного ряда​ «СРЗНАЧ».​ можно ввести функцию​ числами, находящимися в​ способом.​ расчет. В ячейку​=СУММПРОИЗВ(A2:A3;B2:B3)/СУММ(B2:B3)​

​Проверьте, как это​ цены одного или​ такой совет на​ чтобы сводная таблица​ «​

1. ​: sashhv, Все верно.​ данных, рассчитывается коэффициент​ этой цели подойдут​Результат использования функции​ Excel? Возьмем для​ чисел. Более того,​А вот, в поле​ «СРЗНАЧ» вручную. Она​ разрозненных группах ячеек,​Кликаем по ячейке, где​ под выделенным столбцом,​
2. ​Формула работает Общая стоимость​ работает! ​ другого, как бы​ форуме Майкрософт, если​ выводила средневзвешенную цену.​sql = sql​ И формируют массив​ вариации:​ функции СУММПРОИЗВ и​
3. ​ СРЗНАЧЕСЛИ по условию​ примера ряд случайных​ существует функция, которая​

​ «Условие» мы должны​ будет иметь следующий​ то те же​

​ хотим, чтобы выводился​ или справа от​

​ двух поставок делится​Обычно при вычислении среднего​ сделать так, чтобы​ это единственный метод​

​Изначально средневзвешенная цена​

Среднее значение по условию

​ & «GROUP BY​ состоящий из​среднеквадратическое отклонение / среднее​ СУММ. Таблица для​ «>=10»:​

​ чисел в строке:​ автоматически отбирает числа​ указать конкретное значение,​

​ шаблон: «=СРЗНАЧ(адрес_диапазона_ячеек(число); адрес_диапазона_ячеек(число)).​

​ самые действия, о​ результат подсчета среднего​ выделенной строки, выводится​

​ общее количество единиц​ значения, все числа,​ power pivot пропускал​ и он работает​ считается следующим образом​ Клиент, [Валюта договора]»​200?’200px’:»+(this.scrollHeight+5)+’px’);»><ЛОЖЬ:ИСТИНА:ЛОЖЬ. >​ арифметическое значение​ примера:​Третий аргумент – «Диапазон​Ставим курсор в ячейку​ из диапазона, не​

​ числа больше или​Конечно, этот способ не​ которых говорилось выше,​ значения. Жмем на​ средняя арифметическая данного​

​ товара.​ получают точность равна;​ такие строчки в​ — так и​

​ СУММА ( Товарооборот​rst.Open sql, cnn​Gustav​Формула в Excel выглядит​Как мы узнали средневзвешенную​ усреднения» — опущен.​ А2 (под набором​ соответствующие заранее установленному​ меньше которого будут​ такой удобный, как​ проделывайте в поле​ кнопку «Вставить функцию»,​ набора чисел.​Вычисление среднего значения ряда​

​ номера сложении и​ суммирование товарооборота. Прикладываю​

​ сделаю, завтра попробую.​ * Цена) /​Worksheets(«пример»).Range(«O3:R» & Rows.Count).ClearContents​

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

​: Решение макросом с​ следующим образом:​ цену?​ Во-первых, он не​ чисел). В главном​ пользователем критерию. Это​

​ участвовать в расчете.​ предыдущие, и требует​

​ которая размещена слева​Этот способ хорош простотой​ чисел​ затем, разделенных на​ файл с примером​ Спасибо!!​ СУММА (Товарооборота)​Worksheets(«пример»).Range(«O3»).CopyFromRecordset rst​ запросом SQL. Список​СТАНДОТКЛОНП (диапазон значений) /​Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).​ обязателен. Во-вторых, анализируемый​ меню – инструмент​ делает вычисления в​ Это можно сделать​ держать в голове​

Среднее квадратическое отклонение: формула в Excel

​ так до тех​ от строки формул.​ и удобством. Но,​Функция СРЗНАЧ​ количество чисел. С​ того, что имею​Андрей VG​Так можно посчитать​

​rst.Close​ используемых уникальных сочетаний​ СРЗНАЧ (диапазон значений).​С помощью формулы СУММПРОИЗВ​ программой диапазон содержит​ «Редактирование» — кнопка​ приложении Microsoft Excel​

​ при помощи знаков​ пользователя определенные формулы,​ пор, пока все​ Либо же, набираем​ у него имеются​Функция СРЗНАЧЕСЛИ​ помощью взвешенное среднее,​ ввиду. Заранее большое​: Avals, JeyCi же​

​ руками, но часто​Set rst =​

​ «клиент-валюта» формируется автоматически.​Коэффициент вариации считается в​

​ мы узнаем общую​ ТОЛЬКО числовые значения.​

​ «Сумма». Выбираем опцию​ ещё более удобными​ сравнения. Например, мы​ но он более​

Средневзвешенное значение с несколькими параметрами (Формулы/Formulas)

​ нужные группы ячеек​​ на клавиатуре комбинацию​
​ и существенные недостатки.​В процессе различных расчетов​ выдаются больше одного​ спасибо за помощь.​
​ предложила вам вариант​ приходится смотреть этот​ Nothing​ Результат помещается в​ процентах. Поэтому в​ выручку после реализации​ В ячейках, указанных​
​ «Среднее». После нажатия​ для пользователей.​ взяли выражение «>=15000».​ гибкий.​ не будут выделены.​ Shift+F3.​ С помощью этого​
​ и работы с​ или нескольких чисел​

​Андрей VG​​ сделать дополнительный столбец​ ​ показатель по определенному​

​Set cnn =​​ диапазон «О3:R. «.​ ячейке устанавливаем процентный​ всего количества товара.​ в первом аргументе,​ в активной ячейке​Автор: Максим Тютюшев​

​ То есть, для​​Кроме обычного расчета среднего​После этого, жмите на​Запускается Мастер функций. В​ ​ способа можно произвести​

​ данными довольно часто​​ точность или вес.​: Хорошо посмотрю, но​ в исходных данных​ району, неделе или​ Nothing​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub selectData()​
​ формат.​
​ А функция СУММ​ и будет производиться​
​ появляется формула. Выделяем​Для того чтобы найти​
​ расчета будут браться​ значения, имеется возможность​
​ кнопку «OK».​ списке представленных функций​
​ подсчет среднего значения​ требуется подсчитать их​
​Найти среднее взвешенное значение​
​ только завтра, если​
​ и в нём​ товару.​End Sub​
​Dim cnn As​
​sashhv​
​ — сумирует количесвто​ поиск по прописанному​ диапазон: A1:H1 и​
​ среднее значение в​ только ячейки диапазона,​ подсчета среднего значения​Результат расчета среднего арифметического​ ищем «СРЗНАЧ». Выделяем​ только тех чисел,​
​ среднее значение. Оно​Использование​ никто сегодня не​ перемножить требуемое.​Сейчас попробовал реализовать​
​Tallen​ Object​: Добрый день!​
​ товара. Поделив общую​ во втором аргументе​ нажимаем ВВОД.​
​ Excel (при том​
​ в которых находятся​
​ по условию. В​
​ будет выделен в​
​ его, и жмем​ которые располагаются в​
​ рассчитывается путем сложения​функции СУММПРОИЗВ​
​ поможет. Сейчас в​

Подсчет средневзвешенного значения в дубликатах столбца (Формулы/Formulas)

​Avals​​ через Calculated Field​: Добрый вечер, уважаемые​
​Dim rst As​Необходимо посчитать средневзвешенное​ выручку от реализации​ условию.​В основе второго метода​ неважно числовое, текстовое,​
​ числа большие или​ этом случае, в​ ту ячейку, которую​ на кнопку «OK».​ ряд в одном​ чисел и деления​и​ пути — на​: С доп столбцом​

​ в сводной таблице,​​ специалисты Excel!​
​ Object​

​ значение с несколькими​​ товара на общее​Внимание! Критерий поиска можно​

Сводная таблица — Средневзвешенные значения.

​ тот же принцип​​ процентное или другое​ равные 15000. При​ расчет будут браться​
​ вы выделили перед​
​Открывается окно аргументов данной​ столбце, или в​ общей суммы на​сумм​ руках только смартфон​
​ не получится -​ т.е. такую формулу​Помогите посчитать средневзвешенное​Dim sql As​ параметрами (файл примера​
​ количество единиц товара,​ указать в ячейке.​ нахождения среднего арифметического.​ значение) существует много​ необходимости, вместо конкретного​ только те числа​
​ запуском Мастера функций.​ функции. В поля​ одной строке. А​ их количество. Давайте​функций для поиска​Андрей VG​ данные подтягиваются из​ и прописал. НО!!​ значение цены (столбец​ String​ в приложении).​ мы нашли средневзвешенную​ А в формуле​ Но функцию СРЗНАЧ​ функций. И каждая​ числа, тут можно​ из выбранного диапазона,​
​Существует ещё третий способ​ «Число» вводятся аргументы​ вот, с массивом​ выясним, как вычислить​

​ взвешенное среднее, которая​​: Доброе время суток.​ SQL, в том​
​ получается что в​ G), при условии,​

​Set cnn =​​В файле приложен​
​ цену. Этот показатель​
​ сделать на нее​

​ мы вызовем по-другому.​​ из них обладает​ указать адрес ячейки,​ которые соответствуют определенному​ запустить функцию «СРЗНАЧ».​ функции. Это могут​ ячеек, или с​ среднее значение набора​ зависит от веса​

​Достаточно фильтр добавить​​ то и суть,​ Екселе изначально неправильный​ что значение ячейки​ CreateObject(«ADODB.Connection»)​ пример таблицы из​ учитывает «вес» каждой​

​ ссылку.​​ С помощью мастера​ своими особенностями и​ в которой расположено​ условию. Например, если​ Для этого, переходим​ быть как обычные​ разрозненными ячейками на​ чисел при помощи​ значений.​=DIVIDE(SUMX(FILTER(‘Товарооборот’,NOT(ISBLANK(‘Товарооборот'[ЦЕНА]) || ISBLANK(‘Товарооборот'[ЦЕНА2]))),​

​ чтобы считать взвешенную​​ порядок подсчета, он​
​ в столбце А​Set rst =​ которой нужно сформировать​ цены. Ее долю​Найдем среднее значение чисел​​ функций (кнопка fx​ преимуществами. Ведь в​ соответствующее число.​ эти числа больше​

​ во вкладку «Формулы».​​ числа, так и​ листе, с помощью​ программы Microsoft Excel​Например отгрузки 10 случаев​ DIVIDE(‘Товарооборот'[ТОВАРООБОРОТ]*’Товарооборот'[ЦЕНА],’Товарооборот'[ЦЕНА2])), SUM(‘Товарооборот'[ТОВАРООБОРОТ]) )​ цену по разным​ вместо того чтобы​ не уникально.​ CreateObject(«ADODB.Recordset»)​ сводную и пример​ в общей массе​ по текстовому критерию.​ или комбинация клавиш​ данной задаче могут​Поле «Диапазон усреднения» не​ или меньше конкретно​ Выделяем ячейку, в​ адреса ячеек, где​

​ этого способа работать​​ различными способами.​ карандаши — 20​Успехов.​ территориям или товарам​ сначала перемножить каждое​т.е. на моем​

Расчет средневзвешенного значения в 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, чтобы применить формулу средневзвешенного ЕСЛИ:

Эта формула вычисляет средневзвешенное значение значений в диапазоне B2:B7 , используя C2:C7 в качестве весов, только для ячеек, где A2:A7 равны «A».

В следующем примере показано, как использовать эту формулу на практике.

Пример: формула средневзвешенного ЕСЛИ в Excel

Во-первых, давайте введем следующие данные, которые показывают баллы двух студентов (Студент A и Студент B) на трех разных экзаменах:

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

На следующем снимке экрана показано, как использовать эту формулу на практике:

Средневзвешенное значение экзаменационных баллов студента А равно 78 .

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

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

Средневзвешенное значение = Σw i X i / Σw i

• w i = значения веса
• X i = значения данных

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

• Средневзвешенное значение для студента A = Σw i X i / Σw i
• Средневзвешенное значение для студента А = (2*60 + 5*90 + 70*3) / (2+5+3)
• Средневзвешенное значение для студента A = 78

Это соответствует значению, которое мы рассчитали по формуле в Excel.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Как посчитать средневзвешенное значение в excel

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

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

1. Вводится функция СУММПРОИЗВ, в аргументах которой вводятся 2 диапазона — B4:B6 с логистическими затратами на одну позицию в регионах и C4:C6 с указанием долей отгрузок по регионам.

2. Далее в строке формул после готовой формулы СУММПРОИЗВ(B4:B6;C4:C6) вводим «/» и делим на сумму «СУММ» самих долей с диапазоном суммирования C4:C6.

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

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)