VLOOKUP (ВПР)

Навіть якщо ви новачок в Excel, ви, скоріш за все, чули про одну з найпопулярніших функцій — VLOOKUP (або ВПР — вертикальний перегляд). Досвідченим користувачам використання цієї просунутої функції допомагає значно спростити щоденну роботу.

 

ВПР здійснює у стовпці вертикальний пошук вказаного критерію за будь-яким форматом (текст, число, дата тощо) та повертає значення пошуку або будь-яке значення із будь-якого стовпчика у тому ж рядку. В разі відсутності шуканого значення програма видає помилку #N/A (#Н/Д).

 

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

 

XLOOKUP

До появи у 2021-му році XLOOKUP «королевою функцій» вважалася ВПР. Сьогодні її «корону» забрала XLOOKUP (ПРОСМОТРХ). Вона удосконалила деякі недоліки VLOOKUP.

 

Так, XLOOKUP виконує як вертикальний, так і горизонтальний пошук діапазону чи масиву (тобто як у рядках, так і в стовпчиках). ПРОСМОТРХ повертає елемент, який відповідає першому знайденому збігу. Якщо його не існує, функція повертає приблизний збіг.

 

VLOOKUP вимагає, щоб стовпчик, у якому здійснюється пошук, був першим у діапазоні, а також здійснює пошук тільки зліва направо. Натомість XLOOKUP може шукати дані в будь-якому напрямку та в будь-якій колонці і повертати результати з будь-якої іншої колонки.

 

XLOOKUP доступна для користувачів Excel 2021 та Microsoft 365. Тож якщо інший користувач, який має право доступу до файлу, використовує версію Excel до 2021-го, формула для нього не працюватиме.

 

Більше про можливості XLOOKUP ви зможете дізнатися на нашому навчальному курсі 

 

INDEX/MATCH

INDEX/MATCH (або ИНДЕКС/ПОИСКПОЗ) зазвичай не використовуються окремо, натомість їх поєднання стає потужним інструментом аналізу даних. Навіть якщо ви користуєтесь XLOOKUP, варто знати також INDEX та MATCH.

 

Комбінація INDEX/MATCH є більш гнучкою за VLOOKUP, адже дозволяє знайти дані в таблиці незалежно від того, в якому стовпчику вони знаходяться. Ви можете вибирати окремі стовпці або рядки для пошуку, а не весь діапазон, як при використанні VLOOKUP. ВПР` шукає дані тільки зліва направо, натомість INDEX/MATCH не має подібних обмежень.

 

Крім того, ИНДЕКС/ПОИСКПОЗ працює з динамічними посиланнями, тобто додавання або видалення колонок не впливають на роботу функції (на відміну від. ВПР, яка використовує статичні посилання).

 

SUMIF, COUNTIF, SUMIFS, COUNTIFS

Ці функції схожі між собою: вони дозволяють робити умовні обчислення на основі визначених користувачем критеріїв.

 

Наприклад: вам потрібно підрахувати, скільки разів у списку імен зустрічається ім’я Наталя. Для цього використовується функція COUNTIF (СЧЕТЕСЛИ).

 

Якщо ж вам, приміром, потрібно у таблиці зі списком менеджерів та даними продажів по кожному знайти суму всіх продажів окремого менеджера, скористуйтеся функцією SUMIF (СУММЕСЛИ)

 

COUNTIFS (СЧЕТЕСЛИМН) та SUMIFS (СУММЕСЛИМН) використовуються, коли потрібно перевірити декілька умов одночасно.

 

TEXTJOIN

TEXTJOIN (ОБЪЕДИНИТЬ) дозволяє поєднувати вміст кількох клітинок без використання формул або знаку &. При цьому ви можете встановити певний роздільник, який хочете використати для розділення даних (наприклад, пробіл).

 

SUMPRODUCT

SUMPRODUCT (або СУММАПРОИЗВОД) використовується для умовних обчислень: проведення розрахунків, які потребують множення відповідних елементів у двох масивах і потім додавання цих добутків.

 

Наприклад:

У вас є два стовпці: один з кількістю проданих товарів, другий з ціною кожного товару. Вам потрібно розрахувати загальний прибуток з продажу. Функція SUMPRODUCT помножить кількість на ціну для кожного товару, а потім додасть ці добутки, щоб отримати загальний прибуток.

 

FILTER

Функція ФИЛЬТР використовується для фільтрування даних безпосередньо в клітинках та вилучення із них результатів.

 

SORT та SORTBY

SORT (СОРТ) та SORTBY (СОРТПО) здійснюють динамічне сортування набору даних (тобто при зміні вихідних даних оновлюються відсортовані дані).

 

За допомогою SORT можна здійснити сортування за зростанням чи зменшенням одного рядка чи стовпчика. Функція SORTBY застосовується, коли необхідно здійснити сортування декількох стовпчиків.

 

IFS

IFS (ЕСЛИМН) з’явилася у 2019 та допомогла усунути деякі недоліки функції IF.

 

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

 

IFERROR

IFERROR (ЕСЛИОШИБКА) дозволяє встановити, що відображати у клітинці, якщо формула приводить до помилки. Наприклад, при використанні VLOOKUP замість помилки #N/A (#Н/Д), яка виникає, коли формула не може знайти шукане значення, IFERROR дозволяє замінити її на щось більш читабельне, наприклад, «Значення не знайдено».

 

OFFSET

OFFSET (СМЕЩ) використовується рідко, але ж при роботі з динамічними діапазонами даних профі варто її знати. OFFSET дозволяє змістити посилання на певну кількість стовпчиків/рядків від початкової комірки.

 

FIND, SEARCH

FIND (ПОИСК) та SEARCH (ПОИСКБ) дозволяють шукати певний текст та повертати позицію (починаючи з першої), на якій починається перше входження шуканого тексту. Різниця між ними полягає в тому, що FIND (на відміну від SEARCH) чутлива до регістру.

 

RIGHT, LEFT, MID

За допомогою функцій RIGHT, LEFT та MID можна витягнути ліву, праву або середню частину текстового рядка. Часто їх поєднують із функціями FIND та SEARCH.  

 

Наприклад, вам потрібно з адреси електронної пошти anna@gmail.com витягнути ім’я користувача. Для цього скористайтеся функцією LEFT.  

 

TEXTAFTER та TEXTBEFORE

Ці функції доступні в Excel для Microsoft 365. Вони допомагають знайти текст перед (TEXTBEFORE/ТЕКСТДО) або після (TEXTAFTER/ТЕКСТПОСЛЕ) певного роздільника. Раніше для цього потрібно було використовувати комбінацію LEFT/RIGHT та FIND.

 

REPLACE та SUBSTITUTE

REPLACE (ЗАМЕНИТЬ) та SUBSTITUTE (ПОДСТАВИТЬ) допомагають знайти певний текст та замінити його на потрібний. REPLACE дозволяє вказувати лише початкову позицію та загальну кількість символів для заміни у текстовому рядку. Натомість SUBSTITUTE дозволяє вказати цілий текстовий рядок, який потрібно замінити іншим. При цьому не потрібно зазначати позицію та довжину тексту, який необхідно замінити — ви просто вказуєте, який текст треба замінити.

 

SEQUENCE

SEQUENCE (ПОСЛЕДОВАТЕЛЬНОСТЬ) — це нова функція, що доступна в Excel 2021 та Microsoft 365. Вона заповнює діапазон клітинок певною послідовністю чисел/дат. Раніше замість неї використовувалося ручне введення даних або маркер автозаповнення, але з появою спеціальної функції це стало простіше.

 

Наприклад, щоб заповнити клітинки А1-А5 послідовністю чисел від 1 до 5, потрібно набрати:

=SEQUENCE(5;1;1),

 

де 5 — кількість рядків, які потрібно заповнити,

1 — початкове число

та 1 — крок між числами.

 

WORKDAY, NETWORKDAYS, WORKDAY.INTL, NETWORKDAYS.INTL

Функція WORKDAY (РАБДЕНЬ) обчислює дату після вказаної кількості робочих днів. NETWORKDAYS (ЧИСТРАБДНИ) підраховує кількість робочих днів між двома вказаними датами.

 

Ці функції автоматично враховують вихідні дні та ігнорують свята, якщо вони вказані. Але оскільки вихідні дні можуть відрізнятися (наприклад, замість суботи та неділі — понеділок та вівторок), Excel має додаткові функції WORKDAY.INTL (РАБДЕНЬ.МЕЖД)  та NETWORKDAYS.INTL.(ЧИСТРАБДЕНЬ.МЕЖД), які дозволяють вказати робочі та неробочі дні, а потім виконувати розрахунок.

 

IMAGE

Це нова функція, яка доступна на даний момент у Microsoft 365. Її особливість полягає в тому, що вона вставляє зображення у комірку, для чого застосовується URL-адреса зображення. Тобто не потрібно вручну додавати зображення на аркуш та вписувати його в комірку.

 

SMALL та LARGE

SMALL (НАИМЕНЬШИЙ) та LARGE (НАИБОЛЬШИЙ) дозволяють обрати найменше або найбільше значення з діапазону даних.

 

Отже, ми розглянули 20 просунутих функцій Excel. Якщо ви використовуєте їх у своїй роботі, то ви вже набагато ближче до рівня гуру Excel. Детальніше про функції — на нашому курсі Excel: бізнес-аналіз та прогнозування

 

Дізнайтеся більше про повну програму "EXCEL: бізнес-аналіз і прогнозування"