С командата "отивам" можете бързо да намерите и изберете всички клетки, които съдържат данни от определен тип, като например формули. Освен това, за да намерите само онези клетки, които съвпадат определени условия(напр. последната клетка в лист, съдържаща данни или форматиране, използвайте команда Go).

Следвайте стъпките по-долу.

Екип	Да подчертая
Бележки
Константи
Формули	Забележка:С квадратчетата за отметка по-долу формулитипът на формулата се определя.
празен	Празни клетки.
текущ регион	текущия обхват, като например целия списък.
текущ масив	целия масив, ако активната клетка се съдържа в масива.
Обекти	Графични обекти, включително диаграми и бутони, върху листа и в текстови полета.
Разлики между низовете	Всички клетки, които са различни от активната клетка в избрания ред. Винаги има една активна клетка в избраната област - тя е диапазон, ред или колона. С натискане на клавиша ENTER или TAB можете да промените местоположението на активната клетка, която по подразбиране е първата клетка в реда. Ако е избран повече от един ред, сравнението се извършва за всеки отделен ред от селекцията и клетката, използвана при сравнението за всеки допълнителен ред, е в същата колона като активната клетка.
Разлики между колоните	Всички клетки, които са различни от активната клетка в избраната колона. Селекцията винаги има активна клетка, независимо дали е диапазон, ред или колона. Чрез натискане на клавиша ENTER или TAB можете да промените местоположението на активната клетка, която по подразбиране е първата клетка в колона. Ако изберете повече от една колона, сравнението се извършва за всяка отделна колона от селекцията. Клетката, използвана при сравнението за всяка допълнителна колона, е в същия ред като активната клетка.
Влияещи	Клетки, посочени от формулата в активната клетка. В глава зависимелементи, направете едно от следните неща: Кликнете само прави линииза намиране само на клетките, посочени от формулата. Кликнете всички ниваза намиране на всички клетки, пряко или косвено посочени от клетки в селекцията.
Зависим	Клетки с формули, които се отнасят до активна клетка. Направете едно от следните неща: Кликнете само прави линииза намиране само на клетки с формули, които се отнасят директно към активната клетка. Кликнете всички ниваза намиране на всички клетки, които пряко или косвено препращат към активната клетка.
последната клетка	Последната клетка на работен лист, която съдържа данни или форматиране.
Само видими клетки	Само клетки, видими в диапазон, който се пресича със скрити редове и колони.
Условни формати	Само клетки с приложено условно форматиране. В глава Потвърждаване на даннинаправете едно от следните: Щракнете върху бутона всичкоза да намерите всички клетки, които имат приложени условни формати. За да търсите клетки със същото условно форматиране, маркирано в текущата клетка, щракнете единот тях.
Потвърждаване на данни	Прилагат се само клетки, които съдържат правила за валидиране на данни. Направете едно от следните неща:

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

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

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

Да предположим, че имаме следната таблица с фирмени поръчки:

Искаме да оцветим редовете с различни цветове в зависимост от поръчаното количество (стойността в колоната Кол.), за да подчертаете най-важните поръчки. Инструментът Excel ще ни помогне да се справим с тази задача - " Условно форматиране».

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

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

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

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

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

За да опростим контрола на изпълнението на поръчките, можем да маркираме в нашата таблица редове с поръчки с различен статус на доставка с различни цветове, информацията за които се съдържа в колоната Доставка:

• Ако датата на доставка на поръчката е в бъдещето (стойност Край след X дни), тогава запълването на такива клетки трябва да е оранжево;
• Ако поръчката е доставена (стойност Доставено), тогава запълването на такива клетки трябва да е зелено;
• Ако датата на доставка на поръчката е в миналото (стойност Просрочие), тогава запълването на такива клетки трябва да е червено.

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

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

=$E2="Доставено"
=$E2="Просрочени"

Задачата звучи по-трудно за поръчки, които трябва да бъдат доставени хдни (стойност Край след X дни). Виждаме, че времето за доставка на различни поръчки е 1, 3, 5 или повече дни, което означава, че горната формула не важи тук, тъй като е насочена към точна стойност.

В този случай е удобно да използвате функцията ТЪРСЕНЕ(ТЪРСЕНЕ) и за да намерите частично съвпадение, напишете следната формула:

ТЪРСЕНЕ("Дължимо до";$E2)>0
=SEARCH("Дължимо до",$E2)>0

В тази формула E2- това е адресът на клетката, въз основа на стойността на която ще приложим правилото за условно форматиране; знак за долар $ необходими за прилагане на формулата към цялата линия; състояние" >0 ” означава, че правилото за форматиране ще бъде приложено, ако посоченият текст (в нашия случай „Дължим до”) бъде намерен.

улика:Ако формулата използва условието „ >0 “, тогава редът ще бъде маркиран в цвят всеки път, когато посоченият текст бъде открит в ключовата клетка, независимо къде се намира в клетката. В примерната таблица на фигурата по-долу колоната Доставка(колона F) може да съдържа текста „Спешно, срок за 6 часа“ (което означава Спешно, доставка в рамките на 6 часа) и този ред също ще бъде оцветен.

За да маркирате с цвят онези редове, в които съдържанието на ключовата клетка започва с посочения текст или символи, формулата трябва да бъде написана в следната форма:

SEARCH("Дължимо до";$E2)=1
=SEARCH("Дължимо до",$E2)=1

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

И така, следвайки същите стъпки като в , създадохме три правила за форматиране и нашата таблица започна да изглежда така:

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

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

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

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

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

Например, можем да маркираме поръчките, които се очакват в рамките на 1 и 3 дни в розово, а тези с падеж в рамките на 5 и 7 дни в жълто. Формулите ще изглеждат така:

ИЛИ($F2="Срок след 1 дни";$F2="Срок след 3 дни")
=ИЛИ($F2="Срок след 1 дни",$F2="Срок след 3 дни")

ИЛИ($F2="Срок след 5 дни";$F2="Срок след 7 дни")
=ИЛИ($F2="Срок след 5 дни",$F2="Срок след 7 дни")

За да маркирате поръчки с количество минимум 5, но не повече от 10 (стойността в колоната Кол.), записваме формулата с функцията И(И):

И($D2>=5;$D2<=10)
=И($D2>=5,$D2<=10)

Разбира се, във вашите формули можете да използвате не непременно две, но толкова условия, колкото е необходимо. Например:

ИЛИ($F2="Срок след 1 дни";$F2="Срок след 3 дни";$F2="Срок след 5 дни")
=ИЛИ($F2="Срок след 1 дни",$F2="Срок след 3 дни",$F2="Срок след 5 дни")

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

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

Трябва да маркирате дублиращи се стойности в колона? Трябва да изберете първите 5 максимални клетки? Необходимо ли е да се направи термична скала за яснота (цветът се променя в зависимост от увеличаването / намаляването на стойността на клетките)? В Excel маркирането на клетки по условия може да стане много бързо и лесно. Специалната функция "Условно форматиране" е отговорна за подчертаването на клетките с цвят. Горещо препоръчвам! Прочетете повече на:

Описах основните характеристики в началото на статията, но всъщност има много от тях. Повече за най-полезните

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

При щракване ще се отвори меню с различни опции за тази редакция. Както можете да видите, тук наистина има много възможности.

Сега повече за най-полезните:

Excel подчертава клетки по условия. Прости термини

За да направите това, отидете на елемента Правила за избор на клетки. Ако например трябва да изберете всички клетки, по-големи от 100, щракнете върху бутона Още. В прозореца:

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

Маркиране на дублирани стойности, вкл. в множество колони

За да маркирате всички дублирани стойности, изберете съответния елемент от менюто Дублирани стойности.

Какво да направите, ако трябва да намерите повторения в две или повече колони, например, когато пълното име е в различни колони? Направете друга колона и комбинирайте стойностите с формулата = , т.е. в отделна клетка ще сте написали IvanovIvanIvanych. Чрез такава колона вече можете лесно да маркирате дублирани стойности. Важно е да се разбере, че ако словоредът е различен, тогава Excel ще счита такива редове за неповтарящи се (например Иван Иванич Иванов).

Цветно подчертаване на първата/последната стойност. Отново условно форматиране

За да направите това, отидете на Правилата за избор на първата и последната клетка и изберете желания елемент. В допълнение към факта, че можете да маркирате първите / последните стойности (включително проценти), можете да използвате възможността да маркирате данни над и под средното (аз го използвам дори по-често). Много удобно за преглед на резултати, които се отклоняват от нормата или средната!

Построяване на термична диаграма и хистограма

Страхотна функция за визуализация на данни е топлинната/температурната диаграма. Долната линия е, че в зависимост от стойността на стойността в колона или ред, клетката се подчертава с определен нюанс на цвят, колкото повече, толкова по-червено, например. Масите се възприемат много по-добре от око и вземането на решения става по-лесно. Всъщност един от най-добрите анализатори често е нашето око, съответно мозъкът, а не машина!

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

Маркирайте клетки, съдържащи конкретен текст

Много често трябва да намерите клетки, които съдържат определен набор от знаци, можете разбира се да използвате функцията = , но е по-лесно и по-бързо да приложите условно форматиране, отидете - Правила за избиране на клетки - Текстът съдържа

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

Excel подчертаване. Филтрирайте по цвят

В допълнение към горните опции можете да филтрирате избраните клетки по цвят, като използвате обикновен филтър. За моя изненада, много малко хора знаят за това - очевидно ехо от версията от 2003 г. - тази функция я нямаше.

Проверка на условията за форматиране

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

Невалиден диапазон на условно форматиране

важно!Условното форматиране, когато се използва неправилно, често е причина за силни . Има удвояване на форматирането, например, ако копирате клетки с подчертаване много пъти. Тогава ще имате много условия с цвета. Аз самият видях повече от 3 хиляди условия - файлът се забави грозно. Освен това файлът може да се забави, когато диапазонът е зададен както на снимката по-горе, по-добре е да посочите A: A - за целия диапазон.

Прочетете повече за спирачките на Excel и техните причини. Тази статия е помогнала на повече от сто души;)

Надяваме се, че е било полезно, съжалявам!

Споделете нашата статия във вашите социални мрежи:

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

Инструкции за Excel 2010

ВКЛЮЧИ СУБТИТРИ!

Как да го направя в Excel 2007

ВКЛЮЧИ СУБТИТРИ!
Изберете клетките с цените на поръчките и като кликнете върху стрелката до бутона "Условно форматиране", изберете "Създаване на правило".

Нека изберем четвъртия елемент, който ни позволява да сравним текущите стойности със средните. Интересуваме се от стойности над средните. Като щракнете върху бутона "Форматиране", задайте цвета на клетките.


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


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


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


„ОК“ и получихме весело декорирана маса, която ви позволява визуално да проследявате напредъка на поръчките.


Забелязали ли сте, че статусите се задават чрез избор от падащ списък със стойности? Как да направите такива списъци, казахме в инструкциите.

Как да го направя в Excel 2003

ВКЛЮЧИ СУБТИТРИ!
"Условно форматиране" в менюто "Форматиране". Това ще изисква малко повече ръчна работа. Ето как ще изглеждат настройките за първата ни задача - да попълним клетките със стойности над средните.


Ще трябва ръчно да въведете функцията "=AVERAGE()", да поставите курсора между скобите, да щракнете върху бутона до него и да използвате мишката, за да изберете желания диапазон.
Но принципът на действие е същият.
Покорете Excel и ще се видим скоро!

За да поставите правилно акцентите в документ, направен в MS Excel, можете да увеличите размера на клетките, да изберете различен шрифт или размер на буквата или да рисувате върху квадратите, на които определено трябва да обърнете внимание.

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

Обикновено блоково попълване

Оцветяването на един или повече блокове в Excel не е трудно. Първо ги изберете и в раздела Начало щракнете върху стрелката до кофата за боя, за да разширите списъка. Изберете подходящ цвят от там и ако нищо не ви подхожда, щракнете върху „Други цветове“.

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

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

В зависимост от въведените данни

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

Текст

Да вземем следната таблица като пример. Нека направим така, че червеното да съответства на ябълката, жълтото - на крушата, а оранжевото - на портокала.

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

Този прозорец се отваря. Изберете типа в горната част „Форматиране само на клетки, които съдържат“, по-нататък също ще го празнуваме. По-долу посочваме условията: имаме текст, който съдържа определени думи. В последното поле или щракнете върху бутона и посочете клетка, или въведете текст.

Разликата е, че чрез поставяне на препратка към клетка (=$B$4), условието ще се промени в зависимост от това какво е въведено в нея. Например, вместо ябълка в B4, ще посоча касис, правилото ще се промени съответно и блоковете със същия текст ще бъдат боядисани. И ако въведете ябълка в полето, тогава ще се търси тази конкретна дума и няма да зависи от нищо.

Тук изберете цвят за запълване и щракнете върху OK. За да видите всички опции, щракнете върху бутона "Други".

Правилото е създадено и го запазете, като натиснете бутона OK.

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

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

Числен

Сега нека се занимаваме с числени данни. Нека поставим числата в колона D на определен фон според условието, което сме задали.

Изберете колона, създайте правило, посочете неговия тип. След това пишем - „Стойност“ „по-голяма от“ „15“. Можете да въведете последното число ръчно или да посочите адреса на клетката, от която ще бъдат взети данните. Вземаме решение за запълването, щракнете върху „OK“.

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

Нека зададем още правила за избраните клетки - изберете "Управление на правила".

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

Когато всичко е готово, щракнете върху "Прилагане" и "ОК".

Всичко работи, стойности равни на и под 15 са боядисани в бледо синьо.

Тук разликата между вносни и продавани стоки също би била ясно видима. Изберете две колони и щракнете „Правила за избор на клетки“ – „Дублиращи се стойности“. Изберете правилния цвят. След това, ако две клетки една до друга са оцветени, това означава, че всички внесени плодове са продадени.

Нека използваме примера на последната колона, за да покажем как да променим цвета на клетката в зависимост от зададената стойност. Изберете ги и отидете на "Управление на правила".

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

Изберете желаните елементи в прозореца, който се отваря. Ще запълня с тъмно зелено всички стойности, които са по-големи от 90. Тъй като посочих адреса в последното поле (=$F$15), когато числото 90 в клетката се промени, например, на 110, правилото ще също се променят. Запазете промените, като щракнете върху бутона OK.

Ще създам друго правило, но в него ще маркирам в светло зелено всичко, което е по-малко или равно на 90. Не забравяйте да запазите всичко, като щракнете върху бутоните долу вдясно.

Ето какво стана. От последната колона визуално можете бързо да определите печалбата от продажбата на кой продукт е била по-голяма.

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

Мисля, че забелязахте, че правилото се създава не само за текст и числа, но и за дати и в зависимост от попълването и наличието на грешки в клетките. Посочете условие, изберете цвят и запазете.

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