Нулевые значения в excel 2020

Как убрать нули в ячейках в Excel?

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

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

Обработка таблиц с числовыми данными практически неотъемлемая часть работы в Excel для многих пользователей программы.

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

Обычно в таких случаях нули в таблицах заменяют либо на пустое поле, либо на аналогичные по смыслу символы (к примеру, в виде прочерка).

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

На всем листе (в каждой ячейке);
В конкретных (выделенных) ячейках;
В формулах;
В сводных таблицах.

Как скрыть нулевые значения в Excel?

Предположим, что у нас имеется таблица с числами (большого размера, чтобы наглядно оценить различие во внешнем виде), где в частности есть достаточно много нулей, и на ее примере попытаемся удалить ненулевые значения:

На всем листе

Если нам нужно убрать нули в каждой без исключения ячейке листа, то перейдем в панели вкладок Файл -> Параметры -> Дополнительно (так как эти настройки относятся в целом к работе со всей книгой):

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

Нажимаем OK и в результате исходная таблица приобретает следующий вид:

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

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

Важная деталь.

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

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

Частично эту проблему может решить следующий вариант.

В выделенных ячейках

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

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

В начале давайте вспомним, что любое число в Excel имеет формат отображения в виде маски A;B;C;D, где A, B, C, D — формат записи и точка с запятой, отделяющая их друг от друга:

A — запись когда число положительное;
B — запись когда число отрицательное;
C — запись когда число равно нулю;
D — запись если в ячейке не число, а текст (обычно для чисел не используется).

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

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

Поэтому, чтобы убрать нули из выделенных ячеек, щелкаем по ним правой кнопкой мыши и в контекстном меню выбираем Формат ячеек -> Число, а далее среди форматов переходим во Все форматы:

Затем в маске прописываем формат отображения нуля, вместо него либо ничего не пишем (маска # ##0;- # ##0; чтобы ячейка стала пустой), либо пишем заменяющий символ (маска # ##0;-# ##0;»-«, чтобы в ячейке стоял прочерк), и нажимаем OK.

В результате получаем:

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

В формулах

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

Давайте для исходной таблицы пропишем отклонение между периодами с помощью функции ЕСЛИ.
Вместо стандартной формулы =A1-B1 пропишем =ЕСЛИ(A1-B1=0;»-«;A1-B1):

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

В сводных таблицах

Для начала пусть у нас имеется простая и небольшая таблица с данными (при этом в таблице не все ячейки будут заполнены), на основе которой мы построим сводную таблицу:

Задача удаления нулей из сводной таблицы можно условно разделить на 2 направления:

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

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

С помощью создания фильтров;
С помощью настройки параметров сводной таблицы.

Читать еще: Календарь excel скачать

Рассмотрим оба варианта.

Создание и применение фильтра

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

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

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

Перейдем к следующему варианту.

Настройка параметров сводной таблицы

С помощью настройки параметров сводной таблицы мы также можем удалить нули из ячеек. Во вкладке Работа со сводными таблицами выбираем Анализ -> Сводная таблица -> Параметры -> Макет и формат:

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

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

Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно спрашивайте и пишите в комментариях, не стесняйтесь.

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

В этом курсе:

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

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

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

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

Выберите Файл > Параметры > Дополнительно.

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

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

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

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

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

Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.

Щелкните Число > Все форматы.

В поле Тип введите выражение 0;-0;;@ и нажмите кнопку ОК.

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

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

Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.

Для применения числового формата, определенного по умолчанию, выберите Число > Общий и нажмите кнопку ОК.

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

Выделите ячейку, содержащую нулевое (0) значение.

На вкладке Главная щелкните стрелку рядом с кнопкой Условное форматирование и выберите «Правила выделения ячеек» > «Равно».

В левом поле введите 0.

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

В поле Формат ячейки откройте вкладку Шрифт.

В списке Цвет выберите белый цвет и нажмите кнопку ОК.

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

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

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

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

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

Выберите отчет сводной таблицы.

На вкладке Анализ в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.

Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.

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

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

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

Читать еще: Формула возведения в степень в excel

Выберите Файл > Параметры > Дополнительно.

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

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

Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.

В списке Категория выберите элемент Пользовательский.

В поле Тип введите 0;-0;;@

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

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

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

Выделите ячейку, содержащую нулевое (0) значение.

На вкладке Главная в группе Стили щелкните стрелку рядом с элементом Условное форматирование, наведите указатель на элемент Правила выделения ячеек и выберите вариант Равно.

В левом поле введите 0.

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

В диалоговом окне Формат ячеек откройте вкладку Шрифт.

В поле Цвет выберите белый цвет.

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

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

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Второе число вычитается из первого (0).

Возвращает пустую ячейку, если значение равно нулю

Возвращает дефис (-), если значение равно нулю

Дополнительные сведения об использовании этой функции см. в статье Функция ЕСЛИ.

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

Щелкните отчет сводной таблицы.

На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.

Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.

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

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

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

Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию Дополнительно .

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

Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

Вы можете нажать клавиши CTRL+1 или на вкладке Главная в группе Ячейки щелкнуть Формат > Формат ячеек.

В списке Категория выберите элемент Пользовательский.

В поле Тип введите 0;-0;;@

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

Чтобы снова отобразить скрытые значения, выделите ячейки, а затем на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек. Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий. Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число.

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

Выделите ячейку, содержащую нулевое (0) значение.

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите «Правила выделения ячеек» > «Равно».

В левом поле введите 0.

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

В диалоговом окне Формат ячеек откройте вкладку Шрифт.

В поле Цвет выберите белый цвет.

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

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

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Выделите пример, приведенный в этой статье.

Важно: Не выделяйте заголовки строк или столбцов.

Выделение примера в справке

Нажмите клавиши CTRL+C.

В Excel создайте пустую книгу или лист.

Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.

Читать еще: Количество полных месяцев между датами excel

Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе «Зависимости формул» нажмите кнопку Показать формулы.

Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.

Записки программера

Страницы

среда, 16 ноября 2011 г.

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

Существует несколько способов отобразить или скрыть нулевые значения.

Использование настроек программы:

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

Использование числового формата

Если значение в одной из этих ячеек меняется на ненулевое, формат этого значения будет аналогичен общему числовому формату.

Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.
В меню Формат выберите команду Ячейки, а затем откройте вкладку Число.
В списке Категория выберите пункт Пользовательская.
В поле Тип введите #,00;-#,00;;@.

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

0 (ноль) – одно обязательное знакоместо (разряд), т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого знакоместа нет числа, то будет выведен ноль. Например, если к числу 12 применить маску 0000, то получится 0012, а если к числу 1,3456 применить маску 0,00 – получится 1,35.
# (решетка) – одно необязательное знакоместо – примерно то же самое, что и ноль, но если для знакоместа нет числа, то ничего не выводится
(пробел) – используется как разделитель групп разрядов
[ ] – в квадратных скобках перед маской формата можно указать название цвета шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой.

Плюс пара простых правил:

Любой пользовательский текст (кг, чел, шт и тому подобные) или символы (в том числе и пробелы) – надо обязательно заключать в кавычки.
Можно указать несколько (до 4-х) разных масок форматов через точку с запятой. Тогда первая из масок будет применяться к ячейке, если число в ней положительное, вторая – если отрицательное, третья – если содержимое ячейки равно нулю и четвертая – если в ячейке не число, а текст (см. выше пример с температурой).

Примечания

Скрытые значения отображаются только в строке формул или в ячейке при ее редактировании. На печать эти значения не выводятся.
Чтобы снова отобразить скрытые значения, выделите ячейки, выберите команду Ячейки в меню Формат, а затем откройте вкладку Число. Для применения числового формата, определенного по умолчанию, в списке Категория выберите пункт Общие. Для повторного отображения даты или времени выберите подходящий формат даты или времени на вкладке Число.

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

В программе Microsoft Office Excel некоторая выводимая в ячейках информация – это итог вычисления определенных формул. Такой результат может равняться нулю и его отображение становится не нужным, особенно в тех случаях, когда в соседних ячейках результат этих формул выведен в виде текста. Избавиться от ненужных нулей можно несколькими методами. Для этого вам понадобятся:

— Программа МО Excel(закажите лицензионную версию в нашем интернет-магазине).

Инструкция

Для того, чтобы на рабочем листе Excel перестали отображаться нули, нужно воспользоваться специальными настройками данной программы. Если у вас установлена программа Excel 2010 – в меню программы выберите пункт «Файл», в открывшемся меню нажмите команду «Параметры». В программе Excel 2007 нужно нажать круглую иконку Office, далее найти и кликнуть по строке «Параметры Excel».

«Параметры Excel» в Excel 2007
В развернувшимся окне откройте вкладку «Дополнительно», прокрутите появившейся перечень и найдите категорию «Показать конфигурацию для следующего листа», снимите флажок с пункта «Отображать нули в ячейках, содержащих нулевые значения», Пункт «Отображать нули в ячейках, содержащих нулевые значения»

после выполнения этих действий нажмите ОК.
Также можно исключить нули не со всего листа, а с определенных ячеек. Обозначьте нужные ячейки и откройте контекстное меню, щелкнув по выделенному правой клавишей мыши. Нажмите команду «Формат ячеек» Активирование команды «Формат ячеек»

и в левой графе, загрузившегося окна, нажмите по пункту «Все форматы».
В появившемся окне, под словом «Тип» пропишите следующую команду: «0;-0;;@» (кавычки опустите). Введение кода числового формата

В том случае, если значения в выделенных ячейках отображаются с точностью до некоторого количества знаков после запятой – во вписанной ранее команде, допишите после каждого нуля, через запятую, столько нулей, сколько должно отображаться в ячейке. Например, если запись в ячейке отображается с точностью до тысячных, то команда должна быть следующего вида: «0,000;-0,000;;@». Нажмите кнопку ОК.

Еще один способ скрыть отображение нулей – это сделать их цвет аналогичный фону страницы. Для выполнения этой задачи необходимо в категории опций «Стили» выбрать кнопку «Условное форматирование», предварительно обозначив определенные ячейки. Откройте раскрывающийся перечень «Правила выделения ячеек» и щелкните команду «Равно».

В открывшемся меню, в левую строку впишите «0» (без кавычек), а напротив укажите «Пользовательский формат». Загрузится новое окно, откройте вкладку «Шрифт» выберете цвет, соответствующий цвету фона вашего документа. Нажмите кнопку ОК.

Видео: Трюк Excel 7. Скрыть нулевые значения в ячейках Excel

0 0 голоса

Рейтинг статьи