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

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

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

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

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

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

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

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

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

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

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

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

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

Про авторів

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

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

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