Функция СРЗНАЧЕСЛИ в Excel и примеры ее работы в анализе
Функция СРЗНАЧЕСЛИ (на британском AVERAGEIF) в Excel употребляется для вычисления среднего арифметического по данном спектру данных и аспекту.
КАК РАБОТАЕТ ФУНКЦИЯ СРЗНАЧЕСЛИ В EXCEL?
ПРИМЕР 1. Нужно найти среднее значение продаж, превышающее 70000 рублей.
Ставим равно (=) и пишем функцию СРЗНАЧЕСЛИ. Открывает круглую скобку и вводим характеристики функции.
Мы будем делать оценку и вычислять среднее число для 1-го и такого же спектра ячеек. С (С2 по С7) в столбце реализации.
Вводим точку с запятой, указываем в кавычках условие, по которому будет оцениваться спектр. Кавычки необходимы для того, чтоб Excel верно интерпретировал оператор и аргументы функции. В нашем случае это больше > 70000 рублей.
Жмем кнопку Enter и нам указывает готовый итог продаж в среднем. Средний показатель продаж, превосходящих 70000 рублей составляет 79488 рублей.
Функция оценивает сколько ячеек в столбце реализации соответствуют условию >70000. Таковых ячеек 3 (С3, С5, С7). Потом функция вычисляет для их усредненный показатель.
Средняя сумма покупок при условии в функции СРЗНАЧЕСЛИ
ПРИМЕР 2. Определим среднее значение суммы покупок с числом покупок >8. В этом примере для 1-го спектра ячеек производится оценка по условию, а для другого рассчитывается числовое значение в среднем. Вводим символ равенства (=), функцию СРЗНАЧЕСЛИ.
Откроем скобку и введем спектр ячеек, которые необходимо оценить (В2 по B6).
Вводим точку с запятой и в кавычках указываем значение, по которому будет оцениваться спектр (>8).
Вводим точку с запятой и спектр ячеек, для которого необходимо вычислить среднее значение (С2 по С6). Полная формула смотрится последующим образом:
Жмем кнопку Enter. Среднее число суммы покупок с числом покупок >8 составляет 3479 рублей.
Поначалу функция оценивает какие ячейки с B2 по B6 соответствуют условию >8. Таковых ячеек 3 (B2, B3, B6). Потом функция вычисляет средний показатель в столбце сумма, соответственное число которых в столбце B удовлетворяет условию.
Вычисление средних продаж по каждой группе продуктов
ПРИМЕР 3. Рассчитаем средние реализации по каждой группе продуктов в отдельности. Вводим равно (=) и функцию СРЗНАЧЕСЛИ.
Выделяем спектр, в котором находится разыскиваемый аспект (А2 по А12). Фиксируем ее абсолютной ссылкой (F4).
Ставим точку с запятой и вводим ячейку условие (Фрукты).
Опосля выделяем спектр суммирования (С2 по С12) и фиксируем абсолютной ссылкой (F4).
Жмем кнопку Enter. Протягиваем формулу вниз.
Среднее число проданных овощей и фруктов составляет 15 и 18 кг.
Поначалу функция оценивает какие ячейки с A2 по A12 соответствуют условию ячейки F2 (Фрукты). Потом функция вычисляет среднее число килограмм в столбце продано.
Функции СРЗНАЧЕСЛИ и СРЕДНЕЛИ — Средние значения, если — Excel и Гугл Таблицы
Вы сможете применять функцию СРЗНАЧЕСЛИ в Excel для подсчета ячеек, содержащих определенное значение, подсчета ячеек, которые больше либо равны значению и т. Д.
Чтоб применять функцию рабочего листа Excel СРЗНАЧЕСЛИ, изберите ячейку и введите:
(Направьте внимание, как возникают входные данные формулы)
Синтаксис и аргументы функции СРЗНАЧЕСЛИ:
спектр — Спектр подсчитываемых ячеек.
аспекты — Аспекты, определяющие, какие ячейки следует подсчитывать.
средний_ спектр — [необязательно] Ячейки для усреднения. Если не обозначено, употребляется спектр.
Что такое функция СРЗНАЧЕСЛИ?
Функция СРЗНАЧЕСЛИ — одна из старенькых функций, применяемых в электрических таблицах. Он употребляется для сканирования спектра ячеек, проверяя определенный аспект, а потом выдает среднее значение (также называемое математическим средним) для значений в спектре, которые соответствуют сиим значениям. Начальная функция СРЗНАЧЕСЛИ ограничивалась лишь одним аспектом. Опосля 2007 года была сотворена функция СРЗНАЧЕСЛИМН, которая дозволяет применять огромное количество критериев. В главном они употребляются идиентично, но есть некие принципиальные различия в синтаксисе, которые мы обсудим в данной статье.
Если вы еще этого не сделали, вы сможете просмотреть огромную часть аналогичной структуры и примеры в статье СЧЁТЕСЛИМН.
Базисный пример
Давайте разглядим этот перечень зарегистрированных продаж, и мы желаем знать средний доход.
Так как у нас были расходы, отрицательное значение, мы не можем просто вычислить базисное среднее значение. Заместо этого мы желаем усреднить лишь те значения, которые больше 0. «Больше 0» — это то, что будет нашим аспектом в функции СРЗНАЧЕСЛИ. Наша формула для утверждения, что это
Пример с 2-мя столбцами
Хотя начальная функция СРЗНАЧЕСЛИ была разработана, чтоб дозволить для вас применить аспект к спектру чисел, которые вы желаете суммировать, огромную часть времени для вас будет нужно применить один либо несколько критериев к остальным столбцам. Разглядим эту таблицу:
Сейчас, если мы воспользуемся начальной функцией СРЗНАЧЕСЛИ, чтоб выяснить, сколько бананов у нас в среднем. Мы поместим наши аспекты в ячейку D1, и нам необходимо будет указать спектр, который мы желаем в среднем в качестве крайнего аргумента, и потому наша формула будет
Но, когда программеры в конце концов сообразили, что юзеры желают давать наиболее 1-го аспекта, была сотворена функция СРЗНАЧЕСЛИМН. Чтоб сделать одну структуру, которая будет работать для хоть какого количества критериев, для СРЕДНЕЛИН нужно, чтоб спектр сумм был указан первым. В нашем примере это значит, что формула обязана быть
ПРИМЕЧАНИЕ. Эти две формулы дают однообразный итог и могут смотреться приблизительно идиентично, потому обратите особенное внимание на то, какая функция употребляется, чтоб убедиться, что вы перечислили все аргументы в правильном порядке.
Работа с датами, несколько критериев
При работе с датами в электрической таблице, хотя можно ввести дату конкретно в формулу, идеальнее всего иметь дату в ячейке, чтоб вы могли просто ссылаться на ячейку в формуле. К примеру, это помогает компу осознать, что вы желаете применять дату 27.05.2020, а не число 5, разделенное на 27, разделенное на 2022 год.
Давайте поглядим на нашу последующую таблицу, в которой регится количество гостей веб-сайта любые две недельки.
Мы можем указать исходную и конечную точки спектра, который мы желаем поглядеть в D2 и E2. Наша формула для определения среднего числа гостей в этом спектре быть может:
Направьте внимание, как мы смогли соединить сопоставления «=» со ссылками на ячейки для сотворения критериев. Не считая того, хотя оба аспекта применялись к одному и тому же спектру ячеек (A2: A7), для вас нужно записать спектр два раза, по одному разу для всякого аспекта.
Несколько столбцов
При использовании нескольких критериев вы сможете применить их к тому же спектру, что и в прошлом примере, либо вы сможете применить их к различным спектрам. Давайте объединим наши эталоны данных в эту таблицу:
Мы настроили несколько ячеек, чтоб юзер мог вводить то, что он желает находить, в ячейках с E2 по G2. Таковым образом, нам нужна формула, которая суммирует полное количество яблок, собранных в феврале. Наша формула смотрится так:
AVERAGEIFS с логикой типа ИЛИ
Ранее момента все примеры, которые мы употребляли, основывались на сопоставлении на базе И, когда мы отыскиваем строчки, надлежащие всем нашим аспектам. Сейчас разглядим вариант, когда вы желаете отыскать возможность строчки, соответственной тому либо иному аспекту.
Давайте поглядим на этот перечень продаж:
Мы желали бы сложить средние реализации для Адама и Боба. Во-1-х, резвое обсуждение усреднения. Если у вас нечетное количество вещей, к примеру 3 записи для Адама и 2 для Боба, вы не сможете просто взять среднее значение продаж всякого человека. Это именуется усреднением средних значений, и в итоге вы придаете несправедливый вес элементу, в котором не много записей. Если это так с вашими данными, для вас необходимо будет вычислить среднее значение «вручную»: возьмите сумму всех ваших продуктов, разбитую на количество ваших продуктов. Чтоб выяснить, как это создать, вы сможете ознакомиться со статьями тут:
Сейчас, если количество записей такое же, как в нашей таблице, у вас есть несколько вариантов, которые вы сможете создать. Самый обычной — сложить два СРЗНАЧЕСЛИМН совместно, вот так, а потом поделить на 2 (количество частей в нашем перечне).
Тут комп подсчитывает наши личные баллы, а потом мы складываем их.
Наш последующий вариант подступает, когда у вас больше диапазонов критериев, так что для вас не надо повторно переписывать всю формулу. В предшествующей формуле мы вручную отдали приказ компу сложить два различных СРЕДНЕГО РАЗМНЕНИЯ. Но вы также сможете создать это, записав свои аспекты снутри массива, к примеру:
Поглядите, как построен массив в фигурных скобках. Когда комп вычисляет эту формулу, он будет знать, что мы желаем вычислить функцию СРЗНАЧЕСЛИМН для всякого элемента в нашем массиве, создав таковым образом массив чисел. Потом наружная функция AVERAGE возьмет этот массив чисел и превратит его в одно число. Пошаговая оценка формулы будет смотреться так:
Получаем этот же итог, но формулу удалось записать наиболее лаконически.
Работа с пробелами
Время от времени в вашем наборе данных есть пустые ячейки, которые для вас необходимо или отыскать, или избежать. Настройка критериев для их быть может мало сложной, потому давайте разглядим иной пример.
Направьте внимание, что ячейка A3 вправду пуста, а в ячейке A5 есть формула, возвращающая строчку нулевой длины «». Если мы желаем отыскать общее среднее значение вправду пустые ячейки, мы употребляли бы аспект «=», и наша формула смотрелась бы так:
С иной стороны, если мы желаем получить среднее значение для всех ячеек, которые зрительно смотрятся пустыми, мы изменим аспект на «», и формула будет смотреться так:
Давайте перевернем: что, если вы желаете отыскать среднее значение непустых ячеек? К огорчению, текущий дизайн не дозволяет избежать строчки нулевой длины. Вы сможете применять аспект «», но, как вы сможете созидать в примере, он как и раньше включает значение из строчки 5.
Если для вас необходимо не подсчитывать ячейки, содержащие строчки нулевой длины, вы сможете разглядеть возможность использования функции LEN снутри СУММПРОИЗВ.
AVERAGEIF в Гугл Таблицах
Функция СРЗНАЧЕСЛИ работает в Гугл Таблицах буквально так же, как и в Excel:
Срзначесли в excel примеры
Возвращает количество размещений для данного числа объектов, которые выбираются из общего числа объектов. Размещения — это хоть какое огромное количество либо подмножество объектов либо событий, в котором важен внутренний порядок. Размещения различаются от сочетаний, для которых внутренний порядок не имеет значения. Эта функция употребляется, к примеру, для вычисления вероятностей в лотереях.
Число — целое число, задающее количество объектов.
Число_выбранных — целое число, задающее количество объектов в любом размещении.
Оба аргумента усекаются до целых. Если число либо число_выбранных не является числом, то функция ПЕРЕСТ возвращает значение ошибки #ЗНАЧ!. Если число ? 0 либо число_выбранных < 0, функция ПЕРЕСТ возвращает значение ошибки #ЧИСЛО!. Если число < число_выбранных, то функция ПЕРЕСТ возвращает значение ошибки #ЧИСЛО!.
Уравнение для числа размещений имеет последующий вид:
Представим, что необходимо найти шансы на получение выигрышного лотерейного номера. Лотерейный номер состоит из 3-х чисел, каждое из которых может находиться в спектре от 0 (ноль) до 99 включительно. В этом случае количество вероятных размещений определяется при помощи последующей функции.
Вычисление средних значений
Функция СРЗНАЧ
Возвращает среднее значение (среднее арифметическое) аргументов. СРЗНАЧ(число1, [число2]. )
Число1. Неотклонимый аргумент. 1-ое число, ссылка на ячейку либо спектр, для которого требуется вычислить среднее значение.
Число2, . Необязательный аргумент. Доп числа, ссылки на ячейки либо спектры, для которых требуется вычислить среднее значение. Аргументов быть может не наиболее 255.
Аргументы могут быть числами, именами либо ссылками на спектры либо ячейки, содержащие числа. Учитываются логические значения и текстовые представления чисел, которые конкретно введены в перечень аргументов. Если аргумент является ссылкой на спектр либо ячейку, содержащую текст либо логические значения, либо ссылкой на пустую ячейку, то такие значения игнорируются; но ячейки, которые содержат нулевые значения, учитываются. Аргументы, являющиеся значениями ошибок либо текстом, которые не могут быть преобразованы в числа, вызывают ошибки.
Если логические значения и текстовые представления чисел нужно учесть в расчетах, используйте функцию СРЗНАЧА.
Функция СРЗНАЧ вычисляет среднее значение, другими словами центр набора чисел в статистическом распределении. Существует три более всераспространенных метода определения среднего значения, обрисованных ниже.
Среднее значение — это среднее арифметическое, которое рассчитывается методом сложения набора чисел с следующим делением приобретенной суммы на их количество. К примеру, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
Медиана — это число, которое является серединой огромного количества чисел, другими словами половина чисел имеют значения огромные, чем медиана, а половина чисел имеют значения наименьшие, чем медиана. К примеру, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
Мода — это число, более нередко встречающееся в данном наборе чисел. К примеру, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.
При симметричном распределении огромного количества чисел эти величины оценки степени централизации равны. При ассиметричном распределении огромного количества чисел они могут различаться.
Функция СРЗНАЧЕСЛИ
Если требуется вычислить среднее значение лишь для тех значений, которые удовлетворяют определенным аспектам, употребляют функцию СРЗНАЧЕСЛИ.
Возвращает среднее значение (среднее арифметическое) всех ячеек в спектре, которые соответствуют данному условию.
СРЗНАЧЕСЛИ(спектр, условие, «диапазон_усреднения»)
Спектр — одна либо несколько ячеек для вычисления среднего, включающих числа либо имена, массивы, либо ссылки, содержащие числа.
Условие — условие в форме числа, выражения, ссылки на ячейку либо текста, которое описывает ячейки, участвующие в вычислении среднего. К примеру, условие быть может выражено последующим образом: 32, «32», «>32», «яблоки» либо B4.
Диапазон_усреднения — фактическое огромное количество ячеек для вычисления среднего. Если он не указан, употребляется спектр.
Ячейки в спектре, которые содержат значения ИСТИНА либо ЛОЖЬ, игнорируются. Если ячейка в «диапазоне_усреднения» пустая, функция СРЗНАЧЕСЛИ игнорирует ее. Если спектр является пустым либо текстовым значением, то функция СРЗНАЧЕСЛИ возвращает значение ошибки #ДЕЛ/0!. Если ячейка в условии пустая, «СРЗНАЧЕСЛИ» обрабатывает ее как ячейки со значением 0. Если ни одна ячейка в спектре не соответствует условию, функция СРЗНАЧЕСЛИ возвращает ошибку «#ДЕЛ/0!».
В этом аргументе можно применять подстановочные знаки: вопросительный символ (?) и звездочку (*). Вопросительный символ соответствует хоть какому одиночному символу; звездочка — хоть какой последовательности знаков. Если необходимо отыскать сам вопросительный символ либо звездочку, то перед ними следует поставить символ тильды (
Значение «диапазон_усреднения» не непременно обязано совпадать по размеру и форме с спектром. При определении фактических ячеек, для которых рассчитывается среднее, в качестве исходной употребляется верхняя левая ячейка в «диапазон_усреднения», а потом добавляются ячейки с совпадающим размером и формой. К примеру:
