IF Формула Excel - висловлювання If Then

Завантажити Приклад робочої книги

Завантажте зразок робочої книги

Цей підручник демонструє, як користуватися Функція Excel IF в Excel для створення операторів If Then.

Огляд функції IF

Функція IF перевіряє, чи виконується умова. Якщо ІСТИНА зробить одне, якщо НЕВІРНО зробить інше.

Щоб скористатися функцією робочого аркуша Excel Excel, виберіть клітинку та введіть:

(Зверніть увагу, як виглядають вхідні дані формул)

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

1	= IF (логічний_тест, значення_якщо_істина, значення_якщо_фальш)

логічний_тест - Логічний вираз. Приклад: A1> 4.

value_if_true - Значення або Розрахунок для виконання, якщо логічний вираз ІСТИНА.

value_if_false - Значення або Розрахунок для виконання, якщо логічний вираз НЕВІРНИЙ.

IF є «умовною» функцією. Це означає, що ви визначили логічний тест, і він поверне одне значення, якщо цей тест оцінюється як істинне, і інше значення, якщо воно є хибним

Як користуватися функцією IF

Ось дуже простий приклад, щоб ви зрозуміли, що я маю на увазі. Спробуйте ввести в Excel наступне:

1	= IF (2 + 2 = 4, "Це правда", "Це хибно!")

Оскільки 2 + 2 насправді дорівнює 4, Excel поверне «Це правда!». Якби ми використовували це:

1	= IF (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 поверне ІСТИНУ, тільки якщо ВСІ перевірки є істинними.

Отже, ви запровадили свою процентну ставку. Але деякі ваші постійні люди скаржаться. Вони завжди сплачували за свої вкладки в минулому, чому ви розправляєтесь із ними зараз? Ви придумали рішення: ви не будете стягувати відсотки з певних надійних клієнтів.

Ви створюєте новий стовпець для своєї електронної таблиці, щоб ідентифікувати надійних клієнтів, і оновлюєте оператор IF за допомогою функції AND:

1	= IF (AND (C4> D4, F4 = "Ні"), C4*0,03,0)

Давайте розглянемо частину І окремо:

1	І (C4> D4, F4 = "Ні")

Зверніть увагу на дві умови:

• C4> D4: перевірка, якщо вони перевищують обмеження вкладок, як раніше
• F4 = "Ні": це новий біт, який перевіряє, чи не є він надійним клієнтом

Тож тепер ми повертаємо відсоткову ставку, лише якщо клієнт перейшов на вкладку, а у стовпці довірених клієнтів є «Ні». Ваші постійні знову щасливі.

Детальніше читайте на головній сторінці функції Excel AND <>.

Використання IF з OR

АБО - ще одна з логічних функцій Excel. Подібно І, це дозволяє вам визначити кілька умов. Але на відміну від AND, воно поверне TRUE, якщо будь -який із визначених вами тестів відповідає дійсності.

Можливо, клієнти переглядають свою вкладку - не єдина причина, чому ви їх відрізали. Можливо, ви даєте тимчасовим заборону деяким людям з інших причин, можливо, граєте в приміщення.

Тож ви додаєте новий стовпець, щоб ідентифікувати заборонених клієнтів, і оновлюєте своє "Вирізати?" стовпець з тестом АБО:

1	= IF (АБО (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 доларів, але не продажів продуктів харчування. Тому він отримує половину бонусу. Для тренера - зворотне. Дайан і Карла продали більше 800 доларів за обох, тому вони не отримують половини бонусу (обидва аргументи - ІСТИНА), а Ребекка зробила під порогом для обох (обидва аргументи НЕВІРНО), тому формула знову повертає «Ні».

Докладніше читайте на головній сторінці функції Excel XOR <>.

Використання IF з NOT

NOT - це ще одна з логічних функцій Excel, яка дуже часто використовується з IF.

НЕ скасовує результати логічного тесту. Іншими словами, він перевіряє, чи не виконується умова.

Ви можете використовувати його з IF таким чином:

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

Тут ми маємо таблицю з даними про деякі фільми 1980 -х років. Ми хочемо визначити фільми, випущені 1985 або після 1985 року, які не були режисером Стівена Спілберга.

Оскільки NOT вкладено у функцію AND, Excel спочатку оцінить це. Потім він буде використовувати результат як частину І.

Детальніше читайте на головній сторінці функції Excel NOT <>.

Вкладені оператори IF

Ви також можете повернути оператор IF у своїй заяві IF. Це дозволяє робити більш складні розрахунки.

Повернемося до таблиці наших клієнтів. Уявіть, що ви хочете класифікувати клієнтів на основі рівня їх заборгованості перед вами:

• $ 0: Жодного
• До 500 доларів: низький
• $ 500 до $ 1000: Середній
• Понад 1000 доларів: висока

Ви можете зробити це, вставивши оператори IF:

1	= IF (C4 = 0, "None", IF (C4 <= 500, "Low", IF (C4 <= 1000, "Medium", IF (C4> 1000, "High")))

Це легше зрозуміти, якщо розмістити оператори IF в окремих рядках (ALT + ENTER у Windows, CTRL + COMMAND + ENTER на Mac):

12345

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

ЯКЩО С4 дорівнює 0, ми повертаємо “None”. В іншому випадку ми переходимо до наступного оператора IF. Якщо С4 дорівнює або менше 500, ми повертаємо «Низький». В іншому випадку ми переходимо до наступного оператора IF … тощо.

Спрощення складних операторів IF за допомогою стовпців -помічників

Якщо у вас є кілька вкладених операторів IF, і ви також використовуєте логічні функції, ваші формули можуть стати дуже важкими для читання, тестування та оновлення.

Це особливо важливо мати на увазі, якщо інші люди будуть використовувати електронну таблицю. Те, що має сенс у вашій голові, може бути не таким очевидним для інших.

Допоміжні стовпці - чудовий спосіб вирішити цю проблему.

Ви аналітик фінансового відділу великої корпорації. Вас попросили створити електронну таблицю, яка перевірятиме, чи кожен працівник має право на пенсію компанії.

Ось критерії:

Отже, якщо ви молодше 55 років, вам потрібно мати 30 років стажу, щоб мати право на це. Якщо вам від 55 до 59 років, вам потрібен стаж роботи 15 років. І так далі, до 65 років, де ви маєте право незалежно від того, скільки часу ви там пропрацювали.

Ви можете використати єдиний складний оператор IF для вирішення цієї проблеми:

1	= IF (АБО (F4> = 65, AND (F4> = 62, G4> = 5), AND (F4> = 60, G4> = 10), AND (F4> = 55, G4> = 15), G4 > 30), "Допустимо", "Не підходить")

Вау! Досить важко обійти це головою, чи не так?

Кращим підходом може бути використання допоміжних стовпців. Тут ми маємо п’ять логічних тестів, що відповідають кожному рядку в таблиці критеріїв. Це легше зрозуміти, якщо ми додамо розриви рядків до формули, як ми обговорювали раніше:

12345678

= IF (АБО (F4> = 65,AND (F4> = 62, G4> = 5),І (F4> = 60, G4> = 10),І (F4> = 55, G4> = 15),G4> 30), "Допустимо", "Не підходить")

Отже, ми можемо розділити ці п’ять тестів на окремі стовпці, а потім просто перевірити, чи правдивий якийсь із них:

Кожен стовпець у таблиці від E до I містить кожен наш критерій окремо. Тоді в J4 ми маємо таку формулу:

1	= IF (COUNTIF (E4: I4, TRUE), "Допустимо", "Не підходить")

Тут ми маємо оператор IF, і логічний тест використовує COUNTIF <> для підрахунку кількості клітинок у E4: I4, які містять TRUE.

Якщо COUNTIF не знаходить значення TRUE, воно повертає 0, що IF трактується як FALSE, тому IF повертає “Not Eligible”.

Якщо 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, "Високий",TRUE, "Невідомо",))

Ви можете прочитати все про це на головній сторінці функції IFS Excel <>.

Використання 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, щоб кожне з цих множень відбувалося лише в тому випадку, якщо логічний тест повертає істину.

Наприклад, візьмемо такі дані:

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 (“Олівія”), DO помножте значення у стовпцях D та E для цього рядка. В іншому випадку не помножуйте їх. Потім підсумуйте всі результати.

Ви можете дізнатися більше про цю формулу на головній сторінці Формули SUMPRODUCT IF <>.

ІФ у Google Таблицях

Функція IF працює точно так само в Google Таблицях, як і в Excel:

додаткові нотатки

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

Функція IF може перевіряти лише одну умову одночасно. Однак ви можете "вкласти" інші логічні функції в умові IF, щоб перевірити кілька умов одночасно:

= if (AND (a1> 0, a2> 0), TRUE, FALSE)
= якщо (АБО (a1> 0, a2> 0), TRUE, FALSE)
= якщо (XOR (a1> 0, a2> 0), TRUE, FALSE)
АБО Перевірка функцій, якщо один або кілька умови дотримані.
Функції AND перевіряють, якщо все умови дотримані.
Перевірка функцій XOR, якщо один і єдиний умова виконана.

Ви також можете "вкласти" функцію IF у функцію IF:

1	= якщо (a1 <0, якщо (a2 <0, "Обидва", "тільки 1"), "тільки один")

Тепер кілька конкретних прикладів того, як функція IF працює на практиці:

1. Створіть нову трудову книжку.

2. У клітинці A1 введіть значення 10 (і натисніть Enter)

3. Потім у клітинку В1 введіть таку формулу:

1	= ЕСЛИ (A1> 5, "БІЛЬШЕ ЗА 5", "МЕНШЕ 5")

4. Тепер екран повинен виглядати так:

5. Якщо ви правильно ввели формулу, у клітинці В1 з’явиться повідомлення “Більше 5”.

6. Формула, яку ви ввели у клітинку В1, виконує перевірку “A1> 5”, тобто перевіряє, чи значення у комірці А1 більше 5. Наразі значення у клітинці А1 дорівнює 10 - отже, умова - ІСТИНА та повідомлення З'явиться повідомлення «БІЛЬШЕ, ніж 5»

7. Якщо тепер змінити значення в комірці A1 на 2:

Тобто повідомлення у комірці В2 тепер “МЕНШЕ 5”, оскільки умова НЕВІРНА.

8. Ви можете продовжувати змінювати значення в комірці А1, і повідомлення у комірці В2 буде відповідно коригуватися.

9. Звичайно, є ситуації, коли стан може дати жалюгідні результати:

• Що станеться, якщо ми введемо значення 5 у клітинку A1?

• Що робити, якщо залишити клітинку А1 порожньою?

• Що робити, якщо ми помістимо текст у клітинку A1, наприклад фразу DOG

Докладніше про функцію Excel IF

Тепер ми розглянемо функцію IF більш детально. Його можна дуже легко аналізувати велику кількість даних.

Уявіть, що ви - менеджер відділу продажу та маєте команду з продажу. Ви можете записати загальний обсяг продажів, які здійснює кожна особа, у простій таблиці Excel:

Припустимо, що критерієм отримання бонусу було те, що продажі, здійснені цією особою, перевищили 40 000 фунтів стерлінгів. Ви можете просто «зірвати» дані і визначити, що тільки Антон, Ньютон і Монік досягли цілі.

Це досить легко, коли у вас є лише кілька імен. Однак, якщо у вас їх кілька, є можливість помилок. На щастя, за допомогою функції IF Excel це можна зробити набагато швидше та безпечніше.

Налаштуйте нову книгу та введіть дані, як зазначено вище. Потім у клітинку D4 введіть таку формулу:-

1	= IF (C4> 40000, "ПЛАТА БОНУСУ", "БОНУС НІ")

щоб у вас було:

Зверніть увагу, як Excel показує структуру формули IF - що є корисним помічником.

Ввівши формулу, натисніть клавішу ENTER, і ви побачите її оцінку для першого рядка:

Формула була оцінена для Мартіна - оскільки він заробив менше 40 000 фунтів стерлінгів, він не має права на бонус.

А потім перетягуємо формули вниз, натискаючи нижній правий кут і перетягуючи вниз, ми можемо визначити, чи кожна особа має право на бонус:

І ми бачимо, що Excel визначив, хто з продавців має право на бонус.

Повернення до списку всіх функцій Excel

Заяви VBA IF

Ви також можете використовувати оператори If у VBA. Натисніть на посилання, щоб дізнатися більше, але ось простий приклад:

1234567

Sub Test_IF ()Якщо діапазон ("a1"). Значення <0, тодіДіапазон ("b1"). Значення = "Негативне"Закінчити ЯкщоЗакінчити Якщо

Цей код перевірить, чи значення клітинки від’ємне. Якщо так, то в наступній клітинці він напише “мінус”.