Основні функції та формули Excel і Google Sheets для бізнес-аналітиків

Дані відіграють ключову роль у прийнятті стратегічних рішень. Excel і Google Sheets — це потужні інструменти, які допомагають бізнес-аналітикам ефективно обробляти, аналізувати та представляти дані. Завдяки широкому спектру функцій і формул, ці програми дозволяють виконувати фінансові розрахунки, аналіз тенденцій, створювати прогнози та автоматизувати рутинні завдання.

У цій статті ми розглянемо основні функції та формули Excel і Google Sheets, які стануть у пригоді кожному бізнес-аналітику. Ви дізнаєтеся, як застосовувати ці інструменти для вирішення практичних завдань і оптимізації роботи з даними.

2. Функції для обробки тексту

Робота з текстовими даними є важливою частиною задач бізнес-аналітиків, особливо коли потрібно стандартизувати інформацію, виділяти ключові елементи або об’єднувати дані. Ось основні текстові функції в Excel і Google Sheets, їх використання та приклади:

---

CONCAT (або CONCATENATE)

Опис: Об’єднує кілька текстових значень в один рядок.
Синтаксис:
=CONCAT(text1, [text2], ...)

Приклад:
Якщо у вас є імена у стовпцях:

A	B
John	Smith

Для об’єднання імен у повне ім’я використовуйте:
=CONCAT(A1, " ", B1)

Результат: John Smith.

---

TEXT

Опис: Форматує числа або дати як текст.
Синтаксис:
=TEXT(value, format_text)

Приклад:
Якщо у вас є дата в комірці A1:

A
01/09/2025

Для перетворення дати у формат “Вересень 2025” використовуйте:
=TEXT(A1, "mmmm yyyy")

Результат: Вересень 2025.

---

LEFT, RIGHT, MID

Опис: Витягують частини тексту з рядка.

• LEFT: Витягує символи з початку рядка.
• RIGHT: Витягує символи з кінця рядка.
• MID: Витягує символи зі середини рядка.

Синтаксис:

• =LEFT(text, num_chars)
• =RIGHT(text, num_chars)
• =MID(text, start_num, num_chars)

Приклад:
Для номера рахунку:

A
INV-202301

• Щоб витягти код “INV”: =LEFT(A1, 3) — результат: INV.
• Щоб отримати рік: =MID(A1, 5, 4) — результат: 2023.

---

TRIM

Опис: Видаляє зайві пробіли з тексту, залишаючи лише один пробіл між словами.
Синтаксис:
=TRIM(text)

Приклад:
Для тексту:

A
John Smith

=TRIM(A1) поверне John Smith, усуваючи зайві пробіли.

---

LEN

Опис: Рахує кількість символів у текстовому рядку, включаючи пробіли.
Синтаксис:
=LEN(text)

Приклад:
Для тексту:

A
Analytics

=LEN(A1) поверне 9.

---

SPLIT (Google Sheets)

Опис: Розділяє текст на частини за заданим роздільником.
Синтаксис:
=SPLIT(text, delimiter)

Приклад:
Для тексту:

A
John,Smith,IT

=SPLIT(A1, ",") розділить текст на три частини:
| John | Smith | IT |

---

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

3. Функції для роботи з датами та часом

Аналіз даних, що залежать від часу, є невід’ємною частиною бізнес-аналітики. Excel і Google Sheets пропонують численні функції для роботи з датами та часом, що дозволяють автоматизувати обчислення, планувати завдання та аналізувати тимчасові тренди. Нижче наведено ключові функції з прикладами їх використання.

---

TODAY

Опис: Повертає поточну дату.
Синтаксис:
=TODAY()

Приклад:
Для відображення поточної дати в таблиці:
=TODAY()
Результат: 09.01.2025 (поточна дата).

Ця функція корисна для автоматичного оновлення звітів із датами.

---

NOW

Опис: Повертає поточну дату та час.
Синтаксис:
=NOW()

Приклад:
=NOW() поверне 09.01.2025 14:35 (дата й час).

Зручно для створення точних часових міток.

---

DATEDIF

Опис: Обчислює різницю між двома датами в днях, місяцях або роках.
Синтаксис:
=DATEDIF(start_date, end_date, unit)

• unit: “D” (дні), “M” (місяці), “Y” (роки).

Приклад:
Для визначення віку клієнта за датою народження:

A	B
Date of Birth	Today
01.01.2000	09.01.2025

=DATEDIF(A2, B2, "Y") поверне 25.

---

WORKDAY

Опис: Повертає дату завершення роботи через певну кількість робочих днів.
Синтаксис:
=WORKDAY(start_date, days, [holidays])

Приклад:
Якщо проєкт починається 09.01.2025 і триватиме 10 робочих днів:
=WORKDAY("09.01.2025", 10)
Результат: 23.01.2025 (з урахуванням вихідних).

---

TEXT для форматування дати

Опис: Дозволяє форматувати дату як текст.
Синтаксис:
=TEXT(date, format_text)

Приклад:
Для перетворення дати 09.01.2025 у формат “9 січня 2025 року”:
=TEXT(A1, "d mmmm yyyy")
Результат: 9 січня 2025 року.

---

EOMONTH

Опис: Повертає останній день місяця для заданої дати.
Синтаксис:
=EOMONTH(start_date, months)

Приклад:
Щоб знайти останній день місяця для 09.01.2025:
=EOMONTH("09.01.2025", 0)
Результат: 31.01.2025.

---

NETWORKDAYS

Опис: Обчислює кількість робочих днів між двома датами.
Синтаксис:
=NETWORKDAYS(start_date, end_date, [holidays])

Приклад:
Для підрахунку робочих днів між 01.01.2025 і 15.01.2025:
=NETWORKDAYS("01.01.2025", "15.01.2025")
Результат: 11 (без урахування вихідних).

---

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

4. Логічні функції для аналізу даних

Логічні функції в Excel і Google Sheets дозволяють створювати умови, автоматизувати прийняття рішень та виконувати обчислення залежно від заданих критеріїв. Це робить їх надзвичайно корисними для аналізу даних, побудови звітів і моделювання. Розглянемо основні логічні функції з прикладами.

---

IF

Опис: Виконує одну з двох дій залежно від виконання умови.
Синтаксис:
=IF(logical_test, value_if_true, value_if_false)

Приклад:
Для перевірки, чи продажі перевищують ціль:

A	B
Sales	Target
1200	1000

=IF(A2 > B2, "Target Met", "Target Missed")
Результат: Target Met.

---

AND

Опис: Повертає TRUE, якщо всі умови виконуються, і FALSE, якщо хоча б одна умова не виконується.
Синтаксис:
=AND(condition1, condition2, ...)

Приклад:
Для перевірки, чи продажі перевищують ціль і чи досягнуто мінімальної кількості угод:

Sales	Target	Deals	Min Deals
1200	1000	15	10

=AND(A2 > B2, C2 > D2)
Результат: TRUE.

---

OR

Опис: Повертає TRUE, якщо хоча б одна умова виконується, і FALSE, якщо жодна не виконується.
Синтаксис:
=OR(condition1, condition2, ...)

Приклад:
Перевірка, чи виконано хоча б одну з двох умов:
=OR(A2 > B2, C2 > D2)
Результат: TRUE.

---

NOT

Опис: Повертає протилежне значення заданої умови (TRUE стає FALSE і навпаки).
Синтаксис:
=NOT(logical)

Приклад:
Перевірка, чи ціль не досягнуто:
=NOT(A2 > B2)
Результат: FALSE.

---

IFS

Опис: Перевіряє кілька умов і повертає значення для першої виконаної умови.
Синтаксис:
=IFS(condition1, value1, condition2, value2, ...)

Приклад:
Для визначення категорії продажів:

Sales
1200

=IFS(A2 > 1000, "High", A2 > 500, "Medium", A2 <= 500, "Low")
Результат: High.

---

SWITCH (Excel)

Опис: Вибирає результат із кількох варіантів на основі заданого значення.
Синтаксис:
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])

Приклад:
Для класифікації продуктів за кодами:

Code
A

=SWITCH(A1, "A", "Category 1", "B", "Category 2", "C", "Category 3", "Unknown")
Результат: Category 1.

---

IFERROR

Опис: Повертає альтернативне значення, якщо формула викликає помилку.
Синтаксис:
=IFERROR(value, value_if_error)

Приклад:
Для обчислення співвідношення без помилок:

A	B
10	0

=IFERROR(A1/B1, "Error")
Результат: Error (оскільки ділення на нуль).

---

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

5. Статистичні функції для аналізу даних

Статистичні функції в Excel і Google Sheets дозволяють аналізувати великі обсяги даних, обчислювати середні значення, знаходити максимуми та мінімуми, а також виконувати складні статистичні розрахунки. Вони широко використовуються для виявлення тенденцій і проведення порівняльного аналізу. Розглянемо ключові статистичні функції з прикладами їх застосування.

---

AVERAGE

Опис: Обчислює середнє арифметичне числового діапазону.
Синтаксис:
=AVERAGE(number1, [number2], ...)

Приклад:
Для набору даних про продажі:

Sales
100
200
300

=AVERAGE(A1:A3)
Результат: 200.

---

MEDIAN

Опис: Знаходить медіану (середнє значення у впорядкованому наборі даних).
Синтаксис:
=MEDIAN(number1, [number2], ...)

Приклад:
Для набору даних:

Sales
100
200
300

=MEDIAN(A1:A3)
Результат: 200.

---

MODE

Опис: Повертає найчастіше повторюване значення в наборі даних.
Синтаксис:
=MODE(number1, [number2], ...)

Приклад:
Для даних:

Sales
100
200
100

=MODE(A1:A3)
Результат: 100.

---

MIN та MAX

Опис:

• MIN: Знаходить найменше значення в наборі даних.
• MAX: Знаходить найбільше значення в наборі даних.

Синтаксис:

• =MIN(number1, [number2], ...)
• =MAX(number1, [number2], ...)

Приклад:
Для даних:

Sales
100
200
300

=MIN(A1:A3) поверне 100,
=MAX(A1:A3) поверне 300.

---

COUNT та COUNTA

Опис:

• COUNT: Рахує кількість числових значень у діапазоні.
• COUNTA: Рахує кількість непустих комірок у діапазоні.

Синтаксис:

• =COUNT(value1, [value2], ...)
• =COUNTA(value1, [value2], ...)

Приклад:
Для даних:

Values
100
Text

=COUNT(A1:A3) поверне 1 (лише числові значення),
=COUNTA(A1:A3) поверне 2 (усі непусті значення).

---

STDEV

Опис: Обчислює стандартне відхилення набору даних.
Синтаксис:
=STDEV(number1, [number2], ...)

Приклад:
Для даних:

Sales
100
200
300

=STDEV(A1:A3) поверне стандартне відхилення значень.

---

RANK

Опис: Повертає ранг числа у наборі даних (порівняльна позиція).
Синтаксис:
=RANK(number, ref, [order])

Приклад:
Для набору:

Sales
300
200
100

=RANK(A1, A1:A3, 0) поверне 1 (найвище значення).

---

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

6. Фінансові функції для аналізу та прогнозування

Фінансові функції в Excel і Google Sheets є потужним інструментом для оцінки інвестицій, аналізу витрат і доходів, а також прогнозування фінансових показників. Вони особливо корисні для бізнес-аналітиків, які працюють з бюджетами, кредитами та фінансовим плануванням. Розглянемо основні функції та приклади їх використання.

---

PV (Present Value)

Опис: Обчислює поточну вартість інвестиції або кредиту на основі майбутніх платежів і ставки відсотків.
Синтаксис:
=PV(rate, nper, pmt, [fv], [type])

• rate: процентна ставка за період.
• nper: загальна кількість періодів.
• pmt: платіж за період.
• fv: майбутня вартість (опційно).
• type: 0 (платежі наприкінці періоду) або 1 (на початку).

Приклад:
Кредит на 10 000 одиниць з річною ставкою 5% на 5 років:
=PV(5%/12, 60, -200)
Результат: Поточна вартість позики.

---

FV (Future Value)

Опис: Обчислює майбутню вартість інвестиції на основі регулярних внесків і ставки відсотків.
Синтаксис:
=FV(rate, nper, pmt, [pv], [type])

Приклад:
Щомісячний внесок 200 одиниць при 5% річній ставці протягом 5 років:
=FV(5%/12, 60, -200)
Результат: Майбутня сума накопичень.

---

PMT

Опис: Розраховує розмір періодичного платежу для кредиту або інвестиції.
Синтаксис:
=PMT(rate, nper, pv, [fv], [type])

Приклад:
Кредит на 10 000 одиниць з річною ставкою 5% на 5 років:
=PMT(5%/12, 60, 10000)
Результат: Щомісячний платіж.

---

RATE

Опис: Визначає відсоткову ставку за період.
Синтаксис:
=RATE(nper, pmt, pv, [fv], [type], [guess])

Приклад:
Кредит на 10 000 одиниць, 60 місячних платежів по 200 одиниць:
=RATE(60, -200, 10000)
Результат: Місячна ставка.

---

NPV (Net Present Value)

Опис: Обчислює чисту теперішню вартість інвестиції на основі дисконтування грошових потоків.
Синтаксис:
=NPV(rate, value1, [value2], ...)

Приклад:
Грошові потоки:

Year	Cash Flow
1	1000
2	1500
3	2000

=NPV(10%, A2:A4)
Результат: Чиста теперішня вартість.

---

IRR (Internal Rate of Return)

Опис: Обчислює внутрішню норму прибутковості для інвестицій на основі грошових потоків.
Синтаксис:
=IRR(values, [guess])

Приклад:
Грошові потоки:

Year	Cash Flow
0	-5000
1	2000
2	3000

=IRR(A1:A3)
Результат: Внутрішня норма прибутковості.

---

XNPV і XIRR (точніше розраховані NPV та IRR)

Опис: Ці функції враховують точні дати для кожного грошового потоку.
Синтаксис:

• =XNPV(rate, values, dates)
• =XIRR(values, dates, [guess])

Приклад:

Date	Cash Flow
01/01/2025	-5000
01/07/2025	2000
01/01/2026	3000

=XNPV(10%, B1:B3, A1:A3)
Результат: Чиста теперішня вартість з урахуванням точних дат.

---

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

7. Візуалізація даних

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

---

Побудова графіків і діаграм

• Гістограми: Використовуються для аналізу розподілу даних.
  Приклад: Побудова гістограми продажів за кварталами в Excel або Google Sheets.
• Лінійні графіки: Ідеальні для демонстрації змін у часі, наприклад, росту доходу або витрат.
• Кругові діаграми: Відображають співвідношення часток, наприклад, розподіл бюджету між відділами.
• Стовпчасті діаграми: Показують порівняння даних між категоріями, наприклад, обсяги продажів у різних регіонах.

---

Використання умовного форматування для виділення ключових показників

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

---

8. Автоматизація роботи

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

---

Створення макросів в Excel

• Опис: Макроси — це набір команд, які автоматично виконуються для повторюваних задач.
• Застосування:
  • Автоматизація форматування великих таблиць.
  • Виконання складних обчислень за кілька кліків.
  • Експорт даних у потрібний формат.
    Приклад: Запис макросу для сортування таблиці за датами та видалення дублюючих рядків.

---

Використання Google Apps Script у Google Sheets

• Опис: Google Apps Script дозволяє створювати скрипти для автоматизації завдань у Google Sheets.
• Можливості:
  • Автоматичне оновлення таблиць із зовнішніх джерел.
  • Налаштування автоматичних нагадувань і сповіщень.
  • Генерація звітів і зведених таблиць.
    Приклад: Написання скрипту для автоматичного імпорту даних із CRM-системи в Google Sheets.

---

Висновок

Підсумок важливості опанування основних функцій

Робота з Excel і Google Sheets є невід’ємною частиною бізнес-аналітики. Опанування базових функцій і навичок дозволяє ефективніше аналізувати дані, автоматизувати процеси та приймати обґрунтовані рішення.

Поради щодо постійного вдосконалення навичок роботи з таблицями

• Регулярно вивчайте нові функції та можливості.
• Практикуйтесь на реальних даних для кращого розуміння.
• Звертайте увагу на автоматизацію та оптимізацію робочих процесів.

Рекомендації щодо додаткових ресурсів для навчання

• Онлайн-курси: Coursera, Udemy, EdX.
• Книги: “Excel Bible” Джона Вокенбаха, “Google Sheets Mastery”.
• Веб-ресурси: Офіційна документація Microsoft Excel і Google Sheets, блоги аналітиків.
• Форуми та спільноти: Stack Overflow, Reddit (групи про Excel і Google Sheets).

Це дозволить вам залишатися в тренді та підвищувати ефективність своєї роботи з даними.