Як приховати нулі в осередках Excel

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

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

Як приховати нулі в осередках Excel

При роботі з невеликими табличками можна зробити це вручну, але якщо у вас таблиця з великим обсягом даних, то робити руками вам не варіант.

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

Давайте почнемо!

За допомогою параметрів Excel

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

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

Як приховати нулі в осередках Excel

Як це зробити?

Покрокова інструкція:

  • Клацніть на “Файл”;

Як приховати нулі в осередках Excel

  • Далі – “Параметри”;

Як приховати нулі в осередках Excel

  • У вікні виберіть вкладку “Додатково”;

Як приховати нулі в осередках Excel

  • Знайдіть заголовок “Параметри відображення аркуша”;

Як приховати нулі в осередках Excel

  • І вимкніть опцію «Показувати нулі…»;

Як приховати нулі в осередках Excel

  • Підтвердіть.

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

Однак, важливо знати, що Excel просто перестає відображати ці нулі, насправді вони все ще є у цих осередках.

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

За допомогою функції «Умовне форматування»

Отже, функція “Умовне форматування” відмінно підійде для нашого завдання.

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

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

Як приховати нулі в осередках Excel

Як приховати нульові значення?

Покрокова інструкція:

  • Виділіть діапазон осередків та клацніть «Головна»;

Як приховати нулі в осередках Excel

  • Натисніть «Умовне форматування»;

Як приховати нулі в осередках Excel

  • Далі виберіть “Правила виділення осередків” -> “Рівне …”;

Як приховати нулі в осередках Excel

  • У вікні в першому параметрі пропишіть «0»;

Як приховати нулі в осередках Excel

  • А у другому виберіть найнижче значення;

Як приховати нулі в осередках Excel

  • Далі встановіть білий колір тексту;

Як приховати нулі в осередках Excel

  • Підтвердіть.

Таким чином ми досягли результату. За фактом нулі в осередках є, але вони відображаються білим кольором і їх не видно.

Але цей метод має й мінус. Це працюватиме лише в тому випадку, якщо сама табличка білого кольору. Бо білий текст на білій табличці просто не видно. А ось якщо ваша табличка буде іншого кольору – потрібно буде підлаштовуватись під нього.

Є й інший варіант:

  • Виділіть діапазон осередків та клацніть «Головна»;

Як приховати нулі в осередках Excel

  • Відкрийте «Умовне форматування»;

Як приховати нулі в осередках Excel

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

Як приховати нулі в осередках Excel

  • Далі, у другому параметрі, вибираємо найнижче значення;

Як приховати нулі в осередках Excel

  • Відкриваємо вкладку число і в полі “Тип” прописуємо – “;;;”;

Як приховати нулі в осередках Excel

  • Підтверджуємо.

Готово!

Ці 3 точки з комою говорять Excel про те, що ви не хочете відображати жодних даних у цих осередках.

2 цих варіанти також працюватимуть і при нульових значеннях, отриманих за допомогою функцій.

Не забудьте, ці методи просто приховують нулі, а чи не видаляють їх.

За допомогою функції «Формат»

Тепер, давайте створимо формат користувача, який також приховуватиме нулі.

Як це зробити?

Покрокова інструкція:

  • Виділіть діапазон осередків та клацніть «Головна»;

Як приховати нулі в осередках Excel

  • Клацніть на “Формат”;

Як приховати нулі в осередках Excel

  • Далі – “Формат осередків …”;

Як приховати нулі в осередках Excel

  • Клацніть на “Число”;

Як приховати нулі в осередках Excel

  • Зі списку виберіть «(всі формати)»;

Як приховати нулі в осередках Excel

  • У полі формату вводимо наступне: 0; -0;; @.

Як приховати нулі в осередках Excel

  • Підтверджуємо.

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

Як це працює?

В Excel є 4 типи даних:

  1. Позитивні
  2. Негативні
  3. Нулі
  4. Текст

За допомогою цього рядка ми вказали формат типу «Нулі».

Формула типу будується так:

<Позитивні_числа>; <Негативні_числа>; <Нулі>; <Текст>

Ми вказали:

0;-0;;

Як ви можете помітити, все залишається як є крім нулів.

Замінюємо нулі на текст

Щоб приховати їх, ми також можемо замінити їх. Пустим місцем або певним символом (наприклад «-»). Як вам завгодно.

Це також можна зробити за допомогою функції формату.

Покрокова інструкція:

  • Виділіть комірки та клацніть на «Головна»;

Як приховати нулі в осередках Excel

  • Далі – “Формат”;

Як приховати нулі в осередках Excel

  • Далі – “Формат осередків …”;

Як приховати нулі в осередках Excel

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

Як приховати нулі в осередках Excel

  • Вкажіть такий вид: 0; -0; -; @.

Як приховати нулі в осередках Excel

  • Підтвердіть.

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

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

Прибираємо нулі у зведеній таблиці

Отже, давайте подивимося як зробити те саме, але вже в зведеній таблиці:

Початкові дані містять нулі

Отже, припустимо, у нас є наступна табличка:

Як приховати нулі в осередках Excel

Зверніть увагу, як багато в ній нулів.

Зведена таблиця виглядатиме так:

Як приховати нулі в осередках Excel

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

Отже, один із варіантів – використовувати функцію «Умовне форматування».

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

Покрокова інструкція:

  • Виділяємо осередки з нулями та відкриваємо функцію;

Як приховати нулі в осередках Excel

  • Відкриваємо з параметром “Рівне …”;

Як приховати нулі в осередках Excel

  • У першому параметрі вкажіть 0;

Як приховати нулі в осередках Excel

  • А в другому – останній параметр;

Як приховати нулі в осередках Excel

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

Як приховати нулі в осередках Excel

  • Натисніть на цей значок:

Як приховати нулі в осередках Excel

  • І виберіть останній параметр.

Як приховати нулі в осередках Excel

Готово, нульові значення таким чином приховані. Цей спосіб зручний тим, що якщо ви зміните структуру зведеної таблиці, або туди додадуться інші дані, нулі, як і раніше, не будуть відображатися.

Прибираємо нулі при порожніх значеннях у зведеній таблиці

Тепер розглянемо таку табличку:

Як приховати нулі в осередках Excel

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

Отже, якщо ми зробимо зведену таблицю, то отримаємо такий результат:

Як приховати нулі в осередках Excel

Але у вашому випадку замість порожніх осередків там можуть бути нулі. Як же їх прибрати?

Покрокова інструкція:

  • Клацаємо правою кнопкою мишки на будь-який елемент зведеної таблиці та тиснемо “Параметри зведеної таблиці”;

Як приховати нулі в осередках Excel

  • У вікні, відкрийте 4 вкладку і залиште порожнім параметр «У порожніх осередках показувати»;

Як приховати нулі в осередках Excel

  • Підтвердіть.

Таким чином, нулі будуть приховані!

Також ви можете замість порожнього значення вказати будь-яке. Це вже на ваш вибір.

Як приховати нулі в осередках Excel

За допомогою функції «Замінити…»

Всі попередні способи просто приховували нулі в осередках, але вони там залишалися. Але що якщо потрібно видалити всі нульові значення?

Покрокова інструкція:

  • Натисніть «Знайти та виділити» -> «Замінити…»;

Як приховати нулі в осередках Excel

  • У вікні вкажіть наступне:
  • У першому параметрі вкажіть “0”;
  • Другий параметр просто залиште пустим;
  • Далі натисніть кнопку «Параметри > >» та активуйте опцію «Комірка повністю»;

Як приховати нулі в осередках Excel

  • Натисніть “Замінити всі”;

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

Також ви можете відразу не видаляти комірки, а просто виділити їх, щоб зробити це:

  • Відкрийте опцію: «Замінити…»;
  • У вікні зробіть те саме, що і в попередньому прикладі;

Як приховати нулі в осередках Excel

  • А тепер клацніть на “Знайти все”;

Як приховати нулі в осередках Excel

  • Скористайтесь комбінацією гарячих клавіш CTRL+A.

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

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

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

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

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

Як приховати нулі в осередках Excel

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

nBook
Додати коментар