Роздрукувати сторінку
Главная \ Методичні вказівки \ Методичні вказівки \ 1432 Практична робота 27-28 на тему Табличний процесор MS Excel та Visual Basic for Application

Практична робота 27-28 на тему Табличний процесор MS Excel та Visual Basic for Application

« Назад

ПРАКТИЧНА РОБОТА №27-28

Тема: "Табличний процесор MS Excel. VisualBasicforApplicationcтворення макросів, розробка функцій користувача, розробка модулів"

Мета роботи: навчитись використовувати засоби VisualBasicApplication:  MS Excel  для розробки макросів, функцій користувача та програм для автоматизації обчислювальних процесів.                       

Хід роботи

Запишіть тему і мету роботи. Складіть звіт виконання практичної роботи.

Завдання 1.  Розробити макрос для автоматизації відтворення формул робочого аркуша.

 

МЕТОДИЧНІ РЕКОМЕНДАЦІЇ

 

Використовуючизасоби  MsExcelможна розв’язувати величезну кількість економічних і фінансових задач. Але все ж таки   інтерфейс при їх розв’язанні не завжди достатньо гнучкий як і засоби захисту даних.

Всі ці недоліки в багатьох випадках можна усувати, використовуючи засоби Visual Basic for Application.

Наприклад, іноді виникає необхідність у відтворенні формул робочого аркуша: з метою виведення їх на паперовий носій чи з метою їх аналізу. Для відображення формул робочого аркуша потрібно виконати: меню Сервис-Параметры на вкладці Вид діалогового вікна Параметры встановити прапорець у віконці Формулы групи Параметры окна. На це потрібно витрати значну частину часу. Інтерфейс користувача стосовно цього заходу можна покращити шляхом використання макросу. Для цього його потрібно створити, а потім призначити для його застосування або сполучення клавіш, або кнопку панелі інструментів, або командну кнопку на робочому аркуші.

Алгоритм створення макросу

  1. У своїй папці на сервері створіть документ –   лист MS Excel, дайте  ім’я Практ_27.

  2. Перейменуйте робочий аркуш  Лист 1  на МійМакрос

  3. Введіть у комірки цього робочого аркуша дані і виконайте деякі обчислення, наприклад як уу відповідності з наведеними у таблиці Рис.27.1.

  4. Створіть макрос з ім’ям Maкрос_Я, доступний у всій робочій книзі, при активізації якого будуть відображені всі формули активного робочого аркуша. Призначте для його активізації сполучення клавіш Ctrl.

  5. Створіть макрос Макрос_Ф, доступний у всій робочій книзі, при активізації якого буде виключатись режим відображення формул робочого аркуша. Призначте для його активізації сполучення клавіш Ctrl +ф.

  6. Переконайтесь у тому, що створені макроси доступні у всіх робочих аркушах даної книги.

  7. Запишіть у звіт VB код створених макросів.

  8. Призначте для активізації макроса командні кнопки на робочому аркуші Міій макрос. Використайте панель інструментів Элементы управления. Дайте назви кнопкам, шо співпадають з діями  макросів: „Показати формули” та „Сховати формули”. Зробіть кнопки виразними.

  9. Створіть свою панель інструментів з ім’ям Панель_Ваше прізвище і розмістіть на ній кнопки макросів, призначте значки для кнопок .

 

Завдання 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крос_для відображення формул робочого аркуша і їх перевірки.

6. Застосуйте розроблений макрос  Maкрос_NF для відміни режиму відображення формул.

Завдання 3. Вивчення об’єктної моделі   MSExcel, її об’єктів та робота з ними. Розробка процедур стандартного модуля для автоматизації обчислень.

Теоретичні відомості

Об’єктна модель Ms Excel являє собою ієрархію об’єктів, підпорядкованих об’єкту Application, який відповідає самому додатку Ms Excel. Більшість з цих об’єктівзібрані у бібліотеці об’єктів Ms Excel, але деякі входять до складу бібліотеки об’єктів Ms Office.

Основні об’єкти та сімейства:

Об’єкт

Сімейство

Опис

Application

 

Активний додатОК Ms Excel

WorkBook

WorkBooks

Сімейство об’єктів, кожний з яких являє собою робочу книгу

Worksheet

Worksheets

Сімейство об’єктів, кожний з яких являє собою робочий аркуш даної робочої книги

Chart

Charts

Сімейство об’єктів, кожний з яких являє собою діаграму у даній робочій книзі

Name

Names

Сімейство об’єктів, кожний з яких є ім’ям об’єкта , наприклад ім’ям робочого аркуша

Style

Styles

Сімейство об’єктів, кожний з яких є вбудованим стилем або стилем користувача, який використовується у робочій книзі

Window

Windows

Сімейство об’єктів, кожний з яких є вікном робочої книги

WorksheetFunction

 

Об’єкт, який являє собою контейнер для функцій робочого аркуша

Range

 

Діапазон комірок робочого аркуша.

Приклади:

-         Range(“A:G”) – діапазон комірок вказаного діапазону  стовпців;

-         Range(“2:2”) - діапазон комірок другого рядка;

-         Range(“А2”) - комірка А2.

Діапазон комірок може задаватись не тільки як об’єкт 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. Активізуйте редактор Visual Basic з меню Сервис або натисканням клавіш Alt + F11.

  2. У вікні браузера проекту двічі клацніть на значку Эта Книга і введітькод процедури обробки події активізації робочого аркуша (SheetActivate)у відповідності  з наведеним на рисунку 27.4.

Зміст цієї процедури такий: у комірку А1 робочого аркуша Функція користувача вводиться текст Кількість відвідувань (за допомогою властивості Value діапазону). За допомогою властивості Name  робочому  аркушу Лист 2 присвоюється ім’я Функція користувача.

При активізації робочої книги буде обчислюватись кількість активізацій означеного робочого аркуша за умови, що користувачі не змінюють вміст комірки В1 Робочого аркуша Лист 2.

Спробуйте здійснювати перехід між робочими аркушами книги і проаналізуйте, як змінюється вміст комірки В1 Робочого аркуша Лист 2 (Функція користувача).

Збережіть зміни у робочій книзі Практ_27 і закрийте цей документ. Знову активізуйте цей документ. Спробуйте здійснювати перехід між робочими аркушами книги і проаналізуйте, як змінюється вміст комірки В1 Робочого аркуша Лист 2.

Задача 2. Розробити процедуру, яка автоматично вводить в комірки робочого аркуша  Модуль значення аргументу x з заданим кроком Dx, потім обчислює значення функції Z=F(a(x), b) у відповідності з заданою формулою

де b – випадкове ціле число відрізка [0;100]. При цьому значення b та функції Z автоматично виводяться в комірки робочого аркуша Модуль.

Алгоритм розв’язання

  1. Створимо робочий аркуш Модуль, перейменувавши робочий аркуш Лист3.

  2. Розмістимо на цьому робочому аркуші як графічний об’єкт наведені формули функціональної залежності між величинами.

  3. Активізуємо панель Элементы управления. Створимо на робочому аркуші командну кнопку з написом Обчислити (властивість Caption). переключивши MS Excel в режим конструктора.

  4. Введемо в комірки поточного робочого аркуша заголовки у відповідності із рисунком 27.5:

  5. Двічі клацнувши на командній кнопці, створимо код процедури обробки події – клацання на командній кнопці.

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. 

  1. Перейдіть до робочого аркуша Модуль і виконайте відлагодження та тестуваня програми. Для цього натискайте кнопку Обчислити і аналізуйте отримані результати, адже кожного разу буде генеруватись випадкове ціле число, яке впливає на порядок виконання обчислень.

    Зауваження. Економічний зміст випадкового числа може бути, наприклад, таким: означати випадковий попит на продукцію.

  2. Переконайтесь у правильності роботи програми. Проаналізуйте результати обчислень, порівняйте їх з наведеними на Рис.27.7.

  3. Збережіть внесені у документ зміни.

Завдання 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.

 

 Алгоритм розв’язання 

  1. На робочому аркуші Інвестиції створіть зміст, відображений на Рис.27.9.

  2. Активізуйте редактор Visual Basic з меню Сервис або натисканням клавіш Alt + F11.

  3. У вікні браузера проекту двічі клацніть на значку Лист4 (Інвестиції) і введітькод процедури внесення змін у діапазон комірок) у відповідності  з наведеним на Рис.27.10.

  4. Результати автоматичної обробки даних наведені на Рис.27.11.

Завдання 5. Захистіть виконану роботу.

Перелік питань до захисту практичної роботи

  1. Як включити режим відображення формул робочого аркуша?

  2. Як створити макрос та призначити для його активізації командну кнопку?

  3. Як вилучити макрос?

  4. Як розробити функцію користувача?

  5. Яку структура має код функції користувача?

  6. Де зберігаються макроси? Функції користувача?

  7. Який порядок застосування функції користувача?

  8. В якій категорії зберігаються функції користувача?

  9. Які об’єкти має об’єктна модель MS Excel?

  10. Який об’єкт об’єктної моделі MS Excel є контейнером функцій робочого аркуша?

  11. Які функції має робочий аркуш? Наведіть приклади.

  12. Чи існує функція для обчислення натурального логарифму числа, яке зберігається у комірці робочого аркуша?

  13. Як звернутись до тієї чи іншої функції робочого аркуша?

  14. Як створити модуль робочого аркуша? Модуль робочої книги?

  15. Як звернутись у процедурі до діапазону комірок? Окремої комірки робочого аркуша?

  16. Яке призначення має оператор with?

  17. Який алгоритм розробки програми автоматизації обчислень?

  18. Які імена мають процедури, пов’язані з подіями робочого аркуша?

  19. Який режим MS Excel потрібно активізувати, щоб на робочому аркуші можна було розміщувати елементи управління?

  20. Як програмно перейменувати робочий аркуш?

З повагою ІЦ "KURSOVIKS"!