Сьогодні ми розглянемо функцію ЯКЩО.
Функція ЯКЩО часто використовується в Excel для вирішення багатьох завдань. Знати її дуже корисно. У цій статті ми спробуємо розповісти про її роботу на простих прикладах, достатньо один раз розібратися з конструкцією функцією ЯКЩО і ви зможете застосовувати її і в найскладніших випадках.
Функція ЯКЩОперевіряє, чи виконується умова, та повертає одне значення, якщо воно виконується, та інше значення, якщо ні.
Синтаксис функції ЯКЩОдуже простий:
ЯКЩО (лог_вираз; [ значення_якщо_істина]; [значення_якщо_брехня])
лог_вираз - це будь-яке значення або вираз, який при обчисленні дає значення ІСТИНА або БРЕХНЯ.
Що це означає? Вираз при обчисленні дає значення ІСТИНА якщо цей вираз вірний.
У цій частині необхідно перевірити на відповідність виразу.
Наприклад:
ЯКЩО(А1=10 ; [значення_якщо_істина]; [значення_якщо_брехня]) - якщо А1 дорівнює 10, то вираз А1=10 дасть значення ІСТИНА, а якщо не дорівнює 10, то брехня
Інший приклад
ЯКЩО(А1>30; [значення_якщо_істина]; [значення_якщо_брехня]) - якщо в осередки А1 число більше 30, то А1>30 поверне ІСТИНА, а якщо менше, то БРЕХНЯ
Ще приклад
ЯКЩО(С1=”Так” ; [значення_якщо_істина]; [значення_якщо_брехня]) - якщо в комірки C1 міститься слово “Так” то вираз поверне значення ІСТИНА, а якщо ні, то С1=”Так” поверне БРЕХНЯ
ЯКЩО (лог_вираз; [ значення_якщо_істина]; [значення_якщо_брехня])
значення_якщо_істина, значення_якщо_брехня- Як видно з їх назви, це те, що необхідно зробити в залежності від того, що повернув лог виразу : ІСТИНА і БРЕХНЯ
Приклад використання функції ЯКЩО в Excel
Розглянемо використання функції ЯКЩО на практичному прикладі. Ми маємо таблицю замовлень, яку ми використовували при розгляді роботи . Нам необхідно заповнити стовпець на замовлення Ведер (помилково на картинці вказано «Замовлення столів»), тобто необхідно вибрати тільки замовлення з Відрами. Це можна зробити у різний спосіб, але ми з вами буде використовувати функцію ЯКЩО, щоб показати її роботу на прикладі. (Див. малюнок)
Для вирішення поставленого завдання напишемо формулу з використанням функції ЯКЩО
ЯКЩО(A3="Відро";D3;"-")
Як ви змогли помітити аргументи функції, якщо розділені точкою з комою.
Отже, перший аргумент (лог виразу) A3="Відро" перевіряє, чи міститься в осередку А3 слово "Відро", якщо міститься, то виконується другий аргумент функції ЯКЩО ( значення_якщо_істина), у нашому випадку це D3 (тобто вартість замовлення), якщо в осередок А3 не дорівнює слову «Відро», то виконується третій аргумент функції ЯКЩО ( значення_якщо_брехня), у разі це «-» (тобто буде написано тире).
Таким чином, у осередку E3 з'явиться значення D3, тобто число 240.
Одна з найпопулярніших функцій у програмі Excel – ЯКЩО. Це логічне порівняннязначення та одержуваного результату. Якщо говорити простіше, то функція може звучати так:
ЯКЩО умова істинна, то виконуємо це, а інакше робимо щось ще
Синтаксисцієї функції досить простий:
ЯКЩО (логічне_умова; значення_у_випадку_ІСТИНА; значення у випадку_БРЕХНЯ)
Розберемо докладніше:
- Логічне_умова– значення, яке перевірятимемо
- Значення_у_випадку_ІСТИНА- Дія, коли вимога виконується
- Значення у випадку_БРЕХНЯ- дія, коли Логічне_умова не виконується
Як використовувати функцію
Приклад №1
На скріншотах нижче показаний найпростіший варіант використання. Ми спочатку перевіряємозадана умова А1>25. ЯКЩО це вимога виконується, тоді виводимо в комірку « більше 25», інакше « менше або дорівнює 25».
Приклад №2
Тепер складніше завдання. Нижче ми розглянемо приклад переатестації працівників підприємства. Спочатку таблиця виглядає так:
Нам необхідноу стовпці С вивести результати переатестації, які можуть набувати бінарного значення: ЗДАВабо НЕ ЗДАВ. Критерії у нас будуть такими: хто набрав понад 45 балів, той вважається здавшиміспит, ну а решта немає.
Для виконання завдання необхідно:
![](https://i2.wp.com/composs.ru/wp-content/uploads/2017/06/funkciya_esli_v_excel_i_primery_ispol_zovaniya4.jpg)
Використовуємо декілька умов
При необхідності, функцію ЯКЩОможна, можливо вкластиодин в одного. Отже, ми розширимо варіанти рішень.
Наприклад, візьмемо переатестацію співробітників, яку розглянули раніше. Змінимо критерії результату та виставимо кожному оцінку: Погано, Добре та Відмінно. Відмінноставитимемо, коли бали перевищать 60. ОцінкуДобре можна буде отримати, набравши від 45 до 60 балів. Ну і в решті випадків ставимо Погано.
![](https://i1.wp.com/composs.ru/wp-content/uploads/2017/06/funkciya_esli_v_excel_i_primery_ispol_zovaniya6.jpg)
Як видно з прикладу, замість другого та третього значення функції можна підставляти умову. У такий спосіб додаємо необхідну кількість вкладень. Однак варто відзначитищо після додавання 3-5 вкладень працювати з формулою стане практично неможливо, т.к. вона буде дуже громіздкою.
Інші варіанти використання функції
Знаходимо суму стовпців чи осередків
Наприклад, є необхідність підсумувати кожен другий осередок стовпця. У цьому випадку допоможе використання оператора СУМІСЛІ. Він дозволить працювати лише з тими стовпцями, які підпадають під наші вимоги. Ми розглядатимемо випадок, коли необхідно підсумовувати елементи в непарних рядках.
Для вирішення задачі нам знадобиться вставитидодатковий стовпець і пронумерувати рядки 1 та 0. Саме ці дані ми будемо використовувати у нашій формулі. У прикладі видно, яку формулу використовували.
Синтаксис: СУМІСЛИ(діапазон_осередків; використовується_умова; [діапазон_підсумовування])
- Діапазон- Обов'язковий аргумент. Тут визначається діапазон, який оцінюється на відповідність умові.
- Використана_умова- Тут вказуємо, які саме осередки необхідно підсумовувати.
- Діапазон_підсумовування– вказуємо, який саме діапазон використовуватиме підсумовування.
Кількість повторень елементів на аркуші
У цьому випадку ми можемо порахувати скільки разів заданий елемент зустрічається на аркуші. Для цього використовуємо функцію РАХУНКИ. Вона вважає кількість осередків, які збігаютьсяіз заданим значенням.
У цьому прикладі ми вважаємо, скільки клієнтів у кожному місті. У формулі ми задаємо першим діапазон, а другим значення, які слід шукати.
Використовуємо РАХУНКИ та СУМІСЛИ при підрахунку даних
У прикладі ми спробуємо визначити усереднений обсяг доходувід клієнтів у кожному місті. Для цього поділимо сумарний прибуток міста на кількість клієнтів.
Пошук кількох входжень елемента у списку
Якщо у нас є список даних, що постійно оновлюється, в якому в міру зростання можуть з'являтися дублікати, то пошук входжень у цьому списку може виявитися скрутним. Для вирішення цього завдання найпростіше скористатися функцією ВВРі РАХУНКИ.
Для початку, додамододатковий стовпець, який додає порядковий номер входження до імені клієнта. У результаті перша згадка елемента видаватиме Ім'я1, наступне Ім'я2 і т.д.
Використовуємо пошукпо клієнту Крістіна Агілера. Використовуючи формулу ВПР («Христина Агілера3», діапазон_пошуку, 2, брехня), ми отримаємо третій номер цього клієнта. У формулі, останнім значенням ставимо брехню, оскільки список не сортований, і нам необхідний точний збіг елементів.
Як скоротити кількість вкладень ЯКЩО
Версії до EXCEL 2003 включно підтримували до 7 рівніввкладеності ЯКЩО. Починаючи з версії Excel 2007, це обмеження прибрали. Але варто зауважити, що нижче за 3-4 рівні вкладеності мало хто опускається.
Для того, щоб зменшити кількість використання вкладень ЯКЩО, можна використовувати функцію ВИБІР. Вона працює зі значеннями або діями із заданого списку за номером індексу.
Синтаксис: ВИБІР (номер_індексу; значення_індексу1; значення_індексу2; [значення_індексу3];…)
Наприклад, функція ВИБІР (1; «Третій»; «Другий»; «Перший»), поверне нам слово « Третій», якщо ми її додамо в комірку.
Варто зазначити, що є певні обмеження. Зокрема, як індекс можуть виступати тільки числа.
"розвилка".
Розгалужуєтьсяназивається такий алгоритм, в якому вибирається один із кількох можливих варіантів обчислювального процесу. Кожен подібний шлях називається гілкою алгоритму.
Ознакою алгоритму, що розгалужується, є наявність операцій перевірки умови. Найчастіше для перевірки умови використовується умовний оператор if.
if може використовуватися у формі повної або неповної розвилки.У разі неповного розвилки якщо Умоваістинно, то Блокоперацій1 виконується, якщо Умовахибно, то Блокоперацій1 не виконується.
У разі повного розвилки якщо Умоваістинно, то виконується Блокоперацій1 , інакше виконується Блокоперацій2 .
Блокоперацій може складатися з однієї операції. У цьому випадку наявність фігурних дужок, що обмежують блок, не є обов'язковою.
Приклад на C:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#include
int main()
{
int k; // оголошуємо цілу змінну k
printf("k="); // Виводимо повідомлення
scanf("%d", &k); // вводимо змінну k
if (k >= 5) // якщо k>5
printf("%d >= 5", k); // Виводимо "ЗНАЧЕННЯ> = 5"
else // інакше
printf("%d< 5"
, k); // виводимо "ЗНАЧЕННЯ< 5"
getchar(); getchar();
return 0;
}
Результат виконання
![](https://i0.wp.com/prog-cpp.ru/wp-content/uploads/2017-01-24_22-21-03.png)
Оператор if може бути вкладеним.
Приклад на C:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#define _CRT_SECURE_NO_WARNINGS // для можливості використання scanf
#include
#include
int main() (
int key;
system("chcp 1251");
system("cls"); // Очищаємо вікно консолі
printf();
scanf("%d", &key);
if (key == 1) // якщо key = 1
printf( "\n Вибрано перший пункт"); // Виводимо повідомлення
else if (key == 2) // інакше якщо key = 2
printf( "\n Вибрано другий пункт"); // Виводимо повідомлення
else // інакше
printf(); // Виводимо повідомлення
getchar(); getchar();
return 0;
}
Результат виконання
При використанні вкладеної форми оператора if опція else зв'язується з останнім оператором if. Якщо потрібно зв'язати опцію else з попереднім оператором if , внутрішній умовний оператор полягає у фігурних дужках:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#define _CRT_SECURE_NO_WARNINGS // для можливості використання scanf
#include
#include
int main() (
int key; // оголошуємо цілу змінну key
system("chcp 1251"); // Переходимо в консолі російською мовою
system("cls"); // Очищаємо вікно консолі
printf( "Введіть номер пункту, 1 або 2: ");
scanf("%d", &key); // Вводимо значення змінної key
if (key != 1) ( // якщо key не дорівнює 1
if (key == 2) // якщо key дорівнює 2
printf( "\n Вибрано другий пункт"); // Висновок повідомлення
} // якщо key - не 1 і не 2, то нічого не виводиться
else // інакше, якщо key дорівнює 1
printf( "\n Вибрано перший пункт"); // Висновок повідомлення
getchar(); getchar();
return 0;
}
Результат виконання
![](https://i2.wp.com/prog-cpp.ru/wp-content/uploads/2017-01-24_22-24-55.png)
![](https://i1.wp.com/prog-cpp.ru/wp-content/uploads/2017-01-24_22-25-12.png)
![](https://i2.wp.com/prog-cpp.ru/wp-content/uploads/2017-01-24_22-31-07.png)
Тернарні операції
Тернарна умовна операціямає 3 аргументи і повертає свій другий чи третій операнд залежно від значення логічного виразу, заданого першим операндом. Синтаксис тернарної операції у мові Сі
Умова? Вираз1: Вираз2;
Якщо виконується Умова, то тернарна операція повертає Вираз1, в іншому випадку - Вираз2 .
Тернарні операції, як і умови операції, можуть бути вкладеними. Для поділу вкладених операцій використовуються круглі дужки.
Наведений вище приклад з використанням тернарних операцій можна подати у вигляді
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#define _CRT_SECURE_NO_WARNINGS // для можливості використання scanf
#include
#include
int main() (
int key; // оголошуємо цілу змінну key
system("chcp 1251"); // Переходимо в консолі російською мовою
system("cls"); // Очищаємо вікно консолі
printf( "Введіть номер пункту, 1 або 2: ");
scanf("%d", &key); // Вводимо значення змінної key
key == 1? printf( "\n Вибрано перший пункт") :
(key == 2 ? printf( "\n Вибрано другий пункт") :
printf( "\n Перший і другий пункти не вибрані"));
getchar(); getchar();
return 0;
}
Оператор розгалуження switch (оператор множинного вибору)
Оператор if дозволяє здійснити вибір лише між двома варіантами. Для того, щоб робити вибір одного з декількох варіантів, необхідно використовувати вкладений оператор if . З цією ж метою можна використовувати оператор розгалуження switch.
Загальна форма запису
switch (Цілий Вираз)
{
case Константа1: Блок Операцій1;
break;
case Константа2: Блок Операцій2;
break;
. . .
case Константаn: Блокопераційn;
break;
default: Блок ОпераційЗа замовчуванням;
break;
}
Оператор розгалуження switch виконується таким чином:
- обчислюється ЦілийВираз у дужках оператора switch;
- отримане значення порівнюється з мітками ( Константами ) в опціях case , порівняння проводиться до того часу, доки знайдено мітка, відповідна обчисленому значенню целочисленного виразу;
- виконується Блокоперацій відповідної мітки case;
- якщо відповідну мітку не знайдено, то виконається Блок ОпераційЗа замовчуванням описаний в опції default.
Альтернатива default може бути відсутня, тоді не буде зроблено жодних дій.
Опція break; здійснює вихід з оператора switch та перехід до наступного за ним оператора. За відсутності опції break будуть виконуватися всі оператори, починаючи з позначеного даною міткою і закінчуючи оператором опції default .
Константи в опціях case мають бути цілого типу (можуть бути символами).
Повний синтаксис (натисніть, щоб розкрити)
Умовний оператор "Якщо"
Опис:
Оператор Якщокерує виконанням програми, ґрунтуючись на результаті одного чи більше логічних виразів. Оператор може містити будь-яку кількість груп операторів, очолюваних конструкціями. ІнакшеЯкщо - Тоді.
Синтаксис:
Параметри:
Тернарний умовний оператор
Опис:
Дозволяє обчислити один із двох заданих виразів залежно від результату обчислення логічного виразу.
Синтаксис:
? (< Логическое выражение> , < Выражение 1 > , < Выражение 2 > ) |
Параметри:
Значення, що повертається:
Результат обчислення одного із результуючих виразів.
&НаКлієнті Процедура ВиконатиКод(Команда) /// Як скласти оператор Якщо у 1с 8.3, 8.2Якщо 1> 0 Тоді // Істина // Блок операторівПовідомити( "Комп'ютер виконає всі команди з цього блоку."); Повідомити( "Один більше за нуль."); КінецьЯкщо ; Якщо 1< 0 Тогда // Ложь Сообщить("Один менший за нуль."); Інакше Повідомити( "Спрацює саме ця гілка умовного оператора (#А)."); Повідомити( "Один більше за нуль."); КінецьЯкщо ; Якщо 1< 0 Тогда // Ложь Сообщить("Один менший за нуль."); ІнакшеЯкщо 1 = 0 Тоді // Брехня Повідомити( "Один дорівнює нулю."); Інакше Повідомити( "Спрацює саме ця гілка умовного оператора (#Б)."); Повідомити( "Один більше за нуль."); КінецьЯкщо ; /// Як скласти тернарний оператор? в 1с 8.3, 8.2Текст =? (1 > 2, "Один більше двох.", "Один не більше двох."); Повідомити (Текст) ; // виведе "Один не більше двох."КінецьПроцедури /// Завантажити та виконати ці приклади на комп'ютері |
Поширене питання щодо Excel «Як записувати кілька умов в одній формулі?». Особливо часто застосовується дві і більше умов використання функції ЯКЩО. Зробити кілька умов у формулі ЯКЩО досить просто, головне знати основні принципи. Їх і обговорюємо нижче.
На мою думку, показово розглянути приклад із вирішенням системи умов. Такі завдання часто задають в інститутах, на парах Excel.
Наприклад, є ось така, досить нагромаджена формула:
Розберемо на прикладі, як перенести її до Excel
Зрозуміло, що ця формула складатиметься з трьох частин, як мінімум:
SIN(B1)^2 =COS(B1) =EXP(1/B1)
Але як записати кілька цих функцій одну, ще й за умовою? Щоб розібратися, докладно подивимося на функцію ЯКЩО.
Її склад наступний:
ЯКЩО (Умова; якщо умова = ТАК (ІСТИНА); якщо умова = НІ (БРЕХНЯ))
Тобто. якщо ми запишемо просту формулу, що ми отримаємо у результаті в осередку B2?
Правильно — відобразитися 100. Якщо ж А1 буде стояти будь-яке інше значення крім 1, то B2 відобразиться б 0.
Повернімося до нашої системи умов. Тепер нам треба розуміти як записати одразу дві умови до першої точки з комою. У нас у B1 порожньо, а значить = 0, і тільки при виконанні обох умов А1 = 1 і B1 = 0 (знак *) значення формули дорівнюватиме 100.
Особливо розберемо між дужками
Оператор І він * означає, що має виконуватися обидві умови одночасно, А1=1 і B1=0.
Якщо між дужками поставити + (або), достатньо буде однієї з умов. Наприклад, якщо А1=1, то вже буде відображатися 100.
Ми готові до написання формули, це робитимемо частинами
Запишемо першу умову
ЯКЩО((B1>-2)*(B1<9);SIN(B1)^2);
Якщо умова виконується, то виконується перша формула із синусом
Якщо ні, друга умова
ЯКЩО((B1>-2)*(B1<9);SIN(B1)^2;ЯКЩО((B1>=9)*(B1<=19);COS(B1)
У всіх інших випадках буде виконуватися формула =EXP(1/B1)
Разом виходить:
ЯКЩО((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9) * (B1<=19);COS(B1);EXP(1/B1)))
Запис кількох формул в одній
Якщо в осередку B1 буде текст, формула видасть помилку. Тому я часто застосовую формулу.
Уявімо, що вся наша формула з попереднього пункту це один умовний аргумент А
Тоді = ПОЛИПОМИЛКА (А; »»)
Або для нашого прикладу
ЯСЛИПОМИЛКА(ЯКЩО((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9) * (B1<=19);COS(B1);EXP(1/B1)));"")
Приклад можна завантажити