Угнежђене формуле и условна логика у Екселу

Ако свакодневно радите са табелама, пре или касније ћете наићи на њих. Угнежђене формуле и условна логика у ЕкселуОни су основа многих моћних модела: од једноставног пролазног/непролазног модела до сложених система провизија, попуста или класификације података.

Савладајте функције IF, AND, OR и NOT, заједно са модернијим варијацијама као што су Алтернативе за IF.SET и VLOOKUPТо чини сву разлику између табеле која „мање-више функционише“ и стабилне датотеке која се лако одржава и без изненађења у последњем тренутку. Хајде да то погледамо мирно, али директно и са јасним примерима.

Шта је условна логика у Екселу и зашто је толико важна?

Условна логика у Екселу се заснива на постављању питање тачно/нетачно и вратити другачији резултат у сваком случају. Срж свега овога је функција IF, која одговара идеји: „ако се деси А, уради Б; ако се не деси, уради Ц“.

Функција АКО проверава логички услов (логички тест) и, у зависности од тога да ли је услов испуњен или не, враћа различиту вредност: једну када је резултат ТАЧНО, а другу када је НЕТАЧНО. Важи за бројеве, као и за текст, датуме или друге резултате формула.

Што се тиче синтаксе, класична функција се пише овако: =ИФ(логички_тест;валуе_иф_труе;валуе_иф_фалсе)Логички тестови могу имати операторе поређења као што су =, <>, >, <, >= или <= и могу користити референце ћелија, константе или чак друге функције.

Аргумент вредност_ако_је_тачно Ово се враћа када је услов испуњен: може бити текст, број, друга формула или референца. Аргумент валуе_иф_фалсе Ово се враћа када услов није испуњен; ако га оставите празно, Excel ће подразумевано приказати FALSE, што често изазива забуну ако то нисте планирали.

Први једноставни примери са IF функцијом

Типична употреба функције IF је класификација података у две групе. На пример, можда желите да разликујете да ли је особа одрасли или малолетни у зависности од њиховог узраста:

У ћелији можете написати нешто овако: =IF(C3>=18; "Одрасли"; "Малолетни")Овде је логички тест C3>=18, резултат ако је ТАЧНО је „Одрасли“, а резултат ако је НЕТАЧНО је „Малолетни“.

Лепота ове структуре је у томе што функционише на исти начин са текстуалне вредностиНастављајући са истим примером, могли бисте питати да ли особа има право на попуст на основу ознаке коју сте јој доделили у другој колони: =IF(D3="Преко 18"; "Да"; "Не").

У суштини, логика која стоји иза свих ових примера је увек иста: Excel процењује тест, проверава да ли је тачно или нетачно и у сваком случају враћа другачији резултат у зависности од тога шта сте дефинисали.

Комбинујте АКО са логичким функцијама И, ИЛИ и НЕ

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

Функција Y враћа ТАЧНО само ако сви услови су тачниУ комбинацији са SI, типична структура би била: =IF(AND(услов1; услов2); вредност_ако_је_тачно; вредност_ако_је_нетачно)Веома је корисно када је потребно да се истовремено испуни неколико захтева, као што је старосни распон или неколико критеријума валидације.

Функција O враћа ТАЧНО ако испуњен је барем један од условаУ СИ формули, интеграција би била оваква: =АКО(ИЛИ(услов1; услов2); вредност_ако_је_тачно; вредност_ако_је_нетачно)Савршено је за случајеве када постоје различите ситуације које дају право на исти резултат, као што је неколико прихватљивих разлога за примену погодности.

Функција НЕ Инвертује логички резултат: ако је услов тачан, враћа FALSE, а ако је нетачан, враћа TRUE. У комбинацији са IF, имало би овај облик: =IF(NOT(услов); вредност_ако_је_тачно; вредност_ако_је_нетачно), нешто корисно када је оно што тражите управо супротан случај од теста.

Функције И и ИЛИ прихватају до 255 појединачних стањаМеђутим, у пракси се не препоручује ићи тако далеко, јер формуле постају веома тешке за читање, тестирање и одржавање. С друге стране, функција NOT дозвољава само један услов.

Практични примери са ДА, И, ИЛИ и НЕ

Замислите да имате бројеве у табели и желите да потврдите опсеге. Могли бисте да подесите нешто овако: =АКО(И(А2>0; Б2<100); ТАЧНО; НЕТАЧНО)Ова формула потврђује да је A2 веће од 0 и да је B2 мање од 100. Ако су оба тачна, враћа TRUE; ако било који не успе, враћа FALSE.

Такође савршено функционише са текстом. Пример би био =IF(AND(A3="Црвена"; B3="Зелена"; ТАЧНО, НЕТАЧНО)Овде, Y захтева да A3 садржи „црвено“, а B3 „зелено“. Ако се подудара само једно од та два, резултат ће бити НЕТАЧНО, јер оба услова нису истовремено испуњена.

Коришћење слова O мења понашање. На пример: =АКО(ИЛИ(А4>0; Б4<50); ТАЧНО; НЕТАЧНО) Вратиће TRUE ако је A4 веће од 0 или ако је B4 мање од 50; довољно је да било које од та два буде тачно да би се функција OR сматрала тачном.

Још један уобичајени случај: =IF(OR(A5="Црвена"; B5="Зелена"; ТАЧНО, НЕТАЧНО)Овде ће формула вратити TRUE ако барем једна од две ћелије садржи очекивани текст. За случајеве када желите да проверите више валидних опција, OR је идеалан.

Са НЕ, можете обрнути критеријум. На пример =АКО(НЕ(А6>50); ТАЧНО; НЕТАЧНО) Вратиће TRUE ако A6 није већи од 50. Или =АКО(НЕ(А7="Црвено"); ТАЧНО; НЕТАЧНО) Означиће ТАЧНО када се садржај A7 разликује од „црвеног“.

Рад са датумима користећи условну логику

Условна логика у Екселу није ограничена на бројеве или текст; са Датуми се такође могу упоређивати веома корисно за рокове, рокове или праћење.

Једноставан пример: =АКО(А2>Б2; ТАЧНО; НЕТАЧНО) Упоређује два датума; ако је датум у ћелији А2 каснији од датума у ​​ћелији Б2, формула враћа TRUE. Ово вам омогућава да брзо утврдите да ли се догађај догодио након референтног датума.

Ако желите да потврдите да је датум унутар опсега, можете користити Y: =АКО(И(А3>Б2; А3Ова формула ће проверити да ли је A3 каснији од B2 и ранији од C2. Ако су оба услова испуњена, сматра се да је унутар опсега.

Комбиновање О отвара више могућности. На пример: =АКО(ИЛИ(А4>Б2; А4Критеријум овде је да је А4 новији од Б2 или да је у року од 60 дана од Б2, користећи збир дана директно изнад датума.

А са НЕ, искључени случајеви се не могу идентификовати, као што је =АКО(НЕ(А5>Б2); ТАЧНО; НЕТАЧНО), што означава TRUE када A5 није касније од B2, односно када је раније или једнако референтном датуму.

Користите И, ИЛИ и НЕ у условном форматирању

Условна логика се не користи само у нормалним формулама, већ је кључна и за условно форматирање засновано на формуламаУ том контексту, често чак није потребно да стављате И, ИЛИ или НЕ унутар ДА.

На картици Почетна у програму Excel, унутар Условно форматирање > Ново правилоМожете изабрати „Користи формулу за одређивање ћелија које треба форматирати“ и директно написати логичку формулу, на пример =А2>Б2Ако је услов тачан, Excel примењује форматирање које изаберете.

Настављајући са примерима датума, можете користити =И(А3>Б2; А3 као формулу условног форматирања за истицање ћелија које садрже датум између B2 и C2. Докле год је тај услов испуњен, ћелија ће бити форматирана изабраним форматирањем.

Још једна шанса: =O(A4>B2; A4<B2+60) да би се истакли датуми који испуњавају барем један од тих критеријума. Коришћење О олакшава откривање неколико врста случајева у једном правилу условног форматирања.

На крају, могли бисте предложити =НЕ(А5>Б2) Да би се истакле вредности које не прелазе одређени датум, без потребе да се уоквирују у IF. Условно форматирање директно интерпретира тачан/нетачан резултат формуле.

Шта је угнежђена АКО формула и када је користити

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

Идеја је да Ексел процењује први тестАко је услов испуњен, враћа очекивани резултат; ако није, уместо давања коначне вредности, наставља са израчунавањем друге АКО функције која садржи још један тест, и тако даље. На овај начин можете повезати неколико нивоа доношења одлука.

Добро познат пример је претварање нумеричких оцена у словне оцене. Једноставна прва верзија може изгледати отприлике овако: =АКО(Д2>89; «А»; СИ(Д2>79; «Б»; СИ(Д2>69; «Ц»; СИ(Д2>59; «Д»; «Ф»)))).

Логика која стоји иза ове формуле је корак по корак: ако је оцена већа од 89, додељује се оцена А; у супротном, проверава се да ли је већа од 79 да би се доделила оцена Б; ако ни то није случај, покушава се са 69 за оцену Ц; затим 59 за оцену Д, а ако ниједно од тога није тачно, враћа се оцена Ф. То је класичан пример... АКО је угнежђено у ланцу.

Шема постаје сложенија ако желите да је додатно усавршите, на пример раздвајањем A+, A и A- или додавањем више нијанси. Формула би могла да нарасте до нечега попут овога: =АКО(B2>97; «А+»; SI(B2>93; «А»; SI(B2>89; «А-«; SI(B2>87; «Б+»; SI(B2>83; «Б»; SI(B2>79; «Б-«; SI(B2>77; «Ц+»; SI(B2>73; «Ц»; SI(B2>69; «Ц-«; АКО(B2>57; «Д+»; АКО(B2>53; «Д»; АКО(B2>49; «Д-«; «Ф»)))))))))))).

Иако функционално исправна, ова врста формуле Дуго је, заморно за писање и тешко за доказивање.Штавише, било какве промене граница или ознака захтевају ручно уређивање целог стринга, што значајно повећава вероватноћу увођења тешко откривајуће грешке.

Ограничења и проблеми угнежђених АКО формула

Ексел дозвољава угнежђивање до 64 АКО функције унутар исте формулеАли то не значи да је добра идеја приближити се тој граници. Након неколико нивоа, формула почиње да постаје неуправљива.

Међу главним недостацима су сложеност логике и ризик од тихих грешакаЛанац ИС-а (информационих система) може изгледати као да добро функционише у већини случајева, а ипак може да закаже у малом проценту ситуација које не откријете док већ нису изазвали проблем.

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

Ако се нађете у ситуацији да пишете АКО формулу која стално расте и додаје све више и више услова, вероватно је време да... предложите другачију стратегију: користите прикладнију функцију или реструктурирајте модел података.

Типичан пример угнежђене IF формуле коју је тешко одржавати јесте израчунавање провизија по нивоима. На пример: =АКО(Ц9>15000; 20%; СИ(Ц9>12500; 17,5%; СИ(Ц9>10000; 15%; СИ(Ц9>7500; 12,5%; СИ(Ц9>5000; 10%; 0)))))где повећавате провизију у складу са нивоом прихода.

Значај редоследа у угнежђеним условима

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

Настављајући са примером провизије, ако поређења напишете у растућем редоследу (прво >5000, затим >7500, итд.) уместо у опадајућем редоследу, видећете да високе вредности покрећу први важећи услов и зауставити евалуацијуНа пример, са приходом од 12.500, лоше уређена формула може вратити 10% једноставно зато што је већа од 5.000 и не проверава више заграде.

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

Стога, када се користе угнежђени IF изрази за опсеге или распоне, препоручљиво је јасно дефинисати редослед којим желите да се услови израчунавају и проверити неке граничне случајеве како би се осигурало да се логика понаша како се очекује.

У овом тренутку, има смисла размотрити робусније алтернативе, као што је коришћење референтне табеле са VLOOKUP-ом или прибегнути модернијим функцијама које поједностављују структуру формуле.

Користите VLOOKUP као алтернативу дугим угнежђеним IF наредбама

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

Да бисте то урадили, прво креирате табелу са две колоне: у прву постављате уређене референтне опсеге или вредности (на пример, границе разреда или приходне групе), а у другу... резултат повезан са сваким сегментом (словна оцена, проценат провизије итд.).

Када имате ту табелу, можете користити формулу попут ове: =VLOOKUP(C2; C5:D17; 2; ТАЧНО)где је C2 вредност коју треба тражити, C5:D17 је опсег који укључује табелу, 2 означава да желите резултат из друге колоне, а TRUE активира претрагу приближног опсега.

Још један сличан пример би био =VLOOKUP(B9; B2:C6; 2; TAČNO)Ова функција тражи вредност у ћелији Б9 у првој колони опсега Б2:Ц6 и враћа одговарајућу вредност из друге колоне. На овај начин замењујете Вишенивоско угнежђено IF помоћу једне функције претраге.

Предности су јасне: Референтна табела је видљива и може се уређивати.Можете променити ограничења, проценте или ознаке без додиривања формуле, а ако не желите да је корисници виде, увек је можете ставити на други лист унутар исте радне свеске.

SETIFY: природна еволуција угнежђених IF-ова

У модерним верзијама програма Excel (Office 2019, 2021 и Microsoft 365) имате функцију ДА. ПОСТАВИТЕ, дизајниран управо да ублажи потребу за ланчаним повезивањем IF наредби једну за другом и учини код много читљивијим.

IFS.SET вам омогућава да дефинишете до 127 парова „логички тест – резултат“ у истој формули. Уместо да имате само једну вредност if-false, наводите случајеве један за другим, а Excel враћа одговарајући резултат. први услов који је испуњен.

Узимајући за пример оцене, могли бисте трансформисати класичну формулу неколико међуфункционалних функционала у нешто попут =IFS(D2>89; "А"; D2>79; "Б"; D2>69; "Ц"; D2>59; "Д"; TRUE; "Ф")Последњи пар (TRUE; «F») делује као „свеобухватни“ за сваки случај који није обухваћен горе наведеним условима.

Велика корист је у томе што Заплет заграда нестаје типично за угнежђене IF наредбе, а формула је много јаснија за читање и одржавање, посебно када имате неколико различитих одељака или сценарија.

У напреднијим контекстима, можете чак комбиновати IF.SET са логичким функцијама као што су AND или OR у тестовима, слично као што бисте то урадили унутар класичног IF, али одржавајући организованију структуру.

Међутим, пошто не постоји генерички аргумент value_if_false, морате експлицитно дефинишите супротан случај, обично са коначним паром TRUE – подразумевани резултат, тако да формула увек враћа нешто разумно.

Додатни примери напредне употребе АКО и условне логике

Поред типичних примера белешки и провизија, функција IF и њене варијанте могу се комбиновати са другим Excel функцијама за решавање прилично разноврсни сценарији Из дана у дан.

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

Такође можете проверити вредности као текстНа пример, да би се потврдило да ли књига припада одређеном аутору. Формула би била слична =IF(B2="Стивен Кинг"; "Да"; "Не")Ово се такође односи на листе држава, прописа или било ког другог текста који треба да проверите.

Постоје случајеви када има смисла угнеждите IF да бисте извршили неколико узастопних провераТипичан пример је праћење пошиљке: ако је пакет означен као послат, враћате поруку; ако није, проверавате да ли је спреман; ако није, означавате „на чекању“ или сличан статус.

Штавише, СИ се може интегрисати са другим функцијама као што су ИЛИ, И, ПОДУПАР, СПАЈИ и још много тога. Мало сложенији пример би био праћење цене акције и приказивање текста који показује да ли она расте или пада, повезујући процентуалну промену, нешто попут „Рост 5%“ или „Пад 3%“.

У комбинацији са функцијама за обраду грешака као што су АКО.ГРЕШКА или АКО.НДМожете да решите ситуације у којима је евалуација неважећа, враћајући прилагођене поруке уместо непријатељских кодова грешака.

Уобичајене грешке при раду са IF и условном логиком

Један од најчешћих проблема при коришћењу IF ​​функције јесте наилажење на то да формула враћа наизглед бесмислену 0То је обично зато што нисте експлицитно дефинисали аргументе value_if_true и value_if_false или зато што једна грана враћа празну вредност док се друга интерпретира као број.

Још једна класична грешка је порука #НАМЕ?, што скоро увек указује на то да је у формули нешто погрешно написано: назив функције, текст без наводника, погрешан сепаратор или референца на функцију која не постоји у тој верзији програма Excel.

Да би се ово избегло, препоручљиво је Проверите да ли су текстови увек под наводницима, проверите да ли логички тест има исправну синтаксу (оператори, заграде итд.) и да ли имена функција тачно одговарају верзији на вашем језику.

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

Коначно, задржите функције попут АКО.ГРЕШКА или АКО.НД да бисте омотали главне формуле и елегантно забележили грешке током извршавања, приказујући разумљив текст уместо кода грешке који би могао да збуни корисника.

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