Функция СРЗНАЧЕСЛИМН в Excel

Функция Averageifs в excel употребляется для нахождения среднего значения из мотивированного спектра ячеек, когда нужно выполнить наиболее 1-го условия, для 1-го условия мы употребляли averageif, но для нескольких критерий мы используем averageifs, он может принимать аргументы, где первым аргументом является Столбец спектра, среднее значение которого нужно вычислить, а другие — это спектр критериев, за которым следуют аспекты.

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

Средний спектр: для спектра ячеек нам необходимо взять среднее
Спектр критериев 1: исходя из этого, нам необходимо отнять среднее значение, потому в этом аргументе нам необходимо избрать этот столбец.
Критерии1: В вышеупомянутом Спектр критериев 1 для которого значение необходимо считать средним.
Спектр критериев 2: это 2-ой спектр критериев для вычисления среднего значения, потому в этом аргументе нам необходимо избрать 2-ой столбец.
Criteria2: В вышеупомянутом Спектр критериев 2 для которого значение необходимо считать средним.

Заметка: Логическое значение ИСТИНА либо ЛОЖЬ рассматривается как 1 и 0. Даже нулевое значение считается средним.

Примеры

Пример # 1

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

Функция СРЗНАЧЕСЛИМН в примере 1.1 Excel

В приведенных выше данных нам необходимо взять среднее значение для региона «Север» в месяц «март». Итак, тут нам необходимо посмотреть на два аспекта, чтоб вывести среднее значение.

Откройте функцию AVERAGEIFS на данный момент.

Так как нам необходимо взять средние реализации, чтоб избрать столбец «Реализации» для Средний спектр аргумент.

Последующий аргумент Спектр критериев 1, потому изберите столбец 9 * «Регион» в качестве ссылки.

Для этого спектра критериев нам необходимо среднее значение «Восточного» региона, потому укажите Аспект 1 как «Восток».

Последующий аргумент Спектр критериев 2, потому изберите столбец «Месяц» как ссылку.

Аспект 2 из Спектр критериев 2, за какой месяц нам необходимо отнять среднее значение. Нам необходимо среднее значение в месяц «Мар», потому укажите это в Аспекты 2.

Сейчас нажмите Enter, чтоб получить итог.

Так, средние реализации по региону «Восток» в месяц «март» составляют 2067,67.

Пример # 2

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

Из этих данных нам необходимо взять среднюю продажу меж 2-мя датами. Во-1-х, нам необходимо взять средние реализации с 27 апреля 2019 года по 26 мая 2019 года, а потом средние реализации с 26 мая 2019 года по 17 июня 2019 года.

Эти аспекты должны быть снабжены логическим оператором в знаках Excel.

Давайте сейчас откроем функцию СРЗНАЧЕСЛИМН в ячейке D4.

Изберите Средний спектр в столбце «Реализации».

Потом изберите Спектр критериев 1 как столбец даты.

Аспект 1 обязано быть больше 27 апреля 2019 г .; поначалу укажите знак «больше» в двойных кавычках, а потом соедините его с ячейкой D3.

Сейчас опять для Спектр критериев 2, изберите столбец Дата.

Последующий, Аспект 2 нам необходимы средние реализации на дату наименее 26 мая -2019. Потому введите меньше знака в двойные кавычки и соедините его с ячейкой E3.

Нажмите кнопку Enter, чтоб получить среднее значение.

Таковым образом, средний размер продаж с 27 апреля по 26 мая 2019 года составил 4620,8. Буквально так же нам необходимы средние реализации с 25 мая 2019 года по 18 июня 2019 года; просто скопируйте и вставьте формулу из ячейки D4 в ячейку D8.

Тут мы получили средние реализации 3770,21. Таковым образом, мы можем прийти к выводу, что средние реализации больше в 1-ый период времени, другими словами с 27 апреля 2019 года по 26 мая 2019 года.

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

Как работает функция срзначеслимн в excel

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

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

Что возвращает функция

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

Интересно почитать: Как в эксель сделать рисунок за текстом

Синтаксис

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) – британская версия

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

Аргументы функции

average_range (диапазон_усреднения) – спектр ячеек, по которому будет осуществлена проверка на соответствие данному аспекту;
criteria_range1, criteria_range2. (диапазон_условий) – спектр ячеек, по которому при помощи данного аспекта функция обусловит, для каких ячеек требуется провести вычисление среднего арифметического;
criteria1, criteria 2. (условие) – аспект, по которому функция описывает, для каких ячеек требуется проводить вычисления.

Доборная информация

если в спектре ячеек атрибута average_range есть текстовые значения либо пустые ячейки, функция выдаст ошибку;
аспектом могут быть числа, выражения, ссылка на ячейку, текст либо формула;
аспект обозначенный в качестве текста, математического либо логического знака (=,+,-,/,*) должны быть указаны в двойных кавычках;
в качестве аспекта могут употребляться подстановочные знаки;
вычисление среднего арифметического по атрибуту функции average_range (диапазон_усреднения) рассчитываются лишь при соблюдении всех выше обрисованных требований;
размер спектра атрибута average_range (диапазон_усреднения) и criteria_range (диапазон_условий) должны быть равны;

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

Еще более нужных приемов в работе со перечнями данных и функциями в Excel вы узнаете в практическом курсе “От новенького до мастера Excel“. Успей зарегистрироваться по ссылке!

СРЗНАЧЕСЛИМН (функция СРЗНАЧЕСЛИМН)

В данной статье описаны синтаксис формулы и внедрение функции СРЗНАЧЕСЛИМН в Microsoft Excel.

Описание

Возвращает среднее значение (среднее арифметическое) всех ячеек, которые соответствуют нескольким условиям.

Синтаксис

Аргументы функции СРЗНАЧЕСЛИМН указаны ниже.

Диапазон_усреднения: неотклонимый. Одна либо несколько ячеек для вычисления среднего с числами либо именами, массивами либо ссылками, содержащими числа.

Диапазон_условий1, диапазон_условий2, … параметр "диапазон_условий1" — неотклонимый, другие спектры критерий — нет. От 1 до 127 интервалов, в которых проверяется соответственное условие.

Условие1, условие2, … Параметр "условие1" является неотклонимым, другие условия — нет. От 1 до 127 критерий в форме числа, выражения, ссылки на ячейку либо текста, определяющих ячейки, для которых будет рассчитываться среднее. К примеру, условие быть может выражено последующим образом: 32, "32", ">32", "яблоки" либо B4.

Замечания

Если "диапазон_усреднения" является пустым либо текстовым значением, то функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.

Если ячейка в спектре критерий пустая, функция СРЗНАЧЕСЛИМН обрабатывает ее как ячейку со значением 0.

Ячейки в спектре, которые содержат значение ИСТИНА, оцениваются как 1; ячейки в спектре, которые содержат значение ЛОЖЬ, оцениваются как 0 (ноль).

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

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

Если ячейки в параметре "диапазон_усреднения" не могут быть преобразованы в численные значения, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.

Если нет ячеек, которые соответствуют условиям, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.

В этом аргументе можно применять подстановочные знаки: вопросительный символ (?) и звездочку (*). Вопросительный символ соответствует хоть какому одиночному символу; звездочка — хоть какой последовательности знаков. Если необходимо отыскать сам вопросительный символ либо звездочку, то перед ними следует поставить символ тильды (

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

Среднее значение — это среднее арифметическое, которое рассчитывается методом сложения набора чисел с следующим делением приобретенной суммы на их количество. К примеру, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.

Медиана — это число, которое является серединой огромного количества чисел, другими словами половина чисел имеют значения огромные, чем медиана, а половина чисел имеют значения наименьшие, чем медиана. К примеру, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.

Мода — это число, более нередко встречающееся в данном наборе чисел. К примеру, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

Интересно почитать: Поиск дубликатов в excel

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

Примеры

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

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

Обыкновенные логические функции такие как ЕСЛИ обычно предусмотрены для работы с одним столбцом либо одной ячейкой. Excel также дает несколько остальных логических функций служащих для агрегирования данных. К примеру, функция СУММЕСЛИ для выборочного суммирования спектра значений по условию.

Примеры формулы для суммы диапазонов с условием отбора в Excel

Ниже на рисунке представлен в таблице перечень счетов совместно с состоянием по любому счету в виде положительных либо отрицательных чисел. Допустим нам нужно посчитать сумму всех отрицательных чисел для расчета суммарного расхода по движению денежных средств. Этот итог будет позднее сравниваться совместно с сумой положительных чисел с целью верификации и вывода балансового сальдо. Узнаем однообразные ли суммы доходов и расходов — сойдется ли у нас дебит с кредитом. Для суммирования числовых значений по условию в Excel применяется логическая функция =СУММЕСЛИ():

Функция СУММЕСЛИ анализирует каждое значение ячейки в спектре B2:B12 и инспектирует соответствует ли оно данному условию (обозначенному во 2-м аргументе функции). Если значение меньше чем 0, тогда условие выполнено и данное число учитывается в общей итоговой сумме. Числовые значения больше либо равно нулю игнорируются функцией. Проигнорированы также текстовые значения и пустые ячейки.

В приведенном примере поначалу проверяется значения ячейки B2 и потому что оно больше чем 0 — будет проигнорировано. Дальше проверяется ячейка B3. В ней числовое значение меньше нуля, означает условие выполнено, потому оно добавляется к общей сумме. Данный процесс повторяется для каждой ячейки. В итоге его выполнения суммированы значения ячеек B3, B6, B7, B8 и B10, а другие ячейки не учитываются в итоговой сумме.

Направьте внимание что ниже результата суммирования отрицательных чисел находится формула суммирования положительных чисел. Единственное отличие меж ними — это оборотный оператор сопоставления во 2-м аргументе где указывается условие для суммирования — заместо строчки «<0» (меньше чем ноль) употребляется строчка «>0» (больше чем ноль). Сейчас мы можем убедиться в том, что дебет с кредитом сходится балансовое сальдо будет равно нулю если сложить арифметически в ячейке B16 формулой =B15+B14.

Пример логического выражения в формуле для суммы с условием

Иной пример, когда нам необходимо раздельно суммировать цены на группы продуктов цены до 1000 и раздельно со стоимостью больше 1000. В таком случае 1-го оператора сопоставления нам недостаточно (<1000) придется применять символ «меньше либо равно» (<=1000) по другому цены со значением ровно 1000 не будут включены в расчеты. Наиболее того в таком случае мы уже не можем применять в иной формуле «больше либо равно» (>=1000) по другому мы просуммируем сумму ровно в 1000 — 2 раза, что приведет к неверным итоговым результатам:

Это весьма всераспространенная ошибка юзеров Excel при работе с логическими функциями!

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

2-ой аргумент функции СУММЕСЛИ, другими словами условие, которое обязано быть выполнено, записывается меж двойными кавычками. В данном примере употребляется знак сопоставления — «меньше» (<). По синтаксису функции нужно представлять такие записи как строчка, другими словами в двойных кавычках. По другому Excel выдаст предупреждение о ошибке в формуле.

Синтаксис формулы суммирования по условию в Excel

В синтаксисе функции СУММЕСЛИ находится необязательный для наполнения 3-ий аргумент — спектр суммирования. В прошлом примере условие проверялось по тому же спектру, который необходимо было суммировать. Благодаря третьему аргументу возникает возможность инспектировать условия по одному спектру, а суммировать иной.

Интересно почитать: Смещение в эксель

Ниже на рисунке представлена таблица отчета продаж по клиентам в месяц. Напротив, всякого клиента указана сумма, на которую сделал покупки клиент в протяжении месяца. Некие клиенты в месяц сделали по несколько покупок, потому их наименование дублируется в перечне. Нужно выяснить на какую общую сумму купил продукта «Клиент3» в данном месяце. Тут очень уместно применять формулу с наполнением третьего аргумента функции СУММЕСЛИ:

Как видно на всех выше приведенных примерах во 2-м аргументе указывается строчка текста с условием, а не логическое выражение как в функции ЕСЛИ. На практике использования функции СУММЕСЛИ этот 2-ой логический аргумент с условием может содержать:

сроки с текстовым значением;
числовые значения;
логические выражения;
формулы с функциями;
ссылки на ячейки.

Дальше разглядим больше примеров составления критерий для второго логического аргумента функции СУММЕСЛИ наиболее детально.

Создание критериев критерий для функции СУММЕСЛИ

2-ой аргумент функции именуется «Аспект». Данный логический аргумент употребляется и в остальных схожих логических функциях: СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. В любом случаи аргумент заполняется согласно одних и тех же правил составления логических критерий. Иными словами, для всех этих функций 2-ой аргумент с аспектом критерий является логическим выражением возвращающим итог ИСТИНА либо ЛОЖЬ. Это означает, что выражение обязано содержать оператор сопоставления, к примеру: больше (>) меньше (<) равно (=) неравно (<>), больше либо равно (>=), меньше либо равно (<=). Кроме можно не указывать оператор равно (=), если обязано быть испытано четкое совпадение значений.

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

Таблица правил составления критериев критерий:

Чтоб сделать условие	Примените правило	Пример
Значение равно данному числу либо ячейке с данным адресом.	Не используйте символ равенства и двойных кавычек.	=СУММЕСЛИ(B1:B10;3)
Значение равно текстовой строке.	Не используйте символ равенства, но используйте двойные кавычки по бокам.	=СУММЕСЛИ(B1:B10;»Клиент5″)
Значение различается от данного числа.	Расположите оператор и число в двойные кавычки.	=СУММЕСЛИ(B1:B10;»>=50″)
Значение различается от текстовой строчки.	Расположите оператор и число в двойные кавычки.	=СУММЕСЛИ(B1:B10;»<>выплата»)
Значение различается от ячейки по обозначенному адресу либо от результата вычисления формулы.	Расположите оператор сопоставления в двойные кавычки и соедините его эмблемой амперсант (&) совместно со ссылкой на ячейку либо с формулой.	=СУММЕСЛИ(A1:A10;»<«&C1) либо =СУММЕСЛИ(B1:B10;»<>»&СЕГОДНЯ())
Значение содержит фрагмент строчки	Используйте операторы неоднозначных знаков и расположите их в двойные кавычки	=СУММЕСЛИ(A1:A10;»кг»;B1:B10)

Во 2-м аргументе критериев критерий можно применять различные функции и формулы. Ниже на рисунке изображен перечень дат и присвоенных им значений. Принципиально отметить что сейчас на момент написания статьи дата — «03.11.2018». Чтоб суммировать числовые значения лишь по нынешней дате используйте формулу:

Чтоб суммировать лишь значения от нынешнего денька включительно и до конца периода времени воспользуйтесь оператором «больше либо равно» (>=) совместно с соответствующей функцией =СЕГОДНЯ(). Формула c операторам (>=):

Суммирование по неточному совпадению в условии аспекта отбора

Во 2-м логическом аргументе критериев критерий функции СУММЕСЛИ можно использовать неоднозначные знаки — (?)и(*) для составления относительных неточных запросов. Символ вопросца (?) — следует читать как хоть какой знак, а звездочка (*) — это строчка из хоть какого количества всех знаков либо пустая строчка. К примеру, нам нужно просуммировать лишь защитные краски-лаки с кодом 3 английские буковкы в начале наименования:

Суммируются все значения ячеек в спектре B2:B16 в согласовании со значениями в ячейках спектра A2:A16, в которых опосля третьего знака фрагмент строчки «-защита».

Таковым образом удалось суммировать лишь определенную группу продуктов в общем перечне отчета по складу. Данный фрагмент наименования продукта должен встречаться в определенном месте — 3 знака от начала строчки. Нет необходимости применять сложные формулы с функцией =ЛЕВСИМВ() и т.д. Довольно только пользоваться операторами неоднозначных знаков чтоб сконструировать обычный и лаконичный запрос к базе данных с минимальными перегрузками на системные ресурсы.