Скриване на нулеви стойности в Excel - Направете клетките празни, ако стойността е 0

Гледайте видео - Как да скриете нулевите стойности в Excel + Как да премахнете нулевите стойности

В случай, че предпочитате да четете, вместо да гледате видео, по -долу е пълният писмен урок.

Понякога в Excel може да искате скриване на нулеви стойности във вашия набор от данни и покажете тези клетки като празни.

Да предположим, че имате набор от данни, както е показано по -долу и искате да скриете стойността 0 във всички тези клетки (или искате да я замените с нещо като тире или текст „Не е налично“).

Въпреки че можете да направите това ръчно с малък набор от данни, ще ви трябват по -добри начини, когато работите с големи набори от данни.

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

Важна забележка: Когато скриете 0 в клетка, като използвате методите, показани в този урок, това ще скрие само 0 и няма да го премахне. Въпреки че клетките могат да изглеждат празни, тези клетки все още съдържат 0. И в случай, че използвате тези клетки (които имат скрити нули) във всяка формула, те ще бъдат използвани във формулата.

Нека да започнем и да се потопим във всеки от тези методи!

Автоматично скриване на нулева стойност в клетките

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

Всичко, което трябва да направите, е да премахнете отметката от квадратчето в опциите на Excel и промяната ще бъде приложена към целия работен лист.

Да предположим, че имате набора от данни за продажбите, както е показано по -долу и искате да скриете всички нулеви стойности и вместо това да покажете празни клетки.

По -долу са стъпките за скриване на нули от всички клетки в работна книга в Excel:

1. Отворете работната книга, в която искате да скриете всички нули
2. Щракнете върху раздела Файл
3. Кликнете върху Опции
4. В диалоговия прозорец с опции на Excel, който се отваря, щракнете върху опцията „Разширени“ в левия прозорец
5. Превъртете надолу до раздела „Опция за показване на този работен лист“ и изберете работния лист, в който искате да скриете нулите.
6. Премахнете отметката от опцията „Показване на нула в клетки с нулева стойност“
7. Щракнете върху OK

Горните стъпки незабавно биха скрили нули във всички клетки в избрания работен лист.

Тази промяна се прилага и за клетки, където нулата е резултат от формула.

Не забравяйте, че този метод скрива само стойността 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. Текст

И за всеки от тези типове данни можете да посочите формат.

В повечето случаи има формат по подразбиране - General, който поддържа всички тези типове данни видими. Но имате гъвкавостта да създадете специфичен формат за всеки от тези типове данни.

По -долу е структурата на персонализирания формат, която трябва да следвате, в случай че прилагате персонализиран формат към всеки от тези типове данни:

;;;

В метода, обхванат по -горе, съм посочил формата на:

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 в набора от данни за всички записи за региона West, виждате 0 в обобщената таблица за запад тук.

Ако искате да скриете нула в тази обобщена таблица, можете да използвате условно форматиране, за да направите това.

Условното форматиране в обобщената таблица в Excel обаче работи малко по -различно от обикновените данни.

Ето стъпките за прилагане на условно форматиране към обобщена таблица, за да скриете нули:

1. Изберете всяка клетка в колоната, която има 0 в обобщената обобщена таблица (Не избирайте всички клетки, а само една клетка)
2. Щракнете върху раздела Начало
3. В групата „Стилове“ кликнете върху „Условно форматиране“.
4. Поставете курсора върху опциите „Правила за маркиране на клетки“ и в опциите, които се показват, щракнете върху опцията „Равно на“.
5. В диалоговия прозорец „Равен на“ въведете 0 в лявото поле.
6. Изберете падащото меню Формат и кликнете върху Персонализиран формат.
7. В диалоговия прозорец „Форматиране на клетки“ изберете раздела Номер и кликнете върху опцията „Персонализиран“ в левия прозорец.
8. В опцията Персонализиран формат въведете следното в полето Тип: ;;; (три точки и запетая без място)
9. Щракнете върху OK. Ще видите малка икона за опции за форматиране вдясно от избраната клетка.
10. Кликнете върху тази икона „Опции за форматиране“
11. В опциите, които станат налични, изберете - Всички клетки, показващи стойностите „Сума от приходите“ за „Регион“.

Горните стъпки скриват нулата в обобщената таблица по такъв начин, че ако промените структурата на обобщената таблица (чрез добавяне на повече заглавки на редове/колони, нулите ще продължат да бъдат скрити).

Ако искате да научите повече за това как работи това, ето подробен урок за правилния начин прилага условно форматиране към обобщени таблици.

Когато клетките с изходни данни имат празни клетки

По -долу е пример, където имам набор от данни, който има празни клетки за всички записи за West.

Ако създам обобщена таблица, използвайки тези данни, ще получа резултата, както е показано по -долу.

По подразбиране обобщената таблица ви показва празни клетки, когато всички клетки в изходните данни са празни. Но в случай, че все още виждате 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, можете да ги изтриете, да ги замените с нещо друго или да промените цвета и да ги маркирате.

Скриване на нулите срещу Премахване на нулите

Когато третирате клетките с 0s в него, трябва да знаете разликата между скриването на тези и премахването на тези.

Когато скриете нулевата стойност в клетка, тя не се вижда от вас, но все пак остава в клетката. Това означава, че ако имате набор от данни и го използвате за изчисление, тази стойност 0 също ще бъде използвана при изчислението.

Напротив, когато имате празна клетка, използването й във формули може да означава, че Excel автоматично игнорира тези клетки (в зависимост от формулата, която използвате).

Например по -долу е това, което получавам, когато използвам формулата AVERAGE, за да получа средната стойност за две колони (резултатът е в ред 14). Колона А има клетки, където стойността 0 е скрита (но все още е там в клетката), а колона В има клетки, където стойността 0 е премахната.

Можете да видите разликата в резултата, въпреки факта, че наборът от данни за двата набора от данни изглежда еднакво.

Това се случва, тъй като формулата AVERAGE игнорира празни клетки в колона B, но използва тези в колона A (тъй като тези в колона A все още имат стойност 0).