Средневзвешенная цена в EXCEL. Примеры и описание

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

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

Оценки учащихся часто рассчитываются с использованием этого подхода, что видно на следующем скриншоте. Обычное среднее значение легко вычисляется с помощью СРЗНАЧ . Однако мы хотим, чтобы итог учитывал значимость каждого вида занятий, указанного в столбце C.

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

В этом примере, чтобы посчитать средневзвешенную итоговую оценку, вы умножаете каждый полученный балл на соответствующий процент (преобразованный в десятичную дробь), складываете эти 5 произведений вместе и делите это число на итог сложения пяти весов:

((91 * 0,1) + (85 * 0,05) + (80 * 0,2) + (73 * 0,25) + (68 * 0,4)) / (0,1 + 0,05 + 0,2 + 0,25 + 0,4) = 74,8

Обычная средняя оценка (79,4) и средневзвешенная (74,8) – это разные величины.

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

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

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

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

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

СРЗНАЧЕСЛИ можно вызвать как из панели, так и при помощи мастера, также она достаточно простая для ввода вручную. После чего требуется ввести диапазон подсчета и условие.

В выделенном звене будет прописан результат с учетом заданного условия. Также в случае если условие будет отсутствовать, произведется простой расчёт среднего указанного диапазона.

Для выполнения этой задачи используются функции СРЗНАЧ и если . Скопируйте приведенную ниже таблицу и имейте в виду, что в этом примере чтобы проще было понять, скопируйте его на пустой лист.

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

Математический ввод не учитывает, что количество коробок составляет более 30 центов, чем 20 центов. Чтобы получить правильное среднее значение, используйте эту формулу для получения результата (28 центов на каждую отгрузку).

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

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

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

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

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

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

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

Что такое функция срзнач в excel и для чего она нужна. Как найти среднее значение в Excel

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

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

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

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

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

Как в экселе определить среднее значение. Как вычислять средние значения в Excel

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

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

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

Большинство курсов будут иметь кредитную ценность от 1 до 5 кредитов, и ваша общая оценка будет взвешиваться по количеству кредитов, полученных за каждый курс.

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

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

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

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

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

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

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

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

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

Если вы хотите подсчитать среднее арифметическое между числами, находящимися в разрозненных группах ячеек, то те же самые действия, о которых говорилось выше, проделывайте в поле «Число 2». И так до тех пор, пока все нужные группы ячеек не будут выделены.

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

• Для проверки правильности ответа в любой пустой ячейке введите формулу «=A11/10».
• Среднее арифметическое хорошо применимо для центрирования распределения, когда отдельные числа в некотором множестве чисел не очень отличаются друг от друга.

В нашем примере мы предоставили функции два набора данных: значения от B2 до B7 и значения от C2 до C7. Вы можете использовать столько наборов данных, сколько захотите, при условии, что каждый набор данных имеет одинаковое количество значений.

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

Существует ещё третий способ запустить функцию «СРЗНАЧ». Для этого, переходим во вкладку «Формулы». Выделяем ячейку, в которой будет выводиться результат. После этого, в группе инструментов «Библиотека функций» на ленте жмем на кнопку «Другие функции». Появляется список, в котором нужно последовательно перейти по пунктам «Статистические» и «СРЗНАЧ».

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

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

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

1. Поставьте курсор в ячейку.
2. Перейдите в раздел «Формулы».
3. Найдите кнопку, название которой начинается на «Авто». Это зависит от выбранной в Excel опции по умолчанию («Автосумма», «Авточисло», «Автосмещ», «Автоиндекс»).
4. Нажмите на чёрную стрелочку под ней.
5. Выберите «МИН» (минимальное значение), «МАКС» (максимальное) или «СРЗНАЧ» (среднее).
6. В отмеченной клетке появится формула. Кликните на любую другую ячейку — она будет добавлена в функцию. «Растяните» рамку вокруг неё, чтобы охватить диапазон. Или щёлкайте по сетке с зажатой клавишей Ctrl, чтобы выделять по одному элементу.
7. Когда закончите, нажмите Enter. Результат отобразится в клетке.

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