Разберем несколько вариантов как можно убрать нули в ячейках в 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 способа, чтобы убрать нули из таблицы:
- С помощью создания фильтров;
- С помощью настройки параметров сводной таблицы.
Рассмотрим оба варианта.
Создание и применение фильтра
Давайте в сводной таблице добавим в качестве фильтра поля, по которым мы хотим скрыть нули (в данном примере это Количество, но в принципе тут может стоят все что угодно).
Теперь щелкаем по значку фильтра для поля Количество и в выпадающем списке снимаем галочку напротив значений 0 или (пусто), тем самым исключив эти значения из отображения:
Как мы видим строки, где находились нулевые значения, скрылись, что нам и требовалось.
Перейдем к следующему варианту.
Настройка параметров сводной таблицы
С помощью настройки параметров сводной таблицы мы также можем удалить нули из ячеек. Во вкладке Работа со сводными таблицами выбираем Анализ -> Сводная таблица -> Параметры -> Макет и формат:
В разделе Формат нас интересует пункт Для пустых ячеек отображать, чтобы убрать нули в сводной таблице, нужно поставить галочку в соответствующем поле и оставить поле справа пустым.
В этом случае получается, что пустые ячейки из исходной таблицы подтянулись в сводную, а уже затем мы задали конкретный формат (пустое поле) для их отображения. В итоге получаем:
Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно спрашивайте и пишите в комментариях, не стесняйтесь.
Как убрать нули в сводной таблице
Как скрыть строки с нулевым значением в сводной таблице?
Скрытие строк, содержащих нулевые значения, в сводной таблице может быть общей потребностью, с которой часто сталкиваются пользователи. После скрытия строк с нулевым значением ваша сводная таблица будет выглядеть аккуратно, а данные также будут подавлены. Но как скрыть в сводной таблице строки, содержащие нулевые значения? Пожалуйста, прочтите следующее руководство.
Combine multiple worksheets/workbooks into one worksheet / workbook:
Combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge task in your daily work. But, if you have Kutools for Excel, its powerful utility – Combine can help you quickly combine multiple worksheets, workbooks into one worksheet or workbook. Download the full feature 60-day free trail of Kutools for Excel now!
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!
Скрыть строку с нулевым значением, создав поле фильтра
Чтобы скрыть строки с нулевым значением от отображения в сводной таблице, выполните следующие действия:
1. После создания сводной таблицы в поле Список полей сводной таблицы перетащите нужные поля, нулевые значения которых вы хотите скрыть из Выберите поля для добавления в отчет коробка в Фильтр отчетов поле, и ваши перетаскиваемые поля отображаются в верхней части сводной таблицы, см. снимок экрана:
2. Затем щелкните стрелку раскрывающегося списка поля, нулевые значения которого вы хотите скрыть, и установите флажок Выбрать несколько элементов поле, затем снимите флажок 0, см. снимок экрана:
3. Нажмите OK, и все строки с нулевым значением были скрыты для этого поля, если вам нужно скрыть нулевые значения в других полях, просто повторите шаг 2. Строки с нулевым значением будут скрыты из выбранных вами полей.
Скрыть строку с нулевым значением с помощью функции фильтра в сводной таблице
Помимо описанного выше метода, вы также можете использовать функцию фильтра в сводной таблице, чтобы скрыть строки с нулевым значением. Сделайте так:
1. В сводной таблице выберите любую строку содержимого и щелкните правой кнопкой мыши, затем выберите Фильтр > Фильтры значений, см. снимок экрана:
2. В Фильтр значений В первом раскрывающемся списке выберите поле данных, нулевые значения которого вы хотите скрыть, и выберите не равно из второго выпадающего списка, наконец, введите 0 в текстовое поле, см. снимок экрана:
3. Затем нажмите OK , чтобы закрыть это диалоговое окно, и строки с нулевым значением были скрыты в выбранном вами поле данных.
Внимание: С помощью этого метода вы можете скрыть только одно поле столбца, если вы выберете другое поле для скрытия, будут отображаться исходные скрытые строки, их отношение будет «ИЛИ».
Office Tab — Tabbed Browsing, Editing, and Managing of Workbooks in Excel:
Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:
Исключение нулевых значений из сводной таблицы
У Уильяма есть сводная таблица, основанная на деталях, взятых из магазина для конкретной единицы оборудования. Некоторые части никогда не использовались, поэтому их использование равно нулю. Уильям задается вопросом, есть ли способ отображать в сводной таблице только части с использованием больше нуля.
Есть несколько способов справиться с этой ситуацией. Один из способов, очевидно, – удалить все элементы с нулевым значением из данных, используемых для создания сводной таблицы. Другой способ – создать сводную таблицу, но затем применить фильтр к сводной таблице, чтобы удалить эти элементы с нулевым значением.
Чтобы применить автофильтр после создания сводной таблицы, все, что вам нужно для этого нужно выбрать столбец справа от сводной таблицы и затем создать автофильтр. (Создайте автофильтр, как обычно в своей версии Excel.) Excel достаточно умен, чтобы знать, что автофильтр не должен применяться к пустому столбцу, а вместо этого выполняет свою работу со строками, составляющими сводную таблицу. Щелкните треугольник справа от столбца, по которому вы хотите выполнить фильтрацию, затем выберите «Пользовательский». Затем вы можете указать, что фильтр должен включать только элементы со значением больше нуля.
Еще одна вещь, которую вы можете попробовать, это удобно, если элемент, который вы хотите отфильтровать (в данном случае, поле частей) является либо полем столбца, либо полем строки. Просто щелкните правой кнопкой мыши поле после того, как оно будет помещено в сводную таблицу, и выберите «Настройки». Затем вы можете указать, что хотите исключить конкретное значение (в данном случае значение 0) из сводной таблицы.
Как убрать нули в сводной таблице
Иногда в расчетных (вычисляемых) столбцах сводной таблицы можно наблюдать ошибки EXCEL, хорошо знакомые нам при работе с формулами и функциями.
Такое может образоваться, например, при расчете динамики относительно стартового (нулевого по продажам) периода, где фактически происходит деление на «0».
Для того, чтобы скрыть ошибки, необходимо выполнить следующее:
1. Необходимо кликнуть в любом месте сводной таблицы и на вкладке Параметры в группе Работа со сводными таблицами нажать на кнопку в левой части ленты – «Параметры».
2. В открывшемся окне на вкладе «Разметка и формат» поставить параметр «Для ошибок отображать» и нажать «ОК».
3. В сводной таблице ошибки будут заменены на пустые значения, если в окне не указаны никакие символы замены.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)