Оцветете клетка по условие или формула. Попълване на клетки в зависимост от стойността в Microsoft Excel Как да маркирам ред в Excel с цвят

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

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

Задача1 - текстови стойности

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

Решение1

Нека създадем малка таблица със статусите на заданията в диапазона E6:E9 .

Изберете диапазон от клетки A7:C17 , съдържащ списък с произведения, и инсталирайте през менюто Начало/ Цвят на запълванефонът на запълване е червен (приемаме, че всички работи първоначално са в състояние Не е започнало).

Уверете се, че диапазонът от клетки е избран A7:C17 (A7 трябва да е ). Извикайте командата от менюто Условно форматиране / Създаване на правило / Използване на формула за определяне на клетки за форматиране .

• в полето" Форматирайте стойности, за които е вярна следната формула» трябва да въведете =$C7=$E$8 (в клетката E8 стойност е намерена На работа). Обърнете внимание на използването на ;
• Натисни бутона формат ;
• изберете раздел запълвам ;
• изберете сив цвят ;
• Натиснете OK.

ВНИМАНИЕ!: Още веднъж обръщам внимание на формулата =$C7=$E$8 . Обикновено влизат потребители =$C$7=$E$8 , т.е. въведете допълнителен символ за долар.

Трябва да извършите подобни действия, за да изберете произведения в статуса Завършено. Формулата в този случай ще изглежда като =$C7=$E$9 и задайте цвета на запълване на зелено.

В резултат на това нашата таблица ще приеме следната форма.

За да разширите бързо правилата за условно форматиране към нов ред в таблица, изберете клетките нова линия (A17:C17 ) и натиснете . Правилата ще бъдат копирани в низ 17 маси.

Задача 2 - Дати

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

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

Първо, нека създадем формула за условно форматиране в колони B и E. Ако формулата върне TRUE, тогава съответният ред ще бъде маркиран, ако FALSE, тогава не.

В колона D създаден = МАКС(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7 A, който определя максималната дата за конкретен служител.

Забележка:Ако трябва да определите максималната дата независимо от служителя, тогава формулата ще бъде много по-проста = $B7=MAX($B$7:$B$16) и формулата за масив не е необходима.

Сега нека изберем всички клетки на таблицата без заглавка и да създадем правило. Нека копираме формулата в правилото (не е необходимо да се въвежда като формула за масив!).

Сега да предположим, че колоната с дати е сортирана и е необходимо да се изберат редове, чиито дати на посещение попадат в определен диапазон.

За да направите това, използвайте формулата =AND($B23>$E$22;$B23

За клетки E22 и E23 с използвани крайни дати (маркирани в жълто). $E$22 и $23 E$. защото препратката към тях не трябва да се променя в UV правилата за всички клетки на таблицата.

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

Така че UV правилото например за клетка е А27 ще изглежда =AND($B27>$E$22;$B27 , т.е. А27 ще бъдат подчертани, защото в този ред датират от B27 попада в посочения диапазон (за клетки от колона A, изборът все още ще бъде направен в зависимост от съдържанието на колона B от същия ред - това е "магията" на смесеното адресиране $B23).

И за клетката B31 UV правилото би изглеждало така =AND($B31>$E$22;$B31 , т.е. B31 няма да бъде избран, защото в този ред датата от B31 не попада в определения диапазон.

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

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

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

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

Искаме да оцветим редовете с различни цветове в зависимост от поръчаното количество (стойността в колоната Кол.), за да подчертаете най-важните поръчки. Инструментът 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.

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

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

И така, изборът на групи от клетки е необходим, за да се извърши някакво общо действие с тях: или и т.н. Как можете да изберете клетки в Excel? Разбиране!

Как да изберете всички клетки в Excel лист

Веднага ще отговоря на най-задавания въпрос по темата на статията. Как да изберете всички клетки на лист наведнъж? Предлагам ви два начина, който ви харесва най-добре, използвайте единия:

Избиране на правоъгълен диапазон от клетки

Да предположим, че трябва да изберете правоъгълна група клетки, за да ги форматирате за вашия тип данни. Мога да предложа 5 начина за подчертаване, а вие изберете кой е подходящ за вас в момента:

1. Избор на мишката. Най-често срещаният начин е да задържите левия бутон на мишката в една от клетките (не в рамката) и да плъзнете селекцията в желаната посока по редовете и колоните
2. Чрез натискане на клавиш Shift. Поставете курсора в една от ъгловите клетки на бъдещата селекция. Задръжте Shift и изберете област с клавишите със стрелки. Освободете Shift, когато сте готови.
3. Натиснете клавиш F8 . Командата е подобна на предишната, но не е необходимо да натискате клавиша. Поставете курсора в една от ъгловите клетки на масива, натиснете F8, за да включите режима за избор. Използвайте клавишите със стрелки, за да разширите селекцията, или щракнете с мишката в противоположния ъгъл на бъдещата селекция. Когато масивът е избран, натиснете F8 отново, за да излезете от този режим.

   Друг начин е да поставите курсора в горната лява клетка на диапазона, задръжте Shift и щракнете върху долната дясна клетка (вижте фиг.)

4. Въведете в полето "Име".адрес на клетка или диапазон от клетки (разделени с двоеточие) и натиснете Enter
   
   
5. Изпълнете командата(на клавиатурата - F5 , или Ctrl + G ). В прозореца, който се отваря, в полето "Справка" въведете адреса на клетка или диапазон от клетки, разделени с двоеточие. Натиснете OK

Изберете всички редове и колони

Ако трябва да изберете цяла колона или ред, ето няколко опции:

• Кликнете с мишкатавърху номера на реда или името на колоната. Ако трябва да изберете няколко съседни реда, задръжте левия бутон на мишката върху номера на реда и плъзнете селекцията до тези редове, които искате да изберете. Направете същото с колоните.
• Поставете курсора във всяка клетка от реда, който искате да маркирате и комбинация от преси Shift+интервал. Използвайте Ctrl+интервал, за да изберете колона
• За маркиране на несъседни редове и колони − щипкаCtrl и щракнетепо имена на колони и номера на редове. Когато приключите, отпуснете Ctrl.

Избор на несъседни диапазони

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

Изберете върху няколко листа едновременно

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

За да изберете една и съща област на няколко листа, първо изберете желаните листове. Активирайте първия лист от списъка, задръжте Ctrl и щракнете върху етикетите на всички листове, за да изберете.

Когато всички необходими листове са избрани, можете да извършвате операции. Моля, обърнете внимание, че в реда за име, до името на файла, надписът " [Група]". Това означава, че Excel е готов да обработи група листове.

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

Условен избор на клетки

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

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

Избиране на клетки чрез полето за търсене

За да отворите прозореца за търсене на стойност - стартирайте Начало - Редактиране - Търсене и избор - Намери(или натиснете клавишната комбинация Ctrl+F ). Появява се Find All. В долната част на прозореца ще се появи списък с клетки, съдържащи избраните данни. Изберете една или повече клетки (като държите Ctrl ) в списъка, така че Excel да ги маркира. За да изберете всички намерени клетки - изберете една от тях и натиснете комбинацията Ctrl + A.

Можете да използвате специални знаци за търсене:

• "?" - който и да е знак
• "*" - произволен брой знаци

Например, за да намерите всички клетки, които започват с буквата "A" - въведете "A *" в търсенето.

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

Между другото, чакам вашите въпроси относно тази статия в коментарите!

Условното форматиране в Excel ви позволява да изберете не само клетка, но и целия ред наведнъж.За да изберете целия ред наведнъж в голяма таблица, трябва да зададете нашите условия за форматиране на таблицата в таблицата.Какво е условно форматиране, прочетете статията "Условно форматиране в Excel.
Имаме таблица с данни за посетителите. Трябва да изберем посетители, които са завършили курс на обучение, лечение, работа и т.н. Таблицата е следната. В отделни клетки създаваме малка помощна таблица. Например, нека направим етикет в клетки G2, G3 и G4.
Ако в колоната „Статус“ напишем – „Завършен“ или „Етап 1“, „Етап 2“, целият ред ще бъде оцветен в цвят.
Вместо спомагателна таблица, можете да направите „падащ списък“ в спомагателната клетка.
Сега задаваме условно форматиране на клетките на таблицата.
Избираме цялата таблица с данни и допълнително в долната част на таблицата още един празен ред. Ще копираме празен ред от таблицата, ако трябва да добавим редове в таблицата. Условията за форматиране ще бъдат копирани незабавно.
И така, избрахме таблицата с диапазони A2:E7.
В раздела Начало щракнете върху бутона Условно форматиране и изберете опцията Създаване на правила.
Кликнете върху реда „Сравнете колоните на таблицата, за да определите форматирани клетки“.В реда "Форматиране" напишете формулата. =$E2=$G$2
Забележка– препратката към клетка E2 е смесена.
Натиснете бутона с "0". Тук избираме и задаваме цвета на запълване на линията, цвета на шрифта на тази линия. Избрали сме зелен цвят за запълване на клетката.Кликнете върху всичките три диалогови прозореца "OK". Всичко.
Сега пишем в таблицата в колоната "Състояние" - "Завършено" и нашият ред става зелен, което сме задали в правилата за условно форматиране.
внимание!В клетките на колоната "Състояние" напишете думите по същия начин, както са написани в спомагателната таблица. Например, имаме думата „Завършено“, написана с главна буква. Ако напишем думата „завършено“ в клетка на колона с малка буква, тогава условното форматиране няма да работи. Затова е по-добре да зададете падащ списък в колоната "Състояние".Как да инсталирате падащ списък, вижте статията "Падащ списък в Excel. Оказа се така.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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