Повний довідник формул Excel, що мають значення — від п'яти щоденних до сучасних функцій динамічних масивів, що змінюють роботу з таблицями.
- Спершу освойте п'ять формул: XLOOKUP, SUMIFS, IF, COUNTIFS, INDEX/MATCH — вони закривають 80% реальної роботи в Excel.
- Сучасний Excel (365, 2021+) додав динамічні масиви — FILTER, SORT, UNIQUE, SEQUENCE, LET — що замінюють старі багатокрокові обхідні шляхи.
- Використовуйте таблиці (Ctrl+T) з іменованими діапазонами, щоб формули залишались зрозумілими в міру зростання даних.
- Загортайте ризиковані формули в IFERROR, щоб обробляти помилки елегантно і тримати дашборди чистими.
У Excel близько 500 функцій. Ви будете використовувати приблизно 30. Список формул, вартих запам'ятовування, коротший, ніж люди очікують, а прірва між «я знаю базовий Excel» і «я досвідчений користувач» здебільшого в тому, щоб знати, які саме 30 вивчати.
Чому сучасний Excel інший
Excel 365 і 2021 запровадили динамічні масиви — формули, що автоматично розливаються в кілька клітинок. Це було фундаментальною зміною у роботі додатку. До динамічних масивів повернення кількох значень вимагало Ctrl+Shift+Enter, що було крихким і заплутаним. Тепер ви пишете одну формулу, і вона заповнює стільки клітинок, скільки потрібно результату. Нові функції на основі цього — FILTER, SORT, UNIQUE, SEQUENCE — замінюють десятки старих багатокрокових технік одноформульними рішеннями.
П'ять формул, які повинен знати кожен
| Формула | Що робить | Чому важлива |
|---|---|---|
| XLOOKUP | Знаходить значення, повертає інше | Замінює VLOOKUP; найпоширеніша функція в сучасному Excel |
| SUMIFS | Сума за кількома умовами | Звітність, дашборди, фінансове моделювання |
| COUNTIFS | Підрахунок за кількома умовами | Частотний аналіз, умовні підрахунки |
| IF / IFS | Умовна логіка | Логіка прийняття рішень у будь-якій таблиці |
| INDEX/MATCH | Гнучкий пошук (застаріла альтернатива XLOOKUP) | Зворотна сумісність зі старими версіями Excel |
Формули пошуку
XLOOKUP — сучасний стандарт
XLOOKUP замінює VLOOKUP, HLOOKUP і більшість використання INDEX/MATCH в одній чистішій функції. Доступна в Excel 365 і 2021+. Точне співпадіння за замовчуванням, пошук у будь-якому напрямку, повертає цілі рядки.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Приклад: =XLOOKUP("Іван", A:A, B:B, "Не знайдено") знаходить зарплату Івана або повертає "Не знайдено".
VLOOKUP — застарілий, але поширений
Функція 1985 року, що досі живе в мільйонах таблиць. Завжди передавайте FALSE як четвертий аргумент, інакше отримаєте тихо неправильні результати. Шукає лише зліва направо.
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
INDEX/MATCH — робоча конячка сумісності
Класичне поєднання, що вирішує все, чого не може VLOOKUP. Працює у всіх версіях Excel. Дві функції, але гнучкіше за VLOOKUP.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Математика та статистика
| Формула | Шаблон | Сценарій використання |
|---|---|---|
| SUM | =SUM(range) | Підсумок стовпця або діапазону |
| SUMIF | =SUMIF(criteria_range, criteria, sum_range) | Сума за однією умовою |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, ...) | Сума за кількома умовами |
| AVERAGE / AVERAGEIFS | Ті самі шаблони, що у SUM | Середні значення з опціональними умовами |
| COUNT / COUNTIFS | Ті самі шаблони | Підрахунок клітинок з опціональними умовами |
| MIN / MAX / MEDIAN | =MIN(range) | Знайти екстремальні або середні значення |
| ROUND / ROUNDUP / ROUNDDOWN | =ROUND(value, decimals) | Контролювати кількість десяткових знаків |
Логічні формули
Інструментарій умов
- IF — одна умова:
=IF(A1>100, "Великий", "Малий") - IFS — кілька умов, чистіше вкладених IF:
=IFS(A1>1000, "Гігант", A1>100, "Великий", TRUE, "Малий") - AND / OR / NOT — комбінування умов
- IFERROR — замінити помилки кастомними значеннями:
=IFERROR(VLOOKUP(...), "Не знайдено")
Текстові формули
| Формула | Сценарій використання |
|---|---|
| TEXTJOIN(separator, ignore_empty, range) | Об'єднати текстові значення з роздільником |
| LEFT / RIGHT / MID | Витягти конкретні частини рядка |
| LEN(text) | Підрахунок символів |
| TRIM(text) | Видалити зайві пробіли — вирішує більшість проблем імпорту |
| UPPER / LOWER / PROPER | Змінити регістр |
| FIND / SEARCH / SUBSTITUTE | Знаходити позиції або замінювати текст |
Формули дат
Основні функції дат
- TODAY() — поточна дата, перераховується щодня
- NOW() — поточні дата і час
- DATEDIF(start, end, unit) — різниця між датами ("Y" роки, "M" місяці, "D" дні)
- EOMONTH(date, months) — останній день місяця
- NETWORKDAYS(start, end, [holidays]) — робочі дні між датами
Сучасні формули динамічних масивів
FILTER
Повертає рядки з діапазону, що відповідають умові. Замінює складну багатокрокову фільтрацію однією формулою.
=FILTER(A:E, B:B="Північ")
SORT та SORTBY
Сортує діапазон або сортує один діапазон на основі значень іншого.
UNIQUE
Повертає унікальні значення з діапазону. Замінює видалення дублікатів формулою замість ручної операції.
SEQUENCE
Генерує послідовність чисел. Корисно для тестових даних, динамічних діапазонів або побудови логіки масивів.
LET
Визначає змінні всередині формули для читабельності. Зміна гри для складних формул — присвоює проміжні значення іменам замість повторення виразів.
=LET(rate, 0.05, principal, B2, principal*rate)
Поради досвідчених користувачів
Використовуйте таблиці (Ctrl+T)
Конвертуйте діапазони в Таблиці для автоматичного розширення формул і структурованих посилань на кшталт =SUM(Продажі[Виручка]).
Використовуйте іменовані діапазони
Визначайте діапазони як імена, щоб =SUM(Продажі) замінило =SUM(B2:B100). Критично важливо для будь-якої формули, яку ви будете перечитувати або підтримувати.
Опануйте абсолютні посилання ($)
$A$1 фіксує рядок і стовпець. $A1 фіксує лише стовпець. A$1 фіксує лише рядок. Використовуйте F4 для перемикання між типами посилань.
FAQ
Яка найважливіша формула Excel?
XLOOKUP для Excel 365 або 2021+, VLOOKUP інакше. Формули пошуку використовуються майже в кожній бізнес-таблиці. Спочатку опануйте XLOOKUP.
VLOOKUP чи XLOOKUP?
XLOOKUP для Excel 365 або 2021+. Кращий в усіх вимірах — безпечніші значення за замовчуванням, більша гнучкість, швидший на великих даних, чистіший синтаксис. Використовуйте VLOOKUP лише для зворотної сумісності.
Формули Excel vs Google Таблиці?
Близько 95% формул однаково працюють в обох. Обидва підтримують VLOOKUP, XLOOKUP, INDEX/MATCH, SUMIFS, FILTER, SORT, UNIQUE і більшість функцій дат і тексту.
Скільки часу потрібно для освоєння формул Excel?
П'ять базових формул — 1–2 тижні щоденного використання. 30 найпопулярніших — 1–3 місяці. Стати справжнім досвідченим користувачем — 6–12 місяців.
Підсумок
Вам не потрібно запам'ятовувати 500 функцій Excel. 30, наведені тут, закривають 95% реальної бізнес-роботи, а п'ять топових формул — 80% з цього. Спершу опануйте XLOOKUP, SUMIFS, IF, COUNTIFS і INDEX/MATCH. Додайте функції динамічних масивів (FILTER, SORT, UNIQUE, LET) після засвоєння базових. Загортайте ризиковані формули в IFERROR. Використовуйте таблиці та іменовані діапазони для читабельності.
- Опануйте XLOOKUP, SUMIFS, IF, COUNTIFS, INDEX/MATCH — вони закривають 80% реальної роботи.
- Динамічні масиви (FILTER, SORT, UNIQUE, LET) замінюють старі багатокрокові техніки.
- Завжди передавайте FALSE у VLOOKUP; це позбавляє тихих неправильних результатів.
- Загортайте ризиковані формули в IFERROR для чистого виводу.
- Використовуйте таблиці (Ctrl+T) та іменовані діапазони для читабельності формул.
Одне посилання для контенту та навчальних матеріалів Excel
Додайте URL UniLink до свого біо — показуйте навчальні матеріали, курси, блог. Безкоштовно.