IF формула Excel - Ако тогава изявления

Изтеглете Примерна работна книга

Изтеглете примерната работна книга

Този урок демонстрира как да използвате Excel IF функция в Excel за създаване на изявления If Then.

Преглед на функцията IF

Функцията IF проверява дали условието е изпълнено. Ако TRUE направи едно нещо, ако FALSE направи друго.

За да използвате функцията за работен лист IF Excel, изберете клетка и въведете:

(Забележете как се появяват входовете на формулата)

Функция IF Синтаксис и входове:

1	= IF (логически_тест, стойност_ако_истина, стойност_ако_фалшиво)

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

стойност_ако_истина - Стойност или изчисление за изпълнение, ако логическият израз е TRUE.

value_if_false - Стойност или изчисление за изпълнение, ако логическият израз е FALSE.

IF е „условна“ функция. Това означава, че дефинирате логически тест и той ще върне една стойност, ако този тест се оценява като вярно, и различна стойност, ако е невярна

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

Ето един много основен пример, за да можете да видите какво имам предвид. Опитайте да въведете следното в Excel:

1	= АКО (2 + 2 = 4, "Вярно е", "Не е вярно!")

Тъй като 2 + 2 всъщност е равно на 4, Excel ще върне „Вярно е!“. Ако използвахме това:

1	= АКО (2 + 2 = 5, "Вярно е", "Не е вярно!")

Сега Excel ще върне „Това е невярно!“, Защото 2 + 2 не е равно на 5.

Ето как можете да използвате IF в електронна таблица.

1	= IF (C4-D4> 0, C4-D4,0)

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

Проверявате дали C4-D4 (текущото им количество в раздела минус лимита им) е по-голямо от 0. Това е вашият логически тест. Ако това е вярно, IF връща „Да“ - трябва да ги отрежете. Ако това е невярно, IF връща „Не“ - оставяте ги да продължат да пият.

Какво IF може да върне

По -горе върнахме текстов низ „Да“ или „Не“. Но можете също да върнете числа или дори други формули.

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

Можете да използвате IF за това:

1	= IF (C4> D4, C4*0.03,0)

Ако разделът е по -висок от лимита, върнете раздела умножен по 0,03, което връща 3% от раздела. В противен случай върнете 0: те не са над раздела си, така че няма да начислявате лихви.

Използване на IF с AND

Можете да комбинирате IF с функцията AND на Excel <>. Използвате това в логическия тест, който ви позволява да посочите две или повече условия за тестване. Excel ще върне TRUE само ако ВСИЧКИ тестове са верни.

И така, вие въведохте своя лихвен процент. Но някои от вашите редовни се оплакват. Те винаги са плащали на своите раздели в миналото, защо ги преследвате сега? Вие предлагате решение: няма да начислявате лихва на определени доверени клиенти.

Вие правите нова колона в електронната си таблица, за да идентифицирате доверени клиенти и актуализирате изявлението си IF с функция AND:

1	= IF (AND (C4> D4, F4 = "Не"), C4*0.03,0)

Нека разгледаме отделно частта AND:

1	И (C4> D4, F4 = "Не")

Обърнете внимание на двете условия:

• C4> D4: проверка дали са надхвърлили ограничението си, както преди
• F4 = „Не“: това е новият бит, проверяващ дали не са доверен клиент

Така че сега връщаме лихвения процент само ако клиентът е над раздела си, И ние имаме „Не“ в колоната за доверен клиент. Редовните ви посетители отново са щастливи.

Прочетете повече на главната страница за функцията Excel AND <>.

Използване на IF с OR

ИЛИ е друга от логическите функции на Excel. Подобно на И, тя ви позволява да дефинирате повече от едно условие. Но за разлика от И, тя ще върне ИСТИНА, ако НЯКОЙ от зададените от вас тестове е истина.

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

Така че добавяте нова колона за идентифициране на забранени клиенти и актуализирате „Отрязано?“ колона с OR тест:

1	= АКО (ИЛИ (C4> D4, E4 = "Да"), "Да", "Не")

Гледайки само частта ИЛИ:

1	ИЛИ (C4> D4, E4 = "Да")

Има две условия:

• C4> D4: проверка дали са надхвърлили ограничението си за раздели
• F4 = ”Да”: новата част, проверяваща дали в момента са забранени

Това ще се оцени като вярно, ако те са над раздела си, или ако има „Да“ в колона Е. Както можете да видите, Хари е отрязан сега, въпреки че не е надхвърлил ограничението си за раздели.

Прочетете повече на главната страница за функцията Excel ИЛИ <>.

Използване на IF с XOR

XOR е друга логическа функция, която връща „Изключително или“. Това е малко по -малко интуитивно от предишните, които обсъждахме.

В прости случаи дефинирате две условия и XOR ще се върне:

• ИСТИНА, ако някой от аргументите е истина (същото като нормалното ИЛИ)
• FALSE, ако и двата аргумента са верни
• FALSE, ако и двата аргумента са невярни

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

• Ако продават над $ 800 в храни или над $ 800 в напитки, ще им дадете половин бонус
• Ако продадат над 800 долара и в двете, ще им дадете пълен бонус
• Ако продават под 800 долара и в двете, те не получават никакъв бонус.

Вече знаете как да работите, ако получат пълния бонус. Просто бихте използвали IF с AND, както е описано по -рано.

1	= IF (AND (C4> 800, D4> 800), "Да", "Не")

Но как бихте разбрали кой получава половин бонус? Тук идва XOR:

1	= IF (XOR (C4> = 800, D4> = 800), "Да", "Не")

Както можете да видите, продажбите на напитки на Уди бяха над 800 долара, но не и продажбите на храни. Така той получава половин бонус. Обратното е вярно за Coach. Даян и Карла продадоха повече от 800 долара и за двете, така че не получават половин бонус (и двата аргумента са ИСТИНСКИ), а Ребека направи под прага и за двата (и двата аргумента са НЕВЯРНИ), така че формулата отново връща „Не“.

Прочетете повече на главната страница за функцията Excel XOR <>.

Използване на IF с NOT

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

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

Можете да го използвате с IF по следния начин:

1	= IF (AND (C3> = 1985, NOT (D3 = "Steven Spielberg")), "Watch", "Don't Watch")

Тук имаме таблица с данни за някои филми от 80 -те години. Искаме да идентифицираме филми, пуснати на или след 1985 г., които не са режисирани от Стивън Спилбърг.

Тъй като NOT е вложен във функция AND, Excel първо ще оцени това. След това той ще използва резултата като част от AND.

Прочетете повече на главната страница на функцията Excel NOT <>.

Вложени IF изявления

Можете също да върнете IF изявление в рамките на вашия IF отчет. Това ви позволява да правите по -сложни изчисления.

Да се ​​върнем към масата на нашите клиенти. Представете си, че искате да класифицирате клиентите въз основа на нивото на дълга към вас:

• $ 0: Няма
• До $ 500: Ниско
• $ 500 до $ 1000: Средно
• Над $ 1000: Високо

Можете да направите това, като „вмъкнете“ IF изрази:

1	= IF (C4 = 0, "Няма", IF (C4 <= 500, "Ниско", IF (C4 <= 1000, "Средно", IF (C4> 1000, "Високо")))

По -лесно е да се разбере, ако поставите операторите IF в отделни редове (ALT + ENTER в Windows, CTRL + COMMAND + ENTER в Mac):

12345

=IF (C4 = 0, "Няма",IF (C4 <= 500, "Ниско",IF (C4 <= 1000, "Средно",IF (C4> 1000, "Високо", "Неизвестно"))))

АКО С4 е 0, връщаме „Няма“. В противен случай преминаваме към следващия оператор IF. АКО С4 е равно или по -малко от 500, връщаме „Ниско“. В противен случай преминаваме към следващото IF изявление … и така нататък.

Опростяване на сложни IF изявления с помощни колони

Ако имате множество вложени IF изрази и въвеждате логически функции, вашите формули могат да станат много трудни за четене, тестване и актуализиране.

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

Помощните колони са чудесен начин да заобиколите този проблем.

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

Ето критериите:

Така че, ако сте на възраст под 55 години, трябва да имате 30 години стаж под колана си, за да отговаряте на условията. Ако сте на възраст от 55 до 59 години, имате нужда от 15 години трудов стаж. И така нататък, до 65 -годишна възраст, където имате право, независимо колко дълго сте работили там.

Можете да използвате един, сложен IF израз за решаване на този проблем:

1	= IF (ИЛИ (F4> = 65, И (F4> = 62, G4> = 5), И (F4> = 60, G4> = 10), И (F4> = 55, G4> = 15), G4 > 30), "Допустимо", "Не отговаря на условията")

Уау! Трудно е да се замислиш, нали?

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

12345678

= АКО (ИЛИ(F4> = 65,И (F4> = 62, G4> = 5),И (F4> = 60, G4> = 10),И (F4> = 55, G4> = 15),G4> 30), "Допустимо", "Не отговаря на условията")

Така че, можем да разделим тези пет теста в отделни колони и след това просто да проверим дали някой от тях е истина:

Всяка колона в таблицата от E до I съдържа всеки наш критерий поотделно. Тогава в J4 имаме следната формула:

1	= АКО (COUNTIF (E4: I4, TRUE), "Допустимо", "Не отговаря на условията")

Тук имаме оператор IF и логическият тест използва COUNTIF <> за преброяване на броя клетки в E4: I4, които съдържат TRUE.

Ако COUNTIF не намери TRUE стойност, той ще върне 0, което IF се тълкува като FALSE, така че IF връща „Не отговаря на условията“.

Ако COUNTIF намери някакви TRUE стойности, той ще върне броя им. IF тълкува всяко число, различно от 0, като TRUE, така че връща „Допустимо“.

Разделянето на логическите тестове по този начин прави формулата по -лесна за четене и ако нещо се обърка с нея, е много по -лесно да забележите къде е грешката.

Използване на групиране за скриване на помощни колони

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

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

По -добър вариант е групирането.

Изберете колоните, които искате да групирате, в нашия случай E: I. След това натиснете ALT + SHIFT + СТРЕЛКА НАДЯСНО в Windows или COMMAND + SHIFT + K на Mac. Можете също да отидете в раздела „Данни“ на лентата и да изберете „Група“ от секцията „Контур“.

Ще видите групата, показана над заглавките на колоните, по следния начин:

След това просто натиснете бутона „-“, за да скриете колоните:

Функцията IFS

Вложените IF изрази са много полезни, когато трябва да извършите по -сложни логически сравнения и трябва да го направите в една клетка. Те обаче могат да се усложнят, тъй като стават по -дълги и могат да бъдат трудни за четене и актуализиране на екрана ви.

От Excel 2022 и Excel 365, Microsoft представи друга функция, IFS, за да улесни управлението. Горният пример за вложените IF може да бъде постигнат с IFS по следния начин:

1234567

= IFS (C4 = 0, "Няма",C4 <= 500, "Ниско",C4 <= 1000, "Средно",C4> 1000, "Висока",ИСТИНСКО, „Неизвестно“,)

Можете да прочетете всичко за това на главната страница за функцията Excel IFS <>.

Използване на IF с условно форматиране

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

Така че нека се върнем към нашата бонусна маса за персонала от по -рано.

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

Ето как бихте го направили:

• Изберете диапазона от клетки, съдържащ вашите IF изрази. В нашия случай това е E4: F8.
• Щракнете върху „Условно форматиране“ в секцията „Стилове“ на раздела „Начало“ на лентата.
• Кликнете върху „Правила за маркиране на клетки“ и след това върху „Равно на“.
• Въведете „Да“ (или каквато и да е връщаща стойност, от която се нуждаете) в първото поле и след това изберете форматирането, което искате от второто поле. (За това ще избера зелено).
• Повторете за всички връщани стойности (също така ще задам стойности „Не“ на червено)

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

Използване на IF във формули на масиви

Масивът е диапазон от стойности и в Excel масивите са представени като стойности, разделени със запетая, затворени в скоби, като например:

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

Красотата на масивите е, че те ви позволяват да извършите изчисление за всяка стойност в диапазона и след това да върнете резултата. Например функцията SUMPRODUCT взема два масива, умножава ги заедно и сумира резултатите.

Така че тази формула:

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

… връща 32. Защо? Нека го преодолеем:

12345

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

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

Вземете например тези данни:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCT-Example-Range.png "не"> 1 = СУМПРОДУКТ (АКО ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))

Забележка: В Excel 2022 и по -ранни версии трябва да натиснете CTRL + SHIFT + ENTER, за да превърнете това във формула за масив.

В крайна сметка бихме получили нещо подобно:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCTS-IF-Results-Table.png "не"> 1 $ C $ 2: $ C $ 10 = $ G2

На английски, ако името в колона C е равно на това в G2 („Olivia“), DO умножава стойностите в колони D и E за този ред. В противен случай не ги умножавайте. След това сумирайте всички резултати.

Можете да научите повече за тази формула на главната страница за формулата SUMPRODUCT IF <>.

АКО в Google Таблици

Функцията IF работи абсолютно същото в Google Sheets като в Excel:

допълнителни бележки

Използвайте функцията IF, за да проверите дали дадено условие е TRUE. Ако условието е ИСТИННО, направете едно нещо. Ако е НЕВЯРНО, направете друго. Условието трябва да бъде логически израз (напр. A1> 5), препратка към клетка, съдържаща TRUE или FALSE, или масив, съдържащ всички логически стойности.

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

= if (AND (a1> 0, a2> 0), TRUE, FALSE)
= if (OR (a1> 0, a2> 0), TRUE, FALSE)
= ако (XOR (a1> 0, a2> 0), TRUE, FALSE)
ИЛИ Функционален тест, ако едно или повече условията са изпълнени.
И Функции тест, ако всичко условията са изпълнени.
Тествайте функциите XOR, ако един и единствен условието е изпълнено.

Можете също така да „вмъкнете“ IF функция в рамките на IF функция:

1	= if (a1 <0, if (a2 <0, "И двете", "само 1"), "само едно")

Сега за някои конкретни примери за това как функцията IF работи на практика:

1. Започнете нова работна книга.

2. В клетка A1 въведете стойността 10 (и натиснете Enter)

3. След това в клетка В1 въведете следната формула:

1	= АКО (A1> 5, "ПО -ГОЛЕМО ОТ 5", "ПО -МАЛКО ОТ 5")

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

5. Ако сте въвели формулата правилно, ще видите съобщението „По -голямо от 5“ да се появи в клетка В1.

6. Формулата, която сте въвели в клетка B1, изпълнява теста „A1> 5“, т.е. проверява дали стойността в клетка A1 е по -голяма от 5. В момента стойността в клетка A1 е 10 - така че условието е TRUE и съобщението Появява се „BIGGER THAN 5“

7. Ако сега променим стойността в клетка A1 на 2:

След това съобщението в клетка В2 вече е „ПО -малко от 5“, тъй като условието е НЕВЯРНО.

8. Можете да продължите да променяте стойността в клетка A1 и съобщението в клетка B2 ще се коригира съответно.

9. Разбира се, има ситуации, в които състоянието може да даде пакостни резултати:

• Какво ще стане, ако въведем стойност 5 в клетка A1?

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

• Ами ако поставим текст в клетка A1, например фразата DOG

Повече за функцията Excel IF

Сега ще разгледаме функцията IF по -подробно. Може да се използва за много лесно анализиране на големи количества данни.

Представете си, че сте регионален мениджър продажби и имате екип по продажбите. Можете да запишете общите продажби, които всеки човек прави в обикновена електронна таблица на Excel:

Да предположим, че критерият за бонус е, че продажбите, направени от това лице, надхвърлят £ 40,000. Можете просто да „огледате“ данните и да определите, че само Антон, Нютон и Моник са постигнали целта.

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

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

1	= АКО (C4> 40000, "БОНУС ПЛАЩАЩ", "НЯМА БОНУС")

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

Обърнете внимание как Excel показва структурата на формулата IF - което е полезен помощник.

След като въведете формулата, натиснете ENTER и ще видите как се оценява за първия ред:

Формулата е оценена за Мартин - тъй като той е спечелил по -малко от 40 000 паунда, той няма право на бонус.

И след това плъзгаме формулите надолу, като щракнем в долния десен ъгъл и плъзгайки надолу, можем да определим дали или всеки човек има право на бонус:

Виждаме, че Excel е определил кой от търговците има право на бонус.

Връщане към списъка с всички функции в Excel

Изявления на VBA IF

Можете също да използвате If Statements във VBA. Щракнете върху връзката, за да научите повече, но ето един прост пример:

1234567

Sub Test_IF ()Ако Range ("a1"). Стойност <0 тогаваДиапазон ("b1"). Стойност = "Отрицателно"Край АкоКрай Ако

Този код ще тества дали стойността на клетката е отрицателна. Ако е така, в следващата клетка ще пише „отрицателно“.