2 спосабу змяніць фон у Excel

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

Усім вядома, што рэдагаванне фону просты ячэйкі - гэта простая працэдура. Націсніце на «колер фону». Але што, калі вам патрэбна карэкцыя колеру на аснове пэўнага змесціва ячэйкі? Як я магу зрабіць гэта аўтаматычна? Далей прыводзіцца серыя карыснай інфармацыі, якая дазволіць вам знайсці правільны шлях для выканання ўсіх гэтых задач.

Дынамічная змена колеру фону ячэйкі

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

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

Дапусцім, у вас ёсць набор тавараў у розных штатах з іх коштамі, і вам трэба ведаць, якія з іх каштуюць даражэй 3,7 долараў. Таму мы вырашылі вылучыць чырвоным колерам тыя прадукты, якія вышэй гэтага значэння. А клеткі, якія маюць аднолькавае або большае значэнне, было вырашана афарбаваць у зялёны адценне.

нататка: Скрыншот зроблены ў версіі праграмы 2010 года. Але гэта ні на што не ўплывае, так як паслядоўнасць дзеянняў аднолькавая, незалежна ад таго, якой версіяй - апошняй ці не - карыстаецца чалавек.

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

1. Выберыце ячэйкі, у якіх адценне трэба адрэдагаваць. Напрыклад, дыяпазон $B$2:$H$10 (назвы слупкоў і першы слупок, у якім пералічаны назвы штатаў, выключаны з выбаркі).

2. Націсніце на "Дадому" у групе "Стыль". Будзе прадмет «Умоўнае фарматаванне». Там таксама трэба выбраць «Новае правіла». У ангельскай версіі Excel паслядоўнасць дзеянняў такая: «Галоўная», «Група стыляў», «Умоўнае фарматаванне > Новае правіла».

3. У якое адкрылася акне пастаўце галачку «Фарматаваць толькі ячэйкі, якія змяшчаюць» («Фарматаваць толькі ячэйкі, якія змяшчаюць» у англійскай версіі).

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

5. Далей націсніце на кнопку "Фармат". З'явіцца акно з зонай выбару колеру фону злева. Але перад гэтым трэба адкрыць укладку «Запоўніць» («Напоўніць»). У дадзеным выпадку гэта чырвоны колер. Пасля гэтага націсніце на кнопку «ОК».

6. Затым вы вернецеся да акна «Новае правіла фарматавання», але ўжо ўнізе гэтага акна вы можаце праглядзець, як будзе выглядаць гэтая ячэйка. Калі ўсё нармальна, трэба націснуць на кнопку «ОК».

У выніку вы атрымаеце нешта накшталт гэтага:

Далей трэба дадаць яшчэ адно ўмова, гэта значыць змяніць фон вочак са значэннямі менш 3.45 на зялёны. Каб выканаць гэта заданне, неабходна націснуць на «Новае правіла фарматавання» і паўтарыце крокі вышэй, толькі ўмова павінна быць зададзена як «менш або эквівалентна» (у англійскай версіі «менш або роўна», а затым напісаць значэнне. У канцы трэба націснуць кнопку «ОК».

Цяпер табліца адфарматаваная такім чынам.

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

Рэкамендацыя: Пры неабходнасці вы можаце выкарыстоўваць аналагічны метад фарматавання, адрэдагаваўшы не фон, а шрыфт. Для гэтага ў якое з'явілася на пятым этапе акне фарматавання трэба выбраць ўкладку «Шрыфт» і выконвайце падказкі ў акне. Усё інтуітыўна зразумела, разабрацца можа нават пачатковец.

У выніку вы атрымаеце такую ​​табліцу:

Як захаваць колер ячэйкі ранейшым, нават калі значэнне змяняецца?

Задача: Вы павінны афарбаваць фон так, каб ён ніколі не змяніўся, нават калі фон зменіцца ў будучыні.

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

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

Вылучыце ўсе вочкі, якія змяшчаюць пэўную ўмову

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

Калі вам неабходна пазначыць ячэйкі з пэўным значэннем спецыяльным фонам, вам трэба перайсці на ўкладку "Дадому" і абярыце «Знайсці і выбраць» - «Знайсці».

Увядзіце патрэбныя значэння і націсніце «Знайсці ўсё».

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

У нашым папярэднім прыкладзе, калі мы хацелі знайсці ўсе цэны на паліва ад 3,7 да 3,799 долараў, мы маглі б удакладніць пошукавы запыт.

Цяпер абярыце любы са значэнняў, якія знайшла праграма ўнізе дыялогавага акна, і націсніце на адно з іх. Пасля гэтага націсніце камбінацыю клавіш «Ctrl-A», каб выбраць усе вынікі. Далей націсніце на кнопку «Зачыніць». 

Вось як выбраць усе вочкі з пэўнымі значэннямі з дапамогай функцыі «Знайсці ўсё». У нашым прыкладзе нам трэба знайсці ўсе цэны на паліва вышэй за $3,7, і, на жаль, Excel не дазваляе зрабіць гэта з дапамогай функцыі «Знайсці і замяніць».

Тут выяўляецца «бочка мёду», таму што ёсць яшчэ адзін інструмент, які дапаможа ў такіх складаных задачах. Гэта называецца Выбраць спецыяльныя вочкі. Гэты дадатковы кампанент (які трэба ўсталёўваць у Excel асобна) дапаможа:

• знайсці ўсе значэнні ў пэўным дыяпазоне, напрыклад, ад -1 да 45,
• атрымаць максімальнае або мінімальнае значэнне ў слупку,
• знайсці радок або дыяпазон,
• знайсці клеткі па колеры фону і многае іншае.

Пасля ўстаноўкі надбудовы проста націсніце кнопку «Выбраць па значэнні» («Выбраць па значэнні»), а затым удакладніце пошукавы запыт у акне адона. У нашым прыкладзе мы шукаем лікі большыя за 3,7. Прэса «Выбраць» («Выбраць»), і праз секунду вы атрымаеце такі вынік:

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

Змена фону выбраных вочак праз акно «Фарматаваць вочкі».

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

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

Мы абярэм аранжавы адценне, але вы можаце выбраць любы іншы.

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

У выніку атрымаецца такая табліца:

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

Рэдагаванне колеру фону для спецыяльных вочак (пустых або з памылкамі пры напісанні формулы)

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

Прымяненне формулы для рэдагавання фону

Тут колер ячэйкі будзе рэдагавацца аўтаматычна ў залежнасці ад яе значэння. Гэты метад вельмі дапамагае карыстальнікам і запатрабаваны ў 99% сітуацый.

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

1. На таб "Дадому" вам трэба націснуць «Умоўнае фарматаванне» ->  «Новае правіла» (тое самае, што і ў кроку 2 першага раздзела «Дынамічнае змяненне колеру фону».

2. Далей неабходна выбраць пункт «Выкарыстайце формулу для вызначэння...».

3. Увядзіце формулу =IsBlank() (ISBLANK in the version), if you want to edit the background of an empty cell, or =IsError() (ISERROR in the version), if you need to find a cell where there is an erroneously written formula. Since in this case we need to edit empty cells, we enter the formula =IsBlank(), а затым змясціце курсор паміж круглымі дужкамі і націсніце на кнопку побач з полем уводу формулы. Пасля гэтых маніпуляцый трэба ўручную вылучыць дыяпазон вочак. Акрамя таго, вы можаце самастойна ўказаць дыяпазон, напрыклад, =Пусты(B2:H12).

4. Пстрыкніце па кнопцы «Фарматаваць», абярыце адпаведны колер фону і зрабіце ўсё, як апісана ў п. 5 раздзела «Дынамічнае змяненне колеру фону ячэйкі», пасля чаго націсніце «ОК». Там жа можна паглядзець, якога колеру будзе клетка. Акно будзе выглядаць прыкладна так.

5. Калі вам падабаецца фон ячэйкі, вы павінны націснуць на кнопку «ОК», і змены будуць неадкладна ўнесены ў табліцу.

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

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

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

1. Выберыце дакумент або некалькі вочак і націсніце F5, каб адкрыць акно «Перайсці», а затым націсніце кнопку «Вылучыце».
2. In the dialog box that opens, select the “Blanks” or “Empty cells” button (depending on the version of the program – or English) to select empty cells.
3. Калі вам трэба вылучыць вочкі, у якіх ёсць формулы з памылкамі, варта выбраць пункт «Формулы» і пакіньце адзіны сцяжок побач са словам «Памылкі». Як вынікае з скрыншота вышэй, вочкі можна выбіраць па любых параметрах, і кожная з апісаных налад даступная пры неабходнасці.
4. Нарэшце, вам трэба змяніць колер фону выбраных вочак або наладзіць іх любым іншым спосабам. Для гэтага скарыстайцеся спосабам, апісаным вышэй.

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

Як атрымаць максімум ад Excel?

Як актыўныя карыстальнікі Microsoft Excel, вы павінны ведаць, што ён змяшчае шмат функцый. Некаторыя з іх мы ведаем і любім, а іншыя застаюцца загадкавымі для звычайнага карыстальніка, і вялікая колькасць блогераў спрабуюць праліць на іх хоць трохі святла. Але ёсць агульныя задачы, якія кожны з нас павінен будзе выканаць, і Excel не ўводзіць некаторыя функцыі або інструменты для аўтаматызацыі некаторых складаных дзеянняў.

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

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

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