ИФ Формула Екцел - Изјаве Иф Тхен

Преузмите Пример радне свеске

Преузмите пример радне свеске

Овај водич показује како се користи Екцел ИФ функција у Екцелу за креирање наредби Иф Тхен.

Преглед функције ИФ

ИФ функција проверава да ли је услов испуњен. Ако ТРУЕ уради једно, ако ФАЛСЕ уради друго.

Да бисте користили функцију ИФ Екцел радног листа, изаберите ћелију и откуцајте:

(Обратите пажњу на то како се појављују уноси формуле)

ИФ функција Синтакса и улази:

1	= ИФ (логички_тест, вредност_ако_истина, вредност_ако_нетачно)

логички_тест - Логички израз. Пример: А1> 4.

валуе_иф_труе - Вредност или прорачун које треба извршити ако је логички израз ТРУЕ.

валуе_иф_фалсе - Вредност или прорачун за извођење ако је логички израз ФАЛСЕ.

ИФ је „условна“ функција. То значи да дефинишете логички тест и он ће вратити једну вредност ако се тај тест оцени као тачна, а другу вредност ако је нетачна

Како се користи ИФ функција

Ево врло основног примера да видите на шта мислим. Покушајте да у Екцел унесете следеће:

1	= ИФ (2 + 2 = 4, "Тачно је", "Није тачно!")

Пошто је 2 + 2 у ствари једнако 4, Екцел ће вратити „Истина је!“. Ако смо користили ово:

1	= ИФ (2 + 2 = 5, "Тачно је", "То је лажно!")

Сада ће Екцел вратити „То је лажно!“, Јер 2 + 2 није једнако 5.

Ево како можете да користите ИФ у табели.

1	= ИФ (Ц4-Д4> 0, Ц4-Д4,0)

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

Проверите да ли је Ц4-Д4 (њихов тренутни износ картице минус граница) већи од 0. Ово је ваш логички тест. Ако је ово тачно, ИФ враћа „Да“ - требало би да их одсечете. Ако је ово нетачно, ИФ врати „Не“ - дозволите им да наставе да пију.

Шта ИФ може да врати

Изнад смо вратили текстуални низ, „Да“ или „Не“. Али такође можете вратити бројеве, па чак и друге формуле.

Рецимо да неки од ваших клијената покрећу велике картице. Да бисте то обесхрабрили, почећете да наплаћујете камате клијентима који прекораче своје границе.

За то можете користити ИФ:

1	= ИФ (Ц4> Д4, Ц4*0,03,0)

Ако је картица већа од ограничења, вратите картицу помножену са 0,03, што враћа 3% картице. У супротном, вратите 0: нису преко своје картице, па нећете наплаћивати камате.

Коришћење ИФ са АНД

ИФ можете комбиновати са Екцеловом АНД функцијом <>. Ово користите у логичком тесту који вам омогућава да наведете два или више услова за тестирање. Екцел ће вратити ТРУЕ само ако су сви тестови тачни.

Дакле, применили сте своју каматну стопу. Али неки од ваших редовних људи се жале. Увек су плаћали своје картице у прошлости, зашто их сада сузбијате? Дошли сте до решења: одређеним клијентима од поверења нећете наплаћивати камате.

Направите нову колону у својој табели да бисте идентификовали поуздане клијенте и ажурирате своју ИФ изјаву помоћу функције АНД:

1	= ИФ (АНД (Ц4> Д4, Ф4 = "не"), Ц4*0,03,0)

Погледајмо део АНД одвојено:

1	АНД (Ц4> Д4, Ф4 = "Не")

Обратите пажњу на два услова:

• Ц4> Д4: провера да ли су прекорачили ограничење броја картица, као и раније
• Ф4 = „Не“: ово је нови бит, проверава се да ли су клијенти од поверења

Дакле, сада враћамо каматну стопу само ако је купац преко картице, а ми имамо „Не“ у колони поузданог клијента. Ваши редови су поново срећни.

Прочитајте више на главној страници за Екцел И функцију <>.

Коришћење ИФ са ОР

ИЛИ је још једна од Екцелових логичких функција. Као и АНД, омогућава вам да дефинишете више услова. Али за разлику од АНД, вратиће се ТРУЕ ако је било који од тестова које дефинишете тачан.

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

Додали сте нову колону да бисте идентификовали забрањене купце и ажурирали „Одсечено?“ колона са ОР тестом:

1	= ИФ (ОР (Ц4> Д4, Е4 = "Да"), "Да", "Не")

Гледајући само део ОР:

1	ИЛИ (Ц4> Д4, Е4 = "Да")

Постоје два услова:

• Ц4> Д4: провера да ли су прекорачили ограничење броја картица
• Ф4 = ”Да”: нови део, проверавајући да ли су тренутно забрањени

Ово ће се оценити као тачно ако су прешли картицу или ако у колони Е. постоји „Да“. Као што видите, Харри је сада одсечен, иако није прекорачио ограничење броја картица.

Прочитајте више на главној страници за функцију Екцел ИЛИ <>.

Коришћење ИФ са КСОР -ом

КСОР је још једна логичка функција која враћа “Екцлусиве Ор”. Ово је мало мање интуитивно од претходних о којима смо разговарали.

У једноставним случајевима дефинишете два услова и КСОР ће се вратити:

• ТРУЕ ако је било који аргумент тачан (исто као и уобичајено ИЛИ)
• ФАЛСЕ ако су оба аргумента тачна
• ФАЛСЕ ако су оба аргумента нетачна

Пример би ово могао учинити јаснијим. Замислите да желите да почнете да дајете месечне бонусе свом особљу:

• Ако продају више од 800 долара у храни или преко 800 долара у пићу, дат ћете им пола бонуса
• Ако у оба случаја продају више од 800 долара, даћете им пун бонус
• Ако у оба случаја продају испод 800 УСД, неће добити бонус.

Већ знате како да вежбате ако добију пун бонус. Само бисте користили ИФ са АНД, као што је раније описано.

1	= ИФ (АНД (Ц4> 800, Д4> 800), "да", "не")

Али како бисте схватили ко добија пола бонуса? Ту долази КСОР:

1	= ИФ (КСОР (Ц4> = 800, Д4> = 800), "Да", "Не")

Као што видите, Вудијево пиће продало је преко 800 долара, али не и продају хране. Тако добија полу бонус. За Тренера је обрнуто. Диане и Царла су продале више од 800 долара за обоје, тако да не добијају пола бонуса (оба аргумента су ИСТИНА), а Ребецца је направила испод прага за оба (оба аргумента су ЛАЖНА), па формула поново враћа „Не“.

Прочитајте више на главној страници функције Екцел КСОР <>.

Коришћење ИФ са НОТ -ом

НОТ је још једна од Екцелових логичких функција, која се врло често користи са ИФ.

НЕ поништава исход логичког теста. Другим речима, проверава да ли услов није испуњен.

Можете га користити са ИФ овако:

1	= ИФ (АНД (Ц3> = 1985, НОТ (Д3 = "Стевен Спиелберг")), "Ватцх", "Дон'т Ватцх")

Овде имамо табелу са подацима о неким филмовима из 1980 -их. Желимо да идентификујемо филмове објављене 1985. године или после њих, а које није режирао Стивен Спилберг.

Пошто се НОТ налази у оквиру функције АНД, Екцел ће то прво проценити. Затим ће користити резултат као део АНД.

Прочитајте више на главној страници програма Екцел НОТ Функција <>.

Уметнути ИФ искази

Такође можете вратити ИФ исказ унутар свог ИФ извода. Ово вам омогућава да направите сложеније прорачуне.

Вратимо се на сто наших купаца. Замислите да желите да класификујете клијенте на основу нивоа дуга према вама:

• 0 УСД: Нема
• До 500 УСД: Ниско
• 500 до 1000 долара: Средње
• Преко 1000 УСД: Високо

То можете учинити тако што ћете „угнездити“ ИФ наредбе:

1	= ИФ (Ц4 = 0, "Ништа", ИФ (Ц4 <= 500, "Ниско", ИФ (Ц4 <= 1000, "Средње", ИФ (Ц4> 1000, "Високо")))

Лакше је разумети ако ставке ИФ ставите у засебне редове (АЛТ + ЕНТЕР у оперативном систему Виндовс, ЦТРЛ + ЦОММАНД + ЕНТЕР на Мац рачунарима):

12345

=ИФ (Ц4 = 0, "Ништа",ИФ (Ц4 <= 500, "Ниско",ИФ (Ц4 <= 1000, "Средњи",ИФ (Ц4> 1000, "Високо", "Непознато"))))

АКО је Ц4 0, враћамо „Ништа“. У супротном, прелазимо на следећу ИФ наредбу. АКО је Ц4 једнак или мањи од 500, враћамо „Ниско“. У супротном, прелазимо на следећу ИФ изјаву … и тако даље.

Поједностављивање сложених ИФ изјава помоћу колона помоћника

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

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

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

Ви сте аналитичар у финансијском одељењу велике корпорације. Од вас је затражено да направите табелу која проверава да ли сваки запослени испуњава услове за пензију предузећа.

Ево критеријума:

Дакле, ако сте млађи од 55 година, морате имати 30 година стажа да бисте испунили услове. Ако имате 55 до 59 година, потребно вам је 15 година стажа. И тако даље, до 65 година, где имате право без обзира колико дуго тамо радили.

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

1	= ИФ (ОР (Ф4> = 65, АНД (Ф4> = 62, Г4> = 5), АНД (Ф4> = 60, Г4> = 10), АНД (Ф4> = 55, Г4> = 15), Г4 > 30), "Испуњава услове", "Не испуњава услове")

Вау! Тешко се снаћи око тога, зар не?

Бољи приступ би могао бити коришћење колона помоћника. Овде имамо пет логичких тестова, који одговарају сваком реду у табели критеријума. Ово је лакше видети ако формули додамо преломе редова, као што смо раније расправљали:

12345678

= ИФ (ИЛИ (Ф4> = 65,АНД (Ф4> = 62, Г4> = 5),АНД (Ф4> = 60, Г4> = 10),АНД (Ф4> = 55, Г4> = 15),Г4> 30), "Испуњава услове", "Не испуњава услове")

Дакле, можемо поделити ових пет тестова у засебне колоне, а затим једноставно проверити да ли је неко од њих тачан:

Свака колона у табели од Е до И садржи сваки наш критеријум посебно. Тада у Ј4 имамо следећу формулу:

1	= АКО (ЦОУНТИФ (Е4: И4, ТРУЕ), "Подобно", "Не испуњава услове")

Овде имамо ИФ наредбу, а логички тест користи ЦОУНТИФ <> за бројање броја ћелија унутар Е4: И4 које садрже ТРУЕ.

Ако ЦОУНТИФ не пронађе ТРУЕ вредност, вратиће 0, што ИФ тумачи као ФАЛСЕ, па ИФ враћа „Не испуњава услове“.

Ако ЦОУНТИФ пронађе било коју вредност ТРУЕ, вратиће њихов број. ИФ тумачи било који број осим 0 као ТРУЕ, па враћа „Прихватљиво“.

Раздвајање логичких тестова на овај начин чини формулу лакшом за читање, а ако нешто није у реду са њом, много је лакше уочити где је грешка.

Коришћење груписања за сакривање колона помоћника

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

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

Боља опција је груписање.

Изаберите колоне које желите да групишете, у нашем случају Е: И. Затим притисните АЛТ + СХИФТ + СТРЕЛИЦА ДЕСНО у Виндовс -у или ЦОММАНД + СХИФТ + К на Мац -у. Такође можете отићи на картицу „Подаци“ на врпци и изабрати „Група“ у одељку „Преглед“.

Видећете групу приказану изнад заглавља колона, овако:

Затим једноставно притисните дугме „-“ да бисте сакрили колоне:

ИФС функција

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

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

1234567

= ИФС (Ц4 = 0, "Ништа",Ц4 <= 500, "Ниско",Ц4 <= 1000, "средњи",Ц4> 1000, "Високо",ТРУЕ, „Непознато“,)

Све о томе можете прочитати на главној страници за Екцел ИФС функцију <>.

Коришћење ИФ са условним обликовањем

Екцел -ова функција условног обликовања омогућава вам да обликујете ћелију на различите начине у зависности од њеног садржаја. Будући да ИФ враћа различите вредности на основу нашег логичког теста, можда бисмо желели да користимо условно обликовање да бисмо ове различите вредности лакше видели.

Па да се вратимо на наш сто са бонусом од раније.

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

Ево како бисте то урадили:

• Одаберите распон ћелија који садржи ваше ИФ изразе. У нашем случају то је Е4: Ф8.
• Кликните на „Условно обликовање“ у одељку „Стилови“ на картици „Почетна“ на траци.
• Кликните на „Означи правила ћелија“, а затим на „Једнако“.
• Откуцајте „Да“ (или било коју повратну вредност која вам је потребна) у прво поље, а затим изаберите жељено обликовање из другог оквира. (За ово ћу изабрати зелену боју).
• Поновите за све повратне вредности (такође ћу поставити вредности „Не“ на црвено)

Ево резултата:

Коришћење ИФ у формулама низа

Низ је низ вредности, а у Екцелу низови су представљени као вредности раздвојене зарезима затворене у заградама, као што су:

1	{1,2,3,4,5}

Лепота низа је у томе што вам омогућавају да извршите прорачун за сваку вредност у опсегу, а затим вратите резултат. На пример, функција СУМПРОДУЦТ узима два низа, множи их заједно и сумира резултате.

Дакле, ова формула:

1	= СУМПРОДУЦТ ({1,2,3}, {4,5,6})

… Враћа 32. Зашто? Хајде да то решимо:

12345

1 * 4 = 42 * 5 = 103 * 6 = 184 + 10 + 18 = 32

Можемо унети ИФ израз у ову слику, тако да се свако од ових множења дешава само ако логички тест врати тачно.

На пример, узмите ове податке:

хттпс://ввв.аутоматеекцел.цом/екцел/вп-цонтент/уплоадс/2020/07/СУМПРОДУЦТ-Екампле-Ранге.пнг "не"> 1 = СУМПРОДУЦТ (ИФ ($ Ц $ 2: $ Ц $ 10 = $ Г2, $ Д $ 2: $ Д $ 10*$ Е $ 2: $ Е $ 10))

Напомена: У програму Екцел 2022 и старијим, морате притиснути ЦТРЛ + СХИФТ + ЕНТЕР да бисте ово претворили у формулу низа.

Завршили бисмо са нечим оваквим:

хттпс://ввв.аутоматеекцел.цом/екцел/вп-цонтент/уплоадс/2020/07/СУМПРОДУЦТС-ИФ-Ресултс-Табле.пнг "не"> 1 $ Ц $ 2: $ Ц $ 10 = $ Г2

На енглеском, ако је име у колони Ц једнако ономе што је у Г2 („Оливиа“), ДО помножите вредности у колонама Д и Е за тај ред. У супротном, немојте их множити. Затим збројите све резултате.

Можете сазнати више о овој формули на главној страници формуле СУМПРОДУЦТ ИФ <>.

АКО у Гоогле табелама

ИФ функција ради потпуно исто у Гоогле таблицама као и у Екцелу:

Додатне напомене

Користите функцију ИФ да бисте тестирали да ли је услов ТРУЕ. Ако је услов ИСТИНА, учините једну ствар. Ако је ТОЧНО, урадите друго. Услов мора бити логички израз (нпр: а1> 5), референца на ћелију која садржи ТРУЕ или ФАЛСЕ или низ који садржи све логичке вредности.

ИФ функција може тестирати само један услов одједном. Међутим, можете „угнездити“ друге логичке функције унутар услова ИФ да бисте тестирали више услова одједном:

= иф (АНД (а1> 0, а2> 0), ТРУЕ, ФАЛСЕ)
= иф (ОР (а1> 0, а2> 0), ТРУЕ, ФАЛСЕ)
= иф (КСОР (а1> 0, а2> 0), ТРУЕ, ФАЛСЕ)
ИЛИ Функције се тестирају ако један или више услови су испуњени.
АНД Функције тестирају ако све услови су испуњени.
Тест функција КСОР -а ако један и једини услов је испуњен.

Такође можете „угнездити“ ИФ функцију у ИФ функцију:

1	= иф (а1 <0, иф (а2 <0, "Оба", "само 1"), "само један")

Сада за неке конкретне примере како функција ИФ функционише у пракси:

1. Покрените нову радну књигу.

2. У ћелију А1 унесите вредност 10 (и притисните Ентер)

3. Затим у ћелију Б1 унесите следећу формулу:

1	= АКО (А1> 5, "ВЕЋЕ ОД 5", "МАЊЕ ОД 5")

4. Екран би сада требао изгледати овако:

5. Ако сте исправно унели формулу, видећете поруку „Веће од 5“ у ћелији Б1.

6. Формула коју сте унели у ћелију Б1 изводи тест „А1> 5“, односно проверава да ли је вредност у ћелији А1 већа од 5. Тренутно је вредност у ћелији А1 10 - дакле услов је ТРУЕ и порука Појављује се „ВЕЋЕ ОД 5“

7. Ако сада променимо вредност у ћелији А1 у 2:

Затим је порука у ћелији Б2 сада „МАЊА ОД 5“ јер је услов ЛАЖ.

8. Можете наставити да мењате вредност у ћелији А1 и порука у ћелији Б2 ће се према томе прилагодити.

9. Наравно, постоје ситуације у којима стање може дати лоше резултате:

• Шта се дешава ако унесемо вредност 5 у ћелију А1?

• Шта ако оставимо ћелију А1 празну?

• Шта ако ставимо неки текст у ћелију А1, нпр. Израз ДОГ

Више о Екцел ИФ функцији

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

Замислите да сте регионални менаџер продаје и да имате продајни тим. Можете да забележите укупну продају коју свака особа оствари у једноставној Екцел табели:

Претпоставимо да је критериј за бонус био да је продаја коју је остварила та особа премашила 40.000 фунти. Могли сте само да "погледате" податке и утврдите да су само Антон, Невтон и Моникуе испунили циљ.

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

Подесите нову радну свеску и упишите податке према горе наведеном. Затим у ћелију Д4 упишите следећу формулу:-

1	= ИФ (Ц4> 40000, "БОНУС ПАИАБЛЕ", "НО БОНУС")

тако да имате:

Обратите пажњу на то како Екцел приказује структуру формуле ИФ - што је корисна помоћна белешка.

Након што сте унели формулу, притисните ЕНТЕР и видећете да се процењује за први ред:

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

Затим повлачимо формуле надоле кликом на доњи десни угао и повлачењем надоле можемо утврдити да ли свака особа има право на бонус:

Видимо да је Екцел утврдио ко од продајних људи има право на бонус.

Повратак на листу свих функција у програму Екцел

ВБА ИФ Изјаве

Такође можете да користите Иф Статементс у ВБА. Кликните на везу да бисте сазнали више, али ево једноставног примера:

1234567

Суб Тест_ИФ ()Ако је опсег ("а1"). Вредност <0 тадаОпсег ("б1"). Вредност = "Негативно"Крај АкоКрај Ако

Овај код ће тестирати да ли је вредност ћелије негативна. Ако је тако, у следећој ћелији ће написати „негативно“.