Логічні висловлювання використовуються для запису умов, у яких порівнюються числа, функції, формули, текстові чи логічні значення. Будь-який логічний вираз має містити, принаймні, один оператор порівняння, який визначає відношення між елементами логічного виразу. Нижче наведено список операторів порівняння Excel
>= Більше чи одно
Результатом логічного виразу є логічне значення ІСТИНА (1) або логічне значення брехня (0).
Функція ЯКЩО
Функція ЯКЩО (IF) має наступний синтаксис:
=ЯКЩО(логічне_вираження;значення_якщо_істина;значення_якщо_брехня)
Наступна формула повертає значення 10, якщо значення в осередку А1 більше 3, а в іншому випадку - 20:
ЯКЩО (А1> 3; 10; 20)
Як аргументи функції ЯКЩО можна використовувати інші функції. У функції ЯКЩО можна використовувати текстові аргументи. Наприклад:
ЯКЩО (А1> = 4; "Залік здав"; "Залік не здав")
Можна використовувати текстові аргументи у функції ЯКЩО, щоб у разі невиконання умови вона повертала порожній рядок замість 0.
Наприклад:
ЯКЩО(СУМ(А1:А3)=30;А10;"")
Аргумент логічний_вираз функції ЯКЩО може містити текстове значення. Наприклад:
ЯКЩО (А1 = "Динамо"; 10; 290)
Ця формула повертає значення 10, якщо осередок А1 містить рядок "Динамо", і 290, якщо в ньому є будь-яке інше значення. Збіг між порівнюваними текстовими значеннями має бути точним, але не враховуючи регістру.
Функції І, АБО, НЕ
Функції І (AND), АБО (OR), НЕ (NOT) - дозволяють створювати складні логічні вирази. Ці функції працюють у поєднанні із простими операторами порівняння. Функції І та АБО можуть мати до 30 логічних аргументів і мають синтаксис:
=І(логічне_значення1;логічне_значення2...)
=АБО(логічне_значення1;логічне_значення2...)
Функція НЕ має лише один аргумент та наступний синтаксис:
=НЕ(логічне_значення)
Аргументи функцій І, АБО, НЕ можуть бути логічними виразами, масивами або посиланнями на комірки, що містять логічні значення.
Наведемо приклад. Нехай Excel повертає текст "Пройшов", якщо учень має середній бал більше 4 (осередок А2), і пропуск занять менше 3 (осередок А3). Формула набуде вигляду:
=ЯКІ(І(А2>4;А3
Не дивлячись те що, що функція АБО має самі аргументи, як і І, результати виходять зовсім різними. Так, якщо в попередній формулі замінити функцію І на АБО, то учень проходитиме, якщо виконується хоча б одна з умов (середній бал більше 4 або пропуски занять менше 3). Таким чином, функція АБО повертає логічне значення ІСТИНА, якщо хоча б один із логічних виразів істинно, а функція І повертає логічне значення ІСТИНА, тільки якщо всі логічні вирази істинні.
Функція НЕ змінює значення свого аргументу протилежне логічне значення і зазвичай використовують у поєднані із іншими функціями. Ця функція повертає логічне значення ІСТИНА, якщо аргумент має значення Брехня, і логічне значення Брехня, якщо аргумент має значення ІСТИНА.
Вкладені функції ЯКЩО
Іноді буває дуже важко вирішити логічне завдання лише за допомогою операторів порівняння та функцій І, АБО, НЕ. У цих випадках можна використовувати вкладені функції ЯКЩО. Наприклад, у наступній формулі використовуються три функції ЯКЩО:
=ЯКІ(А1=100;"Завжди";ЯКІ(І(А1>=80;А1 =60;А1)
Якщо значення в комірці А1 є цілим числом, формула читається так: "Якщо значення в комірці А1 дорівнює 100, повернути рядок "Завжди". В іншому випадку, якщо значення в комірці А1 знаходиться між 80 і 100, повернути "Зазвичай". в іншому випадку, якщо значення в клітинці А1 знаходиться між 60 і 80, повернути рядок "Іноді." І, якщо жодна з цих умов не виконується, повернути рядок "Ніколи".
Функції ІСТИНА і БРЕХНЯ
Функції ІСТИНА (TRUE) і БРЕХНЯ (FALSE) надають альтернативний спосіб запису логічних значень ІСТИНА та БРЕХНЯ. Ці функції не мають аргументів і виглядають так:
=ІСТИНА()
=БРЕХНЯ()
Наприклад, осередок А1 містить логічний вираз. Тоді наступна функція повернути значення "Проходьте", якщо вираз у комірці А1 має значення ІСТИНА:
ЯКЩО(А1=ІСТИНА();"Проходьте";"Стоп")
В іншому випадку формула поверне "Стоп".
Функція ЄПУСТО
Якщо потрібно визначити, чи осередок порожній, можна використовувати функцію ЕПУСТО (ISBLANK), яка має наступний синтаксис:
= ЄПУСТО (значення)
Програма Excel, безумовно, має дуже багатий функціонал. І серед багатьох різноманітних інструментів особливе місце займає оператор «ЯКЩО». Він допомагає у вирішенні абсолютно різних завдань, і до цієї функції користувачі звертаються набагато частіше, ніж до інших.
У цій статті ми поговоримо про те, що таке оператор «ЯКЩО», а також розглянемо сферу застосування та принципи роботи з ним.
Визначення функції “ЯКЩО” та її призначення
Оператор «ЯКЩО» – це інструмент програми Ексель для перевірки певної умови (логічного виразу) виконання.
Тобто уявімо, що у нас є якась умова. Завдання “ЯКЩО” – перевірити дотримання цієї умови та вивести в комірку з функцією значення, засноване на результаті перевірки.
- Якщо логічний вираз (умова) виконується, то значення відповідає істині.
- Якщо логічний вираз (умова) не виконується, значення є хибним.
Сама формула функції у програмі є наступним виразом:
=ЯКІ(умова; [значення якщо умова виконано]; [значення якщо умова не виконано])
Використання функції “ЯКЩО” на прикладі
Ймовірно, вищенаведена інформація може здатися не такою зрозумілою. Але, насправді, нічого складного тут немає. І для того, щоб краще зрозуміти призначення функції та її роботу, розглянемо приклад нижче.
Ми маємо таблицю з найменуваннями спортивного взуття. Уявимо, що скоро у нас буде розпродаж, і всьому жіночому взуттю потрібно проставити знижку 25%. В одному зі стовпців у таблиці якраз прописана підлога для кожного найменування.
Наше завдання – у стовпці “Знижка” вивести значення «25%» для всіх рядків із найменуваннями для жіночої статі. І відповідно, значення «0», якщо в стовпці «Підлога» стоїть значення «чоловічий»
Заповнення даних вручну займе чимало часу, і є висока частка ймовірності десь помилитися, особливо якщо список довгий. Набагато простіше в цьому випадку автоматизуватиме процес за допомогою оператора «ЯКЩО».
Для виконання цього завдання потрібно скласти таку формулу:
=ЯКЩО(B2=”жіночий”;25%;0)
- Логічне вираз: B2=”жіночий”
- Значення у разі, умова виконується (істина) – 25%
- Значення, якщо умова не виконується (брехня) – 0.
Цю формулу пишемо у верхньому осередку стовпця “Знижка” і натискаємо Enter. Не забуваймо ставити знак рівно (=) перед формулою.
Після цього для даного осередку буде відображено результат згідно з нашою логічною умовою (забуваємо налаштувати формат осередків – відсотковий). Якщо перевірка виявить, що стать «жіноча» – буде виведено значення 25%. Інакше значення комірки буде 0. Власне кажучи, що нам і потрібно.
Тепер залишилося тільки скопіювати цей вираз на всі рядки. Для цього підведіть курсор миші до правого нижнього краю комірки з формулою. Покажчик мишки повинен перетворитися на хрестик. Затисніть ліву кнопку миші та розтягніть формулу на всі рядки, які потрібно перевірити згідно з заданими умовами.
Ось і все, тепер ми застосували умову всіх рядків і отримали результат для кожної з них.
Ми тільки-но розглянули приклад використання оператора «ЯКЩО» з одним логічним виразом. Але в програмі також можна задавати більше однієї умови. При цьому спочатку проводитиметься перевірка по першому, і у разі його успішного виконання відразу відобразиться задане значення. І тільки якщо не буде виконано перше логічне вираження, набуде чинності перевірка по другому.
Розглянемо наочно з прикладу тієї ж таблиці. Але цього разу ускладнимо завдання. Тепер потрібно проставити знижку на жіноче взуття, залежно від виду спорту.
Перші умови – це перевірка статі. Якщо "чоловічий" - відразу виводиться значення 0. Якщо це "жіночий", то починається перевірка за другою умовою. Якщо вид спорту біг – 20%, якщо теніс – 10%.
Пропишемо формулу для цих умов у потрібному нам осередку.
=ЯКЩО(B2=”чоловічий”;0; ЯКЩО(C2=”біг”;20%;10%))
Клацаємо Enter та отримуємо результат згідно з заданими умовами.
Також у Ексель існує можливість вивести дані щодо одночасного виконання двох умов. При цьому значення вважатиметься хибним, якщо хоча б одна з умов не виконана. Для цього завдання застосовується оператор «І».
Розглянемо з прикладу нашої таблиці. Тепер знижка 30% буде проставлена тільки якщо це жіноче взуття і призначена для бігу. За дотримання цих умов одночасно значення комірки дорівнюватиме 30%, інакше – 0.
Для цього використовуємо таку формулу:
=ЯКЩО(І(B2=”жіночий”;С2=”біг”);30%;0)
Натискаємо клавішу Enter, щоб відобразити результат у комірці.
Аналогічно прикладам вище, розтягуємо формулу інші рядки.
І тут значення логічного висловлювання вважається істинним і під час однієї з умов. Друга умова у своїй може виконуватися.
Поставимо завдання в такий спосіб. Знижка 35% надається лише на чоловіче взуття для тенісу. Якщо це чоловіче взуття для бігу або будь-яке жіноче взуття, знижка дорівнює 0.
У разі потрібна така формула:
=ЯКЩО(АБО(B2=”жіночий”; C2=”біг”);0;35%)
Після натискання Enter ми отримаємо потрібне значення.
Розтягуємо формулу вниз та знижки для всього асортименту готові.
Використовувати функцію ЯКЩО можна не тільки прописуючи її вручну в комірці або рядку формул, але також і через Побудовник формул.
Розберемо, як це працює. Припустимо, нам знову, як і в першому прикладі, потрібно проставити знижку на все жіноче взуття в розмірі 25%.
Висновок
Одним з найпопулярніших і найкорисніших інструментів в Excel є функція ЯКЩОяка перевіряє дані на збіг заданих нами умов і видає результат в автоматичному режимі, що виключає можливість помилок через людський фактор. Тому, знання та вміння застосовувати цей інструмент дозволить заощадити час не тільки на виконання багатьох завдань, а й на пошуки можливих помилок через “ручний” режим роботи.
Основу будь-якого логічного виразу становлять дві величини: істина (TRUE) та брехня (FALSE). З допомогою логічних виразів будуються численні дерева рішень. Найпростіше – питання з відповіддю «ТАК» чи «НІ». У разі «ТАК» виконується одна дія, у разі «НІ» – інша. Для реалізації умов існує програмний оператор IF (ЯКЩО).
У програмі EXCEL є функція IF, яка дозволяє зробити найнеймовірніші логічні конструкції, вкладаючи оператори IF один в одного. У старих версіях програми вкладеність була обмежена 7 рівнями, якщо EXCEL 2010 не має обмежень, а у версії 2016 з'явилася нова функція ЕСЛИМН, що враховує множинність вкладень.
Познайомимося із багатоликою функцією IF.
Функція IF
ЯКЩО – стандартна функція програми EXCEL, яка виконує перевірку конкретної умови. Умова є питанням, що має дві відповіді: істина і брехня.
Умова записується у вигляді логічного виразу, що складається з правої та лівої частини, пов'язаного логічними операторами порівняння: більше (>), менше (<), равно (=), неравно (<>), а також >= і<=. Результат сравнения показывает, выполняется или нет это условие. В случае выполнения условия отрабатывает алгоритм ИСТИНА, в противном случае – ЛОЖЬ.
Синтаксис функції виглядає так:
ЯКЩО (логічне_вираз; значення_якщо_істина; значення_якщо_брехня).
Функції ЯКЩО в EXCEL, приклади
Простим прикладом реалізації функції IF може бути необхідність присвоєння якісної ознаки ряду значень. Наприклад, необхідно виділити у списковому кадровому складі підприємства всіх працівників молодше 35 років. Для цього достатньо в новий стовпець навпроти кожного прізвища підставити формулу виду: =ЯКЩО(осередок_з_віком<35; 1;0). Таким образом, всем молодым людям будет присвоен качественный признак =1, который даст возможность работать только с нужной частью списка.
Цікавим є варіант роботи з формулою IF, коли використовується не цифрові дані, а текст.
Текст у формулі слід виділяти лапками для коректного відпрацювання функції.
Усім інженерам 2 категорії премія складає 30%, решті працівників – 50%. Для реалізації цієї умови вставляємо формулу: =ЯКЩО(осередок_професія=“інженер 2 категорії”; “премія 30%”; “премія 50%”). Усі інженери 2 категорії промаркуються даними за премією 30%, решті працівників буде введено значення «премія 50%».
Коли умови складніші, використовують комбінації вкладених функцій.
Застосування вкладених IF
На підприємстві під час табеля обліку робочого дня виникає потреба поставити місячну норму часу для розрахунку зарплати. У різних фахівців ця величина відрізняється. За допомогою функції IF можна легко вирішити це завдання.
Нехай у денного персоналу місячна норма дорівнює 162 години, у змінного при 8-годинному робочому дні вона становитиме 148 годин, а при робочому дні 7,2 години норма дорівнюватиме 136 годин.
Тоді потрібно перевірити денну норму працівника (8 або 7,2 години) та категорію працюючого (зміна чи день). Реалізувати таку перевірку можна у різний спосіб.
- Варіант 1
Коли як перша перевірка беремо денну норму і другу перевірку робимо по істині, то формула буде така:
ЯКЩО (комірка_денна норма = 8; ЯКЩО (комірка_категорія = "зміна"; 148; 162); 136)
- Варіант 2
Першу перевірку залишаємо колишньою, другу перевіряємо по брехні, то вираз виглядатиме так:
ЯКЩО (комірка_денна норма = 7.2; 136; ЯКЩО (комірка_категорія = "зміна"; 148; 162))
- Варіант 3
Коли першу перевірку робимо за категорією, то маємо: =ЯКЩО(комірка_категорія=“зміна”;ЯКЩО(комірка_деннанорма =8;148;136);162)
Результат завжди буде однаковий.
Припустимо, що є ще зміна по 12 годин з нормою 144 години. Тоді реалізації такого алгоритму знадобиться ще одне вкладення функції IF. Це вже третій рівень.
Наступний вираз дає рішення і в цьому випадку:
Освоївши принцип перевірок, можна зробити складні деревоподібні структури. Хоча практично більше 4 рівнів вкладеності мало застосовується, оскільки складніші комбінації можна реалізувати простіше через інші функції програми, які працюють із вибором даних із масивів за критеріями.
Одночасне застосування двох умов
Багатогранність функції IFвиявляється у тому, що її можна застосовувати одночасно з іншими функціями. EXCEL має ряд корисних вбудованих конструкцій, що використовують метод IF. Це такі формули, як СУМІСЛИ, РАХУНКИ, РОЗМІТНИКИ. З їхньою допомогою легко виконувати розрахунки за конкретною умовою. Але й саму конструкцію IF можна застосувати одночасно з іншими функціями. Розглянемо її одночасне використання з оператором І та АБО.
Функція IF з формулою І
Якщо потрібно, щоб було виконано одночасно кілька умов, то допоможе конструкція І, що має синтаксис:
І (лог_вираж1; лог_вираж2; лог_вираж3; ...)
Вона перевіряє істинність кожної з логічних структур і повертає ІСТИНУ тільки за істинності всіх виразів, що перевіряються.
Де це можна використати? Наприклад, необхідно виділити із загального списку працівників лише слюсарів 6 розряду, які мають стаж роботи більше 10 років, то допоможе конструкція виду:
ЯКЩО(І(осередок_професія=”слюсар”;осередок_розряд=6;осередок_стаж>=10); 1;0)
Ця формула виділить у загальному списку необхідну категорію працівників за встановленою якісною ознакою =1.
Функція IF з формулою АБО
Варіант використання функції АБО з IFможе бути наступним. Керівництво підприємства протягом року поквартально заохочувало працівників. Необхідно обрати всіх, хто отримував поквартальні премії понад 5000 грн.
Конструкція АБО має синтаксис аналогічний формулі І. Але ІСТИНА повертається за умови істинності хоча б одного логічного виразу.
У нашому прикладі потрібна формула:
ЯКЩО (АБО (комірка_премія1квартал>=5000; комірка_премія2квартал>=5000; комірка_премія3квартал>=5000; комірка_премія4квартал>=5000);1;0)
Наступна фільтрація даних за ознакою 1 дасть зробити необхідний перелік робітників.
Отже, очевидна корисність функції IF. У нових версіях програми з'являються покращені формули, що поєднують найбільш затребувані старі прийоми та дії, що дає необмежені можливості для вивчення та застосування на практиці програми ексель.
" була розглянута робота з вбудованою функцією ЯКЩО(). Все б добре, але виникають такі ситуації, і досить часто, коли вкладеністю умови в умову не обійтися. Наприклад, елементарно необхідно перевірити наступне: 1 більше або одно x і x менше або 5 Реалізувати таку умову кількома якщо досить громіздко і проблематично, а в деяких ситуаціях - взагалі неможливо.Для розширення функціоналу якщо і полегшення складання формул з умовою, в Excel є ще пара корисних функції - І () і АБО ().
Функція І()
Функція І() Умова 1 І Умова 2. При цьому всі умови мають бути дійсними. Результатом роботи даної функції є ІСТИНА або БРЕХНЯ (TRUE/FALSE). Приклад: ЯКЩО а = b І а = з ТОДІ значення 1 Інакше значення 2.
Як видно з прикладу, значення 1 буде лише у тому випадку, якщо всі умови вірні.
Функція АБО()
Функція АБО()використовується тоді, коли необхідно перевірити кілька умов у такий спосіб - Умова 1 АБО Умова 2. Результат функції буде істинним, якщо хоча б одна з умов є істинною. Приклад: ЯКЩО а = b АБО а=с ТОДІ значення 1 Інакше значення 2.
Синтаксис функцій І() та АБО() однаковий: Функція(Умова 1; Умова 2; Умова 3 і до 30 умов). Результат ІСТИНА або БРЕХНЯ.
Приклади використання функції І та АБО
Все сказане вище складно до розуміння і відноситься до розділу Мат. логіки та дискретної математики. Спробую це все викласти зрозумілою мовою. Розберемо кілька прикладів. Скажу відразу, всі приклади будуть з використанням функції ЯКЩО.
приклад 1.
Стовпець А, починаючи з першого рядка, містить 56, 55, 88, 6, 74. Стовпець В - 52, 55, 88, 4, 25. Стовпець С - 53, 55, 88, 6, 25. За допомогою функцій ЯКЩО і І необхідно визначити рядки, значення яких дорівнюють наступним чином А=В і В=С.
Переходимо в комірку D1 і за допомогою майстра функцій вводимо наступне
Формула виглядатиме так: “ =ЯКЩО(І(A1=B1;B1=C1);"Всі значення рівні";"Значення не рівні") “
Результат праці:
приклад 2.
Тепер приклад на функцію АБО. Ті ж стовпці, ті ж значення, умова така: за допомогою функцій ЯКЩО та АБО необхідно визначити рядки, в яких є збігаються значення А = В або В = С або А = С.
Майстер функцій виглядає так:
Формула: “ =ЯКЩО(АБО(A1=B1;B1=C1;A1=C1);"Є рівні значення";"Немає рівних значень")”
Результат:
От і все. Функції не складні. Застосовуйте їх там, де зустрічаються умови та вимовляється І та АБО. Також усіма вище перерахованими функціями можна порівнювати текстові значення.
І насамкінець розглянемо ще один приклад з функцією І та АБО з реального життя.
приклад 3.
У таблицю занесено відсоток виконання завдань, необхідно відсотки перевести до п'ятибальної системи наступним чином:
1 – від 0% до 10%
2 - від 11% до 30%
3 - від 31% до 40%
4 - від 41% до 75%
5-від 76% до 100%
Таблиця такого виду.
Функція ЯКЩО в Excel є однією з найбільш часто зустрічаються і часто використовуються. Робота економіста в Excel неможлива без знання цієї функції.
Функція ЯКЩО ставитися до логічних функцій MS Excel, і дозволяє створювати дуже вигадливі конструкції, ґрунтуючись на перевірці умови та виборі з двох і більше альтернатив.
З використанням функції ЯКЩО в Excel можна будувати алгоритми, що гілкуються, будувати дерево рішень та інші системи і формули використовуючи вкладення однієї функції ЯКЩО в іншу і т.д. В Excel 2010 можна використати до 64 вкладень. Це дозволяє побудувати грандіозний алгоритм обчислень.
У загальному вигляді функція ЯКЩО в Excel має наступний синтаксис:
ЯКЩО (лог_вираз; значення_якщо_істина; значення_якщо_брехня)
Функція повертає одне з двох значень залежно від того, яке значення приймає умова ( лог_виріювання). Якщо умова набуває значення ІСТИНА, то функція ЯКЩО повертає значення_якщо_істина, а якщо умова набуває значення брехня, то повертається значення_їли_брехня.
Розглянемо синтаксис цієї функції з прикладу алгоритму обчислення суми з ПДВ або без ПДВ.
Лог_вираз– це може бути будь-яке значення або вираз (формула), який набуває значення ІСТИНА або БРЕХНЯ. Приміром, С5=“з ПДВ” – це логічний вираз. Якщо в комірці С5 знаходиться текст «з ПДВ», то логічний вираз набуває значення ІСТИНА, інакше – БРЕХНЯ, наприклад, якщо там міститься текст «без ПДВ».
Значення_якщо_істина- значення або вираз (формула), яке повертається, якщо аргумент «лог_вираз» має значення ІСТИНА. Так, наприклад, якщо цей аргумент дорівнює формулі С12 * 1,18, то при значенні логічного виразу рівному ІСТИНА, обчислення ведеться за цією формулою.
Якщо аргумент "лог_вираз" має значення ІСТИНА, а аргумент "значення_якщо_істина" не заданий, повертається значення 0 (нуль).
Значення_якщо_брехня- значення або вираз (формула), яке повертається, якщо аргумент «лог_вираз» має значення брехня. Так, наприклад, якщо цей аргумент дорівнює формулі С12*1 або просто С12, то при значенні логічного виразу рівному брехня, обчислення ведеться за цією формулою, тобто, як у нашому прикладі, береться просто значення суми з комірки С12
Якщо аргумент «лог_вираз» має значення Брехня, а аргумент «значення_якщо_брехня» опущений (тобто після аргументу «значення_якщо_істина» відсутня крапка з комою), то повертається логічне значення Брехня. Якщо аргумент «лог_вираз» має значення БРЕХНЯ, а аргумент «значення_якщо_брехня» порожній (тобто після аргументу «значення_якщо_істина» стоїть крапка з комою, а за нею - дужка, що закриває), то повертається значення 0 (нуль).
Розглянемо економіста, що найчастіше зустрічаються в роботі, і при фінансовому моделюванні ситуації, в якому нам допоможе використання функції ЯКЩО.
Використання текстових значень.
Відразу хочу звернути вашу увагу, що при використанні текстових виразів у формулах необхідно укладати їх у лапки “текст” для коректної роботи формул.
Наприклад, нам треба додати якусь текстову ознаку або коментар, залежно від значення того чи іншого осередку.
Створюємо формулу з використанням функції ЯКЩО:
ЯКЩО (Р20> 1000; "перевищення ліміту"; "в рамках ліміту")
Скопіювавши цю формулу по всьому діапазону, що перевіряється, ми можемо тепер легко відфільтрувати значення перевищують ліміт, за створеним нами критерієм.
Приховування значень. Приховування нульових значеньз функцією ЯКЩО.
Ситуація, що практично повсюдно зустрічається – це виникнення помилки #ДЕЛ/0! під час підготовки вихідних форматів. Ми готуємо вихідний формат, в якому в одному зі стовпців обчислюємо темп зростання одного показника до іншого. Як відомо, для цього використовується формула А/В*100%, але оскільки у нас ще немає даних, виникає помилка при розподілі значень зі стовпця А на нульові значення зі стовпця В.
Щоб цього уникнути, використовуємо як один з варіантів функцію ЯКЩО.
ЯКЩО(В10=0;0;А10/В10) або =ЯКЩО(В10=0;””;А10/В10)
У першому випадку замість помилки буде #ДЕЛ/0! буде повернено значення 0, а у другому випадку буде просто порожній осередок. Подвійні лапки повертають порожнє значення в комірку.
Інший приклад – перевірка збіжності балансу. І тут, треба порівняти суму активу і суму пасиву балансу, і якщо вони рівні, тобто. наприклад, С85-С160 = 0, нам треба приховати нульове значення, а якщо є розбіжність - відобразити це значення.
ЯКЩО(С85-С160=0;””; С85-С160)
Багаторівневі, вкладені обчислення з функцією ЯКЩО.
Найчастіше у фінансовому моделюванні зустрічається ситуація, коли необхідно побудувати алгоритм обчислення кінцевого значення залежно від кількох різних значень вихідного логічного висловлювання.
Знову нам не обійтися без функції ЯКЩО. Причому тут ми вже вкладатимемо її одну в іншу, і створюватимемо розгалужений алгоритм.
Типовий приклад, розрахунок знижки в залежності від кількох рівнів обсягу продажу. Або заробітку, залежно від кількох рівнів виробітку.
Припустимо, у нас наступна матриця знижок:
До 100 000 рублів - 0%
Від 100 001 до 300 000 рублів - 3%
Від 300 001 до 500 000 рублів - 5%
Понад 500 001 – 7%
Обмовлюся відразу, розміри знижок – умовний приклад. Їх розрахунок залежить від співвідношення змінних витрат і виручки. І основна мета – збільшити загальну маржу щодо реалізованої продукції.
Створюємо формулу використовуючи функцію ЯКЩО:
Припустимо, в осередку D10 у нас знаходиться обсяг продажів.
ЯКЩО(D10>=500001;D10*0,93;ЯКЩО(D10>=300001;D10*0,95;ЯКЩО(D10>=100001;D10*0,97;D10))))
Ця формула в залежності від рівня обсягу продажу встановлює той чи інший рівень знижок. Кількість рівнів вкладення і відповідно рівнів знижок може бути набагато більшою.
Оператори порівняння під час використанняфункції ЯКЩО.
У логічних виразах для порівняння значень та формул можна використовувати наступні оператори порівняння:
> Більше
< Меньше
>= Більше чи одно
<= Меньше или равно
<>Не дорівнює
Використання операторів порівняння дозволяє гнучкіше використовувати функцію ЯКЩО в Excel для вирішення прикладних економічних завдань.
У прикладі №3 ми використовували оператор >= більше чи однаково завдання діапазону посилань.
На додаток до функції ЯКЩО в excel можна використовувати інші логічні функції, ще більш розширюючи можливості побудови складних алгоритмів та підвищуючи гнучкість обчислень.
Функції І, АБО, НЕ
Функція І в Excel має наступний синтаксис:
І(логічне_значення1, [логічне_значення2], …)
Функція повертає значення ІСТИНА, якщо в результаті обчислення всіх аргументів виходить значення ІСТИНА; повертає значення Брехня, якщо в результаті обчислення хоча б одного з аргументів виходить значення Брехня.
Функція АБО Excel має аналогічний синтаксис:
АБО (логічне_значення1, [логічне_значення2], ...)
Однак повертає значення ІСТИНА, якщо хоча б один із аргументів має значення ІСТИНА. Якщо всі аргументи мають значення брехня, то повертається значення брехня.
Функція НЕ в excel має наступний синтаксис:
НЕ(логічне_значення)
Функція НЕ використовується в тих випадках, коли необхідно переконатися, що значення не дорівнює певній конкретній величині. Вона повертає значення брехня, якщо значення дорівнює ІСТИНА і навпаки.
Використання кількох умовз функцією ЯКЩО
Припустимо, нам треба зробити відбір наших дебіторів, які заборгували нам за 3 місяці велику суму 100 000 рублів. Припустимо, у разі формула має виводити критерій «злісний задолжник».
У осередку В10 у нас буде термін заборгованості в місяцях, а в осередку С10 сума заборгованості.
Тоді формула матиме такий вигляд:
ЯКЩО(І(В10>=3;С10>100000);»злісний заборговник»;»»)
Якщо ми вважаємо критичним або заборгованість старше 3 місяців, або понад 100 000 рублів, формулу можна записати так:
ЯКЩО (АБО (В10> = 3; С10> 100000); «критична заборгованість»;»»)
Загалом, як ви самі розумієте, можливості застосування функції ЯКЩО в excel безмежні.
Найголовніше – це продумати алгоритм обчислень, а інструментарію в MS Excel для його реалізації цілком достатньо.
Microsoft Excel містить додаткові функції, які можна використовувати для аналізу даних з використанням умов. Наприклад, для підрахунку числа входжень текстового рядка або числа в діапазоні осередків можна використовувати функції РАХУНКИ та РАХУНКОВІ. Для обчислення суми значень, що потрапляють в інтервал, заданий текстовим рядком або числами, можна використовувати функції СУММАЄСЛІ та СУМІСЛИМН.