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

Основні формули обчислень

🔢1. SUM (СУММ) — додає всі числа в діапазоні.

🧮Формула: =SUM(A1:A9). Вона додає значення з комірок A1 до A9. Використовується для обчислення загальної суми чисел у вказаному діапазоні. Наприклад, якщо у вас є суми продажів за кілька днів, ви можете обчислити загальну суму.

🔢2. AVERAGE (СРЗНАЧ) — обчислює середнє значення чисел у діапазоні.

🧮Формула: =AVERAGE(B1:B9). Обчислює середнє значення для всіх чисел у діапазоні B1 до B9. Застосовується для визначення середнього значення даних, таких як середній дохід або середня оцінка.

🔢3. ROUND (ОКРУГЛ) — округлює число до заданої кількості знаків після коми.

🧮Формула: =ROUND(C2, 2). Округлює значення в C2 до двох знаків після коми. Це зручно для формату чисел, де потрібно обмежити точність, наприклад, при фінансових розрахунках.

🔢4. MIN (МИН) — знаходить найменше число в діапазоні.

🧮Формула: =MIN(D1:D9). Виводить мінімальне значення в діапазоні D1 до D9. Може бути використана для пошуку найменшого значення в наборі даних, наприклад, найменшої ціни товару.

🔢5. MAX (МАКС) — знаходить найбільше число в діапазоні.

🧮Формула: =MAX(D1:D9). Виводить максимальне значення в діапазоні D1 до D9. Застосовується для пошуку найвищого значення, наприклад, максимального доходу або найбільшої кількості одиниць проданого товару.

🔢6. COUNT (СЧЁТ) — підраховує кількість клітинок, що містять числа.

🧮Формула: =COUNT(F1:F9). Підраховує кількість числових значень у діапазоні F1 до F9. Це допомагає, якщо потрібно порахувати кількість значень у наборі даних.

Пошук і посилання

🔢7. HLOOKUP (ГПР) — шукає значення в рядку та повертає відповідь з іншого рядка.

🧮Формула: =HLOOKUP(J13, J1:O2, FALSE). Шукає значення в J13 і повертає відповідь з таблиці. Це використовується для пошуку даних у горизонтальних масивах або таблицях.

🔢8. XLOOKUP (ХПР) — шукає значення в одному діапазоні і повертає результат з іншого діапазону.

🧮Формула: =XLOOKUP(A7, A2:A4, B2:C4). Шукає значення в A7 і повертає результат з другого діапазону. Це більш універсальна версія VLOOKUP і HLOOKUP.

🔢9. INDEX (ИНДЕКС) — повертає значення з таблиці, використовуючи номер рядка і стовпця.

🧮Формула: =INDEX(K1:L3, 2, 3). Повертає значення з другого рядка і третього стовпця в діапазоні K1:L3. Це зручно для вибору конкретних значень за допомогою координат.

🔢10. MATCH (ПОИСКПОЗ) — шукає значення та повертає його позицію в діапазоні.

🧮Формула: =MATCH(L1, M1:M9, 0). Шукає значення в L1 в діапазоні M1:M9 та повертає його позицію. Використовується разом з INDEX для більш гнучкого пошуку.

🔢11. VLOOKUP (ВПР) — шукає значення в першому стовпці діапазону і повертає відповідь з іншого стовпця.

🧮Формула: =VLOOKUP(G1, H1:H10, FALSE). Шукає G1 в діапазоні H1:H10 та повертає відповідь. Це один з найбільш поширених методів для вертикального пошуку в таблицях.

Умовні обчислення

🔢12. COUNTIF / COUNTSIF (СЧЁТЕСЛИ) — підраховує кількість клітинок, що відповідають певній умові.

🧮Формула: =COUNTIF(N1:N9, ">5"). Підраховує кількість клітинок в діапазоні N1:N9, де значення більше за 5. Це допомагає швидко підрахувати кількість елементів, які відповідають певним критеріям.

🔢13. COUNTA (СЧЁТ) — підраховує кількість непустих клітинок.

🧮Формула: =COUNTA(O1:O9). Підраховує кількість заповнених клітинок в діапазоні O1:O9. Корисно для підрахунку непорожніх клітинок.

🔢14. SUMIF / SUMIFS (СУММЕСЛИ / СУММЕСЛИМН) — додає значення, що відповідають заданій умові.

🧮Формула: =SUMIF(P1:P9, ">10"). Додає значення в діапазоні P1:P9, які більше за 10. Ідеально підходить для додавання значень за умовою.

🔢15. IF / Nested IFs (ЕСЛИ / Вложенные ЕСЛИ) — виконує умови, залежно від значення.

🧮Формула: =IF(R5>5, "Yes", "No"). Перевіряє, чи значення в R5 більше за 5 і повертає відповідь. Використовується для реалізації умовних операцій.

🔢16. AVERAGEIF / AVERAGEIFS (СРЗНАЧЕСЛИ / СРЗНАЧЕСЛИМН) — обчислює середнє значення, що відповідає умові.

🧮Формула: =AVERAGEIF(Q1:Q9, ">5"). Обчислює середнє значення для клітинок у Q1:Q9, де значення більше 5. Використовується для обчислення середнього значення за умовою.

🔢17. Logical Operators (AND, OR) (Логічні оператори (І, АБО)) — поєднує декілька умов.

🧮Формула: =AND(S1>5, S2<10). Перевіряє, чи виконуються обидві умови. Це дозволяє комбінувати декілька умов в одну формулу.

Текстові функції

🔢18. CONCATENATE (СЦЕПИТЬ) — з'єднує два або більше текстових рядків.

🧮Формула: =CONCATENATE(K1, " ", L1). Об'єднує значення з K1 і L1 в одну клітинку. Це корисно, коли потрібно об'єднати інформацію з кількох клітинок в одну.

🔢19. LEFT / RIGHT (ЛЕВ / ПРАВ) — витягує певну кількість символів з початку або кінця тексту.

🧮Формула: =LEFT(U1, 5). Витягує перші 5 символів з тексту в U1. Це використовується для отримання частини тексту з початку чи кінця рядка.

🔢20. MID (СРЕДИНА) — витягує частину тексту з середини рядка.

🧮Формула: =MID(U1, 3, 7). Витягує 7 символів з U1, починаючи з третього. Це дає можливість обробляти текстовий рядок, витягуючи частину з середини.

🔢21. TEXT (ТЕКСТ) — перетворює значення на текст з певним форматом.

🧮Формула: =TEXT(T1, "MMM-YY"). Форматує дату в T1 як місяць-рік. Це дозволяє форматувати числа, дати або час відповідно до заданого шаблону.

🔢22. IFERROR (ЕСЛИОШИБКА) — повертає значення, якщо результат формули є помилкою.

🧮Формула: =IFERROR(U1/V1, "Помилка"). Якщо результат ділення помилковий, повертає "Помилка". Це зручний спосіб обробляти помилки в формулах.

Калькулятор | MS Excel

Іноді при роботі з Excel простіше виконати якісь обчислення не в електронній таблиці, а на калькуляторі, але якщо він не виведений на панель швидкого запуску, то через "Пуск" його запускати довго. Давайте виведемо кнопку калькулятора на панель інструментів Excel.

В верхньому меню вибираємо: Вид → Панель інструментів → Налаштування. Відкриється діалогове вікно Налаштування, перейдіть на вкладку Команди. Тепер, у списку Категорії виберіть Сервіс. Прокрутіть вниз список Команди, ви побачите значок калькулятора (поруч буде текстова напис "Інше"). Утримуючи ліву кнопку миші, перетягніть цей значок зі списку на панель інструментів. Тепер можна просто клікати на цей значок, щоб запустити калькулятор.

Автозаповнення | MS Excel

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

Формат клітинок | MS Excel

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

Кількість символів у тексті | MS Excel

Як часто нам потрібно задати певні вимоги до кількості символів у тексті або рядку, наприклад, при подачі оголошення або складанні тексту. Для цього робимо наступне: в клітинці, в якій вам потрібно вивести кількість символів, ставимо знак "=" і задаємо формулу "ДЛСТР", потім в дужках пишемо саму клітинку, в якій рахуємо символи, виглядає це приблизно так: =ДЛСТР(клітинка), натискаємо Enter.

Прикріплення клітинок | MS Excel

Якщо у вас документ Excel довгий, і для його перегляду потрібно користуватися прокруткою, то дуже зручно буде закріпити верхні рядки ("шапку таблиці"), і при прокручуванні документа вниз, вони будуть в зоні видимості — не потрібно буде повертатись до верху документа і дивитись, що ж позначає та чи інша клітинка. Закріплення робиться просто: ставимо курсор під ту строку, яку хочемо закріпити, в панелі інструментів вибираємо Вікно → Закріпити області. Щоб скасувати закріплення, потрібно натиснути Вікно → Зняти закріплення областей.