Как да използвам функцията if then в excel. Как да изчислим бонус за продажби с помощта на вложени функции if() в excel
Възможността за извършване на логически проверки на клетките е мощен инструмент. Ще намерите безкраен брой приложения за АКО() във вашите ежедневни документи. Проблемът е, че често необходимите проверки са извън възможностите на проста функция АКО() . Това се случва, когато трябва да проверите за две или повече условия, преди да приемете резултата.
За такива случаи Excel предоставя няколко опции: използване АКО() вътре в друг АКО(), функции И()и IL И(). След това ще разгледаме тези методи.
Използване АКО()вътре в друга функция АКО()
Нека разгледаме вариант на базата на предварително проучената функция =IF(A1>1000; "много"; "няколко"). Ами ако трябва да отпечатате друг ред, когато числото в A1 е, например, по-голямо от 10 000? С други думи, ако A1>1000 е вярно, ще искате да изпълните друг тест и да видите дали A1>10000 е вярно. Можете да създадете такава опция, като приложите втората функция АКО() вътре в първия, като аргумент, стойността _if_true: =IF(A1>1000;IF(A1>10000;"много", "много");"няколко") .
Ако A1>1000 е вярно, стартирайте друга функция АКО(), което връща „твърде много“, когато A1>10000. Ако обаче A1 е по-малко или равно на 10000, се връща стойността "много". Ако при първата проверка числото A1 е по-малко от 1000, ще се покаже стойността "малко".
Имайте предвид, че можете също да изпълните втора проверка, ако първата е false (тоест в аргумента value_if_false на функцията ifo). Ето малък пример, който връща "много малко", когато числото в A1 е по-малко от 100: =IF(A1>1000,"много",IF(A1<100;"очень мало"; "мало")) .
Изчисляване на бонус за продажби
Добър пример за използване на един чек в друг чек е изчисляването на бонус за продажби на персонала. който работи в клуб хотел Heliopark Thalasso, Звенигород. В този случай, ако стойността е X, искате един резултат, ако Y, друг, ако Z
- трети. Например, в случай на изчисляване на бонус за успешни продажби са възможни три опции:
- Продавачът не е достигнал планираната стойност, бонусът е 0.
- Продавачът надхвърли планираната стойност с по-малко от 10%, бонусът е 1000 рубли.
- Продавачът надхвърли планираната стойност с повече от 10%, бонусът е 10 000 рубли.
Ето формулата за изчисляване на такъв пример: =IF(E3>0,IF(E3>0.1,10000,1000),0) . Ако стойността в E3 е отрицателна, тогава се връща 0 (без бонус). В случай, че резултатът е положителен, се проверява дали е по-голям от 10% и в зависимост от това се издават 1000 или 10 000. 4.17 показва пример за това как работи формулата.
AND() функция
Често е необходимо да се тества само за вярност на две условия едновременно. Например, бонус плащане се извършва само когато продажбите на определен продукт надвишават целта, а също и общите продажби надхвърлят целта. Ако едно от тези условия не е изпълнено (или и двете условия), премията не се изплаща. В булевата логика това се нарича логическо И, тъй като и двете условия трябва да са верни, за да е верен целият израз.
Булеви изрази в Excel Иобработени с помощта на функцията И(): И (логично1, булево2,…). Всеки аргумент е булева стойност за тестване. Можете да въведете толкова аргументи, колкото са ви необходими.
Още веднъж отбелязваме работата на функцията:
- Ако всички изрази върнат TRUE (или всяко положително число), И()връща TRUE.
- Ако един или повече аргументи връщат FALSE (или 0), И()връща FALSE.
Най-често И()приложен вътре във функция АКО(). В този случай, когато всички аргументи вътре И()върне TRUE, функция АКО()стойността ще върви по своя клон, ако е истина. Ако един или повече от изразите в И()ще върне FALSE, функция АКО()ще следва клона value_if_false.
Ето малък пример: =IF(AND(C2>0;B2>0);1000;"без бонус") . Ако стойността в B2 е по-голяма от нула и стойността в C2 е по-голяма от нула, формулата ще върне 1000, в противен случай ще се покаже низът "без бонус".
Разделяне на стойности в категории
Полезно използване на функцията и () е категоризирането въз основа на стойност. Например, имате таблица с резултатите от някаква анкета или гласуване и искате да разделите всички гласове в категории според следните възрастови диапазони: 18-34,35-49, 50-64,65 и повече. Ако приемем, че възрастта на респондента е в клетка B9, следните аргументи на функцията и () извършват логическа проверка, за да видят дали възрастта принадлежи на диапазона: =AND(B9>=18;B9
Ако отговорът на лицето е в клетка C9, следната формула ще върне гласа на лицето, ако се задейства проверката на възрастовата група 18-34: =IF(AND(B9>=18,B9)
- 35-49: =АКО(И(B9>=35,B9
- 50-64: =АКО(И(B9>=50,B9
- 65+: =IF(B9>=65,C9,"")
ИЛИ() функция
Има ситуации, в които се нуждаете от решение само когато едно от условията е положително (ВЯРНО). Например, може да решите да плащате бонус на служителите, когато общите продажби надвишават целта или когато продажбите на отделен продукт надхвърлят прогнозата. В булева логика това се нарича булева ИЛИ.
Такива условия се проверяват в Excel с помощта на функцията ИЛИ(): ИЛИ(булев1; булев2;...). Всеки аргумент е булева стойност за тестване. Можете да въведете толкова аргументи, колкото са ви необходими. Резултатът от работата ИЛИ()зависи от следните условия:
- Ако един или повече аргументи връщат TRUE (всяко положително число), ИЛИ()връща TRUE.
- Ако всички аргументи връщат FALSE (нула), резултатът от операцията ИЛИ()ще бъде НЕВЕРНО.
Точно като И(), най-често функцията ИЛИ()използвана вътрешна проверка АКО(). В такъв случай, когато един от аргументите вътре ИЛИ()ще върне TRUE, функция АКО()ще следва свой собствен клон value_if_true. Ако всички изрази са в ИЛИ()върне FALSE, функция АКО()ще следва нишката стойност_ако_грешно. Ето малък пример: = IF(OR(C2>0;B2>0);1000;"без бонус") .
В случай, че една от клетките (C2 или B2) съдържа положително число, функцията ще върне 1000. Само когато и двете стойности са отрицателни (или равни на нула), функцията ще върне низа „без бонус“.
Статията ще се фокусира върху функцията на редактора на електронни таблици Excel "If". Свързано е с логическите възможности на приложението. Тази функция може да се припише на най-често срещаната, която често се използва по време на работа.
Основни функции
V програма ExcelФормулата „Ако“ позволява различни видове задачи, които изискват да сравните определени стойности и да получите резултата. Това решение дава възможност да се използват алгоритми за разклоняване и да се създаде дърво на решенията.
Примери за приложение
Функцията изглежда така: =IF(задача; true; false). Първо интегрална часте булев израз. Може да действа като фраза или число. Например "10" или "без ДДС". Този параметър трябва да бъде попълнен. True е стойността, показана като резултат, ако изразът е true. False са данните, които ще бъдат върнати, когато задачата е false.
Равенство на параметрите на две клетки
За да разберете по-добре възможностите на функцията "Ако" в Excel, е необходимо да дадете примери. Ето защо си струва да започнете да ги разглеждате по-нататък. Трябва да въведете стойността 8 в клетка C1. След това в полето на адрес D1 трябва да въведете формула като: =IF(C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.
Може да се разгледа още един пример. Има няколко студенти и техните оценки, които се спечелват за изпита. Данните са както следва: 5, 4, както и 3 и 2. Според условието на задачата е предвидено създаване на текстов коментар „преминал“ или „неуспешен“ за всеки ученик. По този начин, ако студент получи оценка от три или повече, той се счита за издържал изпита. Ако резултатът му е под 3, ученикът не се е справил със задачата. За да разрешите такъв проблем, трябва да напишете следната формула: =IF(C1<3; «не справился»; «сдал»).
Програмата започва да сравнява резултатите на всеки ученик. Ако индикаторът е по-малък от три, надписът "неуспешен" ще се появи в съответната клетка. Когато оценката е 3 и повече, в задължителната колона може да видите коментар, че студентът не е издържал изпита. Струва си да се отбележи, че текстовите коментари винаги трябва да се пишат в кавички. Когато използвате тази функция, можете да използвате следните оператори за сравнение:< >, =, >, <, >=, <=.
Примери, използващи условия "ИЛИ", "И".
Необходимо е допълнително да се разгледат логическите възможности на това приложение. Възможно е да свържете функцията "If" с оператори за сравнение. Това са следните опции:
"ИЛИ";
"И".
Необходимо е да се посочи важно условие в Excel: когато оценката на ученика е равна или по-малка от 5, но по-голяма от 3. В този случай трябва да се покаже коментар: „издържа“ или „не“. Така минават само онези ученици, които са спечелили петици и четворки. За да запишете тази задача в редактор на електронни таблици, трябва да приложите специална формула. Изглежда така: =IF(AND(A1<=5; A1>3); „преминаване“, „не“).
Ако разгледаме по-сложен пример, ще трябва да използвате „ИЛИ“ или „И“. По този начин можете да се запознаете с приложението на формулата в Excel, ако има няколко условия в задачата. Например: =IF(OR(A1=5; A1=10); 100; 0). В този случай можете да заключите, че когато стойността в клетка A1 е 5 или 10, програмата показва резултата 100. В противен случай е 0. Възможно е да се прилагат тези оператори, за да се намери решение на по-сложни проблеми.
Например, в базата данни се изисква да се изчислят длъжниците, които трябва да платят повече от 10 000 рубли. Можете да зададете условието заемът да не е изплащан повече от шест месеца, тоест шест месеца. Благодарение на функцията "If" на редактора на електронни таблици на Excel е възможно автоматично да се получи знак "проблемен клиент" до съответните имена. Да кажем, че клетка A1 съдържа данни, които показват периода на дълга (месеци). Поле B1 съдържа сумата.
В този случай формулата е представена в следния вид: =IF(AND(A1>=6; B1>10000); „проблемен клиент“; „“). По този начин, ако бъде идентифицирано лице, което отговаря на посочените условия, програмата показва съответния коментар срещу неговото име. За останалите членове на списъка тази клетка ще остане празна.
Можете също да видите пример, когато ситуацията е критична. Трябва да се въведе подходящ коментар. В резултат на това формулата ще бъде както следва: =IF(OR(A1>=6; B1>10000); "критична ситуация"; ""). Въпреки това, ако програмата отговаря на поне един от параметрите, се показва съответната бележка.
Трудни задачи
Функцията "If" на Excel се използва за избягване на вградени грешки при деление на нула. Освен това се използва в някои други ситуации. Първият случай е обозначен като "DIV/0". Може да се намери доста често. Това обикновено се случва, когато формулата "A/B" трябва да бъде копирана. В същото време, Б отделни клеткие 0. За да избегнете подобна ситуация, си струва да използвате възможностите на въпросния оператор. Така необходимата формула е, както следва: =IF(B1=0, 0, A1/B1). Така че, ако клетка B1 е запълнена със стойността "нула", редакторът ще покаже "0". В противен случай програмата ще раздели индикатора A1 на данните B1 и ще даде желания резултат.
Отстъпка
Както показва практиката, често възникват ситуации, които са разгледани по-долу. Ще трябва да изчислите отстъпките, като вземете предвид общата сума на средствата, изразходвани за покупката на конкретен продукт. Матрицата, която се използва е както следва: по-малко от 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; повече от 5001 - 7%. Можете да се запознаете със ситуацията, при която Excel има база данни с посетители и информация за сумата, изразходвана за покупка. Следващата стъпка е да се изчисли отстъпката за всеки клиент. За да направите това, трябва да приложите следния израз: =IF(A1>=5001; B1*0.93; IF(A1>=3001; B1*0.95;..).
Системата проверява общата сума на покупките. Ако надвишава стойността от 5001 рубли, цената на продукта се умножава по 93 процента. Ако се надвиши марката от 3001 единици, се извършва подобно действие, но 95% вече са взети предвид.
IF() функция , Английска версияАКО(), използва се при проверка на условията. Например, =IF(A1>100;"Бюджетът е надвишен";"ОК!"). В зависимост от стойността в клетката A1резултатът от формулата ще бъде или "Бюджетът е надвишен" или "ОК!".
Функцията IF() е една от най-често използваните функции.
Синтаксис на функцията
IF(логически_израз, стойност_ако_истина, [стойност_ако_невярно])
Логически_израз- всяка стойност или израз, който се оценява на TRUE или FALSE.
=IF(A1>=100;"Бюджетът е надвишен";"ОК!")
Тези. ако е в клетка A1
съдържа стойност, по-голяма или равна на 100, формулата ще върне ДОБРЕ!,и ако не, тогава Бюджетът е надвишен.
Формулите могат да се използват като аргументи на функцията, например:
=АКО(A1>100,SUM(B1:B10),SUM(C1:C10))
Тези. ако е в клетка A1
съдържа стойност >100, след което сумирането се извършва върху колоната Б
, а ако е по-малко, тогава по колона С
.
Вложено IF
В EXCEL 2007 като стойности на аргументи стойност_ако_истинаи стойност_ако_грешноможете да използвате до 64 вложени функции IF() за изграждане на по-сложни проверки.
=IF(A1>=100;"Бюджетът е надвишен";IF(A1>=90;"Голям проект";IF(A1>=50;"Среден проект";"Малък проект")))
ПРЕГЛЕД(A1;(0;50;90;100);("Малък проект";"Среден проект";"Голям проект";"Бюджет надвишен"))
VLOOKUP(A1;A3:B6;2)
За функцията VLOOKUP() трябва да създадете в диапазона A3:B6 таблица със стойности:
Ако искате да покажете различен текст в случай на присъствие в клетката A1 отрицателна стойност, положителна стойност или 0, тогава можете да напишете следната формула:
ПРЕГЛЕД(A1,(-1E+307,0,1E-307),("<0";"=0";">0"})
или, ако искате да покажете формули вместо текстови стойности, можете да използвате връзки към диапазон от клетки (съдържащи формули)
ПРЕГЛЕД(A24,(-1E+307,0,1E-307),A27:A29)(вижте примерен файл)
Пропуснат трети аргумент [value_if_false]
Третият аргумент на функцията е незадължителен, ако е пропуснат, функцията ще върне FALSE (ако условието не е изпълнено).
=IF(A1>100,"Бюджетът е надвишен")
Ако в клетка A1
съдържа стойността 1, горната формула ще върне FALSE.
Вместо TRUE или FALSE, в първия аргумент се въвежда число
Защото стойността FALSE е еквивалентна на 0, след това формулите
=IF(0;"Бюджетът е надвишен";"ОК!")
или (ако клетката A1
съдържа стойността 0)
=АКО(A1;"Бюджетът е надвишен";"ОК!")
Ще се върне ДОБРЕ!
Ако в клетка A1 има друго число освен 0, тогава формулата ще се върне Бюджетът е надвишен. Този подход е полезен при проверка дали стойността е равна на нула.
Свързване на функцията IF() с други функции с помощта на условия
EXCEL съдържа и други функции, които могат да се използват за анализ на данни с помощта на условия. Например, за да преброите броя на появяванията на числа в диапазон от клетки, използвайте функцията COUNTIF() и за добавяне на стойности, които отговарят на определени условия, използвайте функцията SUMIF().
Функцията IF() като алтернатива може да се използва и за броене и добавяне на стойности с помощта на условия. Следват илюстративни примери.
Нека данните са в диапазона A6:A11 (вижте примерен файл)
Нека изчислим сумата от стойности, които са по-големи от 10, като използваме функцията SUMIF(), като пишем =SUMIF(A6:A11,">10"). Подобен резултат (23) може да се получи, като се използва
=SUM(АКО(A6:A11>10,A6:A11))
(за да въведете формула в клетка вместо ENTERтрябва да натиснете CTRL+SHIFT+ENTER)
Сега нека преброим броя на поява на числа по-големи от 10 в диапазон от клетки A6:A11
=COUNTIF(A6:A11,">10"). Подобен резултат (2) може да се получи с помощта на
=БРОЙ(АКО(A6:A11>10,A6:A11))
Сега, когато концепцията е ясна, можете да използвате функцията IF(), за да конструирате други формули с условия. Например, намиране на минималната стойност сред числа, по-големи от 10:
=MIN(IF(A6:A11>10,A6:A11))