Как заменить н д на 0 в excel

Обновлено: 03.07.2024

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

Причины, почему не отображается ноль в Экселе

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

Ноль не отображается в новых версиях

В ситуации, когда не ставится 0 в Excel новых версий (после 2010-го), сделайте следующие шаги:

  • Войдите в «Файл», а далее «Параметры».
  • Кликните на пункт «Дополнительно».


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


Для отображение скрытых значений в выделенных секциях, сделайте следующее:

  • Выделите секцию с 0-ыми параметрами.
  • Жмите на кнопку Ctrl+1.
  • На вкладке «Главная» жмите «Формат», а после «Формат ячеек».


  • Выберите «Число», а после «Общий».
  • Жмите ОК.


Если в Эксель не отображается 0 в выделенной секции, сделайте следующее:

  1. Выделите ячейки, не отображается нужное число.
  2. Войдите в раздел «Главная», а далее «Формат» и «Формат ячеек».
  3. Жмите на кнопку «Число» и «Все форматы».
  4. В поле «Тип» удалите запись 0;-0;;@ и сохраните данные.


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

  1. Выделите ячейку с 0.
  2. В разделе «Главная» жмите на стрелку возле «Условное форматирование».
  3. Слева введите 0.
  4. Справа выберите «Пользовательский …».
  5. В поле «Формат ячейки» откройте «Шрифт».
  6. В поле «Цвет» укажите обычный черный и кликните «ОК».


Одна из причин, почему в Экселе не пишется 0 в отчетной таблице — внесение соответствующих данных. Для исправления сделайте следующее:

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

Если все равно не пишется ноль в Экселе, убедитесь в правильности действий. В дальнейшем по желанию можно вернуть настройки и скрыть 0-ые значения, возвращенные формулой, заменить эту цифру тире или пробелами, брать ноль в отчете сводной таблицы и т. д.

Не отображается ноль в Excel 2010

В ситуации, когда не пишет 0 в Экселе 2010, можно воспользоваться той же инструкцией, что рассмотрена выше. Для этого зайдите в «Файл» и «Параметры», а после «Дополнительно». На следующем шаге зайдите в группу «Показать параметры для следующего листа» и установите пункт, предусматривающий включение 0. Если ноль не ставится в Экселе из-за этих настроек, указанные шаги должны дать результат.

Если ноль не отображается в выделенных секциях, сделайте следующее:

  • Выделите нужные участник, где нет 0.
  • Войдите в «Главная» и жмите «Формат», а после «Формат ячеек».



Для отображения 0-ых значений, указанных формулой, сделайте следующее:

  • Выберите ячейку в Экселе, где не отображается ноль.
  • В разделе «Главная» в группе «Стили» жмите на стрелку возле «Условное форматирование».
  • Наведите указатель на «Правила выделения …».
  • Выберите «Равно» и слева введите 0.
  • Справа укажите «Пользовательский формат».
  • В «Формат ячеек» выберите «Шрифт».
  • В поле «Цвет» укажите черный.


Если ноль не отображается в отчете в Экселе общей таблицы, войдите в него, а после в «Параметры» и «Параметры сводной таблицы», где жмите на пункт «Параметры». Здесь войдите в «Разметка и формат» и в поле «Формат» уберите отметку «Для ошибок отображать» и «Для пустых ячеек отображать».

Ноль не отображается в Эксель 2007

В ситуации с программой 2007 года вопрос, почему 0 не ставится в Эксель, имеет схожие ответы и решения.

Если ноль вообще не отображается на листе, сделайте следующее:

  1. Жмите на кнопку Майкрософт Офис.
  2. Войдите в параметры, а потом «Дополнительные параметры».
  3. Кликните «Показать параметры для следующего листа».
  4. Поставьте отметку в поле «Показывать нули в ячейка, которые содержат …».

В случае, когда ноль не отображается в выделенных секциях (он был скрыт настройками), сделайте следующее:

  1. Выделите нужную часть.
  2. Зайдите в раздел «Главная», а после этого «Ячейки».
  3. Наведите указатель на «Формат» и выберите «Формат ячеек».
  4. В списке «Категория» выберите «Общий.

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

Если нулевое значение не отображается в сводной таблице Excel, здесь также применима инструкция, которая рассмотрена для 2010-й версии.

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

Почему ЕСЛИОШИБКА лучше и я называю её более оптимизированной? Разберем первую формулу подробнее:
=ЕСЛИ(ЕОШ( A1 / B1 );0; A1 / B1 )
Если вычислить пошагово, то увидим, что сначала происходит вычисление выражения A1 / B1 (т.е. деление). И если его результат ошибка – то ЕОШ вернет ИСТИНА (TRUE) , которое будет передано в ЕСЛИ (IF) . И тогда функцией ЕСЛИ(IF) будет возвращено значение из второго аргумента 0.
Но если результат не является ошибочным и ЕОШ (ISERR) возвращает ЛОЖЬ (FALSE) – то функция заново будет вычислять уже вычисленное ранее выражение: A1 / B1
С приведенной формулой это особой роли не играет. Но если применяется формула вроде ВПР (VLOOKUP) с просмотром на несколько тысяч строк – то вычисление два раза может значительно увеличить время пересчета формул.
Функция же ЕСЛИОШИБКА (IFERROR) один раз вычисляет выражение, запоминает его результат и если он ошибочен возвращает записанное вторым аргументом. Если же ошибки нет, то возвращает запомненный результат вычисления выражения из первого аргумента. Т.е. вычисление по факту происходит один раз, что практически не будет влиять на скорость общего пересчета формул.
Поэтому если у вас Excel 2007 и выше и файл не будет использоваться в более ранних версиях – то имеет смысл использовать именно ЕСЛИОШИБКА (IFERROR) .

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

Итак, есть на листе такие формулы, ошибки которых надо обработать. Если подобных формул для исправления одна-две(да даже 10-15) – то проблем почти нет заменить вручную. Но если таких формул несколько десятков, а то и сотен – проблема приобретает почти вселенские масштабы :-). Однако процесс можно упростить через написание относительно простого кода Visual Basic for Application.
Для всех версий Excel:

Для версий 2007 и выше

Как это работает
Если не знакомы с макросами, то для начала лучше прочитать как их создавать и вызывать: Что такое макрос и где его искать?, т.к. может случиться так, что все сделаете правильно, но забудете макросы разрешить и ничего не заработает.

Копируете приведенный код, переходите в редактор VBA(Alt+F11), создаете стандартный модуль(Insert -Module) и просто вставляете в него этот код. Переходите в нужную книгу Excel и выделяете все ячейки, формулы в которых необходимо преобразовать таким образом, чтобы в случае ошибки они возвращали ноль. Жмете Alt+F8, выбираете код IfIsErrNull(или IfErrorNull, в зависимости от того, какой именно скопировали) и жмете Выполнить.
Ко всем формулам в выделенных ячейках будет добавлена функция обработки ошибки. Приведенные коды учитывают так же:
-если в формуле уже применена функция ЕСЛИОШИБКА или ЕСЛИ(ЕОШ, то такая формула не обрабатывается;
-код корректно обработает так же функции массива;
-выделять можно несмежные ячейки(через Ctrl).
В чем недостаток: сложные и длинные формулы массива могут вызвать ошибку кода, в связи с особенностью данных формул и их обработкой из VBA. В таком случае код напишет о невозможности продолжить работу и выделит проблемную ячейку. Поэтому настоятельно рекомендую производить замены на копиях файлов.
Если значение ошибки надо заменить на пусто, а не на ноль, то надо строку

Const sToReturnVal As String = "0"

Удалить, а перед строкой

Удалить апостроф ( ' )

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

Всем привет. Эта статья – одна из важнейших, что есть на этом сайте. Уже многие годы я учу вас пользоваться функциями поиска: ВПР, ПОИСКПОЗ и др. Эти функции разыскивают нужное значение в таблице и возвращают что-то в зависимости от результата поиска. Это важнейшие инструменты для любого пользователя Excel, вам нужно иметь полное представление о том, как они работают.

Это очень полезная ошибка, она возникает, когда в расчётах, или в данных что-то не так, и функция не может найти искомое значение. Чтобы разобраться с этим, задайте себе вопрос: возможно ли, что нужного значения нет в таблице? В зависимости от ответа, имеем два сценария.

Отсутствие значения в исходной таблице маловероятно или исключено

Если значение должно быть, а его нет, значит нужно проверить:

Отсутствие значения в исходной таблице возможно

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

Эта функция универсальна и позволяет вам определить, что вывести, если значение не найдено:


Можно использовать ЕСЛИОШИБКА, чтобы вывести «НЕ НАЙДЕНО» вместо ошибки:


Выглядит лучше, чем в первом случае, не находите?

Перехват функцией ЕСНД


Не выводить ничего в случае ошибки

Вместо строки «НЕ НАЙДЕНО», поставьте пустые кавычки. Отлично, когда результатом поиска должна быть строка. Вы строку и вернёте, только пустую; Вместо «НЕ НАЙДЕНО» укажите ноль и отключите показ нулей в ячейке. Я делаю так, если результат поиска участвует в последующих вычислениях. Мы ожидаем в этой ячейке число, его и возвращаем:

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

Есть несколько способов исправления этой ошибки.

Убедитесь в том, что делитель в функции или формуле не равен нулю и соответствующая ячейка не пуста.

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

Оценка знаменателя на наличие нуля или пустого значения


Кроме того, эту ошибку можно отключить путем вложения операции деления внутрь функции ЕСЛИОШИБКА. Опять же, используя a2/a3, можно использовать = ЕСЛИОШИБКА (a2/a3, 0). Это сообщает Excel, если формула возвращает ошибку, а затем возвращается значение 0, в противном случае возвращают результат формулы.

В версиях до Excel 2007 можно использовать синтаксис ЕСЛИ(ЕОШИБКА()): =ЕСЛИ(ЕОШИБКА(A2/A3);0;A2/A3) (см. статью Функции Е).

Совет: Если в Microsoft Excel включена проверка ошибок, нажмите кнопку рядом с ячейкой, в которой показана ошибка. Выберите пункт Показать этапы вычисления, если он отобразится, а затем выберите подходящее решение.

У вас есть вопрос об определенной функции?

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

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

Перед нами таблица, состоящая из шести строк, в которой одно число делят на другое. В результате этих действий появилась ошибка дел/0.

Сначала исправим ошибку в ячейки «D4», для этого воспользуемся функцией ЕСЛИОШИБКА (X,Y), где X – это выполняемое действие, а Y – значение, которое должно выводиться на экран в случае возникновения ошибки. Получается, пишем следующую формулу: =ЕСЛИОШИБКА(B4/C4;0).

Исправив первую ошибку, перейдем ко второй. Но на этот раз воспользуемся известной функцией ЕСЛИ, пропишем условие, что если в столбце «С» стоит ноль, то выполняется не деление, а ставиться просто ноль. Вот как будет выглядит формула в ячейки «D6»: =ЕСЛИ(C6=0;0;B6/C6).

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

В математике деление на ноль – невозможно! Одним из способов для объяснения данного правила является анализ процесса, который показывает, что происходит, когда одно число разделено на другое.

Ошибка деления на ноль в Excel

В реальности операция деление это по сути тоже что и вычитание. Например, деление числа 10 на 2 является многократным вычитанием 2 от 10-ти. Многократность повторяется до той поры пока результат не будет равен 0. Таким образом необходимо число 2 вычитать от десяти ровно 5 раз:

Если же попробовать разделить число 10 на 0, никогда мы не получим результат равен 0, так как при вычитании 10-0 всегда будет 10. Бесконечное количество раз вычитаний ноля от десяти не приведет нас к результату =0. Всегда будет один и ото же результат после операции вычитания =10:


Но при необходимости можно обойти возникновения ошибки деления на 0 в Excel. Просто следует пропустить операцию деления если в знаменателе находится число 0. Решение реализовывается с помощью помещения операндов в аргументы функции =ЕСЛИ():


Таким образом формула Excel позволяет нам «делить» число на 0 без ошибок. При делении любого числа на 0 формула будет возвращать значение 0. То есть получим такой результат после деления: 10/0=0.

Как работает формула для устранения ошибки деления на ноль

Для работы корректной функция ЕСЛИ требует заполнить 3 ее аргумента:

  1. Логическое условие.
  2. Действия или значения, которые будут выполнены если в результате логическое условие возвращает значение ИСТИНА.
  3. Действия или значения, которые будут выполнены, когда логическое условие возвращает значение ЛОЖЬ.

В данном случаи аргумент с условием содержит проверку значений. Являются ли равным 0 значения ячеек в столбце «Продажи». Первый аргумент функции ЕСЛИ всегда должен иметь операторы сравнения между двумя значениями, чтобы получить результат условия в качестве значений ИСТИНА или ЛОЖЬ. В большинстве случаев используется в качестве оператора сравнения знак равенства, но могут быть использованы и другие например, больше> или меньше >. Или их комбинации – больше или равно >=, не равно !=.

Если условие в первом аргументе возвращает значение ИСТИНА, тогда формула заполнит ячейку значением со второго аргумента функции ЕСЛИ. В данном примере второй аргумент содержит число 0 в качестве значения. Значит ячейка в столбце «Выполнение» просто будет заполнена числом 0 если в ячейке напротив из столбца «Продажи» будет 0 продаж.

Если условие в первом аргументе возвращает значение ЛОЖЬ, тогда используется значение из третьего аргумента функции ЕСЛИ. В данном случаи — это значение формируется после действия деления показателя из столбца «Продажи» на показатель из столбца «План».

Таким образом данную формулу следует читать так: «Если значение в ячейке B2 равно 0, тогда формула возвращает значение 0. В противные случаи формула должна возвратить результат после операции деления значений в ячейках B2/C2».

Формула для деления на ноль или ноль на число

Усложним нашу формулу функцией =ИЛИ(). Добавим еще одного торгового агента с нулевым показателем в продажах. Теперь формулу следует изменить на:

Скопируйте эту формулу во все ячейки столбца «Выполнение»:


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

Данная функция позволяет нам расширить возможности первого аргумента с условием во функции ЕСЛИ. Таким образом в ячейке с формулой D5 первый аргумент функции ЕСЛИ теперь следует читать так: «Если значения в ячейках B5 или C5 равно ноль, тогда условие возвращает логическое значение ИСТИНА». Ну а дальше как прочитать остальную часть формулы описано выше.

Читайте также: