Обчислювальні функції — це серцевина Excel і те, що по суті відрізняє програму від звичайного калькулятора. Функцій в програмі досить багато — понад 400. Частина дублюється,
проте можливості для економічних розрахунків у програмі величезні.
За допомогою формул можна безпомилково розрахувати собівартість товару, підготувати аналітичні звіти, звести плани. Вирішити як прості економічні завдання обліку, так і скласти
повний баланс підприємства. Добірку інструментів Excel для бухгалтера ми вже робили. Настала черга розглянути основні
функції Excel для економіста, які спростять роботу з даними та великими таблицями.
Що таке функції Excel і де вони знаходяться?
Функції в Excel — це команди, які допомагають за лічені хвилини відкоригувати дані в комірках, розрахувати значення за певними критеріями, вилучити потрібну інформацію з величезного масиву. Функції часто використовують у формулах, щоб їх спростити та зробити більш гнучкими.
Як ми вже говорили, у програмі функцій багато — близько 10 категорій: математичні, логічні, текстові. І спеціальні функції — фінансові, статистичні та інші. Усі функції лежать у вкладці «Формули». Перейшовши до неї, потрібно натиснути кнопку «Вставити функцію» на панелі інструментів, після чого запуститься «Майстер функцій».
Залишиться вибрати в «Категоріях» потрібну функцію для обчислень і заповнити дані за структурою у вікні, що випадає
Якщо ви знаєте, яка функція вам потрібна, просто введіть її у рядку формул, починаючи зі знака «=». Як ввели, натисніть «Enter» для обчислення.
Запам'ятовувати, що робить кожна функція, не потрібно. В Excel є зручні підказки, навіщо потрібна та чи інша формула і як її використовувати.
Нижче розглянемо основні формули та ті, що найчастіше використовують в Excel економісти: ЯКЩО, СУМЯКЩО (SUMIF), ВПР, СУМДОБУТ (SUMPRODUCT), РАХУНОК, СРЗНАЧ та МАКС/МІН.
Функція ЯКЩО (IF) для порівняння даних
Найпопулярніша логічна функція, яка корисна економістам, бухгалтерам. З її допомогою можна порівняти числові та текстові значення за певними критеріями. Наприклад, розрахувати амортизацію, знижки, націнку або усунути помилки з розрахунків.
Іноді використовують вбудовану функцію ЯКЩО (IF) у статистичних, текстових та математичних формулах, що наділяє їх здатністю «ухвалювати рішення». Функція ЯКЩО допомагає точно порівняти значення та отримати результат, залежно від того, істинне порівняння чи ні.
Такий вигляд має формула:
=IF(лог_вираз;значення_якщо_істина;значення_якщо_хибність)
- Лог_вираз — це те, що потрібно перевірити або порівняти (числові або текстові дані в комірках).
- Значення_якщо_істина — це те, що з'явиться в комірці, якщо порівняння буде правильним.
- Значення_якщо_хибність — те, що з'явиться в комірці при неправильному порівнянні.
Наприклад, магазин продає аксесуари для чоловіків та жінок. Цього місяця на всі жіночі товари знижка 20%. Відсортувати акції можна за допомогою функції ЯКЩО для текстових значень.
Пропишемо формулу в стовпчику «Знижка» так:
=IF(B2=«жіночий»;20%;0)
І застосуємо до всіх рядків. В комірках, де рівність виконується, побачимо товари зі знижкою.
Так застосовується функція ЯКЩО для текстових значень з однією умовою
Функції СУМЯКЩО та СУМЯКЩОМН
Ще одна корисна функція СУМЯКЩО (SUMIF), яка дозволяє підсумувати кілька числових даних за певним критерієм. Складається формула з 2-х частин:
- СУМ — математична функція складання числових значень. Записується як =СУМ(комірка/діапазон 1; комірка/діапазон 2; …);
- та функція ЯКЩО, яку розглянули вище.
Наприклад, вам потрібно прорахувати загальну суму нарахованої заробітної плати менеджерам, а у звіті вказані дані щодо всіх співробітників підприємства.
У формулі потрібно прописати такі аргументи:
- Виділити діапазон усіх посад співробітників — у нашому випадку B2:B10.
- Прописуємо критерій вибору через крапку з комою — «менеджер».
- Діапазон підсумовування — це заробітні плати. Вказуємо C2: C10.
І отримуємо в один клік загальну суму заробітної плати менеджерів:
За допомогою функції SUMIF можна підсумувати комірки, які відповідають певному критерію
Важливо! Функція СУМЯКЩО чутлива до правильності та точності написання критеріїв. Найменша помилка може дати неправильний результат. Це також стосується назв комірок. Формула видасть помилку, якщо написати діапазон комірок кирилицею, а не латиницею.
Більш складний варіант цієї формули — функція СУМЯКЩОМН. По суті, це вибіркове підсумовування даних, відібраних за кількома критеріями. На відміну від СУМЯКЩО, можна використовувати до 127 критеріїв відбору даних. Наприклад, за допомогою цієї формули легко розрахувати сумарний прибуток від постачання різних товарів відразу в декілька країн.
У функції СУМЯКЩОМН можна працювати з підстановочними символами, використовувати оператори для обчислень типу «більше», «менше» і «рівно». Для зручності роботи з функцією краще застосовувати абсолютні посилання в Excel — вони не змінюються при копіюванні та дозволяють автоматично перерахувати формулу, якщо дані в комірці змінилися.
Функції ВПР та ГПР — пошук даних у великому діапазоні
Економістам часто доводиться обробляти величезні таблиці, щоб отримати необхідні дані для аналізу. Або зводити дві таблиці в одну, що теж не рідкість. Функція ВПР або, як її ще називають, вертикальний перегляд (англ. VLOOKUP) дозволяє швидко знайти та витягти потрібні дані в стовпцях. Або перенести дані з однієї таблиці у відповідні комірці іншої.
Синтаксис найпростішої функції ВПР має такий вигляд:
=VLOOKUP(шукане_значення; таблиця; номер_стовпця; [інтервальний перегляд]).
Наприклад, вам потрібно швидко отримати найменування товару за номером у списку. За допомогою функції ВПР це зробити дуже просто:
Функція ВПР дозволяє швидко знайти потрібні дані та перенести їх у виділену комірку
В комірці С1 ми вказали номер товару. Потім виділили діапазон комірок, де його шукати (A1:B10) та написали номер стовпця «2», в якому потрібно взяти дані. Натиснули Enter та отримали потрібний товар у виділеній комірці.
Існує менш популярна функція ГПР. Вона працює також як ВПР, але шукає дані в горизонтальних списках.
Важливо! Щоб збільшити область застосування цієї функції, можна встановити дані за допомогою підстановочних знаків.
Одне із завдань цієї функції — спростити підготовку вихідних даних для аналітичних та зведених таблиць. Як підготувати таку таблицю, можете почитати безкоштовно в книзі Зведені таблиці в Excel.
Функція СУМДОБУТ в Excel
Четверта функція — СУМДОБУТ (SUMPRODUCT) або підсумовування добутків. Допоможе швидко впоратись з будь-яким економічним завданням, де є масиви. Включає можливості попередніх формул ЯКЩО, СУМЯКЩО та СУМЯКЩОМН, а також дозволяє провести розрахунки у 255 масивах. Її люблять бухгалтери та часто використовують при розрахунках заробітної плати та інших витрат.
Що може бути добутками? Як задані діапазони, так і цілі масиви даних. Щоб задати кількість масивів, їх потрібно перерахувати через крапку з комою, потім перемножити, а потім підсумувати. При цьому є одна умова — масиви мають бути однакові за типом та довжиною.
Наприклад, у вас є місячний звіт з продажу у магазині, з якого потрібно дізнатись, на яку суму продали футболки розміру «М».
Для цього використовуємо функцію СУМДОБУТ та вказуємо 2 умови. Кожне з них беремо у дужки, а між ними ставимо «зірочку», яка в Excel читається як союз «і».
Запишемо команду так: =SUMPRODUCT((A2:A8=A10)*(B2:B8=B10)*C2:C8), де:
- перша умова A2:A8=A10 — діапазон пошуку та найменування потрібного товару;
- друга умова B2:B8=B10 — діапазон пошуку та розмір;
- C2:C8 — масив, з якого береться підсумкова сума.
За допомогою функції SUMPRODUCT ми дізнались за пару хвилин, що в магазині за місяць продали футболки М-розміру на 100 у.о.
Щоб дізнатися про продаж іншого товару і розміру, потрібно просто замінити дані в комірках A10 і B10.
Цей приклад дуже простий, порівняно з тим, з якими обсягами даних доводиться працювати економістам. Але знаючи, що вміє ця формула, вже не такі страшні таблиці в мільйони рядків і сотні стовпців, і потрібні дані можна знайти за лічені хвилини.
Як застосувати МАКС, ВПР та ПОШУКПОЗ для розв'язання задач
Функції МІН та МАКС допомагають знайти найменше чи найбільше значення даних. Функція ПОШУКПОЗ (MATCH) допомагає знайти номер вказаного елемента у виділеному діапазоні. А формула ВПР, нагадаємо, дозволяє витягти потрібні дані зі стовпців у зазначені комірки.
Розглянемо комбінацію цих функцій з прикладу рішення завдання в Excel для економістів. Складемо комбіновану формулу, щоб знайти компанію із найбільшою сумою заборгованості.
Для початку складемо список боржників, вкажемо суму їхньої заборгованості та виставимо дату закінчення кредитного договору.
Для вирішення завдання можна застосувати функції послідовно:
- Знайти найбільший борг допоможе функція МАКС (=MAX(B2:B10)), де B2:B10 — стовпець з даними заборгованості.
- Щоб знайти номер компанії-боржника у списку, потрібно до таблиці додати стовпець із нумерацією. Тому що функція ПОШУКПОЗ шукає дані тільки в крайньому лівому стовпці виділеного діапазону.
Складаємо функцію за формулою:
=MATCH(шукане_значення;масив_який_ переглядається;тип_зіставлення])
У нашому випадку це буде =MATCH(14569;C2:C10;0), де шукане — максимальна сума боргу. Тип зіставлення буде «0», тому що до стовпця з боргами ми не використовували сортування.
- Щоб дізнатись назву компанії-боржника, застосуємо знайому функцію ВПР.
Виглядатиме вона так =VLOOKUP(D15;A2:B10;2), де D15 — шукане, A2:B10 — таблиця або виділений діапазон з назвами компаній і нумерацією, а «2» — номер стовпця з боржниками.
Такий самий результат можна було отримати, зібравши одну формулу з 3-х:
= VLOOKUP (MATCH (MAX (C2: C10); C2: C10; 0); A2: B10; 2).
В економічних розрахунках функція ВПР допомагає швидко отримати потрібне значення з великого діапазону даних. До того ж значення можна знайти за різними критеріями відбору. Наприклад, ціну товару можна отримати за ідентифікатором, податкову ставку — за рівнем доходу та ін. Крім вищезгаданих функцій, економісти часто використовують формулу СРЗНАЧ (AVERAGE), наприклад, для розрахунку середньої заробітної плати. Функцію РАХУНОК (COUNT), коли потрібно розрахувати кількість відвантажень у розрізі клієнтів або вартості товару за певний період.
До речі, на прикладі відвантажень формула МІН/МАКС допоможе відстежити діапазон, в якому змінювалась вартість товару. Існує ще сотня математичних та фінансових функцій в Ехсеl, за допомогою яких можна вирішити економічні завдання різної складності. Наприклад, обробляти замовлення, планувати завантаження виробництва, розраховувати податки, вести кадровий облік та керувати збутом. Але для вирішення подібних завдань підказок в Excel буде вже недостатньо. Тут потрібно глибше занурюватись у програму та вчитись працювати з різними її інструментами: від функцій і зведених таблиць до діаграм і макросів.
