Створення спеціальних функцій в 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), а потім виберіть команду Вставити>модуль. У правій частині редактора 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
   
   

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

Тепер можна використовувати нову функцію 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, які можна використовувати в спеціальних функціях, менша за кількість, які можна використовувати в макросах. Настроювані функції не можуть виконувати інші дії, окрім повернення значення до формули на аркуші або виразу, який використовується в іншому макросі чи функції VBA. Наприклад, настроювані функції не можуть змінювати розмір вікон, редагувати формулу в клітинці або змінювати шрифт, колір або параметри візерунка тексту в клітинці. Якщо включити код дії такого типу до процедури функції, функція поверне #VALUE! помилку #REF!.

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

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

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

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

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

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

Забезпечення доступності спеціальних функцій звідусіль

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

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

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

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

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

Про авторів

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

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

Ви завжди можете поставити запитання експерту в спільноті tech Excel або отримати підтримку в спільнотах.