Функция IF в Excel. Примери (с множество условия)

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

Например, ако стойността съвпада с определена, тогава в клетката се показва един етикет. Ако не е, друго е. Нека разгледаме този ефективен инструмент по-подробно на практика.

IF функция в Excel (обща информация)

Всяка програма, дори и да е малка, задължително съдържа последователност от действия, която се нарича алгоритъм. Може да изглежда така:

1. Проверете цялата колона A за четни числа.
2. Ако се намери четно число, добавете такива и такива стойности.
3. Ако не бъде намерено четно число, покажете надписа „не е намерено“.
4. Проверете дали полученото число е четно. 
5. Ако да, добавете го към всички четни числа, избрани в параграф 1.

И дори ако това е само хипотетична ситуация, която е малко вероятно да е необходима в реалния живот, изпълнението на всяка задача задължително предполага наличието на подобен алгоритъм. Преди да използвате функцията АКО, трябва да имате ясна представа в главата си какъв резултат искате да постигнете. 

Синтаксис на функцията IF с едно условие

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

=АКО (логически_израз, стойност_ако_вярно, стойност_ако_лъжа)

Нека разгледаме синтаксиса по-подробно:

1. Булев израз. Това е самото условие, чието съответствие или несъответствие Excel проверява. Може да се проверява както цифрова, така и текстова информация.
2. Стойност_ако_истина. Резултатът, който ще се покаже в клетката, ако проверяваните данни отговарят на посочените критерии.
3. стойност_ако_лъжа. Резултатът, който се показва в клетката, ако проверяваните данни не отговарят на условието.

Ето един пример за яснота.

Тук функцията сравнява клетка A1 с числото 20. Това е първият параграф от синтаксиса. Ако съдържанието е по-голямо от тази стойност, в клетката, където е написана формулата, се показва стойността „по-голямо от 20“. Ако ситуацията не отговаря на това условие – „по-малко или равно на 20“.

Ако искате да покажете текстова стойност в клетка, трябва да я оградите в кавички.

Ето още една ситуация. За да се явят на изпитна сесия, студентите трябва да издържат изпитна сесия. Учениците успяха да спечелят кредити по всички предмети, а сега остава и последният, който се оказа решаващ. Нашата задача е да определим кои от учениците са допуснати до изпити и кои не.

Тъй като искаме да проверим за текст, а не за число, първият аргумент е B2=”против”.

Синтаксис на функцията IF с множество условия

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

За да стане по-ясно, ето синтаксиса.

=АКО(логически_израз, стойност_ако_вярно, АКО(логически_израз, стойност_ако_вярно, стойност_ако_невярно))

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

Ето един пример.

И с помощта на такава формула (показана на екранната снимка по-долу) можете да анализирате представянето на всеки ученик.

Както можете да видите, тук е добавено още едно условие, но принципът не се е променил. Така че можете да проверите няколко критерия наведнъж.

Как да разширите функционалността на IF с помощта на оператори И и ИЛИ

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

Функция IF с условие И

Понякога трябва да проверите израз за няколко условия наведнъж. За целта се използва функцията И, записана в първия аргумент на функцията IF. Работи по следния начин: ако a е равно на едно и a е равно на 2, стойността ще бъде c.

Функция IF с условие „ИЛИ“.

Функцията OR работи по подобен начин, но в този случай само едно от условията е вярно. Доколкото е възможно, до 30 условия могат да бъдат проверени по този начин. 

Ето няколко начина за използване на функции И и OR като аргумент на функцията IF.

Сравняване на данни в две таблици

От време на време е възможно да се сравняват две подобни таблици. Например, човек работи като счетоводител и трябва да сравни два отчета. Има и други подобни задачи, като например сравняване на себестойността на стоки от различни партиди, след това оценките на учениците за различни периоди и т.н.

За да сравните две таблици, използвайте функцията COUNTIF. Нека го разгледаме по-подробно.

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

Нашата маса изглежда така.

Избираме гамата, съответстваща на техническите характеристики на първия кухненски робот.

След това щракнете върху следните менюта: Условно форматиране – създаване на правило – използване на формула за определяне на форматираните клетки.

Под формата на формула за форматиране записваме функцията = COUNTIF (диапазон за сравнение; първа клетка от първата таблица)=0. Таблицата с характеристиките на втория кухненски робот се използва като диапазон за сравнение.

Трябва да се уверите, че адресите са абсолютни (със знак за долар пред имената на реда и колоната). Добавете =0 след формулата, така че Excel да търси точни стойности.

След това трябва да зададете форматирането на клетките. За да направите това, до пробата трябва да кликнете върху бутона „Форматиране“. В нашия случай използваме пълнежа, защото е най-удобен за тази цел. Но можете да изберете всяко форматиране, което искате.

Задали сме име на колона като диапазон. Това е много по-удобно от ръчното въвеждане на диапазона.

Функция SUMIF в Excel

Сега да преминем към функциите IF, което ще помогне да се заменят две точки от алгоритъма наведнъж. Първият е СЪМСЛИ, който събира две числа, отговарящи на определено условие. Например, ние сме изправени пред задачата да определим колко пари трябва да се плащат на месец на всички продавачи. За това е необходимо.

1. Добавете ред с общия доход на всички продавачи и щракнете върху клетката, която ще съдържа резултата след въвеждане на формулата. 
2. Намираме бутона fx, който се намира до реда за формули. След това ще се появи прозорец, в който можете да намерите необходимата функция чрез търсенето. След като изберете оператора, трябва да щракнете върху бутона „OK“. Но винаги е възможно ръчно въвеждане.
   

   

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

   

4. Първият аргумент е диапазон. Тук въвеждате клетките, които искате да проверите за съответствие с критериите. Ако говорим за нас, това са позициите на служителите. Въведете диапазона D4:D18. Или просто изберете клетките, които представляват интерес.
5. В полето "Критерии" въведете позицията. В нашия случай – „продавач“. Като диапазон на сумиране посочваме онези клетки, в които са посочени заплатите на служителите (това се прави както ръчно, така и се избират с мишката). Щракнете върху „OK“ и получаваме готовите изчислени заплати на всички служители, които са продавачи.

Съгласете се, че е много удобно. Не е ли?

Функция SUMIFS в Excel

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

Добавете ред, където ще бъде крайният резултат, и вмъкнете формулата в желаната клетка. За да направите това, щракнете върху иконата на функцията. Ще се появи прозорец, в който трябва да намерите функцията SUMMESLIMN. След това го изберете от списъка и се отваря познатият прозорец с аргументи. Но броят на тези аргументи вече е различен. Тази формула дава възможност за използване на безкраен брой критерии, но минималният брой аргументи е пет. 

Само пет могат да бъдат посочени чрез диалоговия прозорец за въвеждане на аргументи. Ако имате нужда от повече критерии, тогава те ще трябва да бъдат въведени ръчно по същата логика като първите два.

Нека разгледаме по-подробно основните аргументи:

1. Обхват на сумиране. Клетки за сумиране.
2. Диапазон на условие 1 – диапазонът, който ще се проверява за съответствие с определен критерий. 
3. Условие 1 е самото условие.
4. Диапазон на критерии 2 е вторият диапазон, който ще бъде проверен спрямо критерия.
5. Условие 2 е второто условие.

По-нататъшната логика е подобна. В резултат на това определихме заплатите на всички ръководители на Южния клон.

Функция COUNTIF в Excel

Ако трябва да определите колко клетки попадат под определен критерий, използвайте функцията COUNTIF. Да кажем, че трябва да разберем колко продавачи работят в тази организация:

1. Първо добавете ред, съдържащ броя на продавачите. След това трябва да кликнете върху клетката, където ще се покаже резултатът.
2. След това трябва да кликнете върху бутона „Вмъкване на функция“, който можете да намерите в раздела „Формули“. Ще се появи прозорец със списък с категории. Трябва да изберем елемента „Пълен азбучен списък“. В списъка се интересуваме от формулата COUNTIF. След като го изберем, трябва да щракнете върху бутона „OK“.
   

   

3. След това имаме броя на продавачите, наети в тази организация. Получава се чрез преброяване на броя на клетките, в които е написана думата „продавач“. Всичко е просто. 

Функция COUNTSLIM в Excel

Подобно на формулата SUMMESLIMN, тази формула отчита броя на клетките, които отговарят на множество условия. Синтаксисът е подобен, но малко по-различен от формулата SUMMESLIMN:

1. Диапазон на условие 1. Това е диапазонът, който ще бъде тестван спрямо първия критерий.
2. Условие 1. Директно първият критерий.
3. Диапазон на условие 2. Това е диапазонът, който ще бъде тестван спрямо втория критерий. 
4. Условие 2.
5. Условия на диапазона 3.

И така нататък.

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

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

Така че, ако се научите как да работите с големи количества данни в Excel, ще бъде много по-лесно да научите програмирането. Благодарение на логическите оператори, тези области наистина имат много общи неща, въпреки че Excel се използва по-често от счетоводителите. Но механизмът за работа с данни е до голяма степен същият. 

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