Как да оцветите клетки в excel въз основа на състоянието. Маркиране на клетки с цвят в EXCEL. Автоматично маркиране на колона въз основа на текущия месец

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Не всички компании купуват специални програмиза извършване на бизнес. Много хора използват MS Excel, защото е много подходящ за големи информационни бази. Опитът показва, че рядко се надхвърля попълването на таблици. Таблицата расте, повече информация става достъпна и възниква необходимостта бързо да изберете само това, от което се нуждаете. В такава ситуация възниква въпросът: как да маркирате клетка в Excel при определено условие, да приложите цветови градиенти към редове в зависимост от типа или името на доставчика и да направите работата с информация бърза и удобна? Прочетете повече по-долу.

Къде се намира условното форматиране?

Как да промените цвета на клетка в Excel в зависимост от стойността - това е много просто и бързо. За да маркирате клетки с цвят, има a специална функция„Условно форматиране“, разположено в раздела „Начало“:

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

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

С помощта на този набор от инструменти се правят следните селекции:

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

Вижте как се търси избрания текст: първото поле определя условието, а второто показва как да маркирате резултата. Моля, обърнете внимание, че можете да изберете цветовете на фона и текста от предложените в списъка. Ако искате да приложите други нюанси, можете да направите това, като отидете на „Персонализиран формат“. Всички „Правила за избор на клетки“ се изпълняват по подобен начин.

„Други правила“ се изпълняват много креативно: в шест варианта на сценария измислете тези, които са най-удобни за работа, например градиент:

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

Правила за избор на първа и последна стойност.

Нека разгледаме втората група функции „Правила за избор на първата и последната стойност“. В него можете:

маркирайте първия или последния N брой клетки с цвят;
прилагане на форматиране към определен процент клетки;
изберете клетки, съдържащи стойност над или под средната в масива;
в раздела „Други правила“ задайте необходимата функционалност.

Хистограми

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

Цветни везни

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

Набори от икони

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

Създавайте, изтривайте и управлявайте правила

Функцията „Създаване на правило“ напълно дублира „Други правила“, изброени по-горе, и създава селекция първоначално по заявка на потребителя.

С помощта на раздела „Изтриване на правило“ създадените скриптове се изтриват от целия лист, от избрания диапазон от стойности, от таблицата.

Инструментът „Управление на правила“ представлява интерес - един вид история на създаване и промяна на форматирането. Променете селекциите, направете правилата неактивни, върнете ги обратно, променете реда на прилагане. Това е много удобно за работа с големи количества информация.

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

За да разберете как да промените цвета на клетката от стойността в Excel задайте дата, помислете за пример с дати на покупка от доставчици през януари 2019 г. За да приложите тази селекция, имате нужда от клетки с зададен формат „Дата“. За да направите това, преди да въведете информация, изберете необходимата колона, щракнете с десния бутон и в менюто „Форматиране на клетки“ намерете раздела „Число“. Задайте числовия формат на "Дата" и изберете типа му, както желаете.

За да изберете необходимите дати, използваме следната последователност от действия:

изберете колони с дати (в нашия случай за януари);
намерете инструмента „Условно форматиране“;
в „Правила за избор на клетки“ изберете елемента „Дата“;
от дясната страна на форматирането отворете падащ прозорец с правила;
изберете подходящото правило (например избрани са дати за предходния месец);
в лявото поле задайте готовия избор на цвят „Жълто запълване и тъмно жълт текст“
Селекцията е оцветена, щракнете върху „OK“.

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

Оцветяване на колона по условие

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

Трябва да маркираме в синьо доставчиците, от които сме закупили стоки на стойност над 100 000 рубли. За да направим такъв избор, ще използваме следния алгоритъм от действия:

маркирайте колоната с покупки през януари;
Щракнете върху инструмента „Условно форматиране“;
отидете на „Правила за избор на клетки“;
елемент “Още...”;
от дясната страна на форматирането задайте сумата на 100 000 рубли;
в лявото поле отидете в раздела „Персонализиран формат“ и изберете синия цвят;
необходимата селекция е оцветена в синьо, щракнете върху „OK“.

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

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

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

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

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

Как да направите това в Excel 2007

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

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

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

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

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

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

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

Как да направите това в Excel 2003

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

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

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

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

Автоматично попълване на клетки с дати

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

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

Това представено решение трябва да автоматизира някои работни процеси и да опрости визуалния анализ на данни.

Автоматично попълване на клетки с текущи дати

Първо, за регистъра с клиентски номера ще създадем заглавия на колони със зелен цвят и текущи месеци, които автоматично ще показват периоди от време. За да направите това, въведете следната формула в клетка B1:

Как работи формулата за автоматично генериране на изходящи месеци?

На фигурата формулата връща периода на изтичане на времето, започващ от датата на написване на статията: 17.09.2017 г. Първият аргумент във функцията DATA съдържа формула, която винаги връща текущата година на днешната дата благодарение на функциите: YEAR и TODAY. Вторият аргумент указва номера на месеца (-1). Отрицателно число означава, че ни интересува кой месец е бил в миналото. Примерни условия за втория аргумент със стойност:

1 – означава първия месец (януари) в годината, посочена в първия аргумент;
0 е преди 1 месец;
-1 е 2 месеца. назад от началото на текущата година (тоест: 01.10.2016 г.).

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

Както можете да видите, функцията ДАТА сега използва стойността от клетка B1 и увеличава номера на месеца с 1 спрямо предходната клетка. В резултат на това получаваме 1 - датата на следващия месец.

Сега копирайте тази формула от клетка C1 в останалите заглавки на колони в диапазона D1:L1.

Изберете диапазона от клетки B1:L1 и изберете инструмента: “HOME” - “Cells” - “Format Cells” или просто натиснете CTRL+1. В диалоговия прозорец, който се появява, в раздела „Число“, в секцията „Числови формати:“, изберете опцията „(всички формати)“. В полето “Тип:” въведете стойността: MMM.YY (задължително главни букви). Благодарение на това ще получим съкратен дисплей на стойностите на датата в заглавките на регистъра, което ще опрости визуалния анализ и ще го направи по-удобен поради по-добра четливост.

Забележка!Когато настъпи месец януари (D1), формулата автоматично променя датата на следващата година.

Как да оцветите колона в Excel въз основа на условие

Сега трябва да маркирате клетките, свързани с текущия месец. Благодарение на това лесно намираме колоната, в която трябва да въведем актуални данни за този месец. За това:

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

Как работи формулата за маркиране на цвят на колона по условие?

Благодарение на факта, че преди да създадем правилото за условно форматиране, покрихме всички таблична частза въвеждане на данни от регистъра, форматирането ще бъде активно за всяка клетка в този диапазон B2:L15. Смесената препратка във формула B$1 (абсолютен адрес само за редове, относителен адрес за колони) означава, че формулата винаги ще препраща към първия ред на всяка колона.

Автоматично маркиране на колона въз основа на текущия месец

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

Забележка!В условията на тази формула последният аргумент на функцията ДАТА е зададен на 1, точно както формулите за дефиниране на дати за заглавки на колони в регистъра.

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

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

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

Сега трябва да маркираме в червено клетките с номерата на клиентите, които не са направили нито една поръчка в продължение на 3 месеца. За това:

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

Анализ на формулата за маркиране на клетки по цвят:

Първо, нека се заемем със средната част на нашата формула. Функцията OFFSET връща референтно отместване на диапазон от основния диапазон с определен брой редове и колони. Върнатата препратка може да бъде една клетка или цял диапазон от клетки. По желание можете да определите броя на редовете и колоните, които да бъдат върнати. В нашия пример функцията връща препратка към диапазон от клетки за последните 3 месеца.

Важната част за нашето условие за осветяване на цвета е в първия аргумент на функцията OFFSET. Той определя от кой месец да започне компенсирането. IN в този пример– това е клетка D2, тоест началото на годината е януари. Естествено, за останалите клетки в колоната номерът на реда за основната клетка ще съответства на номера на реда, в който се намира. Следващите 2 аргумента на функцията OFFSET определят върху колко реда и колони трябва да се извърши отместването. Тъй като ще извършим изчисления за всеки клиент в един и същ ред, ние определяме стойността на отместването за редовете като –¬ 0.

В същото време, за да изчислим стойността на третия аргумент (изместване на колона), използваме вложената формула MONTH(TODAY()), която в съответствие с условията връща номера на текущия месец в текущата година. Изваждаме числото 4 от номера на месеца, изчислен по формулата, т.е. в случай на ноември получаваме отместване от 8 колони. И например за юни - само 2 колони.

Последните два аргумента на функцията OFFSET указват височината (в брой редове) и ширината (в брой колони) на върнатия диапазон. В нашия пример това е област от клетки с височина 1 ред и ширина 4 колони. Този диапазон обхваща колоните от предходните 3 месеца и текущия.

Първата функция във формулата COUNTIF тества колко пъти текстовата стойност "order" се появява в върнатия диапазон с помощта на функцията OFFSET. Ако функцията върне стойност 0, това означава, че не е имало поръчки от клиент с този номер от 3 месеца. И в съответствие с нашите условия, клетката с номера на този клиент се маркира с червен цвят за запълване.

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

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

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

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

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

Но има изход. За клетки, които съдържат динамични (променящи се) стойности, се прилага условно форматиране, а за статистически данни можете да използвате инструмента „Намери и замени“.

Метод 1: Условно форматиране

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

Нека видим как работи този метод конкретен пример. Имаме таблица с доходите на предприятието, в която данните са разбити по месеци. Трябва да подчертаем с различни цветове тези елементи, в които размерът на дохода е по-малък 400000 рубли, от 400000 преди 500000 рубли и надвишава 500000 рубли

Маркираме колоната, която съдържа информация за приходите на компанията. След това преминете към раздела "У дома". Кликнете върху бутона "Условно форматиране", който се намира на лентата в кутията с инструменти "Стилове". В списъка, който се отваря, изберете елемента „Управление на правила...“.

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

Отваря се прозорецът за създаване на правило за форматиране. В списъка с типове правила изберете позиция . В блока за описание на правилото в първото поле превключвателят трябва да е на позиция "Стойности". Във второто поле поставете превключвателя на позиция "По-малко". В третото поле посочваме стойността; елементите на листа, съдържащи стойност, по-малка от която ще бъдат оцветени определен цвят. В нашия случай тази стойност ще бъде 400000 . След това кликнете върху бутона „Форматиране…“.

Отваря се прозорецът Форматиране на клетки. Преминаване към раздела "Попълнете". Изберете цвета на запълване, който искате да маркирате клетки, съдържащи стойност, по-малка от 400000 . След това кликнете върху бутона "ДОБРЕ"в долната част на прозореца.

Връщаме се в прозореца за създаване на правило за форматиране и кликваме върху бутона и там "ДОБРЕ".

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

И отново се озоваваме в прозореца за създаване на правило. Преминаване към секцията „Форматиране само на клетки, които съдържат“. В първото поле на този раздел оставяме параметъра „Стойност на клетка“, а във втория поставяме превключвателя на позиция "между". В третото поле трябва да посочите началната стойност на диапазона, в който ще бъдат форматирани елементите на листа. В нашия случай това число 400000 . В четвъртата посочваме крайната стойност на този диапазон. Ще възлиза на 500000 . След това щракнете върху бутона „Форматиране…“.

В прозореца за форматиране преминете отново към раздела "Попълнете", но този път избираме различен цвят и след това щракваме върху бутона "ДОБРЕ".

След като се върнете към прозореца за създаване на правило, също щракнете върху бутона "ДОБРЕ".

Както виждаме, в Мениджър на правилатаВече създадохме две правила. Така остава да се създаде третият. Кликнете върху бутона „Създаване на правило“.

В прозореца за създаване на правило преминете отново към секцията „Форматиране само на клетки, които съдържат“. В първото поле оставяме опцията „Стойност на клетка“. Във второто поле поставете превключвателя на полиция "Повече ▼". В третото поле въвеждаме номера 500000 . След това, както в предишните случаи, щракнете върху бутона „Форматиране…“.

В прозореца "Формат на клетка"преминете отново към раздела "Попълнете". Този път избираме цвят, който е различен от предишните два случая. Кликнете върху бутона "ДОБРЕ".

В прозореца за създаване на правило натиснете отново бутона "ДОБРЕ".

Отваря се Мениджър на правилата. Както можете да видите, и трите правила са създадени, така че щракнете върху бутона "ДОБРЕ".

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

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

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

Метод 2: Използване на инструмента за намиране и избор

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

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

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

Отваря се прозорец „Намери и замени“в раздела "Намирам". Първо, нека намерим стойностите до 400000 рубли Тъй като нямаме нито една клетка, съдържаща стойност по-малка от 300000 рубли, тогава всъщност трябва да изберем всички елементи, които съдържат числа в диапазона от 300000 преди 400000 . За съжаление е невъзможно директно да се посочи този диапазон, както в случая с прилагането на условно форматиране, в този методзабранено е.
Но има възможност да направим нещата малко по-различно, което ще ни даде същия резултат. Можете да посочите следния модел в лентата за търсене "3?????". Въпросителен знак означава всеки символ. Така програмата ще търси всички шестцифрени числа, които започват с цифрата "3". Тоест резултатите от търсенето ще включват стойности в диапазона 300000 – 400000 , от което се нуждаем. Ако таблицата имаше по-малки числа 300000 или по-малко 200000 , тогава за всеки диапазон от сто хиляди търсенето ще трябва да се извърши отделно.

Въведете израза "3?????"в полето "Намирам"и натиснете бутона „Намерете всичко».

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

След като елементите в колоната са избрани, не бързайте да затваряте прозореца „Намери и замени“. Докато сте в раздела "У дома"към който се преместихме по-рано, отидете на лентата към блока с инструменти "Шрифт". Кликнете върху триъгълника вдясно от бутона „Цвят на запълване“. Отваря се избор от различни цветове за запълване. Избираме цвета, който искаме да приложим към елементи на листа, съдържащи стойности по-малки от 400000 рубли

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

Сега трябва да оцветим елементите, които съдържат стойности в диапазона от 400000 преди 500000 рубли Този диапазон включва числа, които съответстват на шаблона "4??????". Въведете го в полето за търсене и щракнете върху бутона „Намери всички“, като предварително сме избрали колоната, от която се нуждаем.

Подобно на предишния път в резултатите от търсенето избираме целия резултат, получен чрез натискане на комбинация от горещи клавиши CTRL+A. След това преминете към иконата за избор на цвят на запълване. Щракваме върху него и щракваме върху иконата на нюанса, от който се нуждаем, който ще оцвети елементите на листа, където стойностите са в диапазона от 400000 преди 500000 .

Както виждаме, след това действие всички елементи на таблицата с данни в интервала s 400000 от 500000 подчертано с избрания цвят.

Сега просто трябва да изберем последния интервал от стойности - повече 500000 . Тук също имаме късмет, тъй като всички числа са повече 500000 са в диапазона от 500000 преди 600000 . Следователно в полето за търсене въвеждаме израза "5?????"и натиснете бутона „Намери всички“. Ако имаше стойности по-големи от 600000 , тогава ще трябва допълнително да търсим израза „6?????“и т.н.

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

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

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

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