Використання функції IF Excel: тестування за однією умовою

Функція IF в Excel схожа на швейцарський армійський ніж функцій Excel. Дійсно, він використовується в багатьох ситуаціях. Часто ви можете використовувати функцію IF в Excel з іншими функціями. IF, структурно, легко зрозуміти. Функція IF Excel приймає три аргументи:

• Тест, який дає вірну чи хибну відповідь. Наприклад, тест "чи значення в клітинці A5 дорівнює значенню в клітинці A8" може мати лише одну з двох можливих відповідей, так чи ні. У комп’ютерній розмові це правда чи неправда. Це не розрахунок, зауважте, а порівняння.
• Дані, які повертаються функцією IF, якщо тест істинний.
• Дані, які повертаються функцією IF, якщо тест хибний.

Звучить досить легко. Ось кілька прикладів:

Важливий аспект використання IF: дозволити другому або третьому аргументу нічого не повертати. Повертається порожній рядок, і найкращий спосіб зробити це — помістити дві подвійні лапки разом без нічого посередині. В результаті клітинка, що містить функцію IF, залишається порожньою.

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

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

IF часто використовується як перевірка перевірки для запобігання помилкам . Припустимо, що у вас є фінансовий аркуш, який використовує змінний відсоток у своїх розрахунках. Користувач повинен вводити цей відсоток щодня, але він ніколи не повинен перевищувати 10 відсотків. Щоб запобігти ймовірності помилок, ви можете використовувати функцію IF для відображення повідомлення про помилку в сусідній комірці, якщо ви помилково введете значення за межі дозволеного діапазону. Якщо припустити, що відсоток введено в клітинку A3, ось необхідна функція IF:

=IF(A3>.1, "ПОМИЛКА: % в A3 ЗАДАТО ВЕЛИКИЙ", "")

На наступному зображенні показано, як IF можна ефективно використовувати в бізнес-додатку. Вигаданий магазин — Ken's Guitars (як вам не здається?) — стежить за інвентарем у робочому аркуші Excel.

Слідкуйте за інвентарем у гітарному магазині.

Стовпець D показує рівні запасів, а стовпець E показує рівні повторного замовлення. Це працює таким чином: коли рівень запасів продукту такий самий або менший за рівень повторного замовлення, настав час замовити більше продукту. Комірки в стовпці F містять формулу.

Формула Excel у клітинці F8 — =IF(D8<>. Вона говорить, що якщо кількість гітар Stratoblaster 9000 на складі така ж або менша за рівень повторного замовлення, поверніть Order. Якщо кількість на складі більше, ніж рівень повторного замовлення , нічого не повертати. Нічого не повертається, оскільки в наявності є три, а рівень повторного замовлення дорівнює 2. У наступному рядку кількість Flying Xs дорівнює рівню повторного замовлення, тому в клітинці F9 відображається замовлення.

Використовувати функцію IF в Excel легко. Виконайте такі дії:

Введіть два значення в робочий аркуш. Ці значення повинні мати певне значення для вас, наприклад, наведений вище приклад рівнів запасів.

Клацніть клітинку, у якій потрібно відобразити результат.

Введіть =IF(, щоб запустити функцію.

Вирішіть, який тест ви хочете виконати. Ви можете побачити, чи рівні ці два значення; чи один більший за інший; чи є віднімання одного з іншого більше, дорівнює чи менше 0; і так далі. Наприклад, щоб визначити, чи перше значення дорівнює другому, клацніть першу клітинку (або введіть її адресу), введіть знак рівності (=), а потім клацніть другу клітинку (або введіть її адресу).

Введіть кому (,).

Введіть результат, який має з'явитися, якщо тест відповідає дійсності .

Наприклад, введіть «Значення рівні». Текст необхідно брати в лапки.

Введіть кому (,).

Введіть результат, який має з’явитися, якщо тест хибний . Наприклад, введіть «Значення не рівні» .

Введіть a ) і натисніть Enter.

Функція IF Excel може зробити набагато більше. Вкладені функції IF надають вам набагато більше гнучкості у виконанні тестів даних робочого аркуша. Щоб пройти через це, потрібно трохи наполегливості. Вкладений означає, що ви можете розмістити функцію IF всередині іншої функції IF. Тобто внутрішня IF розміщується там, де йде істинний або хибний аргумент у зовнішньому IF (або навіть використовують внутрішні IF для обох аргументів). Навіщо тобі це робити?

Ось приклад: днями ми вирішували, куди піти на вечерю. ми розглядали італійську мову і вирішили, що якщо ми поїдемо в італійське місце, і там подають манікотті, у нас буде манікотті. Інакше ми вирішили з’їсти піцу.

Логічно це рішення виглядає так:

Якщо ресторан італійський, то якщо в ресторані подають манікотті, то у нас буде манікотті, інакше у нас буде піца

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

=IF(Ресторан=Італійська, IF(У ресторані подають манікотті, "манікотті", "піца"), "")

Якби ресторан був не італійським, не було б значення, який був би вибір (на що вказує третій аргумент зовнішнього IF, який порожній).

Ви можете вкладати до 64 операторів IF, хоча все, ймовірно, стане дуже складним, якщо ви перевищите 4 або 5.

Ви можете застосувати вкладений оператор IF, щоб підвищити складність робочого аркуша інвентаризації зверху. На наступному зображенні є додатковий стовпець: Hot Item. Гарячий елемент може мати три форми:

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

Шукаємо гарячі предмети інвентарю.

Є гарячі предмети, а є теплі. Обидва мають відповідати загальному критерію, що запас становить 50 відсотків або менше від рівня повторного замовлення. Тільки після виконання цієї першої умови вступає в дію другий критерій — кількість днів з моменту останнього замовлення. Для мене це звучить як вкладене IF! Ось формула в клітинці G8:

=IF(D8<><><>

Добре, вдихни.

Зовнішній IF перевіряє, чи дорівнює запас у стовпці D половині (50 відсотків) рівня повторного замовлення чи менше. Частина формули, що робить це =IF(D8<>. Цей тест, звичайно, дає істинну чи хибну відповідь. Якщо вона хибна, береться хибна частина зовнішнього IF (яка є просто порожнім рядком). знаходиться в кінці формули: ,"")).

Це залишає всю середню частину, щоб пройти через неї. Залишайтеся з цим!

Якщо перший тест істинний, береться істинна частина зовнішнього ПЧ. Так вийшло, що ця істинна частина є іншою функцією IF:

IF(NOW()-C8<><>

Перший аргумент Excel внутрішнього IF перевіряє, чи є кількість днів з дати останнього замовлення (у стовпці C) меншою або дорівнює 30. Для цього потрібно відняти дату останнього замовлення від сьогоднішнього дня, як це отримано з функції NOW .

Якщо тест відповідає дійсності, а дата останнього замовлення – протягом останніх 30 днів, ГАРЯЧО! повертається. Справді гарячий продавець! Якщо тест хибний… зачекайте, що це? Ще одна функція ЯКЩО! Так: IF всередині IF всередині IF. Якщо кількість днів з дати останнього замовлення перевищує 30, наступний вкладений IF перевіряє, чи була кількість днів у межах останніх 60 днів:

IF(NOW()-C8<>

Якщо цей тест правдивий, Тепло! повертається. Якщо тест хибний, нічого не повертається.

Кілька ключових моментів щодо цього трирівневого оператора IF:

• IF, яка перевіряє, чи кількість днів, що минули, становить 30 або менше, має значення, яке повертається, якщо істина (ГОРЯЧЕ!) і значення, яке повертається як false (все, що повертається наступним вкладеним IF).
• Зовнішнє ПЧ і внутрішнє ПЧ нічого не повертають, якщо їх перевірка хибна.
• На перший погляд, тест протягом 60 або менше днів також вловить дату, яка становить 30 днів або менше від дати останнього замовлення. Це насправді не те, що має бути. Перевірка має полягати в тому, чи є кількість днів, що минули, 60 або менше, але більше 30. Вам не потрібно насправді прописувати це таким чином, оскільки формула досягла точки тестування 60-денного порогу лише тому, що 30 -денний поріг уже не вдалося. Треба остерігатися цих речей!