Условно обликовање у Екцелу: Ултимативни водич са примерима

Условно обликовање једна је од најједноставнијих, али моћних функција у Екцел табелама.

Као што назив говори, можете користити условно обликовање у Екцелу када желите да истакнете ћелије које испуњавају одређени услов.

Омогућава вам да брзо додате слој визуелне анализе свом скупу података. Помоћу условног обликовања у Екцелу можете да креирате топлотне мапе, прикажете иконе за повећање/смањивање, Харвеи мехуриће и још много тога.

Коришћење условног обликовања у Екцелу (примери)

У овом водичу ћу вам показати седам невероватних примера коришћења условног обликовања у Екцелу:

• Брзо идентификујте дупликате помоћу условног обликовања у Екцелу.
• Означите ћелије са вредношћу већом/мањом од броја у скупу података.
• Истицање горњих/доњих 10 (или 10%) вредности у скупу података.
• Истицање грешака/празнина помоћу условног обликовања у Екцелу.
• Креирање топлотних карата помоћу условног обликовања у Екцелу.
• Означите сваки Н -ти ред/колону помоћу условног обликовања.
• Претражите и означите помоћу условног обликовања у Екцелу.

1. Брзо идентификујте дупликате

Условно обликовање у Екцелу може се користити за идентификацију дупликата у скупу података.

Ево како то можете учинити:

• Изаберите скуп података у којем желите да истакнете дупликате.
• Идите на Почетна -> Условно обликовање -> Истицање правила ћелије -> Дуплиране вредности.
• У дијалогу Дуплицате Валуес проверите да ли је Дуплицате селецтед у левом падајућем менију. Помоћу десног падајућег менија можете одредити формат који ће се применити. Постоје неки постојећи формати које можете користити или одредите свој формат помоћу опције Прилагођени формат.
• Притисните ОК.

Ово би тренутно означило све ћелије које имају дупликат у изабраном скупу података. Ваш скуп података може бити у једној колони, у више колона или у не-суседном опсегу ћелија.

Такође видети: Ултимативни водич за проналажење и уклањање дупликата у програму Екцел.

2. Означите ћелије са вредношћу већом/мањом од броја

Можете користити условно обликовање у Екцелу за брзо означавање ћелија које садрже вредности веће/мање од наведене вредности. На пример, истицање свих ћелија са продајном вредношћу мањом од 100 милиона или истицање ћелија са ознакама мањим од прага проласка.

Ево корака за то:

• Изаберите цео скуп података.
• Идите на Почетна -> Условно обликовање -> Правила за истицање ћелија -> Више од… / Мање од…
• На основу опције коју изаберете (веће или мање од), отворио би се оквир за дијалог. Рецимо, одаберете опцију „Веће од“. У оквир за дијалог унесите број у поље са леве стране. Намера је да се означе ћелије које имају број већи од овог наведеног броја.

• Наведите формат који ће се применити на ћелије које испуњавају услов помоћу падајућег менија са десне стране. Постоје неки постојећи формати које можете користити или одредите сопствени формат помоћу опције Прилагођени формат.
• Притисните ОК.

Ово би тренутно означило све ћелије са вредностима већим од 5 у скупу података.Напомена: Ако желите да истакнете вредности веће од једнаке 5, требало би поново да примените условно обликовање са критеријумом „Једнако према“.

Исти процес се може применити за означавање ћелија чија је вредност мања од наведених вредности.

3. Истицање врха/дна 10 (или 10%)

Условно обликовање у Екцелу може брзо идентификовати 10 најбољих ставки или 10% најбољих из скупа података. Ово би могло бити корисно у ситуацијама у којима желите брзо видјети најбоље кандидате према резултатима или вриједностима најбоље понуде у подацима о продаји.

Слично томе, такође можете брзо идентификовати 10 најнижих ставки или 10% најнижих у скупу података.

Ево корака за то:

• Изаберите цео скуп података.
• Идите на Почетна -> Условно обликовање -> Горња / доња правила -> 10 најбољих ставки (или %) / Доле 10 ставки (или %).
• На основу онога што одаберете, отвориће се оквир за дијалог. Рецимо да сте одабрали 10 најбољих ставки, а затим би се отворио оквир за дијалог као што је приказано испод:
• Наведите формат који ће се применити на ћелије које испуњавају услов помоћу падајућег менија са десне стране. Постоје неки постојећи формати које можете користити или одредите свој формат помоћу опције Прилагођени формат.
• Притисните ОК.

Ово би тренутно истакнуло првих 10 ставки у изабраном скупу података. Имајте на уму да ово функционише само за ћелије које у себи имају нумеричку вредност.

Такође, ако имате мање од 10 ћелија у скупу података, а одаберете опције за истицање Топ 10 ставки/Доњих 10 ставки, тада ће све ћелије бити истакнуте.

Ево неколико примера како би условно обликовање функционисало:

4. Истицање грешака/празнина

Ако радите са много нумеричких података и прорачуна у Екцелу, знали бисте колико је важно идентификовати и третирати ћелије које имају грешке или су празне. Ако се ове ћелије користе у даљим прорачунима, то би могло довести до погрешних резултата.

Условно обликовање у програму Екцел може вам помоћи да брзо идентификујете и означите ћелије које имају грешке или су празне.

Претпоставимо да имамо скуп података као што је приказано испод:

Овај скуп података има празну ћелију (А4) и грешке (А5 и А6).

Ево корака за означавање ћелија које су празне или садрже грешке:

• Одаберите скуп података у којем желите означити празне ћелије и ћелије с грешкама.
• Идите на Почетна -> Условно обликовање -> Ново правило.
• У дијалошком оквиру Ново правило обликовања одаберите Користи формулу да бисте одредили које ћелије обликовати.
• Унесите следећу формулу у поље у одељку „Измени опис правила“:
  = ИЛИ (ИСБЛАНК (А1), ИСЕРРОР (А1))
  • Горња формула проверава све ћелије за два услова - да ли је празно или не и да ли има грешку или не. Ако је било који од услова ТРУЕ, враћа ТРУЕ.
• Подесите формат који желите да примените на ћелије које су празне или имају грешке. Да бисте то урадили, кликните на дугме Обликовање. Отвориће се оквир за дијалог „Обликовање ћелија“ у коме можете одредити формат.
• Притисните У реду.

Ово би тренутно истакнуло све ћелије које су или празне или имају грешке.

Белешка: Не морате да користите цео опсег А1: А7 у формули у условном обликовању. Горе поменута формула користи само А1. Када примените ову формулу на цео опсег, Екцел проверава једну по једну ћелију и прилагођава референцу. На пример, када проверава А1, користи формулу = ИЛИ (ИСБЛАНК (А1), ИСЕРРОР (А1)). Када проверава ћелију А2, тада користи формулу = ИЛИ (ИСБЛАНК (А2), ИСЕРРОР (А2)). Аутоматски прилагођава референцу (пошто су то релативне референце) у зависности од тога која се ћелија анализира. Дакле, не морате писати засебну формулу за сваку ћелију. Екцел је довољно паметан да сам промени референцу ћелије 🙂

Такође видети: Коришћење ИФЕРРОР -а и ИСЕРРОР -а за решавање грешака у екцелу.

5. Креирање топлотних карата

Топлотна мапа је визуелни приказ података где боја представља вредност у ћелији. На пример, можете да креирате топлотну мапу где је ћелија са највећом вредношћу обојена зеленом бојом и долази до померања ка црвеној боји како се вредност смањује.

Нешто као што је приказано испод:

Горњи скуп података има вредности између 1 и 100. Ћелије су истакнуте на основу вредности у њему. 100 добија зелену боју, 1 добија црвену боју.

Ево корака за креирање топлотних мапа помоћу условног обликовања у Екцелу.

• Изаберите скуп података.
• Идите на Почетна -> Условно обликовање -> Размере боја и изаберите једну од шема боја.

Чим кликнете на икону топлотне карте, она ће применити обликовање на скуп података. Можете изабрати више градијената боја. Ако нисте задовољни постојећим опцијама боја, можете изабрати још правила и навести боју коју желите.

Напомена: На сличан начин можете применити и Дата Бард и Ицон скупове.

6. Означите сваки други ред/колону

Можда ћете желети да истакнете алтернативне редове како бисте повећали читљивост података.

То се назива линија зебре и може бити посебно корисно ако штампате податке.

Сада постоје два начина за стварање ових линија зебре. Најбржи начин је претварање табеларних података у Екцел табелу. Аутоматски је применио боју на наизменичне редове. Више о томе можете прочитати овде.

Други начин је коришћење условног обликовања.

Претпоставимо да имате скуп података као што је приказано испод:

Ево корака за означавање алтернативних редова помоћу условног обликовања у Екцелу.

• Изаберите скуп података. У горњем примеру изаберите А2: Ц13 (који искључује заглавље). Ако желите да укључите и заглавље, изаберите цео скуп података.
• Отворите оквир за дијалог Условно обликовање (Почетна-> Условно обликовање-> Ново правило) [Тастерска пречица - Алт + О + Д].
• У оквиру за дијалог изаберите „Користи формулу за одређивање ћелија које треба форматирати“.
• Унесите следећу формулу у поље у одељку „Измени опис правила“:
  = ИСОДД (РОВ ())

• Горња формула проверава све ћелије и ако је РОВ број ћелије непаран, враћа ТРУЕ. Наведени условни формат би се применио на све ћелије које враћају ТРУЕ.
• Подесите формат који желите да примените на ћелије које су празне или имају грешке. Да бисте то урадили, кликните на дугме Обликовање. Отворит ће се оквир за дијалог „Обликовање ћелија“ у којем можете одредити формат.
• Притисните ОК.

То је то! Алтернативни редови у скупу података биће истакнути.

У многим случајевима можете користити исту технику. Све што треба да урадите је да користите одговарајућу формулу у условном обликовању. Ево неколико примера:

• Означите алтернативне парне редове: = ИСЕВЕН (РОВ ())
• Означите наизменично додавање редова: = ИСОДД (РОВ ())
• Означите сваки 3. ред: = МОД (РОВ (), 3) = 0

7. Претражите и означите податке помоћу условног обликовања

Ово је помало напредна употреба условног обликовања. Због тога бисте изгледали као Екцел рок звезда.

Претпоставимо да имате скуп података као што је приказано испод, са називом производа, продајним представником и географијом. Идеја је да откуцате низ у ћелију Ц2, а ако се подудара са подацима у било којој ћелији (ћелијама), то би требало да буде истакнуто. Нешто као што је приказано испод:

Ево корака за креирање ове функције Претраживање и истицање:

• Изаберите скуп података.
• Идите на Почетна -> Условно обликовање -> Ново правило (Пречица на тастатури - Алт + О + Д).
• У дијалогу Ново правило обликовања изаберите опцију „Користи формулу за одређивање ћелија које треба форматирати“.
• Унесите следећу формулу у поље у одељку „Измени опис правила“:
  = АНД ($ Ц $ 2 ””, $ Ц $ 2 = Б5)
• Подесите формат који желите да примените на ћелије које су празне или имају грешке. Да бисте то урадили, кликните на дугме Обликовање. Отвориће се оквир за дијалог „Обликовање ћелија“ у коме можете одредити формат.
• Притисните ОК.

То је то! Сада када унесете било шта у ћелију Ц2 и притиснете ентер, означиће све одговарајуће ћелије.

Како ово ради?

Формула која се користи у условном обликовању процењује све ћелије у скупу података. Рецимо да уђете у Јапан у ћелију Ц2. Сада би Екцел проценио формулу за сваку ћелију.

Формула би вратила ТРУЕ за ћелију када су испуњена два услова:

• Ћелија Ц2 није празна.
• Садржај ћелије Ц2 потпуно се подудара са садржајем ћелије у скупу података.

Дакле, све ћелије које садрже текст Јапан биће истакнуте.

Преузмите датотеку примера

Можете користити исту логику за креирање варијација као што су:

• Означите цео ред уместо ћелије.
• Истакните чак и када постоји делимично подударање.
• Истакните ћелије/редове док куцате (динамички) [Свидеће вам се овај трик :)].

Како уклонити условно обликовање у програму Екцел

Након што се примени, условно обликовање остаје на месту ако га не уклоните ручно. Као најбољу праксу, условно обликовање нека се примењује само на оне ћелије где вам је потребно.

Пошто је променљив, то може довести до споре радне књиге програма Екцел.

Да бисте уклонили условно обликовање:

• Изаберите ћелије из којих желите да уклоните условно обликовање.
• Идите на Почетна -> Условно обликовање -> Обриши правила -> Обриши правила из изабраних ћелија.
  • Ако желите да уклоните условно обликовање са целог радног листа, изаберите Обриши правила из целог листа.

Важне ствари које треба знати о условном обликовању у програму Екцел

• Условно обликовање у променљивој форми. То може довести до споре радне свеске. Користите га само када је потребно.
• Када копирате ћелије за лепљење које садрже условно обликовање, копира се и условно обликовање.
• Ако примените више правила на исти скуп ћелија, сва правила остају активна. У случају било каквог преклапања, предност се даје последњем примењиваном правилу. Можете, међутим, променити редослед тако што ћете променити редослед у дијалогу Управљање правилима.