Створення функцій користувача в Excel

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

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

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

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

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

1. Натисніть клавіші ALT+F11 (або FN+ALT+F11 на Mac), щоб відкрити редактор Visual Basic, а потім клацніть 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 і введіть наступний код:

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

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

Розглянемо, як Excel обробляє цю функцію. При натисканні клавіші ВВЕДЕННЯ 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, який зберігає значення у змінній, називається оператором призначення, тому що він обчислює вираз праворуч від знака рівності і призначає результат імені змінної зліва від нього. Бо змінна Discount називається так само, як і процедура функції, значення, що зберігається в змінній, повертається до формули листа, з якої була викликана функція DISCOUNT.

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

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

Discount = Application.Round(Discount, 2)

У VBA немає функції округлення, але є в Excel. Щоб використовувати округлення в цьому операторі, необхідно зазначити VBA, що метод (функцію) Round слід шукати в об'єкті Application (Excel). Для цього додайте слово Application перед словом Round. Використовуйте цей синтаксис щоразу, коли потрібно отримати доступ до функції Excel із модуля VBA.

Функції користувача повинні починатися з оператора Function і закінчуватися оператором End Function. Крім назви функції оператор Function зазвичай включає один або кілька аргументів. Однак, ви можете створити функцію без аргументів. В Excel є кілька вбудованих функцій (наприклад, СЛЧИС і ТДАТА), у яких немає аргументів.

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

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

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

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

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

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

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

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

Щоб вставити функцію користувача швидше (і уникнути помилок), її можна вибрати в діалоговому вікні "Вставка функції". Функції користувача доступні в категорії "Визначені користувачем":

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

1. Створивши потрібні функції, виберіть Файл >Зберегти як.
2. У діалоговому вікні Зберегти як відкрийте список, що розкривається Тип файлу та виберіть значення Надбудова Excel. Збережіть книгу з іменем, що запам'ятовується, таким як MyFunctions, у папці AddIns. Вона буде автоматично запропонована у діалоговому вікні. Зберегти як, тому потрібно лише прийняти розташування, що використовується за умовчанням.
3. Зберігши книгу, виберіть Файл >Параметри Excel.
4. У діалоговому вікні Параметри Excel виберіть категорію Надбудови.
5. У списку, що розкривається Управління виберіть Надбудови Excel. Потім натисніть кнопку. Перейти.
6. У діалоговому вікні Надбудови встановіть прапорець поруч із ім'ям книги, як показано нижче.

1. Створивши потрібні функції, виберіть Файл >Зберегти як.
2. У діалоговому вікні Зберегти як відкрийте список, що розкривається Тип файлу та виберіть значення Надбудова Excel. Збережіть книгу з іменем, що запам'ятовується, таким як MyFunctions.
3. Зберігши книгу, виберіть Сервіс >Надбудови Excel.
4. У діалоговому вікні Надбудови натисніть кнопку "Огляд", знайдіть свою надбудову, натисніть кнопку Відкрити, а потім встановіть прапорець поруч із надбудовою у полі Доступні надбудови.

Після виконання цих дій функції користувача будуть доступні при кожному запуску Excel.Якщо ви подивитеся на браузер проекту Visual Basic редактор під заголовком VBAProject, ви побачите модуль з ім'ям на честь файлу надбудови. Ваша надбудова матиме розширення .xlam.

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

Ця стаття заснована на розділі книги Microsoft Office Excel 2007 Inside Out, написаної Марком Доджем (Mark Dodge) та Крейгом Стінсоном (Craig Stinson). До неї були додані відомості, що стосуються пізніших версій Excel.

Додаткові відомості

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

Як працювати з аргументами функції у таблицях Excel

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

Найкращий спосіб вставки функції – використання діалогового вікна Майстер функцій. Це вікно викликається будь-яким із таких способів.

• Натисніть кнопку Вставити функцію, розташовану у групі Формули ► Бібліотека функцій.
• Натисніть кнопку Вставити функцію ліворуч від рядка формул.
• Натисніть Shift+F3.

Якщо ви не знаєте ім'я функції, яка вам потрібна, то можете знайти її, набравши текст у полі Пошук функції та натиснувши кнопку Знайти (рис. 80.1). Після того, як ви визначите функцію, натисніть кнопку ОК, і ви побачите вікно Аргументи функції, що ознайомить вас з аргументами цієї функції, як показано на рис. 80.2.

Мал. 80.1. Використовуйте вікно Майстер функцій, щоб знайти потрібну вам функцію

До речі, якщо ви вводите функцію вручну (без допомоги діалогового вікна Майстер функцій), то можете натиснути Ctrl+A для відкриття діалогового вікна Аргументи функції. Це поєднання клавіш працює тільки якщо ви ще не вводили будь-які аргументи для функції.

Мал. 80.2. Аргументи функції допомагає ввести аргументи функції

У деяких випадках ви можете включити "фіктивні" аргументи для функції - заповнювачі, які пізніше будуть замінені. Цей підхід виявиться корисним, якщо ви ще не знаєте, посилання на які осередки будуть використані. Для вставки назв аргументів як заповнювачів натисніть Ctrl+Shift+A після введення імені функції. Excel використовує назви аргументу як самих аргументів.

Наприклад, якщо натиснути Ctrl+Shift+A при введенні функції ВВР, то Excel вставить такі назви: = ВПР (шукане значення, таблиця; номер стовпця; інтервальний перегляд). Формула, звичайно, повертає помилку, тому ви повинні замінити фіктивні аргументи фактичними значеннями або посиланнями на комірки.