Бази даних в Excel, Access з викликами на VBA
Зміст
Завдання
Рішення
Висновок
Список використаної літератури
Варіант №2
Завдання
Створити базу даних „Звіт про співробітників фірми”.
Кількість записів – не менш 20.
Для розв’язання задачі необхідно:
Сформувати вихідну таблицю та роздрукувати її.
Визначити суму окладів по відділам фірми.
Знайти відділ з максимальною кількістю співробітників.
Відсортувати таблицю за ПІБ.
Знайти ПІБ співробітників з мінімальним окладом.
Визначити загальну суму окладів по фірмі.
Рішення
Таблиця „Звіт про співробітників фірми” у режимі даних середовища MS Excel наведена на рис.1.
Рис.1.
Додавання проміжних і остаточних підсумків виконується після сортування вихідної бази по обраному полю. Виконаємо сортування вихідної бази по полю "Назва відділу" і додамо проміжні і загальний підсумки. Для додавання підсумків відкриємо вікно діалогу "Проміжні підсумки". Для цього виконуємо команди меню Данные/Итоги. У вікні діалогу задамо поле, у якому буде відслідковуватися зміна значень (наприклад поле " Назва відділу "). Потім вкажемо в рядку "Операция" той вид операції, що потрібний для виконання завдання (Сумма, Кількість і т.д.). У списку вікна діалогу "Додати підсумки по" укажемо, яких ще полях бази даних необхідно виконати аналогічні дії. Вікно діалогу з установленими параметрами наведено на Рис.2.
Рис.2.
Результат встановлення проміжних підсумків зображений на рис.3.
Рис.3.
Додамо ще один рівень підсумків за кількістю співробітників відділу, та розрахуємо максимальну кількість чоловік у відділі (рис.4)
Рис.4.
Відсортуємо таблицю за ПІБ (меню Данные/Сортировка...) Рис.5.
Для звичайного сортування бази даних по одному полю ( по зростанню чи по убуванню) необхідно скористатися пунктом меню Данные Сортировка чи піктограмами на графічному меню.
Сортування
по зростанню Сортування по убуванню
При цьому курсор повинний бути встановлений у поле, що будемо сортувати. Для сортування по зростанню по полю "ПІБ" установимо курсор на комірку у цьому полі і виберемо напрямок сортування "по зростанню".
Рис.5.
Результат сортування зображений на рис.6.
Рис.6.
Знайдемо ПІБ співробітників з мінімальним окладом (рис.7).
Рис.7.
До комірки Е32 занесено формулу =БИЗВЛЕЧЬ(A2:G26;E2;G31:G32). Критерій зображений у діапазоні G31:G32.
Експортуємо таблицю з середовища MS Excel до середовища MS Access (рис.8)
Рис.8.
Таблиця „Звіт” у режимі Конструктора середовища MS Access зображена на рис.9.
Рис.9.
Таблиця „Звіт” у режимі Таблиці середовища MS Access зображена на рис.10.
Рис.10.
Запит на вибірку „Сума окладів по відділам” у режимі Конструктора наведений на рисунку 11.
Рис.11.
Запит на вибірку „Сума окладів по відділам” у режимі Таблиці наведений на рисунку 12.
Рис.12.
Запит на вибірку „Сума окладів по фірмі” у режимі Конструктора наведений на рисунку 13.
Рис.13.
Запит на вибірку „Сума окладів по фірмі” у режимі Таблиці наведений на рисунку 14.
Рис.14.
Запит на вибірку „Максимальна кількість у відділі” у режимі Конструктора наведений на рисунку 15.
Рис.15.
Запит на вибірку „Максимальна кількість у відділі” у режимі Таблиці наведений на рисунку 16.
Рис.16.
Запит на вибірку „Сортування” у режимі Конструктора наведений на рисунку 17.
Рис.17.
Запит на вибірку „Сортування” у режимі Таблиці наведений на рисунку 18.
Рис.18
Запит на вибірку „Співробітник з мінімальним окладом” у режимі Конструктора наведений на рисунку 19.
Рис.19.
Запит на вибірку „Співробітник з мінімальним окладом” у режимі Таблиці наведений на рисунку 20.
Рис.20.
Алгоритм програми на VBA, що реалізований на другому аркушу книги MS Excel “ZVBA” зображений на наступному рисунку 21.
0100090000037800000002001c00000000000400000003010800050000000b0200000000050000000c02bf12390f040000002e0118001c000000fb021000070000000000bc02000000cc0102022253797374656d0012390f0000e4c7110072edc630509679020c020000390f0000040000002d01000004000000020101001c000000fb029cff0000000000009001000000cc0440001254696d6573204e657720526f6d616e0000000000000000000000000000000000040000002d010100050000000902000000020d000000320a1a13000001000400000000003c0fc01220002d00040000002d010000030000000000
Рис.21.
Інтерфейс програми на VBA зображений на наступному рисунку 22.
Рис.22.
Пароль на вхід до робочої книги 12345.
Програмний код на VBA наведений у лістингу 1
Лістинг 1
Private sub> CommandButton1_Click()
Worksheets("ZVBA").Cells(9, 8).Formula = "=Sum(G2:G9)"
Worksheets("ZVBA").Cells(16, 8).Formula = "=Sum(G10:G16)"
Worksheets("ZVBA").Cells(21, 8).Formula = "=Sum(G17:G21)"
Worksheets("ZVBA").Cells(22, 8).Formula = "=Sum(H3:H31)"
End sub>
Private sub> CommandButton2_Click()
Worksheets("ZVBA").Range("A2:G21").Sort _
Key1:=Worksheets("ZVBA").Range("E1")
End sub>
Private sub> CommandButton3_Click()
Worksheets("ZVBA").Range("A2:G21").Sort _
Key1:=Worksheets("ZVBA").Range("A1")
End sub>
Private sub> CommandButton4_Click()
Set myRange = Worksheets("ZVBA").Range("C2:C9")
a = Application.WorksheetFunction.Count(myRange)
MsgBox a
End sub>
Private sub> CommandButton5_Click()
Worksheets("ZVBA").Range("A2:G21").Sort _
Key1:=Worksheets("ZVBA").Range("G1")
a = Cells(2, 5)
MsgBox a
End sub>
Private sub> CommandButton6_Click()
ThisWorkbook.Saved = True
ThisWorkbook.Close
End sub>
Висновок
За допомогою введення формул з математичними, статистичними функціями та функціями для роботи з базами даних у відповідні комірки, та виконання команд меню у середовищі MS Excel були виконані пункти завдання. За допомогою запитів на вибірку у середовищі MS Access також були виконані пункти завдання. За допомогою кодів та макросів мови VBA для елементів управління – розроблених кнопок для меню також були виконані пункти завдання.
Список використаної літератури
Харитонова И.А., Михеева В.Д. Microsoft Access 2000: Разработка приложений. – СПб.: БХВ, 2000. – 832 с.
Робинсон С. Microsoft Access 2000: - СПб.: Издательский дом Питер, 2002. – 512 с.
Вейскас Дж. Эффективная работа с Microsoft Access 2000: СПб.: Питер-Пресс, 2002. – 1040 с.
Дженнингс Р. Использование Microsoft Access 2000: Специальное издание. - Издательский дом «Вильямс», 2002. – 1152 с.
Справка Microsoft Access 2002.//Корпорация Microsoft (Microsoft Corporation), - 2001.
Справка Microsoft Excel 2002.//Корпорация Microsoft (Microsoft Corporation), - 2001.
Руденко В.Д., Макарчук О.М., Патланжоглу М.О. Практичний курс інформатики. /За ред. Мадзігона В.М.-К.: Фенікс, 1997.-307 с.
Ситник В.Ф. та ін. Основи інформаційних систем – К.:КНЕУ, 2001
Береза А.М. Основи створення інформаційних систем – К.:КНЕУ, 2001
Информационные системы в экономике. Под редакцией профессора В.В.Дика – М.: Финансы и статистика, 1996
Смирнова Г.Н, Сорокин А.А., Тельнов Ю.Ф. Проектирование экономических информационных систем – М.: Финансы и статистика, 2003
Overview
Лист1ZVBA
Sheet 1: Лист1
| Звіт про співробітників фірми | ||||||
| № з/п | Назва відділу | Код співробітника | Адреса | ПІБ | Посада | Оклад |
| 1 | Розробки | 1001 | Дніпропетровськ, вул.Південна, 18 | Борин Т.Л. | Інженер | 1100,00 |
| 2 | Розробки | 1002 | Дніпропетровськ, вул.Західна 12 | Бобов Н.А. | Інженер | 1150,00 |
| 3 | Розробки | 1003 | Дніпропетровськ, вул.Олева, 32-2 | Волин А.І. | Інженер | 1200,00 |
| 4 | Розробки | 1004 | Дніпропетровськ, вул.Алова, 24-5 | Додин Н.О. | Інженер | 1250,00 |
| 5 | Розробки | 1005 | Дніпропетровськ, вул.Північна, 34-6 | Гопов П.Л. | Інженер | 1300,00 |
| 6 | Розробки | 1006 | Дніпропетровськ, вул.Східна, 7 | Ганов І.П. | Економіст | 1350,00 |
| 7 | Розробки | 1007 | Дніпропетровськ, вул.Наукова, 56 | Вунин І.Д. | Економіст | 1400,00 |
| 8 | Розробки | 1008 | Дніпропетровськ, вул.Лутова, 45 | Бубин Р.І. | Економіст | 1450,00 |
| Розробки Количество | 8 | |||||
| Розробки Итог | 10200,00 | |||||
| 9 | Збірки | 2001 | Дніпропетровськ, вул.Січнева, 58 | Бобин Н.І. | Слюсар | 1500,00 |
| 10 | Збірки | 2002 | Дніпропетровськ, вул.Лютого, 79 | Габов Н.І. | Слюсар | 1550,00 |
| 11 | Збірки | 2003 | Дніпропетровськ, вул.Травнева, 98 | Гунин Г.Д. | Слюсар | 1600,00 |
| 12 | Збірки | 2004 | Дніпропетровськ, вул.Войкова, 67 | Апин О.Б. | Слюсар | 1650,00 |
| 13 | Збірки | 2005 | Дніпропетровськ, вул.Морська, 56-4 | Бойко Р.Н. | Слюсар | 1700,00 |
| 14 | Збірки | 2006 | Дніпропетровськ, вул.Жовтнева, 86-3 | Валин І.О. | Слюсар | 1750,00 |
| 15 | Збірки | 2007 | Дніпропетровськ, вул.Рижська, 65-5 | Генин О.І. | Завідувач | 1800,00 |
| Збірки Количество | 7 | |||||
| Збірки Итог | 11550,00 | |||||
| 16 | ВТК | 3001 | Дніпропетровськ, вул.Фучика, 84-9 | Гунов А.Б. | Контролер | 1850,00 |
| 17 | ВТК | 3002 | Дніпропетровськ, вул.Цеткин, 67-8 | Дадин Р.І. | Контролер | 1900,00 |
| 18 | ВТК | 3003 | Дніпропетровськ, вул.Зотова, 64-32 | Бабин І.І. | Контролер | 1950,00 |
| 19 | ВТК | 3004 | Дніпропетровськ, вул.Додина, 32-76 | Велин Т.І. | Контролер | 2000,00 |
| 20 | ВТК | 3005 | Дніпропетровськ, вул.Пуго, 6-64 | Гусев Г.Т. | Завідувач | 2050,00 |
| ВТК Количество | 5 | |||||
| ВТК Итог | Максимальний відділ | 8 | 9750,00 | |||
| Общее количество | 20 | |||||
| Общий итог | 31500,00 | |||||
| Оклад | ||||||
| Співробітник з мін.окладом: | Борин Т.Л. | 1100,00 |
Sheet 2: ZVBA
| № з/п | Назва відділу | Код співробітника | Адреса | ПІБ | Посада | Оклад | ||||||
| 1 | Розробки | 1001 | Дніпропетровськ, вул.Південна, 18 | Борин Т.Л. | Інженер | 1100,00 | Меню | |||||
| 2 | Розробки | 1002 | Дніпропетровськ, вул.Західна 12 | Бобов Н.А. | Інженер | 1150,00 | ||||||
| 3 | Розробки | 1003 | Дніпропетровськ, вул.Олева, 32-2 | Волин А.І. | Інженер | 1200,00 | 
|
|||||
| 4 | Розробки | 1004 | Дніпропетровськ, вул.Алова, 24-5 | Додин Н.О. | Інженер | 1250,00 | ||||||
| 5 | Розробки | 1005 | Дніпропетровськ, вул.Північна, 34-6 | Гопов П.Л. | Інженер | 1300,00 | ||||||
| 6 | Розробки | 1006 | Дніпропетровськ, вул.Східна, 7 | Ганов І.П. | Економіст | 1350,00 | ||||||
| 7 | Розробки | 1007 | Дніпропетровськ, вул.Наукова, 56 | Вунин І.Д. | Економіст | 1400,00 | 
|
|||||
| 8 | Розробки | 1008 | Дніпропетровськ, вул.Лутова, 45 | Бубин Р.І. | Економіст | 1450,00 | ||||||
| 9 | Збірки | 2001 | Дніпропетровськ, вул.Січнева, 58 | Бобин Н.І. | Слюсар | 1500,00 | ||||||
| 10 | Збірки | 2002 | Дніпропетровськ, вул.Лютого, 79 | Габов Н.І. | Слюсар | 1550,00 | ||||||
| 11 | Збірки | 2003 | Дніпропетровськ, вул.Травнева, 98 | Гунин Г.Д. | Слюсар | 1600,00 | 
|
|||||
| 12 | Збірки | 2004 | Дніпропетровськ, вул.Войкова, 67 | Апин О.Б. | Слюсар | 1650,00 | ||||||
| 13 | Збірки | 2005 | Дніпропетровськ, вул.Морська, 56-4 | Бойко Р.Н. | Слюсар | 1700,00 | ||||||
| 14 | Збірки | 2006 | Дніпропетровськ, вул.Жовтнева, 86-3 | Валин І.О. | Слюсар | 1750,00 | 
|
|||||
| 15 | Збірки | 2007 | Дніпропетровськ, вул.Рижська, 65-5 | Генин О.І. | Завідувач | 1800,00 | ||||||
| 16 | ВТК | 3001 | Дніпропетровськ, вул.Фучика, 84-9 | Гунов А.Б. | Контролер | 1850,00 | ||||||
| 17 | ВТК | 3002 | Дніпропетровськ, вул.Цеткин, 67-8 | Дадин Р.І. | Контролер | 1900,00 | ||||||
| 18 | ВТК | 3003 | Дніпропетровськ, вул.Зотова, 64-32 | Бабин І.І. | Контролер | 1950,00 | 
|
|||||
| 19 | ВТК | 3004 | Дніпропетровськ, вул.Додина, 32-76 | Велин Т.І. | Контролер | 2000,00 | ||||||
| 20 | ВТК | 3005 | Дніпропетровськ, вул.Пуго, 6-64 | Гусев Г.Т. | Завідувач | 2050,00 | ||||||
|
||||||||||||