Давайте рассмотрим несколько вариантов того, как удалить нули в ячейках в Excel, заменив их пустым полем или альтернативными нулевыми символами (например, дефисом).
Приветствую всех, уважаемые читатели блога TutorExcel.Ru!
Обработка таблиц с числовыми данными — практически неотъемлемая часть работы в Excel для многих пользователей программы.
Внешний вид отображения данных также является довольно важным атрибутом, и если мы привыкли показывать ненулевые значения, бывают ситуации, когда лучше не отображать и не скрывать нули визуально.
Обычно в этих случаях нули в таблицах заменяются пустым полем или символами аналогичного значения (например, в виде дефиса).
В зависимости от требуемых действий можно выделить несколько начальных условий такой замены нулей:
- На всем листе (в каждой ячейке);
- В определенных ячейках (выделены;
- В формулах;
- В сводных таблицах.
Как скрыть нулевые значения в Excel?
Предположим, у нас есть таблица с числами (большая, чтобы визуально оценить разницу во внешнем виде), где, в частности, много нулей и, используя его пример, мы попытаемся удалить ненулевые значения:
На всем листе
Если нам нужно удалить нули в каждой ячейке листа без исключения, то перейдите на панель вкладок Файл -> Параметры -> Дополнительно (так как эти настройки обычно применяются для работы со всей книгой):
Затем в блоке Параметры отображения листа (расположен примерно в середине ленты) снимите флажок рядом с Показывать нули в ячейках, содержащих нулевые значения (по умолчанию флажок установлен и отображаются все нули).
Нажимаем ОК, и в результате исходная таблица выглядит так:
Действительно, как мы видим, таблица стала немного нагляднее, удобнее для восприятия данных, и наше внимание не отвлекают лишние детали.
Чтобы вернуть отображение нулей, нужно проделать обратную процедуру: в настройках поставить галочку напротив соответствующего поля.
Важная деталь.
Этот параметр скрывает нулевые значения только на выбранном листе.
Если вам нужно удалить нули во всей книге, вам нужно сделать эту настройку для каждого листа:
Определенным недостатком этого метода удаления нулевых значений является отсутствие альтернативы заменяющему типу отображения, т.е вместо 0 всегда будет отображаться пустая ячейка.
Следующий вариант может частично решить эту проблему.
В выделенных ячейках
Если вам нужно удалить нулевые значения не во всех ячейках, а только в определенных, механизм удаления нулей немного отличается от предыдущего метода.
Рассмотрим ту же таблицу, и на этот раз попробуйте убрать нули с левой стороны таблицы.
Вначале помните, что любое число в Excel имеет формат отображения в виде маски A; B; C; D, где A, B, C, D — формат обозначений и точка с запятой, отделяющая их друг от друга:
- А — запись при положительном числе;
- B — запись при отрицательном числе;
- C — запись, когда число равно нулю;
- D — записывается, если ячейка содержит не число, а некоторый текст (обычно не используется для чисел).
Нет необходимости явно указывать все маски в записи. Например, если указан только один, он применяется к формату всех чисел, если два, первая часть формы отвечает за отображение положительных чисел и нулей, а вторая — за отрицательные.
Поскольку наша задача — удалить из записи ровно ноль, мы должны записать в маске, как появится третий параметр (часть C маски) и одновременно первый (часть A, положительное число) со вторым (часть B, отрицательное число).
Поэтому, чтобы удалить нули из выбранных ячеек, щелкните их правой кнопкой мыши и выберите в контекстном меню Формат ячеек -> Число, затем среди форматов перейдите во Все форматы:
Затем в маске записываем формат отображения нуля, вместо него либо ничего не пишем (маска # ## 0; — # ## 0; чтобы ячейка стала пустой), либо пишем символ замены (mask # ## 0; — # ## 0; «-» чтобы в ячейке был тире) и нажмите ОК.
В результате получаем:
Чтобы отменить отображение нулей, опять же, вам нужно исправить запись маски в исходном виде.
Вперед, продолжать.
В формулах
Проблема с нулями в ячейках также может быть решена формульным способом.
Если значение в ячейке вычисляется по формуле, то с помощью функции ЕСЛИ мы можем установить различные сценарии отображения (например, если значение не равно 0, мы оставляем значение, а если оно равно 0, мы возвращаем пустое поле или любая другая альтернатива).
Мы записываем отклонение между периодами для исходной таблицы с помощью функции ЕСЛИ.
Вместо стандартной формулы = A1-B1 пишем = SE (A1-B1 = 0; «-«; A1-B1):
Как мы теперь видим, в столбце с отклонениями вместо 0 есть тире, чего мы и хотели добиться.
В сводных таблицах
Для начала предположим, что у нас есть простая и небольшая таблица с данными (при этом не все ячейки таблицы будут заполнены), на основе которой мы построим сводную таблицу:
Задачу удаления нулей из сводной таблицы условно можно разделить на 2 направления:
- Работайте с данными как с ячейками листа;
В этом случае мы не делаем различий между ячейками сводной таблицы и ячейками листа, например, мы можем использовать вышеуказанные методы, чтобы скрыть 0. - Работа с данными в сводной таблице.
В этом случае мы работаем не с ячейками листа, а непосредственно со сводной таблицей.
Поскольку мы уже подробно разобрали первый вариант выше, остановимся подробнее на втором, в котором также выделим 2 способа убрать нули из таблицы:
- Создавая фильтры;
- Настроив параметры сводной таблицы.
Рассмотрим оба варианта.
Создание и применение фильтра
Мы добавляем в сводную таблицу в качестве фильтра поля, по которым мы хотим скрыть нули (в этом примере это Количество, но в принципе здесь это может быть что угодно).
Теперь щелкните значок фильтра для поля Количество и в раскрывающемся списке снимите флажок рядом с 0 или (пустым) значениями, таким образом исключив эти значения из отображения:
Как мы видим, линии, на которых были нулевые значения, были скрыты, что нам и требовалось.
Переходим к следующему варианту.
Настройка параметров сводной таблицы
Регулируя параметры сводной таблицы, мы также можем удалить нули из ячеек. На вкладке Работа со сводными таблицами выберите Анализ -> Сводная таблица -> Параметры -> Макет и формат:
В разделе Формат нас интересует пункт Отображение пустых ячеек, чтобы убрать нули в сводной таблице, нужно поставить галочку в соответствующем поле и оставить поле справа пустым.
В этом случае оказывается, что пустые ячейки из исходной таблицы были вставлены в сводную таблицу, и только после этого мы устанавливаем определенный формат (пустое поле) для их отображения. В результате получаем:
это все!
Скачайте файл с примером.
Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи, обязательно задавайте и пишите в комментариях, не сомневайтесь.
Удачи и до скорых встреч на страницах блога TutorExcel.Ru!
Поделиться с друзьями:
Поиск по сайту:
Числовая маска, таблица, формат ячейки, ячейка
- Круглый индикатор выполнения (индикатор выполнения) в Excel
- Как заморозить область в Excel?
