ТОП 15 формул в Excel

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

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

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

Стало цікаво? Тоді ласкаво просимо до огляду найкорисніших 15 формул Excel.

Трохи термінології

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

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

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

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

А тепер перевіримо все це на практиці.

Формула 1: VLOOKUP

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

синтаксис

Це досить складна формула, яка має 4 аргументи, і її використання має багато особливостей.

Синтаксис:

=VLOOKUP(пошукове_значення, таблиця, номер_стовпця, [діапазон_пошуку])

Розглянемо докладніше всі аргументи:

1. Значення для пошуку.
2. Таблиця. Необхідно, щоб у першому стовпці було пошукове значення, а також значення, яке повертається. Останній розташований де завгодно. Користувач може самостійно вирішити, куди вставити результат формули. 
3. Номер колонки.
4. Інтервальний перегляд. Якщо це не потрібно, ви можете опустити значення цього аргументу. Це логічний вираз, що вказує на ступінь точності збігу, який має знайти функція. Якщо вказано параметр «True», то Excel шукатиме значення, найближче до того, яке вказано як значення пошуку. Якщо вказано параметр «False», то функція буде шукати тільки ті значення, які є в першому стовпці.

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

Формула 2: Якщо

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

синтаксис

Формула цієї функції має три основні аргументи і виглядає так:

=ЯКЩО(логічний_вираз, “значення_якщо_істина”, “значення_якщо_хибність”).

Тут логічний вираз означає формулу, яка безпосередньо описує критерій. З його допомогою дані будуть перевірятися на відповідність певній умові. Відповідно, аргумент «value if false» призначений для тієї ж задачі, з тією лише різницею, що він дзеркально протилежний за значенням. Простіше кажучи, якщо умова не підтверджується, то програма виконує певні дії.

Є інший спосіб використання функції IF – вкладені функції. Умов тут може бути набагато більше, до 64. Приклад міркування, що відповідає формулі, наведеній на скріншоті, такий. Якщо комірка A2 дорівнює двом, то потрібно вивести значення «Так». Якщо воно має інше значення, то потрібно перевірити, чи дорівнює клітинка D2 двом. Якщо так, то потрібно повернути значення «ні», якщо тут умова виявляється хибною, то формула повинна повернути значення «можливо».

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

функція IF також можна використовувати, щоб визначити, чи порожня певна комірка. Для досягнення цієї мети потрібно використати ще одну функцію − ІСБЛАНК.

Ось синтаксис:

=IF(ISBLANK(номер комірки),”Пусто”,”Не пусто”).

Крім того, можна використовувати замість функції ІСБЛАНК застосувати стандартну формулу, але вказати, що припускаючи, що в комірці немає значень.

ЯКЩО – це одна з найпоширеніших функцій, яка дуже проста у використанні, і вона дозволяє зрозуміти, наскільки вірні певні значення, отримати результати за різними критеріями, а також визначити, чи є певна комірка порожньою.

Ця функція є основою для деяких інших формул. Зараз ми розберемо деякі з них докладніше.

Формула 3: SUMIF

функція СУММЕСЛІ дозволяє узагальнити дані за умови їх відповідності певним критеріям.

синтаксис

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

=SUMIF(діапазон, умова, [діапазон_суми])

Розберемося докладніше, що собою являє кожен з аргументів:

1. Хвороба. Цей аргумент дозволяє передавати у функцію комірки, які надалі підлягають підсумовуванню.
2. Діапазон підсумовування. Цей аргумент необов’язковий і дозволяє вказати комірки для підсумовування, якщо умова хибна.

Отже, у цій ситуації Excel узагальнив дані за тими запитами, де кількість переходів перевищує 100000 XNUMX.

Формула 4: SUMMESLIMN

Якщо умов декілька, то використовується пов’язана функція SUMMESLIMN.

синтаксис

Формула цієї функції виглядає так:

=SUMIFS(діапазон_сумування, діапазон_умови1, умова1, [діапазон_умови2, умова2], …)

Потрібні другий і третій аргументи, а саме «Діапазон умови 1» і «Діапазон умови 1».

Формула 5: COUNTIF і COUNTIFS

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

синтаксис

Для входу в цю функцію необхідно вказати таку формулу:

= COUNTIF (діапазон, критерії)

Що означають наведені аргументи?

1. Діапазон — це набір комірок, серед яких ведеться підрахунок.
2. Критерії – умова, яка враховується при відборі комірок.

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

Якщо говорити про пов’язану функцію КОНКУРСИ, то в ньому, як і в попередньому прикладі, передбачена можливість використання відразу кількох критеріїв. Його формула така:

=COUNTIFS(діапазон_умови1, умова1, [діапазон_умови2, умова2],…)

І подібно до попереднього випадку, «Діапазон умов 1» і «Умова 1» є обов’язковими аргументами, тоді як інші можна опустити, якщо такої необхідності немає. Максимальна функція забезпечує можливість застосування до 127 діапазонів разом з умовами.

Формула 6: ЯКЩО ПОМИЛКА

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

синтаксис

Ця функція має два аргументи. Синтаксис такий:

=ЯКЩОПОМИЛКА(значення;значення_якщо_помилка)

Опис аргументів:

1. Значенням є сама формула, перевірена на помилки.
2. Значення if error — це результат, який з’являється після виявлення помилки.

Якщо говорити про приклади, то ця формула покаже текст «Помилка в обчисленні», якщо ділення неможливо.

Формула 7: ЛІВО

Ця функція дозволяє вибрати необхідну кількість символів зліва від рядка.

Його синтаксис такий:

=ЛІВИЙ(текст;[кількість_символів])

Можливі аргументи:

1. Текст – рядок, з якого потрібно отримати певний фрагмент.
2. Кількість символів безпосередньо означає кількість символів, які потрібно витягти.

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

Формула 8: PSTR

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

Його синтаксис такий:

=MID(текст,початкова_позиція,кількість_символів).

Розширення аргументу:

1. Текст - це рядок, який містить необхідні дані.
2. Початкова позиція - це безпосередньо позиція того символу, який служить початком для вилучення тексту.
3. Кількість символів – кількість символів, яку формула повинна витягти з тексту.

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

Формула 9: ПРОПІСН

Ця функція робить великі літери, які містяться в певному рядку. Його синтаксис такий:

=ПОТРІБНО(текст)

Є тільки один аргумент – сам текст, який буде оброблено. Ви можете використовувати посилання на клітинку.

Формула 10: НИЖЧЕ

По суті, це обернена функція, яка перетворює кожну літеру в нижній регістр тексту або клітинки.

Його синтаксис схожий, є лише один аргумент, що містить текст або адресу клітинки.

Формула 11: ПОШУК

Ця функція дозволяє знайти потрібний елемент серед діапазону комірок і задати його положення.

Шаблон для цієї формули:

=MATCH(пошукове_значення, пошуковий_масив, тип_збігу)

Перші два аргументи є обов’язковими, останній необов’язковий.

Є три способи зіставлення:

1. Менше або дорівнює 1.
2. Точно – 0.
3. Найменше значення, що дорівнює або перевищує -1.

У цьому прикладі ми намагаємося визначити, за яким із ключових слів відбувається до 900 кліків включно.

Формула 12: DLSTR

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

Його синтаксис схожий на попередній:

=DLSTR(текст)

Так, його можна використовувати для визначення довжини опису статті при SEO-просуванні сайту.

Це також добре поєднувати з функцією IF.

Формула 13: ПІДКЛЮЧАЙТЕСЯ

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

Синтаксис:

=CONCATENATE(текст1,текст2,текст3);

Формула 14: ПРОПНАЧ

Ця функція міняє місцями великі та малі символи.

Синтаксис дуже простий:

=ПРОПЛАН(текст)

Формула 15: ДРУК

Ця формула дозволяє видалити зі статті всі невидимі символи (наприклад, розриви рядків).

Його синтаксис такий:

=PRINT(текст)

В якості аргументу можна вказати адресу комірки.

Висновки

Звичайно, це не всі функції, які використовуються в Excel. Ми хотіли представити такі, про які пересічний користувач електронних таблиць не чув або рідко використовує. Статистично найпоширенішими функціями є обчислення та отримання середнього значення. Але Excel — це більше, ніж просто програма для роботи з електронними таблицями. У ньому можна автоматизувати абсолютно будь-які функції. 

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