Практична робота 9:"Табличний процесор Excel"

ПРАКТИЧНА РОБОТА № 9.

 

 Тема: Робота із VBA. Розрахунок комісійних за допомогою VBA. Створення простих функцій користувача і програм на VBA.

 Ціль: Навчитися працювати із VBA, створювати прості функції користувача і програми, а також розраховувати комісійні за допомогою VBA.

 

МЕТОДИЧНІ ВКАЗІВКИ.

 

9.1. Користувальницькі функції робочого листа й VBA.

 

Функції користувача будуються  в стандартному модулі редактора VBA. VBA (Visual Basic for Applications) - єдина для всіх додатків Microsoft Office мова програмування, побудована на основі Visual Basic. Щоб потрапити в редактор VBA, виберіть команду Сервис -> Макрос -> Редактор Visual Basic або натисніть комбінацію клавіш <Alt>+<F11>. Він має стандартний вигляд для Windows-додатків: рядок меню, панель інструментів (у цьому випадку стандартну) і два вікна Project – VBAProject й Properties.

У вікні VBAProject відображається реєстр модулів і форм, що входять у створюваний проект. Модуль, спрощено говорячи, – це лист, у якому набирається код програми. Подвійним щигликом на значку модуля у вікні Project –> VBAProject можна відкрити відповідний модуль. Значок активного модуля в цьому вікні виділяється сірими кольорами. В VBA у кожного робочого листа є власний модуль. Крім того, робоча книга має свій, і якщо в проекті створюються користувальницькі форми, те кожна з них має по модулю. Єдиний модуль, що нам зараз необхідний, – це стандартний модуль. Щоб додати його в проект, виберіть команду Insert -> Module (мал. 9.1).

 

9.2. Структура коду функції користувача

 

Після створення стандартного модуля, перейдемо до написання функції користувача. У загальному випадку, функція користувача має такий вигляд:

 

Function name   (arglist)

[statements]

End Function

 

де name –  ім'я функції користувача; argilst – список аргументів; statements – це послідовність інструкцій, виконуваних при знаходженні значення функції.

У сукупності вони утворять так називане тіло функції. Важлива особливість функції користувача полягає в тому, що носієм повертаємого значення є її ім'я, тобто ідентифікатор name. Тому серед інструкцій програми повинна бути присутнім, принаймні, одна, котра є оператором присвоювання імені функції значення якого-небудь вираження.

 

 

Мал. 9.1. Додавання модуля VBA.

 

 

 

9.3. Найпростіша функція користувача

 

Для початку, побудуємо дуже просту функцію, що повертає вартість товару по його вартості без ПДВ і значенню ставки ПДВ. У стандартному модулі наберіть наступний код (мал. 9.2):

 

Function Стоимость (Стоимостьбезндс,   НДС)

Стоимость = Стоимостьбезндс  *   (1  + НДС  /  100)

End Function

 

Якщо правильно набрати перший рядок:

 

Function Стоимость (Стоимостьбезндс,   НДС)

 

те при натисканні <Enter> остання інструкція End Function додасться редактором VBA автоматично. Варто лише набрати інструкцію, що залишилася, у тілі функції користувача, і користувальницька функція буде створена. За замовчуванням вона потрапляє в розділ Определенные пользователем списку Категория вікна Мастер функций.

 

 

Мал. 9.2. Введення функції користувача.

 

 

Знайдемо, наприклад, вартість товару з урахуванням ПДВ за умови, що ПДВ дорівнює 25%, а вартість товару без урахування ПДВ дорівнює 1000 (мал. 9.3). Для цього:

 

1.   Виберіть комірку А2 й введіть у неї число 1000 (вартість без ПДВ).

2.   Виділіть комірку В2 й введіть у неї число 25 (ПДВ).

3.   Виділіть комірку С2, у якій знайдемо значення функції.

4.   Виберіть команду Вставка -> Функция.

5. У першому вікні Майстра функцій у списку Категория знайдіть Определенные пользователем, а потім у списку Функция виберіть функцію Стоимость та натисніть кнопку ОК.

6.  У другому вікні Майстра функцій у поле СтоимостьБезНДС введіть посилання на комірку А2, а в поле ПДВ – посилання на комірку В2 і натисніть кнопку ОК.

 

9.4. Математичні функції

 

При створенні функцій користувача варто знати, які убудовані математичні функції є в VBA. Таких функцій зовсім не багато:

 

Мал. 9.3. Використання функції користувача.

 

 

Функція              Повертає значення

 

Abs (number) Абсолютна величина числа

 

Atn (number) Арктангенс

 

Cos(number) Косинус

 

Exp(number) Експонента, тобто результат піднесення підстави

натурального логарифма до зазначеного степеня

 

Log (number) Натуральний логарифм

 

Rnd (number) Випадкове число з інтервалу [0,1). Якщо number менше нуля,

то Rnd повертає щоразу те саме число. Якщо number більше нуля або опущений, то Rnd повертає наступне випадкове число в послідовності. Якщо number  дорівнює нулеві, то Rnd повертає випадкове число, отримане при попередньому виклику цієї функції (перед викликом функції Rnd використовуйте оператор Randomize).

 

Функція              Повертає значення

 

Sgn(number) Знак числа

 

Sin(number) Синус

 

Sqr (number) Квадратний корінь із числа

 

Tan (number) Тангенс

 

Fix (number),

Int (number), Обидві функції Int й Fix відкидають дробову частину числа й

повертають ціле значення. Різниця полягає в тому, що для негативного значення параметра функція Int повертає найближче негативне ціле число менше або рівне вказаному, a Fix – найближче негативне ціле число, більше або рівне вказаному.

 

Створимо ще одну користувальницьку функцію: F(x) = cos² (πх). Щоб це зробити, потрібно в стандартному модулі набрати наступний код:

 

Function F(x)

Pi = Atn(1) * 4

F = Cos(Pi * x)^2

End Function

 

В VBA немає функції, що повертає число π. Тому для його знаходження застосовується функція Atn. Можна задати π явно, вказавши достатнє число значущих цифр.

 

9.5. Оператор умовного переходу

 

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

 

If condition Then   [statements]   [Else elsestatements]

 

Якщо умова condition приймає значення True, то виконуються інструкції statements, якщо False, те інструкції  elsestatements. Гілка Else є необов'язковою.

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

 

 

 

If condition Then

[statements]

[Else  

[elsestatements] ]

End If

 

 

9.6. Розрахунок комісійних

 

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

 

 

Обсяг продажів за тиждень, грн.        Комісійні, %

 

Від 0 до 9 999     8

Від 10 000 до 19 999    10

Від 20 000 до 39 999    12

Більше 40 000     14

 

Побудуємо для розрахунку комісійних у стандартному модулі користувальницьку функцію:

 

Function Комиссионные (Продажи)

if Продажи <= 9999 Then

Комиссионные = Продажи *  0.08

ElseIf Продажи <= 19999 Then

Комиссионные = Продажи * 0.1

ElseIf Продажи <= 39999 Then

Комиссионные = Продажи *  0.12

Else

Комиссионные = Продажи *  0.14

End If

End Function

 

Можна трохи ускладнити завдання: будемо вважати, що комісійні залежать від ставки, займаної менеджером. Якщо він прийнятий у постійний штат фірми, то комісійні нараховуються за описаним вище правилом. Якщо ж він перебуває на іспитовому терміну, то його комісійні становлять 75% від номіналу.

Для рішення цього завдання до функції Комиссионные треба додати ще один аргумент – Ставка, що ідентифікує ставку менеджера. Якщо менеджер перебуває на іспитовому терміну, то значення аргументу покладається рівним 0, а якщо він зарахований у штат, то значення аргументу покладається рівним 1. Тоді, функція Комиссионные прийме вид:

 

 

Function Комиссионные(Продажи, Ставка)

If Продажи <= 9999 Then

Оплата = Продажи * 0.08

ElseIf Продажи <= 19999 Then

Оплата = Продажи * 0.1

ElseIf Продажи <= 39999 Then

Оплата = Продажи * 0.12

Else

Оплата = Продажи * 0.14

End If

If Ставка = 0 Then

Комиссионные = 0.75 * Оплата

Else

Комиссионные = Оплата

End If

End Function

 

Після розрахунку комісійних за допомогою користувальницької функції «Комиссионные» робочий лист буде виглядати так:

 

 

Мал. 9.4. Розрахунок комісійних за допомогою функції користувача

 

 

 

9.7. Найпростіша програма на VBA

 

Напишемо найпростішу програму, результатом дії якої буде відображення на екрані діалогового вікна з  вітанням "Hello, World!". Cпочатку перейдіть у редактор Visual Basic, натиснувши комбінацію клавіш <Alt>+<F11>. Потім додайте в проект стандартний модуль, де наберіть наступний код:

 

Sub Hello ()

MsgBox "Hello, World!"

End Sub

 

Для запуску програми на виконання натисніть клавішу <F5> або виберіть команду Run -> Run Sub/UserForm.

 

 

Мал 9.5. Результат роботи програми на VBA.

 

У цій програмі відбувається наступне: перший і третій рядки – це перша й остання інструкції продедури, написаної мовою VBA. Процедура на VBA має наступний синтаксис:

 

Sub name   (arglist)

[statements]

End Sub

name – ім'я процедури користувача; arglist – список аргументів, що може бути й порожнім, як у нашому випадку; statements – це послідовність інструкцій.

Оператор MsgBox має наступний синтаксис: MsgBox prompt. У результаті його дії на екрані відображається діалогове вікно із кнопкою ОК і повідомленням, текст якого задається параметром prompt (рядок тексту повинний бути  в лапках!).

 

 

ПРАКТИЧНЕ ЗАВДАННЯ.

 

 9.1. За допомогою математичних функцій VBA скласти користувальницьку функцію для обчислення вираження:

 

 Варіант 1:  sin ;

 Варіант 2:  cos (2x+1);

 Варіант 3:  arctg 3x;

 Варіант 4:  cos 2x+1;

 Варіант 5:  3ctg x;

 Варіант 6:  lоg x;

 Варіант 7:  ;

 Варіант 8:  ; 

 Варіант 9:  |6x+3|;

 Варіант 10:  sin(cos x).

 

9.2.  Скласти функцію нарахування комісійних за правилом: якщо продукції продано менш, ніж на С1 грн., комісійні становлять Х1% від вартості, якщо продукції продано менш, ніж на С2 грн., але не менш, ніж на С1 грн., комісійні становлять Х2% від вартості, якщо продукції продано не менше, ніж на С2 грн., комісійні становлять Х3% від вартості проданої продукції.

 

   С1  С2  Х1  Х2  Х3 

 

Варіант 1:  1000  2000  1,0  1,5  2,3

 Варіант 2:  2000  4000  2,0  3,0  4,5

 Варіант 3:  3000  6000  3,0  4,5  6,8

 Варіант 4:  4000  8000  4,0  6,0  9,0

 Варіант 5:  5000  10000  5,0  7,5  11,3

   

С1  С2  Х1  Х2  Х3 

 

Варіант 6:  6000  12000  6,0  9,0  13,5

 Варіант 7:  7000  14000  1,0  1,5  2,3

 Варіант 8:  8000  16000  2,0  3,0  4,5

 Варіант 9:  9000  18000  3,0  4,5  6,8

 Варіант 10:  10000  20000  4,0  6,0  9,0

 

 

 

КОНТРОЛЬНІ ПИТАННЯ.

 

1. Що таке VBA?
2. Як запустити редактор VBA?
3. Що таке модуль VBA?
4. Як додати в робочу книгу новий модуль?
5. Яку структуру має функція на VBA?
6. Як побудувати в MS Excel свою функцію?
7. Як використати самостійно створену функцію?
8. Перелічте відомі вам математичні функції VBA.
9. Для чого використовується оператор умовного переходу?