Створення макроса за допомогою макрорекордера

 Програма виконуватиметься, поки у вікно InputBox не буде  введено число 0 (k =0).

 Приклад 4. Постановка  задачі: задана таблиця, показана  на малюнку 1 з назвами стовпців:

 

 

прізвище працівника;

місячний оклад;

дата обліку;

коефіцієнт участі в роботі.

 

 

 

 

 

1 | 2 | 3 | 4

Артим | 100 | 01.06.00 | 1

Дрозд | 200 | 01.06.00 | 1,2

Лісенко | 300 | 01.03.00 | 0,9

Лісенко | 300 | 01.05.00 | 0,8

Дрозд | 200 | 01.04.00 | 1

Артим | 100 | 01.02.00 | 1,1

Артим | 100 | 01.05.01 | 0,8

 Малюнок 1. Поточна інформація  про працівників цеху

 Необхідно:

використовуючи MS Word, утворити файл з даними, зразки яких подані в  таблиці;

транспортувати (скопіювати) дані в таблицю MS Excel, зберегти у вигляді  файлу. Відредагувати таблицю, виготовити назви стовпців. Занести у верхній  правий кут таблиці MS Excel поточну  системну дату;

виготовити звіт, у якому  відобразити відомість на зарплату за 2-й квартал 2000 року, згрупувати дані по працівниках. Зарплата дорівнює місячному  окладу працівника, помноженому на коефіцієнт участі в роботі;

виготовити звіт, у якому  відобразити розподіл зарплат робітників по кварталах;

побудувати графік сумарних витрат на зарплату за кожний місяць 2000 року;

виготовити кнопку, що запускає макрос для редагування комірок  таблиці Excel.

 Виконання роботи прикладу 4

 В процесі виконання  роботи виконувались такі команди:

 Задача 4.1. Утворення файлу  MS Word

 

головне меню MS Word: команда  Файл, Створити;

вікно Створення документа: перемикач Звичайний, команда Ок;

вікно Документ1: внесення в  документ потрібних написів та заголовків;

головне меню: команда Таблиця, Додати таблицю;

вікно Вставка таблиці, за допомогою штовхачів вибрати  кількість рядків – 8, кількість  стовпчиків – 4, команда Ок;

шляхом перетягування  ліній таблиці   встановити потрібні розміри клітинок, внести дані в  таблицю;

головне меню: команда Файл, Зберегти як;

вікно Збереження документа: вибрати потрібну папку, внести в  поле ім’я файлу Zarplata.doc, команда  Зберегти.

виокремити готову таблицю, головне меню: команда Виправлення, Копіювати.

 Задача 4.2. Утворення файлу  MS Excel

увійти в MS Excel;

головне меню MS Excel: команда  Файл, Створити;

вікно Книга1: встановити курсор у комірку А1, головне меню: команда  Виправлення, Вставити;

при потребі відредагувати  назви таблиці та імен стовпців;

виокремити рядок з  назвами стовпців (діапазон A4:D4), контекстне меню: команда Формат комірок;

вікно Формат комірок: вибрати  колір та узір тла з написами імен стовпців;

виокремити діапазон A1:D1 (назви  таблиці), головне меню: команда Формат, Комірки;

вікно Формат комірок: розділ Вирівнювання, перемикач Об'єднання  комірок, команда Ок;

встановит назви таблиці  по центру поля об'єднаних комірок;

встановити курсор в комірку F1. Головне меню: команда Вставити, Функцію;

Вікно Майстра функцій: вибрати  тип функції Дата і час, вибрати  із списку функцію Сьогодні, команда  Ок;

контекстне меню: команда  Формат комірок (курсор – у комірці F1);

вікно Формат комірок: розділ Межі, вибрати тип і колір ліній  комірки з датою;

вікно Формат комірок: розділ Число, вибрати зі списку тип Дата, вибрати зразок дд.мм.рр, команда  Ок. Перевірити візуально чи правильно  працює функція дати;

виокремити таблицю з  заголовком, головне меню: команда  Формат, Комірки;

вікно Формат комірок: розділ Межі, вибрати тип і колір ліній  таблиці;

головне меню: команда Файл, Зберегти як;

у вікні Збереження документа  вибрати потрібну папку, у поле ввести ім'я файлу Zarplata.xls. Зберегти документ.

 В результаті виконаної  роботи таблиця Excel матиме вигляд, показаний на малюнку 2.

Поточна інформація про роботу працівників цеху | 26.03.02

прізвище | оклад | дата | коеф

Артим | 100 | 01.06.00 | 1

Дрозд | 200 | 01.06.00 | 1,2

Лісенко | 300 | 01.03.00 | 0,9

Лісенко | 300 | 01.05.00 | 0,8

Дрозд | 200 | 01.04.00 | 1

Артим | 100 | 01.02.00   | 1,1

Артим | 100 | 01.05.01 | 0,8

 Малюнок 2. Результати  виконання задачі 4.2

 Задача 4.2а. Утворення  проміжної таблиці для задач  4.3, 4.4, 4.5

 

виокремити таблицю (комірки A4:D11). Головне меню: команда Виправлення, Копіювати;

встановити курсор на вільне місце листа, наприклад, у комірку A14;

головне меню: команда Виправлення, Вставити;

додати імена стовпців місяць, квартал, рік і зарплата у  комірки, відповідно, E14, F14, G14 і H14;

виокремити комірки C15:C21 з  датами, за допомогою команди головного  меню: Формат, Комірки викликати  вікно Формат комірок, де в розділі  Число, вибрати зі списку тип Дата, вибрати зразок дати YYYY.MM.DD,

встановити курсор у комірку E15. Головне меню: команда Вставка, Функцію;

вікно Майстра функцій – 1-й крок: вибір типу функції Дата, вибір функції Місяць;

вікно Майстра функцій – 2-й крок: вибір діапазону (аргумента) C15. Команда Оk;

перетягнути комірки E15 вниз до кінця таблиці;

занести в комірку F15 формулу  ЯКЩО(Е15<4;1;ЯКЩО(Е15<7;2;ЯКЩО(Е15<10;3;4)))  для одержання числового значення кварталу;

перетягнути комірку F15 вниз до кінця таблиці;

одержати в комірках G15:G21 числового значення року, подібно  до того як одержали значення місяця, застосувати  функцію Рік;

в комірку H15 занести формулу B15*D15 для начислення зарплати;

перетягнути комірку H15 вниз до кінця таблиці;

встановити тип ліній  і колір тла за допомогою команди  Формат комірок, подібно до того як це робилося в задачі 4.2. Готова проміжна таблиця показана на малюнку 2а.

прізвище | оклад | дата | коеф | місяць | квартал | рік | зарплата

Артим | 100 | 2000.06.01 | 1 | 6 | 2 | 2000 | 100

Дрозд | 200 | 2000.06.01 | 1,2 | 6 | 2 | 2000 | 240

Лісенко | 300 | 2000.03.01 | 0,9 | 3 | 1 | 2000 | 270

Лісенко | 300 | 2000.05.01 | 0,8 | 5 | 2 | 2000 | 240

Дрозд | 200 | 2000.04.01 | 1   | 4 | 2 | 2000 | 200

Артим | 100 | 2000.02.01 | 1,1 | 2 | 1 | 2000 | 110

Артим | 100 | 2001.05.01 | 0,8 | 5 | 2 | 2001 | 80

 Малюнок 2а. Проміжна  таблиця для задач 4.3, 4.4, 4.5

 Задача 4.3. Виготовлення  відомості на зарплату

виокремити таблицю (комірки A14:H21) для одержання копії. Головне  меню: команда Виправлення, Копіювати;

об'єднати комірки на ширину проміжної таблиці у вільному місці листа, наприклад, A24:H24, внести напис "Відомість на зарплату";

встановити курсор у комірку A25;

головне меню: команда Виправлення, Вставити;

посортувати скопійовану  таблицю за прізвищами. Для цього  виокремити комірки з даними A25:H25 і викликати вікно Сортування діапазону за допомогою команди  головного меню Дані, Сортування. У  цьому вікні вибрати параметри  сортування за прізвищами і за зростанням;

відібрати лише дані за 2-й  квартал 2000 року для цього виокремити комірки F24:G31 з даними про квартал  і рік. Головне меню: команда Дані, Фільтр, Автофільтр;

у комірці F24 вибрати з  кишеньки число 2. Перевірити дію фільтра, в таблиці повинні залишитися лише дані за 2-й квартал;

у комірці G24 вибрати з  кишеньки число 2000. Перевірити дію фільтра, в таблиці повинні залишитися лише дані з датою 2000-го року;

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

відредагувати виготовлені  майстром назви підсумків, об'єднати комірки в рядках підсумків, вирівняти  написи. Готова відомість на зарплату показана на малюнку 3.

Відомість на зарплату

прізвище | оклад | дата | коеф | місяць | квартал | рік | зарплата

Артим | 100 | 2000.06.01 | 1 | 6 | 2 | 2000 | 100

Артим всього | 100

Дрозд | 200 | 2000.06.01 |   1,2 | 6 | 2 | 2000 | 240

Дрозд | 200 | 2000.04.01 | 1 | 4 | 2 | 2000 | 200

Дрозд всього | 440

Лісенко | 300 | 2000.05.01 | 0,8 | 5 | 2 | 2000 | 240

Лісенко всього | 240

Загальний підсумок | 780

Малюнок 3. Відомість на зарплату

 Задача 4.4. Виготовлення  зведеної відомості (розподіл  зарплат по кварталах)

виокремити проміжну таблицю (комірки A14:H21), команда головного  меню Виправлення, Копіювати;

відкрити Лист2, встановити курсор у його вільне місце, наприклад, у комірку А1, команда головного  меню Виправлення, Вставити;

відфільтрувати дані за 2000 рік, для цього виокремити стовпець G1:G8 (рік), виконати команду головного  меню Дані, Фільтр, Автофільтр і вибрати  з кишеньки, розташованої поряд з  назвою стовпця число 2000;

встановити курсор вільне місце таблиці, наприклад, у комірку  А10, викликати майстра зведеної таблиці  командою головного меню Дані, Зведена  таблиця;

виконати кроки майстра:

крок 1, вибрати дані – на основі даних, що знаходяться в  базі даних MS Excel;

крок 2, вказати діапазон A1:H7, тобто – всю таблицю;

крок 3, перетягнути кнопки полів у потрібні місця діаграми: рядок – прізвище, стовпець – квартал, дані – зарплата;

крок 4, погодитися з пропозицією  майстра на розміщення таблиці в  комірку A10;

встановити тип ліній  таблиці, відредагувати написи, запам'ятати  файл. Результат виконання задачі 4.4 показано на малюнку 4.

Розподіл зарплат по кварталах | квартал |

Сума

прізвище | 1 | 2

Артим | 110 | 100 | 210

Дрозд | 440 | 440

Лісенко | 270 | 240 | 510

Всього | 380 | 780 | 1160

 Малюнок 4. Зведена таблиця,  розподіл зарплат по кварталах

 Задача 4.5а. Підготовка  даних для діаграми

для побудови діаграми відповідно до поставленої задачі необхідно  мати два стовпці: числові значення місяців 2000 року та значення зарплат, підсумовані  за кожний місяць. Використовуючи проміжну таблицю, скопіюємо її і помістимо  на вільне місце,   наприклад, у  комірку А1 якогось листа, наприклад, Листа3;

поміняємо місцями стовпці  місяць і прізвище, оскільки операція консолідації виконується за крайнім  лівим полем таблиці. Для цього  скористуємося вільним допоміжним стовпцем, наприклад, I1:I8. Виокремимо стовпець Е1:Е8 з числовими значеннями місяців, виконаємо команду головного  меню Виправлення, Вирізати. Перенесемо дані у допоміжний стовпець. Виокремимо стовпець з прізвищами і перенесемо його у звільнені комірки Е1:Е8, тобто туди, де були значення місяців. Перенесемо значення місяців з допоміжного  стовпця в перший стовпець;

посортуємо дані за значеннями місяців. Для цього виокремимо ту частину таблиці, де маємо дані (назви  стовпчиків не повинні приймати участі в сортуванні, інакше вони можуть опинитися  не зверху, а всередині таблиці), це – комірки A2:H8. Виконаємо команду  головного меню Дані, Сортування. У  вікні Сортування діапазону виберемо параметри: Сортувати по місяцях, За зростанням;

відфільтруємо дані за 2000 рік, подібно до того як це робилося в  задачі 3, п. 4;

скопіюємо відфільтровану таблицю  і помістимо її на вільне місце, наприклад, в комірку А10. Копіювання відфільтрованої  таблиці обов'язкове, інакше при  консолідації можуть виявитися не врахованими  результаті роботи фільтра;

встановимо курсор на вільне місце, наприклад, у комірку A18, де надіємося  одержати таблицю з результатами консолідації;

виконаємо команду головного  меню Дані, Консолідація. У вікні  Консолідація виберемо функцію Сума, Посилання на адреси $A$11:$H$16 (діапазон задамо, виокремливши комірки), Список діапазонів – Додати, Використати  в якості імен – Значення лівого стовпця. Виконаємо команду Ok.

 Результати показані  на малюнку 5. Аналізуючи малюнок  5, знаходимо, що дані всіх стовпців  таблиці, крім першого і останнього, виявилися спотвореними. Для одержання  потрібного нам графіка це  не має значення, оскільки ці  стовпці нам не потрібні, тому  залишимо одержану таблицю без  змін. Зрештою, можна було б  знищити середні стовпці, залишивши  лише числові значення місяців  і підсумовані – зарплат.

Зауважимо, що в даному   прикладі використаний не найкращий  спосіб підготовки даних для діаграми. Студентам надається пропозиція застосувати більш досконалі  засоби.

2 | 100 | 2000.02.01 | 1,1 | 1 | 2000 | 110

3 | 300 | 2000.03.01 | 0,9 | 1 | 2000 | 270

4 | 200 | 2000.04.01 | 1 | 2 | 2000 | 200

5 | 300 | 2000.05.01 | 0,8 | 2 | 2000 | 240

6 | 300 | 2100.11.02 | 2,2 | 4 | 4000 | 340

 Малюнок 5. Результати  підготовки даних для діаграми

 Задача 4.5б. Виготовлення  діаграми

встановимо курсор на вільне місце Листа3, наприклад, у комірку  А20, де надіємося одержати діаграму;

виконаємо команду головного  меню Вставка, Діаграма. Виконаємо кроки  майстра:

крок 1 = Тип діаграми. Встановимо:

Розділ – Стандартні, Тип – Гістограма, Вид – Звичайна діаграма;

крок 2 = Джерело даних для  діаграми. Встановимо:

Розділ Діапазон даних, Діапазон даних – виокремимо A18:H22, Ряди – в стовпцях;

Розділ Ряд, Параметр ряд – вилучимо лишні ряди, залишимо лише Ряд 5, Значення – погодимося з пропозицією майстра на діапазон Лист3!$H$18: $H$22, Підписи осі X – виокремимо діапазон Лист3!$A$18:$A$22;

крок 3 = Параметри діаграми. Встановимо:

Розділ Заголовки, Назва  діаграми – Виплати зарплат, Ось  Х – місяці, Ось Y – гривень;

Розділ Осі, Ось X – Автоматично, Ось Y – Значення;

Розділ Лінії сітки, Ось X – Основні, Ось Y – Основні;

Розділ Легенда – Не додавати легенду;

Розділ Підписи даних, Підписи значень – Значення, Ключ легенди – відсутній;

Розділ Таблиця даних – відсутня;

крок 4 = Розміщення діаграми – на даному листі. Команда Готово;

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

Задача 4.6а. Виготовлення кнопки для запуску макроса, програмування  макроса

на тлі Листа1 виконаємо  команду   головного меню Вигляд, Панелі інструментів, Елементи управління;

вікно Панелі елементів, вибираємо  елемент Кнопка;

переводимо курсор на площину  Листа1, він приймає вигляд хрестика. Фіксуємо курсор на вільному місці  листа, наприклад, у комірку Е3. Елемент  Кнопка готовий;

вікно Елементи управління: кнопка Конструктор;