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

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

Відео: Створення призначених для користувача функцій в Excel, частина 1

• 2
  Відкрийте редактор Visual Basic, який вбудований в Microsoft Excel, вибравши Інструменти-gt; Макроси-gt; Редактор Visual Basic (або натиснувши Alt + F11).
• Відео: Створення складних функцій в Microsoft Excel, частина 1

  
  
  
  
  3
  Додайте новий модуль в свою книгу Excel, натиснувши на зазначену кнопку. Ви можете створити для користувача функцію на робочому аркуші без додавання нового модуля, але в такому випадку ви не зможете використовувати цю функцію на інших сторінках книги.
• 4
  створіть "Заголовок" або "прототип" вашої функції. Він повинен мати наступну структуру: public function TheNameOfYourFunction (param1 As type1, param2 As type2) As returnType У неї може бути скільки завгодно параметрів, а їх тип повинен відповідати будь-якому базовому типу даних Excel або типу об`єктів, наприклад Range. Параметри в даному випадку виступають в якості "операндів", З якими працює функція. Наприклад, якщо ви пишете SIN (45), щоб обчислити синус 45 градусів, 45 виступає в якості параметра. Код вашої функції буде використовувати це значення для обчислень і представлення результату.
• 5
  Додайте код вашої функції, переконавшись, що ви 1) використовуєте значення, передані в якості параметров- 2) привласнюєте результат імені функції-і 3) закінчуєте код функції виразом "end function".Вивчення програмування на VBA або на будь-якому іншому мовою може зайняти деякий час і докладного вивчення керівництва. Однак, функції зазвичай мають невеликі блоки коду і використовують дуже мало можливостей мови. Найбільш використовувані елементи мови VBA:
• блок If, який дозволяє виконувати якусь частину коду тільки в разі виконання умови. наприклад:
  
  
Public Function CourseResult (grade As Integer) As String
If grade gt; = 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 lt; value And IsPrime = True
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.
  
  Нижче показаний код функції, перетворюючої невеликі числа в слова.
• 6
  
  
  
  
  Перейдіть назад в робочу книгу Excel і використовуйте свою функцію, набравши в якійсь комірці знак одно, а потім ім`я функції. Додайте до імені функції відкриває дужку, параметри, розділені комами, і дужку. наприклад:= NumberToLetters (A4)Ви також можете використовувати свою призначену для користувача функцію, знайшовши її в категорії призначені для користувача в майстра вставки формули. Просто натисніть на кнопку Fx, розташовану зліва від поля формул.Параметри можуть бути трьох типів:
• Константні значення, безпосередньо вводяться в формулі в осередку. Текстові рядки в такому випадку повинні бути укладені в лапки.
• Посилання на осередки на кшталт B6 або посилання на діапазони на кшталт A1: C3 (Параметр повинен мати тип Range)
• Інші вкладені функції (ваша функція теж може бути вкладеною по відношенню до інших функцій). Наприклад: = Factorial (MAX (D6: D8))
• 7
  Переконайтеся, що результат функції правильний при кількох її спрацьовування, щоб переконатися, що вона правильно обробляє різні значення параметрів:

• Поради

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

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

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