Как да промените цвета на реда в Excel по условие, в зависимост от условията

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

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

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

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

Да предположим, че трябва да маркирате редове в различни нюанси, като се фокусирате върху това, което е написано в клетка в колоната "Количество", за да разберете ясно кои от транзакциите са най-печеливши. За да постигнете този резултат, трябва да използвате функцията „Условно форматиране“. Следвайте инструкциите стъпка по стъпка:

1. Изберете клетките, които искате да форматирате.
2. Създайте ново правило за форматиране, като щракнете върху съответния елемент в контекстното меню, което се появява след щракване върху бутона „Условно форматиране“ в раздела „Начало“.

1. След това ще се появи диалогов прозорец, където трябва да изберете настройката „използване на формула за определяне на форматирани клетки“. След това запишете следната формула: =$C2>4 в полето по-долу. Естествено, можете да вмъкнете свой собствен адрес на клетка и свой собствен текст, както и да замените знака > с < или =. Освен това е важно да не забравяте да поставите знака $ пред препратката към клетката, за да я коригирате при копиране. Това прави възможно обвързването на цвета на линията със стойността на клетката. В противен случай при копиране адресът ще се „премести“.
2. Кликнете върху „Форматиране“ и превключете към последния раздел, за да посочите желания нюанс. Ако не харесвате нюансите, предложени от програмата, винаги можете да кликнете върху „Още цветове“ и да изберете нюанса, от който се нуждаете.
3. След като завършите всички операции, трябва да щракнете двукратно върху бутона „OK“. Можете също така да зададете други типове форматиране (тип шрифт или специфичен стил на границата на клетка) в други раздели на този прозорец.
4. В долната част на прозореца има панел за визуализация, където можете да видите как ще изглежда клетката след форматиране.
5. Ако всичко ви устройва, щракнете върху бутона „OK“, за да приложите промените. Всичко, след извършване на тези действия, всички редове, в които клетките съдържат число, по-голямо от 4, ще бъдат сини.

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

Приложете няколко правила според техния приоритет

Предишният пример показа опция за използване на едно правило за условно форматиране, но може да искате да приложите няколко наведнъж. Какво да правим в такъв случай? Например, можете да добавите правило, според което линии с номер 10 или повече ще бъдат маркирани в розово. Тук е необходимо допълнително да запишете формулата =$C2>9и след това задайте приоритети, така че всички правила да могат да се прилагат, без да са в конфликт помежду си.

1. В раздела „Начало“ в групата „Стилове“ трябва да кликнете върху „Условно форматиране“ и в появилото се меню да изберете „Управление на правила“ в самия край на списъка.
2. След това трябва да покажете всички правила, специфични за този документ. За да направите това, трябва да намерите списъка в горната част „Показване на правилата за форматиране за“ и да изберете елемента „Този ​​лист“ там. Освен това чрез това меню можете да конфигурирате правила за форматиране за конкретни избрани клетки. В нашия случай трябва да управляваме правилата за целия документ.
3. След това трябва да изберете правилото, което искате да приложите първо, и да го преместите в горната част на списъка с помощта на стрелките. Ще получите такъв резултат.
4. След като зададете приоритетите, трябва да кликнете върху „OK“ и ще видим как съответните линии са променили цвета си според приоритета. Първо, програмата провери дали стойността в колоната Qty е по-голяма от 10 и ако не, дали е по-голяма от 4.

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

Да приемем, че докато работите с електронна таблица, вие изпитвате затруднения да следите бързо кои елементи вече са доставени и кои не. Или може би някои са остарели. За да опростите тази задача, можете да опитате да изберете редове въз основа на текста, който е в клетката „Доставка“. Да предположим, че трябва да зададем следните правила:

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

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

Като цяло логиката на действията за доставени и просрочени поръчки ще бъде същата като в описания по-горе пример. Необходимо е да се предпишат формули в прозореца за условно форматиране =$E2=»Доставено» и =$E2=»Просрочени» съответно. Малко по-трудна задача за сделки, които изтичат след няколко дни.

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

За този случай има функция =ТЪРСЕНЕ(“Дължимо”, $E2)>0, където:

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

В английската версия е известен като =SEARCH. Той е предназначен да търси клетки, които частично отговарят на въведената заявка.

Съвет: Параметърът >0 във формулата означава, че няма значение къде се намира входната заявка в текста на клетката.

Например колоната „Доставка“ може да съдържа текста „Спешно, срок до 6 часа“ и съответната клетка пак ще бъде форматирана правилно.

Ако е необходимо да се приложат правила за форматиране към редове, където ключовата клетка започва с желаната фраза, тогава трябва да напишете =1 във формулата вместо >0. 

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

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

Точно както при ред, стъпките по-горе могат да се приложат към отделна клетка или диапазон от стойности. В този пример форматирането се прилага само към клетките в колоната „Номер на поръчката“:

Как да приложите множество условия за форматиране

Ако трябва да приложите няколко правила за условно форматиране към низове, тогава вместо да пишете отделни правила, трябва да създадете едно с формули = ИЛИ or =И. Първото означава „едно от тези правила е вярно“, а второто означава „и двете от тези правила са верни“.

В нашия случай пишем следните формули:

=ИЛИ($F2=»Дължим до 1 дни», $F2=»Дължим до 3 дни»)

=ИЛИ($F2=»Дължим до 5 дни», $F2=»Дължим до 7 дни»)

И формулата =И може да се използва, например, за да се провери дали числото в колоната Qty е. по-голямо или равно на 5 и по-малко или равно на 10.

Потребителят може да използва повече от едно условие във формулите.

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