Видалення нульових значень в Microsoft Excel

При використанні формул в Ексель, в разі, якщо осередки, на які посилається оператор, порожні, в області розрахунків за замовчуванням будуть стояти нулі. Естетично це не дуже красиво виглядає, особливо, якщо в таблиці дуже багато подібних діапазонів з нульовими значеннями. Та й користувачеві важче орієнтуватися в даних в порівнянні з ситуацією, якби подібні області були б взагалі порожніми. Давайте з'ясуємо, якими способами можна видалити відображення нульових даних в Excel.

Алгоритми видалення нулів

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

Спосіб 1: настройки Excel

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

1. Перебуваючи у вкладці «Файл», переходимо в розділ «Параметри».
2. У запустити вікні робимо переміщення в розділ «Додатково». У правій частині вікна шукаємо блок налаштувань «Показати параметри для наступного листа». Знімаємо прапорець біля пункту «Показувати нулі в осередках, які містять нульові значення». Для приведення зміни налаштувань в дію не забуваємо натиснути на кнопку «OK» внизу вікна.

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

Спосіб 2: застосування форматування

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

1. Виділяємо діапазон, в якому потрібно приховати комірки з нульовими значеннями. Кількома по його виділяє фрагменту правою кнопкою миші. У контекстному меню вибираємо пункт «Формат ячеек ...».
2. Виробляється запуск вікна форматування. Переміщаємося у вкладку «Число». Перемикач числових форматів повинен бути встановлений в позицію «Все формати». У правій частині вікна в полі «Тип» вписуємо такий вираз:

   0;-0;;@

   Для збереження введених змін тиснемо на кнопку «OK».

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

урок: Форматування таблиць в Excel

Спосіб 3: умовне форматування

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

1. Виділяємо діапазон, в якому можуть міститися нульові значення. Перебуваючи у вкладці «Головна», натискаємо на кнопку на стрічці «Умовне форматування», яка розміщена в блоці налаштувань «Стилі». У меню, послідовно переходимо по пунктам «Правила виділення осередків» і «Так само».
2. Відкривається віконце форматування. В поле «Форматувати осередки, які РІВНІ» вписуємо значення «0». У правому полі в списку натискаємо по пункту «Призначений для користувача формат ...».
3. Відкривається ще одне вікно. Переходимо в ньому у вкладку «Шрифт». Кількома по випадному списку «Колір», в якому вибираємо білий колір, і тиснемо на кнопку «OK».
4. Повернувшись в попереднє вікно форматування, теж тиснемо на кнопку «OK».

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

урок: Умовне форматування в Excel

Спосіб 4: застосування функції ЯКЩО

Ще один варіант приховування нулів передбачає використання оператора ЯКЩО.

1. Виділяємо першу осередок з того діапазону, в який виводяться результати обчислень, і де можливо будуть присутні нулі. Кількома по піктограмі «Вставити функцію».
2. Запускається Майстер функцій. Виробляємо пошук в списку представлених функцій оператора «ЯКЩО». Після того, як він виділений, тиснемо на кнопку «OK».
3. Активується вікно аргументів оператора. В поле «Логічне вираз» вписуємо ту формулу, яка вираховує в цільовій комірці. Саме результат розрахунку цієї формули в кінцевому підсумку і може дати нуль. Для кожного конкретного випадку це вираз буде різним. Відразу після цієї формули в тому ж полі дописуємо вираз «= 0» без лапок. В поле «Значення якщо істина» ставимо пробіл - «». В поле «Значення якщо брехня» знову повторюємо формулу, але вже без вираження «= 0». Після того, як дані введені, тиснемо на кнопку «OK».
4. Але дана умова поки застосовується лише до однієї осередку в діапазоні. Щоб зробити копіювання формули і на інші елементи, ставимо курсор в нижній правий кут комірки. Відбувається активація маркера заповнення у вигляді хрестика. Затискаємо ліву кнопку миші і простягаємо курсор по всьому діапазону, який потрібно перетворити.
5. Після цього в тих осередках, в яких в результаті обчислення виявляться нульові значення, замість цифри «0» буде стояти пробіл.

До речі, якщо у вікні аргументів на поле «Значення якщо істина» встановити тире, то при виведенні результату в осередках з нульовим значенням буде не пробіл, а прочерк.

урок: Функція 'ЯКЩО' в Excel

Спосіб 5: застосування функції ЕЧІСЛО

Наступний спосіб є своєрідною комбінацією функцій ЯКЩО і ЕЧІСЛО.

1. Як і в попередньому прикладі, відкриваємо вікно аргументів функції ЯКЩО в першій клітинці оброблюваного діапазону. В поле «Логічне вираз» записуємо функцію ЕЧІСЛО. Ця функція показує, чи заповнений елемент даними чи ні. Потім в тому ж полі відкриваємо дужки і вписуємо адресу тієї комірки, яка в разі, якщо вона порожня, може зробити нульовий цільову комірку. Закриваємо дужки. Тобто, по суті, оператор ЕЧІСЛО перевірить, чи містяться якісь дані в зазначеній галузі. Якщо вони є, то функція видасть значення «ІСТИНА», якщо його немає, то - «БРЕХНЯ».

   А ось значення наступних двох аргументів оператора ЯКЩО ми переставляємо місцями. Тобто, в поле «Значення якщо істина» вказуємо формулу розрахунку, а в полі «Значення якщо брехня» ставимо пробіл - «».

   Після того, як дані введені, тиснемо на кнопку «OK».

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

урок: Майстер функцій в Excel

Існує цілий ряд способів видалити цифру «0» в осередку, якщо вона має нульове значення. Найпростіше, відключити відображення нулів в налаштуваннях Excel. Але тоді слід врахувати, що вони зникнуть по всьому листу. Якщо ж потрібно застосувати відключення виключно до якоїсь конкретної області, то в цьому випадку на допомогу прийде форматування діапазонів, умовне форматування і застосування функцій. Який з цих методів вибрати залежить вже від конкретної ситуації, а також від особистих умінь і переваг користувача.

• ← Виділення таблиці в Microsoft Excel
• Застосування функції СЬОГОДНІ в Microsoft Excel →