Как заменить n d на 0 в Excel

Обновлено: 20.11.2024

Формулы

Преобразовано в

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

Заменить все значения ошибок в выбранном диапазоне пустым, нулевым или определенным текстом в Excel:

Шаг 1. Выберите диапазон, с которым вы будете работать.

Шаг 2. Нажмите клавишу F5, чтобы открыть диалоговое окно "Перейти".

Шаг 3. Нажмите кнопку "Специальный". Откроется диалоговое окно "Перейти к специальному".

Шаг 4. В диалоговом окне «Перейти к специальному» отметьте только параметры «Формула» и «Ошибки», см. снимок экрана:

Шаг 6. Теперь просто введите 0 или любое другое значение, которое необходимо для замены ошибок, и нажмите клавиши Ctrl + Enter. Затем вы получите, что все выбранные ячейки ошибок заполнены 0.

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

Если у вас установлен Kutools for Excel, его инструмент «Мастер условий ошибок» упростит вашу работу, заменив все виды ошибок формул на 0, пустые ячейки или любые пользовательские сообщения.

Перед применением Kutools for Excel сначала загрузите и установите его.

1: выберите диапазон со значениями ошибок, которые вы хотите заменить нулем, пробелом или текстом по мере необходимости, затем нажмите Kutools > Еще > Мастер условий ошибки. Смотрите скриншот:

<р>2. В диалоговом окне Мастер условий ошибки выполните следующие действия:

(2) В разделе "Отображение ошибок", если вы хотите заменить все значения ошибок пробелами, установите флажок Ничего (пустая ячейка).

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

(3) Нажмите кнопку ОК.

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

Заменить все значения ошибок пустыми

Заменить все значения ошибок на ноль

Заменить все значения ошибок определенным текстом

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

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

Как заменить нули пробелами в Excel

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

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

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

С этим пониманием давайте продолжим и рассмотрим шаги по замене нулей пустым, тире или текстовым полем в Excel.

1. Заменить нули пробелом в Excel

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

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

<р>2. Перейдите на вкладку «Главная» > нажмите «Найти и выбрать» в разделе «Редактирование» и выберите параметр «Заменить» в раскрывающемся меню.

<р>3. В диалоговом окне «Найти и заменить» введите 0 в поле «Найти» > оставьте поле «Заменить на» пустым (ничего не вводите в него) и нажмите «Параметры» .

<р>4. В расширенном окне «Найти и заменить» выберите параметр «Соответствовать всему содержимому ячейки» и нажмите кнопку «Заменить все».

После того как вы нажмете кнопку "Заменить все", все ячейки в ваших данных, содержащие нули, немедленно станут пустыми.

2. Заменить нули на тире в Excel

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

<р>1. Выберите все данные, в которых вы хотите заменить нули тире.

<р>2. Перейдите на вкладку «Главная»> выберите параметр «Формат» в группе «Ячейки»> нажмите «Формат ячеек…» в раскрывающемся меню.

<р>3. В диалоговом окне «Формат ячеек» щелкните вкладку «Число» и выберите параметр «Пользовательский» на левой панели. На правой панели введите 0;-0;"–" в поле "Тип".

<р>4. Нажмите OK, чтобы сохранить и применить это изменение.

После того, как вы нажмете «ОК», в ячейках со значениями 0 будет отображаться прочерк (–). Кроме того, если вы введете ноль в любую ячейку, он будет заменен тире (-).

3. Заменить нули текстом в Excel

Вы также можете заменить нули в любом текстовом поле в Excel. Например, вы можете заменить нули на «Н/Д», «Недоступно» или любой другой текст.

<р>1. Выберите все данные, в которых вы хотите заменить нули текстом.

<р>3. В диалоговом окне «Формат ячеек» щелкните вкладку «Число» и выберите параметр «Пользовательский» на левой панели. На правой панели введите 0;-0;[Red]"NA" в поле "Тип".

<р>4. Нажмите OK, чтобы сохранить и применить это изменение.

После нажатия кнопки "ОК" во всех ячейках со значениями 0 будет отображаться текст "Н/П" красного цвета.

Примечание. Если вы хотите отобразить "NA" обычным цветом, просто введите 0;-0;"NA" .

4. Поиск нулей в Excel

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

<р>1. Выберите все данные, имеющие нулевые значения

<р>4.В расширенном окне «Найти и заменить» выберите параметр «Соответствовать всему содержимому ячейки» и нажмите кнопку «Найти все».

<р>5. Как только вы нажмете «Найти все», ячейки с нулевыми значениями будут выделены.

<р>6. Удерживая нажатой клавишу Control, нажмите клавишу A, чтобы выбрать все ячейки, в которых есть 0.

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

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

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

Скрыть или отобразить все нулевые значения на листе

Нажмите «Файл» > «Параметры» > «Дополнительно».

В разделе "Параметры отображения для этого листа" выберите лист и выполните одно из следующих действий:

Чтобы отображать нулевые (0) значения в ячейках, установите флажок Отображать ноль в ячейках с нулевым значением.

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

Скрыть нулевые значения в выбранных ячейках

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

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

Можно нажать Ctrl+1 или на вкладке "Главная" нажать "Формат" > "Форматировать ячейки".

Нажмите "Число" > "Пользовательский".

В поле "Тип" введите 0;-0;;@ и нажмите кнопку "ОК".

Чтобы отобразить скрытые значения:

Выберите ячейки со скрытыми нулями.

Можно нажать Ctrl+1 или на вкладке "Главная" нажать "Формат" > "Форматировать ячейки".

Нажмите «Число» > «Общие», чтобы применить числовой формат по умолчанию, а затем нажмите «ОК».

Скрыть нулевые значения, возвращаемые формулой

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

На вкладке "Главная" нажмите стрелку рядом с пунктом "Условное форматирование" > "Правила выделения ячеек равны".

В поле слева введите 0.

В поле справа выберите Пользовательский формат.

В поле "Формат ячеек" перейдите на вкладку "Шрифт".

В поле "Цвет" выберите белый и нажмите "ОК".

Отображать нули как пробелы или тире

Для этого используйте функцию ЕСЛИ.

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

Вот как читать формулу. Если 0 является результатом (A2-A3), не отображать 0 — не отображать ничего (обозначается двойными кавычками «»). Если это не так, отобразите результат A2-A3. Если вы не хотите, чтобы ячейки были пустыми, но хотите отобразить что-то отличное от 0, поместите тире «-» или другой символ между двойными кавычками.

Скрыть нулевые значения в отчете сводной таблицы

Нажмите на отчет сводной таблицы.

На вкладке "Анализ" в группе "Сводная таблица" щелкните стрелку рядом с пунктом "Параметры" и выберите "Параметры".

Перейдите на вкладку "Макет и формат" и выполните одно или несколько из следующих действий:

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

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

Отображать или скрывать все нулевые значения на листе

Нажмите «Файл» > «Параметры» > «Дополнительно».

В разделе "Параметры отображения для этого листа" выберите лист и выполните одно из следующих действий:

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

Использовать числовой формат, чтобы скрыть нулевые значения в выбранных ячейках

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

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

Можно использовать Ctrl+1 или на вкладке "Главная" нажать "Формат" > "Форматировать ячейки".

В списке "Категория" нажмите "Пользовательская".

В поле Тип введите 0;-0;;@

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

Чтобы снова отобразить скрытые значения, выделите ячейки, а затем нажмите клавиши CTRL+1 или на вкладке Главная в группе Ячейки выберите Формат и щелкните Формат ячеек. В списке Категория щелкните Общие, чтобы применить числовой формат по умолчанию. Чтобы повторно отобразить дату или время, выберите соответствующий формат даты или времени на вкладке Число.

Используйте условный формат, чтобы скрыть нулевые значения, возвращаемые формулой

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

На вкладке "Главная" в группе "Стили" нажмите стрелку рядом с полем "Условное форматирование", выберите "Правила выделения ячеек" и нажмите "Равно".

В поле слева введите 0.

В поле справа выберите Пользовательский формат.

В диалоговом окне "Формат ячеек" перейдите на вкладку "Шрифт".

В поле "Цвет" выберите белый.

Используйте формулу для отображения нулей в виде пробелов или тире

Для выполнения этой задачи используйте функцию ЕСЛИ.

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

Описание (результат)

Второе число вычитается из первого (0)

Возвращает пустую ячейку, если значение равно нулю (пустая ячейка)

Возвращает дефис, если значение равно нулю (-)

Дополнительную информацию об использовании этой функции см. в разделе Функция ЕСЛИ.

Скрыть нулевые значения в отчете сводной таблицы

Нажмите на отчет сводной таблицы.

На вкладке "Параметры" в группе "Параметры сводной таблицы" щелкните стрелку рядом с параметром "Параметры" и выберите "Параметры".

Перейдите на вкладку "Макет и формат" и выполните одно или несколько из следующих действий:

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

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

Отображать или скрывать все нулевые значения на листе

Нажмите кнопку Microsoft Office , выберите «Параметры Excel» и выберите категорию «Дополнительно».

В разделе "Параметры отображения для этого листа" выберите лист и выполните одно из следующих действий:

Использовать числовой формат, чтобы скрыть нулевые значения в выбранных ячейках

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

Можно нажать Ctrl+1 или на вкладке "Главная" в группе "Ячейки" нажать "Формат" > "Форматировать ячейки".

В списке "Категория" нажмите "Пользовательская".

В поле Тип введите 0;-0;;@

Чтобы снова отобразить скрытые значения, выберите ячейки, а затем на вкладке "Главная" в группе "Ячейки" выберите "Формат" и щелкните "Формат ячеек". В списке Категория щелкните Общие, чтобы применить числовой формат по умолчанию. Чтобы повторно отобразить дату или время, выберите соответствующий формат даты или времени на вкладке Число.

Используйте условный формат, чтобы скрыть нулевые значения, возвращаемые формулой

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

На вкладке "Главная" в группе "Стили" нажмите стрелку рядом с пунктом "Условное форматирование" > "Правила выделения ячеек" > "Равно".

В поле слева введите 0.

В поле справа выберите Пользовательский формат.

В диалоговом окне "Формат ячеек" перейдите на вкладку "Шрифт".

В поле "Цвет" выберите белый.

Используйте формулу для отображения нулей в виде пробелов или тире

Для выполнения этой задачи используйте функцию ЕСЛИ.

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

Как скопировать пример?

Выберите пример из этой статьи.

Важно! Не выделяйте заголовки строк или столбцов.

Выбор примера из справки

В Excel создайте пустую книгу или лист.

На листе выберите ячейку A1 и нажмите CTRL+V.

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

Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих результаты, нажмите CTRL+` (ударение) или перейдите на вкладку "Формулы" > в группе "Аудит формул" > "Показать формулы".

После того, как вы скопируете пример на пустой лист, вы можете адаптировать его под свои нужды.

Быстрое заполнение пробелов нулями или другими значениями в таблицах Excel (0, -, N/A, Null или другой текст)

От группы обучения Avantix | Обновлено 7 августа 2021 г.

Применимо к: Microsoft® Excel® 2010, 2013, 2016, 2019 и 365 (Windows)

Вы можете быстро заменить пустые ячейки в Excel нулями, тире или другими числовыми или текстовыми значениями (0, -, N/A, Null или другим текстом). Полезно заполнять пустые ячейки числом, символом или значением, если вы хотите использовать набор данных в качестве источника для сводной таблицы или использовать другие инструменты анализа данных.

Хотите узнать больше об Excel? Посетите наш виртуальный класс или онлайн-курсы Excel >

Ниже показано диалоговое окно «Перейти к специальному» в Excel:

Чтобы быстро заменить пробелы в диапазоне Excel и заполнить нулями, тире или другими значениями:

Выделите диапазон ячеек с пустыми ячейками, которые вы хотите заменить.
Нажмите Ctrl + G, чтобы открыть диалоговое окно «Перейти», а затем нажмите «Специальный», чтобы открыть диалоговое окно «Перейти к специальному». Кроме того, вы можете щелкнуть вкладку "Главная" на ленте, а затем выбрать "Перейти к специальному" в раскрывающемся меню "Найти и выбрать".
Выберите «Пробелы» в диалоговом окне «Перейти к специальному» и нажмите «ОК». Excel выберет все пустые ячейки в диапазоне.
Введите значение, которое вы хотите ввести в пустые поля (например, 0, – или текст). Значение будет введено в активную ячейку. Вы также можете ввести значение в строке формул.
Нажмите Ctrl + Enter. Ноль, тире или другое значение будет вставлено во все выбранные пустые ячейки.

Эта статья была впервые опубликована 11 декабря 2020 г. и была обновлена для большей ясности и содержания.

Подпишитесь, чтобы получать больше статей, подобных этой

Была ли эта статья полезной для вас? Если вы хотите получать новые статьи, присоединяйтесь к нашему списку адресов электронной почты.

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