Умоўнае фарматаванне ў Excel – падрабязна з прыкладамі

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

Як зрабіць умоўнае фарматаванне ў Excel

Вы можаце знайсці «Умоўнае фарматаванне» на самай першай укладцы стужкі, перайшоўшы ў раздзел «Стылі». 

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

Далей трэба выбраць адпаведны аператар для параўнання патрэбнай зменнай з лікам. Ёсць чатыры аператара параўнання - больш, менш, роўна і паміж. Яны пералічаны ў меню правілаў. 

Далей мы задалі шэраг лікаў у дыяпазоне A1:A11.

Давайце адфарматаваць прыдатны набор даных. Затым адкрываем меню налад «Умоўнае фарматаванне» і задаем патрэбныя крытэры выбару дадзеных. У выпадку з намі, напрыклад, мы абярэм крытэрый «Больш». 

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

Затым завяршыце канфігурацыю кнопкай OK.

Для чаго патрэбна ўмоўнае фарматаванне?

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

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

Возьмем прыклад.

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

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

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

І сапраўды, арыентавацца нашмат лягчэй. 

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

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

Па значэнні іншай ячэйкі

Зараз давайце паглядзім на наступны практычны прыклад.

Дапусцім, у нас ёсць такая табліца, і перад намі стаіць задача вылучыць радкі, якія змяшчаюць пэўныя значэння. 

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

Паслядоўнасць нашых дзеянняў у гэтай сітуацыі будзе наступнай:

1. Выберыце дыяпазон значэнняў. 
2. Націсніце кнопку «Умоўнае фарматаванне» - «Стварыць правіла».
3. Звярніце ўвагу, што ў нашым выпадку формулу трэба ўжыць у выглядзе правіла. Далей выкарыстоўваем функцыю IFкаб вылучыць адпаведныя радкі. 

Далей запоўніце радкі, як паказана на гэтым малюнку.

Важна! Спасылка на радкі павінна быць абсалютная. Калі мы маем на ўвазе клетку, то ў дадзеным выпадку яна змешаная (з фіксацыяй слупка).

Аналагічным чынам ствараецца правіла для тых працоўных праектаў, якія на сённяшні дзень не завершаны. 

Вось так выглядаюць нашы крытэрыі.

І вось у выніку мы маем такую ​​базу.

Некалькі ўмоў

Для яснасці возьмем дыяпазон A1:A11 з табліцы, каб прадэманстраваць, як на рэальным практычным прыкладзе можна выкарыстоўваць некалькі ўмоў для вызначэння фарматавання ячэйкі.

Самі ўмовы наступныя: калі лік у вочку перавышае значэнне 6, то яно вылучаецца чырвоным колерам. Калі ён зялёны, значыць, колер зялёны. І, нарэшце, самыя вялікія лічбы, больш за 20, будуць вылучаны жоўтым колерам. 

Існуе некалькі спосабаў выканання ўмоўнага фарматавання па некалькіх правілах.

1. Спосаб 1. Выбіраем наш дыяпазон, пасля чаго ў меню налад умоўнага фарматавання выбіраем такое правіла выбару вочак, як «Яшчэ». Злева пішацца лічба 6, а справа задаецца фарматаванне. У нашым выпадку заліванне павінна быць чырвоным. Пасля гэтага цыкл паўтараецца двойчы, але ўжо зададзены іншыя параметры – больш за 10 і зялёны колер і больш за 20 і жоўты колер адпаведна. Вы атрымаеце такі вынік.
   

   

2. Спосаб 2. Перайдзіце ў галоўнае меню налад інструмента Excel «Умоўнае фарматаванне». Там знаходзім меню «Стварыць правіла» і пстрыкаем па гэтым пункце левай кнопкай мышы.
   

   

    Пасля гэтага ў «Далей» выбіраем пункт «Выкарыстоўваць формулу …» (выдзелены на малюнку чырвонай рамкай) і задаем першая ўмова. Пасля гэтага націсніце OK. І затым цыкл паўтараецца для наступных умоў гэтак жа, як апісана вышэй, але выкарыстоўваецца формула.

   

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

А вось скрыншот для лепшага разумення.

Напрыклад, у нас ёсць лік 24. Яно адпавядае ўсім тром умовам адначасова. У гэтым выпадку будзе заліванне, якое адпавядае першай умове, нягледзячы на ​​тое, што яна больш падпадае пад трэцюю. Такім чынам, калі важна, каб лічбы вышэй за 20 былі запоўненыя жоўтым, трэцяя ўмова павінна быць пастаўлена на першае месца. 

Умоўнае фарматаванне даты

Зараз давайце паглядзім, як умоўнае фарматаванне працуе з датамі. У нас такі класны асартымент.

У гэтым выпадку вам трэба ўсталяваць правіла ўмоўнага фарматавання, напрыклад «Дата».

Пасля гэтага адкрываецца дыялогавае акно з цэлым наборам умоў. Падрабязна з імі вы можаце азнаёміцца ​​на гэтым скрыншоце (яны пералічаны ў левай частцы экрана ў выглядзе спісу).

Нам проста трэба выбраць прыдатны і націснуць кнопку «ОК».

Мы бачым, што тыя даты, якія адносяцца да апошняга тыдня на момант малявання гэтага дыяпазону, былі вылучаны чырвоным. 

Выкарыстанне формул

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

Для гэтага ў меню ўмоўнага фарматавання націсніце кнопку «Стварыць правіла», а затым абярыце пункт, паказаны на скрыншоце.

Радкі (па значэнні ячэйкі)

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

Як стварыць правіла

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

Для пачатку трэба знайсці на стужцы ўкладкі «Галоўная» пункт «Умоўнае фарматаванне». Яго можна пазнаць па характэрным рознакаляровым вочкам рознага памеру з малюнкам побач з перакрэсленым знакам роўнасці. 

Ёсць кнопка «Стварыць правіла». Вы таксама можаце націснуць кнопку «Кіраваць правіламі», пасля чаго адкрыецца дыялогавае акно, праз якое вы таксама можаце стварыць правіла, націснуўшы адпаведную кнопку ў левым верхнім куце. 

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

Правілы выбару клетак

Добра, мы так шмат гаворым пра тое, як ствараць правілы, але што гэта? Правіла выбару вочак адносіцца да функцый, якія ўлічваюцца праграмай, каб вырашыць, як адфарматаваць вочкі, якія ім падыходзяць. Напрыклад, умовы могуць быць больш за пэўную лічбу, менш за пэўную лічбу, формула і г.д. Іх цэлы набор. З імі можна азнаёміцца ​​і папрактыкавацца ў выкарыстанні «ў пясочніцы». 

Фарматаванне ўсіх ячэек на аснове іх значэнняў

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

Фарматуйце толькі ўнікальныя або дублікаты вочак

Існуе вялікая колькасць правілаў, непасрэдна ўключаных у праграму. З імі можна азнаёміцца ​​ў меню «Умоўнае фарматаванне». У прыватнасці, для таго, каб фарматаванне прымянялася выключна да паўтаральных значэнняў або толькі да унікальным, існуе спецыяльная опцыя - «Паўторныя значэння».

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

Фармат значэнняў у дыяпазоне ніжэй і вышэй сярэдняга

Для фарматавання значэнняў ніжэй або вышэй сярэдняга таксама ёсць спецыяльная опцыя ў тым жа меню. Але трэба выбраць іншае падменю – «Правілы выбару першага і апошняга значэнняў». 

Фарматаванне толькі першага і апошняга значэнняў

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

Фарматаванне толькі вочак з пэўным змесцівам

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

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