Як створити для користувача функцію в microsoft excel

Хоча в Excel безліч (можливо, сотні) вбудованих функцій, таких як SUM (СУМ), VLOOKUP (ВВР), LEFT (ЛЕВСИМВ) та інших, як тільки ви починаєте використовувати Excel для більш складних завдань, ви можете виявити, що вам потрібна така функція, якої ще не існує. Не впадайте у відчай, ви завжди можете створити функцію самі.

кроки

1. Створіть нову книгу Excel або відкрийте книгу, в якій хочете використовувати призначену для користувача функцію (UDF).

Create a User Defined Function in Microsoft Excel Step 2

2. Відкрийте редактор Visual Basic, який вбудований в Microsoft Excel, вибравши «Інструменти» -> «Макроси» -> «Редактор Visual Basic» (або натиснувши Alt + F11).

Create a User Defined Function in Microsoft Excel Step 3

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

Create a User Defined Function in Microsoft Excel Step 4

4. Створіть «заголовок» або «прототип» вашої функції. Він повинен мати наступну структуру:

public function TheNameOfYourFunction (param1 As type1, param2 As type2) As returnType У неї може бути скільки завгодно параметрів, а їх тип повинен відповідати будь-якому базовому типу даних Excel або типу об`єктів, наприклад Range. Параметри в даному випадку виступають в якості «операндів», з якими працює функція. Наприклад, якщо ви пишете SIN (45), щоб обчислити синус 45 градусів, 45 виступає в якості параметра. Код вашої функції буде використовувати це значення для обчислень і представлення результату.

Create a User Defined Function in Microsoft Excel Step 5

5. Додайте код потрібної функції, переконавшись, що ви 1) використовуєте значення, передані в якості параметров- 2) привласнюєте результат імені функції-3) закінчуєте код функції виразом "end function". Вивчення програмування на VBA або на будь-якому іншому мовою може зайняти деякий час і вимагати докладного вивчення керівництва. Однак функції зазвичай мають невеликі блоки коду і використовують дуже мало можливостей мови. Найбільш використовувані елементи мови VBA:

блок If, який дозволяє виконувати якусь частину коду тільки в разі виконання умови. наприклад:


Public Function Course Result (grade As Integer) As String
If grade> = 5 Then
CourseResult = "Approved"
Else
CourseResult = "Rejected"
End If
End Function

Зверніть увагу на елементи всередині блоку If: IF умова THEN код_1 ELSE код_2 END IF. Ключове слово Else і друга частина коду необов`язкові.

блок Do, який виконує частину коду, поки виконується умова (While) Або до тих пір (Until), Поки воно не виконається. наприклад:

Public Function IsPrime (value As Integer) As Boolean
Dim i As Integer
i = 2
IsPrime = True
Do
If value / i = Int (value / i) Then
IsPrime = False
End If
i = i + 1
Loop While i < value>End Function

Зверніть увагу на елементи: DO код LOOP WHILE / UNTIL умова. Зверніть також увагу на другий рядок, де «оголошена» змінна. У своєму коді ви можете додавати змінні і пізніше їх використовувати. Змінні служать для зберігання тимчасових значень всередині коду. І нарешті, зверніть увагу, що функція оголошена як BOOLEAN, що є типом даних, в якому дозволено лише значення TRUE і FALSE. Цей спосіб визначення, чи є число простим, далеко не самий оптимальний, але ми залишили його таким, щоб зробити код більш читабельним.

блок For , який виконує частину коду вказане число раз. наприклад:

Public Function Factorial (value As Integer) As Long
Dim result As Long
Dim i As Integer
If value = 0 Then
result = 1
ElseIf value = 1 Then
result = 1
Else
result = 1
For i = 1 To value
result = result * i
Next
End If
Factorial = result
End Function

Зверніть увагу на елементи:FOR змінна = початкове_значення TO конечное_значеніе код NEXT. Також зверніть увагу на елемент ElseIf в вираженні If, який дозволяє додати більше умов до коду, який потрібно виконати. І нарешті, зверніть увагу на оголошення функції і змінної "result" як Long. Тип даних Long дозволяє зберігати значення, набагато перевищують Integer.

Нижче показаний код функції, перетворюючої невеликі числа в слова.

Create a User Defined Function in Microsoft Excel Step 6

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

= NumberToLetters (A4)

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

Константні значення, безпосередньо вводяться в формулі в осередку. Текстові рядки в такому випадку повинні бути укладені в лапки.

Посилання на осередки на кшталт B6 або посилання на діапазони на кшталт A1: C3 (Параметр повинен мати тип Range).

Інші вкладені функції (ваша функція теж може бути вкладеною по відношенню до інших функцій). Наприклад: = Factorial (MAX (D6: D8))

Create a User Defined Function in Microsoft Excel Step 7

7. Переконайтеся, що результат функції правильний при кількох її спрацьовування, щоб упевнитися, що вона правильно обробляє різні значення параметрів.

Поради

Всякий раз, коли ви пишете блок коду всередині структури If, For, Do і так далі, переконайтеся, що ви він має відступ, який можна зробити за допомогою пробілів чи знаків табуляції (стиль відступів ви вибираєте самі). Це зробить ваш код більш читабельним, і вам самим потім легше буде відслідковувати помилки і вносити зміни.
Використовуйте ім`я, яке не використовується в якості імені функції в Excel, інакше ви зможете використовувати тільки одну з цих функцій.
Excel має безліч вбудованих функцій, і більшість обчислень можна зробити за допомогою незалежного їх використання або з використанням їх комбінацій. Перш ніж писати свою функцію, пройдіться по всьому списку вже існуючих функцій. При використанні вбудованих функцій виконання може відбуватися швидше.
У деяких випадках для обчислення результату функції необов`язково знати всі значення параметрів. У подібних випадках ви можете використовувати ключове слово Optional перед ім`ям параметра в заголовку функції. У коді ви можете використовувати функцію IsMissing (імя_параметра), щоб визначити, чи було параметру присвоєно якесь значення чи ні.
Якщо ви не знаєте, як написати код функції, прочитайте статтю про те, як написати найпростіший макрос в Microsoft Excel.

попередження

У зв`язку з певними заходами безпеки деякі люди можуть відключити макроси. Обов`язково сповістіть своїх колег про те, що книга Excel, яку ви їм посилаєте, містить макроси, і що ці макроси не зашкодять їх комп`ютерів.
Приклади функцій, використаних в цій статті, - не обов`язково найкращий спосіб вирішити пов`язані з ними проблеми. Ці функції були використані, щоб наочно показати використання контрольних структур мови.
VBA, як і багато інших мов, має ще кілька контрольних структур крім Do, If і For. Ці структури були приведені тут, щоб пояснити, що можна робити всередині коду функцій. В інтернеті є безліч підручників, за якими ви можете вивчити VBA.