2 начина за промяна на фона в Excel

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

Всеки знае, че редактирането на фона на проста клетка е проста процедура. Кликнете върху „цвят на фона“. Но какво ще стане, ако имате нужда от корекция на цвета въз основа на определено съдържание на клетка? Как мога да накарам това да се случи автоматично? Това, което следва, е поредица от полезна информация, която ще ви позволи да намерите правилния начин за изпълнение на всички тези задачи.

Динамична промяна на цвета на фона на клетката

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

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

Да приемем, че имате набор от продукти в различни щати с техните цени и трябва да знаете кой от тях струва повече от $3,7. Затова решихме да маркираме продуктите, които са над тази стойност, в червено. И клетките, които имат подобна или по-голяма стойност, беше решено да се оцветят в зелен оттенък.

Забележка: Екранната снимка е направена във версия 2010 на програмата. Но това не засяга нищо, тъй като последователността от действия е една и съща, независимо коя версия - най-новата или не - използва лицето.

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

1. Изберете клетките, в които нюансът трябва да се редактира. Например диапазонът $B$2:$H$10 (имената на колоните и първата колона, която изброява имената на щатите, са изключени от извадката).

2. Кликнете върху "Начало" в група „Стил“. Ще има артикул „Условно форматиране“. Там също трябва да изберете "Ново правило". В английската версия на Excel последователността от стъпки е следната: „Начало“, „Група стилове“, „Условно форматиране > Ново правило».

3. В прозореца, който се отваря, поставете отметка в квадратчето „Форматиране само на клетки, които съдържат“ („Форматиране само на клетки, които съдържат“ в английската версия).

4. В долната част на този прозорец под надписа „Форматиране само на клетки, които отговарят на следното условие“ (Форматирайте само клетки с) можете да зададете правила, по които ще се извършва форматирането. Избрали сме формата за посочената стойност в клетките, която трябва да е по-голяма от 3.7, както можете да видите от екранната снимка: 

5. След това щракнете върху бутона „Формат“. Ще се появи прозорец със зона за избор на цвят на фона отляво. Но преди това трябва да отворите раздела „Попълване“ («Попълнете»). В случая е червено. След това кликнете върху бутона "OK".

6. След това ще се върнете към прозореца „Ново правило за форматиране“, но вече в долната част на този прозорец можете да видите как ще изглежда тази клетка. Ако всичко е наред, трябва да кликнете върху бутона "OK".

В резултат на това получавате нещо подобно:

След това трябва да добавим още едно условие, тоест да променим фона на клетки със стойности по-малки от 3.45 на зелен. За да изпълните тази задача, трябва да кликнете върху „Ново правило за форматиране“ и повторете стъпките по-горе, само условието трябва да бъде зададено като „по-малко от или равностойно“ (в английската версия „по-малко или равно на“, след което напишете стойността. В края трябва да кликнете върху бутона „OK“.

Сега таблицата е форматирана по този начин.

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

Препоръка: Ако е необходимо, можете да използвате подобен метод на форматиране, като редактирате не фона, а шрифта. За да направите това, в прозореца за форматиране, който се появи на петия етап, трябва да изберете раздела «Шрифт» и следвайте подканите, дадени в прозореца. Всичко е интуитивно ясно и дори начинаещ може да го разбере.

В резултат на това ще получите таблица като тази:

Как да запазим цвета на клетката същия, дори ако стойността се промени?

Task: Трябва да оцветите фона така, че никога да не се променя, дори ако фонът се промени в бъдеще.

Решение: намерете всички клетки с конкретен номер с помощта на функцията на Excel „Намери всички“ „Намери всички“ или добавка „Избор на специални клетки“ („Избор на специални клетки“) и след това редактирайте формата на клетката с помощта на функцията „Форматиране на клетки“ («Форматиране на клетки»).

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

Изберете всички клетки, които съдържат определено условие

Има няколко възможни метода в зависимост от вида на конкретната стойност, която трябва да се намери.

Ако трябва да обозначите клетки с конкретна стойност със специален фон, трябва да отидете в раздела "Начало" И изберете „Намери и избери“ – „Намери“.

Въведете необходимите стойности и щракнете върху „Намери всички“.

Помогне: Можете да щракнете върху бутона "Настроики" вдясно, за да се възползвате от някои допълнителни настройки: къде да търсите, как да преглеждате, дали да зачитате главни и малки букви и т.н. Можете също да пишете допълнителни знаци, като звездичка (*), за да намерите всички редове, съдържащи тези стойности. Ако използвате въпросителен знак, можете да намерите всеки отделен знак.

В предишния ни пример, ако искахме да намерим всички котировки за гориво между $3,7 и $3,799, бихме могли да прецизираме нашата заявка за търсене.

Сега изберете някоя от стойностите, които програмата намери в долната част на диалоговия прозорец, и щракнете върху една от тях. След това натиснете клавишната комбинация „Ctrl-A“, за да изберете всички резултати. След това щракнете върху бутона „Затвори“. 

Ето как да изберете всички клетки с конкретни стойности с помощта на функцията Намери всички. В нашия пример трябва да намерим всички цени на горивата над $3,7 и за съжаление Excel не ви позволява да направите това с помощта на функцията Find and Replace.

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

• намиране на всички стойности в определен диапазон, например между -1 и 45,
• вземете максималната или минималната стойност в колона,
• намиране на низ или диапазон,
• намирайте клетки по цвят на фона и много повече.

След като инсталирате добавката, просто щракнете върху бутона „Избор по стойност“ („Избор по стойност“) и след това прецизирайте заявката за търсене в прозореца на добавката. В нашия пример търсим числа, по-големи от 3,7. Натиснете "Избирам" („Изберете“) и след секунда ще получите резултат като този:

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

Промяна на фона на избраните клетки през прозореца „Форматиране на клетки“.

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

За да направите това, трябва да отворите прозорец „Формат на клетка“като натиснете клавиша Ctrl + 1 (можете също да щракнете с десния бутон върху избраните клетки и да щракнете с левия бутон върху елемента „форматиране на клетки“) и коригирайте форматирането, от което се нуждаете.

Ние ще изберем оранжев нюанс, но вие можете да изберете всеки друг.

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

Резултатът е таблица като тази:

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

Редактиране на цвета на фона за специални клетки (празни или с грешки при писане на формула)

Подобно на предишния пример, потребителят има възможност да редактира цвета на фона на специални клетки по два начина. Има статични и динамични опции.

Прилагане на формула за редактиране на фон

Тук цветът на клетката ще се редактира автоматично въз основа на нейната стойност. Този метод помага много на потребителите и е търсен в 99% от ситуациите.

Като пример можете да използвате предишната таблица, но сега някои от клетките ще бъдат празни. Трябва да определим кои от тях не съдържат показания и да редактираме цвета на фона.

1. В раздела "Начало" трябва да кликнете върху „Условно форматиране“ ->  "Ново правило" (същото като в стъпка 2 от първия раздел „Променете динамично цвета на фона“.

2. След това трябва да изберете елемента „Използвайте формула, за да определите...“.

3. Въведете формула =IsBlank() (ISBLANK във версията), ако искате да редактирате фона на празна клетка, или =IsError() (ISERROR във версията), ако трябва да намерите клетка, в която има погрешно написана формула. Тъй като в този случай трябва да редактираме празни клетки, въвеждаме формулата =IsBlank(), след което поставете курсора между скобите и щракнете върху бутона до полето за въвеждане на формула. След тези манипулации трябва ръчно да се избере набор от клетки. Освен това можете сами да посочите диапазона, напр. =празен е(B2:H12).

4. Щракнете върху бутона „Форматиране“ и изберете подходящия цвят на фона и направете всичко, както е описано в параграф 5 от раздела „Динамична промяна на цвета на фона на клетката“, след което щракнете върху „OK“. Там можете да видите и какъв ще бъде цветът на клетката. Прозорецът ще изглежда нещо подобно.

5. Ако харесвате фона на клетката, трябва да кликнете върху бутона „OK“ и промените ще бъдат направени веднага в таблицата.

Статична промяна на цвета на фона на специални клетки

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

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

1. Изберете вашия документ или няколко клетки и натиснете F5, за да отворите прозореца Отиди на, след което натиснете бутона „Открояване“.
2. В диалоговия прозорец, който се отваря, изберете бутона „Празни клетки“ или „Празни клетки“ (в зависимост от версията на програмата – или английска), за да изберете празни клетки.
3. Ако трябва да маркирате клетки, които имат формули с грешки, трябва да изберете елемента "Формули" и оставете едно квадратче за отметка до думата „Грешки“. Както следва от екранната снимка по-горе, клетките могат да бъдат избрани според всякакви параметри и всяка от описаните настройки е достъпна, ако е необходимо.
4. И накрая, трябва да промените цвета на фона на избраните клетки или да ги персонализирате по друг начин. За да направите това, използвайте метода, описан по-горе.

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

Как да извлечете максимума от Excel?

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

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

Ако комбинирате тези инструменти с основната функционалност на Excel, можете да постигнете много страхотни резултати. Например можете да разберете кои цени на горивата са се променили и след това да намерите дубликати във файла за последната година.

Виждаме, че условното форматиране е удобен инструмент, който ви позволява да автоматизирате работата върху таблици без никакви специфични умения. Вече знаете как да попълвате клетки по няколко начина въз основа на тяхното съдържание. Сега остава само да го приложим на практика. Късмет!