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

Практична робота 29 на тему Табличний процесор MS Excel та Visual Basic

« Назад

Практична робота №29

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

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

Хід роботи

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

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

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

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

Алгоритм

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

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

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

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

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

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

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

  8. Призначте для активізації макроса командні кнопки . Використайте панель інструментів Формы. Дайте назви кнопкам, шо співпадають з назвами макросів. Зробіть кнопки виразними.

 

Завдання 2. Розробка функцій користувача.

Між економічними показниками   Yта X існує функціональна залежність:

Розробіть функцію  для обчислення значень Y.

Алгоритм

1. Активізуйте Visual Basic.

2. У стандартному модулі у вікні коду створіть код процедури – функції з ім’ям Ваше_Прізвище (див.мал. 44.1)

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

4. Застосуйте створену функцію для обчислення значень економічного показника Y на робочому аркуші Лист 2.

4.1. Спочатку в деякий діапазон суміжних комірок введіть значення аргумента, наприклад,  від 0 до 30, застосовуючи автозаповнення.

4.2. Далі в деяку комірку введіть формулу, яка містить розроблену функцію користувача, аргумент функції вкажіть як відносне посилання на першу комірку із значенням аргументу.

4.3. Скопіюйте формулу у наступні суміжні комірки. Див.мал. 44.2.

4.4. Задайте від’ємне значення аргументу і переконайтесь у правильності обчислень.

5. Застосуйте розроблений макрос  Maкрос_Fдля відображення формул робочого аркуша.

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(1, 5) (комірка першого стовпця і п’ятого рядка).

Крім того можливі такі способи опису діапазону комірок А2:С7:

1)    Range(“А2:С7”)

2)    Range(Cells(1, 2), Cells(3, 7). 

Повне та неявне посилання на об’єкт. Повне посилання на об’єкт складається з переліку імен вкладених один в одного об’єктів. Наприклад, повне посилання на комірку А3 робочого аркуша з ім’ям Аналіз робочої книги Облік має вигляд:

Application. WorkBooks(„Облік”).Worksheets(„Аналіз”). Range(„А3”)

Кожного разу застосовувати таке посилання досить обтяжливо і не завжди в цьому є потреба. Натомість в  багатьох випадках застосовують неявне посилання на об’єкт. Якщо посилання використовується у програмі, яка буде виконуватись у середовищі Ms Excel, то достатньо застосувати відносне посилання у вигляді

WorkBooks(„Облік”).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

endwith 

 

Задача 1. Розробити процедуру для автоматичного обліку кількості переходів до даного робочого аркуша. Зберегти процедуру у модулі ЭтаКнига.

Алгоритм

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

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

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

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

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

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

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

Алгоритм

1. Створимо робочий аркуш Модуль, перейменувавши робочий аркуш Лист3.
2. Розмістимо на цьому робочому аркуші як графічний об’єкт наведені формули функціональної залежності між величинами.
3. Активізуємо панель Элементы управления. Створимо на робочому аркуші командну кнопку з написом Обчислити (властивість Caption). переключивши MS Excel в режим конструктора.
4. Введемо в комірки поточного робочого аркуша заголовки у відповідності з малюнком 44.4.
5. Двічі клацнувши на командній кнопці, створимо код процедури обробки події – клацання на командній кнопці.

5.1. При цьому спроектуємо виведення значень: b – у комірку B7; x – у комірки стовпця В, починаючи з комірки B9; а – у комірки стовпця С, починаючи з комірки C9; Z – у комірки стовпця D, починаючи з комірки D9.

5.2. Код процедури може бути таким, як на малюнку 44.5.

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

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

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

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

 

Завдання 4. Складіть звіт і захистіть виконану роботу

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

  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"!