Як приховати нулі в осередках 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”. Тепер ви можете видалити їх або, наприклад, відредагувати.

Приховані нулі та віддалені

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

Якщо ви приховуєте нулі, то вони, як і раніше, залишаються в осередках, просто їх тепер не видно. Це означає, що ви можете використовувати посилання на такі осередки у формулах, виконувати обчислення з ними і так далі. А от якщо ви видалили нулі – зробити це не можна. Це велика різниця.

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

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