Практична робота 27-28 на тему Табличний процесор MS Excel та Visual Basic for Application
« Назад ПРАКТИЧНА РОБОТА №27-28 Тема: "Табличний процесор MS Excel. VisualBasicforApplication: cтворення макросів, розробка функцій користувача, розробка модулів" Мета роботи: навчитись використовувати засоби VisualBasicApplication: MS Excel для розробки макросів, функцій користувача та програм для автоматизації обчислювальних процесів.
Хід роботи Запишіть тему і мету роботи. Складіть звіт виконання практичної роботи. Завдання 1. Розробити макрос для автоматизації відтворення формул робочого аркуша.
МЕТОДИЧНІ РЕКОМЕНДАЦІЇ
Використовуючизасоби MsExcelможна розв’язувати величезну кількість економічних і фінансових задач. Але все ж таки інтерфейс при їх розв’язанні не завжди достатньо гнучкий як і засоби захисту даних. Всі ці недоліки в багатьох випадках можна усувати, використовуючи засоби Visual Basic for Application. Наприклад, іноді виникає необхідність у відтворенні формул робочого аркуша: з метою виведення їх на паперовий носій чи з метою їх аналізу. Для відображення формул робочого аркуша потрібно виконати: меню Сервис-Параметры на вкладці Вид діалогового вікна Параметры встановити прапорець у віконці Формулы групи Параметры окна. На це потрібно витрати значну частину часу. Інтерфейс користувача стосовно цього заходу можна покращити шляхом використання макросу. Для цього його потрібно створити, а потім призначити для його застосування або сполучення клавіш, або кнопку панелі інструментів, або командну кнопку на робочому аркуші. Алгоритм створення макросу
Завдання 2. Розробка функцій користувача. Між економічними показниками Y та X існує функціональна залежність: Розробіть функцію користувача для обчислення значень функції Y. Алгоритм розв’язання 1. Створіть у поточній книзі робочий аркуш з ім’ям Функція Користувача. Активізуйте редактор Visual Basic for Application: меню Сервис =>Макрос=>Редактор Visual Basic (або замість цієї послідовності команд натисніть Alt+F11) 2. У стандартному модулі у вікні коду створіть код процедури – функції з ім’ям Ваше_Прізвище. Зразок коду наведено на рис. 26.2. 3. Збережіть зміни у документі. 4. Застосуйте створену функцію для обчислення значень економічного показника Y на робочому аркуші Функція Користувача. 4.1. Спочатку в деякий діапазон суміжних комірок введіть значення аргумента, наприклад як на Рис.27.3. 4.2.Далі в деяку комірку введіть формулу, яка містить розроблену функцію користувача, аргумент функції вкажіть як відносне посилання на першу комірку із значенням аргументу. 4.3. Скопіюйте формулу у наступні суміжні комірки. Див.рис.26.3. 4.5. Задайте від’ємне значення аргументу і переконайтесь у правильності обчислень. 5. Застосуйте розроблений макрос Maкрос_F для відображення формул робочого аркуша і їх перевірки. 6. Застосуйте розроблений макрос Maкрос_NF для відміни режиму відображення формул.
Завдання 3. Вивчення об’єктної моделі MSExcel, її об’єктів та робота з ними. Розробка процедур стандартного модуля для автоматизації обчислень. Теоретичні відомості Об’єктна модель Ms Excel являє собою ієрархію об’єктів, підпорядкованих об’єкту Application, який відповідає самому додатку Ms Excel. Більшість з цих об’єктівзібрані у бібліотеці об’єктів Ms Excel, але деякі входять до складу бібліотеки об’єктів Ms Office. Основні об’єкти та сімейства:
Діапазон комірок може задаватись не тільки як об’єкт Range, а й з використанням функцій робочого аркуша (об’єкта Worksheet) Rows та Columns. Наприклад: Rows(4); Columns(3). Альтернативним способом звернення до комірки робочого аркуша є властивість Cells робочого аркуша (об’єкта Worksheet). Наприклад, комірку А5 робочого аркуша можна описати як Range(“А5”) або Cells(5, 1) (комірка першого рядка і п’ятого стовпця). Крім того можливі такі способи опису діапазону комірок А2:С7: 1) Range(“А2:С7”) 2) Range(Cells(2, 1), Cells(7, 3)). Повне та неявне посилання на об’єкт.Повне посилання на об’єкт складається з переліку імен вкладених один в одного об’єктів. Наприклад, повне посилання на комірку А3 робочого аркуша з ім’ям Мій макрос робочої книги Практ_27 має вигляд: Application.WorkBooks(„Практ_27”).Worksheets(„Мій макрос ”).Range(„А3”) Кожного разу застосовувати таке посилання досить обтяжливо і не завжди в цьому є потреба. Натомість в багатьох випадках застосовують неявне посилання на об’єкт. Якщо посилання використовується у програмі, яка буде виконуватись у середовищі Ms Excel, то достатньо застосувати відносне посилання у вигляді WorkBooks(„Практ_44”).Worksheets(„Мій макрос”). Range(„А3”). Якщо вказана книга є активною, то посилання можна ще скоротити Worksheets(„Мій макрос”). Range(„А3”). Якщо і вказаний робочий аркуш є активним, то посилання можна скоротити ще: Range(„А3”). При роботі з властивостями і методами одного й того ж об’єкта потрібно повторювати при кожному зверненні одне й теж ім’я об’єкта. Між тим існує оператор with, який дозволяє спростити написання програми і водночас покращити читабельність тексту програми. Наприклад замість коду Range(“A3”).Value=32 Range(“A3”).Font.Italic=True Range(“A3”).Font.Color=vbRed можна записати with Range(“A3”) .Value=32 .Font.Italic=True .Font.Color=vbRed end with або ще так with Range(“A3”) .Value=32 with Font .Italic=True .Color=vbRed end with end with Задача 1. Розробити процедуру для автоматичного обліку кількості переходів до робочого аркуша Функція користувача. Зберегти процедуру у модулі книги. Алгоритм
Зміст цієї процедури такий: у комірку А1 робочого аркуша Функція користувача вводиться текст Кількість відвідувань (за допомогою властивості Value діапазону). За допомогою властивості Name робочому аркушу Лист 2 присвоюється ім’я Функція користувача. При активізації робочої книги буде обчислюватись кількість активізацій означеного робочого аркуша за умови, що користувачі не змінюють вміст комірки В1 Робочого аркуша Лист 2. Спробуйте здійснювати перехід між робочими аркушами книги і проаналізуйте, як змінюється вміст комірки В1 Робочого аркуша Лист 2 (Функція користувача). Збережіть зміни у робочій книзі Практ_27 і закрийте цей документ. Знову активізуйте цей документ. Спробуйте здійснювати перехід між робочими аркушами книги і проаналізуйте, як змінюється вміст комірки В1 Робочого аркуша Лист 2.
Задача 2. Розробити процедуру, яка автоматично вводить в комірки робочого аркуша Модуль значення аргументу x з заданим кроком Dx, потім обчислює значення функції Z=F(a(x), b) у відповідності з заданою формулою де b – випадкове ціле число відрізка [0;100]. При цьому значення b та функції Z автоматично виводяться в комірки робочого аркуша Модуль. Алгоритм розв’язання
5.1. При цьому спроектуємо виведення значень: b – у комірку B7; x – у комірки стовпця В, починаючи з комірки B9; а – у комірки стовпця С, починаючи з комірки C9; Z – у комірки стовпця D, починаючи з комірки D9. 5.2. Код процедури може бути таким: Private Sub CommandButton1_Click() Dim x(7) As Double Dim a(7) As Double Dim z(7) As Double Dim k, b As Integer k = 1 Randomize b = Int(100 * Rnd) Cells(k + 6, 2) = b For i = -2 To 10 Step 2 x(k) = i If b + x(k) >= 0 Then a(k) = Sqr(b + x(k)) Cells(k + 8, 2) = x(k) Cells(k + 8, 3) = a(k) If (a(k) + b) <> 0 Then z(k) = (a(k) ^ 3 + b) ^ (1 / 3) / (a(k) + b) Cells(k + 8, 4) = z(k) Else: Cells(k + 8, 4) = "Н.Р." End If Else Cells(k + 8, 4) = "Н.Р." End If k = k + 1 Next i End Sub Вікно коду показано на Рис.27.6.
Завдання 4. Доступ до функцій робочого аркуша та робота з ними. Розробити програму для автоматичного обліку максимального і мінімального розміру інвестицій. Зберегти процедуру у модулі робочого аркуша Інвестиції. Завдання полягає у розробці проекта, в якому при кожному внесенні змін у певні комірки робочого аркуша відбувається обчислення максимального і мінімального значення в цих комірках, після чого здійснюється форматування комірок, які містять найменше і найбільше значення: вміст комірки з найбільшим значенням форматується жирним шрифтом червоного кольору розміру 20, а вміст комірки з найменшим значенням форматується жирним шрифтом зеленого кольору розміру 20. Решта комірок – звичайним чорним шрифтом. Крім того вікні повідомлення виводитья величина сумарних інвестицій.
МЕТОДИЧНІ РЕКОМЕНДАЦІЇ Об’єкт Application через властивість WorksheetFunction надає можливість використання у коді процедур вбудованих функції робочого аркуша. Наприклад, наведені нижче рядки коду обчислюють і повідомляють: - суму значень комірок вказаного діапазону A1:A4 MsgBox Application.WorkSheetFunction.Sum(Range(“A1:A4”)) - найменше значення комірок вказаного діапазону A1:В4 MsgBox Application.WorkSheetFunction.Min(Range(“A1:В4”)) - середнє значення комірок вказаного діапазону A1:В4 MsgBox Application.WorkSheetFunction. Average (Range(“A1:В4”)) Повний перелік функцій робочого аркуша можна переглянути у діалоговому вікні браузера об’єктів Excel після активізації редактора Visual Basic. Рис.27.8.
Алгоритм розв’язання
Завдання 5. Захистіть виконану роботу.Перелік питань до захисту практичної роботи
З повагою ІЦ "KURSOVIKS"! |