Скрыть нулевое значение в Excel (сделать ячейку пустой, если значение равно 0)

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

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

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

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

提示 提示: Когда вы скрываете 0 в ячейке, используя метод, показанный в этом руководстве, он просто скрывает 0, не удаляя его.Хотя ячейки могут казаться пустыми, они по-прежнему содержат 0.Если вы используете эти ячейки (скрытые нули) в любой формуле, эти ячейки будут использоваться в формуле.

Давайте начнем работать над этими методами!

Автоматически скрывать нулевые значения в ячейках

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

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

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

Вот шаги, чтобы скрыть нули во всех ячейках книги Excel:

1. Откройте книгу, в которой вы хотите скрыть все нули
2. Перейдите на вкладку «Файл»
3. 點擊 選項
4. В открывшемся диалоговом окне «Параметры Excel» щелкните параметр «Дополнительно» на левой панели.
5. Прокрутите вниз до раздела «Параметры отображения для этого листа» и выберите лист, на котором вы хотите скрыть нули.
6. Снимите флажок «Отображать нули в ячейках с нулевыми значениями»
7. Нажмите ОК

Вышеуказанные шаги скроют нули сразу во всех ячейках на выбранном листе.

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

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

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

Скрыть нулевые значения в ячейках с помощью условного форматирования

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

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

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

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

Вот шаги, чтобы скрыть нули в Excel с помощью условного форматирования:

1. Выберите набор данных
2. Щелкните вкладку Главная
3. В группе «Стиль» нажмите «Условное форматирование».
4. Поместите курсор на параметр «Выделить правила ячеек» и выберите параметр «Равно» в отображаемых параметрах.
5. В диалоговом окне «Равно» введите «0» в левом поле.
6. Выберите раскрывающееся меню «Формат» и нажмите «Пользовательский формат».
7. В диалоговом окне «Формат ячеек» выберите вкладку «Шрифт».
8. В раскрывающемся списке Цвет выберите Белый.
9. Щелкните ОК.
10. Щелкните ОК.

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

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

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

1. Выберите набор данных
2. Щелкните вкладку Главная
3. В группе «Стиль» нажмите «Условное форматирование».
4. Поместите курсор на параметр «Выделить правила ячеек» и выберите параметр «Равно» в отображаемых параметрах.
5. В диалоговом окне «Равно» введите 0 в левое поле.
6. Выберите раскрывающееся меню «Формат» и нажмите «Пользовательский формат».
7. В диалоговом окне «Формат ячеек» выберите вкладку «Числа», а затем щелкните параметр «Пользовательский» на левой панели.
8. В параметрах пользовательского форматирования введите следующее в поле «Тип»:;;; (три точки с запятой, без пробелов)
9. Щелкните ОК.
10. Щелкните ОК.

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

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

Вы также можете использовать этот метод, чтобы выделить эти ячейки другим цветом, например красным или желтым.Для этого просто добавьте еще один шаг (после шага 8), где вам нужно выбрать вкладку «Заливка» в диалоговом окне «Формат ячеек» и выбрать цвет, которым вы хотите выделить ячейки.

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

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

Скрыть нулевые значения с помощью пользовательского формата

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

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

1. Выберите весь набор данных, чтобы применить этот формат.Выберите все ячейки, если вы хотите использовать этот параметр для всего листа.
2. Выбрав ячейку, перейдите на вкладку «Главная».
3. В группе «Ячейки» нажмите «Параметры форматирования».
4. В раскрывающемся списке выберите «Формат ячеек».Это откроет диалоговое окно «Формат ячеек».
5. В диалоговом окне «Формат ячеек» выберите вкладку «Числа».
6. В параметрах на левой панели щелкните Пользовательские параметры.
7. В параметрах справа введите следующий формат в поле Тип:0; -0 ;; @
8. Нажмите ОК

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

Как работает этот пользовательский формат?

В Excel можно указать четыре типа форматов данных:

1. Положительное число
2. отрицательное число
3. 零
4. 文本

Для каждого из этих типов данных можно указать формат.

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

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

;;;

В методе, представленном выше, я указал формат как:

0; -0 ;; @

Приведенный выше формат делает следующее:

1. Положительные числа отображаются как есть
2. Отрицательные числа отображаются со знаком минус
3. Нули скрыты (поскольку формат не указан)
4. Текст отображается как есть

Замените нули тире или какой-либо определенный текст

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

Например, вы можете удалить нули и заменить их дефисами или текстом, таким как «недоступно» или «данные еще не доступны».

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

Ниже приведены шаги для преобразования 0 в тире в Excel:

1. Выберите весь набор данных, чтобы применить этот формат.Выберите все ячейки, если вы хотите использовать этот параметр для всего листа.
2. Выбрав ячейку, перейдите на вкладку «Главная».
3. В группе «Ячейки» нажмите «Параметры форматирования».
4. В раскрывающемся списке выберите «Формат ячеек».Это откроет диалоговое окно «Формат ячеек».
5. В диалоговом окне «Формат ячеек» выберите вкладку «Числа».
6. В параметрах на левой панели щелкните Пользовательские параметры.
7. В параметрах справа введите следующий формат в поле Тип:0;-0;–;@
8. Нажмите ОК

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

注意: В этом примере я решил заменить нули тире, но вы можете использовать любой текст.Например, если вы хотите отобразить NA в ячейке, вы можете использовать следующий формат:0;-0;"Н/Д";@

Советы профессионалов: Вы также можете указать цвет текста в пользовательском числовом формате.Например, если вы хотите, чтобы в ячейках с нулями текст NA отображался красным цветом, вы можете использовать следующий формат:0;-0;[Красный]"Н/Д";@

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

Есть два возможных сценария, в которых сводная таблица отображает значение как 0:

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

Давайте посмотрим, как скрыть нули в сводной таблице в каждом конкретном случае:

Когда исходная ячейка данных равна 0

Ниже приведен пример набора данных, который я использовал для создания сводной таблицы.Обратите внимание, что все записанные значения количества/дохода/прибыли для «Запада» равны 0.

Сводная таблица, созданная с помощью этого набора данных, выглядит следующим образом:

Поскольку все записи в западном регионе имеют 0 в наборе данных, вы увидите здесь 0 в западной сводной таблице.

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

Однако условное форматирование в сводных таблицах Excel работает немного иначе, чем обычные данные.

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

1. Выберите любую ячейку в столбце, который содержит 0 в сводке сводной таблицы (не выбирайте все ячейки, а только одну)
2. Щелкните вкладку Главная
3. В группе «Стиль» нажмите «Условное форматирование».
4. Поместите курсор на параметр «Выделить правила ячеек» и выберите параметр «Равно» в отображаемых параметрах.
5. В диалоговом окне «Равно» введите 0 в левое поле.
6. Выберите раскрывающееся меню «Формат» и нажмите «Пользовательский формат».
7. В диалоговом окне «Формат ячеек» выберите вкладку «Числа», а затем щелкните параметр «Пользовательский» на левой панели.
8. В параметрах пользовательского форматирования введите следующее в поле «Тип»:;;; (три точки с запятой, без пробелов)
9. Щелкните ОК.Вы увидите небольшие значки параметров форматирования справа от выбранных ячеек.
10. Щелкните этот значок Параметры форматирования
11. Среди доступных вариантов выберите – Все ячейки, отображающие значение суммы дохода для области.

Приведенные выше шаги скрывают нули в сводной таблице, если вы измените структуру сводной таблицы (добавив к ней больше заголовков строк/столбцов, нули будут по-прежнему скрыты).

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

Когда в ячейке исходных данных есть пустые ячейки

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

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

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

1. Щелкните правой кнопкой мыши любую ячейку в сводной таблице.
2. Нажмите «Параметры сводной таблицы».
3. В диалоговом окне «Параметры сводной таблицы» перейдите на вкладку «Макет и форматирование».
4. В параметрах форматирования установите флажок и «Отображать: для пустых ячеек» и оставьте поле пустым.
5. Щелкните ОК.

Приведенные выше шаги скроют нули в сводной таблице и отобразят пустую ячейку.

Вы можете указать на шаге 0, хотите ли вы, чтобы сводная таблица показывала что-то вместо 4.Например, если вы хотите отобразить текст — «Данные недоступны», вместо 0, в поле (показано ниже).

Найти и удалить нули в Excel

Во всех приведенных выше методах я показал вам, как скрыть значение 0 в ячейке, но значение остается в ячейке.

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

1. Выберите набор данных (или весь лист)
2. Щелкните вкладку Главная
3. В группе «Редактирование» нажмите «Найти и выбрать».
4. Из вариантов, которые появляются в раскрывающемся списке, нажмите «Заменить».
5. В диалоговом окне «Найти и заменить» внесите следующие изменения:
   • В поле Найти введите 0
   • В поле «Заменить на» ничего не вводите (оставьте поле пустым)
   • Нажмите кнопку «Параметры» и установите флажок «Соответствовать всему содержимому ячейки».
6. нажмите заменить все

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

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

1. Выберите набор данных (или весь лист)
2. Щелкните вкладку Главная
3. В группе «Редактирование» нажмите «Найти и выбрать».
4. Из вариантов, которые появляются в раскрывающемся списке, нажмите «Заменить».
5. В диалоговом окне «Найти и заменить» внесите следующие изменения:
   • В поле Найти введите 0
   • В поле «Заменить на» ничего не вводите (оставьте поле пустым)
   • Нажмите кнопку «Параметры» и установите флажок «Соответствовать всему содержимому ячейки».
6. Нажмите кнопку «Найти все».Это даст вам список всех ячеек, содержащих 0
7. Нажмите Control-A (удерживайте нажатой клавишу управления и нажмите A).Это выберет все ячейки с 0 в них.

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

скрыть ноль удалить нули

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

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

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

Например, вот что я получаю, когда использую формулу СРЗНАЧ для получения среднего значения двух столбцов (результат в строке 14).Значения 0 в ячейках столбца A были скрыты (но все еще присутствуют в ячейках), а значения 0 в ячейках столбца B удалены.

Хотя наборы данных для обоих наборов данных выглядят одинаково, вы можете увидеть разницу в результатах.

Это связано с тем, что формула СРЗНАЧ игнорирует пустые ячейки в столбце B, но использует ячейки в столбце A (поскольку ячейки в столбце A по-прежнему имеют значение 0).