В Excel є тисячі вбудованих функцій. Якщо знати хоча б частину з них, можна добряче економити час при обробці даних та складанні звітів.
У цій статті зібрали 10 базових функцій, які найчастіше використовуються в Екселі.
#1. SUM
Синтаксис: =SUM(число1; [число 2]; ...)
число1 — обов'язковий аргумент.
Функція дає можливість визначити суму окремих числових значень, діапазонів, посилань на клітинки з числовими значеннями чи суму всіх цих трьох видів. Часто використовується у підбитті підсумкових значень рядків або стовпців для формування звітів.
Приклади використання
Допустимо, у вас є масив числових значень і вам потрібно порахувати суму деяких із них. Використовуючи функцію SUM, в частині аргументів функції ми підставляємо потрібні посилання на клітинки та отримуємо відповідь — 322.
У деяких випадках в масивах немає значень, які потрібно так само підсумувати, і замість посилань можемо додати свої числа. Відповідь у цьому разі — 293.
Оскільки функція працює не тільки з числовими значеннями, а з цілими діапазонами, можна знайти суму всього діапазону.
Можна не обмежувати себе у кількості значень, які потрібно підсумувати, а порахувати всі значення у стовпцях чи рядках. Наприклад, підсумуємо всі значення в перших двох стовпцях.
Якщо в одному або кількох клітинках діапазону виявиться не числове значення, а текстове, Excel прирівнюватиме ці значення до нуля.
#2. COUNT
Синтаксис: =COUNT(значення1; [значення2]; ...)
значення1 — обов'язковий аргумент.
За допомогою цієї функції можна підрахувати клітинки, які містять лише числові значення у списку аргументів. Найчастіше використовують у розрахунках середніх значень, коли використання функції AVERAGE в Excel недоцільно.
Наступна формула повертає кількість клітинок у діапазоні A1:E1, які містять числа.
Як бачимо, у списку аргументів діапазон із п'яти значень, але функція поверне три, адже числові значення містяться лише у стовпцях A, B, C. У стовпці D — текст, а Е — незаповнена клітинка.
Близькі до застосування функції:
=COUNTA(значення1;[значення2];…) — рахує кількість непорожніх значень у переліку аргументів.
=COUNTBLANK(діапазон) — рахує кількість порожніх значень у вказаному діапазоні.
#3. MIN
Синтаксис: = MIN(число 1; [число 2]; ...)
число1 — обов'язковий аргумент.
Функція дозволяє знайти мінімальне числове значення у вказаному списку аргументів. Часто використовується при формуванні фінансової звітності, коли потрібно визначити дату початку періоду звіту, мінімальний чек покупки та інші параметри.
Приклад
Допустимо, у нас є діапазон чисел та текстових виразів, і потрібно знайти мінімальне значення.
Наприклад, мінімальне значення серед двох вибраних діапазонів A1:D2, A4:D4 та числа 54 буде 2. Порожні поля та текстові вирази функцією виключаються та у розрахунках не використовуються.
Близькі за призначенням функції
=MINA(значення1;[значення2];…) — знаходить мінімальне значення у списку аргументів, при цьому текстові та хибні логічні вирази дорівнюють нулю, а логічний вираз TRUE в клітинці дорівнює 1.
=MAX(число1;[число2];…) — знаходить максимальне значення у списку аргументів, при цьому текстові та порожні вирази ігноруються.
=MAXA(значення1;[значення2];…) — знаходить максимальне значення у списку аргументів, при цьому текстові і хибні логічні вирази прирівнюються до нуля, а логічний вираз TRUE у клітинках дорівнює 1.
#4. AVERAGE
Синтаксис: =AVERAGE(число1;[число2];…)
число1 — обов'язковий аргумент.
За допомогою цієї функції можна знайти середнє арифметичне окремих числових значень, діапазонів, посилань на клітинки з числовими значеннями або середнє цих 3-х видів. Обчислюється шляхом підсумовування всіх чисел та розподілом суми на кількість цих чисел. Текстові та логічні значення в діапазоні ігноруються.
Припустимо, у нас є діапазон із 6 клітинок: 4 з них заповнені числами, включаючи 0, одне значення — текстове та ще одне — порожнє. Функція підсумує лише числові та ділить суму на загальну кількість числових — 4.
У підсумку ми отримаємо середнє, що дорівнює 4. Перевіримо за формулою:
(4 + 5 + TEКСT + 7 + 0 + Порожнє значення) / 4 = 16 / 4 = 4
TEКСT та Порожнє значення ігноруються.
#5. ROUND
Синтаксис: =ROUND(число; число_розрядів)
число — аргумент.
число_розрядів — до якого розряду округлюється число.
Функція ROUND застосовується для округлення дійсних чисел до необхідної кількості знаків після коми та повертає округлене значення згідно з математичним правилом округлення.
Наприклад, для округлення числа 2,57525 до 2 символів після коми можна ввести формулу =ROUND(2,57525;2), яка поверне значення 2,58. Ця функція часто використовується при складанні балансових та інших видів звітності.
#6. IF
Синтаксис: =IF(логічний_вираз; значення_якщо_істина; значення_якщо_хибність)
логічний_вираз — умова, виконання якої перевіряє оператор.
значення_якщо_істина — у разі, якщо умова виявиться правдивою, буде повернено це значення.
значення_якщо_хибність — у разі, якщо умова виявиться неправдивою, буде повернено це значення.
Ця функція одна з найвідоміших у роботі з Екселем. У ній перевіряються числа та/або текст, функції, формули. Коли значення відповідають заданій умові, з'являється запис із поля значення_якщо_істина, не відповідають — значення_якщо_хибність. Часто використовується для розподілу виразів на категорії, групи.
Функція підтримує використання операторів порівняння:
= (дорівнює),
< (менше),
<= (менше або дорівнює),
> (більше),
>= (більше або дорівнює),
<> (не дорівнює).
Також часто використовують цю функцію у зв'язці з логічними операторами AND, OR.
Розглянемо декілька прикладів.
Приклад 1
Припустимо, що ми маємо таблицю вартості продуктів харчування за 1 кг (л), і завдання полягає в тому, щоб розбити товари на дві групи залежно від ціни:
– до 500
– більше 500
Для розв’язання цього завдання використовується функція IF.
У клітинці C2 наступна формула: =IF(B2<=500;"до 500";"більше 500"). Вона розшифровується так: якщо у зазначеній клітинці число буде менше або дорівнює 500 (1 на скриншоті), тоді функція повинна повернути текстовий вираз "до 500" (2 на скриншоті), якщо він виявиться іншим — "більше 500" (3 на скриншоті).
Протягнемо цю формулу вниз і перевіримо працездатність.
Приклад 2
Припустимо, що у таблиці вище нам необхідно кожному товару присвоїти категорію за ціною: "висока вартість" і "низька вартість".
У клітинці D2 була прописана така формула: =IF(С2="до 500"; "низька вартість"; "висока вартість"). Розшифровка: якщо у вказаній клітинці текстове значення дорівнює "до 500", тоді функція повинна повернути текстовий вираз "низька вартість", якщо він виявиться іншим — "висока вартість".
Зверніть увагу: коли перевіряєте умови на текстове значення, це значення в лапках необхідно брати ще одні лапки. Подивимося результат, протягнувши формулу до кінця таблиці.
#7. VLOOKUP
Синтаксис: =VLOOKUP(шукане_значення; таблиця; номер_стовпця; [інтервальний_перегляд])
шукане_значення — значення, яке необхідно знайти в стовпці з даними. Аргументи бувають числовими та текстовими. Шукане значення має перебувати у крайньому лівому стовпці діапазону клітинок зазначеної таблиці.
таблиця — посилання на діапазон клітинок. У лівому стовпці виконується пошук шуканого значення, а зі стовпців праворуч виводиться відповідне значення. Лівий стовпець ще називають ключовим. Якщо в таблиці не виявиться шуканого значення, повернеться помилка #N/A.
номер_стовпця — номер стовпця таблиці, згідно з яким потрібно вивести результат.
[інтервальний_перегляд] — необов'язковий аргумент. Приймає два значення: TRUE та FALSE. TRUE встановлюється за замовчуванням, і функція передбачає, що лівий стовпець таблиці відсортовано за зростанням в алфавітному порядку. Якщо в цьому аргументі — TRUE, функція шукає найближче до шуканого або значення, що збігається з ним, FALSE— шукає стовідсотковий збіг з шуканим значенням.
Це функція, яка спростить роботу з великими масивами даних та кількома таблицями. Буде корисною, якщо потрібно підтягнути відповідний критерію стовпець з іншої таблиці (наприклад, група, категорія).
Приклад
Повернемося до таблиці з пункту 6 (IF). Припустимо, що кожному предмету купівлі привласнили групу, до якої він належить (овочі, фрукти, кондитерські вироби та ін.), і ми маємо довідник відповідності, але в іншій таблиці. У масивах даних, що складаються з тисяч рядків, робити це вручну дуже довго, а VLOOKUP впорається за секунду.
У клітинці E2 пишемо формулу: =VLOOKUP(A2;$G$2:$H$20;2;FALSE). Обов'язково ставимо знаки фіксації діапазону $ — у разі їх відсутності при протягуванні формули вниз діапазон клітинок таблиці також буде зміщуватися вниз разом з шуканим значенням.
Як прочитати формулу простою мовою: знайти значення з клітинки A2 в лівому стовпці таблиці G2:H20 і вивести відповідний знайденому шуканому_значенню вираз зі стовпця 2 цієї таблиці, при цьому шукане значення повинно збігатися з даними в лівому стовпці таблиці (аргумент FALSE).
Простягаємо формулу вниз — і виводиться результат.
#8. IFERROR
Синтаксис: =IFERROR(значення; значення_якщо_помилка)
значення — аргумент, який перевіряється на наявність помилки
значення_якщо_помилка — значення, яке повертається, якщо сталася помилка.
Ця функція перевіряє аргумент на наявність помилок #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, або #NULL!. Якщо вираз у клітинці, який перевіряється, містить помилку, функція поверне значення, яке визначено в цьому випадку. Якщо помилки немає — результат розрахунків або дані клітинки. Часто використовується при діленні на нуль.
Приклад
Візьмемо таблицю зі значеннями виторгу та кількістю проданих одиниць. Потрібно знайти виторг за одну одиницю продукції. Для розв’язання цього завдання виторг ділимо на кількість одиниць.
Excel свариться, тому що в одному виразі виконується ділення на нуль. Тому перевіряємо на помилку в сусідньому стовпці.
#9. TRIM
Синтаксис: = TRIM(текст)
текст — текстове значення, з якого потрібно видалити зайві пробіли.
Цю функцію використовують для обробки текстів із різних джерел. Коли ці тексти мають зайві пробіли, вони видаляються.
Приклад
Формула =TRIM(" Виторг з початку року ") поверне "Виторг з початку року", прибравши з виразу зайві пробіли.
За допомогою функції можна видалити зайві знаки пробілу з тексту (код символу — 32). У деяких випадках у тексті може бути знак нерозривного пробілу (код — 160). Щоб видалити ці зайві знаки, потрібно використовувати таку формулу:
=TRIM(SUBSTITUTE(A1;CHAR(160);CHAR(32))).
У такому разі функція спочатку замінює всі знаки нерозривного пробілу на пробіл, а потім видаляє зайві пробіли.
#10. CONCATENATE
Синтаксис: =CONCATENATE(текст1;[текст2];[текст3];…)
текст1 — обов'язковий аргумент.
Для об'єднання значень різних клітинок в одну використовується функція CONCATENATE. Також можна застосовувати аналог — & (амперсанд). Функція часто використовується для об'єднання даних із кількох стовпців.
Приклад
Припустимо, у нас є таблиця працівників з розділеними ПІБ, посадою та віком, а у звіті потрібно відобразити всі ці значення в одній клітинці. Для виконання цього завдання використовуємо функцію так:
Бажаєте отримувати дайджест статей?
