Как посчитать среднюю цену из трех цен в экселе

Средневзвешенная цена в EXCEL

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

Найдем средневзвешенную цену. В отличие от средней цены, вычисляемой по формуле СРЗНАЧ(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.

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

Вычисление в EXCEL среднего по условию (один ТЕКСТовый критерий)

Найдем среднее всех ячеек, значения которых соответствуют определенному условию. Для этой цели в MS EXCEL существует простая и эффективная функция СРЗНАЧЕСЛИ() , которая впервые появилась в EXCEL 2007. Рассмотрим случай, когда критерий применяется к диапазону содержащему текстовые значения.

Пусть дана таблица с наименованием фруктов и количеством ящиков на каждом складе (см. файл примера ).

Рассмотрим 2 типа задач:

• Найдем среднее только тех значений, у которых соответствующие им ячейки (расположенные в той же строке) точно совпадают с критерием (например, вычислим среднее для значений,соответствующих названию фрукта «яблоки»);
• Найдем среднее только тех значений, у которых соответствующие им ячейки (расположенные в той же строке) приблизительно совпадают с критерием (например, вычислим среднее для значений, которые соответствуют названиям фруктов начинающихся со слова «груши»). В критерии применяются подстановочные знаки (*, ?) .

Рассмотрим эти задачи подробнее.

Критерий точно соответствует значению

Как видно из рисунка выше, яблоки бывают 2-х сортов: обычные яблоки и яблоки RED ЧИФ. Найдем среднее количество на складах ящиков с обычными яблоками. В качестве критерия функции СРЗНАЧЕСЛИ() будем использовать слово «яблоки» .

При расчете среднего, функция СРЗНАЧЕСЛИ() учтет только значения 2; 4; 5; 6; 8; 10; 11, т.е. значения в строках 6, 9-14. В этих строках в столбце А содержится слово «яблоки» , точно совпадающее с критерием.

В качестве диапазона усреднения можно указать лишь первую ячейку диапазона — функция СРЗНАЧЕСЛИ() вычислит все правильно:= СРЗНАЧЕСЛИ($A$6:$A$16;»яблоки»;B6)

Критерий «яблоки» можно поместить в ячейку D 8 , тогда формулу можно переписать следующим образом:= СРЗНАЧЕСЛИ($A$6:$A$16;D8;B6)

В критерии применяются подстановочные знаки (*, ?)

Найдем среднее содержание в ящиках с грушами. Теперь название ящика не обязательно должно совпадать с критерием «груша», а должно начинаться со слова «груша» (см. строки 15 и 16 на рисунке).

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

Решение задачи выглядит следующим образом (учитываются значения содержащие слово груши в начале названия ящика):= СРЗНАЧЕСЛИ($A$6:$A$16; «груши*»;B6)

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

Найти среднее значение, если соответствующие ячейки:

Критерий

Формула

Значения, удовлетворяющие критерию

Примечание

заканчиваются на слово яблоки

Свежие яблоки

Использован подстановочный знак * (перед значением)

содержат слово яблоки

яблоки местные , свежие яблоки или Лучшие яблоки на свете

Использован подстановочный знак * (перед и после значения)

начинаются с гру и содержат ровно 5 букв

груша или груши

Использован подстановочный знак ?

Формула СРЗНАЧЕСЛИМН среднее с несколькими условиями в Excel

Расчет среднего значения – это одна из часто используемых операций в универсальном аналитическом инструменте – Excel. В арсенале программы имеется функция СРЗНАЧЕСЛИМН, которая не просто вычисляет среднее арифметическое число, а существенно расширяет возможности этой операции выборочным расчетом с учетом нескольких условий.

Как найти среднее значение по нескольких условиях в Excel

Ниже на рисунке представлены результаты зимних олимпийских игр за 1972-ой год, который следует проанализировать. Допустим в данном примере нам необходимо рассчитать средний показатель с учетом нескольких условий. Критерии выборки данных обусловлены значениями показателей, записанных в отдельных ячейках: страна, дисциплина, медаль. Они находятся в дополнительной таблице для составления запроса к данным. Необходимо вычислить средний показатель результатов только с учетом этих критериев запроса выборки из общего списка показателей исходной таблицы. Формула выглядит так:

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

Функция СРЗНАЧЕСЛИМН обладает очень похожей структурой как в функции СУММЕСЛИМН. Первый аргумент диапазон усредняемых значений, а за ним следуют пары аргументов: Диапазон_ условия1;Условие1, Диапазон_ условия2;Условие2… и т.д. Таких пар может быть до 127 шт. В данном примере используются 3 пары критериев с условиями:

1. C2:C19;H1 – Условие выбирает только те строки, которые содержать название страны «Швейцария».
2. A2:A19;»*»&H2 – Второе условие выбирает из столбца «Дисциплина» только те ячейки в значениях которых встречается слово «женщины».
3. E2:E19;H3 – Условие выбирает только строки содержащие золотые медали.

Альтернативная формула для среднего арифметического числа с условиями

Обычно в программе Excel существует несколько путей для решения той или иной задачи. Можно ли чем-то заменить или как-то обойтись без функции СРЗНАЧЕСЛИМН? Данную функцию можно заменить формулой из комбинации СУММЕСЛИМН и СЧЁТЕСЛИМН. Формула следующая:

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

Обратите внимание на схожесть значений в аргументах всех трех функций. Так где есть возможность лучше воспользоваться функцией СРЗНАЧЕСЛИМН, так как при необходимости внести изменения в критериях достаточно поменять их лишь один раз.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Функция СРЗНАЧЕСЛИМН среднее значение в Excel по условию

Функция СРЗНАЧЕСЛИМН в Excel используется для расчета среднего арифметического числовых значений из указанного диапазона ячеек с учетом одного или нескольких условий, которые можно задать в качестве дополнительных аргументов функции.

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

Как сделать выборку средних значений по условию в Excel

Примеры использования функции СРЗНАЧЕСЛИМН в Excel.

Пример 1. Студенты, изучавшие некоторый предмет, на протяжении семестра зарабатывали баллы (до 100 баллов). При этом те, кто набрал 49 и менее баллов считается не сдавшим предмет. Определить средний балл для студентов, которые набрали 50 и более баллов (сдали).

Вид таблицы данных:

Для определения искомого значения используем формулу:

• B3:B17 – диапазон усреднения (оценки всех студентов);
• C3:C17 – диапазон, на основе данных которого будет проверяться условие;
• «сдал» — условие (проверка каждой ячейки на соответствие хранящихся в ней данных заданной текстовой строке).

В результате получим:

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

Расчет среднего чека для выбранного товара по условию в Excel

Пример 2. Перед продавцами магазина стоят следующие задачи по продажам:

• количество товаров в чеке – не менее 3 единиц;
• средняя цена в чеке – 40 у. е.;
• обязательная продажа продукции фирмы Adidas.

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

Вид таблицы данных:

Для расчетов используем следующую формулу:

• B3:B13 – диапазон ячеек с ценами в чеке;
• A3:A12 – с номерами продавцов;
• C15 – ячейка с номером продавца как критерий выбора;
• C3:C12 – с количеством единиц товаров в чеке;
• «> *»&C18&»*» – критерий выбора по названию бренда (так как наименования перечислены через запятую, для нахождения подстроки в строке используем символы «*» для указания любого числа любых символов в начале и в конце исследуемой строки).

Результат вычислений для первого продавца:

Для второго продавца:

Как видно, оба продавца справились с поставленной задачей.

Правила работы с функцией СРЗНАЧЕСЛИМН в Excel

Функция СРЗНАЧЕСЛИМН предназначена для расчета среднего значения с учетом нескольких условий. Она имеет следующую синтаксическую запись:

=СРЗНАЧЕСЛИМН( диапазон_усреднения;диапазон_условий1;условие1; [диапазон_условий2;условие2];…)

• диапазон_усреднения – обязательный для заполнения, принимает ссылку на одну ячейку или диапазон, которые содержат числа или данные другого типа, которые могут быть преобразованы в числовые. Среднее арифметическое будет рассчитано на основе данных из данного диапазона с учетом соответствующих критериев.
• диапазон_условий1 – обязательный для заполнения, принимает ссылку на одну ячейку или диапазон, которые содержат данные, на основе которых будет выполнен отбор числовых данных (первый аргумент) для расчета среднего арифметического. Последующие аргументы (диапазон_условий2…) являются необязательными для заполнения и имеют тот же смысл (для более тонкой настройки отбора данных).
• условие1 – обязательный для заполнения, принимает числовые, текстовые, ссылочные данные, имена и логические выражения, используемые в качестве критерия для отбора данных. Последующие аргументы (условие2…) необязательны для заполнения, имеют тот же смысл. Примеры:

1. 2 – выбрать значения из диапазона, которые равны числу 2.
2. «2» — то же самое (текст будет преобразован в число).
3. «>5» — рассчитать среднее арифметическое для чисел из диапазона, значения которых больше 5.
4. «аккумулятор» — рассчитать среднее арифметическое для числовых данных, сопоставимых с категорией аккумулятор.
5. A2 – использовать для проверки данные, хранящиеся в ячейке A2 (может хранить числа или данные, преобразуемые в число, а также выражения).

1. Пустые ячейки, переданные в качестве аргументов условиеX преобразуются в значения 0.
2. Ошибка #ДЕЛ/0! возникает в случае, если диапазон_усреднения содержит нечисловые данные, которые не могут быть преобразованы в числа.
3. Логические данные (ЛОЖЬ, ИСТИНА) автоматические преобразуются в соответствующие числовые значение 0 и 1.
4. Размеры и формы диапазонов условий и усреднения должны совпадать.
5. Результат работы рассматриваемой функции будет являться кодом ошибки #ДЕЛ/0!, если в результате проверки условий не были найдены данные, которые им соответствуют.
6. В логическом выражении, используемом в качестве аргумента для указания критериев поиска данных (условиеX) можно использовать подстановочные знаки («*» — любое количество любых символов, «?» — любой одиночный символ).

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры