ЭИ_ПОСОБИЕ_ЧАСТЬ_2
.pdfПлан
1.Завантаження VBA. Вивчення складових частин вікна VBA.
2.Створення процедур.
3.Створення функцій користувача.
Технологія виконання завдання
1.Перед першим листом лабораторних робіт у Excel втавить три нових листи. Першому з них призначити ім'я "Процедури", другому - "Функція", третьому "Робітник". Перейти на лист «Рабочий». Заполнить диапазон ячеек А1:А10 произвольным набором чисел.
2.Создать в VBA модуль пользователя, для чего:
2.1.Перейти в редактор Visual Basic - сочетание клавиш Alt+F11 или пункт меню Сервис/ Макрос/ Редактор Visual Basic.
2.2.В окне VBA выполнить Вставка/ Модуль.
3.В среде VBA разработать процедуры (PR1, PR2, PR3).
3.1.Процедура PR1 должна выполнить следующие действия:
3.1.1.Изменить имя листа «Рабочий», присвоив ему название «Студент».
3.1.2.Поместить в ячейку А11 формулу для подсчета суммы значений из заданного диапазона ячеек (А1:А10).
3.1.3.Отформатировать результат вычисления по формуле (начертание – «жирный», цвет – красный).
3.2.Процедура PR2 должна выполнить следующие действия:
3.2.1.Очистить содержимое диапазона ячеек А1:А12.
3.2.2.Записать значение "15" в ячейку А12.
3.2.3.Отформатировать содержимое ячейки А12 (задать размер шрифта равным 20).
3.3.Разработать процедуру PR2, которая должна выполнить следующие действия:
3.3.1.Удалить Лист «Студент».
3.3.2.Закончить работу приложения.
3.4.Перейти на Лист «Процедуры», создать для каждой процедуры элементы управления – кнопки. Назначить им макросы (процедуры).
3.5.Проверить работу процедур, используя кнопки или выполнить их, используя команды меню Сервис.
4.В среде VBA разработать функцию:
44
4.1.Создать в VBA модуль пользователя.
4.2.В среде VBA разработать функцию в соответствии с заданием.
4.3.Перейти на Лист «Функции».
4.4.Проверить работу функции:
4.4.1.Вызвать Мастер функции.
4.4.2.В диалоговом окне Мастера функций (Шаг 1) в поле
Категория выбрать категорию Определенные пользователем.
4.4.3.В списке Выберите функцию выбрать имя разработанной функции. Нажать ОК.
4.4.4.В диалоговом окне Аргументы функции ввести аргументы функции. Нажать ОК.
Варіанти завдань приведені в таблиці 4.
Таблиця 4. Завдання
№ |
Умова |
№ |
Умова |
№ |
Умова |
№ |
Умова |
|||||||||||||||
вар. |
завдання |
вар. |
завдання |
вар. |
завдання |
вар. |
завдання |
|||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
1 |
F= x+y3 |
7 |
F=a + c*x2 |
13 |
F= a*cos x |
19 |
F=xy |
|||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
2 |
F=(x+y)3 |
8 |
F=a*sin x |
14 |
F=a3+3 |
20 |
F= xyz |
|||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
F=a +b*x |
9 |
F=3,5 + 7*x |
15 |
F= |
|
|
|
|
|
|
21 |
F= x3+1 |
|||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
4 |
F= |
x y3 |
|
|
10 |
F=3,5 – 2*x2 |
16 |
F=y + |
x |
|
|
|
22 |
F=1 + |
|
|
x y3 |
|||||
|
|
|
|
|
|
|
|
2 |
|
|
|
4 |
|
|||||||||
|
|
|
4 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
5 |
|
|
|
x y3 |
|
11 |
F=(x-1)*x |
17 |
F= a3 _ |
|
1 |
|
|
2 |
F= a * x -2 |
|||||||
F=x + |
|
2 |
|
|
||||||||||||||||||
|
|
|
|
|
|
|
|
|||||||||||||||
|
|
|
|
|
|
|
|
|||||||||||||||
|
|
|
4 |
|
|
|
|
|
|
|
|
|
|
2 |
|
|||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||
6 |
|
|
a * x |
12 |
F= x2 + 1 |
18 |
2 |
1 |
|
24 |
|
x |
|
|
||||||||
|
|
|
|
|
|
|
|
F= |
- y |
|||||||||||||
|
F= |
2 |
|
|
|
|
|
F= x + 2 |
|
2 |
||||||||||||
|
|
|
|
|
|
|
|
|
|
|||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Контрольні питання
1.Що таке функція?
2.Як створюється функція користувача?
45
3.Що таке процедура?
4.У чому відмінність функції від процедури?
5.Перелічите найбільше часто використовувані типи перемінних?
6.Яка конструкція використовується для опису перемінної?
7.Як викликати функцію і процедуру?
8.Чим відрізняється метод від властивості?
9.Які методи можна застосувати до об'єкта Worksheets?
10.Як зміниться властивість об'єкта, якщо йому призначити властивість
Visible?
Чому ви навчилися?
Створювати функції користувача.
Створювати процедури.
Застосовувати методи до об’єктів та задавати їм властивості.
1. |
Результат виконання |
лабораторної |
роботи |
повинний |
бути |
|
представлений у наступному виді: |
|
|
|
|
||
Елементи |
керування |
для |
виконання |
процедур |
на |
листі |
"Процедури":
2. Листинг модуля VBA має такий вигляд:
Sub PR1()
Application.Worksheets("Рабочий").Name = "Студент"
Worksheets("Студент").Range("A11").Formula = "=Sum(A1:A10)"
Worksheets("Студент").Range("A11").Font.Bold = True
Worksheets("Студент").Range("A11").Font.ColorIndex = 3
End Sub
Sub PR2()
Worksheets("Студент").Range("A1:A12").Clear
Worksheets("Студент").Range("A3") = 15
46
Worksheets("Студент").Range("A3").Font.Size = 20
End Sub
Sub PR3()
Worksheets("Студент").Delete
Application.Quit
End Sub
3. Результат виконання функції в осередку електронної таблиці.
Використання інструкцій VBA
Під керуючими конструкціями розуміють звичайно оператори (інструкції) і убудовані функції мови за допомогою яких здійснюється керування ходом виконання програми. У VBA використовуються такі основні структури керування:
If…Then…Else Select Case
For…Next
Do…Loop...
Нижче будуть приведені приклади синтаксису цих структур.
Увага! Квадратні дужки означають необов'язковість використання цих елементів, тобто вони можуть бути відсутні.
1. Умовний оператор Іf...Then...Else.
Умовне виконання групи операторів у залежності від значення вираження.
Синтаксис:
1.1.Проста лінійна форма:
If умова Then [оператор_1] [Else оператор_2]
Оператор_1 виконується, якщо умова істина, у противному випадку виконується оператор_2.
Умова це вираження логічного типу. Вираження може бути простим і складним. У першому випадку використовуються логічні відносини, перелік яких приведений у таблиці 3.1. Складні логічні вираження виходять із простих за допомогою використання логічних операцій, перелік яких приведений у таблиці 3.2. і, при необхідності, круглих дужок.
1.2. Блочна форма:
47
If умова1 Then [блок_ операторів1] [ElseIf умова_2 Then [блок_ операторів2] [ElseIf умова_3 Then [блок_ операторів_3]
...
[Else
[блок_ операторів]] End If
Блок операторів_i виконується, якщо умова_ І є істина, у противному випадку виконується блок_ операторів.
Оператори Else і Elseіf є необов'язковими. Можна використовувати стільки операторів Elseіf скільки потрібно в блоці Іf, але вони ніколи не можуть знаходитися після оператора Else. Блоки операторів Іf можуть бути вкладеними, тобто містити в собі інші Іf блоки.
Таблиця 3.1 |
Логічні відносини |
||
|
|
|
|
Операція |
Найменування |
Вираження |
Результат |
|
|
|
|
= |
дорівнює |
A=B |
True, якщо A дорівнює B |
|
|
|
|
<> |
не дорівнює |
A<>B |
True якщо A не дорівнює B |
|
|
|
|
> |
більше |
A>B |
True якщо A більше B |
|
|
|
|
< |
меньше |
A<B |
True якщо A менше B |
|
|
|
|
>= |
більше або дорівнює |
A>=B |
True якщо A більше або дорівнює B |
|
|
|
|
<= |
меньше або |
A<=B |
True якщо A менше, або дорівнює B |
|
дорівнює |
|
|
|
|
|
|
Таблиця 3.2 |
Логічні операцій |
|
|
|
||
|
|
|
|
|
|
|
Операція |
Найменування |
Вираження |
|
A |
B |
Результат |
|
|
|
|
|
|
|
Not |
Логічне |
Not A |
False |
|
|
True |
заперечення |
True |
|
|
False |
||
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
True |
|
True |
True |
And |
Логічне И |
A And B |
True |
|
False |
False |
False |
|
True |
False |
|||
|
|
|
|
|||
|
|
|
False |
|
False |
False |
|
|
|
|
|
|
|
Or |
Логічне ИЛИ |
A Or B |
True |
|
True |
True |
True |
|
False |
True |
|||
|
|
|
|
|||
|
|
|
|
|
|
|
48
|
|
|
False |
True |
True |
|
|
|
False |
False |
False |
|
|
|
|
|
|
2. Оператор Select Case.
Виконує одну з кілька груп операторів у залежності від значення вираження, що перевіряється.
Синтаксис:
Select Case вираження, що перевіряється [Case список виражень-n [оператори-n]] ...
[Case Else
[else оператори]]
End Select
Приклад використання.
Написати функцію користувача, що обчислює значення функції F в залежності від значення аргументу X:
sin 4 (1 x x2) |
,0 x 1 |
|||||||||
|
cos(10x 3) |
|||||||||
|
|
|||||||||
|
|
|
|
|
|
|
|
|
|
|
|
x |
3 |
5x |
|
, |
2 x 5, або х 10 |
||||
|
|
|||||||||
|
|
|
|
|||||||
F |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
x2 |
4 |
|
|
|
|
6 x 8 |
||||
|
|
|
|
|
|
, |
|
|
|
|
|
|
|
4 |
|
|
|
||||
x2 |
|
|
|
|
|
|||||
0 |
|
|
|
|
|
|
|
|
иначе |
|
|
|
|
|
|
|
|
|
|
|
|
1) Конструкція If…Then…Else
FunctionF1_If(X) IfX>=0AndX<=1Then F1_If=Sin(1+X+X^2)^4/Cos(10*X+3) ElseIfX>=2AndX<=5OrX>10Then F1_If=Sqr(Abs(X^3-5*X)) ElseIfX>=6AndX<=8Then F1_If=(X^2+4)/(X^2-4)
Else F1_If=0 EndIf EndFunction
2)Конструкція Select Case
49
Function F1_Case(X)
Select Case X
Case 0 To 1
F2_Case = Sin(1 + X + X ^ 2) ^ 4 / Cos(10 * X + 3)
Case 2 To 5, Is>10
F2_Case = Sqr(Abs(X ^ 3 - 5 * X))
Case 6 To 8
F2_Case = (X ^ 2 + 4) / (X ^ 2 - 4)
Case Else
F2_Case = 0
End Select
End Function
3. Оператори циклу.
Для виконання якого-небудь оператора, або блоку операторів, кілька разів використовуються оператори циклу. Існують 2 види циклів:
цикли, що виконуються заздалегідь задану кількість разів, у VBA для цього використовується оператор For ... Next;
цикли, що виконуються доти поки не буде виконана яка-небудь умова
- оператор Do ... Loop.
3.1.Є різновиди кожного типу циклів.
Нижче приведений синтаксис основних видів операторів циклу. Оператор циклу For ... Next
Повторює блок операторів задану кількість разів.
Синтаксис: For лічильник = початок To кінець [Step крок] [оператори]
[Exit For]
[оператори] Next [лічильник], де:
лічильник - обов'язковий; числова перемінна, котра використовується як лічильник циклу; початок - обов'язковий;. початкове значення лічильника;
кінець - обов'язковий; кінцеве значення лічильника; крок - необов'язковий; значення, на яке змінюється лічильник при кожнім виконанні циклу. За замовчуванням дорівнює одиниці.
50
оператори – оператори; одних, або більш операторів між For and Next, що виконуються задану кількість разів.
Оператор циклу Do ... Loop
Повторює блок операторів, поки умова щира - whіle, або доти , коли умова стане щирим - untіl.
Синтаксис: Do [{While | Until} умова] [оператори]
[Exit Do]
[оператори]
Loop
Приклад використання різних типів операторів циклу. Обчислити значення функції F (дивиться попередній приклад) для декількох значень аргументу X, що знаходяться на листі Excel у стовпці A, із другого рядка, тобто A2 - це адреса першого значення аргументу.
Пояснення. У VBA діапазон осередків "A:A" означає стовпець. У російськомовній версії Excel функція СЧЕТЗ підраховує кількість заповнених осередків у виділеному діапазоні. В англомовній версії це функція Count. Таким чином, функціяApplication.Count(Range("A:A")) підраховує кількість не порожніх (заповнених) осередків у стовпці A
1) Оператор цикла For ... Next
Function F1_If(X)
...........................
End Function Sub For_Next() Dim n As Integer
n = Application.CountA(Range("A:A")) For r = 2 To n
X = Cells(r, 1).Value Cells(r, 2).Value = F1_If(X) Next
End Sub
2) Оператори циклу Do … Loop
а) конструкція Do While... Loop
Function F1_If(X)
...........................
End Function
Sub Do_While()
51
n = Application.CountA(Range("A:A")) r = 2
Do While r <= n
X = Cells(r, 1).Value Cells(r, 2).Value = F1_If(X) r = r + 1
Loop End Sub
б) конструкція Do Until... Loop
Function F1_If(X)
...........................
End Function
Sub Do_Until()
n = Application.CountA(Range("A:A")) r = 2
Do Until r > n
X = Cells(r, 1).Value Cells(r, 2).Value = F1_If(X) r = r + 1
Loop End Sub
Методичні вказівки до виконання завдання № 6
Тема: Використання інструкцій VBA
Ціль: Одержати практичні навички розробки програм у середовищі VBA з використанням керуючих конструкцій мови.
План
1.Вивчення прикладів використання керуючих конструкцій VBA по методичних матеріалах лабораторної роботи.
2.Розробка процедури, що передбачає використання умовного оператора Іf і оператора циклу For ... Next.
3.Призначення процедурі елемента керування - кнопку. Перевірка роботи процедури.
4.Модифікування процедури, заміною оператора циклу For ... Next на одну з кострукций Do ... Loop. Перевірка роботи процедури.
52
Технологія виконання завдання
Постановка задачі: Виконати автоматизований облік максимального і мінімального виторгу від продажів продукції фірми "Вектор". Акцентувати увага в створеній звітній таблиці на наступному: для продукції, виторг від продажів якої є максимальної, виконати оформлення осередків напівжирним червоним шрифтом, для продукції з мінімальним виторгом від продажів - нормальним зеленим шрифтом, а для всіх інших - нормальним чорним шрифтом.
Для рішення поставленої задачі необхідно виконати наступні дії:
1.Відкрити книгу MS Excel з лабораторними роботами.
2.Перейти на листок "Аналіз попиту".
3.Уставити новий робочий лист "Автоматизований облік".
4.Скопіювати фрагмент таблиці "Аналіз попиту і продажів торговельної фірми "Вектор" (стовпці "Код", "Найменування продукції", "Виторг від продажів") у буфер обміну.
5.Уставити фрагмент таблиці "Аналіз попиту і продажів торговельної фірми "Вектор" на новому листі робочої книги "Автоматизований облік", починаючи з осередку А1 так, щоб заголовок розмістився в першому рядку, шапка таблиці - у другому і третьому рядках, код - в осередках діапазону А4:А10, нименования продукції - в осередках діапазону В4:У10, значення виторгу від реалізації - в осередках діапазону З4:З10.
6.У середовищі VBA розробити процедуру AvtUchet, що виконує
автоматизований облік максимального і мінімального виторгу від продажів продукції.
Примітка. Об'єкт Application через властивість WorksheetFunction надає можливість використовувати в коді убудовані функції робочого листа.
Для зручності роботи з використовувати константи, убудовані в VBA: vbBlack – чорний, vbRed – червоний, vbGreen – зелений.
7.Перевірити роботу процедури, призначивши їй елемент керування - кнопка.
8.Модифікувати процедуру, замінивши оператор циклу For ... Next на одну з кострукций Do ... Loop. Перевірити роботу процедури.
Контрольні питання
53