Excel если ноль, то ноль

Обновлено: 02.07.2024

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

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

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

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

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

Это руководство охватывает:

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

Давайте приступим к изучению каждого из этих методов!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как работает этот специальный формат?

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

Положительные числа
Отрицательные числа
Нали
Текст

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

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

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

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

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

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

Совет для профессионалов. Если вы хотите, чтобы отрицательные числа отображались в наборе данных красным цветом, используйте следующий формат: 0;[Red]0;0;@

Заменить нули тире или другим текстом

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

Например, вы можете удалить нули и заменить их тире или текстом, например "Недоступно" или "Данные еще не получены"

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

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

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

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

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

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

Может быть два сценария, когда сводная таблица показывает значение как 0:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Скрытие нулей Vs. Удаление нулей

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

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

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

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

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

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

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

Изображение: iStock/muchomor

Windows: обязательно к прочтению

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

Я использую Microsoft 365 в 64-разрядной системе Windows 10, но вы можете использовать более простые версии. Вы можете работать со своими данными или скачать демонстрационный файл .xlxs. Браузер правильно отобразит результаты. Последний метод — единственный, который можно реализовать в браузере.

Как скрыть нули во всем листе Excel

Самый простой способ подавить нули — это простая настройка с результатом «все или ничего». Это одновременно и плюс, и минус, в зависимости от ваших потребностей. Помните, что этот параметр работает для всего листа:

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

На рис. Б слева показаны результаты снятия флажка с этой опции; вы можете видеть, где нули, выражения, оцениваемые как ноль, и пробелы были для сравнения. (Столбец D содержит простое выражение =B3+C3, а три ячейки пусты.) Просто помните, что весь лист содержит нуль. Попробуйте ввести 0 или выражение, которое возвращает ноль в пустой ячейке. Вы можете ввести его, и вы можете увидеть его в строке формул, но вы не можете увидеть его в ячейке. Если этот параметр является излишним, вы можете использовать собственный формат.

Рисунок А

Рисунок Б

Как подавить диапазон в Excel

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

Прежде чем мы продолжим, нам нужно рассмотреть структуру или синтаксис пользовательского формата:

Самый простой пользовательский формат, подавляющий ноль:

Нули являются заполнителями и будут отображать любое положительное или отрицательное значение. Оставление третьего компонента, нулевого компонента, пустым — это то, что подавляет нули. @ — это текстовый заполнитель, аналогичный заполнителю 0. Теперь давайте применим этот пользовательский формат и посмотрим, что произойдет:

Выберите диапазон, который хотите отформатировать. В данном случае это B3:D7.
Нажмите кнопку "Дополнительно" в группе номеров (на вкладке "Главная").
В списке "Категория" нажмите "Пользовательская" (внизу).
Введите 0;0;;@ в поле «Тип», как показано на рисунке C.
Нажмите "ОК", чтобы закрыть диалоговое окно.

Рисунок C

Сначала казалось, что все работает нормально, но сравните отформатированные результаты на рисунке D с исходными данными на рисунке B. Мы потеряли символ валюты и десятичные разряды. (Если вы удалите символ @, формат также удалит весь текст.)

Рисунок D

Хорошая новость заключается в том, что мы можем легко изменить пользовательский формат, чтобы он соответствовал нашим потребностям. В частности, повторите попытку, используя формат $0.00;$0.00;;@. На рисунке E вы можете видеть, что второй формат поддерживает символ валюты и десятичные разряды. Если пользовательский формат вам не подходит, вы можете использовать формулу.

Рисунок Д

Как скрыть ноль в ячейке Excel

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

Наше простое выражение в столбце D имеет вид =B3+C3, и два из них (D5 и D7) возвращают 0. Выберите ячейку D3 и введите выражение

и скопируйте в D4:D7. На рисунке F показаны результаты; D5 и D7 пусты. Функция ЕСЛИ() оценивает действительное выражение, которое суммирует два значения, и возвращает пустую строку (""), когда результат равен 0. Если это не 0, ЕСЛИ() возвращает ненулевой результат. Большая разница между этим решением и первыми двумя заключается в том, что вы будете использовать его только с выражениями, а не со значениями.

Рисунок F

Оставайтесь с нами

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

Еженедельный информационный бюллетень Microsoft

Будьте инсайдером Microsoft в своей компании, прочитав эти советы, рекомендации и памятки по Windows и Office.

Доставка по понедельникам и средам

Опубликовано: 18 ноября 2020 г., 8:19 по тихоокеанскому стандартному времени. Изменено: 17 ноября 2020 г., 12:22 по тихоокеанскому стандартному времени. Подробнее о программном обеспечении

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

Скачать рабочую тетрадь

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

4 простых способа выполнить формулу If Zero Leave Blank в Excel

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

1. Вставьте функцию ЕСЛИ для выполнения, если ноль оставить пустым

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

Шаг 1:

Во-первых, мы узнаем текущий запас нашей продукции. Для этого мы воспользуемся этой простой формулой.

Шаг 2:

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

Где logical_test — C4-D4=0
Функция оставит пустую ячейку, если значение истинно.
В противном случае будут отображаться цифры.

Получите результат, нажав ENTER. Переместите курсор в правый нижний угол ячейки, пока не появится этот знак (+). Затем дважды щелкните знак, чтобы применить ту же функцию к остальным ячейкам. Из результата видно, что функция if оставила пустую ячейку, в которой значение равно нулю.

2. Применить пользовательское форматирование для выполнения, если ноль оставить пустым

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

Шаг 1:

На вкладке "Главная" перейдите на ленту с цифрами. Щелкните числовой формат, чтобы открыть доступные параметры. Затем нажмите "Другие числовые форматы".

Шаг 2:

Введите требуемый формат, чтобы оставить поле пустым, если значение ячейки равно нулю. В поле типа введите 0;-0;;@. Нажмите OK, чтобы продолжить. И это избавит вас от нулей.

3. Используйте условное форматирование для выполнения, если ноль оставить пустым

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

Шаг 1:

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

Затем выберите вариант "Равно", чтобы продолжить.

Шаг 2:

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

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

Наконец, ОК, чтобы выполнить свою работу.

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

4. Изменить параметры Excel на «Выполнять, если ноль оставить пустым»

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

Шаг 1:

Выберите столбец, затем перейдите в «Файл», чтобы открыть «Параметры».

Чтобы продолжить, нажмите "Параметры".

Шаг 2:

Теперь найдите раздел «Дополнительные параметры» и нажмите на него. Прокрутите вниз, пока не найдете Параметры отображения для этого рабочего листа.

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

Что нужно помнить

👉 Вы можете вставить цветную ячейку вместо пустой ячейки с помощью условного форматирования

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

Заключение

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

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

Вариант 1. Не отображать нулевые значения

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

Существует три метода скрытия нулевых значений.

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

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

Небольшой совет от себя лично: в моей надстройке для Excel «Professor Excel Tools» есть встроенный менеджер компоновки. Благодаря этому вы можете легко применить это форматирование к полной книге Excel.
Попробовать? Нажмите здесь, и загрузка начнется сразу же.

Вариант 2. Замените нули пустыми ячейками

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

Скажем, ваша формула поиска выглядит так: =ВПР(A3,C:D,2,FALSE) (далее именуемая «исходная формула»). Вы хотите предотвратить получение нуля, даже если возвращаемое значение, найденное формулой ВПР в столбце D, является пустым значением. Этого можно добиться с помощью формулы ЕСЛИ.

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

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