Бази даних в Excel, Access з викликами на VBA

Створити базу даних „Звіт про співробітників фірми”.

Кількість записів - не менш 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.

2

Рис.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(H2:H21)"

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 для елементів управління - розроблених кнопок для меню також були виконані пункти завдання.

Список використаної літератури

4. Дженнингс Р. Использование Microsoft Access 2000: Специальное издание. - Издательский дом «Вильямс», 2002. - 1152 с.