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

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

Чтобы найти среднее арифметическое, сложите все числа в наборе и разделите сумму на число. Например, оценки школьника по информатике: 3, 4, 3, 5, 5. А как же четверть: 4. Среднее арифметическое мы нашли по формуле: = (3+4+3+5+5)/ 5.

Как это сделать быстро с помощью функций Excel? Например, возьмем серию случайных чисел в строке:

1. Ставим курсор в ячейку А2 (под набором цифр). В главном меню — инструмент «Редактирование» — кнопка «Сумма». Выберите вариант «Среднее». После щелчка в активной ячейке появится формула. Выберите диапазон: A1:H1 и нажмите ENTER.
2. Второй метод основан на том же принципе нахождения среднего арифметического. Но мы будем вызывать функцию СРЗНАЧ по-другому. С помощью мастера функций (кнопка fx или комбинация клавиш SHIFT+F3).
3. Третий способ вызова функции СРЗНАЧ с панели: «Формула» — «Формула» — «Другие функции» — «Статические» — «СРЗНАЧ».

Или: сделайте ячейку активной и введите формулу вручную: =СРЗНАЧ(A1:A8).

Теперь посмотрим, что еще умеет функция СРЗНАЧ.

Найдите среднее арифметическое первых двух и последних трех чисел. Формула: =СРЗНАЧ(A1:B1;F1:H1). Результат:

Стандартный способ вычисления

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

После этого с помощью функции «СРЕДНИЙ» производится расчет. В ячейке под выбранным столбцом или справа от выбранной строки отображается среднее арифметическое заданного набора чисел.

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

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

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

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

Мы нажимаем на ячейку, где мы хотим, чтобы результат расчета среднего значения отображался. Нажмите кнопку «Вставить функцию», расположенную слева от строки формул. Либо набираем на клавиатуре комбинацию Shift+F3.

Запустится мастер функций. В списке представленных функций ищем «СРЕДНЯЯ». Выберите его и нажмите на кнопку «ОК».

Откроется окно аргументов для этой функции. Аргументы функции вводятся в поля «Число». Это могут быть как обычные номера, так и адреса ячеек, где эти номера находятся. Если вам неудобно вводить адреса ячеек вручную, следует нажать кнопку справа от поля ввода данных.

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

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

Затем нажмите на кнопку «ОК».

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

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

Есть и третий способ запуска функции «СРЕДНИЙ». Для этого перейдите на вкладку Формулы. Выберите ячейку, в которой должен отображаться результат. После этого в группе инструментов «Библиотека функций» на ленте нажмите кнопку «Другие функции». Появится список, в котором нужно пройти по порядку пункты «Статистические» и «СРЕДНИЕ».

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

Дальнейшие действия точно такие же.

Читайте также: Корреляционно-регрессионный анализ в Excel: инструкция выполнения

Использование арифметического выражения

Как известно, среднее значение равно сумме чисел, деленной на число. Эту формулу можно использовать и в Excel.

1. Встаем в нужную ячейку, ставим знак «равно» и пишем арифметическое выражение по следующему принципу:
   =(Число1+Число2+Число3…)/Число_терминов.
   Примечание. Число может быть либо конкретным числовым значением, либо ссылкой на ячейку. В нашем случае попробуем усреднить числа в ячейках B2, C2, D2 и E2.
   Окончательный вид формулы следующий: =(B2+E2+D2+E2)/4.
2. Когда все будет готово, нажмите Enter, чтобы получить результат.

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

Инструменты на ленте

Этот метод основан на использовании специального инструмента на ленте программы. Вот как это работает:

1. Выделяем диапазон ячеек с числовыми данными, для которых хотим определить среднее значение.
2. Переходим во вкладку «Главная» (если мы не в ней). На панели инструментов «Редактирование» найдите значок «Автосумма» и щелкните маленькую стрелку вниз рядом с ним. Нажмите «Среднее» в открывшемся списке”.
3. Сразу под выбранным диапазоном будет отображаться результат, который представляет собой среднее значение для всех выбранных ячеек.
4. Если мы перейдем к ячейке с результатом, то увидим в строке формул, какая функция использовалась программой для расчетов — это оператор СРЗНАЧ, аргументами которого являются выбранный нами диапазон ячеек.

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

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

Чтобы было понятнее, рассмотрим следующую ситуацию. Допустим, у нас есть две строки, заполненные данными. Мы хотим получить среднее значение по двум строкам одновременно, поэтому выделяем их и используем рассматриваемый инструмент.

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

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

Альтернативный способ использования «Среднего» на ленте:

1. Переходим в первую свободную ячейку после столбца или строки (в зависимости от структуры данных) и нажимаем кнопку расчета среднего значения.
2. Вместо того, чтобы сразу показать результат, на этот раз программа предложит нам сначала проверить диапазон ячеек, для которых необходимо вычислить среднее значение, и при необходимости подкорректировать координаты.
3. Когда вы будете готовы, нажмите клавишу Enter и получите результат в данной ячейке.

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

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

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

Среднее значение по условию

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

Найдите среднее арифметическое чисел, больших или равных 10.

Функция: =СРЗНАЧЕСЛИ(A1:A8,»>=10″)
Результат использования функции СРЗНАЧЕСЛИ при условии «>=10»:

Третий аргумент — «Диапазон усреднения» — опущен. Во-первых, это не обязательно. Во-вторых, анализируемый программой диапазон содержит ТОЛЬКО числовые значения. В ячейках, указанных в первом аргументе, поиск будет производиться по условию, указанному во втором аргументе.

Обратите внимание на следующее! Критерии поиска можно ввести в ячейку. И в формуле ссылаться на него.

Найдем среднее значение чисел по текстовому критерию. Например, средние продажи товара «столы».

Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Ассортимент – столбец с названиями товаров. Критерием поиска является ссылка на ячейку со словом «таблицы» (вместо ссылки А7 можно вставить слово «таблицы»). Средний диапазон — ячейки, из которых будут браться данные для расчета среднего значения.

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

Обратите внимание на следующее! Для текстового критерия (условия) необходимо указать средний диапазон.

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

Как рассчитать средний процент в Excel? Для этой цели подходят функции СУММПРОИЗВ и СУММ. Пример таблицы:

Как узнать средневзвешенную цену?

Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).

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

Среднее квадратическое отклонение: формула в Excel

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

Для расчета этого статистического показателя подготовлена ​​дисперсионная формула. Из него взят корень. Но в Excel есть готовая функция для нахождения стандартного отклонения.

Стандартное отклонение связано с масштабом исходных данных. Этого недостаточно для образного представления изменчивости анализируемой области. Для получения относительного уровня дисперсии данных рассчитывается коэффициент вариации:

стандартное отклонение / среднее арифметическое

Формула в Excel выглядит так:

СТАНДОТКЛОН (диапазон значений) / СРЗНАЧ (диапазон значений).

Коэффициент вариации рассчитывается в процентах. Поэтому задаем процентный формат в ячейке.

Расчет без игнорирования нулевых значений

Итого в Excel нужно посчитать среднее значение столбцов с учетом нулевых параметров. В этом случае вы должны использовать опции C AVERAGE и IF. В этом случае сам запрос может выглядеть так: =C СРЗНАЧЕСЛИ(A2:A7;»<>0″).

Как было сказано выше, рассчитать среднее значение в Excel можно по-разному — через формулы, путем ручного ввода или с помощью мастера. Однако в некоторых случаях может потребоваться использование дополнительных функций.

 

Использование функции СРЗНАЧ – на примерах.

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

Пример 1. Расчет среднего из нескольких чисел.

Вы можете указать номера напрямую. Например,

=СРЗНАЧ(1;2;3;4) возвращает результат 2.5.

Чтобы вычислить среднее значение столбца, обратитесь ко всему столбцу:
=СРЕДНЕЕ(А:А)

Чтобы получить построчно, введите ссылку на него:
=СРЕДНЕЕ(1:1)

Для расчета в любом диапазоне укажите его:
=СРЕДНЕЕ(A1:C20)

Чтобы получить среднее значение несмежных ячеек, введите каждую ячейку отдельно, например
=СРЗНАЧ(A1;C1;D1)

И, конечно же, ничто не мешает вам включать числа, ссылки и диапазоны в одну формулу. Например, следующее выражение вычисляет среднее значение двух диапазонов и одного отдельного значения:

=СРЗНАЧ(B3:B5; C7:D9; B11)

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

= КРУГЛЫЙ(СРЕДНИЙ(B3:B5; B7:B9; B11); 0)

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

Пример 2. Расчет среднего процента.

Если в вашей электронной таблице есть процентный столбец, как вы получите средний процент завершения? Возьмем обычную формулу Excel для среднего 🙂

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

Пример 3. Расчет среднего времени.

Это совсем не так просто, как с обычными числами. Ведь время включает в себя часы, минуты и секунды, верно? Вычисление различных единиц времени вручную было бы очень трудным.. но AVERAGE отлично справляется с этой задачей.

Важные особенности СРЗНАЧ!

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

• В расчет включаются нулевые значения (0.
• Текстовые строки, логические значения TRUE и FALSE, а также пустые ячейки игнорируются. Если вы хотите включить в расчет логическое и текстовое представление чисел, используйте СРЗНАЧ.
• Булевы значения, которые вы вводите непосредственно в выражение, учитываются. Например, =СРЗНАЧ(ИСТИНА; ЛОЖЬ) возвращает 0,5, что является средним значением 1 и 0.

Примечание. При использовании СРЗНАЧ в электронных таблицах Excel помните о разнице между ячейками, содержащими нулевые значения, и теми, которые на самом деле пусты. Нули учитываются, пробелы нет. Это может быть особенно важно, если на этом листе не включен параметр «Показывать ноль в ячейках, содержащих нулевые значения». То есть ячейки с нулями будут внешне пустыми. Вы можете найти эту опцию в Excel: «Параметры» > «Дополнительно» > «Параметры отображения листа .

Чем отличается функция СРЗНАЧА?

СРЗНАЧ похож на СРЗНАЧ тем, что вычисляет среднее арифметическое чисел. Разница в том, что СРЗНАЧ включает в расчет все непустые ячейки, независимо от того, содержат ли они числа, текст, логические ИСТИНА и ЛОЖЬ или пустые строки, возвращаемые другими формулами.

СРЗНАЧ(значение1; значение2</a>;…)

value1, 2,.. — это числа, массивы, ссылки на ячейки или диапазоны, которые вы хотите усреднить. Первый аргумент обязателен, остальные (до 255) необязательны.

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

• Пустые ячейки таблицы игнорируются.
• Текст, включая пустые строки («»), возвращаемые другими формулами, оценивается как 0.
• Логическое значение TRUE оценивается как 1, а FALSE оценивается как 0.

Например, =СРЗНАЧ(2, ЛОЖЬ) дает нам 1, что является средним значением между 2 и 0.

=СРЗНАЧ(2, ИСТИНА) возвращает 1,5, что является средним значением 2 и 1.

На следующем снимке экрана показаны два варианта расчета среднего значения в Excel и разные результаты, которые они возвращают:

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

Среднее по условию — функция СРЗНАЧЕСЛИ

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

СРЗНАЧЕСЛИ(диапазон; критерии; среднийдиапазон)

Он имеет следующие аргументы (первые два обязательны, последний необязателен):

• Диапазон — диапазон ячеек, которые будут проверяться по заданным параметрам.
• Критерии — условие, используемое для определения того, какие ячейки следует срезать. Они могут быть представлены в виде числа, логического выражения, текста или строки, например 5, > 5, «кошка» или A2.
• Средний диапазон — то, что вы хотите усреднить (это необязательно). Если он опущен, функция будет учитывать диапазон условия.

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

Точное соответствие критериям

Классическое применение СРЗНАЧЕСЛИ — найти среднее арифметическое для ячеек, точно соответствующих заданному условию.

В этом примере давайте просто рассчитаем средние продажи (B2:B8) для заказов на бананы (A2:A8):

=СРЗНАЧЕСЛИ(A2:A8, B10, B2:B8)

Вместо того, чтобы вводить условие непосредственно в формулу, вы можете ввести его в отдельную ячейку ($B$10) и затем обратиться к нему. Это очень известный и широко используемый метод.

Примечание. Чтобы округлить возвращенное число до определенного числа знаков после запятой, используйте одну из функций округления Excel или используйте диалоговое окно «Формат ячеек», чтобы изменить только отображение числа на экране.

Например, чтобы округлить приведенный выше результат до 2 знаков после запятой, вы можете обернуть его функцией ОКРУГЛ следующим образом:

=ОКРУГЛ(СРЗНАЧ.ЕСЛИ(A2:A8;»бананы», B2:B8),2)

Либо выберите ячейку с формулой (в данном примере E1), нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». Затем перейдите на вкладку «Числовой» или «Валюта» и выберите количество знаков после запятой, которое вы хотите отобразить. Помните, что в этом случае фактическое сохраненное значение не изменится, и во всех расчетах будет использоваться точное неокругленное число, когда вы ссылаетесь на него в других формулах.

Частичное соответствие критериям (с символами подстановки).

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

• Вставьте знак вопроса (?), чтобы заменить один символ.
• Вставьте звездочку (*), чтобы заменить ею последовательность символов.
• Чтобы найти настоящий вопросительный знак или звездочку, введите тильду (~) перед символом (то есть ~? или ~*).

Предположим, у вас есть 3 разных вида бананов, и вы хотите найти среднее значение их продаж:

=СРЗНАЧ.ЕСЛИ(A2:A8, «*бананы», B2:B8)

Если перед и/или после слова поиска может стоять другой символ, добавьте звездочку * до и после слова.

=СРЗНАЧ.ЕСЛИ(A2:A8, «*бананы*», B2:B8)

Чтобы найти среднее значение всех предметов, кроме бананов, напишите следующую формулу:

=СРЗНАЧ.ЕСЛИ(A2:A8, «<>*бананы*», B2:B8)

Как применить числовые условия и логические операторы.

Довольно часто вам может понадобиться усреднить ячейки, число которых больше или меньше определенного числа. Например, в столбце А есть список чисел, и мы хотим найти среднее число чисел, превышающих 10.

Правильный подход заключается в заключении логического оператора и числа в двойные кавычки.

Итак, мы получили это:

=СРЗНАЧЕСЛИ(A2:A7;»>10″)

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

=СРЗНАЧ.ЕСЛИ(A2:A7,»<>0″)

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

Для пустых или непустых ячеек

При анализе данных в Excel часто требуется найти среднее значение чисел, соответствующих либо пустым, либо непустым областям.

Среднее, если пусто.

Чтобы включить пустые ячейки, которые абсолютно ничего не содержат (ни формулы, ни строки нулевой длины), введите «=» в качестве аргумента.

Например, давайте посчитаем среднее значение ячеек C2:C8, если ячейка в столбце B в той же строке полностью пуста:

=СРЗНАЧЕСЛИ(B2:B8, «=», C2:C8)

Для усреднения значений, соответствующих визуально пустым ячейкам, в том числе содержащим пустые строки, возвращаемые другими функциями (например, с формулой вида = «»), введите «». Например:

=СРЗНАЧЕСЛИ(B2:B8, «», C2:C8)

Среднее, если не пустое.

Чтобы найти среднее значение, соответствующее непустым ячейкам, введите «<>».

Например, вот как мы вычисляем среднее арифметическое C2:C8, если соответствующая позиция в столбце B не пуста:

=СРЗНАЧЕСЛИ(B2:B8, «<>», C2:C8)

Использование ссылок и других функций в описании условий.

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

Если ссылка является критерием точного соответствия, просто укажите его в аргументе:

=СРЗНАЧЕСЛИ(B2:B8,E1, C2:C8)

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

Например, чтобы рассчитать средние продажи (C2:C8) больше, чем E4:

=СРЗНАЧЕСЛИ(C2:C8, «>»&B13)

С датами в B2:B8 следующее возвращает среднее значение продаж (C2:C8), которые произошли до текущей даты:

=СРЗНАЧ.ЕСЛИ(B2:B8; «<=»&»2020-07-22»; C2:C8)

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

=СРЗНАЧЕСЛИ(B2:B8, «<=»&СЕГОДНЯ(), C2:C8)

Среднее по нескольким условиям — функция СРЗНАЧЕСЛИМН

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

СРЗНАЧЕСЛИМН(диапазон подсчета; диапазон критериев_1; критерий_1; диапазон критериев_2; критерий_2,…)

Он имеет следующие аргументы:

• Count_range — диапазон ячеек, который вы хотите усреднить.
• критерии_диапазона_1, 2 ,… — от 1 до 127 диапазонов для проверки соответствия заданным параметрам. Первый обязателен, остальные необязательны.
• Критерии_1, 2,… — от 1 до 127 критериев, определяющих, какие ячейки следует усреднить. Они могут быть в виде числа, логического выражения, текста или ссылки. Первый необходим, дополнительные — при необходимости.

Функция СРЗНАЧЕСЛИМН доступна в Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010 и 2007.

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

Пример 1. Среднеарифметическое количество ячеек по нескольким параметрам (текст и число)

Допустим, у вас есть список товаров в столбце A и итоговые данные о продажах в столбце B. Давайте найдем, что средний объем продаж бананов превышает 100 единиц.

Здесь:

• С2:С8 — ячейки, которые нужно усреднить, если выполняются оба условия;
• Первое условие: A2:A8 (столбец «Товар»), а критерий_1 — «бананы»;
• Второе условие — С2:С8 (столбец «Продажи»), а критерий_2 — «> 100».

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

=СРЗНАЧЕСЛИ(C2:C8; A2:A8;C10; C2:C8; «>»&C11)

Как видите, только две ячейки (С3 и С5) удовлетворяют обоим условиям, поэтому для расчета берутся только они.

Пример 2. Среднеарифметическое на основе критерия даты

В данном примере посчитаем среднее количество доставленных до 22 июля 2020 г товаров, статус которых определен (позиция в соответствующем столбце не пуста). С количеством в столбце D, датами в столбце B (критерий_1) и статусом в C (критерий_2) формула выглядит следующим образом:

=СРЗНАЧЕСЛИ(D2:D8, B2:B8, «<=22-07-2020», C2:C8; «<>»)

В критерии1 вы вводите дату с оператором сравнения. В критерии2 введите «<>», что указывает на включение в столбец «Статус» только непустых ячеек».

Примечание. При использовании числа или даты в сочетании с логическим оператором в условии необходимо заключить эту комбинацию в кавычки — «<=22.07.2020”.

Что вспомнить!

СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН имеют много общего, в частности:

• При подсчете пустые области, логические значения ИСТИНА/ЛОЖЬ и текст игнорируются.
• Когда указанные условия оцениваются, пустые ячейки обрабатываются как ноль (0).
• Если усредняемый диапазон содержит только пустые ячейки или текст, обе функции возвращают ошибку #DIV/0!.
• Если ничего не соответствует требованиям, мы также получаем ошибку #DIV/0!.

ХАРАКТЕРИСТИКИ

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

=СРЗНАЧЕСЛИ(B2:B10, «<>», C2:C8)

тогда мы все равно найдем среднее значение по C2:C8.

• В отличие от AVERAGEIF, AVERAGEIFS требует, чтобы площадь каждого критерия была того же размера, что и подсчитываемая площадь. В противном случае мы получим ошибку #ЗНАЧ!.

Как усреднить числа по нескольким параметрам с помощью логики ИЛИ?

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

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

Пример 1. Среднее с логикой ИЛИ на основе нескольких текстовых критериев

Предположим, вы хотите получить средние продажи (C2:C8) как для бананов, так и для яблок (A2:A8). Чтобы вычислить это, вам нужна формула массива, которая включает несколько функций Excel:

{=СРЗНАЧ(ЕСЛИ(ЧИСЛО(СООТВЕТСТВИЕ(A2:A8,{«бананы»,»яблоки»},0)),B2:B8))}

Помните, что формулы массива нужно вводить с помощью Ctrl + Shift + Enter, а не только ENTER.

Если элементы, которые мы ищем, записаны отдельно (скажем, в E1 и E2), наш расчет будет выглядеть так:

{=СРЗНАЧ(ЕСЛИ(ЧИСЛО(СООТВЕТСТВИЕ(A2:A8,E1:E2,0)),B2:B8))}

Из всех рассмотренных до сих пор формул средних значений Excel эта самая сложная (хотя осталось еще два примера 😉

Как это работает?

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

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

ПОИСКПОЗ берет исходные данные в A2:A8 в качестве условий поиска и сравнивает каждый из них с матрицей поиска, представленной критериями в E1:E2.

третий аргумент, установленный в 0, указывает на проверку точных совпадений:

ПОИСКПОЗ(A2:A8;E1:E2;0)

Когда совпадение найдено, программа возвращает относительную позицию в массиве поиска, в противном случае — ошибка #Н/Д!.

{#СЕЙЧАС!; 1; 2; 1; 2; #СЕЙЧАС!; 1}

ISNUMBER преобразует все числа в TRUE, а ошибки в FALSE:

{ЛОЖЬ; ПОДЛИННЫЙ; ПОДЛИННЫЙ; ПОДЛИННЫЙ; ПОДЛИННЫЙ; ЛОЖЬ; ПОДЛИННЫЙ}

Этот массив передается на логический тест IF.

Он заменяет позиции, соответствующие TRUE в приведенном выше массиве, фактическими значениями из B2:B8:

{ЛОЖЬ; 80; 130; 120; 70; ЛОЖЬ; 90}

Этот последний массив передается в AVERAGE, который оценивает игнорирование логических значений.

Пример 2. Среднее с логикой ИЛИ на основе числовых критериев с операторами сравнения

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

Предположим, у вас есть количество в столбце B и сумма в столбце C, и вы хотите усреднить суммы, превышающие 50 в столбце B или C. Делая это, вы избежите дублирования, т.е не подсчитывая строку дважды, если число больше чем 50 в обоих столбцах.

Вот рабочий пример:

{=СУММ(ЕСЛИ((C2:C8+D2:D8>50),D2:D8,0))/СУММ(—((C2:C8+D2:D8)>50))}

Помните, что это формула массива и поэтому вы должны нажать Ctrl+Shift+Enter и ввести ее правильно.

Как видите, выражение состоит из 2 частей. В первой части вы используете функцию ЕСЛИ с оператором ИЛИ в аргументе (C2:C8> 50) + (D2:D8> 50). Как вы, наверное, знаете, в формулах массива плюс (+) действует как оператор ИЛИ.

Таким образом, первая часть суммирует числа в столбце C, если выполняется какое-либо из условий.

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

И, конечно же, вы можете указать разные условия для каждой области. Например, если столбец C больше 50 или столбец D больше 100, используйте следующее выражение: (C2: C8 > 50) + (D2: D8 > 100). Все это будет выглядеть так:

=СУММ(ЕСЛИ((C2:C8>50)+(D2:D8>100),D2:D8,0))/СУММ(—((C2:C8>50)+(D2:D8>100)))

Пример 3. Среднее с логикой ИЛИ на основе пустых/непустых ячеек

Средняя формула с несколькими условиями ИЛИ, соответствующими пустым и непустым ячейкам, очень похожа на ту, которую мы только что обсуждали.

Для непустых ячеек.

Следующая формула массива находит среднее значение (столбец B), если указана либо дата (столбец B), либо статус (столбец C), т е если столбцы B или C не пусты.

Чтобы сделать его более компактным, вы можете объединять диапазоны с помощью амперсанда (&):

{=СУММ(ЕСЛИ((B2:B8&C2:C8)<>»»;D2:D8;0))/СУММ(—((B2:B8&C2:C8)<>»»))}

Слишком пустые ячейки.

Чтобы усреднить числа в столбце D, соответствующие пустым ячейкам в B или C, замените непустой оператор (<> «») на пустой оператор (= «»).

Объединение диапазонов амперсандом в этом случае не работает.

Это тоже формула массива, поэтому не забудьте нажать Ctrl+Shift+Enter.

{=СУММ(ЕСЛИ(—((B2:B8=»») + (C2:C8=»»))>0;D2:D8;0)) / СУММ(—(((B2:B8=» «) + (C2:C8=»»))>0))}

Как посчитать среднее в Excel.