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

Как убрать нули в ячейках в 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 способа, чтобы убрать нули из таблицы:

  • С помощью создания фильтров;
  • С помощью настройки параметров сводной таблицы.
Читайте также:  В ms excel нельзя удалить

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

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

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

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

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

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

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

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

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

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

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

Источник: tutorexcel.ru

Удаление нулевых значений в Microsoft Excel

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

Алгоритмы удаления нулей

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

Способ 1: настройки Excel

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

    Находясь во вкладке «Файл», переходим в раздел «Параметры».

  • В запустившемся окне делаем перемещение в раздел «Дополнительно». В правой части окна ищем блок настроек «Показать параметры для следующего листа». Снимаем флажок около пункта «Показывать нули в ячейках, которые содержат нулевые значения». Для приведения изменения настроек в действие не забываем нажать на кнопку «OK» внизу окна.
  • После этих действий все ячейки текущего листа, которые содержат нулевые значения, будут отображаться, как пустые.

    Способ 2: применение форматирования

    Скрыть значения пустых ячеек можно при помощи изменения их формата.

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

    Производится запуск окна форматирования. Перемещаемся во вкладку «Число». Переключатель числовых форматов должен быть установлен в позицию «Все форматы». В правой части окна в поле «Тип» вписываем следующее выражение:

    Для сохранения введенных изменений жмем на кнопку «OK».

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

    Способ 3: условное форматирование

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

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

    Открывается окошко форматирования. В поле «Форматировать ячейки, которые РАВНЫ» вписываем значение «0». В правом поле в раскрывающемся списке кликаем по пункту «Пользовательский формат…».

    Открывается ещё одно окно. Переходим в нем во вкладку «Шрифт». Кликаем по выпадающему списку «Цвет», в котором выбираем белый цвет, и жмем на кнопку «OK».

  • Вернувшись в предыдущее окно форматирования, тоже жмем на кнопку «OK».
  • Теперь при условии, если значение в ячейке будет нулевым, то оно будет невидимым для пользователя, так как цвет его шрифта сольется с цветом фона.

    Способ 4: применение функции ЕСЛИ

    Ещё один вариант скрытия нулей предусматривает использование оператора ЕСЛИ.

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

    Запускается Мастер функций. Производим поиск в списке представленных функций оператора «ЕСЛИ». После того, как он выделен, жмем на кнопку «OK».

    Активируется окно аргументов оператора. В поле «Логическое выражение» вписываем ту формулу, которая высчитывает в целевой ячейке. Именно результат расчета этой формулы в конечном итоге и может дать ноль. Для каждого конкретного случая это выражение будет разным. Сразу после этой формулы в том же поле дописываем выражение «=0» без кавычек. В поле «Значение если истина» ставим пробел – « ». В поле «Значение если ложь» опять повторяем формулу, но уже без выражения «=0». После того, как данные введены, жмем на кнопку «OK».

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

    Читайте также:  Взлом пароля эксель

  • После этого в тех ячейках, в которых в результате вычисления окажутся нулевые значения, вместо цифры «0» будет стоять пробел.
  • Кстати, если в окне аргументов в поле «Значение если истина» установить тире, то при выводе результата в ячейках с нулевым значением будет не пробел, а прочерк.

    Способ 5: применение функции ЕЧИСЛО

    Следующий способ является своеобразной комбинацией функций ЕСЛИ и ЕЧИСЛО.

      Как и в предыдущем примере, открываем окно аргументов функции ЕСЛИ в первой ячейке обрабатываемого диапазона. В поле «Логическое выражение» записываем функцию ЕЧИСЛО. Эта функция показывает, заполнен ли элемент данными или нет. Затем в том же поле открываем скобки и вписываем адрес той ячейки, которая в случае, если она пустая, может сделать нулевой целевую ячейку. Закрываем скобки. То есть, по сути, оператор ЕЧИСЛО проверит, содержатся ли какие-то данные в указанной области. Если они есть, то функция выдаст значение «ИСТИНА», если его нет, то — «ЛОЖЬ».

    А вот значения следующих двух аргументов оператора ЕСЛИ мы переставляем местами. То есть, в поле «Значение если истина» указываем формулу расчета, а в поле «Значение если ложь» ставим пробел – « ».

    После того, как данные введены, жмем на кнопку «OK».

  • Так же, как и в предыдущем способе, копируем с помощью маркера заполнения формулу на остальную часть диапазона. После этого нулевые значения исчезнут из указанной области.
  • Существует целый ряд способов удалить цифру «0» в ячейке, если она имеет нулевое значение. Проще всего, отключить отображения нулей в настройках Excel. Но тогда следует учесть, что они исчезнут по всему листу. Если же нужно применить отключение исключительно к какой-то конкретной области, то в этом случае на помощь придет форматирование диапазонов, условное форматирование и применение функций. Какой из данных способов выбрать зависит уже от конкретной ситуации, а также от личных умений и предпочтений пользователя.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Источник: lumpics.ru

    Скрытие значений в EXCEL равных 0

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

    Скрыть значения равные 0 во всех ячейках на листе можно через Параметры MS EXCEL (далее нажмите Дополнительно/ Раздел Показать параметры для следующего листа/ , затем снять галочку Показывать нули в ячейках, которые содержат нулевые значения ).

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

    Пользовательский формат

    Пользовательский формат вводим через диалоговое окно Формат ячеек .

    • для вызова окна Формат ячеек нажмите CTRL+1 ;
    • выберите (все форматы).
    • в поле Тип введите формат # ##0,00;-# ##0,00;

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

    Условное форматирование

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

    • выделите интересующий диапазон;
    • в меню выберите Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Равно );
    • в левом поле введите 0;
    • в правом поле выберите пользовательский формат (белый цвет шрифта);

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

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

    Значение Пустой текст (“”)

    Замечательным свойством значения Пустой текст является, то что оно не отображается в ячейке (например, введите формулу =”” ). Записав, например, в ячейке B1 формулу =ЕСЛИ(A1;A1;””) получим пустую ячейку В1 в случае, если в А1 находится значение =0. Столбец А можно скрыть.

    Источник: excel2.ru

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

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

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

    Инструкция

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

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

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

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

    Читайте также:  Если вложенная в если в excel

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

  • Еще один способ скрыть отображение нулей – это сделать их цвет аналогичный фону страницы. Для выполнения этой задачи необходимо в категории опций «Стили» выбрать кнопку «Условное форматирование», предварительно обозначив определенные ячейки. Откройте раскрывающийся перечень «Правила выделения ячеек» и щелкните команду «Равно».
  • В открывшемся меню, в левую строку впишите «0» (без кавычек), а напротив укажите «Пользовательский формат». Загрузится новое окно, откройте вкладку «Шрифт» выберете цвет, соответствующий цвету фона вашего документа. Нажмите кнопку ОК.
  • Видео: Трюк Excel 7. Скрыть нулевые значения в ячейках Excel


    Источник: besthard.ru

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

    Страницы

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

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

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

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

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

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

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

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

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

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

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

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

    Примечания

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

    Источник: programming086.blogspot.com

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

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

    Как скрыть ошибки в Excel

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

    Чтобы скрыть ОШИБКУ в ячейке:

    1. Выделите диапазон ячеек D2:D8, а потом используйте инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило».
    2. В разделе данного окна «Выберите тип правила:» воспользуйтесь опцией «Форматировать только ячейки, которые содержат:».
    3. В разделе «Измените описание правила:» из левого выпадающего списка выберите опцию «Ошибки».
    4. Нажимаем на кнопку «Формат», переходим на вкладку «Шрифт» и в разделе «Цвет:» указываем белый. На всех окнах «ОК».

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

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

    Пример автоматического скрытия нежелательных нулей в ячейках таблицы:

    1. Выделите диапазон ячеек D2:D8 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Правила выделения ячеек»-«Равно».
    2. В левом поле «Форматировать ячейки, которые РАВНЫ:» вводим значение 0.
    3. В правом выпадающем списке выбираем опцию «Пользовательский формат», переходим на вкладку «Шрифт» и в разделе «Цвет:» указываем белый.

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

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

    Источник: exceltable.com