2 спосабу змяніць колер залівання вочак у Excel на аснове іх значэнняў

У гэтым артыкуле вы знойдзеце два хуткіх спосабу змяніць колер ячэйкі ў залежнасці ад яе значэння ў Excel 2013, 2010 і 2007. Акрамя таго, вы даведаецеся, як выкарыстоўваць формулы ў Excel для змены колеру пустых вочак або ячэйкі з памылкамі формул.

Усім вядома, што каб змяніць колер залівання адной ячэйкі або цэлага дыяпазону ў Excel, проста націсніце кнопку Колер залівання (Колер залівання). Але што, калі вам трэба змяніць колер залівання ўсіх вочак, якія змяшчаюць пэўнае значэнне? Больш за тое, што, калі вы хочаце, каб колер залівання кожнай ячэйкі змяняўся аўтаматычна пры змене змесціва гэтай ячэйкі? Далей у артыкуле вы знойдзеце адказы на гэтыя пытанні і атрымаеце пару карысных саветаў, якія дапамогуць выбраць правільны спосаб вырашэння кожнай канкрэтнай праблемы.

• Змяняйце колер залівання вочак у залежнасці ад іх значэнняў (дынамічна) – колер залівання будзе змяняцца аўтаматычна пры змене значэння ячэйкі.
• Змяніць колер ячэйкі ў залежнасці ад яе бягучага значэння (статычна) – пасля ўстаноўкі колер залівання не мяняецца незалежна ад таго, як змяняецца значэнне ячэйкі.
• Змяніць колер пэўных вочак (пустых, з памылкамі, з формуламі)

Як дынамічна змяніць колер ячэйкі ў Excel на аснове яе значэння

Колер залівання будзе мяняцца ў залежнасці ад значэння ячэйкі.

Праблема: У вас ёсць табліца або дыяпазон даных, і вы хочаце змяніць колер залівання вочак у залежнасці ад іх значэнняў. Больш за тое, неабходна, каб гэты колер мяняўся дынамічна, адлюстроўваючы змены дадзеных у вочках.

Рашэнне: Выкарыстоўвайце ўмоўнае фарматаванне ў Excel, каб вылучыць значэнні, большыя за X, меншыя за Y або паміж X і Y.

Дапусцім, у вас ёсць спіс цэн на газ у розных штатах, і вы хочаце, каб цэны былі вышэйшыя за $ 3.7, былі вылучаны чырвоным колерам і меншыя або роўныя $ 3.45 – зялёны.

нататка: Скрыншоты для гэтага прыкладу былі зроблены ў Excel 2010, аднак у Excel 2007 і 2013 кнопкі, дыялогавыя вокны і налады будуць сапраўды такімі ж або крыху іншымі.

Такім чынам, вось што вам трэба зрабіць крок за крокам:

1. Выберыце табліцу або дыяпазон, у якім вы хочаце змяніць колер залівання вочак. У гэтым прыкладзе мы вылучаем $B$2:$H$10 (загалоўкі слупкоў і першы слупок з назвамі дзяржаў не вылучаюцца).
2. націсніце Галоўная (Дом), у разд Стылі (Стылі) націсніце ўмоўнае фарматаванне (Умоўнае фарматаванне) > новыя правілы (Стварыць правіла).
3. У верхняй частцы дыялогавага акна Новае правіла фарматавання (Стварыць правіла фарматавання) у полі Выберыце тып правіла (Выберыце тып правіла) абярыце Фарматаваць толькі ячэйкі, якія змяшчаюць (Фарматаваць толькі ячэйкі, якія змяшчаюць).
4. У ніжняй частцы дыялогавага акна ў полі Фарматаваць толькі вочкі з (Фарматаваць толькі ячэйкі, якія адпавядаюць наступнай умове) Усталюйце ўмовы для правіла. Мы вырашылі адфарматаваць толькі вочкі з умовай: Значэнне ячэек (значэнне ячэйкі) – больш (больш) - 3.7як паказана на малюнку ніжэй.Затым націсніце кнопку памер (Фармат), каб выбраць колер залівання, які будзе прымяняцца, калі выканана вызначаная ўмова.
5. У якое з'явілася дыялогавым акне Фарматаваць вочкі Ўкладка (Фарматаваць вочкі). Fill (Заліванне) і абярыце колер (мы выбралі чырванаваты) і націсніце OK.
6. Пасля гэтага вы вернецеся да акна Новае правіла фарматавання (Стварэнне правіла фарматавання), дзе ў полі Папярэдні прагляд (Узор) пакажа ўзор вашага фарматавання. Калі вы задаволены, націсніце OK.

Вынік налад фарматавання будзе выглядаць прыкладна так:

Так як нам трэба наладзіць іншую ўмову, якая дазваляе змяняць колер залівання на зялёны для вочак са значэннямі, меншымі або роўнымі 3.45, затым націсніце кнопку яшчэ раз новыя правілы (Стварыць правіла) і паўтарыце крокі з 3 па 6, усталяваўшы патрэбнае правіла. Ніжэй прыведзены прыклад другога правіла ўмоўнага фарматавання, якое мы стварылі:

Калі ўсё будзе гатова - націсніце OK. Цяпер у вас ёсць прыгожа адфарматаваная табліца, якая дазваляе адразу бачыць максімальныя і мінімальныя цэны на газ у розных штатах. Добра ім там, у Тэхасе! 🙂

Савет: Такім жа чынам вы можаце змяніць колер шрыфта ў залежнасці ад значэння ячэйкі. Для гэтага проста адкрыйце ўкладку Шрыфт (Шрыфт) у дыялогавым акне Фарматаваць вочкі (Фармат ячэйкі), як мы рабілі ў кроку 5, і выберыце патрэбны колер шрыфта.

Як усталяваць пастаянны колер ячэйкі на аснове яе бягучага значэння

Пасля ўстаноўкі колер залівання не зменіцца, незалежна ад таго, як змесціва ячэйкі зменіцца ў будучыні.

Праблема: Вы хочаце адрэгуляваць колер ячэйкі ў залежнасці ад яе бягучага значэння, а таксама хочаце, каб колер залівання заставаўся нязменным, нават калі значэнне ячэйкі змяняецца.

Рашэнне: Знайдзіце ўсе ячэйкі з пэўным значэннем (або значэннямі) з дапамогай інструмента Знайсці ўсё (Знайсці ўсё), а затым змяніць фармат знойдзеных вочак з дапамогай дыялогавага акна Фарматаваць вочкі (фармат ячэйкі).

Гэта адна з тых рэдкіх задач, для якіх няма тлумачэнняў у файлах даведкі Excel, на форумах або ў блогах і для якой няма прамога рашэння. І гэта зразумела, бо гэтая задача нетыповая. І ўсё ж, калі вам трэба змяніць колер залівання вочак назаўжды, гэта значыць раз і назаўсёды (або пакуль вы не зменіце яго ўручную), выканайце наступныя дзеянні.

Знайдзіце і вылучыце ўсе вочкі, якія адпавядаюць зададзенай умове

Тут магчымыя некалькі сцэнарыяў у залежнасці ад таго, які тып каштоўнасці вы шукаеце.

Калі вы хочаце афарбаваць ячэйкі пэўным значэннем, напрыклад, 50, 100 or 3.4 – потым на ўл Галоўная (Дом) у раздзеле Рэдагаванне (Рэдагаванне) націсніце Знайдзіце Select (Знайдзіце і вылучыце) > Знаходзіць (Знаходзіць).

Увядзіце патрэбнае значэнне і націсніце Знайсці ўсё (Знайсьці ўсе).

Савет: У правай частцы дыялогавага акна Знайсці і замяніць (Знайсці і замяніць) ёсць кнопка опцыі (Параметры), націснуўшы на якую, вы атрымаеце доступ да шэрагу налад пашыранага пошуку, напрыклад Match Case (з улікам рэгістра) і Супаставіць увесь змест ячэйкі (Усю клетку). Вы можаце выкарыстоўваць сімвалы падстаноўкі, такія як зорачка (*), каб адпавядаць любому радку сімвалаў, або знак пытання (?), каб адпавядаць любому асобнаму сімвалу.

Адносна папярэдняга прыкладу, калі нам трэба знайсці ўсе цэны на бензін з 3.7 у 3.799, тады мы ўсталюем наступныя крытэрыі пошуку:

Цяпер націсніце любы са знойдзеных элементаў унізе дыялогавага акна Знайсці і замяніць (Знайсці і замяніць) і націсніце Ctrl + A,каб вылучыць усе знойдзеныя запісы. Пасля гэтага націсніце кнопку Фермер (Блізка).

Вось як вы можаце выбраць усе вочкі з зададзеным значэннем (значэннямі), выкарыстоўваючы опцыю Знайсці ўсё (Знайсці ўсё) у Excel.

Аднак на самой справе нам трэба знайсці ўсе цэны на бензін, якія перавышаюць $ 3.7. На жаль, інструмент Знайсці і замяніць (Знайсці і замяніць) не можа дапамагчы нам у гэтым.

Змяніце колеры залівання выбраных вочак з дапамогай дыялогавага акна "Фарматаваць вочкі".

Цяпер у вас выбраны ўсе ячэйкі з зададзеным значэннем (або значэннямі), мы толькі што зрабілі гэта з дапамогай інструмента Знайсці і замяніць (Знайдзі і замяні). Усё, што вам трэба зрабіць, гэта ўсталяваць колер залівання для выбраных вочак.

Адкрыйце дыялогавае акно Фарматаваць вочкі (Фармат ячэйкі) любым з 3 спосабаў:

• прэсаванне Ctrl + 1.
• пстрыкнуўшы па любой вылучанай ячэйцы правай кнопкай мышы і выбраўшы пункт з кантэкстнага меню Фарматаваць вочкі (фармат ячэйкі).
• таб Галоўная (Дадому) > Клеткі. Клеткі. (клеткі) > памер (Фармат) > Фарматаваць вочкі (фармат ячэйкі).

Далей наладзьце параметры фарматавання, як хочаце. На гэты раз мы ўсталюем колер залівання аранжавым, проста для змены 🙂

Калі вы хочаце змяніць толькі колер залівання, не закранаючы астатнія параметры фарматавання, вы можаце проста націснуць кнопку Колер залівання (Колер залівання) і выберыце колер, які вам падабаецца.

Вось вынік нашых змяненняў фарматавання ў Excel:

У адрозненне ад папярэдняга метаду (з умоўным фарматаваннем), зададзены такім чынам колер залівання ніколі не зменіцца сам без вашага ведама, як бы ні змяняліся значэння.

Змяніць колер залівання спецыяльных вочак (пустыя, з памылкай у формуле)

Як і ў папярэднім прыкладзе, вы можаце змяніць колер залівання пэўных вочак двума спосабамі: дынамічна і статычна.

Выкарыстоўвайце формулу, каб змяніць колер залівання спецыяльных вочак у Excel

Колер ячэйкі зменіцца аўтаматычна ў залежнасці ад значэння ячэйкі.

Вы, хутчэй за ўсё, скарыстаецеся такім спосабам вырашэння задачы ў 99% выпадкаў, гэта значыць напаўненне вочак будзе змяняцца ў адпаведнасці з зададзеным вамі ўмовай.

Напрыклад, давайце зноў возьмем табліцу коштаў на бензін, але на гэты раз мы дадамо яшчэ пару станаў і зробім некаторыя вочкі пустымі. Цяпер паглядзіце, як вы можаце знайсці гэтыя пустыя вочкі і змяніць іх колер залівання.

1. На ўкладцы Дадаткова Галоўная (Дом) у раздзеле Стылі (Стылі) націсніце ўмоўнае фарматаванне (Умоўнае фарматаванне) > новыя правілы (Стварыць правіла). Гэтак жа, як у 2-м кроку прыкладу Як дынамічна змяніць колер ячэйкі ў залежнасці ад яе значэння.
2. У дыялогавым акне Новае правіла фарматавання (Стварыць правіла фарматавання) абярыце опцыю Выкарыстоўвайце формулу, каб вызначыць, якія клеткі фарматаваць (Выкарыстоўвайце формулу, каб вызначыць, якія вочкі трэба адфарматаваць). Далей у поле Фармат значэнняў, дзе гэтая формула праўдзівая (Фармат значэнняў, для якіх праўдзівая наступная формула) увядзіце адну з формул:
   • каб змяніць запаўненне пустых вочак

     =ISBLANK()

     =ЕПУСТО()

   • каб змяніць зацяненне вочак, якія змяшчаюць формулы, якія вяртаюць памылку

     =ISERROR()

     =ЕОШИБКА()

   Паколькі мы хочам змяніць колер пустых вочак, нам спатрэбіцца першая функцыя. Увядзіце яго, затым змясціце курсор паміж дужкамі і націсніце на значок выбару дыяпазону справа ад радка (або ўвядзіце патрэбны дыяпазон уручную):

   =ISBLANK(B2:H12)

   =ЕПУСТО(B2:H12)

   

3. націсніце кнопку памер (Фармат), абярыце патрэбны колер залівання на ўкладцы Fill (Запоўніць), а затым націсніце OK. Падрабязныя інструкцыі прыведзены ў кроку 5 прыкладу «Як дынамічна змяніць колер ячэйкі ў залежнасці ад яе значэння». Прыклад наладжанага вамі ўмоўнага фарматавання будзе выглядаць прыкладна так:
4. Калі вы задаволены колерам, націсніце OK. Вы ўбачыце, як створанае правіла будзе неадкладна прыменена да табліцы.

Статычна змяняць колер залівання спецыяльных вочак

Пасля наладжвання запаўненне застанецца нязменным, незалежна ад значэння ячэйкі.

Калі вы хочаце ўсталяваць пастаянны колер залівання для пустых вочак або вочак з формуламі, якія змяшчаюць памылкі, выкарыстоўвайце гэты метад:

1. Выберыце табліцу або дыяпазон і націсніце F5каб адкрыць дыялогавае акно перайсці да (Скачок), затым націсніце кнопку спецыяльны (Вылучыць).
2. У дыялогавым акне Перайсці да Спецыяльны (Вылучыце групу вочак) адзначце опцыю нарыхтоўкі (Пустыя вочкі), каб выбраць усе пустыя вочкі.Калі вы хочаце вылучыць ячэйкі, якія змяшчаюць формулы з памылкамі, адзначце опцыю формулы (формулы) > памылкі (Памылкі). Як вы можаце бачыць на малюнку вышэй, вам даступна мноства іншых налад.
3. Нарэшце, з дапамогай дыялогавага акна зменіце запаўненне выбраных вочак або задайце любыя іншыя параметры фарматавання Фарматаваць вочкі (Фарматаваць вочкі), як апісана ў раздзеле Змена запаўнення вылучаных вочак.

Не забывайце, што зробленыя такім чынам параметры фарматавання будуць захаваны нават пры запаўненні пустых вочак значэннямі або выпраўленні памылак у формулах. Цяжка ўявіць, што камусьці можа спатрэбіцца гэты шлях, хіба што ў мэтах эксперыменту 🙂