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

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

Допустим, есть такая табличка с нулями:

При работе с небольшими табличками можно сделать это вручную, но если у вас таблица с большим объемом данных, то делать руками для вас не вариант.

Итак, я продемонстрирую вам несколько способов как можно сделать это.

Давайте начнём!

С помощью параметров Excel

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

Допустим, у нас есть такая табличка и перед нами стоит задача скрыть все нулевые значения:

Как это сделать?

Пошаговая инструкция:

• Щелкните на «Файл»;

• Далее — «Параметры»;

• В окошке выберите вкладку «Дополнительно»;

• Найдите заголовок «Параметры отображения листа»;

• И отключите опцию «Показывать нули…»;

• Подтвердите.

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

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

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

С помощью функции «Условное форматирование»

Итак, функция «Условное форматирование» отлично подойдет для нашей задачи.

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

Допустим, у нас есть такая табличка:

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

Пошаговая инструкция:

• Выделите диапазон ячеек и щелкните «Главная»;

• Щелкните «Условное форматирование»;

• Далее выберите «Правила выделения ячеек» -> «Равно…»;

• В окошке, в первом параметре пропишите «0»;

• А во втором выберите самое нижнее значение;

• Далее установите белый цвет текста;

• Подтвердите.

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

Но у этого метода есть и минус. Это будет работать только в том случае, если сама табличка белого цвета. Потому как белый текст на белой табличке просто не видно. А вот если ваша табличка будет другого цвета — нужно будет подстраиваться под него.

Есть и другой вариант:

• Выделите диапазон ячеек и щелкните «Главная»;

• Откройте «Условное форматирование»;

• Выбираем опцию «Равно…» и в первом параметре указываем 0;

• Далее, во втором параметре, выбираем самое нижнее значение;

• Открываем вкладку число и в поле «Тип» прописываем — «;;;»;

• Подтверждаем.

Готово!

Эти 3 точки с запятой говорят Excel о том, что вы не хотите отображать никаких данных в этих ячейках.

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

Не забудьте, эти методы просто скрывают нули, а не удаляют их.

С помощью функции «Формат»

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

Как это сделать?

Пошаговая инструкция:

• Выделите диапазон ячеек и щелкните «Главная»;

• Щелкните на «Формат»;

• Далее — «Формат ячеек…»;

• Щелкните на «Число»;

• Из списка выберите «(все форматы)»;

• В поле формата вводим следующее: 0;-0;;@.

• Подтверждаем.

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

Как это работает?

В Excel есть 4 типа данных:

1. Положительные
2. Отрицательные
3. Нули
4. Текст

С помощью этой строки мы указали формат для типа «Нули».

Формула типа строится так:

<Положительные_числа>; <Отрицательные_числа>; <Нули>; <Текст>  

Мы указали:

0;-0;;@  

Как вы можете заметить, все остается как есть кроме нулей.

Заменяем нули на текст

Чтобы скрыть их, мы также можем их заменить. Пустым местом или каким-то определенным символом (например «-»). Как вам угодно.

Это также можно сделать с помощью функции «Формат».

Пошаговая инструкция:

• Выделите ячейки и щелкните на «Главная»;

• Далее — «Формат»;

• Далее — «Формат ячеек…»;

• «Число» и выберите из списка «(все форматы)»;

• Укажите такой вид: 0;-0;-;@.

• Подтвердите.

Таким образом, вместо нулей в ячейках будут появляться знак «-».

Важная информация: я часто использую знак «-» вместо нулей, но вы можете использовать абсолютно любое значение, например 0;-0; «нет значения»;@

Убираем нули в сводной таблице

Итак, давайте посмотрим как сделать тоже самое, но уже в сводной таблице:

Начальные данные содержат нули

Итак, допустим у нас есть следующая табличка:

Обратите внимание как много в ней нулей.

Сводная таблица будет выглядеть так:

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

Итак, один из вариантов — использовать функцию «Условное форматирование».

Но, если вы применяете эту функцию при работе со сводной таблицей — все немного иначе.

Пошаговая инструкция:

• Выделяем ячейки с нулями и открываем функцию;

• Открываем с параметром «Равно…»;

• В первом параметре укажите 0;

• А во втором — последний параметр;

• Во вкладке «Число» из списка выберите «(все форматы)» и укажите тип «;;;»;

• Нажмите на этот значок:

• И выберите последний параметр.

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

Убираем нули при пустых значениях в сводной таблице

Теперь рассмотрим такую табличку:

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

Итак, если мы сделаем сводную таблицу, то получим такой результат:

Но в вашем случае, вместо пустых ячеек, там могут быть нули. Как же убрать их?

Пошаговая инструкция:

• Щелкаем правой кнопкой мышки на любой элемент сводной таблицы и жмем «Параметры сводной таблицы»;

• В открывшемся окне откройте 4 вкладку и оставьте пустым параметр «В пустых ячейках показывать»;

• Подтвердите.

Таким образом, нули будут скрыты!

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

С помощью функции «Заменить…»

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

Пошаговая инструкция:

• Щелкните «Найти и выделить» -> «Заменить…»;

• В открывшемся окне укажите следующее:

• В первом параметре укажите «0»;
• Второй параметр просто оставьте пустым;
• Далее нажмите кнопку «Параметры > >» и активируйте опцию «Ячейка целиком»;

• Щелкните «Заменить все»;

Готово! Все ячейки, значение в которых равно нулю будут моментально удалены.

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

• Откройте опцию : «Заменить…»;
• В открывшемся окне сделайте тоже самое что и в предыдущем примере;

• А теперь щелкните на «Найти все»;

• Воспользуйтесь комбинацией горячих клавиш CTRL + A.

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

Скрытые нули и удаленные

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

Если вы скрываете нули, то они по-прежнему остаются в ячейках, просто их теперь не видно. Это значит что вы можете использовать ссылки на такие ячейки в формулах, выполнять вычисления с ними и так далее. А вот если вы удалили нули — сделать это нельзя. Это большая разница.

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

Разница действительно большая, хотя на первый взгляд эти столбики абсолютно одинаковые.