Excel для бізнесу: корисні формули та лайфгаки

У Microsoft Excel мова, якою пишуть функції та формули, залежить від мови інтерфейсу програми. В англійській версії аргументи (номери клітинок або комірок) розділяють комами, в українській — крапками з комами.

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

=CONCATENATE(A1," ",B1)

В українському Excel:

=ОБ’ЄДНАТИ(A2;" ";B2)

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

У 1996 році Excel використовували 30 мільйонів людей. У 2023 році — 1,5 мільярда. Офіційних даних за 2025 рік поки немає, але, ймовірно, ця кількість збільшилася.

Як знайти потрібне число, ім’я, координати чи посилання у великій таблиці на сотні рядків та стовпчиків? За допомогою формул. Вам знадобляться:

VLOOKUP — допомагає знайти елементи в таблиці або діапазоні за рядком (ціну товару за назвою, ім’я працівника за ідентифікатором тощо). Формула:

=VLOOKUP(що шукаємо, де шукаємо, номер стовпця, приблизний або точний збіг)

• що шукаємо — номер клітинки, в якій розташований елемент пошуку. Якщо вам потрібна ціна товару, назва якого вписана в клітинку А2, то тут пишемо А2;
• де шукаємо — діапазон значень таблиці, використовуємо : між літерами, що позначають стовпці;
• номер стовпця — якщо ціни вказані у третьому стовпці, тут пишемо 3;
• приблизний збіг позначаємо як 1/TRUE, а точний — 0/FALSE.

Отримуємо: =VLOOKUP(A2,A:C,3,FALSE)

VLOOKUP завжди шукає в стовпцях, що знаходяться праворуч від першої клітинки.

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

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

=XLOOKUP(що шукаємо, де шукаємо, що повертаємо (яке значення хочемо отримати), додаткові параметри)

Наприклад, хочемо отримати ціну миші:

=XLOOKUP(“002”,А2:А5,С2:С5)

За формулою шукатиметься артикул 002 в стовпці від клітинки А2 до А5 і значення для нього в стовпці від С2 до С5. Відповідь = 1000 грн.

Додаткові параметри: точний або неточний збіг, режим пошуку, що робити, якщо збігів не знайдено.

Випадаючий список — інструмент, що допомагає уникнути помилок, коли користувач вводить дані в систему. Замість того щоб щоразу набирати текст, він вибиратиме із кількох запропонованих варіантів.

Аби створити такий список, виділіть клітинку та натисніть: Дані — Перевірка даних — Додати правило — Спадне меню. Налаштуйте елементи списку.

Ви можете скопіювати список та змінити його в окремих клітинках.

В Excel можна за кілька секунд перенести дані, що записані горизонтально (у рядок), у вертикальний стовпчик і навпаки. Іноді це треба, щоб змінити структуру таблиці для презентації, іншим разом — аби проаналізувати дані або реструктуризувати скопійовану інформацію.

• Скопіюйте потрібні клітинки (Ctrl + C).
• Клацніть правою кнопкою миші на клітинку.
• Виберіть: Спеціальна вставка — Транспортувати.

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

Як використовувати? Додайте знак $ перед посиланням на клітинку. Для клітинки А1 ви можете зафіксувати рядок (A$1), стовпець ($A1), і рядок, і стовпець ($А$1).

Flash Fill — інтелектуальний алгоритм, що аналізує приклади, які ви вводите, розпізнає логіку та автоматично заповнює решту даних. Щоб отримати результат, треба натиснути Ctrl + E

Наприклад, ви маєте список електронних адрес працівників та хочете витягнути з них домени:

olena@gmail.com

maria@company.ua

hanna@1234.com.ua

Натисніть на сусідню з першою адресою клітинку, напишіть gmail.com, натисніть Ctrl + E і решта доменів підтягнеться автоматично.

Так можна розділити чи об’єднати текст, вибрати з нього частину інформації, змінити регістр, мову чи формат, додати валютні символи.

Збережіть дані у форматі PDF (Файл — Зберегти — Тип файлу (*pdf)), аби надсилати звіти партнерам і клієнтам, архівувати фінальні версії звітів, друкувати таблиці у якісному форматі та захистити дані від редагування. 

Максимальний розмір таблиці в Excel (з 2010 версії) становить 1048576 рядків на 16384 стовпців.

Одна з найпоширеніших операцій в Excel — розрахунок відсотків. Ця інформація потрібна, щоб визначити знижку чи націнку, комісії чи податки. 

• Відсоток від числа. Щоб дізнатися 20% від 5000, помножте: =5000*20% або =5000*0,2
• Частка від загального. Аби визначити, яку частку займає 2000 від 5000, порахуйте: 2000/5000*100
• Збільшити або зменшити на відсоток. Щоб збільшити 5000 на 20%, помножте 5000*1,2, а щоб зменшити — помножте 5000*0,8. Чому 1,2 та 0,8, тому що до 100% ми додаємо чи віднімаємо 20% відповідно.

Хочете підготувати звіт, що міститиме імена клієнтів, назви товарів чи артикули, які не повторюються? Використовуйте функцію:

=UNIQUE(A2:A100)

Щоб порахувати середній чек, визначити середню заробітну плату в компанії, проаналізувати середні продажі за період тощо, використовуйте формулу:

=AVERAGE(перша клітинка:остання клітинка)

Середнє значення для клітинок від А1 до А100 виглядатиме так =AVERAGE(A1:A100). Використайте AVERAGEIF, аби знайти значення з певними умовами. Наприклад, порахувати середнє значення для чисел більше ніж 100 =AVERAGEIF(A1:A100,">100").

Читайте також:

• 12 корисних сервісів для глибокого аналізу конкурентів;
• ШІ у бізнесі: як він змінює роботу, зарплати та вимоги для людей;
• One Drive: Як хмарне сховище полегшує щоденну роботу команди;
• Як розгорнути LLM-модель у Kyivstar Cloud: досвід, що надихає.