Как заменить 0 на пустую ячейку в excel
Перейти к содержимому

Как заменить 0 на пустую ячейку в excel

  • автор:

Как заменить пустые ячейки нулем в Excel

Как заменить пустые ячейки нулем в Excel

Самый простой способ заменить пустые ячейки нулями в Excel — использовать функцию « Перейти к специальному» .

В следующем примере показано, как использовать эту функцию на практике.

Пример: заменить пустые ячейки нулем в Excel

Предположим, у нас есть следующий набор данных, который показывает очки, набранные различными баскетбольными командами:

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

Для этого нажмите Ctrl+G , чтобы открыть окно « Перейти» :

Затем нажмите кнопку Special в нижнем левом углу окна Go To .

В появившемся новом окне выберите « Пробелы » и нажмите « ОК »:

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

Все пустые значения в столбце Points будут автоматически выделены:

Наконец, введите значение 0 в строке формул и нажмите Ctrl+Enter .

Каждая из пустых ячеек в столбце Баллы будет автоматически заменена нулями.

Примечание.Важно нажать Ctrl+Enter после ввода нуля, чтобы каждая пустая ячейка была заполнена нулем, а не только первая выбранная пустая ячейка.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Excel: как вернуть пустую ячейку вместо нуля в формулах

Excel: как вернуть пустую ячейку вместо нуля в формулах

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

=IF( B2 / C2 =0, "", B2 / C2 ) 

Эта конкретная формула пытается разделить значение в ячейке B2 на значение в ячейке C2 .

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

В следующем примере показано, как использовать эту формулу на практике.

Пример: вернуть пустую ячейку вместо нуля в формуле Excel

Предположим, у нас есть следующий набор данных, который показывает общий объем продаж и возвратов для различных продуктов:

Предположим, мы вводим следующую формулу в ячейку D2 , чтобы рассчитать коэффициент возврата для продукта A:

= B2 / C2 

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

Чтобы вернуть пустое значение вместо нуля, мы можем ввести следующую формулу в ячейку D2 :

=IF( B2 / C2 =0, "", B2 / C2 ) 

Затем мы можем скопировать и вставить эту формулу в каждую оставшуюся ячейку в столбце D:

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

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

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

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

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

В более новых версиях Office, таких как Office 2013, Office 2016, Office 2019, Office 2021 и подписка Office 365, иногда вы не хотите, чтобы на листах отображались нулевые значения (0). Иногда они должны отображаться. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.

Скрытие и отображение всех нулевых значений на листе

  1. Откройте меню Файл, выберите раздел Параметры, а затем — Дополнительно.
  2. В группе Показать параметры для следующего листа выберите лист, после чего выполните одно из указанных ниже действий.
    • Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.
    • Чтобы отображать нулевые значения в виде пустых ячеек, снимите флажок Показывать нули в ячейках, которые содержат нулевые значения.

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

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

Кнопка

  1. Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.
  2. Можно нажать клавиши CTRL+1 или на вкладке Главная выбрать Формат >формат ячеек.

Отображение скрытых значений

Отображение скрытых значений.

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

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

  1. Выделите ячейку, содержащую нулевое (0) значение.
  2. На вкладке Главная щелкните стрелку рядом с элементом Условное форматирование > выделить правила ячеек, равные.
  3. В левом поле введите 0.
  4. В правом поле выберите Пользовательский формат.
  5. В поле Формат ячеек выберите вкладку Шрифт .
  6. В поле Цвет выберите белый, а затем нажмите кнопку ОК.

Отображение нулей в виде пробелов или тире

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

Данные в ячейках A2 и A3 на листе Excel

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

  • =ЕСЛИ(A2-A3=0;»»;A2-A3)

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

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

  1. Выберите отчет сводной таблицы.
  2. На вкладке Анализ в группе Сводная таблица щелкните стрелку рядом с элементом Параметры, а затем выберите Параметры.
  3. Выберите вкладку Макет & Формат , а затем выполните одно или несколько из следующих действий:
    • Отображение ошибки изменения — В поле Формат установите флажок Для ошибок отображать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
    • Изменение отображения пустой ячейки — Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

Использование функции ЕСЛИ для проверки ячейки на наличие символов

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

Ячейка E2 содержит формулу =ЕСЛИ(D2=1;

В данном случае мы используем ЕСЛИ вместе с функцией ЕПУСТО:

  • =ЕСЛИ(ЕПУСТО(D2);»Пустая»;»Не пустая»)

Эта формула означает: ЕСЛИ(ячейка D2 пуста, вернуть текст «Пустая», в противном случае вернуть текст «Не пустая»). Вы также можете легко использовать собственную формулу для состояния «Не пустая». В следующем примере вместо функции ЕПУСТО используются знаки «». «» — фактически означает «ничего».

Проверка пустоты ячейки — ячейка E2 содержит формулу =ЕСЛИ(ЕПУСТО(D2);

Эта формула означает: ЕСЛИ(в ячейке D3 ничего нет, вернуть текст «Пустая», в противном случае вернуть текст «Не пустая»). Вот пример распространенного способа использования знаков «», при котором формула не вычисляется, если зависимая ячейка пуста:

  • =ЕСЛИ(D3=»»;»»;ВашаФормула()) ЕСЛИ(в ячейке D3 ничего нет, не возвращать ничего, в противном случае вычислить формулу).

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *