Как да използваме логически функции в Excel IF, AND, OR, XOR, NOT

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

Как да използвате IF функцията

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

Нека да разгледаме структурата на функцията IF и след това да видим някои примери за нейното използване.

Функцията IF приема 3 бита информация:

= IF (logical_test, [value_if_true], [value_if_false])

• логическа проба: Това е условието функцията да проверява.
• стойност при вярно: Действието, което трябва да се изпълни, ако условието е изпълнено или е вярно.
• стойност при невярно: Действието, което трябва да се изпълни, ако условието не е изпълнено, или е невярно.

Оператори за сравнение за използване с логически функции

Когато извършвате логически тест със стойности на клетките, трябва да сте запознати с операторите за сравнение. Можете да видите разбивка на тези в таблицата по-долу.

Сега нека разгледаме някои примери за това в действие.

АКО Функция Пример 1: Стойности на текста

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

Следната формула се използва в колона С за показване на „Не“, ако колона Б съдържа текста „Завършен“ и „Да“, ако съдържа нещо друго.

= IF (B2 = "до ключ", "Не", "Да")

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

IF Функция Пример 2: Числени стойности

Функцията IF също е чудесна за сравняване на числови стойности.

Във формулата по-долу ще тестваме дали клетка В2 съдържа число, по-голямо или равно на 75. Ако го направи, ще покажем думата "Pass" и ако не е думата "Fail".

= IF (В2> = 75, "проход", "Fail")

Функцията IF е много повече от просто показване на различен текст върху резултата от теста. Можем също да го използваме, за да изпълним различни изчисления.

В този пример искаме да дадем 10% отстъпка, ако клиентът харчи определена сума пари. Като пример ще използваме £ 3,000.

= IF (В2> = 3000, В2 * 90%, В2)

Част от формулата B2 * 90% е начин, по който можете да извадите 10% от стойността в клетка B2. Има много начини да направите това.

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

IF Функция Пример 3: Дата стойности

В този трети пример използваме функцията IF за проследяване на списък с датите на падежа. Искаме да покажем думата „Просрочена“, ако датата в колона Б е в миналото. Но ако датата е в бъдеще, изчислете броя на дните до датата на падежа.

Формулата по-долу се използва в графа C. Проверяваме дали датата в клетка B2 е по-малка от днешната дата (функцията TODAY връща днешната дата от часовника на компютъра).

= IF (В2 
 
Какви са вложените IF Формули?
 
Може би преди сте чували за термина вложени IFs. Това означава, че можем да напишем IF функция в друга IF функция. Може да искаме да направим това, ако имаме повече от две действия за изпълнение.
 
Една IF функция е в състояние да изпълнява две действия ( стойност при вярно и стойност при невярно ). Но ако ние вграждаме (или гнездим) друга функция IF в стойност при невярно раздел, след което можем да извършим друго действие.
 
Вземете този пример, където искаме да покажем думата „Отлично“, ако стойността в клетка В2 е по-голяма или равна на 90, покажете „Добро“, ако стойността е по-голяма или равна на 75, и покажете „Лошо“, ако нещо друго.
 
= IF (B2> = 90, "Отличен", IF (B2> = 75, "Добър", "Лош"))
 
 
Сега сме разширили нашата формула отвъд това, което само една функция може да направи. А ако е необходимо, можете да поставите повече функции на IF.
 
Обърнете внимание на двете затварящи скоби в края на формулата-една за всяка функция IF.
 
Има алтернативни формули, които могат да бъдат по-чисти от този вложен IF подход. Една много полезна алтернатива е функцията SWITCH в Excel.
 
Логическите функции И и ИЛИ
 
Функциите AND и OR се използват, когато искате да извършите повече от едно сравнение във вашата формула. Функцията IF само може да се справи само с едно условие или сравнение.
 
Вземете пример, при който отстъпка на стойност с 10% зависи от сумата, която клиентът харчи и колко години са били клиент.
 
От своя страна, функциите AND и OR ще върнат стойността на TRUE или FALSE.
 
Функцията AND връща TRUE само ако всяко условие е изпълнено и в противен случай връща FALSE. Функцията OR връща TRUE, ако са изпълнени едно или всички условия, и връща FALSE само ако не са изпълнени условия.
 
Тези функции могат да тестват до 255 условия, така че със сигурност не са ограничени само до две условия, както е показано тук.
 
По-долу е представена структурата на функциите AND и OR. Те са написани едни и същи. Просто заменете името И за ИЛИ. Само тяхната логика е различна.
 
= AND (logical1, [logical2]…)
 
Нека видим пример и за двете, които оценяват две условия.
 
Пример и функция
 
Функцията AND се използва по-долу, за да провери дали клиентът харчи поне £ 3,000 и е бил клиент в продължение на най-малко три години.
 
= И (В2> = 3000, С2> = 3)
 
 
Можете да видите, че той връща FALSE за Matt и Terry, защото въпреки че и двете отговарят на един от критериите, те трябва да се срещнат и с функцията AND.
 
ИЛИ Функция Пример
 
Функцията OR се използва по-долу, за да провери дали клиентът харчи поне £ 3,000 или е бил клиент в продължение на поне три години.
 
= OR (В2> = 3000, С2> = 3)
 
 
В този пример формулата връща TRUE за Matt и Terry. Само Джули и Джилиън се провалят в двете условия и връщат стойността на FALSE.
 
Използване на AND и OR с функцията IF
 
Тъй като функциите AND и OR връщат стойността на TRUE или FALSE, когато се използват самостоятелно, рядко се използват сами.
 
Вместо това, обикновено ще ги използвате с функцията IF или в рамките на функция на Excel, като например условно форматиране или валидиране на данни, за да извършите някои ретроспективни действия, ако формулата се изчислява на TRUE.
 
Във формулата по-долу функцията AND е вложена в логическия тест на функцията IF. Ако функцията AND върне TRUE, тогава 10% се дисконтира от сумата в колона B; в противен случай не се дава отстъпка и стойността в колона Б се повтаря в колона D.
 
= IF (И (В2> = 3000, С2> = 3), В2 * 90%, В2)
 
 
Функцията XOR
 
В допълнение към функцията OR, има и изключваща функция. Това се нарича XOR функция. Функцията XOR бе въведена с версията на Excel 2013.
 
Тази функция може да отнеме известно усилие, за да се разбере, така че е показан практически пример.
 
Структурата на функцията XOR е същата като функцията OR.
 
= XOR (логическо1, [логическо2]…)
 
Когато се оценяват само две условия, функцията XOR връща:
 
 
TRUE ако някое от условията оцени на TRUE.
 
FALSE, ако и двете условия са TRUE, или нито едно от условията не е TRUE.
 
 
Това се различава от функцията OR, защото това би върнало TRUE, ако и двете условия бяха TRUE.
 
Тази функция става малко по-объркваща, когато се добавят повече условия. След това функцията XOR връща:
 
 
TRUE ако е нечетен брой условия връща TRUE.
 
FALSE, ако дори брой условия водят до TRUE или ако всичко условията са FALSE.
 
 
Нека да разгледаме прост пример за функцията XOR.
 
В този пример продажбите се разделят на две половини на годината. Ако продавачът продава £ 3000 или повече в двете половини, тогава им се дава златен стандарт. Това се постига с функция AND с IF като по-рано в статията.
 
Но ако продадат по 3 000 паунда или повече в половината, тогава ние искаме да им присвоим статут на сребро. Ако те не продават по 3 000 паунда или повече, тогава няма нищо.
 
Функцията XOR е идеална за тази логика. Формулата по-долу се въвежда в колона E и показва функцията XOR с IF, за да се покаже „Да“ или „Не“ само ако някое от условията е изпълнено.
 
= IF (XOR (B2> = 3000, C2> = 3000), "Да", "Не")
 
 
Функцията NOT
 
Крайната логическа функция, която трябва да се обсъди в тази статия, е функцията НЕ, и ние оставихме най-простите за последно. Въпреки че понякога може да е трудно да се видят начините на използване на функцията в реалния свят.
 
Функцията NOT обръща стойността на аргумента си. Така че, ако логическата стойност е TRUE, тя връща FALSE. И ако логическата стойност е FALSE, тя ще върне TRUE.
 
Това ще бъде по-лесно да се обясни с някои примери.
 
Структурата на функцията НЕ е;
 
= Не (логично)
 
НЕ Функционален пример 1
 
В този пример си представете, че имаме централен офис в Лондон и след това много други регионални сайтове. Искаме да покажем думата „Да“, ако сайтът е нещо различно от Лондон и „Не“, ако е Лондон.
 
Функцията NOT е вложена в логическия тест на функцията IF по-долу, за да се обърне TRUE резултатът.
 
= IF (НЕ (B2 = "Плевен"), "Да", "Не")
 
 
Това може да се постигне и чрез използване на логическия оператор НЕ на. По-долу е даден пример.
 
= IF (B2 "Лондон", "Да", "Не")
 
НЕ Функционален пример 2
 
Функцията NOT е полезна при работа с информационни функции в Excel. Това са група функции в Excel, които проверяват нещо и връщат TRUE, ако проверката е успешна, и FALSE, ако не е.
 
Например, функцията ISTEXT ще провери дали клетката съдържа текст и ще върне TRUE, ако го направи, и FALSE, ако не го направи. Функцията NOT е полезна, защото може да обърне резултата от тези функции.
 
В примера по-долу искаме да платим на продавача 5% от сумата, която продават. Но ако не раздуха нищо, думата „нищо“ е в клетката и това ще доведе до грешка във формулата.
 
Функцията ISTEXT се използва за проверка на наличието на текст. Това връща TRUE, ако има текст, така че функцията NOT го връща на FALSE. И IF изпълнява изчисленията си.
 
= IF (НЕ (ISTEXT (В2)), В2 * 5%, 0)
 
 
Овладяването на логически функции ще ви даде голямо предимство като потребител на Excel. Много е полезно да можете да тествате и сравнявате стойности в клетките и да извършвате различни действия въз основа на тези резултати.
 
 
Тази статия обхваща най-добрите логически функции, използвани днес. Последните версии на Excel видяха въвеждането на повече функции, добавени към тази библиотека, като например функцията XOR, спомената в тази статия. Следенето на тези нови допълнения ще ви предпази от тълпата.