Екцел

19 савета за угнежђене ИФ формуле

19 Tips Nested If Formulas

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





Али један ИФ често води до другог, а када комбинујете више од пар ИФ -а, ваше формуле могу почети да личе на мале Франкенстеине :)

Да ли су угнежђени ИФ зли? Да ли су понекад неопходни? Које су алтернативе?





Читајте даље да бисте сазнали одговоре на ова питања и више ...

1. Основни ИФ

Пре него што говоримо о угнежђеним ИФ, хајде да брзо прегледамо основну структуру ИФ:



 
= IF (test,[true],[false])

ИФ функција покреће тест и изводи различите радње у зависности од тога да ли је резултат тачан или нетачан.

Обратите пажњу на угласте заграде ... то значи да су аргументи опционални. Међутим, морате доставити било вредност за труе, или вредност за фалсе.

За илустрацију, овде користимо ИФ за проверу резултата и израчунавање „Пролаза“ за оцене од најмање 65:

Основна ИФ функција - повратак

Ћелија Д3 у примеру садржи ову формулу:

 
= IF (C3>=65,'Pass')

Што се може прочитати овако: ако је резултат у Ц3 најмање 65, вратите 'Пасс'.

Међутим, имајте на уму да ако је резултат мање од 65, ИФ враћа ФАЛСЕ, јер нисмо навели вредност ако је фалсе. Да бисмо приказали „Неуспех“ за непролазне оцене, можемо додати „Неуспех“ као лажни аргумент овако:

 
= IF (C3>=65,'Pass','Fail')

Основна ИФ функција - са додатом вредношћу за фалсе

Видео: Како изградити логичке формуле .

2. Шта значи гнежђење

Гнежђење једноставно значи комбиновање формула једна у другој, тако да једна формула обрађује резултат друге. На пример, ево формуле у којој је функција ТОДАИ угнежђена унутар функције МОНТХ:

 
= MONTH ( TODAY ())

Функција ТОДАИ враћа тренутни датум унутар функције МОНТХ. МОНТХ функција узима тај датум и враћа текући месец. Чак и умерено сложене формуле често користе гнежђење, па ћете свуда видети гнежђење у сложенијим формулама.

3. Једноставан угнежђени ИФ

Уметнути ИФ су само још два ИФ израза у формули, где се један ИФ израз појављује унутар другог.

За илустрацију, у наставку сам проширио оригиналну формулу пролаз/неуспех изнад за руковање „непотпуним“ резултатима додавањем функције ИФ и уметањем једног ИФ у други:

Основни угнежђени ИФ

 
= IF (C3='','Incomplete', IF (C3>=65,'Pass','Fail'))

Спољни ИФ се прво покреће и тестира да ли је Ц3 празан. Ако је тако, спољни ИФ враћа 'Инцомплете', а унутрашњи ИФ никада не ради.

Ако је резултат није празно , спољни ИФ враћа ФАЛСЕ, а оригинална ИФ функција је покренута.

Научите угнежђене ИФ -ове са јасан, концизан видео тренинг .

4. Уметнути ИФ за ваге

Често ћете видети угнежђене ИФ -ове подешене за руковање „скалама“ ... на пример, за додељивање оцена, трошкова испоруке, пореских стопа или других вредности које варирају на скали са нумеричким уносом. Све док на скали нема превише нивоа, угнежђени ИФ -ови овде добро функционишу, али формулу морате држати организованом или ће је бити тешко читати.

Трик је у томе да одлучите о смеру (од високог до ниског или од ниског до високог), а затим према томе структурирајте услове. На пример, за додељивање оцена по редоследу „од ниског до високог“ можемо представити потребно решење у следећој табели. Имајте на уму да не постоји услов за 'А', јер када прођемо кроз све остале услове, знамо да резултат мора бити већи од 95, па самим тим и 'А'.

Сцоре Оцена Стање
0-63 Ф. <64
64-72 Д. <73
73-84 Ц. <85
85-94 Б <95
95-100 ДО

Са јасно схваћеним условима, можемо унети прву ИФ изјаву:

како се ради фреквенција на екцелу
 
= IF (C5<64,'F')

Ово води рачуна о 'Ф'. Сада, за руковање са 'Д', морамо додати још један услов:

 
= IF (C5<64,'F', IF (C5<73,'D'))

Имајте на уму да сам једноставно убацио још један ИФ у први ИФ ради 'лажног' резултата. Да бисмо формулу проширили на „Ц“, понављамо поступак:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C')))

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

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Ево коначне угнежђене ИФ формуле на делу:

Довршен угнежђени пример ИФ за израчунавање оцена

Видео: Како направити угнежђени ИФ за додељивање оцена

5. Уметнути ИФ -ови имају логичан ток

Многе формуле се решавају изнутра, јер се 'унутрашње' функције или изрази морају прво решити да би се наставила формула.

Угњежђени ИФ -ови имају свој логички ток, будући да се „спољни“ ИФ -и понашају као улаз у „унутрашње“ ИФ -ове. То значи да резултати спољних ИФ -ова одређују да ли унутрашњи ИФ -ови уопште раде. Доњи дијаграм визуализује логички ток горње формуле за оцену.

Логички ток угнежђеног ИФ -а

6. Помоћу Евалуате посматрајте логички ток

У оперативном систему Виндовс можете користити Оцените функцију да бисте гледали како Екцел решава ваше формуле, корак по корак. Ово је одличан начин да 'видите' логички ток сложенијих формула и решите проблеме када ствари не функционишу онако како очекујете. На доњем екрану је прозор Евалуате отворен и спреман за рад. Сваки пут када притиснете дугме Евалуате, „следећи корак“ у формули је решен. Евалуате можете пронаћи на картици Формуле на траци (Алт М, В).

Коришћењем Евалуате за пролазак кроз угнежђени ИФ који додељује оцене

Нажалост, Мац верзија програма Екцел не садржи функцију Евалуате, али и даље можете користити трик Ф9 испод.

7. Помоћу Ф9 уочите резултате провере

Када изаберете израз у траци са формулама и притиснете тастер Ф9, Екцел решава само изабрани део. Ово је моћан начин да потврдите шта формула заиста ради. На доњем екрану користим прозоре екрана за одабир различитих делова формуле, а затим кликом на Ф9 да видим да је тај део решен:

Помоћу Ф9 проверите угнежђени ИФ који додељује оцене

Користите Цонтрол + З (Цомманд + З) на Мацу да бисте поништили Ф9. Такође можете притиснути Есц да изађете из уређивача формула без икаквих промена.

Видео: Како отклонити грешке у формули помоћу Ф9

8. Упознајте своје границе

Екцел има ограничења у погледу дубине уметања ИФ функција. До Екцел 2007, Екцел је дозволио до 7 нивоа угнежђених ИФ -ова. У програму Екцел 2007+, Екцел дозвољава до 64 нивоа.

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

9. Упарите заграде као професионалац

Један од изазова са угнежђеним ИФ -овима је подударање или „уравнотежење“ заграда. Када се заграде не подударају правилно, ваша формула је покварена. Срећом, Е кцел нуди неколико алата који ће вам помоћи да се уверите да су заграде „уравнотежене“ током уређивања формула.

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

Заграде формуле су усклађене у боји, али их је тешко видети

Друго (и боље) када затворите заграде, Екцел ће накратко подебљати одговарајући пар. Такође можете кликнути на формулу и помоћу тастера са стрелицом кретати се кроз заграде, а Екцел ће накратко подебљати обе заграде када постоји одговарајући пар. Ако нема подударања, нећете видети подебљање.

Нажалост, подебљавање је функција само за Виндовс. Ако користите Екцел на Мац -у за уређивање сложених формула, понекад има смисла копирати и залепити формулу у добар уређивач текста ( Тект Вранглер је бесплатан и одличан) да бисте добили боље алате који одговарају заградама. Тект Вранглер ће трептати када се заграде подударају, а можете користити Цомманд + Б да изаберете сав текст који се налази у заградама. Можете да залепите формулу назад у Екцел након што исправите ствари.

10. За навигацију и избор користите прозор са саветима на екрану

Када је у питању навигација и уређивање угнежђених ИФ -ова, савет са екрана функција је ваш најбољи пријатељ. Помоћу њега можете да се крећете и прецизно бирате све аргументе у угнежђеном ИФ:

Крећите се и изаберите аргументе формуле врхом екрана

Можете ме видети како често користим прозор са екраном у овом видеу: Како изградити угнежђени ИФ .

11. Пазите на текст и бројеве

Само као брзи подсетник, када радите са ИФ функцијом, водите рачуна да правилно подударате бројеве и текст. Често видим формуле АКО овако:

 
= IF (A1='100','Pass','Fail')

Да ли је резултат теста у А1 заиста текст а не број? Не? Онда немојте користити наводнике око броја. У супротном, логички тест ће вратити ФАЛСЕ чак и када је вредност пролазна оцена, јер '100' није исто што и 100. Ако је резултат теста нумерички, користите ово:

 
= IF (A1=100,'Pass','Fail')

12. Додавање прелома редова олакшава читање угнежђених ИФ -ова

Када радите са формулом која садржи много нивоа угнежђених ИФ -ова, може бити тешко да ствари буду јасне. Пошто Екцел не мари за „размак“ у формулама (тј. Додатни размаци или преломи редова), можете знатно побољшати читљивост угнежђених иф ако додате преломе редова.

На пример, доњи екран приказује угнежђени ИФ који израчунава провизију на основу продајног броја. Овде можете видети типичну угнежђену ИФ структуру, коју је тешко дешифровати:

Уметнути ИФ -ови без прелома редова тешко се читају

Међутим, ако додам преломе редова пре сваке „вредности ако је нетачно“, логика формуле јасно искаче. Осим тога, формулу је лакше уређивати:

Преломи редова олакшавају читање угнежђених ИФ -ова

Можете додати преломе редова у Виндовс -у помоћу Алт + Ентер, на Мац -у користите Цонтрол + Оптион + Ретурн.

Видео: Како олакшати читање угнежђеног ИФ -а .

13. Ограничите ИФ -ове са АНД и ОР

Уметнути ИФ -ови су моћни, али се брзо компликују како додате више нивоа. Један од начина да се избегне више нивоа је коришћење ИФ у комбинацији са функцијама АНД и ОР. Ове функције враћају једноставан ТРУЕ/ФАЛСЕ резултат који савршено ради унутар ИФ -а, па их можете користити за проширење логике једног ИФ -а.

На пример, у доњем проблему желимо да ставимо „к“ у колону Д да бисмо означили редове у којима је боја „црвена“, а величина „мала“.

ИФ са функцијом АНД једноставнији је од два угнежђена ИФ -а

Формулу бисмо могли написати са два угнежђена ИФ -а овако:

 
= IF (B6='red', IF (C6='small','x',''),'')

Међутим, заменом теста функцијом АНД можемо поједноставити формулу:

 
= IF ( AND (B6='red',C6='small'),'x','')

На исти начин, лако можемо проширити ову формулу функцијом ОР да проверимо да ли је црвена ИЛИ плава И мала:

 
= IF ( AND ( OR (B4='red',B4='blue'),C4='small'),'x','')

Све ово могао да се уради са угнежђеним ИФ -овима, али би формула брзо постала сложенија.

Видео: АКО ово ИЛИ оно

14. Замените угнежђене ИФ -ове са ВЛООКУП

Када угнежђени ИФ једноставно додељује вредности на основу једног уноса, може се лако заменити са ВЛООКУП функција . На пример, овај угнежђени ИФ додељује бројеве пет различитих боја:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Лако га можемо заменити овим (много једноставнијим) ВЛООКУП -ом:

 
= VLOOKUP (E3,B3:C7,2,0)

Уметнути ИФ вс ВЛООКУП

Као бонус, ВЛООКУП чува вредности на радном листу (где се могу лако променити) уместо да их угради у формулу.

Иако горња формула користи потпуно подударање, можете је лако користити ВЛООКУП за оцене такође.

Такође видети: 23 ствари које треба знати о ВЛООКУП -у

Видео: Како се користи ВЛООКУП

Видео: Зашто је ВЛООКУП бољи од угнежђених ИФ -ова

15. Одаберите ИЗАБЕРИ

Функција ЦХООСЕ може понудити елегантно рјешење када требате пресликати једноставне, узастопне бројеве (1,2,3, итд.) У произвољне вриједности.

У доњем примеру, ЦХООСЕ се користи за креирање прилагођених скраћеница за радни дан:

Уметнути ИФ вс функција ИЗАБЕРИ

Наравно, ти могао користите дугу и компликовану угнежђену ИФ да бисте урадили исту ствар, али немојте :)

16. Користите ИФС уместо угнежђених ИФ

Ако користите Екцел 2016 путем система Оффице 365, постоји нова функција коју можете користити уместо угнежђених ИФ -ова: функција ИФС. ИФС функција пружа посебну структуру за процену више услова без гнежђење:

ИФС функција - више услова без гнежђења

Горе наведена формула изгледа овако:

 
= IFS (D5<60,'F',D5<70,'D',D5<80,'C',D5<90,'B',D5>=90,'A')

Имајте на уму да имамо само један пар заграда!

Шта се дешава када отворите табелу која користи функцију ИФС у старијој верзији програма Екцел? У Екцел 2013 и 2010 (и верујем у Екцел 2007, али не могу да тестирам) видећете '_клфн.' додато ИФС -у у ћелији. Претходно израчуната вредност ће и даље бити присутна, али ако нешто изазове поновно израчунавање формуле, видећете грешку #НАМЕ. Мицрософт има више информација овде .

17. Мак оут

Понекад можете користити МАКС или МИН на врло паметан начин који избегава ИФ израз. На пример, претпоставимо да имате прорачун који треба да резултира позитивним бројем или нулом. Другим речима, ако прорачун врати негативан број, само желите да прикажете нулу.

МАКС функција вам даје паметан начин да то учините без ИФ -а на видику:

 
= MAX (calculation,0)

Ова техника враћа резултат израчунавања ако је позитиван, а нула у супротном.

Волим ову конструкцију јер је тако једноставно . За потпуни опис погледајте овај чланак .

18. Грешке у замци са ИФЕРРОР -ом

Класична употреба ИФ -а је хватање грешака и пружање другог резултата када дође до грешке, овако:

 
= IF ( ISERROR (formula),error_result,formula)

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

У програму Екцел 2007 представљена је функција ИФЕРРОР која вам омогућава елегантније хватање грешака:

 
= IFERROR (formula,error_result)

Сада када формула направи грешку, ИФЕРРОР једноставно враћа вредност коју сте навели.

19. Користите логичку логику

Такође понекад можете избећи угнежђене ИФ -ове користећи оно што се назива „логичка логика“. Реч боолеан се односи на ТРУЕ/ФАЛСЕ вредности. Иако Екцел приказује речи ТРУЕ и ФАЛСЕ у ћелијама, Екцел интерно третира ТРУЕ као 1, а ФАЛСЕ као нулу. Ову чињеницу можете користити за писање паметних и врло брзих формула. На пример, у горњем примеру ВЛООКУП -а, имамо угнежђену ИФ формулу која изгледа овако:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Користећи логичку логику, формулу можете преписати овако:

 
=(E3='red')*100+(E3='blue')*200+(E3='green')*300+(E3='orange')*400+(E3='purple')*500

Сваки израз изводи тест, а затим множи исход теста са „вредност ако је тачно“. Пошто тестови враћају ТРУЕ или ФАЛСЕ (1 или 0), резултати ФАЛСЕ се ефикасно поништавају из формуле.

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

Видео: Како користити логичку логику у Екцел формулама

Када вам је потребан угнежђени ИФ?

Уз све ове опције за избегавање угнежђених ИФ -ова, можда ћете се запитати када има смисла користити угнежђени ИФ?

Мислим да угнежђени ИФ -ови имају смисла када треба да процените неколико различитих улаза донети одлуку.

На пример, претпоставимо да желите да израчунате статус фактуре „Плаћено“, „Отворено“, „Доспело“ итд. Ово захтева да погледате старост фактуре и изузетан баланс:

Израчунавање статуса фактуре са угнежђеним ИФ -ом

У овом случају, угнежђени ИФ је савршено решење.

Твоје мисли?

Ста је са тобом? Да ли сте ИФ-стер? Избегавате ли угнежђене ИФ -ове? Да ли су угнежђени ИФ зли? Поделите своја размишљања испод.

Брзо научите Екцел формуле помоћу сажети видео тренинг . Аутор Даве Брунс


^