Приховати нульові значення в Excel - Зробіть клітинки порожніми, якщо значення 0

Подивіться відео - Як приховати нульові значення в Excel + Як видалити нульові значення

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

Іноді в Excel може виникнути бажання приховати нульові значення у наборі даних і покажіть ці клітинки як пробіли.

Припустимо, у вас є набір даних, як показано нижче, і ви хочете приховати значення 0 у всіх цих клітинках (або хочете замінити його чимось, таким як тире або текст «Недоступно»).

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

У цьому підручнику я покажу вам кілька способів приховання нульових значень у Excel та один метод вибору та видалення всіх нульових значень із набору даних.

Важлива примітка: Коли ви ховаєте 0 у комірці за допомогою методів, показаних у цьому посібнику, вона лише приховує 0, а не видаляє його. Хоча клітини можуть виглядати порожніми, ці клітинки все ще містять 0. І якщо ви використовуєте ці клітинки (які мають приховані нулі) у будь -якій формулі, вони будуть використані у формулі.

Давайте почнемо і зануримось у кожен із цих методів!

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

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

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

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

Нижче наведено кроки, щоб приховати нулі з усіх клітинок у книзі в Excel:

1. Відкрийте книгу, у якій потрібно приховати всі нулі
2. Перейдіть на вкладку Файл
3. Натисніть Параметри
4. У діалоговому вікні Параметри Excel, що відкриється, клацніть на опції «Додатково» на лівій панелі
5. Прокрутіть униз до розділу, у якому написано "Параметри відображення для цього аркуша" та виберіть аркуш, у якому потрібно приховати нулі.
6. Зніміть прапорець "Показувати нуль у клітинках, які мають нульове значення"
7. Натисніть ОК

Вищеописані кроки миттєво приховають нулі у всіх клітинках на вибраному аркуші.

Ця зміна також застосовується до клітинок, де нуль є результатом формули.

Пам’ятайте, що цей метод лише приховує значення 0 у клітинках і не видаляє їх. 0 все ще є, він просто прихований.

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

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

Хоча вищезгаданий метод є найбільш зручним, він не дозволяє приховувати нулі лише в певному діапазоні (скоріше він приховує нульові значення у всьому аркуші).

Якщо ви хочете приховати нулі лише для певного набору даних (або кількох наборів даних), можна скористатися умовним форматуванням.

За допомогою умовного форматування можна застосувати певний формат на основі значення в осередках. Отже, якщо значення в деяких клітинках дорівнює 0, ви можете просто запрограмувати умовне форматування, щоб приховати його (або навіть виділити, якщо хочете).

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

Нижче наведено кроки, щоб приховати нулі в Excel за допомогою умовного форматування:

1. Виберіть набір даних
2. Перейдіть на вкладку «Домашня сторінка»
3. У групі «Стилі» натисніть «Умовне форматування».
4. Наведіть курсор на опції "Правила виділення клітинок" і у вікні, що з'явиться, натисніть на опцію "Дорівнює".
5. У діалоговому вікні "Дорівнює" введіть "0" у лівому полі.
6. Виберіть спадне меню Формат і натисніть «Спеціальний формат».
7. У діалоговому вікні «Форматування клітинок» виберіть вкладку «Шрифт».
8. У спадному меню Колір виберіть білий колір.
9. Натисніть OK.
10. Натисніть OK.

Наведені вище кроки змінюють колір шрифту клітинок з нулями на білий, що робить ці клітинки порожніми.

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

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

1. Виберіть набір даних
2. Перейдіть на вкладку «Домашня сторінка»
3. У групі «Стилі» натисніть «Умовне форматування».
4. Наведіть курсор на опції "Правила виділення клітинок" і в параметрах, що з'являються, натисніть на опцію "Дорівнює".
5. У діалоговому вікні "Дорівнює" введіть 0 у лівому полі.
6. Виберіть спадне меню Формат і натисніть Спеціальний формат.
7. У діалоговому вікні "Форматування клітинок" виберіть вкладку "Номер" і натисніть опцію "Спеціальний" на лівій панелі.
8. У опції Спеціальний формат введіть наступне у полі Тип: ;;; (три крапки з комою без пробілу)
9. Натисніть OK.
10. Натисніть OK.

Наведені вище кроки змінюють користувацьке форматування клітинок зі значенням 0.

Коли ви використовуєте три крапки з комою як формат, він приховує все у комірці (будь то числове або текстове). А оскільки цей формат застосовується лише до тих клітинок, у яких є значення 0, він приховує лише нулі, а не решту набору даних.

Ви також можете використовувати цей метод, щоб виділити ці клітинки іншим кольором (наприклад, червоним або жовтим). Для цього просто додайте ще один крок (після кроку 8), де вам потрібно вибрати вкладку «Заповнити» у діалоговому вікні «Форматувати клітинки», а потім вибрати колір, яким потрібно виділити клітинки.

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

Примітка: Обидва методи, розглянуті в цьому розділі, приховують лише нульові значення. Це не видаляє ці нулі. Це означає, що якщо ви використовуєте ці клітинки у розрахунках, усі нулі також будуть використані.

Приховати нульове значення за допомогою користувацького форматування

Інший спосіб приховати нульові значення з набору даних - створити власний формат, який приховує значення в осередках з 0, тоді як будь -яке інше значення відображається належним чином.

Нижче наведено кроки для використання власного формату, щоб приховати нулі в клітинках:

1. Виберіть весь набір даних, для якого потрібно застосувати цей формат. Якщо ви хочете цього для всього робочого аркуша, виділіть усі клітинки
2. Вибравши клітинки, перейдіть на вкладку «Домашня сторінка»
3. У групі Осередки натисніть параметр Формат
4. У спадному меню натисніть «Форматувати клітинки». Відкриється діалогове вікно Форматування комірок
5. У діалоговому вікні Форматування клітинок виберіть вкладку Число
6. У параметрі на лівій панелі натисніть опцію Спеціальний.
7. У параметрах праворуч введіть такий формат у поле Тип: 0;-0;;@
8. Натисніть OK

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

Як працює цей нестандартний формат?

В Excel є чотири типи форматів даних, які ви вказуєте:

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

І для кожного з цих типів даних можна вказати формат.

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

Нижче наведено структуру користувацького формату, якої потрібно дотримуватися, якщо ви застосовуєте власний формат до кожного з цих типів даних:

;;;

У методі, описаному вище, я вказав формат:

0;-0;;@

Вищевказаний формат робить наступне:

1. Позитивні числа відображаються як є
2. Негативні числа показані з від’ємним знаком
3. Нулі приховані (оскільки не вказано формат)
4. Текст відображається як є

Професійна порада: Якщо ви хочете, щоб у вашому наборі даних від’ємні числа відображалися червоним кольором, використовуйте такий спеціальний формат: 0; [Червоний] 0; 0;@

Замініть нулі тире або якимось певним текстом

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

Наприклад, ви можете видалити нулі та замінити їх тире або текстом, таким як “Недоступно” або “Дані ще попереду”

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

Нижче наведено кроки для перетворення 0 у тире в Excel:

1. Виберіть весь набір даних, для якого потрібно застосувати цей формат. Якщо ви хочете цього для всього робочого аркуша, виділіть усі клітинки
2. Вибравши клітинки, перейдіть на вкладку Домашня сторінка
3. У групі Осередки натисніть параметр Формат
4. У спадному меню натисніть «Форматувати клітинки». Відкриється діалогове вікно Форматування комірок
5. У діалоговому вікні Форматування клітинок виберіть вкладку Число
6. У параметрі на лівій панелі натисніть опцію Спеціальний.
7. У параметрах праворуч введіть такий формат у поле Тип: 0;-0;-;@
8. Натисніть OK

Наведені вище кроки збережуть усі інші клітинки такими, які є, і змінять клітинки зі значенням 0, щоб показати тире (-).

Примітка: У цьому прикладі я вирішив замінити нуль тире, але ви також можете використовувати будь -який текст. Наприклад, якщо ви хочете показати NA у клітинках, ви можете використовувати такий формат: 0; -0; "NA";@Професійна порада: Ви також можете вказати кілька кольорів тексту у форматуванні спеціального числа. Наприклад, якщо ви хочете, щоб клітинки з нулями відображали текст NA червоним кольором, ви можете використовувати такий формат: 0; -0; [Червоний] "NA";@

Приховати нульові значення у зведених таблицях

Існує два сценарії, коли зведена таблиця показує значення як 0:

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

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

Коли клітинки вихідних даних мають 0

Нижче наведено зразок набору даних, який я використав для створення зведеної таблиці. Зауважте, що кількість/дохід/прибуток для всіх записів для "Захід" дорівнює 0.

Зведена таблиця, створена за допомогою цього набору даних, виглядає так:

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

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

Однак умовне форматування у зведеній таблиці в Excel працює трохи інакше, ніж звичайні дані.

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

1. Виберіть будь -яку клітинку у стовпці, що має 0 у зведеному звіті зведеної таблиці (не вибирайте всі клітинки, а лише одну клітинку)
2. Натисніть вкладку Домашня сторінка
3. У групі «Стилі» натисніть «Умовне форматування».
4. Наведіть курсор на опції "Правила виділення клітинок" і у вікні, що з'явиться, натисніть на опцію "Дорівнює".
5. У діалоговому вікні "Дорівнює" введіть 0 у лівому полі.
6. Виберіть спадне меню Формат і натисніть Спеціальний формат.
7. У діалоговому вікні «Форматування клітинок» виберіть вкладку «Номер» і натисніть опцію «Спеціальний» на лівій панелі.
8. У опції Спеціальний формат введіть наступне у полі Тип: ;;; (три крапки з комою без пробілу)
9. Натисніть OK. Ви побачите маленький значок Параметри форматування праворуч від вибраної комірки.
10. Натисніть на цей значок «Параметри форматування»
11. У доступних параметрах виберіть - Усі клітинки, що показують значення "Суми доходів" для "Регіон".

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

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

Коли клітинки вихідних даних мають порожні клітинки

Нижче наведено приклад, коли у мене є набір даних, у якому є порожні клітинки для всіх записів для Заходу.

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

За замовчуванням зведена таблиця показує порожні клітинки, коли всі осередки вихідних даних порожні. Але якщо ви все ще бачите 0 у цьому випадку, виконайте наведені нижче дії, щоб приховати нуль і показати порожні клітинки:

1. Клацніть правою кнопкою миші будь-яку клітинку зведеної таблиці
2. Натисніть «Параметри зведеної таблиці»
3. У діалоговому вікні "Параметри зведеної таблиці" клацніть на вкладці "Макет і формат"
4. У параметрах форматування поставте прапорець біля опцій «Для показу порожніх клітинок:» і залиште це поле порожнім.
5. Натисніть OK.

Наведені вище кроки приховають нулі у зведеній таблиці і замість цього покажуть порожню клітинку.

Якщо ви хочете, щоб зведена таблиця відображала щось замість 0, ви можете вказати це на кроці 4. Наприклад, якщо ви хочете показати текст - "Дані недоступні", замість 0 введіть цей текст у поле (як показано нижче).

Знайдіть і видаліть нулі в Excel

У всіх вищенаведених методах я показав вам способи приховати значення 0 у комірці, але значення все одно залишається у клітинці.

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

1. Виберіть набір даних (або весь аркуш)
2. Натисніть вкладку Домашня сторінка
3. У групі "Редагування" натисніть "Знайти та вибрати"
4. У параметрах, які відображаються у спадному меню, натисніть «Замінити»
5. У діалоговому вікні «Знайти та замінити» внесіть такі зміни:
   • У полі "Знайти що" введіть 0
   • У полі "Замінити на" нічого не вводите (залиште поле порожнім)
   • Натисніть кнопку «Параметри» та встановіть прапорець «Зіставити весь вміст комірки»
6. Натисніть Замінити все

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

Якщо ви не хочете видалити нулі відразу і спочатку виділити клітинки, виконайте такі дії:

1. Виберіть набір даних (або весь аркуш)
2. Натисніть вкладку Домашня сторінка
3. У групі "Редагування" натисніть "Знайти та вибрати"
4. У параметрах, які відображаються у спадному меню, натисніть «Замінити»
5. У діалоговому вікні «Знайти та замінити» внесіть такі зміни:
   • У полі "Знайти що" введіть 0
   • У полі "Замінити на" нічого не вводите (залиште поле порожнім)
   • Натисніть кнопку «Параметри» та встановіть прапорець «Зіставити весь вміст комірки»
6. Натисніть кнопку «Знайти все». Це дасть вам список усіх клітинок, у яких є 0
7. Натисніть Control-A (утримуйте клавішу управління і натисніть A). Це вибере всі клітинки, у яких є 0.

Тепер, коли у вас є всі клітинки з вибраним 0, ви можете видалити їх, замінити на щось інше або змінити колір та виділити їх.

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

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

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

Навпаки, якщо у вас є порожня клітинка, її використання у формулах може означати, що Excel автоматично ігнорує ці клітинки (залежно від того, яку формулу ви використовуєте).

Наприклад, нижче я отримую, коли я використовую формулу AVERAGE, щоб отримати середнє значення для двох стовпців (результат у рядку 14). У стовпці А є клітинки, де значення 0 було приховано (але все ще є в комірці), а у стовпці В - комірки, де значення 0 було видалено.

Ви можете побачити різницю в результаті, незважаючи на те, що набір даних для обох наборів даних виглядає однаково.

Це відбувається, коли формула AVERAGE ігнорує порожні клітинки у стовпці B, але вона використовує ті, що є у стовпці A (оскільки ті у стовпці A все ще мають значення 0).