Створення спеціальних функцій в Excel

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

Для створення спеціальних функцій, як і макросів, використовується мова програмування Visual Basic for Applications (VBA). Спеціальні функції відрізняються від макросів двома характерними рисами. По-перше, у них використовуються процедури типу Функція, а не Підпрограма. Це означає, що вони починаються з інструкції Function замість Sub і закінчуються інструкцією End Function замість End Sub. По-друге, вони виконують обчислення, а не дії. Спеціальні функції не обробляють певні типи інструкцій, зокрема такі, що вибирають і форматують діапазони. У цій статті описано, як створювати й використовувати спеціальні функції. Функції та макроси створюються в редакторі Visual Basic (VBE), який відкривається з Excel в окремому вікні.

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

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

Приклад бланка замовлення без спеціальної функції

Щоб створити спеціальну функцію DISCOUNT у цій книзі, виконайте такі дії:

  1. Натисніть клавіші Alt+F11, щоб відкрити редактор Visual Basic (на комп’ютері Mac натисніть клавіші Fn+Alt+F11), а потім клацніть Insert (Вставити) > Module (Модуль). У правій частині редактора Visual Basic відкриється вікно нового модуля.

  2. Скопіюйте наведений нижче код і вставте його в новий модуль.

    Function DISCOUNT(quantity, price)
    If quantity >=100 Then
    DISCOUNT = quantity * price * 0.1
    Else
    DISCOUNT = 0
    End If

    DISCOUNT = Application.Round(Discount, 2)
    End Function

Примітка.: Щоб код було легше читати, до рядків можна додати відступи за допомогою клавіші Tab. Однак це не обов’язково, адже код працюватиме в будь-якому разі. Коли ви додаєте рядок із відступом, редактор Visual Basic припускає, що перед наступним рядком має бути такий самий відступ. Щоб повернутися на один символ табуляції ліворуч, натисніть клавіші Shift+Tab.

Тепер ви можете скористатися новою функцією DISCOUNT. Закрийте редактор Visual Basic, виберіть клітинку G7 і введіть такий текст:

=DISCOUNT(D7;E7)

Excel обчислює 10-відсоткову знижку на 200 одиниць товару за ціною 47,50 і повертає результат 950,00.

У першому рядку коду VBA (Function DISCOUNT(quantity, price)) зазначено, що функція DISCOUNT потребує два аргументи: quantity (кількість) і price (ціна). Коли ви викликаєте функцію в клітинці аркуша, аркуш має містити ці два аргументи. У формулі =DISCOUNT(D7;E7) D7 – це аргумент quantity, а E7 – price. Тепер можна скопіювати формулу DISCOUNT до діапазону клітинок G8:G13, щоб отримати наведені нижче результати.

Розгляньмо, як Excel інтерпретує цю функцію. Коли ви натискаєте клавішу Enter, Excel шукає ім’я DISCOUNT у поточній книзі та виявляє, що це спеціальна функція в модулі VBA. Імена аргументів у дужках (quantity та price) – це заповнювачі для значень, на яких ґрунтується обчислення знижки.

Приклад бланка замовлення зі спеціальною функцією

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

If quantity >= 100 Then
DISCOUNT = quantity * price * 0.1
Else
DISCOUNT = 0
End If

Якщо кількість проданих одиниць товару перевищує або дорівнює 100, VBA виконує наведену нижче інструкцію, яка перемножує значення quantity (кількість) і price (ціна), а потім множить результат на 0,1.

Discount = quantity * price * 0.1

Результат зберігається як змінна Discount. Інструкція VBA, яка зберігає значення в змінній, має назву assignment (призначення), тому що вона оцінює вираз праворуч від знака рівності та призначає результат імені змінної ліворуч від нього. Змінна Discount має таке саме ім’я, що й функція, тож значення, яке зберігається в змінній, повертається до формули на аркуші, яка викликала функцію DISCOUNT.

Якщо значення аргументу quantity менше 100, VBA виконує таку інструкцію:

Discount = 0

Нарешті, ця інструкція округлює значення, призначене змінній Discount, до двох десяткових розрядів:

Discount = Application.Round(Discount, 2)

У VBA немає функції ROUND, а в Excel – є. Тож, щоб використовувати в цій інструкції функцію ROUND, слід дати VBA указівку шукати метод (функцію) Round в об’єкті Application (Excel). Щоб зробити це, перед словом Round потрібно додати слово Application. Цей синтаксис можна використовувати щоразу, коли потрібно отримати доступ до функції Excel із модуля VBA.

Спеціальна функція має починатися з інструкції Function і закінчуватися інструкцією End Function. Окрім імені функції інструкція Function зазвичай визначає один або кілька аргументів. Проте можна створити функцію без аргументів. В Excel є кілька вбудованих функцій, зокрема RAND і NOW, які не мають аргументів.

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

У спеціальних функціях можна використовувати менше ключових слів VBA, ніж у макросах. Спеціальні функції не можуть виконувати жодні інші дії, крім як повертати значення до формули на аркуші або до виразу, який використовується в іншому макросі чи функції VBA. Наприклад, спеціальні функції не можуть змінювати розмір вікон, редагувати формули в клітинках і змінювати параметри шрифту, кольору чи візерунка для тексту в клітинці. Якщо включити до функції код для виконання дії такого типу, функція поверне помилку #VALUE!.

Єдина дія, яку може виконувати функція (крім обчислень), – відображати діалогові вікна. У спеціальній функції можна використовувати інструкцію InputBox, щоб отримати дані від користувача. Передати інформацію користувачу можна за допомогою інструкції MsgBox. Крім того, можна використовувати спеціальні діалогові вікна (UserForms), але цю тему слід розглядати в окремій статті.

Навіть простий макрос або спеціальну функцію може бути складно зрозуміти. У такому випадку можна додати пояснювальний текст у вигляді приміток. Щоб додати примітку, перед пояснювальним текстом потрібно ввести апостроф. У наведеному нижче прикладі показано функцію DISCOUNT із примітками. Завдяки таким приміткам вам або іншому користувачу буде легше зрозуміти код VBA, повернувшись до нього через певний час. Якщо в майбутньому знадобиться змінити код, примітка допоможе згадати його первісне призначення.

Приклад функції VBA із примітками

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

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

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

Щоб використовувати спеціальну функцію, книгу з модулем, у якому її створено, має бути відкрито. Якщо ви спробуєте використати спеціальну функцію, коли цю книгу закрито, станеться помилка #NAME?. Якщо ви посилаєтеся на функцію з іншої книги, імені функції має передувати ім’я книги, у якій вона міститься. Наприклад, якщо ви створили функцію DISCOUNT у книзі Personal.xlsb та викликаєте цю функцію з іншої книги, замість =discount() потрібно вводити =personal.xlsb!discount().

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

Діалогове вікно "Вставлення функції"

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

  1. Створивши потрібні функції, виберіть Файл > Зберегти як.

    В Excel 2007 натисніть кнопку Microsoft Office і виберіть Зберегти як.

  2. У діалоговому вікні Збереження документа відкрийте розкривний список Тип файлу та виберіть пункт Надбудова Excel. Збережіть книгу в папці AddIns, використовуючи зрозуміле ім’я, як-от МоїФункції. Цю папку запропоновано в діалоговому вікні Збереження документа, тому вам потрібно лише прийняти стандартне розташування.

  3. Зберігши книгу, виберіть Файл > Параметри.

    В Excel 2007 натисніть кнопку Microsoft Office і виберіть Параметри Excel.

  4. У діалоговому вікні Параметри Excel виберіть категорію Надбудови.

  5. З розкривного списку Керування виберіть пункт Надбудови Excel. Натисніть кнопку Перейти.

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

    Діалогове вікно "Надбудови"

  1. Створивши потрібні функції, виберіть File (Файл) > Save As (Зберегти як).

  2. У діалоговому вікні Save As (Збереження документа) відкрийте розкривний список Save As Type (Тип файлу) і виберіть пункт Excel Add-In (Надбудова Excel). Збережіть книгу, використовуючи зрозуміле ім’я, як-от МоїФункції.

  3. Зберігши книгу, виберіть Tools (Знаряддя) > Excel Add-Ins (Надбудови Excel).

  4. У діалоговому вікні Add-Ins (Надбудови) натисніть кнопку Browse (Огляд), знайдіть потрібну надбудову, натисніть кнопку Open (Відкрити), а потім у розділі Add-Ins Available (Наявні надбудови) установіть прапорець біля потрібної надбудови.

Коли ви виконаєте ці дії, спеціальні функції будуть доступні щоразу після запуску Excel. Якщо потрібно додати функцію до бібліотеки, поверніться до редактора Visual Basic. У вікні проекту редактора Visual Basic під заголовком VBAProject відображатиметься модуль із таким самим ім’ям, як у файлу вашої надбудови. Надбудова матиме розширення XLAM.

Модуль з іменем у редакторі Visual Basic

Якщо двічі клацнути модуль у вікні проекту, редактор Visual Basic відобразить код функції. Щоб додати нову функцію, розташуйте курсор за інструкцією End Function, яка закінчує останню функцію у вікні коду, і введіть потрібний код. У такий спосіб можна створити будь-яку кількість функцій, і вони завжди відображатимуться в діалоговому вікні Вставлення функції в категорії "Визначені користувачем".

Початковий текст цієї статті було взято з книги Марка Доджа та Крейга Стінсона Microsoft Office Excel 2007 Inside Out. Пізніше статтю було оновлено для охоплення новіших версій Excel.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×