Поставяне на клетки въз основа на стойността в Microsoft Excel

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

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

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

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

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

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

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

Нека видим как това работи на конкретен пример. Разполагаме с таблица за приходите на предприятието, в която данните се прекъсват ежемесечно. Трябва да подчертаем в различни цветове тези елементи, при които размерът на дохода е по-малък от 400 000 рубли, от 400 000 до 500 000 рубли и надхвърля 500 000 рубли.

1. Избираме колона, в която се намира информацията за приходите на предприятието. След това преминете към раздела Начална страница. Кликваме върху бутона "Условно форматиране" , който се намира на лентата в полето "Стилове" . В отворения списък изберете елемента "Управление на правилата ..." .
2. Прозорецът за управление на правилата за условно форматиране се отваря. Полето "Показване на правилата за форматиране за" трябва да бъде зададено на "Текущ фрагмент" . По подразбиране трябва да е там, но само в случай, проверете и в случай на несъответствие, променете настройките според горепосочените препоръки. След това кликнете върху бутона "Създаване на правило ..." .
3. Прозорецът за създаване на правилото за форматиране се отваря. В списъка с типове правила изберете опцията "Форматиране само на клетки, които съдържат" . В блока за описание на правила в първото поле превключвателят трябва да бъде в позиция "Стойности" . Във второто поле поставете превключвателя в положение "по-малко" . В третото поле посочваме стойността, елементите на листа, съдържащи стойност по-малка от тази, ще бъдат оцветени с определен цвят. В нашия случай тази стойност ще бъде 400 000 . След това кликнете върху бутона "Форматиране ..." .
4. Отваря се прозорецът за клетъчен формат. Преминаваме към раздела "Попълване" . Избираме цвета на пълнежа, който искаме да имаме клетки, които съдържат по-малко от 400 000 . След това кликнете върху бутона "OK" в долната част на прозореца.
5. Връщаме се в прозореца за създаване на правилото за форматиране и там също така кликваме върху бутона "OK" .
6. След това действие отново ще бъдем пренасочени към Управляващия правилник за условията за форматиране . Както виждате, едно правило вече е добавено, но трябва да добавим още две. Затова кликнете отново върху бутона "Създаване на правило ..." .
7. И отново стигаме до прозореца за създаване на правила. Преминаваме към секцията "Форматирайте само клетките, които съдържат" . В първото поле на тази секция оставаме параметъра "Стойност на клетката" , а във втория - превключвателя в позиция "Между" . В третото поле трябва да посочите началната стойност на диапазона, в който елементите на листа ще бъдат форматирани. В нашия случай този брой е 400 000 . На четвърто място, ние посочваме крайната стойност на този диапазон. Тя ще бъде 500 000 . След това кликнете върху бутона "Форматиране ..." .
8. В прозореца за форматиране отново преминаваме към раздела "Запълване" , но този път вече сме избрали различен цвят, след което кликнете върху бутона "OK" .
9. След като се върнете в прозореца за създаване на правила, кликнете върху бутона "OK" .
10. Както виждате, в Управителя на правилата вече сме създали две правила. По този начин остава да се създаде трета. Кликваме върху бутона "Създаване на правило" .
11. В прозореца за създаване на правила отново преминете към секцията "Форматирайте само клетките, които съдържат" . В първото поле оставаме опцията "Клетъчна стойност" . Във второто поле задайте превключвателя към полицията "Още" . В третото поле ще караме номер 500000 . След това, както в предишните случаи, кликнете върху бутона "Форматиране ..." .
12. В прозореца "Форматиране на клетки" отново отиваме в раздела "Попълване" . Този път изберете цвят, който се различава от двата предишни случая. Кликнете върху бутона "OK" .
13. В прозореца за създаване на правила повторете кликването върху бутона "OK" .
14. Отваря се мениджърът на правилата . Както можете да видите, и трите правила са създадени, така че кликнете върху бутона "OK" .
15. Сега елементите на таблицата са оцветени в съответствие с конкретните условия и граници в настройките за условно форматиране.
16. Ако променим съдържанието в една от клетките, оставяйки по този начин извън някое от дадените правила, тогава този елемент на листа автоматично ще промени цвета си.

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

1. За да направите това, след като отидем в прозореца Форматиране от Управителя на правилата , оставаме в секцията "Форматиране на всички клетки въз основа на техните стойности" . В полето "Цвят" можете да изберете цвета, чрез който елементите на листа ще бъдат затъмнени. След това кликнете върху бутона "OK" .
2. В мениджъра на правилата също кликнете върху бутона "OK" .
3. Както можете да видите, след това клетките в колоната са оцветени с различни нюанси от един и същи цвят. След това стойността, която съдържа листовия елемент, е по-голяма, сянката е по-светла, толкова по-малка - по-тъмната.

Урокът: Условно форматиране в Excel

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

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

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

1. Изберете колоната с данните, които трябва да бъдат форматирани с цвят. След това отидете в раздела "Начало" и кликнете върху бутона "Намиране и всичко" , който се намира на лентата в кутията с инструменти "Редактиране" . В отворения списък кликнете върху "Намери" .
2. Прозорецът "Намери и замени" се отвори в раздела "Намери" . На първо място, ние откриваме стойности до 400 000 рубли. Тъй като нямаме нито една клетка със стойност по-малка от 300 000 рубли, по същество трябва да изберете всички елементи, които съдържат номера в диапазона от 300 000 до 400 000 . За съжаление можете директно да посочите този диапазон, както при условното форматиране, този метод не може да се използва.

   Но има възможност да се направи малко по-различно, което ще ни даде същия резултат. Можете да зададете следния модел "3 ?????" в лентата за търсене. Въпросният знак означава всеки знак. По този начин програмата ще търси всички шестцифрени числа, започващи с числото "3" . Това означава, че резултатите от търсенето ще включват стойности от порядъка на 300 000 - 400 000 , което ни е необходимо. Ако масата имаше числа по-малки от 300 000 или по-малко от 200 000 , тогава за всеки стоте хиляди, търсенето трябваше да се извърши отделно.

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

3. След това долната част на прозореца отваря резултатите от търсенето. Кликнете върху левия бутон на мишката върху някоя от тях. След това въведете Ctrl + A. След това всички резултати от резултата от търсенето се осветяват и същевременно се подчертават елементите в колоната, към която се отнасят тези резултати.
4. След като елементите в колоната са маркирани, не бързайте да затворите прозореца "Намери и замени" . Докато сте в раздела "Начало", в който се появихме по-рано, отидете на лентата в полето "Font" . Кликнете върху триъгълника отдясно на бутона "Цвят на запълване" . Избирането на различните цветове за попълване се отваря. Изберете цвета, който искаме да приложим към елементите на листа, съдържащи стойности, по-малки от 400 000 рубли.
5. Както можете да видите, всички клетки в колоната, в които стойностите са по-малки от 400 000 рубли, се открояват с избрания цвят.
6. Сега трябва да рисуваме елементи, в които стойностите варират от 400 000 до 500 000 рубли. Този диапазон включва цифри, съответстващи на шаблона "4" . Ние го карам в полето за търсене и кликнете върху бутона "Намери всички" , преди да изберете колоната, от която се нуждаем.
7. По подобен начин, с предишното време в изхода за търсене, избираме целия резултат, получен чрез натискане на комбинацията CTRL + A клавишна комбинация. След това преминете към иконата за избор на цвят за попълване. Кликнете върху него и кликнете върху иконата на желания оттенък, която ще оцветява елементите на листа, където стойностите варират от 400 000 до 500 000 .
8. Както можете да видите, след това действие всички елементи на таблицата с данни в интервала от 400000 до 500000 се открояват с избрания цвят.
9. Сега трябва да определим последния интервал от стойности - повече от 500 000 . Тук сме щастливи, тъй като всички числа над 500 000 са в диапазона от 500 000 до 600 000 . Затова в полето за търсене въведете израза "5 ?????" и кликнете върху бутона "Намиране на всички" . Ако имаше стойности над 600 000 , тогава би трябвало допълнително да търсим израза "6 ?????" и т.н.
10. Отново изберете резултатите от търсенето с помощта на комбинацията Ctrl + A. След това, с помощта на бутона на лентата, изберете нов цвят за попълване на интервала, надхвърлящ 500 000 по същата аналогия с предишната.
11. Както можете да видите, след това действие всички елементи на колоната ще бъдат затъмнени според цифровата стойност, която е поставена в тях. Сега можете да затворите прозореца за търсене, като кликнете върху стандартния бутон за затваряне в горния десен ъгъл на прозореца, тъй като нашата задача може да се счита за решена.
12. Но ако променим номера на друг, който надхвърля границите, определени за определен цвят, цветът няма да се промени, както беше при предишния метод. Това показва, че тази опция ще работи надеждно само в таблиците, в които данните не се променят.

Урокът: Как да направя търсене в Excel

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

• ← Преобразувайте всички букви в главни букви в Microsoft Excel
• Функция на INDIRECT в Microsoft Excel →