12 функцій Excel, які можуть спростити ваше життя

Excel, напевно, — одна з найбільш недооцінених програм у світі. Величезна кількість користувачів застосовує програму як простий табличний редактор, і більшість операцій робить вручну. Натомість Excel — це суперуніверсальний інструмент, адже він може: під’єднуватися до зовнішніх джерел даних, легко трансформувати, аналізувати дані, візуалізувати, динамічно фільтрувати, будувати дашборди, звіти тощо, на льоту оновлювати дані... Всього не перелічиш.

Про маловідомі або «секретні» функції Excel нам розповів Богдан Дуда, Senior BI Analyst, який вже понад 18 років досліджує аналітику.

Секрет № 1. Формула Filter

На мою думку, це одна з найбільш недооцінених формул. Можливо, причина в тому, що вона з’явилася лише 5 років тому і в україномовному сегменті про неї поки що не так багато інформації.

Отже, що робить Filter — фільтрує дані. Але вона здійснює це автоматично, динамічно, підтримує складні умови фільтрації, третій аргумент формули дає змогу зручно керувати обробкою помилок, підтримує масиви й має безшовну інтеграцію з іншими функціями. Синтаксис формули максимально простий.

=FILTER(A2:A100, B2:B100="Немає", "Нічого не знайдено")

Перший аргумент — діапазон, який ми фільтруємо.
Другий аргумент — умова фільтрування.
Третій аргумент (необов’язковий) — що виводити, коли помилка.

І все.

Секрет № 2. Гарячі клавіші = швидша робота

Тут, звісно, все дуже суб’єктивно, адже різні користувачі мають дуже різні завдання, тож я поділюся своєю улюбленою добіркою шорткатів — можливо, комусь вони стануть у пригоді.

Секрет № 3. Умовне форматування

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

Якщо потрібно щось складніше, можна використати New Rule — створити власне правило, написавши формулу, яка визначатиме його логіку, і задати параметри форматування: колір, шрифт, лінії тощо.

Клікнувши на значок New Rule, можна відкрити вікно, як на картинці знизу, де потрібно обрати Use a formula to determine which cell to format і в полі Format values where this formula is true: написати свою умову форматування, а потім, клікнувши Format, задати свій неповторний стиль.

Секрет № 4. Як швидко виправляти помилки у формулах

Напевно, всі знають формулу IFERROR. Вона дає змогу задати значення, яке відтворюватиметься замість помилки. Але мало хто знає, що можна використати іншу формулу.

Ось так написана формула =IFERROR(VLOOKUP(A2;K2:L25;2;0);0) означає, що замість помилок виводитиметься 0.

А таке написання формули =IFERROR(VLOOKUP(A2;K2:L25;2;0);VLOOKUP(--A2;K2:L25;2;0)) вказує, що там, де помилка, IFERROR запускатиме ще раз VLOOKUP, але цього разу шукатиме трохи по-іншому, а саме на льоту перетворюватиме А2 на числовий формат і знаходитиме щастя таким чином.

Секрет № 5. Фільтри для сортування 2:0

У секреті № 1 ми вже згадували формулу Filter, а зараз подивімося на формулу Sort.

Я напишу її трохи складніше: =SORT(A2:D257;{3;4};{1;-1})

A2:D257 — діапазон сортування.
{3;4} — стовпці, за якими ми сортуємо.
{1;-1} — порядок сортування (за зростанням для третього стопвця, за спаданням для 4-го).

Відтак, формула Sort допомагає динамічно сортувати таблиці — по одному або по кількох стовпцях, а також можна задавати напрям сортування для кожного зі стовпців.

 Секрет № 6. Power Query для рутинних завдань

Power Query — це чудовий інструмент, який дає змогу під’єднуватися до величезної кількості джерел даних, має зручний інтерфейс для трансформування даних (де всі формули генеруються кліканням мишки та записуються в кроки) та можливість оновлення даних.

Навіть початківці можуть робити просто грандіозні трансформації даних.

Для більш просунутих на вкладці Automate можна знайти Office Scripts, який теж непогано допомагає автоматизовувати рутину.

Але раджу переходити до цього тільки після опанування Power Query.

Секрет № 7. Як об’єднати або розділити дані у клітинках

Кілька років тому в Excel з’явилися 3 круті формули для роботи з текстом: TEXTBEFORE, TEXTAFTER і TEXTSPLIT. Це справжня революція! У кожної з них два обов’язкові аргументи: клітинка з текстом і роздільник, а також багато додаткових опцій, як-от номер роздільника, робота з кількома роздільниками одночасно, функція «якщо помилка» тощо.

Дослідіть їх — і вони вам точно сподобаються. 

Секрет № 8. Дата й час

В Excel з датами працюємо не так часто, але це дуже корисно. Покажу трюк із форматом даних і одну цікаву формулу.

Формула має назву =NETWORKDAYS.INTL. Вона маловідома, але дієва. Її фішка — можна задавати користувацькі вихідні. Наприклад, формула =NETWORKDAYS.INTL(C3;C2;"0000111") робить п’ятницю теж вихідним днем. За допомогою нулів і одиниць можна змінювати будь-який день на робочий або вихідний.

Ще один трюк з форматом. Якщо ви хочете, щоб ваші дати мали не такий вигляд «28.04.2025», а, наприклад, ось такий «Пн 28.04.2025», тобто показували ще день тижня, змініть формат з dd.mm.yyyy на ddd dd.mm.yyyy — просто додайте тричі d. 

Секрет № 9. Діаграми

Візьмемо ось такий графік:

Має доволі нудний або щонайменше буденний вигляд.

«Розвеселити» його можна ось так:

Як це зробити?

Я скопіював цю форму з фігур, додав до неї градієнтну заливку і вставив у графік, просто копіюючи фігуру. Потім наїхав на будь-який синій стовпець із попереднього графіка і натиснув вставити.

Вуаля!

Секрет № 10. Формули масивів

Тепер все по-іншому. Забудьте про конструкцію Ctrl+Shift+Enter. Зараз, якщо ви працюєте з масивом, просто додайте ось цей значок #. Так-так, хештег.

Якщо у вас є діапазон А1:А4 і він динамічний, то зіслатися на нього, наприклад, у клітинці B1, можна, ввівши А1#. Тепер ви посилаєтеся не на клітинку А1, а на діапазон А1:А4. Будьте уважні!

Секрет № 11. Пошук та заміна

Розглянемо ситуацію: нам потрібно видалити всі рядки з таблиці, якщо в цьому рядку в будь-якому стовпці є пуста клітинка. Для цього натискаємо F5. З’явиться таке віконечко: оберемо тут Blanks, що означає пусті клітинки. І натиснемо ОК.

Після цього треба натиснути Ctrl + «-», що означає команду, яка видаляє рядки. Всі пусті рядки видаляться. 

Якщо треба видалити рядки, що містять якесь слово, наприклад, subtotal, тоді натискаємо Ctrl+F (пошук) — відкриється вікно, де в полі пошуку потрібно написати необхідне нам слово.

Після цього натискаємо Find All, тобто «Знайти все».

Excel знайде всі рядки, де є Subtotal, після цього натискаємо Ctrl+A, тобто виділити все.

Весь отриманий результат має стати синім.

Тоді закриємо це вікно і натискаємо Ctrl + «-», щоб видалити рядки зі словом Subtotal. 

Секрет № 12. Зведені таблиці

Зведені таблиці — дуже потужний інструмент в Excel. Але все ж таки він має свої обмеження.

Щоб їх обійти, варто скористатися формулами DAX. На вкладці Power Pivot (якщо її немає, то можна підключити через file-options-Add-ins-COM Add-ins-Go і поставити галочку біля Power Pivot та натиснути Ok) знайти значок Add to Data Model (додати до моделі даних)

І кожну таблицю додати до моделі даних.

Потім між цими таблицями можна зробити зв’язки й здійснювати аналітику, використовуючи формули DAX (щось на кшталт цієї Онлайн:=CALCULATE([Виручка];'Data'[Місце продажу]="Онлайн").

Формули DAX трохи складніші від Excel-формул, але дозволяють Excel перетворити на Power BI.