Если 0, то пустая ячейка Excel

Обновлено: 07.07.2024

Я пытаюсь создать простую бухгалтерскую книгу, и в крайнем правом углу «Книги» она суммирует все дебетовые/кредитовые данные, которые я ввожу. Но вместо того, чтобы оставлять неиспользуемые строки пустыми, последняя сумма повторяется в нижней части страницы.

Как я могу сделать эту ячейку пустой, если уравнение равно 0?

это формула, которую я сейчас использую.

7 ответов 7

Вы можете изменить числовой формат столбца на этот пользовательский формат:

что скроет все значения 0.

Для этого выберите столбец, щелкните правой кнопкой мыши > Формат ячеек > Пользовательский.

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

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

Это недостаток, если вы хотите построить диаграмму и игнорировать эти значения… они всегда будут считаться равными 0… :(

=iferror(1/ (1/ H15+G16-F16), "")

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

Я думал, что должен быть способ построить что-то подобное, но я не приходил в голову, пока не увидел этот пост. Спасибо. У меня так много формул If(тарабарщина=0,"",тарабарщина), которые я хочу исправить. Я думаю, что это может работать лучше, как =iferror(1/ (1/(H15+G16-F16)), "")

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

Столбец H — это ваша общая сумма.
Вы помещаете эту формулу в H16.
Столбец G — это дополнения к сумме.
Столбец F – выводы из вашего суммирования.
Вы хотите оставить ячейку суммирования пустой, если не введен дебет или кредит

Ответ будет таким:

СЧИТАТЬПУСТОТЫ показывает, сколько ячеек не заполнено или имеет значение "".
ЕСЛИ позволяет условно выполнить одно из двух действий в зависимости от того, является ли первое утверждение истинным или ложным. Второй аргумент, разделенный запятой, — это то, что делать, если он истинен, третий аргумент, разделенный запятой, — что делать, если он ложен.
<> означает "не равно".

Уравнение говорит о том, что если количество пустых ячеек в диапазоне F16:G16 (ваши кредитные и дебетовые ячейки) не равно 2, что означает, что обе они не пусты, то вычислите уравнение, которое вы указали в своем вопросе. В противном случае установите ячейку пустой ("").
Когда вы скопируете это уравнение в новые ячейки в столбце H, отличном от H16, оно обновит ссылки на строки, чтобы были просмотрены правильные строки для сумм кредита и дебета.

ВНИМАНИЕ: Это уравнение полезно, если вы просто добавляете записи для кредитов и дебетов в конец списка и хотите, чтобы промежуточная сумма обновлялась автоматически. Вы бы заполнили это уравнение до произвольной длинной длины далеко за пределы фактических данных. Тогда вы не увидите промежуточную сумму за концом записей кредита/дебета, она будет просто пустой, пока вы не заполните новую запись кредита/дебета. Однако если вы оставили пустую строку в своих кредитно-дебетовых записях, ссылка на предыдущую сумму H15 будет пустой, что в данном случае рассматривается как 0.

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

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

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

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

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

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

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

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

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

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

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

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

Предположим, у нас есть набор данных, в котором указана информация о производстве различных заводов компании. Единица считается готовой к продаже, когда упаковка завершена. Теперь в столбце «Единица, готовая к продаже» (столбец E) мы хотим показать 0, если какая-либо ячейка в столбце «Упаковка единицы» (столбец D) той же строки пуста.

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

4 способа отобразить 0 в Excel, если ячейка пуста

1. Функция ЕСЛИ для отображения 0 в пустой ячейке

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

Чтобы отображать 0 в ячейках столбца E, если какая-либо ячейка столбца D пуста,

➤ Введите следующую формулу в ячейку E6,

Формула покажет 0 в ячейке E6, если ячейка D6 пуста. В противном случае он покажет значение D6 в E6.

➤ Теперь нажмите клавишу ВВОД и перетащите ячейку E6, чтобы применить ту же формулу ко всем остальным ячейкам столбца E.

В результате вы увидите, что в ячейках столбца E отображается 0, если ячейки столбца D той же строки пусты.

2. IПУСТО Функция для отображения 0

Мы также можем использовать функцию ЕПУСТО, чтобы отобразить 0, если другая ячейка пуста.

Чтобы отображать 0 в ячейках столбца E, если какая-либо ячейка столбца D пуста,

➤ Введите следующую формулу в ячейку E6,

Здесь функция ЕПУСТО определяет, является ли ячейка D6 пустой или нет, и если ячейка D6 пуста, формула будет отображать 0 в ячейке E6.

В результате вы увидите, что в ячейках столбца E отображается 0, если ячейки столбца D той же строки пусты.

Похожие чтения:

Найти пустую ячейку в Excel (7 методов)
Как считать в Excel, если ячейки не пусты: 7 примеров формул
Удалить пустые ячейки в Excel (6 способов)
Как удалить пустые строки в Excel (8 простых способов)

3. Замена пустой ячейки на 0 с помощью перехода к специальному

Мы можем заменить все пустые ячейки на 0 с помощью функции "Перейти к специальным".

➤ Сначала выберите свой набор данных и перейдите в раздел «Редактирование» > «Найти и выбрать» > «Перейти к специальному».

После этого появится окно "Перейти к специальному".

➤ Выберите «Пробелы» и нажмите «ОК».

В результате будут выбраны все пустые ячейки.

➤ Теперь введите 0 и нажмите CTRL+ENTER.

В результате все пустые ячейки будут заменены на 0.

4. Отображать 0 в пустых ячейках в параметрах отображения

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

➤ Перейдите на вкладку "Главная" и выберите "Параметры".

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

➤ Наконец, нажмите OK.

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

Заключение

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

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

Иногда в 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).

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