Как да използвате множество оператори IF в Microsoft Excel

Инструкция IF в Excel изпълнява логически тест, който връща една стойност, ако условие е изпълнено, и друга стойност, ако не е. Използването на един оператор IF на Excel може да побере само основни операции, но ако вложите няколко оператора IF, можете да изпълнявате сложни задачи. Единственият недостатък е, че трябва да внимавате при конструирането на формулата, за да избегнете грешки. Алтернативните функции на Excel могат да вършат същата работа с по-малък риск от грешка. Научете основите на вложените IF изрази в Excel и алтернативите, за да намерите най-добрия подход за вашите нужди.

Функцията IF в Excel по същество казва "ако това е вярно, върнете x стойност, но ако не е, върнете y стойност." Например, ако проверявате дали различни отдели във вашия бизнес са били под или над бюджета, можете да сравните техните разходи с техните бюджети с помощта на функцията и да я върнете „Над бюджета“, ако отделът е изразходвал твърде много или „В рамките на бюджета“, ако е не направи. Най-общо казано, вие използвате оператори за сравнение, за да оформите изявлението си, като например:

• = (равно на)

• (по-голям от)

• = (по-голямо или равно на)

• < (по-малък от)
• <= (по-малко или равно на)
• <> (не е равно на)

Разбирането на синтаксиса на оператора IF на Excel е решаваща стъпка към научаването да го използвате правилно. Форматът на функцията е IF(логически_тест, стойност_ако_истина, [стойност_ако_невярно]) където квадратните скоби показват, че последният аргумент е незадължителен. Те са лесни за тълкуване: The логически_тест означава твърдението, което искате да тествате, стойност_ако_истина е мястото за това, което искате Excel да покаже дали тестът е верен и [value_if_false] е незадължителното място за това, което искате Excel да покаже, ако тестът е фалшив.

Това е достатъчно, за да използвате формулата на Excel за изрази if-then, които могат да бъдат демонстрирани с помощта на примера „Над бюджета/в рамките на бюджета“ от по-рано. Представете си, че имате сумата, изразходвана от различни отдели в колона B, и сумата, предвидена в колона C, като стойностите започват от втория ред. В клетка D2 ще въведете =IF(B2>C2, "Над бюджета", "В рамките на бюджета") за да върнете резултата, който търсите (отбелязвайки, че трябва да използвате кавички, за да го накарате да върне текста вътре). Първата част казва "ако стойността в B2 е по-голяма от стойността в C2" (ако разходите са по-големи от бюджета), втората част казва "след това върнете текста Надвишен бюджет и третата част казва „ако не, тогава се върнете В рамките на бюджета."

Оползотворяването на максимума от инструмента на Excel IF означава да можете да комбинирате множество оператори IF, когато имате задача, която е подходяща за това. Можете да включите втори оператор IF в оригиналния си оператор IF и можете да включите друг оператор IF в него и така нататък. Това е особено полезно, ако искате да категоризирате нещо в една от няколко групи с помощта на една формула. Например, можете да използвате вложени IF изрази, за да преобразувате резултатите, постигнати от учениците на тест, в оценка.

За да използвате правилно няколко оператора IF, помислете за логиката на това, което искате да направи Excel. По същество, след вашето условие (първият аргумент във функцията), можете да добавите друг оператор IF в стойност_ако_истина или стойност_ако_грешно интервали, за да кажете на Excel какво да прави по-нататък. Мислете за това като накарайте Excel да провери клетката спрямо втори критерий, ако въпросната клетка не го прави отговаряте на първия критерий (или наистина, ако отговаря на първия) и можете да продължите този процес за дълго време. С други думи, вие превръщате „ако това, тогава онова“ в „ако това и това, тогава онова" или "ако не това, но това, тогава онова."

Представете си, че сте дали на учениците тест, при който резултатът от 85 процента или по-висок е А, между 70 и 85 процента е B, между 55 и 70 процента е C, между 40 процента и 55 процента е D и всичко по-ниско е Неуспех. Резултатите са в колона B, от ред 2 до ред 11. Можете да използвате внимателно изградена функция на Excel IF с множество условия, за да категоризирате резултатите на учениците.

Първата част е лесна (за резултат в B2, в този пример): =IF(B2>=85, "A") казва на Excel да върне A, ако ученикът е отбелязал 85 или повече. Влагането ви позволява да добавяте други оценки, като използвате третия аргумент (какво да направите, ако условието е невярно), за да добавите допълнителни условия: =IF(B2>=85, "A", IF(B2>=70, "B")), което казва на Excel да върне A за резултати от 85 или по-високи, но ако не, върне B, ако резултатът е над 70. Продължете по този начин, за да завършите формулата, като всеки път използвате третия аргумент за нов оператор IF: =IF(B2>=85, "A", IF(B2>=70, "B",IF(B2>=55, "C", IF(B2>=40, "D", "Fail")) ))

Това изглежда сложно, така че разбиването му трябва да затвърди идеята. Първите два аргумента казват „ако резултатът в B2 е 85 или повече, тогава ученикът получава A“, а третият казва, „в противен случай проверете следното условие“. В вторият IF води до това твърдение, "(за ученици, отбелязали по-малко от 85), ако резултатът в B2 е 70 или по-висок, тогава ученикът получава B и ако не проверете следното условие." Третият IF казва: "(за ученици, отбелязали по-малко от 70) ако резултатът в B2 е 55 или по-висок, ученикът, отбелязал резултат, получава C, а ако не, проверете следното условие" и четвъртото казва "(за ученици с по-малко от 55) ако резултатът в B2 е по-висок от 40, ученикът получава D, ако не, ученикът се проваля."

При условие, че въведете правилно формулата за вашия оператор IF, той ще върне една от опциите, които сте дали на Excel. Въпреки това, ако направите грешка, формулата може да върне a 0 или #ИМЕ? в клетката, съдържаща вашата формула.

А 0 грешка означава, че или стойност_ако_истина или стойност_ако_грешно аргументът е празен. Отстранете този проблем, като се уверите, че стойността е въведена и в двата аргумента, или поне в стойност_ако_истина аргумент. В стойност_ако_грешно аргументът може да бъде напълно премахнат и той ще върне FALSE, ако не поставите втора запетая след стойност_ако_истина поле.

А #ИМЕ? грешка ви казва, че вероятно сте написали грешно или неправилно нещо във формулата. Това може да е малко по-трудно за поправяне, защото може да е всяка част от формулата, но най-добрият съвет е да проверите отново всичко или да напишете формулата отново.

Формулата за вложена функция IF в предишния пример стана доста дълга, което е един от основните недостатъци на използването на множество изрази IF в Excel. Те могат да станат сложни и е лесно да се направи грешка, защото трябва внимателно да следвате логиката, за да сте сигурни, че всичко работи по начина, по който възнамерявате. Microsoft посочва, че неправилно вложена формула може дори да върне правилния резултат през повечето време, но неправилни резултати в редки случаи. Тези са трудни за забелязване.

Неподреждането на условията в правилния ред е често срещана грешка. В примера за класирани тестове, ако сте започнали с по-нисък резултат =АКО(B2>=40, "D", ..." и продължи да изброява останалите във възходящ ред, дори резултат от 100 би върнал D. Това е така, защото първото условие се проверява първо, така че ако сте започнали с „ако клетка B2 е 40 или по-висока, върнете D" ще класира всеки резултат от 40 или повече като D, без да проверява нищо друго, защото отговаря на първия състояние. Това е перфектен пример защо трябва да следвате логиката на вашата формула.

Трябва също така да се уверите, че сте добавили правилния брой скоби в края на множество оператори IF, за да затворите формулата правилно. Excel помага за това, като координира цветовете и подчертава скобите, докато работите.

Технически можете да вложите до 64 IF функции в една формула, но Microsoft не препоръчва използването на функцията по този начин поради причините по-горе. Нещата бързо се усложняват и има вероятност, ако се опитвате да вложите повече от 10 оператора IF, вероятно има по-добър инструмент за работата, който вече е включен в Excel.

Най-удобната алтернатива на функцията IF е функцията IFS, която е версия на функция, която изпълнява същата задача като влагането в една функция и може да обработва до 127 условия. Функцията IFS има по-опростен синтаксис: =IFS(логически_тест1, стойност_ако_истина1, [логически_тест2, стойност_ако_истина2], [логически_тест3, стойност_ако_истина3]…) и така нататък. По същество това работи като вложени оператори IF, с изключение на това, че следващите IF оператори заемат пространството след "стойността, ако е истина", без да е необходимо да извеждат функцията отново.

Формулата в примера за тестова оценка може да бъде написана много по-просто с функцията IFS. Вместо:

=IF(B2>=85, "A", IF(B2>=70, "B",IF(B2>=55, "C", IF(B2>=40, "D", "Fail")) ))

просто пишете

=IFS(B2>=85, "A", B2>=70, "B", B2>=55, "C", B2>=40, "D", TRUE, "Fail")

В ВЯРНО на предпоследното място казва "ако никоя от другите стойности не е изпълнена, върнете следващата стойност."

Тази функция е налична само в Excel 2019 или Excel за Office 365.

Функцията VLOOKUP в Excel често е по-проста алтернатива на множество оператори IF и е налична във всяка версия на Excel от 2007 г. нататък. Не е идеален за всички ситуации, защото или връща резултати за точно съвпадение или за най-близкия номер, така че не може да се използва за оценки като предишния пример. Въпреки това, в много ситуации това може да ви спести много работа при изписването на формула, въпреки че трябва да изградите таблица с възможни стойности в една колона, като продукти, които вашият магазин продава, и какво искате функцията да върне, като цената на продукта, в друга колона, за пример.

Синтаксисът за VLOOKUP е =VLOOKUP(търсенна_стойност, таблица_масив, номер_индекс_столбец, [търсен_обхват]) и изучаването на всеки от компонентите ви показва как да го използвате. В търсеща_стойност Аргументът е стойността, която искате да проверите, която трябва да бъде клетка в същата колона като началото на таблицата, срещу която искате да я проверите, над или под нея. В таблица_масив е диапазонът от клетки, съдържащи вашата таблица с информация, като продукти и съответните им цени, и col_index_num е колоната, в която искате Excel да търси, за да намери стойността, която да върне 1 посочвайки най-лявата колона, 2 посочване на следващия и т.н. накрая, [range_lookup] е по избор, но въведете „TRUE“, ако искате Excel да базира отговора на най-близката стойност или „FALSE“, ако искате само точно съвпадение.