Зрозумілі формули Excel і функції для початківців та профі

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

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

Секрет успіху Excel – у формулах та функціях. Саме вони є «цеглинками», з яких складаються складні таблиці з багаторівневими обчисленнями.

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

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

Найкращі репетитори з Excel вільні зараз

5 (3 відгуки)

Ірина

450₴

/год

1-^ий урок безкоштовно!

5 (1 відгуки)

Mohamed

1737₴

/год

1-^ий урок безкоштовно!

5 (3 відгуки)

Ірина

450₴

/год

1-^ий урок безкоштовно!

5 (1 відгуки)

Mohamed

1737₴

/год

1-^ий урок безкоштовно!

Поїхали!

Що таке формули і функції?

Якщо ви не користуєтеся формулами і функціями в Excel, то фактично ви і самим Excel не користуєтеся.

Багато початківців починають із простих «сіткових» проектів, наприклад, створюють календар, і впевнені, що їм не потрібні ніякі математичні рівняння. І заповнюють комірки вручну…

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

Вони не лише допоможуть краще зрозуміти можливості Excel, а й суттєво підвищать вашу ефективність. І так – навіть календар можна створити саме за допомогою формул і функцій!

Що таке формула?

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

Вони здебільшого пов’язані з базовими математичними діями – додаванням, відніманням, множенням та діленням.

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

Наприклад, формула може виглядати так: =1+2+3 або =B1-B2.

Що таке функція?

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

Це особливо зручно, коли потрібно знайти суму для великої кількості комірок з даними.

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

Як використовувати формули і функції в Excel

Щоб ввести формулу або функцію в Excel, спершу потрібно вибрати комірку, у якій відображатиметься результат.

Далі, у рядку формул Excel просто введіть знак = і після нього потрібну формулу чи функцію. Підтвердьте введене, натиснувши Enter/Return на клавіатурі, і Excel автоматично виконає задані інструкції в обраній клітинці.

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

А якщо ні, ви можете створити функції, визначені користувачем (тобто вами), вони ще відомі як User Defined Functions (UDF). Для цього використайте мову програмування Visual Basic for Applications (VBA) всередині Excel.

Найкращі репетитори з Excel вільні зараз

5 (3 відгуки)

Ірина

450₴

/год

1-^ий урок безкоштовно!

5 (1 відгуки)

Mohamed

1737₴

/год

1-^ий урок безкоштовно!

5 (3 відгуки)

Ірина

450₴

/год

1-^ий урок безкоштовно!

5 (1 відгуки)

Mohamed

1737₴

/год

1-^ий урок безкоштовно!

Поїхали!

Як створювати формули в Excel

Створення формули в Excel – одне з найлегших завдань. По суті, вам потрібно лише знати базову математику!

Є два способи ввести змінні у формулу:

• ввести конкретні числа, з якими ви хочете працювати;
• використати посилання/адреси комірок, значення яких потрібно обчислити.

Ці два методи можна комбінувати. Наприклад, формула =((A2*B2)/2) означає, що навіть якщо значення у клітинках A2 та B2 зміняться, результат усе одно буде ділитися на 2.

Як ввести формулу в Excel

Як уже зазначалося, формулу потрібно вводити у рядку формул у верхній частині робочого аркуша Excel. Починайте формулу зі знака =, а далі вводьте вашу формулу в дужках.

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

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

Якщо у формулі не використані абсолютні посилання, Excel автоматично змінюватиме посилання на клітинки.

Як видалити формули в Excel

Формули легко видалити – просто клікніть на клітинку, яка їх містить, і натисніть клавішу Delete. Якщо ви випадково видалили формулу, просто натисніть Ctrl+Z, щоб скасувати дію та повернути формулу!

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

Як створювати функції в Excel

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

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

Наприклад, проста функція SUM записується так: =SUM(D1+D2)

Щоб скласти значення кількох комірок, можна використати двокрапку між першою та останньою коміркою діапазону.

Наприклад: =SUM(D1:D50)

Якщо потрібно обчислити значення з кількох стовпців, посилання на діапазони можна розділити комою.

Приклад: =SUM(D1:D50,H3:H25)

Як створювати функції, визначені користувачем, в Excel

Excel містить понад 450 вбудованих функцій, але якщо вам потрібної функції ще немає, ви можете створити власну.

Користувачі можуть створювати User Defined Functions (UDF) за допомогою вбудованої мови програмування Visual Basic.

Створення власних функцій – навичка від середнього до просунутого рівня.

Ось як ввести власну функцію:

1. Натисніть Alt+F11 (Windows) або Fn + Opt + F11 (Mac), щоб відкрити редактор VBA в Excel. Або перейдіть на вкладку Розробник і натисніть Visual Basic.
2. Клікніть правою кнопкою миші на будь-якому елементі у вікні Огляд проєкту, потім оберіть Вставити > Модуль, щоб додати новий модуль.
3. Напишіть код UDF, використовуючи синтаксис VBA.
4. Закрийте редактор.

Щоб писати власний код, вам потрібно більше вивчати VBA – це окрема тема, але саме так ви вводите власні функції, коли вони потрібні!

Перелік формул Excel: найкорисніші формули

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

Найкорисніші формули електронних таблиць Excel

SUM

Призначення: Складає числа у зазначеному діапазоні клітинок або списку значень.
Синтаксис: SUM(number1, number2, ...)

AVERAGE

Призначення: Обчислює середнє значення у діапазоні або списку.
Синтаксис: AVERAGE(number1, number2, ...)

COUNT

Призначення: Підраховує кількість клітинок у діапазоні, що містять числа.
Синтаксис: COUNT(value1, [value2], ...)

IF

Призначення: Виконує умовну логіку на основі заданої умови.
Синтаксис: IF(logical_test, value_if_true, value_if_false)

SUBTOTAL

Призначення: Виконує різні агрегатні функції (наприклад, SUM, AVERAGE) для діапазону, ігноруючи інші функції SUBTOTAL у цьому діапазоні.
Синтаксис: SUBTOTAL(function_num, ref1, [ref2], ...)

CONCATENATE (або CONCAT)

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

UPPER

Призначення: Перетворює текст на великі літери.
Синтаксис: UPPER(text)

LOWER

Призначення: Перетворює текст на малі літери.
Синтаксис: LOWER(text)

PROPER

Призначення: Робить великою першу літеру кожного слова в текстовому рядку.
Синтаксис: PROPER(text)

NOW

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

TODAY

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

DATEDIF

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

RANGE

Призначення: Обчислює різницю між мінімальним і максимальним значенням у заданому діапазоні комірок із даними.
Синтаксис: RANGE(cell_range)

Інші популярні функції – VLOOKUP і HLOOKUP, які замінили старішу функцію LOOKUP із попередніх версій Excel. Різниця між LOOKUP та V/HLOOKUP незначна, але важлива. Оригінальна функція LOOKUP занадто загальна, тому і застаріла, а Microsoft рекомендує використовувати більш специфічні V та H LOOKUP.

VLOOKUP (Вертикальний пошук)

Призначення: VLOOKUP шукає значення в першому стовпці таблиці та повертає відповідне значення з того ж рядка з іншого стовпця.
Синтаксис: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Приклад використання: Часто застосовується для вертикального пошуку, наприклад, для отримання інформації з таблиці за певним ідентифікатором.

HLOOKUP (Горизонтальний пошук) працює так само, як VLOOKUP, але по рядках (горизонтально).

Як користуватися функцією IF

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

Функція IF використовується для умовної логіки, дозволяючи виконувати різні дії залежно від того, чи є задана умова істинною чи хибною.

Проста функція IF може виглядати так:

=IF(A1>10, "Greater than 10", "Less than or equal to 10")

Функцію IF можна поєднувати з багатьма іншими функціями, щоб створювати такі функції, як SUMIF, COUNTIF, AVERAGEIF та багато інших.

Таким чином ви можете наказати Excel виконувати основну функцію, якщо змінні відповідають певним умовам.

Наприклад:

=COUNTIF(C1:C10, "Category1")

Ця формула підраховує кількість входжень у діапазоні C1:C10, де значення дорівнює "Category1". Звичайна функція COUNT рахувала б усі випадки значень у C1–C10, що відповідають заданому числу або тексту.

Ось корисний список інших функцій Excel, які можуть стати в пригоді!

Функція	Що робить	Синтаксис	Приклад
AND	Повертає TRUE (істина), якщо всі її аргументи є TRUE.	AND(argument)	=AND(A1>5, B1<10) Формула поверне TRUE, якщо значення в клітинці A1 більше 5 і значення в B1 менше 10; інакше поверне FALSE.
CHOOSE	Використовується для вибору одного з максимум 254 значень на основі номера-індексу. Наприклад, якщо value1 до value7 – це дні тижня, функція CHOOSE повертає один із днів, коли як index_num вказане число від 1 до 7.	CHOOSE(index_num, value1, [value2], ...)	=CHOOSE(3, "Понеділок", "Вівторок", "Середа", "Четвер", "П’ятниця") Формула поверне "Середа", бо індекс 3 відповідає третьому значенню у списку.
CLEAN	Видаляє з тексту всі непечатні символи.	CLEAN(text)	=CLEAN(A1) – видаляє непечатні символи з вмісту клітинки A1.
CONVERT	Перетворює число з однієї системи вимірювання в іншу.	CONVERT(number,from_unit,to_unit)	=CONVERT(10,"lbm","kg") – конвертує 10 фунтів у кілограми.
CUMIPMT	Повертає сумарний відсоток, сплачений між двома періодами.	CUMIPMT(int_rate, #_of_pmt_periods, present_value, start_period, end_period, timing_of_pmt)	=CUMIPMT(0.05/12, 60, 30000, 1, 12, 0) – обчислює загальну суму відсотків, сплачених за перший рік кредиту у 30 000 грн при річній ставці 5% і терміні 60 місяців.
CUMPRINC	Повертає суму основного боргу, сплаченого за кредитом між двома періодами.	CUMPRINC(int_rate, #_of_pmt_periods, present_value, start_period, end_period, timing_of_pmt)	=CUMPRINC(0.05/12, 60, 20000, 1, 12, 0) У цьому випадку формула поверне суму основного боргу, яку буде сплачено за перший рік кредиту у 20 000 грн під 5% річних на 5 років.
DAYS	Використовується для обчислення кількості днів між двома датами.	DAYS(end_date, start_date)	=DAYS("2025-12-31", "2025-01-01") Функція поверне 364, тобто кількість днів між 1 січня та 31 грудня 2025 року.
EXACT	Перевіряє, чи два текстові значення ідентичні.	EXACT(text1, text2)	=EXACT("Excel", "excel") Функція поверне FALSE, бо регістр літер різний.
FLOOR	Округлює число вниз, у бік нуля.	FLOOR(number_to_round, place_to_round_to)	=FLOOR(7.8, 1) Функція поверне 7, тобто округлює число 7.8 вниз до найближчого кратного 1.
FORECAST	Обчислює або прогнозує майбутнє значення на основі наявних даних.	FORECAST(x, known_y's, known_x's)	=FORECAST(10, B1:B5, A1:A5) Функція прогнозує значення для x = 10 на основі відомих значень у діапазонах A1:A5 (x) та B1:B5 (y).
HYPERLINK	Створює ярлик або посилання, що відкриває документ, збережений на мережевому сервері, інтранеті або в Інтернеті.	HYPERLINK(link_location, [friendly_name])	=HYPERLINK("https://www.example.com", "Відкрити сайт") Функція створює клікабельне посилання з назвою "Відкрити сайт", що веде на веб-адресу https://www.example.com.
INDEX	Використовується для повернення значення або посилання на значення з таблиці чи діапазону.	INDEX(array, row_num, [column_num])	=INDEX(A1:C3, 2, 3) Функція поверне значення з другої строки та третього стовпця діапазону A1:C3.
IPMT	Повертає суму відсотків, сплачених за інвестицією або кредитом у певному періоді.	IPMT(rate, period, #_of_pmt_periods, present_value, [future_value], [due_date])	=IPMT(0.05/12, 1, 60, 20000) Функція обчислює суму відсотків за перший місяць кредиту у 20 000 грн під 5% річних на 5 років.
ISREF	Повертає TRUE, якщо значення є посиланням на клітинку.	ISREF(value)	=ISREF(A1) Функція поверне TRUE, якщо A1 містить посилання на клітинку, і FALSE, якщо це просте значення або текст. Фактично, це захисний інструмент для перевірки структури даних у Excel.
MATCH	Використовується для пошуку елемента в діапазоні клітинок та повертає відносну позицію цього елемента в діапазоні.	MATCH(lookup_value, lookup_array, [match_type])	=MATCH(50, A1:A10, 0) Функція поверне позицію числа 50 у діапазоні A1:A10. Параметр 0 означає точний збіг.
MODE	Повертає найчастіше значення у наборі даних.	MODE(number1,[number2],...)	=MODE(A1:A10) Функція поверне число, яке найчастіше зустрічається в діапазоні A1:A10.
RANK	Повертає ранг числа у списку чисел.	RANK(number,ref,[order])	=RANK(85, B1:B10, 0) Функція поверне позицію числа 85 у діапазоні B1:B10 за спаданням (0 означає спадання, 1 – зростання).
ROMAN	Перетворює арабське число у римське, як текст.	ROMAN(number)	=ROMAN(2025) Функція поверне "MMXXV", тобто римське позначення числа 2025.

Яка функція вам найбільше припала до душі?

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

Проте з терпінням і практикою ви точно опануєте нові можливості Excel. І незчуєтеся, як станете експертом!

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

Готові почати створювати дивовижні системи обробки даних в Excel?