Способи збереження функцій, створених користувачем, та включення їх до бібліотеки вбудованих функцій
У зв'язку з тим, що при виході з Excel, створені у VBA користувачем функції не зберігаються в бібліотеці вбудованих функцій, їх можна зберегти і включити при необхідності в бібліотеку одним з наступних способів:
Спосіб 1– Функція зберігається як файл Excel. Для цього після створення функції на аркуші модуля VBA та її використання перед виходом з Excel:
1. На робочому аркуші Excel виберіть команди Файл/Зберегти як … – буде видано діалогове вікно Збереження.
2. У спискуЗберегти в:виберіть диск (папку), на якому хочете зберегти функцію. Наприклад, дискета 3,5 (або папка Мої документи). У розділі Ім'я файлу введіть ім'я функції. Наприклад, G. (Бажано вибирати імена функцій, які б нагадували про призначення функції, наприклад, Доход, премія тощо.). У списку тип файлу виберіть Книга Microsoft Excel і клацніть Зберегти.
3. Вийдіть із Excel.
Для включення функції до бібліотеки вбудованих функцій та використання її:
1. Увійдіть до Excel і виберіть команду Файл\Відкрити. Відкрийте диск, на якому збережено функцію, перейдіть до функції та натисніть кнопку Відкрити.
Якщо створена функція дуже важлива для вас і використовується вами досить часто, бажано скоротити шлях доступу до неї. У цьому випадку можна використовувати для її збереження Спосіб 2, який дозволяє включити її до бібліотеки вбудованих функцій, і вона буде доступна при наступних запусках Excel.
Спосіб 2. - Збереження функції користувача в особистій книзі макросів (PERSONAL.XLS).
Для цього необхідно створити проект PERSONAL…, для чого: 1. Виконати командиСервіс \ Макрос \ Почати запис ... - буде видано діалогове вікно Запис макросу. 2. У полі Зберегти: виділити Особиста книга макросів і клацнути ОК. 3. Виконати команду Зупинити запис. 4. Виконавши команди Сервіс\Макрос\Редактор Visual Basic, ви переконаєтеся, що проект PERSONAL створено. Тепер ви можете створювати функції у проекті PERSONAL, так як, наприклад, створили функцію G у проекті Книга1.
Приклад 2. Створення функції користувача для обчислення наступної функції з трьома умовами:
Для запису функції на VBA використовується малий оператор
If t -1 And t =0 Then z=(1+t)^(3/5)
Завдання: 1. Реалізувати функції прикладу 1 і прикладу 2 і освоїти способи запуску збереження та використання функцій користувача. 2. Завдання для самостійного виконання. Розробити функцію виконання прикладу 2 з допомогою блочного оператора If Then Else.
Функція Ln не є внутрішньою функцією VBA, тому для її виклику потрібно скористатися конструкцією Application.Ln. Послідовність введення функції бібліотеку вбудованих функцій майстра функцій і її використання аналогічні попередньому прикладу.
Приклад 3.Створимо функцію користувача з ім'ям Вартість, яка розраховує вартість партії книг за прогресивною шкалою цін, а саме: якщо продається від 100 до 200 примірників книги, то знижка від її відпускної ціни становить 7%, якщо продається від 201 до 300 екземплярів -10 %, а якщо понад 300 екземплярів – 15 %. Крім того, для постійних клієнтів передбачено додаткову знижку у розмірі 5%.
Аргументи цієї функції назвемо Ціна однієї книги, кількість і знижка. Для аргументу Знижка передбачимо лише два допустимі значення: 1 для постійних клієнтів та 0 в іншому випадку.
Для побудовикористувальницької функції Вартість:
2. Виконайте командуВставка, Модульдля створення аркуша Модуля.
4. Наберіть на аркуші Модуля наведену нижче процедуру:
Function Вартість (ЦінаОдноїКниги, Кількість, Знижка)
'Обчислення вартості без урахування знижки для постійних клієнтів.
'Продаж від 201 до 300 екземплярів
If Кількість =40000
Is є ключовим словом VBA, що означає вираз в операторі Case. В операторі Case допустимі складові умови, наприклад:
Case 4, 7 То 8, 11 То 12, 15 перевіряє, чи належить вираз, що перевіряється одному з відрізків: від 7 до 8 і від 11 до 12 або дорівнює одному з значень: 4 і 15.
Case 5, 6, 9 То 10, 13, 14 Is>=16 перевіряє, чи належить вираз, що перевіряється, відрізку від 9 до 10 або дорівнює одному з значень: 5, 6, 13 і 14, або воно менше, ніж 16.
Для порівняння наведемо формулу, яку треба ввести в комірку В1 при вирішенні цього завдання в Excel без використання функцій користувача
=ЯКЩО(І(А1>=0; A1 =10000;A1 =40000;A1*0.14)))
Приклад 5. – У цьому прикладі, який використовує оператор Select Case, здійснюється вибір ставки податку (0; 0,05; 0,10; 0,15; 0,20) залежно від значення виразу intПараметр. Передбачено також варіант вибору, коли значення виразу intПараметр не збігається з жодним виразом у пропозиціях Case.
Function Податок(sngСума As Single, intПараметр As Integer)
Select Case intПараметр
Приклад 6. Створення функції користувача з оператором циклу For-Next
Створимо функцію користувача, що обчислює різницю між поточним обсягом вкладу та розміром позички при постійній річній процентній ставці та нерівномірних платежах, тобто. функцію користувача, що обчислюється занаступною формулою
де P(1) і d(1) - розмір та дата видачі позички, причому P(1) береться зі знаком мінус, P(j), d(j) - розмір та дата j-ї виплати, n-1 - число виплат, i – річна процентна ставка.
Function Дохід (відсоток As Double, платіж As Variant, _
рік As Variant) As Double
Dim i, j, n As Integer, s As Double
n=платіж. Rows. Count
Мал. 3.5.1.2. розв'язання задачі про нерівномірні платежі
Вирішимо за допомогою функції Дохід наступне завдання. Припустимо, що 11.01.97 у вас беруть у борг 10000 руб. і пропонують повернути: 20.12.97 – 2000 руб., 18.10.98 – 40000 руб., 12.04.99 – 7000 руб. Чи має сенс ця угода за річної ставки 10%?
Для вирішення цього завдання введено дані, як показано на рис. 3.5.1.2.
Позика введена в комірку В2 зі знаком мінус, т.к. ці гроші у вас забирають. У комірку В8, де обчислюється різницю між поточним обсягом вкладу та розміром позички, введемо формулу = Дохід (В7; В2: В5; D2: D5). У цьому випадку знайдене значення дорівнює 857.91. Оскільки результат позитивний, ця угода вигідна.
Приклад 7. Створення функції користувача з оператором Exit For
Створимо функцію Тест, що визначає номер першого входження елементау вектора. Якщо серед компонент вектораанемає елементів, що дорівнюєв, функція Тест набуває значення, що дорівнює -1.
Function Тест(a As Variant, As Variant) As Integer
Dim i, n As Integer, t As Boolean
If t=False Then Тест=-1
Приклад8. Створення функції користувача за допомогою оператора циклу While-Wend
Синтаксис оператора While-Wend наведено на с.
Нижче наведена функція Доход_2 обчислює те саме значення, що й функція Доход, але з використанням оператора циклу While-Wend:
Function Дохід_2 (відсоток As Double, платіж As Variant, _
рік As Variant) As Double
Dim i, j, n As Integer, s As Double
While i 6 Then Y=1*X+C Else Y=5*1*X
4. Щоб повернутися до Excel, виконайте команду Файл, Закрити та повернутися до Microsoft Excel.
5. На робочому аркуші Excel до осередків А1:А12 введіть значення Х рівні 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, а в осередки В1:В12 введіть значення С, рівні - 5; 6,5; 8; 9,5; 11; 12,5; 14; 15,5; 17; 18,5; 20; 21,5.
7. У рядок Х діалогового вікна введіть А1, а в рядок В1 і ОК.
8. Повторіть п.п. 6-7 для комірки С2.
9. Встановіть покажчик миші на маркері заповнення (маленький Å у нижньому кутку) і протягніть його до комірки С12.
У осередках С1:С12 - результати обчислення функції Y(x).
10. Побудуйте графік (діаграму) функції Y(x)
Варіанти функцій
Варіант 1
Тут хi - елементи одномірного масиву х, i змінюється в межах від 1 до 12 з кроком 1. Чисельні значення а, с, k1, k2 та xi вибрати самостійно.
Варіант 2
де xi та ci -елементи одновимірних масивів, відповідно х(12), с(12); i змінюється від 1 до 12 із кроком 1.
Варіант 3
Масив xi містить 12 елементів. Чисельні значення елементів масиву та величини а, в, с та k вибрати самостійно.
Варіант 4
Масиви Х та С містять по 12елементів. Численні значення елементів масивів і а, k1, k2 вибрати самостійно.
Варіант 5
де масиви Х та С містять по 12 елементів. Чисельні значення величин а, b та елементів масивів вибрати самостійно.
Варіант 6
Тут хi - елементи одновимірного масиву,сзмінюється одночасно з хi від початкового значення = 5 з кроком 1,5; чисельні значення елементів масиву Х(12), величин b та k - вибрати самостійно.
Варіант 7
Тут сi та хi - елементи одновимірних масивів відповідно С(15) та Х(15). Чисельні значення елементів масивів, величин b, k1, k2 та k3 вибрати самостійно.
Варіант 8
Тут хi - елементи масиву Х(12), змінюється одночасно зхвід початкового значення с = -8 з кроком 1.5. Численні значення елементів масиву, величини b вибрати самостійно.
Варіант 9
Тут хi - елементи масиву Х(12), а змінюється одночаснохвід початкового значення а =1.5 з кроком 0.5. Численні значення елементів масиву, величинсі k1 вибрати самостійно.
Варіант 10
Тут хi - елементи масиву Х(12), а змінюється одночасно з хi від початкового значення а = -1.5 з кроком 0.5. Численні значення елементів масиву, величинbі k вибрати самостійно.
Варіант 11
Тут масив містить 20 елементів, коефіцієнти а, c, b, k1, k2, k3 вибрати самостійно.
Варіант 12
Тут хі - елементи одномірного масиву Х(12); k1