Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
В этой статье описаны синтаксис формулы и использование С AVERAGEIF
в Microsoft Excel.
Описание
Возвращает среднее значение (среднее арифметическое) всех ячеек в диапазоне, которые соответствуют данному условию.
Синтаксис
СРЗНАЧЕСЛИ(диапазон, условия, [диапазон_усреднения])
Аргументы функции СРЗНАЧЕСЛИ указаны ниже.
-
Диапазон. Обязательный. Одна или несколько ячеек для вычисления среднего, включающих числа или имена, массивы или ссылки, содержащие числа.
-
Условие. Обязательный. Условие в форме числа, выражения, ссылки на ячейку или текста, которое определяет ячейки, используемые при вычислении среднего. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.
-
Диапазон_усреднения. Необязательный. Фактическое множество ячеек для вычисления среднего. Если этот параметр не указан, используется диапазон.
Замечания
-
Ячейки в диапазоне, которые содержат значения ИСТИНА или ЛОЖЬ, игнорируются.
-
Если ячейка в «диапазоне_усреднения» пустая, функция СРЗНАЧЕСЛИ игнорирует ее.
-
Если диапазон является пустым или текстовым значением, то #DIV0! значение ошибки #ЗНАЧ!.
-
Если ячейка в условии пустая, «СРЗНАЧЕСЛИ» обрабатывает ее как ячейки со значением 0.
-
Если ни одна из ячеек в диапазоне не соответствует условиям, то #DIV/0! значение ошибки #ДЕЛ/0!.
-
В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, то перед ними следует поставить знак тильды (~).
-
Значение «диапазон_усреднения» не обязательно должно совпадать по размеру и форме с диапазоном. При определении фактических ячеек, для которых вычисляется среднее, в качестве начальной используется верхняя левая ячейка в «диапазоне_усреднения», а затем добавляются ячейки с совпадающим размером и формой. Например:
|
Если диапазон равен |
И «диапазон_усреднения» |
Обрабатываемые ячейки |
|
A1:A5 |
B1:B5 |
B1:B5 |
|
A1:A5 |
B1:B3 |
B1:B5 |
|
A1:B4 |
C1:D4 |
C1:D4 |
|
A1:B4 |
C1:C2 |
C1:D4 |
Примечание: Функция СРЗНАЧЕСЛИ измеряет среднее значение, то есть центр набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения: :
-
Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 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, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Стоимость имущества |
Комиссионные |
|
|
100000 |
7000 |
|
|
200000 |
14000 |
|
|
300000 |
21000 |
|
|
400000 |
28000 |
|
|
Формула |
Описание |
Результат |
|
=СРЗНАЧЕСЛИ(B2:B5;»<23000″) |
Среднее значение всех комиссионных меньше 23 000. Этому условию удовлетворяют три из четырех значений, сумма которых составляет 42 000. |
14000 |
|
=СРЗНАЧЕСЛИ(A2:A5;»<250000″) |
Среднее среди всех значений стоимости имущества меньше 250 000. Этому условию удовлетворяет два из четырех значений, их сумма составляет 300 000. |
150000 |
|
=СРЗНАЧЕСЛИ(A2:A5;»<95000″) |
Среднее среди всех значений стоимости имущества меньше 95 000. Так как количество значений стоимости имущества, удовлетворяющих этому условию, равно 0, функция СРЗНАЧЕСЛИ вернет ошибку #ДЕЛ/0! из-за попытки деления на 0. |
#ДЕЛ/0! |
|
=СРЗНАЧЕСЛИ(A2:A5;»>250000″;B2:B5) |
Среднее значение всех комиссионных для стоимости имущества более 250 000. Этому условию удовлетворяют два значения, сумма которых составляет 49 000. |
24500 |
Пример 2
|
Регион |
Доходы (в тысячах) |
|
|
Восточный |
45678 |
|
|
Западный |
23789 |
|
|
Северный |
-4789 |
|
|
Южная (новое представительство) |
0 |
|
|
Средний Запад |
9678 |
|
|
Формула |
Описание |
Результат |
|
=СРЗНАЧЕСЛИ(A2:A6;»=*Западная»;B2:B6) |
Среднее значение всех доходов для Западной и Средне-Западной областей. |
16733,5 |
|
=СРЗНАЧЕСЛИ(A2:A6;»<>*(Новое представительство)»;B2:B6) |
Среднее значение всех доходов для всех областей, за исключением новых представительств. |
18589 |
Нужна дополнительная помощь?
# DIV / 0! это ошибка деления в Excel, и причина, по которой она возникает, потому что, когда мы делим любое число на ноль, мы получаем эту ошибку, поэтому это причина, по которой ошибка отображается как # DIV / 0 !.
Например, если вы хотите найти процентную оценку учащегося, вам нужно иметь две вещи: во-первых, какие были общие оценки за письменный экзамен и какова была достигнутая оценка на экзамене, поэтому с помощью этих двух чисел мы можем найти процент оценки.
- Но посмотрите на сценарий ниже.
На изображении выше Джон набрал 85 баллов, но у нас нет общего балла за экзамен, и нам нужно рассчитать процент ученика Джона.
- Чтобы получить процент, нам нужно разделить достигнутый результат по оценка экзамена, поэтому примените формулу как B2 / C2.
- У нас есть ошибка деления как # DIV / 0!.
Поскольку у нас нет никакого значения в ячейке C2, мы получили эту ошибку, потому что если вы разделите любое число на пустую ячейку или ячейку с нулевым значением, мы получим эту ошибку.
Сценарии получения «# DIV / 0!» в Excel
Ниже приведены примеры ошибки # Div / 0 в Excel.
Вы можете скачать этот шаблон Excel с ошибкой Div0 здесь — Шаблон Excel с ошибкой Div0
Как мы видели выше, # DIV / 0! Ошибка возникает, когда мы делим число на пустую ячейку или нулевое значение в excel, но есть много других ситуаций, когда мы получим эту ошибку, давайте рассмотрим их подробно сейчас.
# 1 — Деление на ноль
- Посмотрите на изображение формулы ниже.
Как видно выше, у нас есть два # DIV / 0! Значения ошибок в ячейках D2 и D5, потому что в ячейке C2 у нас нет значения, поэтому ячейка становится пустой, а ячейка C5 имеет нулевое значение, поэтому приводит к # DIV / 0! Ошибка.
# 2 — Суммирование ячеек
- Посмотрите на изображение ниже.
Ячейки B7 применили формулу SUM excel, взяв диапазон ячеек от B2 до B6.
Но у нас # DIV / 0! Ошибка. Эта ошибка возникает из-за того, что в диапазоне ячеек от B2 до B6 имеется как минимум одна ошибка деления # DIV / 0! В ячейке B4, поэтому конечный результат функции СУММ такой же.
# 3 — Функция AVERAGE
- Посмотрите на изображение формулы ниже.
В ячейке B7 мы применили функцию СРЕДНЕЕ, чтобы найти средний балл учащихся.
Но результат # DIV / 0! Ошибка. Когда мы пытаемся найти среднее значение для пустых или пустых ячеек, мы получаем эту ошибку.
- Теперь посмотрим на пример ниже.
В этом сценарии тоже # DIV / 0! Ошибка, потому что в диапазоне формулы от B2 до B6 нет ни одного числового значения, поскольку все значения не являются числовыми; мы получили эту ошибку деления.
- Аналогичный набор ошибок деления возникает и с функцией СРЗНАЧЕСЛИ в Excel. Например, посмотрите на пример ниже.
В первой таблице у нас есть городская температура за две последовательные даты. Во второй таблице мы пытаемся найти среднюю температуру для каждого города и применили функцию СРЗНАЧЕСЛИ.
Но для города «Сурат» у нас # DIV / 0! Ошибка, потому что в исходной таблице нет названия города «Сурат», поэтому, когда мы пытаемся найти среднее значение для города, которого нет в реальной таблице, мы получаем эту ошибку.
Как исправить ошибку «# DIV / 0!» Ошибка в Excel?
Теперь мы знаем, в каких сценариях мы получаем эту ошибку, теперь давайте посмотрим, как справиться с этой ошибкой в Excel.
# 1 — Используйте функцию ЕСЛИОШИБКА
ЕСЛИОШИБКА в excel — это функция, специально используемая для устранения любой ошибки вместо получения # DIV / 0! Ошибка, можем получить альтернативный результат.
- Посмотрите на изображение ниже.
В приведенной выше функции мы использовали ЕСЛИОШИБКА, чтобы получить альтернативный результат «Знаменатель пуст или ноль» всякий раз, когда мы получаем ошибку # DIV / 0 !.
# 2 — Используйте функцию ЕСЛИ
Мы также можем использовать функцию ЕСЛИ в Excel, чтобы проверить, пуста ли ячейка знаменателя или равна нулю.
- Посмотрите на сценарий ниже.
Вышеупомянутая формула говорит, что если ячейка знаменателя равна нулю, вернуть результат как «0» или вернуть результат деления. Таким образом, мы можем решить проблему # DIV / 0! Ошибки.
Что нужно помнить здесь
- Функция AVERAGE возвращает ошибку # DIV / 0! Когда диапазон ячеек, передаваемых в функцию СРЕДНЕЕ, не имеет даже единичных числовых значений.
- Функция СУММ возвращает # ДЕЛ / 0! Ошибка, если в диапазоне, передаваемом в функцию SUM, есть хотя бы один # DIV / 0! Ценность в этом.
УЗНАТЬ БОЛЬШЕ >>
Post Views: 1 085
В математике деление на ноль – невозможно! Одним из способов для объяснения данного правила является анализ процесса, который показывает, что происходит, когда одно число разделено на другое.
Ошибка деления на ноль в Excel
В реальности операция деление это по сути тоже что и вычитание. Например, деление числа 10 на 2 является многократным вычитанием 2 от 10-ти. Многократность повторяется до той поры пока результат не будет равен 0. Таким образом необходимо число 2 вычитать от десяти ровно 5 раз:
- 10-2=8
- 8-2=6
- 6-2=4
- 4-2=2
- 2-2=0
Если же попробовать разделить число 10 на 0, никогда мы не получим результат равен 0, так как при вычитании 10-0 всегда будет 10. Бесконечное количество раз вычитаний ноля от десяти не приведет нас к результату =0. Всегда будет один и ото же результат после операции вычитания =10:
- 10-0=10
- 10-0=10
- 10-0=10
- ∞ бесконечность.
В кулуарах математиков говорят, что результат деления любого числа на ноль является «не ограниченным». Любая компьютерная программа, при попытке деления на 0, просто возвращает ошибку. В Excel данная ошибка отображается значением в ячейке #ДЕЛ/0!.
Но при необходимости можно обойти возникновения ошибки деления на 0 в Excel. Просто следует пропустить операцию деления если в знаменателе находится число 0. Решение реализовывается с помощью помещения операндов в аргументы функции =ЕСЛИ():
Таким образом формула Excel позволяет нам «делить» число на 0 без ошибок. При делении любого числа на 0 формула будет возвращать значение 0. То есть получим такой результат после деления: 10/0=0.
Как работает формула для устранения ошибки деления на ноль
Для работы корректной функция ЕСЛИ требует заполнить 3 ее аргумента:
- Логическое условие.
- Действия или значения, которые будут выполнены если в результате логическое условие возвращает значение ИСТИНА.
- Действия или значения, которые будут выполнены, когда логическое условие возвращает значение ЛОЖЬ.
В данном случаи аргумент с условием содержит проверку значений. Являются ли равным 0 значения ячеек в столбце «Продажи». Первый аргумент функции ЕСЛИ всегда должен иметь операторы сравнения между двумя значениями, чтобы получить результат условия в качестве значений ИСТИНА или ЛОЖЬ. В большинстве случаев используется в качестве оператора сравнения знак равенства, но могут быть использованы и другие например, больше> или меньше >. Или их комбинации – больше или равно >=, не равно !=.
Если условие в первом аргументе возвращает значение ИСТИНА, тогда формула заполнит ячейку значением со второго аргумента функции ЕСЛИ. В данном примере второй аргумент содержит число 0 в качестве значения. Значит ячейка в столбце «Выполнение» просто будет заполнена числом 0 если в ячейке напротив из столбца «Продажи» будет 0 продаж.
Если условие в первом аргументе возвращает значение ЛОЖЬ, тогда используется значение из третьего аргумента функции ЕСЛИ. В данном случаи — это значение формируется после действия деления показателя из столбца «Продажи» на показатель из столбца «План».
Таким образом данную формулу следует читать так: «Если значение в ячейке B2 равно 0, тогда формула возвращает значение 0. В противные случаи формула должна возвратить результат после операции деления значений в ячейках B2/C2».
Формула для деления на ноль или ноль на число
Усложним нашу формулу функцией =ИЛИ(). Добавим еще одного торгового агента с нулевым показателем в продажах. Теперь формулу следует изменить на:
Скопируйте эту формулу во все ячейки столбца «Выполнение»:
Теперь независимо где будет ноль в знаменателе или в числителе формула будет работать так как нужно пользователю.
Читайте также: Как убрать ошибки в Excel
Данная функция позволяет нам расширить возможности первого аргумента с условием во функции ЕСЛИ. Таким образом в ячейке с формулой D5 первый аргумент функции ЕСЛИ теперь следует читать так: «Если значения в ячейках B5 или C5 равно ноль, тогда условие возвращает логическое значение ИСТИНА». Ну а дальше как прочитать остальную часть формулы описано выше.
Привет, друзья. Ошибки в Экселе часто пугают новичков, ведь их исправление не всегда понятно и очевидно. Сегодня поговорим об ошибке, #ДЕЛ/0!, которая иногда появляется там, где её совсем не ждёшь.
Эта ошибка возникает, когда ваша формула пытается произвести деление на ноль. Да, в высшей математике такая операция вернёт «бесконечность», а в большинстве языков программирования – Infinity. Но в Экселе это вызовет ошибку, которая «поломает» все связанные расчёты. Давайте рассмотрим основные причины описанного.
Формула ссылается на пустую ячейку, или нулевое значение
Это простейший случай, который очень легко отследить.
В примере на картинке мы видим, что делитель – пустое значение (или ноль). Это могло случиться из-за того, что:
- Эта ячейка еще не заполнена, но это будет сделано позднее
- В ячейке правильное значение, равное нулю
Первое, что нужно сделать – проверить, все ли данные правильно внесены в таблицу, ввести или исправить некорректные величины.
Если все данные корректны, ошибку можно обойти с помощью формул, но об этом ниже в статье.
Формула среднего значения без подходящих аргументов
Когда вы пользуетесь функциями расчёта среднего значения СРЗНАЧ, СРЗНАЧЕСЛИ, СРЗНАЧЕСЛИМН, программа суммирует элементы и делит сумму на их количество. При том, если подходящих элементов для суммирования в диапазоне нет, их сумма и количество будет равны нулю, функция вернет #ДЕЛ/0!
В примере выше я попытался посчитать средний показатель Сидорова, но такого человека нет в списке, функция вернула злосчастную ошибку.
Чтобы это исправить, нужно либо проверить список работников на ошибки и добавить Сидорова, либо применять формулы обхода.
Формула ссылается на ячейку, в которой содержится ошибка
Если ваша формула ссылается на ячейку, в которой ошибка #ДЕЛ/0!, она тоже вернёт эту ошибку. Смотрите, на примере:
В ячейке F12 – функция суммирования, там вообще нет никакого деления, но есть ошибка. Внутри диапазона суммирования, в ячейке F5, программа не смогла вычислить среднее значение, что и вызвало «цепное» распространение ошибки. В этом случае, нужно проверить внутренние формулы на предмет корректности, исправить и повторить расчёт.
Обход деления на ноль с помощью функции ЕСЛИ
Сейчас расскажу, как с помощью функций устранить ошибки и сохранить корректность расчёта. Но перед их применением следует убедиться, что ошибки нельзя исправить другими способами.
Применяя функцию ЕСЛИ, можно проверить значение делителя. Вот так:
=ЕСЛИ(делитель=0; 0; делимое/делитель)
Формула проконтролирует значение делителя. Если он нулевой – вернёт ноль. Если нет – отношение делимого к делителю:
Видим, в примере формула отследила возможную ошибку и вывела вместо нее ноль.
Перехват с помощью функции ЕСЛИОШИБКА
Вы можете использовать функцию ЕСЛИШИБКА для «отлова» любых ошибок, не только этой. Достаточно обернуть вашу формулу этой функцией:
Если результат выражения – ошибка, функция вернет «значение_если_ошибка». В противном случае, результат вычисления выражения. Вот так:
Всегда с осторожностью используйте формулы и функции отлова ошибок. Они не разбираются, корректны ли исходные данные. Возможно, в таблице опечатка, а вы её не исправите, т.к. не увидите сообщения об ошибке!
Неплохим решением для минимизации неправильного ввода данных будет применение выпадающего списка.
А у меня на этом всё. Если у вас что-то не получается по теме статьи – пишите комментарии с вопросами!
Исправление ошибки #DIV/0! #BUSY!
Ошибка #ДЕЛ/0! возникает в Microsoft Excel, когда число делится на ноль (0). Это происходит, если вы вводите простую формулу, например =5/0, или если формула ссылается на ячейку с 0 или пустую ячейку, как показано на этом рисунке.
Есть несколько способов исправления этой ошибки.
Убедитесь в том, что делитель в функции или формуле не равен нулю и соответствующая ячейка не пуста.
Измените ссылку в формуле, чтобы она указывала на ячейку, не содержащую ноль (0) или пустое значение.
Введите в ячейку, которая используется в формуле в качестве делителя, значение #Н/Д, чтобы изменить результат формулы на #Н/Д, указывая на недоступность делителя.
Во многих случаях это #DIV/0! их можно избежать, поскольку формулы ожидают ввода от вас или другого пользователя. В этом случае сообщение об ошибке отображаться не нужно, поэтому для ее подавления можно использовать несколько способов обработки ошибок.
Оценка знаменателя на наличие нуля или пустого значения
Самый простой способ избавиться от ошибки #ДЕЛ/0! — воспользоваться функцией ЕСЛИ для оценки знаменателя. Если он равен нулю или пустой, в результате вычислений можно отображать значение «0» или не выводить ничего. В противном случае будет вычислен результат формулы.
Например, если ошибка возникает в формуле =A2/A3, можно заменить ее формулой =ЕСЛИ(A3;A2/A3;0), чтобы возвращать 0, или формулой =ЕСЛИ(A3;A2/A3;»»), чтобы возвращать пустую строку. Также можно выводить произвольное сообщение. Пример: =ЕСЛИ(A3;A2/A3;»Ожидается значение.»). Он сообщает Excel ЕСЛИ(существует, то вернуть результат формулы, в противном случае игнорировать ее).
Чтобы подавления этой #DIV/0, используйте #DIV. #BUSY!
Вы также можете скрыть эту ошибку, вложенную операцию деления в функцию ЕСЛИERROR. При использовании A2/A3 можно также использовать =ЕСЛИERROR(A2/A3;0). Он сообщает Excel, если формула возвращает ошибку, возвращается 0, в противном случае возвращается результат формулы.
В версиях до Excel 2007 можно использовать синтаксис ЕСЛИ(ЕОШИБКА()): =ЕСЛИ(ЕОШИБКА(A2/A3);0;A2/A3) (см. статью Функции Е).
Примечание. Как методы ЕСЛИERROR, так и ЕСЛИ(ЕERROR()) — это подавляющие обработчики всех ошибок, а не только #DIV/0!. Прежде чем применять обработку ошибок, убедитесь, что формула работает правильно. В противном случае вы можете не понимать, что формула работает неправильно.
Совет: Если в Microsoft Excel включена проверка ошибок, нажмите кнопку рядом с ячейкой, в которой показана ошибка. Выберите пункт Показать этапы вычисления, если он отобразится, а затем выберите подходящее решение.
У вас есть вопрос об определенной функции?
Помогите нам улучшить Excel
У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.
|
Mirdv Пользователь Сообщений: 179 |
Добрый день, |
|
ZVI  Пользователь Сообщений: 4325 |
Сделайте так: |
|
Атлика Пользователь Сообщений: 37 |
Считает неверно, т.к. Excel воспринимает только одно условие, ср, а сб не учитывает. Можно попробовать СРЗНАЧЕСЛИМН. |
|
Mirdv Пользователь Сообщений: 179 |
Владимир, спасибо. Атлика, с этой функцией я тоже пытался играться, но нужного мне результата не достиг. Буду разбираться |
|
Михаил С. Пользователь Сообщений: 10514 |
#5 28.05.2013 18:22:07
при разных количеств сред и суббот формула даст неверный результат. Здесь нужно как-то так
Атлика, в данном случае СРЗНАЧЕСЛИМН вернет ноль (не может один день быть и средой и субботой, а все функции с *ЕСЛИМН работают именно так) |
||||
|
MCH Пользователь Сообщений: 3872 |
#6 28.05.2013 21:07:10 Формула массива
Изменено: MCH — 28.05.2013 21:09:56 |
||||
|
Igor67  Пользователь Сообщений: 3704 |
MCH, Миша а нет в диспетчере формул такой функции ПРАВБ, которая работает аналогично ПРАВСИМВ(). Где ее нашли? |
|
MCH Пользователь Сообщений: 3872 |
#8 28.05.2013 21:30:41
Игорь, ну поправь на ПРАВСИМВ, я использовал ее для сокращения формулы
Зайди в справку по ПРАВСИМВ() |
||||
|
Igor67 Пользователь Сообщений: 3704 |
MCH, Спасибо, так глубоко не смотрел |
|
Nic70y Пользователь Сообщений: 3289 Win7 MSO 2013 |
{=СРЗНАЧ(ЕСЛИ(ПСТР(C3:N3;4;1)=»с»;ЕСЛИ(C5:N5<>»»;C5:N5)))} |
|
ZVI Пользователь Сообщений: 4325 |
Михаил, спасибо, Вы правы. Вообще-то, для среднего значения принято также игнорировать пустые ячейки, что учтено только в формуле Nic70y, которую можно немного упростить: =СРЗНАЧЕСЛИ(C3:N3;»*с*»;C5:N5) Для общего случая мне нравится решение МСН, хотя оно также учитывает пустые ячейки, Изменено: ZVI — 29.05.2013 15:58:07 |
|
Mirdv Пользователь Сообщений: 179 |
#12 29.05.2013 14:00:04
Почему эта формула выдает результат 0, на всякий случай у меня Excel 2010? Изменено: Mirdv — 29.05.2013 22:57:09 |
||
|
Mirdv, а не забыли, что это формула массива? |
|
|
Mirdv Пользователь Сообщений: 179 |
Сразу вводил как формулу массива — выдает ошибку деление на ноль (( |
|
ZVI Пользователь Сообщений: 4325 |
Там лишняя точка с запятой затесалась (иногда появляется после редактирования сообщения — глюк форума), нужно так: Изменено: ZVI — 29.05.2013 15:56:27 |
|
Mirdv Пользователь Сообщений: 179 |
Владимир, спасибо |
|
MEP Пользователь Сообщений: 248 |
Коллеги, почему то некорректно работает формула СРЗНАЧЕСЛИМН… помогите разобраться. считаю вручную получается одно, задаешь фомулой — другое. пример во вложении Прикрепленные файлы
|
|
Z  Пользователь Сообщений: 6111 Win 10, MSO 2013 SP1 |
Однако верно считает — и пром итоги, и формула… Но есть вариантик (на него, наверное, расчитывали)- не учитывать «0», то тады да — 161,37. Но насколько это верно… «Ctrl+S» — достойное завершение ваших гениальных мыслей!.. 😉 |
|
MEP Пользователь Сообщений: 248 |
, да как раз таки 0 надо учесть в предыдущем столбике (одно из условий). вот голову уже сломал |
|
MEP Пользователь Сообщений: 248 |
разобрался в Вашем примере. большое спасибо. мне решение подошло. |
|
MEP Пользователь Сообщений: 248 |
я только немного не понял вконце «<>»&0 в чем отличие от «<>0» ? |
|
Nic70y Пользователь Сообщений: 3289 Win7 MSO 2013 |
#22 10.08.2013 15:00:40 =СРЗНАЧЕСЛИМН(C2:C117;B2:B117;0;A2:A117;»Дни работы»;C2:C117;«<>0») это не принципиально |
|
5 / 5 / 0 Регистрация: 15.06.2015 Сообщений: 342 |
|
|
1 |
|
|
06.09.2015, 16:27. Показов 12128. Ответов 7
Добрый день!
__________________
0 |
|
5905 / 3127 / 688 Регистрация: 23.11.2010 Сообщений: 10,450 |
|
|
06.09.2015, 16:44 |
2 |
|
Вариант, если правильно понимаю Код =ЕСЛИОШИБКА(СРЗНАЧ(S24:$S$52);-99999)
1 |
|
2637 / 1694 / 694 Регистрация: 04.09.2015 Сообщений: 3,367 |
|
|
06.09.2015, 16:51 |
3 |
|
Код =СУММ(A24:$A$52)/МАКС(1;СЧЁТЗ(A24:$A$52))
1 |
|
5 / 5 / 0 Регистрация: 15.06.2015 Сообщений: 342 |
|
|
06.09.2015, 16:53 [ТС] |
4 |
|
Fairuza, не не работает. как было так и осталось дел/0 в самом низу 3 ячейки.(столбец U) Добавлено через 1 минуту
0 |
|
5905 / 3127 / 688 Регистрация: 23.11.2010 Сообщений: 10,450 |
|
|
06.09.2015, 16:57 |
5 |
|
РешениеСтранно, у меня нет ошибки
1 |
Сообщение было отмечено Oleg395 как решение
|
5 / 5 / 0 Регистрация: 15.06.2015 Сообщений: 342 |
|
|
06.09.2015, 17:00 [ТС] |
6 |
|
Fairuza, прошу прощения. просто столбец узкий был. и поэтому отражались ####. Спасибо ваш вариант рабочий. Помогли очень!
0 |
|
2637 / 1694 / 694 Регистрация: 04.09.2015 Сообщений: 3,367 |
|
|
06.09.2015, 17:07 |
7 |
|
Решение «хотел избавиться от дел/0, заменив на -9999» Я подумал вы так искали способ избавится от ошибки дел/0!
1 |
|
5 / 5 / 0 Регистрация: 15.06.2015 Сообщений: 342 |
|
|
06.09.2015, 17:20 [ТС] |
8 |
|
AlexM, Благодарю и вас!
0 |
При работе в Excel вы можете столкнуться с ошибкой #ДЕЛ/0, которая значит что вы пытаетесь поделить число на 0, а как вы знаете, делить на 0 нельзя. Исправить эту ошибку в Экселе можно несколькими способами.
Способ 1. При помощи функции ЕСЛИ
При помощи функции ЕСЛИ() мы сможем обработать ситуацию, при которой числитель равен 0. Синтаксис функции следующий ЕСЛИ(условие; результат при верном значении; результат при неверном значении). В примере выше мы должны проверить, является ли значение в ячейке С4 равно 0, если равно 0, то присваиваем значение «-«, а если нет — то вычисляем значение от деления. Итого формула выглядит так: =ЕСЛИ(C4=0;»-«;C3/C4)
Способ 2. При помощи функции ЕСЛИОШИБКА
Функция ЕСЛИОШИБКА возвращает заданное значение, если в ячейке ошибка, иначе вычисляет заданную формулу. Синтаксис функции следующий ЕСЛИОШИБКА(Формула; Значение если ошибка). В нашем случае будет так: =ЕСЛИОШИБКА(C3/C4;»-«)
Спасибо, что прочитали статью. Теперь вы знаете, как исправить ошибку #ДЕЛ/0 в Excel. Есть вопросы — добро пожаловать в комментарии 😉
В учебнике показано, как использовать функцию СРЗНАЧЕСЛИ в Excel для вычисления среднего арифметического с условием.
Microsoft Excel имеет несколько различных функций для вычисления среднего арифметического числа. Когда вы хотите усреднить ячейки, соответствующие определенному условию, используйте функцию СРЗНАЧЕСЛИ.
Функция СРЗНАЧЕСЛИ используется для вычисления среднего значения всех ячеек в заданном диапазоне, которые соответствуют определенному условию.
СРЗНАЧЕСЛИ(диапазон, критерии, [average_range])
Всего у функции 3 аргумента — первые 2 обязательны, последний опционален:
- Диапазон (обязательно) — диапазон ячеек для проверки на соответствие критериям.
- Критерии (обязательно) — условие, определяющее, какие ячейки усреднять. Он может быть представлен в виде числа, логического выражения, текстового значения или ссылки на ячейку, например, 5, «>5», «cat» или A2.
- Average_range (необязательно) — ячейки, которые вы действительно хотите усреднить. Если опущено, то диапазон будет усреднен.
Функция СРЗНАЧЕСЛИ доступна в Excel 365 — 2007.
Excel СРЗНАЧЕСЛИ — о чем нужно помнить!
Чтобы эффективно использовать функцию СРЗНАЧЕСЛИ на листах, обратите внимание на следующие ключевые моменты:
- При расчете среднего значения пустые ячейки, текстовые значения и логические значения ИСТИНА и ЛОЖЬ игнорируются.
- Нулевые значения включены в среднее значение.
- Если ячейка критерия пуста, она рассматривается как нулевое значение (0).
- Если средний_диапазон содержит только пустые ячейки или текстовые значения, #DIV/0! возникает ошибка.
- Если ни одна ячейка в диапазоне не соответствует критериям, #DIV/0! возвращается ошибка.
- Аргумент Average_range не обязательно должен иметь тот же размер, что и диапазон. Однако фактические ячейки, подлежащие усреднению, определяются размером аргумента диапазона. Другими словами, верхняя левая ячейка в диапазоне_усреднения становится начальной точкой, и усредняется столько столбцов и строк, сколько содержится в аргументе диапазона.
формула СРЗНАЧЕСЛИ, основанная на другой ячейке
С помощью функции Excel СРЗНАЧЕСЛИ вы можете усреднить столбец чисел на основе:
- критерии, применяемые к одному и тому же столбцу
- критерии, применяемые к другому столбцу
В случае, если условие применяется к тому же столбцу, который должен быть усреднен, вы определяете только первые два аргумента: диапазон и критерии. Например, чтобы найти среднее значение продаж в B3:B15, превышающее 120 долларов, используется формула:
=СРЗНАЧЕСЛИ(B3:B15, «>120»)
Чтобы усреднить на основе другой ячейки, вы определяете все 3 аргумента: диапазон (ячейки для проверки на соответствие условию), критерии (условие) и средний_диапазон (ячейки для расчета).
Например, чтобы получить среднее значение продаж, выполненных после 1 октября, используйте следующую формулу:
=СРЗНАЧЕСЛИ(C3:C15, «>10.01.2022», B3:B15)
Где C3:C15 — ячейки для проверки на соответствие критериям, а B3:B15 — ячейки для усреднения.
Как использовать функцию СРЗНАЧЕСЛИ в Excel — примеры
А теперь давайте посмотрим, как вы можете использовать Excel СРЗНАЧЕСЛИ в реальных рабочих листах, чтобы найти среднее значение ячеек, соответствующих вашим критериям.
СРЗНАЧЕСЛИ текстовые критерии
Чтобы найти среднее числовых значений в данном столбце, если другой столбец содержит определенный текст, вы создаете формулу СРЗНАЧЕСЛИ с текстовыми критериями. Когда текстовое значение включается непосредственно в формулу, оно должно быть заключено в двойные кавычки («»).
Например, чтобы усреднить числа в столбце B, если столбец A содержит слово «Apple», используется следующая формула:
=СРЗНАЧЕСЛИ(A3:A15, «яблоко», B3:B15)
В качестве альтернативы вы можете ввести целевой текст в какую-либо ячейку, скажем, F3, и использовать ссылку на эту ячейку для критериев. В этом случае двойные кавычки не нужны.
=СРЗНАЧЕСЛИ(A3:A15, F3, B3:B15)
Преимущество этого подхода заключается в том, что он позволяет усреднить продажи для любого другого товара, просто изменив текстовые критерии в F3, без необходимости вносить какие-либо коррективы в формулу.
Кончик. Чтобы округлить среднее значение до определенного числа знаков после запятой, используйте команду «Увеличить десятичное число» или «Уменьшить десятичное число» на вкладке «Главная» в группе «Число». Это изменит представление среднего значения на дисплее, но не само значение. Чтобы округлить фактическое значение, возвращаемое формулой, используйте СРЗНАЧЕСЛИ вместе с ОКРУГЛ или другими функциями округления. Для получения дополнительной информации см. Как округлить среднее значение в Excel.
логический критерий СРЗНАЧЕСЛИ для числовых значений
Чтобы проверить различные числовые значения в критериях, используйте их вместе с операторами «больше» (>), «меньше» (<), «равно» (=), «не равно» (<>) и другими логическими операторами.
При включении логического оператора с числом не забудьте заключить всю конструкцию в двойные кавычки. Например, чтобы усреднить числа, которые меньше или равны 120, формула будет выглядеть так:
=СРЗНАЧЕСЛИ(B3:B15, «<=120″)
Обратите внимание, что оператор и номер заключены в кавычки.
При использовании критерия «равно» знак равенства (=) можно опустить.
Например, для усреднения продаж, осуществленных 9 сентября 2022 года, формула выглядит следующим образом:
=СРЗНАЧЕСЛИ(C3:C15, «9/9/2022», B3:B15)
Использование AVERAGEIF с датами
Как и в случае с числами, вы можете использовать даты в качестве критериев для функции СРЗНАЧЕСЛИ. Критерии даты могут быть построены несколькими различными способами.
Давайте посмотрим, как вы можете усреднить продажи до определенной даты, скажем, до 1 ноября 2022 года.
Самый простой способ — заключить логический оператор и дату в двойные кавычки:
=СРЗНАЧЕСЛИ(C3:C15, «<1/11/2022″, B3:B15)
Или вы можете заключить оператора и дату в кавычки отдельно и соединить их с помощью знака &:
=СРЗНАЧЕСЛИ(C3:C15, «<«&»1/11/2022», B3:B15)
Чтобы убедиться, что дата введена в формате, понятном Excel, вы можете использовать функцию ДАТА, объединенную с логическим оператором:
=СРЗНАЧЕСЛИ(C3:C15, «<«&ДАТА(2022, 11, 1), B3:B15)
Чтобы усреднить продажи на сегодняшнюю дату, используйте функцию СЕГОДНЯ в критериях:
=СРЗНАЧЕСЛИ(C3:C15, «<«&СЕГОДНЯ(), B3:B15)
На скриншоте ниже показаны результаты:
СРЗНАЧесли больше 0
Функция СРЗНАЧ в Excel пропускает пустые ячейки, но включает в расчеты нулевые значения. Чтобы использовать только средние значения больше нуля, используйте «>0» для критериев.
Например, чтобы вычислить среднее значение чисел в ячейках B3:B15, которые больше нуля, в ячейке E4 используется следующая формула:
=СРЗНАЧЕСЛИ(B3:B15, «>0»)
Обратите внимание, как результат отличается от обычного среднего в E3:
Среднее, если не 0
Приведенное выше решение хорошо работает для набора положительных чисел. Если у вас есть как положительные, так и отрицательные значения, вы можете усреднить все числа, кроме нулей, используя «<> 0» для критериев.
Например, чтобы усреднить все значения в B3:B15, кроме нулей, используйте следующую формулу:
=СРЗНАЧЕСЛИ(B3:B15; «<>0″)
Среднее значение Excel, если оно не равно нулю или пусто
Поскольку функция СРЗНАЧЕСЛИ пропускает пустые ячейки, вы можете просто использовать критерий «не ноль» («<>0»). В результате как нулевые значения, так и пустые ячейки будут игнорироваться. Чтобы убедиться в этом, в нашем тестовом наборе данных мы заменили пару нулевых значений на пробелы, и получили абсолютно такой же результат, как и в предыдущем примере:
=СРЗНАЧЕСЛИ(B3:B15; «<>0″)
Среднее значение, если другая ячейка пуста
Чтобы усреднить ячейки в данном столбце, если ячейка в другом столбце в той же строке пуста, используйте «=» для критериев. Это будут пустые ячейки, которые абсолютно ничего не содержат — ни пробела, ни строки нулевой длины, ни непечатаемых символов и т. д.
Чтобы усреднить значения, соответствующие визуально пустым ячейкам, включая те, которые содержат пустые строки («»), возвращаемые другими функциями, используйте «» для критериев.
В целях тестирования мы будем использовать оба критерия для усреднения чисел в B3:B15, у которых нет даты доставки в C3:C15 (т. е. если ячейка в столбце C пуста).
=СРЗНАЧЕСЛИ(C3:C15, «=», B3:B15)
=СРЗНАЧЕСЛИ(C3:C15, «», B3:B15)
Поскольку одна из визуально пустых ячеек (C12) на самом деле не пуста — в ней есть строка нулевой длины — формулы дают разные результаты:
Среднее значение, если другая ячейка не пуста
Чтобы усреднить диапазон ячеек, если ячейка в другом диапазоне не пуста, используйте «<>» для критериев.
Например, следующая формула СРЗНАЧЕСЛИ вычисляет среднее значение для ячеек с B3 по B15, если ячейка в столбце C в той же строке не пуста:
=СРЗНАЧЕСЛИ(C3:C15, «<>«, B3:B15)
Подстановочный знак AVERAGEIF (частичное совпадение)
Чтобы усреднить ячейки на основе частичного совпадения, используйте подстановочные знаки в критериях формулы СРЗНАЧЕСЛИ:
- Знак вопроса (?) для соответствия любому одиночному символу.
- Звездочка
для соответствия любой последовательности символов.
Предположим, у вас есть 3 разных сорта бананов, и вы хотите найти их среднее значение. Следующая формула сделает это возможным:
=СРЗНАЧЕСЛИ(A3:A15, «*банан», B3:B15)
При необходимости вместе со ссылкой на ячейку можно использовать подстановочный знак. Предполагая, что целевой элемент находится в ячейке В4, формула принимает следующий вид:
Формула подстановочного знака СРЗНАЧЕСЛИ
Если ключевое слово может появиться в любом месте ячейки (в начале, в середине или в конце), поставьте звездочку с обеих сторон:
=СРЗНАЧЕСЛИ(A3:A15, «*банан*», B3:B15)
Чтобы найти среднее значение всех предметов, исключая любой банан, используйте эту формулу:
=СРЗНАЧЕСЛИ(A3:A15, «<>*банан*», B3:B15)
Как рассчитать среднее значение в Excel, исключая определенные ячейки
Чтобы исключить определенные ячейки из среднего, используйте логический оператор «не равно» (<>).
Например, чтобы усреднить количество продаж для всех товаров, кроме «яблока», используйте следующую формулу:
=СРЗНАЧЕСЛИ(A3:A15, «<>яблоко», B3:B15)
Если исключенный элемент находится в предопределенной ячейке (D4), формула принимает следующий вид:
=СРЗНАЧЕСЛИ(A3:A15, «<>«&D4, B3:B15)
Чтобы найти среднее значение всех элементов, исключая любой «банан», используйте «не равно» вместе с подстановочным знаком:
=СРЗНАЧЕСЛИ(A3:A15, «<>*банан», B3:B15)
Если исключенный подстановочный элемент находится в отдельной ячейке (D9), соедините логический оператор, подстановочный знак и ссылку на ячейку с помощью амперсанда:
«&»*»&D9, B3:B15)» title=»Рассчитать среднее значение в Excel, за исключением определенных ячеек.»/>
Рассчитать среднее значение в Excel, за исключением определенных ячеек.
Как использовать СРЗНАЧЕСЛИ со ссылкой на ячейку
Вместо того, чтобы вводить критерии непосредственно в формулу, вы можете использовать логический оператор в сочетании со ссылкой на ячейку для построения критериев. Таким образом, вы сможете протестировать различные условия, изменив значение в ячейке критериев, не редактируя формулу СРЗНАЧЕСЛИ.
Когда условие по умолчанию равно «равно», вы просто используете ссылку на ячейку в качестве аргумента критерия. Приведенная ниже формула вычисляет среднее значение всех продаж в диапазоне B3:B15, относящихся к элементу в ячейке F4.
=СРЗНАЧЕСЛИ(A3:A15, F4, B3:B15)
Когда критерий включает логический оператор, вы строите его следующим образом: заключаете логический оператор в кавычки и используете амперсанд (&), чтобы соединить его со ссылкой на ячейку.
Например, чтобы найти среднее значение продаж в B3:B15, превышающее значение в F9, используйте следующую формулу:
=СРЗНАЧЕСЛИ(B3:B15, «>»&F9)
Аналогичным образом вы можете использовать логическое выражение с другой функцией в критериях.
С датами в C3:C15 приведенная ниже формула возвращает среднее значение продаж, которые были доставлены до текущей даты включительно:
СРЗНАЧЕСЛИ со ссылкой на ячейку
Вот как вы используете функцию СРЗНАЧЕСЛИ в Excel для вычисления среднего арифметического с условиями. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!