Ошибка при выполнении функции average произошла ошибка деление на ноль невозможно

Если ячейка полностью заполнена знаками решётки (#), это вовсе не ошибка. В ячейке недостаточно места для отображения результата. Увеличьте размеры ячейки или уменьшите шрифт, чтобы результат мог отобразиться.

Если ошибка всё же произошла, помочь в её исправлении поможет расшифровка:

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

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

Отслеживание ошибки через этапы вычисления

Так решетки (######) вместо значения ячеек можно увидеть при отрицательно дате. Например, мы пытаемся отнять от старой даты новую дату. А в результате вычисления установлен формат ячеек «Дата» (а не «Общий»).

Как сделать ошибки в excel?

Но выбор типов формата через ленту не такой обширный, как через специализированное окно. Поэтому лучше применить второй вариант форматирования. Выделяем целевой диапазон. Кликаем по нему правой кнопкой мыши. В контекстном меню выбираем пункт «Формат ячеек». Можно также после выделения диапазона нажать комбинацию клавиш Ctrl+1.

Выполнение деления

В Microsoft Excel деление можно произвести как при помощи формул, так и используя функции. Делимым и делителем при этом выступают числа и адреса ячеек.

Лист Эксель можно использовать как своеобразный калькулятор, просто деля одно число на другое. Знаком деления выступает слеш (обратная черта) – «/».

1. Становимся в любую свободную ячейку листа или в строку формул. Ставим знак «равно»(=). Набираем с клавиатуры делимое число. Ставим знак деления (/). Набираем с клавиатуры делитель. В некоторых случаях делителей бывает больше одного. Тогда, перед каждым делителем ставим слеш (/).

После этого Эксель рассчитает формулу и в указанную ячейку выведет результат вычислений.

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

Как известно, деление на 0 является некорректным действием. Поэтому при такой попытке совершить подобный расчет в Экселе в ячейке появится результат «#ДЕЛ/0!».

Допустим, вы хотите узнать количество человеко-часов, затраченных на завершение проекта (общее количество сотрудников в Project), или фактические нормы миль на галлон для вашего последнего межфирменного перерыва (общее количество мильных галлонов). Есть несколько способов разделения чисел.

Как сделать функцию деления в excel?

Выделяем в ячейку, в которую будет выводиться результат вычисления. Ставим в ней знак «=». Далее кликаем по месту, в котором расположено делимое. За этим её адрес появляется в строке формул после знака «равно». Далее с клавиатуры устанавливаем знак «/». Кликаем по ячейке, в которой размещен делитель. Если делителей несколько, так же как и в предыдущем способе, указываем их все, а перед их адресами ставим знак деления.

Чтобы упростить процесс – можно включить стрелки ссылок. Можно легко определить на какие ячейки ссылается формула, нажав Формулы – Зависимости формул – Влияющие ячейки. На листе синими стрелками будет указано, на какие данные Вы ссылаетесь.

Как разделить одно число на другое

На рабочем листе книги «Эксель» введите в разные ячейки любые два числа. Они помогут разобраться с тем, как сделать формулу деления в Excel. Затем в свободной ячейке наберите знак начала формулы (равно =) и укажите первую ячейку с делимым. Далее наберите знак деления в формуле Excel – обратный слэш – и укажите ячейку с делителем.

Удобно, что при попытке деления на ячейку, содержимое которой представляет собой 0, «Эксель» выведет соответствующую ошибку: #ДЕЛ/0!.

Важно! Эксель позволяет делить между собой любые числа, в том числе и даты. А вот при попытке выполнить деление над ячейками, содержащими текст, вы получите ошибку «#ЗНАЧ!», о есть неверное значение.

Если Вы написали формулу, а она возвращает очевидно неправильный результат – разбираемся с логикой формулы. Вы могли банально ошибиться в скобках, порядке следования операторов. Изучите правила написания формул и проверьте все ли верно в Вашей. Если верно – переходим к следующему шагу.

Как сделать чтобы excel не убирал нули?

• Столбцы обозначены буквами в верхней части вашего листа. Они начинаются с «А» и далее по алфавиту; после буквы «Z» используются сочетания двух букв.
• Строки обозначены вдоль левой стороны. Они пронумерованы в последовательном порядке.

На рабочeм листe книги «Эксeль» ввeдитe в разныe ячeйки любыe два числа. Они помогут разобраться с тeм, как сдeлать формулу дeлeния в Excel. Затeм в свободной ячeйкe набeритe знак начала формулы (равно =) и укажитe пeрвую ячeйку с дeлимым. Далee набeритe знак дeлeния в формулe Excel – обратный слэш – и укажитe ячeйку с дeлитeлeм.

Дeлeниe с остатком

При выполнeнии дeлeния в большинствe случаeв мы получаeм дробноe число. Для упрощeния восприятия таких чисeл eсть в «Эксeль» два важных момeнта.

В «Эксeль» eсть двe отличныe функции для получeния частного и остатка от дeлeния. Они носят названиe «Частноe» и «Остаток» и обe принимают два обязатeльных аргумeнта – числитeль и знамeнатeль. Разбeрeмся с ними подробнee.

Ввeдитe в «Эксeль» два числа – 7 и 3 (в ячeйки D9 и D10 в нашeм случаe), и правee от них – формулы:

Как видим, частноe от дeлeния равно двум, остаток – 1. В любую свободную ячeйку ввeдитe формулу:

Здeсь знак ампeрсанда помогаeт присоeдинить к содeржимому одной ячeйки содeржимоe другой в видe тeкста.

Рeзультат выполнeния данной формулы будeт слeдующий: 2 1/3 – двe цeлых и одна трeть.

Как видим, формула дeлeниe в Excel нe прeдставляeт собой ничeго сложного. Главноe помнить, что при вычислeнии формул «Эксeль» используeт стандартныe правила матeматики.

В Экселе с помощью функций можно получить целую часть от деления и остаток от деления. Целую часть от деления можно получить с помощью функции ЧАСТНОЕ. В формулу подставляется два значения, числитель и знаменатель.

Перестали считать формулы — как исправить? Проблемы с формулами в таблице Excel

1. Запись ИНДЕКС(B3:B9;ПОИСКПОЗ(МИН(ЕСЛИ(B3:B9>=E4;B3:B9;»»));B3:B9;0);1) используется для поиска указанной ближайшей величины из диапазона (B3:B9), которая больше указанной (E4).
2. Если искомая величина превышает значение последнего элемента в диапазоне (элемента с наибольшим значением), будет возвращен код ошибки #ЗНАЧ!. Благодаря функции ЕСЛИОШИБКА вместо кода ошибки будет отображена текстовая строка «Нет трубы в списке».

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

Ошибка #ИМЯ! в Excel

Относиться к категории ошибки в написании функций. Недопустимое имя: #ИМЯ! – значит, что Excel не распознал текста написанного в формуле (название функции =СУМ() ему неизвестно, оно написано с ошибкой). Это результат ошибки синтаксиса при написании имени функции. Например:

В данном случаи пересечением диапазонов является ячейка C3 и функция отображает ее значение.

Заданные аргументы в функции: =СУММ(B4:D4 B2:B3) – не образуют пересечение. Следовательно, функция дает значение с ошибкой – #ПУСТО!

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

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

• Вставить рисунок в Excel
• Как отобразить гривны в табличном редакторе Excel
• Ввод данных в ячейки таблицы Excel
• Найти в Excel 2007 то, что было в Еxcel 2003
• Как удалить все пустые строки в таблице Excel
• Полезные уроки по Excel
• Относительные и абсолютные ссылки в формулах Excel
• Строки в Excel
• Вставка текущих даты и времени в таблицы Excel
• Спецсимволы для вставки в анкету

На самом деле, редактор таблиц «Microsoft Excel» достаточно многофункциональная вещь. К примеру, его можно использовать исключительно как калькулятор, используя соответствующие знаки. К примеру, для умножения нужно использовать звёздочку («*»), а для деления достаточно слэша («/»).

Алгоритмы удаления нулей

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

Способ 1: настройки Excel

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

1. Находясь во вкладке «Файл», переходим в раздел «Параметры».
2. В запустившемся окне делаем перемещение в раздел «Дополнительно». В правой части окна ищем блок настроек «Показать параметры для следующего листа». Снимаем флажок около пункта «Показывать нули в ячейках, которые содержат нулевые значения». Для приведения изменения настроек в действие не забываем нажать на кнопку «OK» внизу окна.

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

Способ 2: применение форматирования

Скрыть значения пустых ячеек можно при помощи изменения их формата.

Для сохранения введенных изменений жмем на кнопку «OK».

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

Способ 3: условное форматирование

Для удаления лишних нулей можно также применить такой мощный инструмент, как условное форматирование.

1. Выделяем диапазон, в котором могут содержаться нулевые значения. Находясь во вкладке «Главная», кликаем по кнопке на ленте «Условное форматирование», которая размещена в блоке настроек «Стили». В открывшемся меню последовательно переходим по пунктам «Правила выделения ячеек» и «Равно».
2. Открывается окошко форматирования. В поле «Форматировать ячейки, которые РАВНЫ» вписываем значение «0». В правом поле в раскрывающемся списке кликаем по пункту «Пользовательский формат…».
3. Открывается ещё одно окно. Переходим в нем во вкладку «Шрифт». Кликаем по выпадающему списку «Цвет», в котором выбираем белый цвет, и жмем на кнопку «OK».
4. Вернувшись в предыдущее окно форматирования, тоже жмем на кнопку «OK».

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

Урок: Условное форматирование в Excel

Способ 4: применение функции ЕСЛИ

Ещё один вариант скрытия нулей предусматривает использование оператора ЕСЛИ.

1. Выделяем первую ячейку из того диапазона, в который выводятся результаты вычислений, и где возможно будут присутствовать нули. Кликаем по пиктограмме «Вставить функцию».
2. Запускается Мастер функций. Производим поиск в списке представленных функций оператора «ЕСЛИ». После того, как он выделен, жмем на кнопку «OK».
3. Активируется окно аргументов оператора. В поле «Логическое выражение» вписываем ту формулу, которая высчитывает в целевой ячейке. Именно результат расчета этой формулы в конечном итоге и может дать ноль. Для каждого конкретного случая это выражение будет разным. Сразу после этой формулы в том же поле дописываем выражение «=0» без кавычек. В поле «Значение если истина» ставим пробел – « ». В поле «Значение если ложь» опять повторяем формулу, но уже без выражения «=0». После того, как данные введены, жмем на кнопку «OK».
4. Но данное условие пока применимо только к одной ячейке в диапазоне. Чтобы произвести копирование формулы и на другие элементы, ставим курсор в нижний правый угол ячейки. Происходит активация маркера заполнения в виде крестика. Зажимаем левую кнопку мыши и протягиваем курсор по всему диапазону, который следует преобразовать.
5. После этого в тех ячейках, в которых в результате вычисления окажутся нулевые значения, вместо цифры «0» будет стоять пробел.

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

Способ 5: применение функции ЕЧИСЛО

Следующий способ является своеобразной комбинацией функций ЕСЛИ и ЕЧИСЛО.

А вот значения следующих двух аргументов оператора ЕСЛИ мы переставляем местами. То есть, в поле «Значение если истина» указываем формулу расчета, а в поле «Значение если ложь» ставим пробел – « ».

Мы рады, что смогли помочь Вам в решении проблемы.

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

Помогла ли вам эта статья?

Так же разделить числа в редакторе таблиц «Microsoft Excel» можно с помощью мастера функций, а конкретно, с помощью одного инструмента, который называется «ЧАСТНОЕ». Главная особенность данной функции в том, что она делит число на число не оставляя остаток.

Способ 4: деление столбца на константу

Все так же, как в Excel 2007, только сначала открываем меню Файл, переходим на Параметры, а потом – Дополнительно – Показать параметры для следующего листа – уберите, или поставьте галочку на пункте – Показывать нули в ячейках, которые содержат нулевые значения.

Отключаем режим “Показать формулы”

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

Если в формуле допустить ошибки, программа может воспринимать ее как простое текстовое значение, следовательно, расчеты по ней выполнятся не будут. Например, одной из самых популярных ошибок является пробел, установленный перед знаком “равно”. При этом помним, что знак “=” обязательно должен стоять перед любой формулой.

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

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

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

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

• #ДЕЛ/0! – результат деления на ноль;
• #Н/Д – ввод недопустимых значений;
• #ЧИСЛО! – неверное числовое значение;
• #ЗНАЧ! – используется неправильный вид аргумента в функции;
• #ПУСТО! – неверно указан адрес дапазона;
• #ССЫЛКА! – ячейка, на которую ссылалась формула, удалена;
• #ИМЯ? – некорректное имя в формуле.

Если мы видим одну из вышеперечисленных ошибок, в первую очередь проверяем, все ли данные в ячейках, участвующих в формуле, заполнены корректно. Затем проверяем саму формулу и наличие в ней ошибок, в том числе тех, которые противоречат законам математики. Например, не допускается деление на ноль (ошибка #ДЕЛ/0!).

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

Также можно воспользоваться полезным инструментом “Проверка ошибок”, который расположен в том же блоке.

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

Небольшое предупреждение

​=ЕСЛИ(C2>B2;C2-B2;0)​имеет значение ИСТИНА.​ если​Если удалить столбец B,​Адрес указан без разделяющего​ значение даты или​

​ вручную работают.​ не считаются,​ те, при расчете​ как текст, если​«Текстовый»​ язык пользовательского интерфейса​ Начальный​ можно применять стили​ версия Excel, которая​ вложении операторов ЕСЛИ​ с функцией ЕПУСТО:​На рисунке выше мы​значение_если_ложь​Другими словами, формула =НЕ(ЛОЖЬ)​ формула вернет ошибку​ двоеточия:​ времени. Думаю, Вы​

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

​=ЕСЛИ(ЕПУСТО(D2);”Пустая”;”Не пустая”)​ возвращаем не текст,​ (необязательно)​ вернет ИСТИНА, а​#ССЫЛКА!​В имени функции допущена​ знаете, что Excel​

Распространенные неполадки

​ были допущены ошибки,​​ вычисляться не будет.​​Общий доступ​​Панель быстрого доступа можно​​ срезы.​ компьютером. Excel Starter​ неточность, формула может​Эта формула означает:​

Иногда при вычислениях выпадают ошибки, начинающиеся со знака «#». Например, «#Н/Д», «#ЧИСЛО!», и т.д. эти ошибки я описывал ранее, прочтите этот пост и постарайтесь осмыслить причину появления Вашей ошибки. Когда это произойдет, Вы легко все исправите.

Исправляем ошибки в формуле

• И(B4>=80;C4>=70;СРЗНАЧ(B4:D4)>=75) – проверяемые логические выражения согласно условию задачи;
• “Зачисл.” – результат, если функция И вернула значение ИСТИНА (все выражения, представленные в виде ее аргументов, в результате вычислений вернули значение ИСТИНА);
• “Не зач.” – результат, если И вернула ЛОЖЬ.

Логическое выражение «С2>=8» построено с помощью операторов отношения «>» и «=». Результат его вычисления – логическая величина «ИСТИНА» или «ЛОЖЬ». В первом случае функция возвращает значение «В2/2». Во втором – «В2».

В учебнике показано, как использовать функцию СРЗНАЧЕСЛИ в 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 СРЗНАЧЕСЛИ в реальных рабочих листах, чтобы найти среднее значение ячеек, соответствующих вашим критериям.

СРЗНАЧЕСЛИ текстовые критерии

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

Например, чтобы усреднить числа в столбце 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 для вычисления среднего арифметического с условиями. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!