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 |
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
|
Функция срзнач без учета ошибок #н/д и дел/0 |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
В этом учебном материале по Excel мы рассмотрим примеры того как игнорируя ошибки усреднить значение.
Общая формула
|
=СРЗНАЧЕСЛИ(Значения;«>=0») |
Описание
Чтобы усреднить список значений, игнорируя любые ошибки, которые могут существовать в диапазоне, вы можете использовать функцию СРЗНАЧЕСЛИ или АГРЕГАТ. В показанном примере формула E5 имеет следующий вид:
|
=СРЗНАЧЕСЛИ(Значения;«>=0») |
где Значения — именованный диапазон B5:B12.
Пояснение
Функция СРЗНАЧЕСЛИ может вычислять среднее числовых данных с одним или несколькими условиями. В данном случае условием является выражение >=0. Это отфильтровывает значения ошибок, а СРЗНАЧЕСЛИ возвращает среднее значение остальных восьми значений, и равно 47,6.
Альтернатива с АГРЕГАТ
Функция АГРЕГАТ также может игнорировать ошибки при вычислении среднего значения. Чтобы вычислить среднее значение с помощью функции АГРЕГАТ, вы можете использовать следующую формулу:
Здесь цифра 1 обозначает среднее значение, а цифра 6 — возможность игнорировать ошибки. Как и СРЗНАЧЕСЛИ выше, АГРЕГАТ возвращает среднее из остальных восьми значений — 47,6.
В учебнике показано, как использовать функцию СРЗНАЧЕСЛИ в 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 для вычисления среднего арифметического с условиями. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
|
Mirdv Пользователь Сообщений: 179 |
Добрый день, |
|
ZVI  Пользователь Сообщений: 4338 |
Сделайте так: |
|
Атлика Пользователь Сообщений: 37 |
Считает неверно, т.к. Excel воспринимает только одно условие, ср, а сб не учитывает. Можно попробовать СРЗНАЧЕСЛИМН. |
|
Mirdv Пользователь Сообщений: 179 |
Владимир, спасибо. Атлика, с этой функцией я тоже пытался играться, но нужного мне результата не достиг. Буду разбираться |
|
Михаил С. Пользователь Сообщений: 10514 |
#5 28.05.2013 18:22:07
при разных количеств сред и суббот формула даст неверный результат. Здесь нужно как-то так
Атлика, в данном случае СРЗНАЧЕСЛИМН вернет ноль (не может один день быть и средой и субботой, а все функции с *ЕСЛИМН работают именно так) |
||||
|
MCH Пользователь Сообщений: 3888 |
#6 28.05.2013 21:07:10 Формула массива
Изменено: MCH — 28.05.2013 21:09:56 |
||||
|
Igor67  Пользователь Сообщений: 3733 |
MCH, Миша а нет в диспетчере формул такой функции ПРАВБ, которая работает аналогично ПРАВСИМВ(). Где ее нашли? |
|
MCH Пользователь Сообщений: 3888 |
#8 28.05.2013 21:30:41
Игорь, ну поправь на ПРАВСИМВ, я использовал ее для сокращения формулы
Зайди в справку по ПРАВСИМВ() |
||||
|
Igor67 Пользователь Сообщений: 3733 |
MCH, Спасибо, так глубоко не смотрел |
|
Nic70y Пользователь Сообщений: 3289 Win7 MSO 2013 |
{=СРЗНАЧ(ЕСЛИ(ПСТР(C3:N3;4;1)=»с»;ЕСЛИ(C5:N5<>»»;C5:N5)))} |
|
ZVI Пользователь Сообщений: 4338 |
Михаил, спасибо, Вы правы. Вообще-то, для среднего значения принято также игнорировать пустые ячейки, что учтено только в формуле 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 Пользователь Сообщений: 4338 |
Там лишняя точка с запятой затесалась (иногда появляется после редактирования сообщения — глюк форума), нужно так: Изменено: ZVI — 29.05.2013 15:56:27 |
|
Mirdv Пользователь Сообщений: 179 |
Владимир, спасибо |
|
MEP Пользователь Сообщений: 253 |
Коллеги, почему то некорректно работает формула СРЗНАЧЕСЛИМН… помогите разобраться. считаю вручную получается одно, задаешь фомулой — другое. пример во вложении Прикрепленные файлы
|
|
Z  Пользователь Сообщений: 6111 Win 10, MSO 2013 SP1 |
Однако верно считает — и пром итоги, и формула… Но есть вариантик (на него, наверное, расчитывали)- не учитывать «0», то тады да — 161,37. Но насколько это верно… «Ctrl+S» — достойное завершение ваших гениальных мыслей!.. |
|
MEP Пользователь Сообщений: 253 |
, да как раз таки 0 надо учесть в предыдущем столбике (одно из условий). вот голову уже сломал |
|
MEP Пользователь Сообщений: 253 |
разобрался в Вашем примере. большое спасибо. мне решение подошло. |
|
MEP Пользователь Сообщений: 253 |
я только немного не понял вконце «<>»&0 в чем отличие от «<>0» ? |
|
Nic70y Пользователь Сообщений: 3289 Win7 MSO 2013 |
#22 10.08.2013 15:00:40 =СРЗНАЧЕСЛИМН(C2:C117;B2:B117;0;A2:A117;»Дни работы»;C2:C117;«<>0») это не принципиально |
Когда вы применяете функцию СРЕДНЕЕ для вычисления среднего значения диапазона ячеек, включающего некоторые значения ошибок, вы получите результат ошибки. В следующей статье будет рассказано о том, как усреднить ячейки, игнорируя ошибки в Excel.
Средние ячейки, игнорирующие значения ошибок с помощью формул массива
Средние ячейки, игнорирующие значения ошибок с помощью формул массива
Средние ячейки, игнорирующие значения ошибок с помощью формул массиваСледующие удобные формулы массива могут помочь вам вычислить среднее значение ячеек без учета ошибок. Пожалуйста, сделайте так:
1. Введите эту формулу массива: = СРЕДНИЙ (ЕСЛИ (ЕСТЬ ОШИБКА (A1: C6); «»; A1: C6)), см. снимок экрана:
2, Затем нажмите Shift + Ctrl + Enter одновременно, и вы получите средний результат, как показано на скриншоте ниже:
Ноты:
1. Кроме приведенной выше формулы, вам может помочь еще одна формула: = СРЕДНИЙ (ЕСЛИ (ЕЧИСЛО (A1: C6); A1: C6)), пожалуйста, не забудьте нажать Shift + Ctrl + Enter ключи.
2. В приведенных выше формулах A1: C6 — это диапазон данных, который вы хотите вычислить, вы можете изменить его по своему усмотрению.
Статьи по теме:
Как усреднить абсолютные значения в Excel?
Как усреднить только положительные или отрицательные числа в Excel?
Как усреднить диапазон данных, игнорируя ноль в Excel?
Как рассчитать среднее значение без максимальных и минимальных значений в Excel?
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (13)
Оценок пока нет. Оцените первым!
Функция «ЕСЛИОШИБКА» в Excel.
Смотрите также можно было бы пример — ошибка в ячейке В1 Выйдет такая ошибка.Например, у нас Исправляем это слово в сложить все ячейки,решетка ошибка. Выберите пунктвведите необходимое количество1000 сообщества, посвященном Excel СРЗНАЧ в сочетании
логические функции в от функции «ЕОШИБКА» по столбцу. ВЕсть две похожие все исправить так: деления на ноль стоит не нуль,Исправить эту ошибку была таблица с ячейке на число п. э. выдает).Показать этапы вычисления пересчетов в Excel..
У вас есть предложения с функциями ЕСЛИExcel тем, что выполняет ячейках С9:С14 Excelфункции вВсе красиво и ошибок при вычислении среднего: то разделить ячейку
можно, заменив в формулами. В процессе или удаляем это ошибку.Эта ошибка показывает,, если он отобразится, Чем больше предельноеВ Excel ошибка #ЧИСЛО! по улучшению следующей и ЕОШИБКА для. Читайте статью «Функция сразу две функции показывает ошибку., п.ч.Excel «ЕСЛИОШИБКА» и «ЕОШИБКА» больше нет.Причем заметьте, что итоги А1 на ячейку ячейке В1 нуль работы, мы удалили слово, т. д.Кстати!
что столбец недостаточно а затем выберите число итераций, тем также может возникать, версии Excel? Если определения наличия ошибок «ЕСЛИ» в Excel».Эта (не надо писать ячейки столбцов А-Обратите внимание, что эта в нашей таблице В1. на число. не нужный, теперь, Формула посчитает всеМожет быть и широкий и всё
подходящее решение. больше времени потребуется если: да, ознакомьтесь с в указанном диапазоне. функция поможет посчитать
две функции –
и В не
это функция появилась только тоже уже неПолучилось так.Но, лучший вариант, столбец. Но формула правильно. число написано в число не входитЗадать вопрос на форуме
для вычислений.в формуле используется функция, темами на портале В данном конкретном ячейки с определенными «ЕСЛИ» и «ЕОШИБКА»). заполнены.логические функции в
с 2007 версии считаются — однаВ Excel можно чтобы не отслеживать с ячейками этого
Ошибка «# ИМЯ?» в текстовом формате – в ячейку. сообщества, посвященном ExcelДля задания максимально допустимой выполняющая итерацию, например пользовательских предложений для
excel-office.ru
Исправление ошибки #ЗНАЧ! в функции СРЗНАЧ или СУММ
случае требуется формула данными. Формулу писать проще.Чтобы убрать этуExcel
Microsoft Excel. В ошибка начинает порождать сравнивать значения ячеек. пустые ячейки или столбца осталась вExcel
это, тоже, вызоветИсправить эту ошибкуУ вас есть предложения величины разности между ВСД или СТАВКА, Excel. массива:Пример функции «ЕСЛИ» в В ячейку D7 надпись ошибки, в
. Эти функции не
более ранних версиях другие, передаваясь по Используя функции сравнивания, ячейки с нулем, другом столбце.. ошибку. В Excel можно, увеличив ширину по улучшению следующей результатами вычислений введите которая не может
В Excel эта ошибка=СРЗНАЧ(ЕСЛИ(ЕОШИБКА(B2:D2);»»;B2:D2)) Excel пишем такую формулу. ячейку С7 напишем дают в таблице приходилось использовать функции цепочке от одной
можно начислить премию это установить формулу
Мы видим, что в
У вас есть вопрос об определенной функции?
В Excel oшибка есть два формата
Помогите нам улучшить Excel
столбца. Какими способами версии Excel? Если ее в поле найти результат. возникает тогда, когдаПримечание:смотрите в статье
support.office.com
Исправление ошибки #ЧИСЛО!
=ЕСЛИОШИБКА(A7/B7;»») Формула намного такую формулу. =ЕСЛИ(ЕОШИБКА(A7/B7);»»;A7/B7) написать словаЕОШ (ISERROR) зависимой формулы к
сотрудникам, расчитать другие с условием, с формуле нашего примера «#ИМЯ?» возникает, когда – формат значения увеличить размер столбца, да, ознакомьтесь сОтносительная погрешностьЧтобы исправить ошибку, измените формула или функция Это формула массива, которую «Как сделать тест короче. Копируем формулу Этой формулой мы#и другой. Так что данные, т.д. Подробнее функцией «ЕСЛИ», например. складываются три ячейки, в формуле неверно
и формат ячейки. установить автоподбор ширины темами на портале
-
. Чем меньше число, число итераций формулы содержит недопустимое числовое нужно вводить с в Excel» тут.
по столбцу D, говорим Excel –ССЫЛКА! или
-
ЕНД (ISNA) из-за одной ошибочной об этом, читайтеВ Excel логических а в таблице написано название функции, Эти форматы влияют
столбца, т. д., пользовательских предложений для тем точнее результат -
в Excel. значение. помощью клавиш CTRL+SHIFT+ВВОД.Как найти в получилось так. если при делении#
-
. Эти функции похожи ячейки, в конце в статье «Функция функций «ЕСЛИ» много всего две ячейки, диапазона, ячейки, т. на работу формул. смотрите в статье
-
Excel. и тем большеНа вкладкеЗачастую это происходит, если Excel автоматически заключит таблице данные поМожно ячейки А7 наДЕЛ/О!, если ошибка. на
-
-
концов, может перестать сравнения в Excel». разны, с разными п. ч. мы
е. какого-то имени. Чтобы разобраться в «Как изменить ширинуЧасто, вместо результата времени потребуется ExcelФайл
вы вводите числовое формулу в фигурные двум параметрам изфункцию «ЕСЛИОШИБКА» в
ячейку В7 будетНапример, мы написалиЕСЛИОШИБКА работать весь расчет.Тот, кто никогда не условиями. Подробнее об удалили один столбец.
столбца, т. д., пользовательских предложений для тем точнее результат
ячейку В7 будетНапример, мы написалиЕСЛИОШИБКА работать весь расчет.Тот, кто никогда не условиями. Подробнее об удалили один столбец.У вас есть вопрос об определенной функции?
Например, написали в форматах, читайте статью
Помогите нам улучшить Excel
столбца, высоту строки по формуле Excel для вычислений.выберите команду значение с использованием скобки {}. Если двух столбцов, читайте
support.office.com
Ошибки в формулах Excel.
Excel ошибка (например, они формулу. А ячейки,, но они только Для лечения подобных ситуаций ошибался — опасен. этих функциях читайте
Исправить ошибку можно, формуле адрес ячейки «Преобразовать дату в в Excel». выходят ошибки. Например,Результат формулы — число,Параметры
типа или формата вы попытаетесь ввести в статье «Соединитьвставлять в другие пустые), то не на которые ссылается
проверяют наличие ошибок в Microsoft Excel(Книга самурая) в статье «Функция удалив эту ссылку
В1 русской буквой текст Excel».Получится так.ошибка в слишком большое или. Если вы используете данных, которые не их вручную, Excel функции «ВПР» и формулы с другими
пиши в ячейке
формула, пока пустые. и не умеют есть мегаполезная функция
Ошибки случаются. Вдвойне обидно, «ЕСЛИ» в Excel» в формуле или «Б». Вышла ошибка.Как найти ошибку вОшибка «# ЗНАЧ!» вExcel «#ИМЯ?», «#ЗНАЧ!», «#ССЫЛКА!» слишком малое для Excel 2007, нажмите поддерживаются в разделе отобразит формулу как «СЦЕПИТЬ» в Excel». функциями. Например. С7 ошибку, а
В таком случае,
заменять их на ЕСЛИОШИБКА (IFERROR), которая когда они случаются и статьях, перечисленных вернуть удаленный столбецПроверяем вычисления в формуле формулеExcel «;#» отображения в Excel.кнопку Microsoft Office аргументов формулы. Например, текст.Если функция СРЗНАЧ или
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B11;НАИМЕНЬШИЙ(ЕСЛИ($F$2=A2:A11;СТРОКА(B2:B11)-1;»»); поставь «пусто». А для Excel это что-то еще. Поэтому умеет проверять заданную не по твоей
внизу, в разделе в таблицу, или поиском ошибок. ExcelExcel., т. д.Чтобы исправить ошибку, изменитеи выберите
нельзя ввести значениеПримечание: СУММ ссылается наСТРОКА()-5));»») если ошибки нет ошибка и в
приходилось использовать их формулу или ячейку вине. Так в
«Другие статьи по удалить всю формулу, подчеркнула ошибку., смотрите в статьеВ Excel ошибкаРассмотрим какие бывают формулу таким образом,Параметры Excel$1,000 Приведенная выше формула будет
ячейки, содержащие ошибкиЭта формула взята (ячейки заполнены), то
ячейке будет написано
обязательно в связке и, в случае Microsoft Excel, некоторые
этой теме». т.д.Исправляем ошибку. «Как проверить формулы «#ЗНАЧ!» указывает на ошибки при написании
чтобы результат ее.в формате валюты, работать не только #ЗНАЧ!, формула также из статьи «Выбрать раздели ячейку А7# с функцией проверки
возникновения любой ошибки, функции и формулыДля примера, мыОшибка «# ДЕЛ/0!» вОшибка «# ССЫЛКА!» в в Excel». то, что значение
формулы и вычисления находился вНа вкладке так как знаки в случае ошибок вызовет ошибку #ЗНАЧ!. сразу много данных
на ячейку В7 ДЕЛ/О! Вот, чтобы вместоЕСЛИ (IF)
выдавать вместо нее могут выдавать ошибки написали такую формулу.ExcelExcelЗдесь Excel нам в ячейке написано
как найти ошибку в диапазоне от -1*10307Формулы доллара используются как
#ЗНАЧ!, но иЧтобы не учитывать ошибки, из таблицы Excel». и напиши результат. этой записи была, создавая вложенные конструкции заданное значение: ноль,
не потому, что =ЕСЛИ(B1=0;»»;A1/B1).. показывает, что стоит не в том формуле Excel, и до 1*10307.в разделе индикаторы абсолютной ссылки, в случае ошибок
мы создадим формулу, Этой формулой выбираем Скопировали формулу по
пустая ячейка, и типа:
пустую текстовую строку вы накосячили приПояснения к формулеЭта ошибка возникает,Эта ошибка появляется, в ячейке текст формате. Например, в как исправить этиСовет:
Параметры вычислений
а запятые — #Н/Д, #ПУСТО!, #ДЕЛ/0! пропускающую ошибочные значения из списка товаров, столбцу, получилось так. применим эти функции.Такой вариант ощутимо медленне «» или что-то вводе, а из-за
excel-office.ru
Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)
. если делим на
если в формуле
(слово «масло»). Нельзя формуле сложения в ошибки. Если в Microsoft Excelустановите флажок как разделители аргументов. и других. в диапазоне и те, которые выбралЕсли вы работаете в У нас есть работает и сложнее еще. временного отсутствия данныхЕсли в ячейке нуль или на указана не существующая
сложить число и диапазоне ячеек естьОшибка в включена проверка ошибок,Включить итеративные вычисления Чтобы предотвратить появлениеТаким же образом можно рассчитывающую среднее для покупатель и переносим Excel 2007 и такая таблица. для понимания, такСинтаксис функции следующий:
или копирования формул В1 стоит нуль, пустую ячейку. Разделим в таблице ячейка, текст, и получить ячейка с текстом,Excel «# # # нажмите кнопку. ошибки #ЧИСЛО!, вводите использовать функцию СУММ: остальных значений. их в лист
новее, то функция
В ячейке С7 мы
что лучше использовать=ЕСЛИОШИБКА(Что_проверяем; Что_выводить_вместо_ошибки) «с запасом» на
то оставить ячейку ячейку А1 на
диапазон ячеек, т. результат цифрой по не с числом. # #»рядом с ячейкой,В поле значения в виде=СУММ(ЕСЛИ(ЕОШИБКА(B2:D2);»»;B2:D2))Чтобы решить эту проблему, заказа. будет называться «ЕСЛИОШИБКА». написали такую формулу. новую функцию ЕСЛИОШИБКА,Так, в нашем примере избыточные ячейки. Классический С1 пустой. Если ячейку В1 (нуль). д. этой конкретной формуле. Формула не может( в которой показанаПредельное число итераций
неформатированных чисел, напримерЗадать вопрос на форуме мы используем функциюЕсть другие Эта функция отличается =A7/B7 Скопировали формулу
planetaexcel.ru
если это возможно.
Содержание
- Исправление ошибки #ЗНАЧ! в функции СРЗНАЧ или СУММ
- У вас есть вопрос об определенной функции?
- Дополнительные сведения
- Excel не считает числа: не суммирует, не вычитает, не перемножает, не делит и не вычитает.(числа не складываются)
- Как же посчитать значения формул при отключенном автоматическом расчете?
- Как включить автоматические выключения формул в Excel?
- Похожее:
- Excel не считает числа: не суммирует, не вычитает, не перемножает, не делит и не вычитает.(числа не складываются): 7 комментариев
- Что делать, если Эксель не считает или неверно считает сумму
- Основные причины неисправности
- Изменяем формат ячеек
- Отключаем режим «Показать формулы»
- Ошибки в синтаксисе
- Включаем пересчет формулы
- Ошибка в формуле
- Другие ошибки
- Формула не растягивается
- Неверно считается сумма ячеек
- Формула не считается автоматически
- Проблемы с вычислением формул в Microsoft Excel
- Устранение проблем с вычислением
- Способ 1: изменение формата ячеек
- Способ 2: отключение режима «Показать формулы»
- Способ 3: исправление ошибки в синтаксисе
- Способ 4: включение пересчета формулы
- Способ 5: ошибка в формуле
Исправление ошибки #ЗНАЧ! в функции СРЗНАЧ или СУММ
Если ячейки, содержащие #VALUE, ссылаются на ячейки с #VALUE! в формулах будет #VALUE! ошибку «#ВЫЧИС!».
Чтобы не учитывать ошибки, мы создадим формулу, пропускающую ошибочные значения в диапазоне и рассчитывающую среднее для остальных значений.
Чтобы решить эту проблему, мы используем функцию СРЗНАЧ в сочетании с функциями ЕСЛИ и ЕОШИБКА для определения наличия ошибок в указанном диапазоне. В данном конкретном случае требуется формула массива:
Это формула массива. Поэтому вам нужно нажать ввод (только если вы используете Microsoft 365 ) или CTRL+SHIFT+ВВОД в формуле.
Примечание: Если у вас есть текущая версия Microsoft 365 ,можно просто ввести формулу в ячейку вывода, а затем нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как формулу массива устаревшей. Для этого сначала выберем выходную ячейку, введите формулу в нее и нажимая для подтверждения CTRL+SHIFT+ВВОД. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Примечание: Приведенная выше формула будет работать не только в случае ошибок #ЗНАЧ!, но и в случае ошибок #Н/Д, #ПУСТО!, #ДЕЛ/0! и других.
Таким же образом можно использовать функцию СУММ:
У вас есть вопрос об определенной функции?
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Источник
Excel не считает числа: не суммирует, не вычитает, не перемножает, не делит и не вычитает.(числа не складываются)
Не один раз сталкивался с ситуацией, когда открываешь «экселевский» файл, присланный по почте, ставишь в ячейках новые числа (цифры), а ячейки с формулами остаются неизменными – не считаются (не складываются, не делятся, не перемножаются). Это явление довольно распространенное, и обусловлено тем, что в программе Excel отключен автоматический расчет формул в ячейках.
Отключать автоматический расчет удобно если у Вас слабый компьютер и приходится работать с большими таблицами по нескольку сотен тысяч строк. Если отключены расчеты машина (компьютер) не зависает и не тормозит.
Как же посчитать значения формул при отключенном автоматическом расчете?
Все довольно просто. Необходимо:
- Зайти во вкладку «формулы»;
- Группа элементов «Вычисление»;
- Кнопка «Произвести вычисления»
Как включить автоматические выключения формул в Excel?
Включить вычисления можно двумя способами:
Первый способ – все в той же вкладке «формулы» в группе элементов «вычисления» выбрать кнопку «Параметры вычислений». Появится выпадающий список из трех позиций:
- Автоматически – автоматическое вычисление;
- Автоматически, кроме данных таблиц;
- Вручную – ручная активация расчетов.
- Открыть меню «Файл» => «Параметры»;
- В появившемся окне перейти во вкладку «Формулы»;
- Поставить флажок напротив пункта «Автоматически» в меню «Вычисления в книге»;
- Нажать кнопку «Ок».
Как видите все несложно.
Удачи в изучении Excel и успехов в работе.
Вопросы можете задавать в комментариях.
Похожее:
- Умножение столбцов таблицы Excel на выбранное число.Как умножить столбец таблицы Excel на одно.
- Как увеличить производительность труда или Таблица: Сочетание горячих клавиш в «Эксель»:Как увеличить производительность труда подчиненных в программе.
- Сумма или среднее значение одной и той же ячейки с разных листов документа Excel.Добрый день. Часто возникают ситуации, когда нужно.
Excel не считает числа: не суммирует, не вычитает, не перемножает, не делит и не вычитает.(числа не складываются): 7 комментариев
подруга дала лицензию и офис не работал, все искал почему, но решил удалить. скачал с торрента другую версию и все работает, теперь буду учиться в экселе работаьь вот!
Не тянется формула по столбцу, он остается пустым, хотя формула присутствует. Автоматический расчет включен, не понимаем в чем дело. Подскажите, пожалуйста.
Добрый день, попробуйте изменить формат ячеек с текстового на общий.
Источник
Что делать, если Эксель не считает или неверно считает сумму
Приложение Эксель используют не только для создания таблиц. Его главным предназначением является расчет чисел по формулам. Достаточно вписать в ячейки новые значения и система автоматически пересчитает их. Однако, в некоторых случаях расчет не происходит. Тогда, необходимо выяснить, почему Эксель не считает сумму.
Основные причины неисправности
Эксель может не считать сумму или формулы по многим причинам. Проблема часто заключается, как в неправильной формуле, так и в системных настройках книги. Поэтому, рекомендуется воспользоваться несколькими советами, чтобы выяснить, какой именно подходит в данной конкретной ситуации.
Изменяем формат ячеек
Программа выводит неправильные расчеты, если указанные форматы не соответствуют значению, которое находится в ячейке. Тогда вычисление или вообще не будет применяться, или выдавать совсем другое число. Например, если формат является текстовым, то расчет проводится не будет. Для программы, это только текст, а не числа. Также, может возникнуть ситуация, когда формат не соответствует действительному. В таком случае, у пользователя не получится правильно вставить вычисление, и Эксель не посчитает сумму и не рассчитает результат формулы.
Чтобы проверить, действительно ли дело в формате, следует перейти во вкладку «Главная». Предварительно, необходимо выбрать непроверенную ячейку. В этой вкладке находится информация о формате.
Если его нужно изменить, достаточно нажать на стрелочку и выбрать требуемый из списка. После этого, система произведет новый расчет.
Список форматов в данном разделе полный, но без описаний и параметров. Поэтому в некоторых случаях пользователь не может найти нужный. Тогда, лучше воспользоваться другим методом. Так же, как и в первом варианте, следует выбрать ячейку. После этого кликнуть правой клавишей мыши и открыть команду «Формат ячеек».
В открытом окне находится полный список форматов с описанием и настройками. Достаточно выбрать нужный и нажать на «ОК».
Отключаем режим «Показать формулы»
Иногда пользователь может заметить, что вместо числа отображено само вычисление и формула в ячейке не считается. Тогда, нужно отключить данный режим. После этого система будет выводить готовый результат расчета, а не выражения.
Для отключения функции «Показать формулы», следует перейти в соответствующий раздел «Формулы». Здесь находится окно «Зависимости». Именно в нем расположена требуемая команда. Чтобы отобразить список всех зависимостей, следует кликнуть на стрелочке. Из перечня необходимо выбрать «Показать» и отключить данный режим, если он активен.
Ошибки в синтаксисе
Часто, неправильное отображение результата является следствием ошибок синтаксиса. Такое случается, если пользователь вводил вычисление самостоятельно и не прибегал к помощи встроенного мастера. Тогда, все ячейки с ошибками не будут выдавать расчет.
В таком случае, следует проверить правильное написание каждой ячейки, которая выдает неверный результат. Можно переписать все значения, воспользовавшись встроенным мастером.
Включаем пересчет формулы
Все вычисления могут быть прописаны правильно, но в случае изменения значений ячеек, перерасчет не происходит. Тогда, может быть отключена функция автоматического изменения расчета. Чтобы это проверить, следует перейти в раздел «Файл», затем «Параметры».
В открытом окне необходимо перейти во вкладку «Формулы». Здесь находятся параметры вычислений. Достаточно установить флажок на пункте «Автоматически» и сохранить изменения, чтобы система начала проводить перерасчет.
Ошибка в формуле
Программа может проводить полный расчет, но вместо готового значения отображается ошибка и столбец или ячейка может не суммировать числа. В зависимости от выводимого сообщения можно судить о том, какая неисправность возникла, например, деление на ноль или неправильный формат.
Для того, чтобы перепроверить синтаксис и исправить ошибку, следует перейти в раздел «Формулы». В зависимостях находится команда, которая отвечает за вычисления.
Откроется окно, которое отображает саму формулу. Здесь, следует нажать на «Вычислить», чтобы провести проверку ошибки.
Другие ошибки
Также, пользователь может столкнуться с другими ошибками. В зависимости от причины, их можно исправить соответствующим образом.
Формула не растягивается
Растягивание необходимо в том случае, когда несколько ячеек должны проводить одинаковые вычисления с разными значениями. Но бывает, что этого не происходит автоматически. Тогда, следует проверить, что установлена функция автоматического заполнения, которая расположена в параметрах.
Кроме того, рекомендуется повторить действия для растягивания. Возможно, ошибка была в неправильной последовательности.
Неверно считается сумма ячеек
Сумма также считается неверно, если в книге находятся скрытые ячейки. Их пользователь не видит, но система проводит расчет. В итоге, программа отображает одно значение, а реальная сумма должна быть другой.
Такая же проблема возникает, если отображены значения с цифрами после запятой. В таком случае их требуется округлить, чтобы вычисление производилось правильно.
Формула не считается автоматически
Эксель не будет считать формулу автоматически, если данная функция отключена в настройках. Пользователь может устранить данную проблему, если перейдет в параметры, которые находятся в разделе «Файл».
В открытом окне следует перейти к настройке автоматического перерасчета и установить флажок на соответствующей команде. После этого требуется сохранить изменения.
Источник
Проблемы с вычислением формул в Microsoft Excel
Одной из наиболее востребованных возможностей Excel является работа с формулами. Благодаря данной функции программа самостоятельно производит различного рода расчеты в таблицах. Но иногда случается так, что пользователь вписывает формулу в ячейку, но она не выполняет своего прямого назначения – вычисления результата. Давайте разберемся, с чем это может быть связано, и как решить данную проблему.
Устранение проблем с вычислением
Причины проблем с вычислением формул в Экселе могут быть совершенно разными. Они могут быть обусловлены, как настройками конкретной книги или даже отдельного диапазона ячеек, так и различными ошибками в синтаксисе.
Способ 1: изменение формата ячеек
Одной из самых распространенных причин, по которой Эксель вообще не считает или не правильно считает формулы, является неверно выставленный формат ячеек. Если диапазон имеет текстовый формат, то расчет выражений в нем вообще не производится, то есть, они отображаются как обычный текст. В других случаях, если формат не соответствует сути рассчитываемых данных, результат, выводимый в ячейку, может отображаться некорректно. Давайте выясним, как решить эту проблему.
- Для того, чтобы посмотреть, какой формат имеет конкретная ячейка или диапазон, переходим во вкладку «Главная». На ленте в блоке инструментов «Число» имеется поле отображения текущего формата. Если там указано значение «Текстовый», то формула точно вычисляться не будет.
Для того, чтобы произвести смену формата достаточно кликнуть по данному полю. Откроется список выбора форматирования, где можно выбрать значение, соответствующее сути формулы.
Но выбор типов формата через ленту не такой обширный, как через специализированное окно. Поэтому лучше применить второй вариант форматирования. Выделяем целевой диапазон. Кликаем по нему правой кнопкой мыши. В контекстном меню выбираем пункт «Формат ячеек». Можно также после выделения диапазона нажать комбинацию клавиш Ctrl+1.
Теперь будет производиться расчет формулы в стандартном порядке с выводом результата в указанную ячейку.
Способ 2: отключение режима «Показать формулы»
Но возможно причина того, что вместо результатов расчета у вас отображаются выражения, состоит в том, что в программе включен режим «Показать формулы».
- Чтобы включить отображение итогов, переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул», если кнопка «Показать формулы» активна, то кликаем по ней.
Способ 3: исправление ошибки в синтаксисе
Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если вы вводили её вручную, а не через Мастер функций, то такое вполне вероятно. Очень распространенной ошибкой, связанной с отображением выражения, как текста, является наличие пробела перед знаком «=».
В таких случаях нужно внимательно пересмотреть синтаксис тех формул, которые неправильно отображаются, и внести в них соответствующие коррективы.
Способ 4: включение пересчета формулы
Бывает и такая ситуация, что формула вроде и отображает значение, но при изменении связанных с ней ячеек сама не меняется, то есть, результат не пересчитывается. Это означает, что у вас неправильно настроены параметры вычислений в данной книге.
- Перейдите во вкладку «Файл». Находясь в ней, следует кликнуть по пункту «Параметры».
Теперь все выражения в данной книге будут автоматически пересчитываться при изменении любого связанного значения.
Способ 5: ошибка в формуле
Если же программа все-таки производит расчет, но в результате показывает ошибку, то тут вероятна ситуация, что пользователь просто ошибся при вводе выражения. Ошибочными формулами считаются те, при расчете которых в ячейке появляются следующие значения:
В этом случае нужно проверить, правильно ли записаны данные в ячейках, на которые ссылается выражение, нет ли в них ошибок в синтаксисе или не заложено ли в самой формуле какое-либо некорректное действие (например, деление на 0).
Если функция сложная, с большим количеством связанных ячеек, то легче проследить вычисления с помощью специального инструмента.
- Выделяем ячейку с ошибкой. Переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул» кликаем по кнопке «Вычислить формулу».
Как видим, причины того, что Эксель не считает или не правильно считает формулы, могут быть совершенно различными. Если вместо расчета у пользователя отображается сама функция, тот в этом случае, скорее всего, либо ячейка отформатирована под текст, либо включен режим просмотра выражений. Также, возможна ошибка в синтаксисе (например, наличие пробела перед знаком «=»). В случае если после изменения данных в связанных ячейках результат не обновляется, то тут нужно посмотреть, как настроено автообновление в параметрах книги. Также, нередко вместо корректного результата в ячейке отображается ошибка. Тут нужно просмотреть все значения, на которые ссылается функция. В случае обнаружения ошибки следует устранить её.
Помимо этой статьи, на сайте еще 12339 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Источник
Для расчета среднего значения по условию в Excel используется функция СРЗНАЧЕСЛИ. Кроме суммирования и подсчета количества значений, вычисление среднего значения по условию – это одна из самый часто выполняемых операций с диапазонами ячеек в Excel. Поэтому стоит научится эффективно использовать такие востребованные функции.
Как найти среднее арифметическое число по условию в Excel
Ниже на рисунке представлен список медалистов зимних олимпийских игр за 1972-ой год. Допустим нам необходимо узнать средний показатель результатов призеров из Швейцарии. Название страны для условия записано в отдельной ячейке, благодаря чему можно легко менять его на другое. Формула решения:
Для решения данной задачи Excel предлагает специальную функцию СРЗНАЧЕСЛИ. Принцип работы данной функции очень похож на родственную ей СУММЕСЛИ для суммирования по условию. Также использует те же обязательные для заполнения аргументы: Диапазон и Условие. Отличается только опциональный аргумент: Диапазон_усреднения.
В данном примере каждое значение в диапазоне … проверяется: будет или не будет оно учтено при расчете среднего показателя после выборки. Все зависит от того выполняет ли условие значение текущей ячейки согласно с логическим выражением, указанным в критерии отбора. При этом если не найдется ни одной ячейки удовлетворяющей условие выборки, тогда функция СРЗНАЧЕСЛИ возвращает ошибку #ДЕЛ/0!
Альтернативная формула для расчета среднего числа с условием
При желании можно составить альтернативную формулу без использования функции СРЗНАЧЕСЛИ. Среднее значение в математике называется среднее арифметическое – является суммой всех значений, разделенной на их количество. Аналогично функция СРЗНАЧЕСЛИ суммирует все значения в диапазоне, указанном в первом аргументе с учетом заданного критерия выборки значений указанном уже во втором аргументе. А затем результат разделяется на количество выбранных значений. Такой же самый результат мы получим, используя комбинацию двух родственных функций, разделив результат вычисления СУММЕСЛИ на результат СЧЁТЕСЛИ. Поэтому для вычисления среднего значения по условию анализа выше приведенной таблицы можно применить другую формулу:
В программе Excel 90% задач можно решить несколькими правильными решениями.