2 начина за промяна на цвета на запълване на клетки в Excel въз основа на техните стойности

В тази статия ще намерите два бързи начина за промяна на цвета на клетка въз основа на нейната стойност в Excel 2013, 2010 и 2007. Също така ще научите как да използвате формули в Excel, за да промените цвета на празни клетки или клетки с грешки във формулата.

Всеки знае, че за да промените цвета на запълване на една клетка или цял диапазон в Excel, просто щракнете върху бутона Цвят на запълване (Цвят на запълване). Но какво ще стане, ако трябва да промените цвета на запълване на всички клетки, съдържащи определена стойност? Освен това, какво ще стане, ако искате цветът на запълване на всяка клетка да се променя автоматично, когато се променя съдържанието на тази клетка? По-нататък в статията ще намерите отговори на тези въпроси и ще получите няколко полезни съвета, които ще ви помогнат да изберете правилния метод за решаване на всеки конкретен проблем.

• Променете цвета на запълване на клетките въз основа на техните стойности (динамично) – Цветът на запълване ще се промени автоматично, когато стойността на клетката се промени.
• Промяна на цвета на клетка въз основа на текущата й стойност (статично) – Веднъж зададен, цветът на запълване не се променя, без значение как се променя стойността на клетката.
• Промяна на цвета на определени клетки (празни, с грешки, с формули)

Как динамично да промените цвета на клетка в Excel въз основа на нейната стойност

Цветът на запълване ще се промени в зависимост от стойността на клетката.

Проблем: Имате таблица или диапазон от данни и искате да промените цвета на запълване на клетките въз основа на техните стойности. Освен това е необходимо този цвят да се променя динамично, отразявайки промените в данните в клетките.

Решение: Използвайте условно форматиране в Excel, за да подчертаете стойности, по-големи от X, по-малки от Y или между X и Y.

Да приемем, че имате списък с цени на бензина в различни щати и искате цени, които са по-високи от $ 3.7, бяха подчертани в червено и по-малки или равни $ 3.45 - зелено.

Забележка: Екранните снимки за този пример са направени в Excel 2010, но в Excel 2007 и 2013 бутоните, диалоговите прозорци и настройките ще бъдат абсолютно същите или малко по-различни.

И така, ето какво трябва да направите стъпка по стъпка:

1. Изберете таблицата или диапазона, в който искате да промените цвета на запълване на клетката. В този пример подчертаваме $B$2:$H$10 (не са избрани заглавията на колоните и първата колона, съдържаща имената на държавите).
2. Щракнете върху Начало (Начало), в раздела Стилове (Стилове) щракнете Условно форматиране (Условно форматиране) > Нови правила (Създаване на правило).
3. В горната част на диалоговия прозорец Ново правило за форматиране (Създаване на правило за форматиране) в полето Изберете тип правило (Изберете тип правило) изберете Форматирайте само клетки, които съдържат (Форматирайте само клетки, които съдържат).
4. В долната част на диалоговия прозорец в полето Форматиране само на клетки с (Форматирайте само клетки, които отговарят на следното условие) Задайте условията за правилото. Избираме да форматираме само клетки с условието: Стойност на клетката (стойност на клетка) – по-голяма от (Повече ▼) - 3.7както е показано на фигурата по-долу.След това натиснете бутона Размер (Формат), за да изберете кой цвят за запълване да се приложи, ако определеното условие е изпълнено.
5. В диалоговия прозорец, който се появява Форматиране на клетки Раздел (Форматиране на клетки). Напълнете (Попълнете) и изберете цвят (избрахме червеникаво) и щракнете OK.
6. След това ще се върнете към прозореца Ново правило за форматиране (Създаване на правило за форматиране) където в полето Предварителен преглед (Пример) ще покаже пример от вашето форматиране. Ако сте доволни, щракнете OK.

Резултатът от вашите настройки за форматиране ще изглежда по следния начин:

Тъй като трябва да настроим друго условие, което ни позволява да променим цвета на запълване на зелен за клетки със стойности, по-малки или равни на 3.45, след което натиснете бутона отново Нови правила (Създаване на правило) и повторете стъпки от 3 до 6, като зададете желаното правило. По-долу е извадка от второто правило за условно форматиране, което създадохме:

Когато всичко е готово – щракнете OK. Вече имате добре форматирана таблица, която ви позволява да видите с един поглед максималните и минималните цени на бензина в различните държави. Браво на тях там, в Тексас! 🙂

Съвет: По същия начин можете да промените цвета на шрифта в зависимост от стойността на клетката. За да направите това, просто отворете раздела Font (Шрифт) в диалоговия прозорец Форматиране на клетки (Формат на клетка), както направихме в стъпка 5 и изберете желания цвят на шрифта.

Как да зададете постоянен цвят на клетка въз основа на текущата й стойност

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

Проблем: Искате да коригирате цвета на клетка въз основа на текущата й стойност и искате цветът на запълване да остане същият, дори когато стойността на клетката се промени.

Решение: Намерете всички клетки с конкретна стойност (или стойности), като използвате инструмента Намери всички (Намери всички) и след това променете формата на намерените клетки с помощта на диалоговия прозорец Форматиране на клетки (формат на клетка).

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

Намерете и изберете всички клетки, които отговарят на дадено условие

Тук са възможни няколко сценария в зависимост от типа стойност, която търсите.

Ако искате да оцветите клетки с конкретна стойност, например, 50, 100 or 3.4 – след това на табл Начало (Начало) в секцията Редактиране (Редактиране) щракнете Намерете Изберете (Намерете и маркирайте) > Какво (Намирам).

Въведете желаната стойност и щракнете Намери всички (Намери всички).

Съвет: От дясната страна на диалоговия прозорец Търсене и заместване (Намери и замени) има бутон Настроики (Опции), с натискането на който ще имате достъп до редица разширени настройки за търсене, като напр Съвпадение (малки и малки букви) и Съвпадение на цялото съдържание на клетка (Цялата клетка). Можете да използвате заместващи знаци, като звездичката (*), за да съпоставите произволен низ от знаци, или въпросителния знак (?), за да съпоставите всеки отделен знак.

По отношение на предишния пример, ако трябва да намерим всички цени на бензина от 3.7 да се 3.799, тогава ще зададем следните критерии за търсене:

Сега щракнете върху някой от намерените елементи в долната част на диалоговия прозорец Търсене и заместване (Намиране и замяна) и щракнете Ctrl + Aза да маркирате всички намерени записи. След това натиснете бутона Fermer (Близо).

Ето как можете да изберете всички клетки с дадена стойност (стойности), като използвате опцията Намери всички (Намиране на всички) в Excel.

В действителност обаче трябва да намерим всички цени на бензина, които надвишават $ 3.7. За съжаление инструментът Търсене и заместване (Намиране и замяна) не може да ни помогне с това.

Променете цветовете за запълване на избраните клетки, като използвате диалоговия прозорец Форматиране на клетки

Вече имате избрани всички клетки с дадена стойност (или стойности), току-що направихме това с инструмента Търсене и заместване (Намерете и заменете). Всичко, което трябва да направите, е да зададете цвета на запълване на избраните клетки.

Отворете диалогов прозорец Форматиране на клетки (Формат на клетка) по един от 3 начина:

• натискане Ctrl + 1.
• като щракнете върху която и да е избрана клетка с десния бутон на мишката и изберете елемента от контекстното меню Форматиране на клетки (формат на клетка).
• етикет Начало (Начало) > клетки. клетки. (клетки) > Размер (Формат) > Форматиране на клетки (формат на клетка).

След това коригирайте опциите за форматиране, както желаете. Този път ще зададем цвета на запълване на оранжев, само за промяна 🙂

Ако искате да промените само цвета на запълване, без да докосвате останалите опции за форматиране, можете просто да щракнете върху бутона Цвят на запълване (Цвят за запълване) и изберете цвета, който харесвате.

Ето резултата от нашите промени във форматирането в Excel:

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

Промяна на цвета на запълване на специални клетки (празни, с грешка във формулата)

Както в предишния пример, можете да промените цвета на запълване на конкретни клетки по два начина: динамично и статично.

Използвайте формула, за да промените цвета на запълване на специални клетки в Excel

Цветът на клетката ще се промени автоматично в зависимост от стойността на клетката.

Най-вероятно ще използвате този метод за решаване на проблема в 99% от случаите, тоест пълненето на клетките ще се промени в съответствие с условието, което сте посочили.

Например, нека отново вземем таблицата с цените на бензина, но този път ще добавим още няколко състояния и ще направим някои клетки празни. Сега вижте как можете да намерите тези празни клетки и да промените цвета на запълването им.

1. В раздела Разширени Начало (Начало) в секцията Стилове (Стилове) щракнете Условно форматиране (Условно форматиране) > Нови правила (Създаване на правило). Точно както във втората стъпка от примера Как динамично да промените цвета на клетка въз основа на нейната стойност.
2. В диалоговия прозорец Ново правило за форматиране (Създаване на правило за форматиране) изберете опция Използвайте формула, за да определите кои клетки да форматирате (Използвайте формула, за да определите кои клетки да форматирате). По-навътре в полето Форматиране на стойности, където тази формула е вярна (Форматирайте стойности, за които е вярна следната формула) въведете една от формулите:
   • за промяна на запълването на празните клетки

     =ISBLANK()

     =ЕПУСТО()

   • за промяна на оцветяването на клетките, съдържащи формули, които връщат грешка

     =ISERROR()

     =ЕОШИБКА()

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

   =ISBLANK(B2:H12)

   =ЕПУСТО(B2:H12)

   

3. преса Размер (Форматиране), изберете желания цвят на запълване в раздела Напълнете (Попълнете) и след това щракнете OK. Подробни инструкции са дадени в стъпка 5 от примера „Как да промените динамично цвета на клетка въз основа на нейната стойност.“ Пример за условното форматиране, което сте задали, ще изглежда по следния начин:
4. Ако сте доволни от цвета, щракнете OK. Ще видите как създаденото правило веднага ще бъде приложено към таблицата.

Променете статично цвета на запълване на специални клетки

Веднъж конфигуриран, запълването ще остане непроменено, независимо от стойността на клетката.

Ако искате да зададете постоянен цвят за запълване на празни клетки или клетки с формули, които съдържат грешки, използвайте този метод:

1. Изберете таблица или диапазон и щракнете F5за да отворите диалоговия прозорец Отидете в (Скок), след което натиснете бутона Специален (Осветете).
2. В диалоговия прозорец Отидете на Special (Изберете група клетки) отметнете опцията заготовки (Празни клетки), за да изберете всички празни клетки.Ако искате да маркирате клетки, съдържащи формули с грешки, отметнете опцията формули (формули) > грешки (Грешки). Както можете да видите на снимката по-горе, има много други настройки, достъпни за вас.
3. Накрая променете запълването на избраните клетки или задайте други опции за форматиране, като използвате диалоговия прозорец Форматиране на клетки (Форматиране на клетки), както е описано в Промяна на запълването на избрани клетки.

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