- Автоматически скрывать нулевое значение в ячейках
- Скрыть нулевое значение в ячейках с помощью условного форматирования
- Скрыть нулевое значение с помощью настраиваемого форматирования
- Замените нули тире или определенным текстом
- Скрыть нулевые значения в сводных таблицах
- Найти и удалить нули в Excel
- Скрытие нулей против. Удаление нулей
Посмотреть видео - Как скрыть нулевые значения в Excel + Как удалить нулевые значения
Если вы предпочитаете читать, а не смотреть видео, ниже представлено полное письменное руководство.
Иногда в Excel вы можете захотеть скрыть нулевые значения в вашем наборе данных и покажите эти ячейки как пустые.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите скрыть значение 0 во всех этих ячейках (или хотите заменить его чем-нибудь, например тире или текстом «Недоступно»).
Хотя вы можете сделать это вручную с небольшим набором данных, вам понадобятся лучшие способы при работе с большими наборами данных.
В этом руководстве я покажу вам несколько способов скрыть нулевые значения в Excel и один метод выбора и удаления всех нулевых значений из набора данных.
Важная заметка: Когда вы скрываете 0 в ячейке с помощью методов, показанных в этом руководстве, он только скроет 0, но не удалит его. Хотя ячейки могут выглядеть пустыми, в них все еще есть нули. И если вы используете эти ячейки (со скрытыми нулями) в любой формуле, они будут использоваться в формуле.Давайте начнем и рассмотрим каждый из этих методов!
Автоматически скрывать нулевое значение в ячейках
Excel имеет встроенную функцию, которая позволяет автоматически скрывать все нулевые значения для всего рабочего листа.
Все, что вам нужно сделать, это снять флажок в параметрах Excel, и изменение будет применено ко всему рабочему листу.
Предположим, у вас есть набор данных о продажах, как показано ниже, и вы хотите скрыть все нулевые значения и вместо этого показать пустые ячейки.
Ниже приведены шаги, чтобы скрыть нули во всех ячейках книги в Excel:
- Откройте книгу, в которой вы хотите скрыть все нули
- Перейдите на вкладку Файл.
- Нажмите на Параметры
- В открывшемся диалоговом окне "Параметры Excel" нажмите "Дополнительно" на левой панели.
- Прокрутите вниз до раздела с надписью «Параметры отображения для этого рабочего листа» и выберите рабочий лист, на котором вы хотите скрыть нули.
- Снимите флажок "Показывать ноль в ячейках с нулевым значением".
- Нажмите ОК
Вышеупомянутые шаги мгновенно скроют нули во всех ячейках на выбранном листе.
Это изменение также применяется к ячейкам, в которых ноль является результатом формулы.
Помните, что этот метод скрывает только значение 0 в ячейках и не удаляет их. 0 все еще там, он просто скрыт.
Это лучший и самый быстрый способ скрыть нулевые значения в Excel. Но это полезно только в том случае, если вы хотите скрыть нулевые значения на всем листе. Если вы хотите только скрыть нулевые значения в определенном диапазоне ячеек, лучше использовать другие методы, описанные далее в этом руководстве.Скрыть нулевое значение в ячейках с помощью условного форматирования
Хотя описанный выше метод является наиболее удобным, он не позволяет скрыть нули только в определенном диапазоне (скорее, он скрывает нулевые значения на всем листе).
Если вы хотите скрыть нули только для определенного набора данных (или нескольких наборов данных), вы можете использовать условное форматирование.
С условным форматированием вы можете применить определенный формат на основе значения в ячейках. Итак, если значение в некоторых ячейках равно 0, вы можете просто запрограммировать условное форматирование, чтобы скрыть его (или даже выделить его, если хотите).
Предположим, у вас есть набор данных, показанный ниже, и вы хотите скрыть нули в ячейках.
Ниже приведены шаги, чтобы скрыть нули в Excel с помощью условного форматирования:
- Выберите набор данных
- Перейдите на вкладку "Главная"
- В группе «Стили» нажмите «Условное форматирование».
- Поместите курсор на опции «Highlight Cells Rules» и в появившихся опциях нажмите на опцию «Equal to».
- В диалоговом окне «Равно» введите «0» в левом поле.
- Выберите раскрывающийся список «Формат» и нажмите «Пользовательский формат».
- В диалоговом окне «Формат ячеек» выберите вкладку «Шрифт».
- В раскрывающемся списке «Цвет» выберите белый цвет.
- Щелкните ОК.
- Щелкните ОК.
Вышеупомянутые шаги изменяют цвет шрифта ячеек с нулями на белый, что делает эти ячейки пустыми.
Этот обходной путь работает, только если у вас белый фон на листе. В случае, если в ячейках есть какой-либо другой фоновый цвет, белый цвет может по-прежнему оставлять видимыми нули.
Если вы действительно хотите скрыть нули в ячейках, где ячейки выглядят пустыми, независимо от того, какой цвет фона задан ячейкам, вы можете использовать следующие шаги.
- Выберите набор данных
- Перейдите на вкладку "Главная"
- В группе «Стили» нажмите «Условное форматирование».
- Поместите курсор на опции «Highlight Cells Rules» и в появившихся опциях нажмите на опцию «Equal to».
- В диалоговом окне «Равно» введите 0 в левом поле.
- Выберите раскрывающийся список «Формат» и нажмите «Пользовательский формат».
- В диалоговом окне «Формат ячеек» выберите вкладку «Число» и щелкните параметр «Пользовательский» на левой панели.
- В параметре Пользовательский формат введите следующее в поле Тип: ;;; (три точки с запятой без пробела)
- Щелкните ОК.
- Щелкните ОК.
Приведенные выше шаги изменяют настраиваемое форматирование ячеек со значением 0.
Когда вы используете три точки с запятой в качестве формата, он скрывает все в ячейке (будь то числовые или текстовые). И поскольку этот формат применяется только к тем ячейкам, в которых указано значение 0, он скрывает только нули, а не остальную часть набора данных.
Вы также можете использовать этот метод, чтобы выделить эти ячейки другим цветом (например, красным или желтым). Для этого просто добавьте еще один шаг (после шага 8), где вам нужно выбрать вкладку «Заливка» в диалоговом окне «Формат ячеек», а затем выбрать цвет, которым вы хотите выделить ячейки.Оба описанных выше метода также работают, когда 0 является результатом формулы в ячейке.
Примечание. Оба метода, описанные в этом разделе, скрывают только нулевые значения. Эти нули не удаляются. Это означает, что если вы используете эти ячейки в расчетах, все нули также будут использоваться.Скрыть нулевое значение с помощью настраиваемого форматирования
Другой способ скрыть нулевые значения из набора данных - создать настраиваемый формат, который скрывает значение в ячейках с нулями, в то время как любое другое значение отображается должным образом.
Ниже приведены инструкции по использованию настраиваемого формата для скрытия нулей в ячейках:
- Выберите весь набор данных, к которому вы хотите применить этот формат. Если вы хотите, чтобы это было для всего листа, выберите все ячейки
- Выделив ячейки, перейдите на вкладку "Главная"
- В группе "Ячейки" выберите параметр "Формат".
- В раскрывающемся списке щелкните Формат ячеек. Откроется диалоговое окно Формат ячеек.
- В диалоговом окне «Формат ячеек» выберите вкладку «Число».
- В параметре на левой панели щелкните параметр Пользовательский.
- В параметрах справа введите следующий формат в поле Тип: 0;-0;;@
- Нажмите ОК.
Вышеупомянутые шаги будут применять настраиваемый формат к выбранным ячейкам, где только те ячейки, которые имеют значение 0, отформатированы так, чтобы скрыть значение, в то время как остальные ячейки отображают данные, как ожидалось.
Как работает этот нестандартный формат?
В Excel вы можете указать четыре типа форматов данных:
- Положительные числа
- Отрицательные числа
- Нули
- Текст
И для каждого из этих типов данных вы можете указать формат.
В большинстве случаев существует формат по умолчанию - Общий, который сохраняет все эти типы данных видимыми. Но у вас есть возможность создать определенный формат для каждого из этих типов данных.
Ниже приведена структура настраиваемого формата, которой необходимо следовать, если вы применяете настраиваемый формат к каждому из этих типов данных:
;;;
В описанном выше методе я указал формат:
0;-0;;@
Приведенный выше формат выполняет следующие функции:
- Положительные числа показаны как есть
- Отрицательные числа отображаются со знаком минус
- Нули скрыты (формат не указан)
- Текст отображается как есть
Замените нули тире или определенным текстом
В некоторых случаях вы можете захотеть не просто скрыть нули в ячейках, но заменить их чем-то более значимым.
Например, вы можете удалить нули и заменить их тире или текстом, например «Недоступно» или «Данные еще не поступили».
Это снова стало возможным с помощью настраиваемого форматирования чисел, где вы можете указать, что должна отображать ячейка вместо нуля.
Ниже приведены шаги по преобразованию 0 в тире в Excel:
- Выберите весь набор данных, к которому вы хотите применить этот формат. Если вы хотите, чтобы это было для всего листа, выберите все ячейки
- Выделив ячейки, перейдите на вкладку "Главная"
- В группе "Ячейки" выберите параметр "Формат".
- В раскрывающемся списке щелкните Формат ячеек. Откроется диалоговое окно Формат ячеек.
- В диалоговом окне «Формат ячеек» выберите вкладку «Число».
- В параметре на левой панели щелкните параметр Пользовательский.
- В параметрах справа введите следующий формат в поле Тип: 0;-0;-;@
- Нажмите ОК.
Вышеупомянутые шаги сохранят все остальные ячейки как есть и поменяют ячейки со значением 0, чтобы отобразить тире (-).
Примечание: В этом примере я решил заменить ноль на тире, но вы также можете использовать любой текст. Например, если вы хотите отобразить NA в ячейках, вы можете использовать следующий формат: 0; -0; ”NA”; @Совет профессионала: Вы также можете указать несколько цветов текста в пользовательском форматировании чисел. Например, если вы хотите, чтобы в ячейках с нулями отображался текст NA красным цветом, вы можете использовать следующий формат: 0; -0; [Красный] ”NA”; @Скрыть нулевые значения в сводных таблицах
Возможны два сценария, в которых в сводной таблице значение отображается как 0:
- Ячейки исходных данных, суммированные в сводной таблице, имеют 0 значений.
- Ячейки исходных данных, которые суммированы в сводной таблице, являются пустыми, а сводная таблица была отформатирована так, чтобы пустые ячейки отображались как нулевые.
Давайте посмотрим, как скрыть нули в сводной таблице в каждом сценарии:
Когда в ячейках исходных данных есть нули
Ниже приведен образец набора данных, который я использовал для создания сводной таблицы. Обратите внимание, что числа Количество / Доход / Прибыль для всех записей для «Запад» равны 0.
Сводная таблица, созданная с использованием этого набора данных, выглядит, как показано ниже:
Поскольку в наборе данных для всех записей для западного региона были нули, вы видите здесь 0 в сводной таблице для западного региона.
Если вы хотите скрыть ноль в этой сводной таблице, вы можете использовать для этого условное форматирование.
Однако условное форматирование в сводной таблице в Excel работает немного иначе, чем обычные данные.
Вот шаги, чтобы применить условное форматирование к сводной таблице, чтобы скрыть нули:
- Выберите любую ячейку в столбце, имеющую нули в сводной таблице сводной таблицы (не выбирайте все ячейки, а только одну ячейку)
- Перейдите на вкладку "Главная"
- В группе «Стили» нажмите «Условное форматирование».
- Поместите курсор на опции «Highlight Cells Rules» и в появившихся опциях нажмите на опцию «Equal to».
- В диалоговом окне «Равно» введите 0 в левом поле.
- Выберите раскрывающийся список «Формат» и нажмите «Пользовательский формат».
- В диалоговом окне «Формат ячеек» выберите вкладку «Число» и нажмите «Пользовательский» на левой панели.
- В параметре Пользовательский формат введите следующее в поле Тип: ;;; (три точки с запятой без пробела)
- Щелкните ОК. Вы увидите небольшой значок параметров форматирования справа от выбранной ячейки.
- Щелкните значок «Параметры форматирования».
- В появившихся опциях выберите - Все ячейки, показывающие значения «Сумма дохода» для «Регион».
Вышеупомянутые шаги скрывают ноль в сводной таблице таким образом, что если вы измените структуру сводной таблицы (добавив к ней дополнительных заголовков строк / столбцов, нули будут по-прежнему скрыты).
Если вы хотите узнать больше о том, как это работает, вот подробное руководство о том, как правильно применить условное форматирование к сводным таблицам.
Когда в ячейках исходных данных есть пустые ячейки
Ниже приведен пример, в котором у меня есть набор данных с пустыми ячейками для всех записей для West.
Если я создам сводную таблицу с использованием этих данных, я получу результат, как показано ниже.
По умолчанию в сводной таблице отображаются пустые ячейки, когда все ячейки в исходных данных пусты. Но если вы все еще видите 0 в этом случае, выполните следующие шаги, чтобы скрыть ноль и показать пустые ячейки:
- Щелкните правой кнопкой мыши любую ячейку в сводной таблице.
- Нажмите «Параметры сводной таблицы».
- В диалоговом окне «Параметры сводной таблицы» перейдите на вкладку «Макет и формат».
- В параметрах формата установите флажок «Для пустых ячеек:» и оставьте поле пустым.
- Щелкните ОК.
Приведенные выше шаги скроют нули в сводной таблице и вместо этого покажут пустую ячейку.
Если вы хотите, чтобы в сводной таблице отображалось что-то вместо 0, вы можете указать это на шаге 4. Например, если вы хотите показать текст - «Данные недоступны» вместо 0, введите этот текст в поле поле (как показано ниже).
Найти и удалить нули в Excel
Во всех приведенных выше методах я показал вам способы скрыть значение 0 в ячейке, но значение по-прежнему остается в ячейке.
Если вы хотите удалить нулевые значения из набора данных (в результате чего у вас останутся пустые ячейки), выполните следующие действия:
- Выберите набор данных (или весь лист)
- Перейдите на вкладку "Главная"
- В группе «Редактирование» нажмите «Найти и выбрать».
- В вариантах, показанных в раскрывающемся списке, нажмите «Заменить».
- В диалоговом окне «Найти и заменить» внесите следующие изменения:
- В поле «Найти» введите 0.
- В поле «Заменить на» ничего не вводите (оставьте поле пустым).
- Нажмите кнопку «Параметры» и установите флажок «Соответствовать всему содержимому ячейки».
- Нажмите "Заменить все".
Вышеупомянутые шаги мгновенно заменят все ячейки с нулевым значением пустыми.
Если вы не хотите сразу удалять нули и сначала выбрать ячейки, выполните следующие действия:
- Выберите набор данных (или весь лист)
- Перейдите на вкладку "Главная"
- В группе «Редактирование» нажмите «Найти и выбрать».
- В вариантах, показанных в раскрывающемся списке, нажмите «Заменить».
- В диалоговом окне «Найти и заменить» внесите следующие изменения:
- В поле «Найти» введите 0.
- В поле «Заменить на» ничего не вводите (оставьте поле пустым).
- Нажмите кнопку «Параметры» и установите флажок «Соответствовать всему содержимому ячейки».
- Нажмите кнопку «Найти все». Это даст вам список всех ячеек, в которых есть 0.
- Нажмите Control-A (удерживайте клавишу управления и нажмите A). Это выберет все ячейки, в которых есть 0.
Теперь, когда у вас есть все ячейки с выбранным 0, вы можете удалить их, заменить их чем-то другим или изменить цвет и выделить их.
Скрытие нулей против. Удаление нулей
Обрабатывая ячейки с нулями, вы должны знать разницу между их скрытием и удалением.
Когда вы скрываете нулевое значение в ячейке, оно не отображается для вас, но все равно остается в ячейке. Это означает, что если у вас есть набор данных и вы используете его для вычислений, это значение 0 также будет использоваться в вычислении.
Напротив, если у вас есть пустая ячейка, ее использование в формулах может означать, что Excel автоматически игнорирует эти ячейки (в зависимости от того, какую формулу вы используете).
Например, ниже показано, что я получаю, когда использую формулу СРЕДНЕЕ, чтобы получить среднее значение для двух столбцов (результат находится в строке 14). В столбце A есть ячейки, в которых значение 0 было скрыто (но все еще есть в ячейке), а в столбце B есть ячейки, из которых значение 0 было удалено.
Вы можете увидеть разницу в результате, несмотря на то, что набор данных для обоих наборов данных выглядит одинаково.
Это происходит потому, что формула AVERAGE игнорирует пустые ячейки в столбце B, но использует ячейки в столбце A (поскольку ячейки в столбце A по-прежнему имеют значение 0).
