Функція IF в Excel. Приклади (з кількома умовами)

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

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

Функція IF в Excel (загальна інформація)

Будь-яка програма, навіть якщо вона невелика, обов'язково містить послідовність дій, яка називається алгоритмом. Це може виглядати так:

1. Перевірте весь стовпець A на парні числа.
2. Якщо знайдено парне число, додайте такі-то значення.
3. Якщо парне число не знайдено, то вивести напис «не знайдено».
4. Перевірте, чи парне отримане число. 
5. Якщо так, то додайте його до всіх парних чисел, вибраних у параграфі 1.

І навіть якщо це всього лише гіпотетична ситуація, яка навряд чи знадобиться в реальному житті, виконання будь-якого завдання обов'язково передбачає наявність подібного алгоритму. Перед використанням функції ЯКЩО, в голові потрібно мати чітке уявлення про те, якого результату ви хочете досягти. 

Синтаксис функції IF з однією умовою

Будь-яка функція в Excel виконується за допомогою формули. Шаблон, за яким дані повинні передаватись у функцію, називається синтаксисом. У випадку з оператором IF, формула буде в цьому форматі.

=ЯКЩО (логічний_вираз, значення_якщо_істина, значення_якщо_хибність)

Давайте розглянемо синтаксис докладніше:

1. Логічний вираз. Це сама умова, відповідність або невідповідність якої перевіряє Excel. Перевіряти можна як числову, так і текстову інформацію.
2. Значення_якщо_істина. Результат, який буде відображено в комірці, якщо дані, що перевіряються, відповідають заданим критеріям.
3. значення_якщо_хибне. Результат, який відображається в клітинці, якщо дані, що перевіряються, не відповідають умові.

Ось приклад для наочності.

Тут функція порівнює клітинку A1 з числом 20. Це перший абзац синтаксису. Якщо вміст перевищує це значення, у комірці, де була записана формула, відображається значення «більше 20». Якщо ситуація не відповідає цій умові – «менше або дорівнює 20».

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

Ось інша ситуація. Для допуску до екзаменаційної сесії студенти повинні скласти залікову сесію. Учням вдалося зібрати заліки з усіх предметів, і тепер залишився останній, який виявився вирішальним. Наше завдання визначити, хто зі студентів допущений до іспитів, а хто ні.

Оскільки ми хочемо перевірити текст, а не число, першим аргументом є B2=”cons”.

Синтаксис функції IF із кількома умовами

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

Щоб було зрозуміліше, ось синтаксис.

=ЯКЩО(логічний_вираз, значення_якщо_істина, ЯКЩО(логічний_вираз, значення_якщо_істина, значення_якщо_хибність))

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

Ось приклад.

І за допомогою такої формули (показана на скріншоті нижче) можна проаналізувати успішність кожного учня.

Як бачимо, тут додали ще одну умову, але принцип не змінився. Таким чином можна перевірити відразу кілька критеріїв.

Як розширити функціональні можливості IF за допомогою операторів І та АБО

Час від часу виникає ситуація, щоб перевірити відразу на відповідність декільком критеріям, а не використовувати логічні вкладені оператори, як у попередньому прикладі. Для цього скористайтеся або функцією И або функція OR в залежності від того, чи потрібно відповідати відразу кільком критеріям або хоча б одному з них. Розглянемо ці критерії детальніше.

Функція IF з умовою І

Іноді потрібно перевірити вираз на кілька умов одночасно. Для цього використовується функція І, записана в першому аргументі функції IF. Це працює так: якщо a дорівнює одиниці, а a дорівнює 2, значення буде c.

Функція IF з умовою «АБО».

Функція АБО працює подібним чином, але в цьому випадку виконується лише одна з умов. Наскільки можливо, таким чином можна перевірити до 30 умов. 

Ось кілька способів використання функцій И и OR як аргумент функції IF.

Порівняння даних двох таблиць

Час від часу можна порівняти дві подібні таблиці. Наприклад, людина працює бухгалтером і їй потрібно порівняти два звіти. Є й інші подібні завдання, наприклад, порівняння вартості товарів різних партій, потім оцінки студентів за різні періоди і так далі.

Щоб порівняти дві таблиці, використовуйте функцію COUNTIF. Давайте розглянемо це більш детально.

Припустимо, у нас є дві таблиці, що містять характеристики двох кухонних комбайнів. І нам потрібно порівняти їх, і виділити відмінності кольором. Це можна зробити за допомогою умовного форматування та функції COUNTIF. 

Наша таблиця виглядає так.

Підбираємо асортимент, відповідний технічним характеристикам першого кухонного комбайна.

Після цього клацніть такі меню: Умовне форматування – створити правило – використовувати формулу для визначення відформатованих клітинок.

У вигляді формули для форматування запишемо функцію =COUNTIF (діапазон для порівняння; перша клітинка першої таблиці)=0. Таблиця з характеристиками другого кухонного комбайна використовується як діапазон порівняння.

Необхідно переконатися, що адреси є абсолютними (зі знаком долара перед назвами рядків і стовпців). Додайте =0 після формули, щоб Excel шукав точні значення.

Після цього потрібно задати форматування осередків. Для цього біля зразка потрібно натиснути на кнопку «Форматувати». У нашому випадку ми використовуємо заливку, тому що вона найбільш зручна для цієї мети. Але ви можете вибрати будь-яке форматування.

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

Функція SUMIF в Excel

Тепер перейдемо до функцій IF, що допоможе замінити відразу два пункти алгоритму. Перший є САММСЛІ, який додає два числа, які відповідають певній умові. Наприклад, перед нами стоїть завдання визначити, скільки грошей потрібно виплачувати в місяць всім продавцям. Для цього необхідно.

1. Додайте рядок із загальним доходом усіх продавців і клацніть клітинку, яка буде містити результат після введення формули. 
2. Знаходимо кнопку fx, яка знаходиться поруч з рядком для формул. Далі з'явиться вікно, де через пошук можна знайти потрібну функцію. Після вибору оператора необхідно натиснути кнопку «ОК». Але ручне введення завжди можливе.
   

   

3. Далі з'явиться вікно для введення аргументів функції. Всі значення можна вказати у відповідних полях, а діапазон ввести через кнопку поруч з ними.
   

   

4. Перший аргумент – діапазон. Тут ви вводите комірки, які ви хочете перевірити на відповідність критеріям. Якщо говорити про нас, то це посади співробітників. Введіть діапазон D4:D18. Або просто виберіть клітинки, які вас цікавлять.
5. У полі «Критерії» введіть посаду. У нашому випадку – «продавець». В якості діапазону підсумовування вказуємо ті комірки, де вказані зарплати співробітників (це робиться як вручну, так і виділяється мишкою). Натискаємо «ОК», і отримуємо готову нараховану заробітну плату всіх співробітників, які є продавцями.

Погодьтеся, що це дуже зручно. Це не воно?

Функція SUMIFS в Excel

Ця функція дозволяє визначити суму значень, які відповідають декільком умовам. Наприклад, нам поставили завдання визначити загальну зарплату всіх керівників, які працюють у південній філії компанії.

Додайте рядок, де буде кінцевий результат, і вставте формулу в потрібну клітинку. Для цього натисніть на значок функції. З'явиться вікно, в якому потрібно знайти функцію SUMMESLIMN. Далі виберіть його зі списку і відкриється знайоме вікно з аргументами. Але кількість цих аргументів зараз інша. Ця формула дає можливість використовувати нескінченну кількість критеріїв, але мінімальна кількість аргументів - п'ять. 

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

Розглянемо основні аргументи докладніше:

1. Діапазон підсумовування. Комірки для підсумовування.
2. Діапазон умов 1 – діапазон, який буде перевірятися на відповідність певному критерію. 
3. Умова 1 є самою умовою.
4. Діапазон критеріїв 2 – це другий діапазон, який перевірятиметься на відповідність критерію.
5. Умова 2 є другою умовою.

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

Функція COUNTIF в Excel

Якщо вам потрібно визначити, скільки комірок підпадає під певний критерій, використовуйте функцію COUNTIF. Припустимо, нам потрібно зрозуміти, скільки продавців працює в цій організації:

1. Спочатку додайте рядок із кількістю продавців. Після цього потрібно клацнути по комірці, де буде відображатися результат.
2. Після цього потрібно натиснути на кнопку «Вставити функцію», яка знаходиться у вкладці «Формули». З'явиться вікно зі списком категорій. Нам потрібно вибрати пункт «Повний алфавітний список». У списку нас цікавить формула COUNTIF. Після того, як ми виберемо його, нам потрібно натиснути кнопку «ОК».
   

   

3. Після цього ми маємо кількість продавців, зайнятих у цій організації. Його було отримано шляхом підрахунку кількості клітинок, у яких написано слово «продавець». Все просто. 

Функція COUNTSLIM в Excel

Подібно до формули SUMMESLIMN, ця формула підраховує кількість клітинок, які відповідають кільком умовам. Синтаксис схожий, але дещо відрізняється від формули SUMMESLIMN:

1. Діапазон умов 1. Це діапазон, який перевірятиметься за першим критерієм.
2. Умова 1. Безпосередньо перший критерій.
3. Діапазон умов 2. Це діапазон, який перевірятиметься за другим критерієм. 
4. Умова 2.
5. Умови діапазону 3.

І так далі.

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

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

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

Функція в правильних руках IF і його варіації дозволяють перетворити аркуш Excel в повноцінну програму, яка може діяти за складними алгоритмами. Розуміння того, як працює функція IF це перший крок до вивчення макросів – наступний крок у більш гнучкій роботі з електронними таблицями. Але це вже більш професійний рівень.