Заняття 7 з 12
Заняття 7 Використання більш складних функцій і формул
Прийоми використання складних комбінацій функцій і формул.
Презентація
Використання логічних функцій у формулах і функцій для підведення підсумків з умовами. Побудова формул для пошуку й отримання даних з діапазонів аркуша Excel.
Навички MOS 77-420
| Назва навички | Номер навички |
|---|---|
| Демонстрація вміння використовувати функцію СУММЕСЛИ | 4.3.1 |
| Демонстрація вміння використовувати функцію СРЗНАЧЕСЛИ | 4.3.2 |
| Демонстрація вміння використовувати функцію СЧЁТЕСЛИ | 4.3.3 |
Теорія:
1. Використання логічних функцій у формулах
Відеоверсія
Текстова версія
Під час проведення розрахунків нерідко виникають випадки, коли необхідність виконання тієї чи іншої операції залежить від певної умови. Наприклад, нарахувати знижку на покупку тільки якщо загальна вартість перевищує певний поріг, скажімо, $100; нарахувати подвійну ставку заробітної плати, якщо працівник працює у вихідний; перевірити наявність військового квитка, якщо претендент чоловічої статі й призовного віку тощо.
Функція ЕСЛИ (IF)
Найчастіше використовуваною логічною функцією в Excel є функція ЕСЛИ. Ця функція перевіряє умову і, якщо вона виконується, повертає один результат, якщо умова хибна — повертається другий результат.

Порівнюється факт продажів з планом і, якщо факт дорівнює або перевищує план, у стовпчику "Чи досягнута мета" ставиться позначка "Так".
У формулах Excel функції можна використовувати як аргументи інших функцій — у цьому випадку це будуть вкладені функції, логічна ЕСЛИ — не виняток. Для розрахунку стовпців "Чи досягнута мета" і "Персональний бонус" вкладені функції не потрібні, бонус нараховується лише у випадку досягнення мети. А от якщо потрібно перевіряти належність певних значень діапазону — без вкладеності не обійтися.
Розглянемо такий приклад, на якому легко зрозуміти не лише принцип роботи вкладених функцій, а й порядок обчислень у формулах Excel.
Приклад. Є група дівчат, у яких відомий зріст, також відомий діапазон норми росту (150–165 см). Необхідно проставити навпроти кожної дівчини позначки: «нижче норми», якщо зріст менший за 150; «норма», якщо зріст знаходиться в діапазоні від 150 до 165; і «вище норми», якщо зріст перевищує 165 сантиметрів.

Формула дуже проста — знадобилося одне вкладення другого рівня. Це не єдиний варіант розв'язання задачі, наприклад, можна використати таку формулу:
=ЕСЛИ(B3<$E$2;"меньше нормы";ЕСЛИ(B3>$F$2;"больше нормы";"норма"))

Також чудово працюватиме й така формула:
=ЕСЛИ(B4>$F$2;"больше нормы";ЕСЛИ(B4<$E$2;"меньше нормы";"норма"))
Пізніше, при розгляді логічної функції И, ми повернемося до цього прикладу, а зараз кілька слів про принцип роботи обчислень у логічних формулах Excel.
Порівняння відбувається по черзі до першого відповідного випадку, а потім переривається — це важливо розуміти. Наприклад, якщо записати формулу так:
=ЕСЛИ(B2<$F$2;"норма";ЕСЛИ(B2<$E$2;"меньше нормы";"больше нормы"))
то обчислення працюватимуть некоректно, оскільки перша перевірка фактично приховує другу: всі значення, які менші за 165, автоматично будуть меншими за 150.

Функція ЕСЛИМН (УСЛОВИЯ) (IFS) — лише починаючи з Excel 2016
У новіших версіях Excel Microsoft додала кілька логічних функцій, що зменшують громіздкість вкладених ЕСЛИ у формулах. Однією з таких функцій є нова функція ЕСЛИМН (раніше мала ім'я УСЛОВИЯ), яка доповнює ЕСЛИ, коли потрібно зробити кілька вкладень:
На момент запису цього заняття функція ЕСЛИМН ще мала ім'я УСЛОВИЯ. Microsoft не зберегла цю назву, тому в оновленій версії Excel при спробі ввести УСЛОВИЯ буде помилка, тоді як функції, введені раніше, автоматично перейменувалися на ЕСЛИМН. В англійській версії Excel назва IFS не змінювалася.
=ЕСЛИМН(B2<$F$2;"меньше нормы";B2<$G$2;"норма";ИСТИНА;"больше нормы")

Якщо вкладень дуже багато (а 10–20 вкладень ЕСЛИ — не рідкість), то розбиратися в новій функції значно простіше.
У функції УСЛОВИЯ немає значення за замовчуванням, однак наприкінці обчислень це можна задати, використавши константу «ИСТИНА».
Функція ПЕРЕКЛЮЧ (SWITCH)
Ще одна функція, що допомагає зменшити нагромадження ЕСЛИ, з'явилася в Excel 2016. Дуже часто в логічних формулах порівнюють не більшість/меншість, а перевіряють рівність значенню — наприклад, відповідність кольорових кодів їх розшифровкам або коди роботи обладнання (код 200 — норма, 404 — помилка запиту, 403 — заборонено доступ тощо). Раніше для таких випадків теж використовувалися вкладення ЕСЛИ, що робило формулу громіздкою.
Наприклад, так виглядає формула розшифровки колірних кодів за допомогою функції ПЕРЕКЛЮЧ (колірне виділення додано умовним форматуванням).

А тепер та сама формула, реалізована можливостями ЕСЛИ.

Різниця помітна в доволі простих прикладах, а з ростом кількості варіантів вибору переваги нових функцій ЕСЛИМН і ПЕРЕКЛЮЧ ставатимуть зрозумілішими.
Функції И (AND) та ИЛИ (OR)
Ці функції використовують логічні умови як аргументи й повертають результат ИСТИНА або ЛОЖЬ. Функція И повертає ИСТИНА, якщо всі умови виконуються, а функція ИЛИ — якщо виконується хоча б одна з перелічених умов.
Приклади:

Як правило, ці функції самі по собі рідко використовуються — їх частіше застосовують як вкладені функції у формулах. Наприклад, повертаючись до прикладу з визначенням норми росту, формулу:
=ЕСЛИ(B2<$E$2;"меньше нормы";ЕСЛИ(B2<$F$2;"норма";"больше нормы"))
можна переписати з використанням функції И ось так:
=ЕСЛИ(И(B2>$E$2;B2<$F$2);"норма";ЕСЛИ(B2<$E$2;"меньше нормы";"больше нормы"))

Функція НЕ (NOT)
Призначення цієї функції — змінювати логічне значення, тобто якщо результат обчислення ЛОЖЬ, функція поверне ИСТИНА і навпаки. На практиці зустрічається рідше.
Функція ЕСЛИОШИБКА (IFERROR)
Функція для перехоплення помилок, добре підходить, коли потрібно зловити помилку #, що виникає, якщо формула вже введена, а дані в клітинках ще не заповнені.

Ця функція чудово поєднується з умовним форматуванням: наприклад, можна задати колір клітинкам, що містять помилки обчислень. Вона також корисна при «ловінні» інших помилок, наприклад, у складніших гілках обчислень.
Важко переоцінити значення логічних функцій у Excel — вони зазвичай поєднуються з іншими функціями, що безпосередньо виконують обчислення. У продовженні курсу ми будемо будувати дедалі складніші конструкції з функцій, а розглянуті тут базові логічні функції допомагатимуть у виконанні обчислень.
2. Використання формул для підведення підсумків з умовами
Відеоверсія
Текстова версія
Комбіноване використання логічних функцій і функцій для підведення підсумків із групи Автосума (що розглядалися у 4-му занятті) дозволяє створювати формули, обчислення в яких залежать від певних умов — наприклад, підсумовувати лише додатні значення або знайти середню зарплату керівного складу співробітників тощо.
Більш того, на практиці таке поєднання функцій виявилося дуже затребуваним, тому, щоб розвантажити конструкції з кількох функцій, у Microsoft додали ряд функцій для підведення підсумків із умовами (сумувати, середнє, кількість якщо виконується умова), розглянемо їх.
Функція СУММЕСЛИ (SUMIF)
Сумує значення діапазону за виконання певної умови. Діапазон для сумування та діапазон з умовами можуть не збігатися.
Роботу цієї функції розглянемо на прикладі. У звіті за місяць з продажів автомобілів автосалону потрібно підрахувати загальний прибуток у розрізі класів автомобілів.

Щоб порахувати загальний прибуток у розрізі класу автомобілів достатньо однієї простенької формули.
Функція СРЗНАЧЕСЛИ (AVERAGEIF)
Функція СРЗНАЧЕСЛИ (AVERAGEIF) за своїм діянням аналогічна СУММЕСЛИ (SUMIF), за тією лише різницею, що знаходить середні значення, а не суму; діапазони для сумування, як і в попередньому варіанті, можуть як збігатися, так і відрізнятися. Минулого разу ми сумували прибуток по класах автомобілів, тобто діапазони різнилися, зараз ми можемо знайти середню ціну автомобілів із вартістю менше $15000.

Функції СУММЕСЛИМН (SUMIFS) і СРЗНАЧЕСЛИМН (AVERAGEIFS)
Відрізняються від попередніх двох функцій із підсумковими умовами тим, що можна встановлювати кілька критеріїв, наприклад, порахувати середню ціну автомобілів вартістю менше 15 тис. доларів і більше 10 тис. доларів.

Працюючи з функціями СУММЕСЛИМН (SUMIFS) і СРЗНАЧЕСЛИМН (AVERAGEIFS) слід пам'ятати, що по-перше, їхній синтаксис відрізняється від функцій для одного критерію: діапазон умов і сумування (або усереднення) поміняні місцями; а по-друге, умови в функціях сприймаються як логічне І, тобто щоб функція виконала сумування/усереднення, потрібно, щоб виконалися всі умови. Тому, наприклад, щоб порахувати прибуток від продажів авто C-класу і B-класу однією функцією СУММЕСЛИМН вирішити, що називається «в лоб», не вийде, бо тут логічна умова звучить як: «сумувати значення, якщо автомобіль B-класу або C-класу». В кінці цього питання ми розглянемо варіант складання умови, який у деяких випадках може допомогти.
Функції СЧЁТЕСЛИ (COUNTIF) і СЧЁТЕСЛИМН (COUNTIFS)
Підраховують клітинки, які відповідають певному критерію; працюють аналогічно розглянутим раніше функціям із сумуванням і підрахунком середнього значення. Використання цих функцій буде корисним, наприклад, при підрахунку кількості студентів, що склали іспит на відмінно, або набрали понад 80 балів тощо.

Максимальна кількість критеріїв у всіх розглянутих функціях на кілька критеріїв (СУММЕСЛИМН, СРЗНАЧЕСЛИМН, СЧЁТЕСЛИМН) не повинна перевищувати 127.
При складанні формул у функціях можна робити неточні запити за допомогою символів "*" та "?". Детально техніка роботи з цими символами описана в другому занятті "Пошук і заміна даних". Зараз ми за допомогою нечіткого запиту знайдемо загальну суму прибутку по седанам із прикладу.

3. Використання формул для пошуку даних у книзі
Відеоверсія
Текстова версія
Одним із найпоширеніших виробничих завдань, які доводиться вирішувати в Excel, є пошук значень у діапазоні даних. Для цих цілей у Excel найчастіше використовують функцію ВПР (VLOOKUP), яка виконує пошук значення в першому стовпці діапазону й повертає значення з іншого стовпця того ж діапазону.
У Excel є також функція ГПР (HLOOKUP), яка відрізняється від ВПР лише тим, що здійснює пошук не в першому стовпці таблиці, а в першому рядку, тобто пошук відбувається горизонтально, а не вертикально. Оскільки переважна більшість таблиць влаштована вертикально — навіть сам лист Excel при 16 тис. стовпців має понад мільйон рядків, ніби натякаючи, що правильно розміщувати дані по рядках — ВПР здобула набагато більшу популярність, ніж ГПР.
Скорочення зазначених функцій походить від слів ВПР – вертикальний пошук, ГПР – горизонтальний пошук.
Попри поширеність, ВПР має низку недоліків, які вирішуються за допомогою інших функцій (та зв'язок) в Excel. У цьому питанні ми розглянемо недоліки ВПР і ефективні способи заміни ВПР іншими функціями. Проте почнемо з розгляду самої ВПР.
Функція ВПР (VLOOKUP)
Наприкінці 2019 року Microsoft представила заміну цієї функції — ПРОСМОТРХ (XLOOKUP), яка більш продуктивна, більш функціональна й легша в опануванні. Єдина умова — ця функція працює лише в Office 365 або наступній версії, яка має вийти після 2019 року. Посилання на цю функцію та супутні новини — в розділі "Корисне"
Синтаксис функції ВПР такий:
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
На наступному рисунку подано приклад і ілюстративне зображення роботи функції ВПР.

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

Кілька особливостей роботи функції ВПР в Excel:
- якщо "искомое_значение" буде менше за найменше значення в першому стовпці, то формула поверне помилку "#Н/Д";
- значення в таблиці можуть бути числами, текстом або логічними; крім того, Excel ігнорує регістр символів, тому записи великими й малими буквами еквівалентні;
- 4-й аргумент функції є опціональним (його можна не вводити): якщо він опущений або дорівнює 1 чи TRUE, то функція шукатиме приблизне співпадіння — поверне найбільше значення, яке менше або дорівнює шуканому. Наприклад, у розглянутому прикладі в стовпці "Тривалість роботи" після сортування при пошуку персонального бонусу для 0,6 років буде повернено бонус для 0,5 роки, бо це найбільше значення в стовпці, яке менше від шуканого; те саме стосується 0,9 чи 0,99 — для всіх цих значень повернеться бонус від 0,5;
- якщо 4-й аргумент дорівнює FALSE або 0, то стовпець не повинен бути відсортований за зростанням, а шукатиметься лише точне співпадіння; якщо його немає, функція поверне помилку "#Н/Д";
- пошук можна виконувати й за текстовими значеннями, наприклад, за ПІБ; тут треба бути максимально уважним і не забувати вказувати 4-м параметром 0 або FALSE, бо якщо ви випадково помилитеся в ПІБ при приблизному співпадінні, Excel поверне значення таблиці іншого співробітника, а не помилку "#Н/Д".
Функція ГПР (HLOOKUP)
Як уже зазначалося, ця функція відрізняється від попередньої напрямком перегляду — він здійснюється по рядках, а не по стовпцях.
За допомогою функції ГПР знайдемо температуру кипіння хлороформу; також нагадаємо, що діапазонам можна давати імена, тому попередньо позначимо діапазон "свойства_растворителей":
=ГПР("Температура кипения";свойства_растворителей;4;0)

Окрім напрямку перегляду, ГПР нічим не відрізняється від ВПР.

Альтернатива ВПР: ІНДЕКС + ПОИСКПОЗ
При частому використанні функції ВПР не можна не звернути увагу на її певні недоліки. Перший полягає в тому, що пошук шуканого значення завжди здійснюється в першому стовпці діапазону з даними; а другий — повернений результат завжди має знаходитися справа, що є очевидним, якщо пошук іде по першому стовпцю. З першим недоліком можна миритися, бо в ВПР не обов'язково вказувати весь діапазон — можна лише частину його. А от із другим буде складніше: не варто ж міняти стовпці місцями заради одного пошуку.
В Excel можна відносно легко виконати пошук у будь-якому стовпці й повернути значення як ліворуч від початкового стовпця, так і праворуч від нього; правда, для цього знадобиться не одна функція, а дві: ІНДЕКС і ПОИСКПОЗ.
При використанні зв'язки ІНДЕКС і ПОИСКПОЗ друга функція виступатиме як аргумент першої.
Функція ІНДЕКС нам знадобиться в формі масиву:
ИНДЕКС(массив, номер_строки, [номер_столбца])
Дана функція поверне значення з масиву на перетині вказаного нами рядка і стовпця; якщо проводити аналогію з ВПР, то нам відомий масив і номер стовпця, а от номер рядка необхідно знайти. Для того, щоб знайти номер рядка знадобиться функція ПОИСКПОЗ (MATCH):
ПОИСКПОЗ(искомое_значение; просматриваемый_массив; [тип_сопоставления])
Ця функція поверне номер рядка, або точніше порядковий номер елемента в діапазоні, але для нас це одне й те саме.

За допомогою такої зв'язки можна за тривалістю роботи в компанії знайти ФИО співробітника, чого не можна зробити за допомогою ВПР, оскільки ФИО знаходяться лівіше від стовпця з шуканим значенням (у даному випадку стаж роботи в компанії).

Множинний ВПР
За замовчуванням ВПР (ГПР або розглянута вище зв'язка ІНДЕКС і ПОИСКПОЗ) виконують пошук тільки до першого співпадіння. Якщо в стовпці, де знаходиться шукане значення, усі значення унікальні (наприклад, простий номер або унікальний ідентифікатор), проблем немає, але що робити, якщо шуканих значень декілька (наприклад, однофамільці або кілька людей зі однаковим стажем)? Описані вище способи знайдуть тільки перше співпадіння.
Хороша новина: в Excel досить легко реалізувати множинний вибір значень, правда зробити це доведеться з використанням деяких проміжних обчислень.
Основою для вибору всіх значень із переліку буде функція ІНДЕКС, відповідно, це дає нам можливість вибирати значення як лівіше, так і правіше від шуканого значення. Також нам знадобляться функції: ЕСЛИ, СТРОКА, НАИМЕНЬШИЙ. Крім того, тут буде використовуватись введення формули в режимі формули масиву, але про все по порядку.
З функцією ЕСЛИ ми знайомилися в першому питанні цього заняття, а от з іншими поки ні.
Синтаксис функції СТРОКА надзвичайно простий:
СТРОКА([ссылка])
Функція повертає номер рядка посилання; якщо посилання не вводити, то повернеться номер рядка клітинки, в яку введена сама функція.
Функція НАИМЕНЬШИЙ за своєю функціональністю дуже схожа на розглянуту раніше функцію МИН, вона також повертає мінімальне значення з діапазону, основна відмінність від МИН у тому, що можна вибрати, яке саме найменше значення повернути (1-е найменше, 2-е з кінця, 3-е тощо). Синтаксис:
НАИМЕНЬШИЙ(массив; k)
Обидва параметри обов'язкові до заповнення, а якщо k дорівнює 1, то функція НАИМЕНЬШИЙ стає аналогом функції МИН.
Тепер, щоб вибрати всі значення (ФИО), які відповідають критерію (наприклад, стаж роботи 4 роки), спочатку необхідно знайти номери рядків, в яких розташовані ці прізвища. Це робиться за допомогою функцій ЕСЛИ і СТРОКА. Позначення стажу винесене в окрему клітинку, а в формулі використовується абсолютне посилання на неї — у майбутньому це дозволить легко знаходити інші значення без зміни самих формул.

Логіка формули така: якщо значення в стовпці "Тривалість роботи" дорівнює стажу (4 роки), тоді виводити номер рядка, і, оскільки функція СТРОКА виводить номер рядка не в діапазоні, а на листі, то віднімаємо 1, бо наш діапазон зміщений на один рядок вниз; відповідно, якби заголовки таблиці були у другому рядку, а сам діапазон з даними починався з третього рядка, тоді довелося б віднімати 2 і т.д.
Після того як номери рядків у діапазоні знайдені, ми можемо за допомогою функції ІНДЕКС уже вивести результат, оскільки діапазон відомий, номер стовпця вводимо самостійно (це перший стовпець з прізвищами), а номер рядка ми знайшли; лише значення стоять не по порядку — щоб це виправити, за допомогою функції НАИМЕНЬШИЙ виведемо всі знайдені значення від меншого до більшого. До речі, якщо скористатися функцією НАИБОЛЬШИЙ, то можна вивести значення від більшого до меншого.

На передостанньому етапі необхідно передати отримані параметри в функцію ІНДЕКС для правильного виведення співробітників. Тут є один нюанс: потрібно запланувати очікувану кількість відповідей — якщо розтягнемо формулу на меншу кількість клітинок, то знайдуться не всі прізвища; якщо буде більше, то отримаємо помилку "#ЧИСЛО", яку виправимо на останньому етапі.

На останньому етапі (який зовсім не обов'язковий, а радше естетичний) будемо використовувати функцію ЕСЛИОШИБКА для перехоплення помилки.

Цю формулу можна записати в один рядок, єдиним проміжним стовпцем залишаться цифри (1,2,3 тощо), які можна сховати. Якщо записати формулу в стовпці, то її потрібно буде використовувати як формулу масиву, тобто завершувати введення не Enter, а поєднанням Ctrl+Shift+Enter; при цьому в рядку формул формула буде в фігурних дужках.

Флешкартки
Так звані карти пам'яті: дивитесь на карту й намагаєтесь відповісти; по кліку на карту відобразиться правильна відповідь. Карти пам'яті корисні для запам'ятовування ключових позицій заняття. Усі заняття цього курсу забезпечені картами пам'яті.
Практика
У практичній частині ви знайдете завдання за пройденим заняттям. Після їх виконання у вас є можливість порівняти свій варіант із підготовленою лектором відповіддю. Наполегливо рекомендується дивитися розв'язок лише після того, як ви виконали завдання самостійно. Для деяких завдань є невеликі підказки.
Завдання 1. Набуття навичок використання функції ЕСЛИ
- Відкрити книгу Задание 7.1 старт.
- Розрахувати середній зріст, заповнивши відповідну клітинку на листі Excel. Обов'язково округлити значення середнього зросту до сотих, використовуючи для цього функцію ОКРУГЛ.
- Заповнити стовпець D рядками "Вищий за середній" або "Нижчий за середній" залежно від зросту учня, використовуючи логічну функцію ЕСЛИ.
- Скопіювати таблицю. Відредагувати формули задачі, передбачивши третю альтернативу — зріст учня теоретично може збігатися із середнім значенням. У такому випадку надрукувати рядок "Рівний середньому".
- Скопіювати попередню таблицю. Доповнити розв'язок другої задачі обчисленням максимального й мінімального зросту. Власників цих значень замість рядків "Вищий за середній" або "Нижчий за середній" виводити як "Максимальний зріст".
- Зберегти книгу під іменем Задание 7.1 выполнено.
Переглянути розв'язок
Завдання 2. Набуття навичок використання функції УСЛОВИЯ (Excel 2016)
- Відкрити книгу з попереднього завдання Задние 7.1 выполнено і переробити останню формулу, замінивши функцію ЕСЛИ функцією УСЛОВИЯ.
- Зберегти книгу під іменем Задание 7.2 выполнено.
Переглянути розв'язок
Завдання 3. Набуття навичок використання підсумкових функцій з умовами
- Відкрити книгу Задание 7.3 старт.
- Використовуючи функцію СУММЕСЛИ розрахувати суми значень і заповнити відповідні клітинки на листі "СУММЕСЛИ".
- Підрахувати підсумкові значення на листі "СУММ_СРЗНАЧ_СЧЕТЕСЛИ".
- Зберегти книгу під іменем Задание 7.3 выполнено.
Переглянути розв'язок
Завдання 4. Набуття навичок пошуку даних за допомогою ВПР
- Відкрити книгу Задание 7.4 старт.
- На листі "ВПР" за допомогою функції ВПР знайти персональний бонус співробітника залежно від тривалості роботи та залежно від ФИО. Примітка: искомое_значение у формулі використовувати як посилання на клітинку.
- На листі "ГПР" за допомогою функції ГПР знайти розчинність у воді для певних розчинників. Примітка: искомое_значение у формулі використовувати як посилання на клітинку.
- Зберегти книгу під іменем Задание 7.4 выполнено.
Переглянути розв'язок
Завдання 5. Набуття навичок пошуку даних за допомогою функцій ИНДЕКС і ПОИСКПОЗ
- Відкрити книгу Завдання 7.5 старт.
- За допомогою функцій ИНДЕКС (INDEX) і ПОИСКПОЗ (MATCH) вивести будь-який показник таблиці для будь-якого співробітника (в одне поле вводиться номер стовпця показника, а в іншому вибирається ПІБ співробітника). Наприклад, для 4-го стовпця і Федорової Марії Петрівни — це буде 2600000 (стовпець "Факт продаж").
- Зберегти книгу під іменем Завдання 7.5 виконано.
