Как использовать срзначесли в excel
Функция AVERAGEIF (СРЗНАЧЕСЛИ) в Excel. Как использовать?
Функция СРЗНАЧЕСЛИ (AVERAGEIF) в Excel используется для вычисления среднего арифметического по заданном диапазону данных и критерию.
Что возвращает функция
Возвращает число, обозначающее среднее арифметическое по заданному диапазону данных и критерию, указанному в качестве аргумента.
Синтаксис
=AVERAGEIF(range, criteria, [average_range]) — английская версия
=СРЗНАЧЕСЛИ(диапазон, условия, [диапазон_усреднения]) — русская версия
Аргументы функции
Дополнительная информация
Примеры использования функции СРЗНАЧЕСЛИ в Excel
Пример 1. Вычисляем среднее арифметическое по критерию
На примере выше функция проверяет список значений в диапазоне «А2:А6» на соответствие критерию «Андрей» и вычисляет по соответствующим критерию ячейкам среднее арифметическое в диапазоне ячеек «В2:В6».
Так как в диапазоне «А2:А6» указаны данные для двух ячеек «Андрей» — «62» и «19», то функция вычисляет среднее арифметическое — «40.5».
Пример 2. Используем подстановочные знаки в функции СРЗНАЧЕСЛИ
Вы можете использовать подстановочные знаки в критерии функции.
» — используется, если нужно найти сам вопросительный знак или звездочку.
На примере выше, функция проверяет список данных в диапазоне «А2:А6» на соответствие критерию с подстановочными знаками «*а*», который подразумевает любые данные содержащие букву «а». Так как в диапазоне данных «А2:А6» этому критерию соответствуют все имена кроме «Олег» => среднее арифметическое будет вычислено по диапазону ячеек B2:B6 (исключая ячейку B5) = «29.5».
Пример 3. Используем операторы сравнения в функции СРЗНАЧЕСЛИ
В случаях, когда вы не указываете аргумент average_range (диапазон_усреднения), она, автоматически, производит расчеты из заданного диапазона ячеек в аргументе range (диапазон).
На примере выше, функция определяет по заданному критерию «>19» какие ячейки из диапазона «B2:B6» больше числа «19» и вычисляет по ним среднее арифметическое. Важно, любые операторы следует указывать в двойных кавычках!
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!
СРЗНАЧ (функция СРЗНАЧ)
В этой статье описаны синтаксис формулы и использование функции С AVERAGE в Microsoft Excel.
Описание
Возвращает среднее арифметическое аргументов. Например, если диапазон A1:A20 содержит числа, формула =СПБ(A1:A20) возвращает среднее из этих чисел.
Синтаксис
Аргументы функции СРЗНАЧ описаны ниже.
Число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.
При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. При ассиметричном распределении множества чисел они могут отличаться.
Совет: При вычислении средних значений ячеек следует учитывать различие между пустыми ячейками и ячейками, содержащими нулевые значения, особенно если в диалоговом окне Параметры Excel настольного приложения Excel снят флажок Показывать нули в ячейках, которые содержат нулевые значения. Если этот флажок установлен, пустые ячейки игнорируются, но нулевые значения учитываются.
Местонахождение флажка Показывать нули в ячейках, которые содержат нулевые значения
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
СРЗНАЧЕСЛИМН (функция СРЗНАЧЕСЛИМН)
В этой статье описаны синтаксис формулы и использование функции СРЗНАЧЕСЛИМН в Microsoft Excel.
Описание
Возвращает среднее значение (среднее арифметическое) всех ячеек, которые соответствуют нескольким условиям.
Синтаксис
Аргументы функции СРЗНАЧЕСЛИМН указаны ниже.
Диапазон_усреднения: обязательный. Одна или несколько ячеек для вычисления среднего с числами или именами, массивами или ссылками, содержащими числа.
Диапазон_условий1, диапазон_условий2, … параметр «диапазон_условий1» — обязательный, остальные диапазоны условий — нет. От 1 до 127 интервалов, в которых проверяется соответствующее условие.
Условие1, условие2, … Параметр «условие1» является обязательным, остальные условия — нет. От 1 до 127 условий в форме числа, выражения, ссылки на ячейку или текста, определяющих ячейки, для которых будет вычисляться среднее. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.
Замечания
Если «диапазон_усреднения» является пустым или текстовым значением, то функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
Если ячейка в диапазоне условий пустая, функция СРЗНАЧЕСЛИМН обрабатывает ее как ячейку со значением 0.
Ячейки в диапазоне, которые содержат значение ИСТИНА, оцениваются как 1; ячейки в диапазоне, которые содержат значение ЛОЖЬ, оцениваются как 0 (ноль).
Каждая ячейка в аргументе «диапазон_усреднения» используется в вычислении среднего значения, только если все указанные для этой ячейки условия истинны.
В отличие от аргументов диапазона и условия в функции СРЗНАЧЕСЛИ, в функции СРЗНАЧЕСЛИМН каждый диапазон_условий должен быть одного размера и формы с диапазоном_суммирования.
Если ячейки в параметре «диапазон_усреднения» не могут быть преобразованы в численные значения, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/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.
При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. При смещенном распределении множества чисел значения могут быть разными.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Функция СРЗНАЧЕСЛИ в Excel и примеры ее работы в анализе
Функция СРЗНАЧЕСЛИ (на английском AVERAGEIF) в Excel используется для вычисления среднего арифметического по заданном диапазону данных и критерию.
КАК РАБОТАЕТ ФУНКЦИЯ СРЗНАЧЕСЛИ В EXCEL?
ПРИМЕР 1. Необходимо определить среднее значение продаж, превышающее 70000 рублей.
Ставим равно (=) и пишем функцию СРЗНАЧЕСЛИ. Открывает круглую скобку и вводим параметры функции.
Мы будем выполнять оценку и вычислять среднее число для одного и того же диапазона ячеек. С (С2 по С7) в столбце продажи.
Вводим точку с запятой, указываем в кавычках условие, по которому будет оцениваться диапазон. Кавычки нужны для того, чтобы Excel правильно интерпретировал оператор и аргументы функции. В нашем случае это больше > 70000 рублей.
Нажимаем клавишу Enter и нам показывает готовый результат продаж в среднем. Средний показатель продаж, превышающих 70000 рублей составляет 79488 рублей.
Функция оценивает сколько ячеек в столбце продажи соответствуют условию >70000. Таких ячеек 3 (С3, С5, С7). Затем функция вычисляет для них усредненный показатель.
Средняя сумма покупок при условии в функции СРЗНАЧЕСЛИ
ПРИМЕР 2. Определим среднее значение суммы покупок с числом покупок >8. В этом примере для одного диапазона ячеек выполняется оценка по условию, а для другого вычисляется числовое значение в среднем. Вводим знак равенства (=), функцию СРЗНАЧЕСЛИ.
Откроем скобку и введем диапазон ячеек, которые нужно оценить (В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 для разных типов данных с помощью формул СРЗНАЧ или СРЗНАЧА. Вы также узнаете, как использовать функции СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН для усреднения ячеек, соответствующих определенным критериям.
Средняя зарплата… Средняя продолжительность жизни… Ежедневно мы с вами слышим эти словосочетания, служащие для описания множества одним единственным числом. Но, как ни странно, «среднее значение» — достаточно коварное понятие, часто вводящее в заблуждение обычного, неискушенного в математической статистике, человека. Например, в какой-то абстрактной компании работает десять сотрудников. Девять из них получают зарплату около 50 000 рублей, а один 1 500 000 рублей (по странному совпадению он же является генеральным директором этой компании).
В математике среднее называется средним арифметическим, и оно рассчитывается путем сложения группы чисел, а затем деления на количество этих чисел.
В приведенном выше примере средняя зарплата составляет: 195 000 рублей =(50000*9+1500000)/10. Вряд ли эта цифра соответствует реальной ситуации, но таковы расчёты среднего арифметического значения.
И всё же в большинстве случаев знать среднее весьма полезно.
Как посчитать среднее арифметическое в Экселе? Вам не нужно писать сложные математические выражения. Есть несколько функций «на все случаи жизни».
Функция СРЗНАЧ.
СРЗНАЧ применяется для возврата среднего арифметического указанных ячеек.
Использование функции СРЗНАЧ – на примерах.
Пример 1. Расчет среднего из нескольких чисел.
Вы можете указать числа непосредственно. Например,
=СРЗНАЧ(1;2;3;4) возвращает результат 2,5.
Чтобы вычислить среднее по столбцу, укажите ссылку на него целиком:
=СРЗНАЧ(A:A)
Чтобы получить по строке, введите ссылку на нее:
=СРЗНАЧ(1:1)
Чтобы вычислить в каком-то диапазоне, укажите его:
=СРЗНАЧ(A1:C20)
Чтобы вернуть среднее из несмежных ячеек, запишите каждую ячейку отдельно, например
=СРЗНАЧ(A1; C1; D1)
И, естественно, ничто не мешает вам включать числа, ссылки и диапазоны в одну формулу. Например, следующее выражение вычисляет среднее из 2 диапазонов и 1 отдельного значения:
=СРЗНАЧ(B3:B5; C7:D9; B11)
Примечание. Если вы хотите округлить полученное число до ближайшего целого, примените одну из функций округления, например:
Помимо чисел, вы можете использовать функцию СРЗНАЧ для вычисления среднего на основе других видов чисел, таких как проценты и время. Это показано в следующих примерах.
Пример 2. Расчет среднего процента.
Если у вас есть столбец с процентами на вашем листе, как вы получаете средний процент выполнения? Возьмём обычную формулу Excel для среднего 🙂
Примечание. Обратите внимание, что СРЗНАЧ учитывает и нулевые значения при расчете. А это существенно повлияет на итоговую цифру. Если вы предпочитаете исключать нули из расчета, возьмите вместо этого СРЗНАЧЕСЛИ, как будет показано чуть ниже. ССЫЛКА
Пример 3. Расчет среднего времени.
Этим совсем не так просто, как с обычными числами. Ведь время включает часы, минуты и секунды? Вычислять разные единицы времени вручную было бы очень сложно… но СРЗНАЧ отлично с этим справляется.
Важные особенности СРЗНАЧ!
Как вы только что видели, применять СРЗНАЧ очень просто. Тем не менее, у нее есть несколько особенностей, о которых вам нужно знать.
Чем отличается функция СРЗНАЧА?
СРЗНАЧА аналогична СРЗНАЧ в том, что она вычисляет среднее арифметическое чисел. Разница в том, что СРЗНАЧА включает в расчет все непустые ячейки, независимо от того, содержат ли они числа, текст, логические ИСТИНА и ЛОЖЬ, или пустые строки, возвращаемые другими формулами.
Она обрабатывает различные типы данных, такие как числа, текстовые строки и логические. Что нужно запомнить:
Например, =СРЗНАЧА(2;ЛОЖЬ) дает нам 1, что является средним между 2 и 0.
=СРЗНАЧА(2;ИСТИНА) возвращает 1,5, что является средним от 2 и 1.
На следующем скриншоте показаны два варианта расчёта для среднего значения в Excel, и разные результаты, которые они возвращают:
Поэтому, если вы не хотите включать в свои вычисления логические выражения и текстовые строки, используйте СРЗНАЧ, а не СРЗНАЧА.
СРЗНАЧЕСЛИ вычисляет среднее арифметическое всех ячеек, которые соответствуют заданным критериям.
СРЗНАЧЕСЛИ (диапазон; критерии; [диапазон_усреднения])
Она имеет следующие аргументы (первые 2 обязательны, последний – нет):
А теперь давайте посмотрим, как вы можете применить СРЗНАЧЕСЛИ на реальных рабочих листах, чтобы найти среднее ячеек, соответствующих вашим требованиям.
Точное соответствие критериям
В этом примере давайте усредним только продажи (B2: B8) для заказов на бананы (A2: A8):
Вместо того, чтобы вводить условие непосредственно в формулу, вы можете ввести его в отдельную ячейку ($B$10) и затем обратиться к ней. Это очень известный и всеми применяемый приём.
Примечание. Чтобы округлить возвращаемое число до определенного количества десятичных знаков, примените одну из функций округления Excel, или диалоговое окно «Формат ячеек», чтобы изменить только отображение числа на экране.
Например, чтобы округлить полученный выше результат до 2 десятичных знаков, вы можете заключить его в функцию ОКРУГЛ следующим образом:
Частичное соответствие критериям (с символами подстановки).
Вы можете применить символы подстановки в аргументе критерия для выбора ячеек на основе частичного совпадения:
) перед этим символом (то есть,
Предположим, что у вас есть 3 разных вида бананов, и вы хотите найти среднюю величину их продаж:
Если вашему ключевому слову может предшествовать и/или последовать какой-либо другой символ, добавьте звездочку * как перед словом, так и после него.
Чтобы найти среднее по всем товарам, за исключением каких-либо бананов, запишите следующую формулу:
Как применить числовые условия и логические операторы.
Довольно часто вам может потребоваться усреднить ячейки, в которых количество больше или меньше определенного числа. Например, есть перечень чисел в столбце A, и мы хотим найти среднее из тех из них, которые больше 10.
Правильный подход — заключить логический оператор и число в двойные кавычки.
Итак, мы получили вот что:
Другая весьма распространенная проблема – как найти среднее чисел, которые не равны нулю. Для этого вам понадобится оператор «не равно» в аргументе критерия:
Как вы, возможно, заметили, мы не задействуем третий аргумент [диапазон усреднения] ни в одном из приведенных выше выражений, поскольку мы хотим считать по начальному диапазону.
Для пустых или непустых ячеек
При выполнении анализа данных в Excel часто может потребоваться найти среднее чисел, которые соответствуют либо пустым, либо непустым областям.
Среднее, если пусто.
Чтобы включить пустые ячейки, которые не содержат абсолютно ничего (без формулы, без строки нулевой длины), введите «=» в аргументе.
Например, вычислим среднее ячеек C2: C8, если клетка в столбце B в той же строке абсолютно пуста:
Чтобы усреднить значения, соответствующие визуально пустым ячейкам, включая те, которые содержат пустые строки, возвращаемые другими функциями (например, с формулой вроде = «»), запишите «». Например:
Среднее, если не пустое.
Чтобы найти среднее, соответствующее непустым ячейкам, введите «<>».
Например, так вычисляем среднеарифметическое C2:C8, если соответствующая позиция в столбце B не пуста:
Использование ссылок и других функций в описании условий.
Вместо того, чтобы записывать параметры подсчета непосредственно в формуле, вы можете обратиться к определенной ячейке, в которую можно вводить различные значения, не изменяя саму формулу. Так значительно уменьшается риск случайной ошибки при корректировке.
Если ссылка является критерием точного соответствия, то просто укажите ее в аргументе:
Если в качестве условия вы берёте логическое выражение со ссылкой или c другой функцией, нужно заключить логический оператор в «двойные кавычки» и добавить амперсанд (&) для их объединения.
Например, чтобы рассчитать средние продажи (C2: C8), превышающие указанное в E4:
С датами в B2: B8, приведенное ниже возвращает среднюю величину продаж (C2: C8), которые произошли до текущей даты:
Вместо прямого указания даты можно взять соответствующую функцию. К примеру,
СРЗНАЧЕСЛИМН учитывает сразу несколько условий и возвращает среднеарифметическое ячеек, которые соответствуют всем указанным критериям.
СРЗНАЧЕСЛИМН(диапазон подсчёта, диапазон_критерия_1, критерий_1, [диапазон_критерия_2, критерий_2],…)
Она имеет следующие аргументы:
Функция СРЗНАЧЕСЛИМН доступна в Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010 и 2007.
Как уже упоминалось, СРЗНАЧЕСЛИМН находит среднее ячеек, которые соответствуют всем указанным вами критериям (логика «И»). По сути, вы применяете его совершенно аналогично СРЗНАЧЕСЛИ, за исключением того, что вы можете указать в ней несколько областей проверки и соответствующих условий.
Пример 1. Среднеарифметическое количество ячеек по нескольким параметрам (текст и число)
Предположим, что у вас есть список товаров в столбце A и суммы продаж в столбце B. Давайте выясним среднее продаж бананов, превышающее 100 единиц.
Собираем вышеперечисленные компоненты вместе. И если вы замените условия ссылками, вы получите нечто похожее на это:
Как видите, только две ячейки (С3 и С5) удовлетворяют обоим условиям, и поэтому только они и берутся для расчета.
Пример 2. Среднеарифметическое на основе критерия даты
В этом примере давайте посчитаем среднее количество товаров, доставленных до 22 июля 2020 года, статус которых определен (позиция в соответствующем столбце не пустая). С количеством, указанным в столбце D, датами в колонке В (критерий_1) и статусом в С (критерий_2), формула выглядит следующим образом:
В критерии1 вы вводите дату, которой предшествует оператор сравнения. В критерии2 вы вводите «<>», который указывает включать только непустые ячейки в пределах столбца «Статус».
Как усреднить числа по нескольким параметрам с помощью логики ИЛИ?
Поскольку СРЗНАЧЕСЛИМН работает с логикой «И», а СРЗНАЧЕСЛИ допускает только 1 критерий, нам придется составить нашу собственную формулу для усреднения с помощью логики «ИЛИ».
Другими словами, мы составим выражение для вычисления среднего арифметического в Excel, если выполняется любое из указанных требований.
Пример 1. Среднее с логикой ИЛИ на основе нескольких текстовых критериев
Предположим, вы хотите получить средние продажи (C2: C8) как для бананов, так и для яблок (A2: A8). Чтобы рассчитать это, вам понадобится формула массива, включающая несколько функций Excel:
Если товары, которые мы ищем, записать отдельно (скажем, в E1 и E2), то наш расчет будет выглядеть так:
Из всех формул усреднения Excel, которые обсуждались до сих пор, это самая хитрая (хотя осталось еще два примера 😉
Как это работает?
Для наших любопытных и вдумчивых читателей, которые хотят не только механически применять рекомендации, но и понимать, что же они делают, даем подробное объяснение логики расчетов.
В начале функция ЕСЛИ определяет, какие значения в исходном диапазоне соответствуют любому из указанных параметров, и передает их в СРЗНАЧ. Вот как:
ПОИСКПОЗ берёт исходные данные в A2:A8 в качестве условий поиска и сравнивает каждое из них с массивом поиска, представленным критериями в E1: E2.
3- й аргумент, установленный в 0, указывает искать точные совпадения:
Когда совпадение найдено, программа возвращает относительную позицию в массиве поиска, в противном случае ошибку #Н/Д!.
ЕЧИСЛО преобразует любые числа в ИСТИНА, а ошибки в ЛОЖЬ:
Этот массив отправляется на логическую проверку ЕСЛИ.
Она заменяет позиции, соответствующие ИСТИНА в приведённом выше массиве, фактическими значениями из B2: B8:
Этот окончательный массив передается в СРЗНАЧ, которая вычисляет, игнорируя логические значения.
Пример 2. Среднее с логикой ИЛИ на основе числовых критериев с операторами сравнения
Если вы хотите найти среднее арифметическое на основе нескольких числовых критериев и условий больше/меньше, чем в сочетании с логикой ИЛИ, то формула, рассмотренная в предыдущем примере, не будет работать. Ведь вы не можете поместить эти логические выражения в массив. Решение заключается в применении функции СУММ в формуле массива.
Предположим, у вас есть количество в столбце B и сумма в столбце C, и вы хотите усреднить суммы, которые больше 50 в столбце B или C. При этом вы хотите избежать дубликатов, то есть не считать строку дважды, если число больше 50 в обоих столбцах.
Вот рабочий пример:
Помните, что это формула массива, и поэтому вы должны нажать Ctrl + Shift + Enter и ввести ее правильно.
Как видите, выражение состоит из 2 частей. В первой части вы применяете функцию ЕСЛИ с оператором ИЛИ в аргументе (C2:C8> 50) + (D2:D8> 50). Как вы, вероятно, знаете, в формулах массива плюс (+) действует как оператор ИЛИ.
Итак, первая часть складывает числа в столбце C, если выполняется любое из условий.
Вторая часть возвращает количество таких ячеек, а затем вы делите сумму на количество, чтобы найти среднеарифметическое.
И, естественно, вы можете указать разные условия для каждого диапазона. Например, чтобы усреднить продажи, если столбец C больше 50 или столбец D больше 100, используйте следующие выражения: (C2: C8> 50) + (D2: D8> 100). Всё целиком будет выглядеть следующим образом:
Пример 3. Среднее с логикой ИЛИ на основе пустых/непустых ячеек
Средняя формула с несколькими условиями ИЛИ, соответствующими пустым и непустым ячейкам, очень похожа на ту, которую мы только что обсуждали.
Для непустых ячеек.
Следующая формула массива находит среднее количество (столбец B), если указана либо дата (столбец B), либо статус (столбец C), т. е. если столбцы B или C не пустые.
Чтобы сделать её более компактной, вы можете объединить диапазоны с помощью амперсанда (&):
Для пустых ячеек.
Чтобы усреднить числа в столбце D, соответствующие пустым ячейкам в B или C, замените непустой оператор (<> «») пустым оператором (= «»).
Объединение диапазонов при помощи амперсанда не будет работать в этом случае.
Вот как рассчитывается среднее в Excel.
Я благодарю вас за чтение и надеюсь еще не раз увидеть вас в нашем блоге!