Практична робота «Створення, редагування та впорядкування бази даних засобами Excel. Фільтрація, тобто створення запитів на вибірку даних, сортування даних у таблицях. Форми»

Практична робота «Створення, редагування та впорядкування бази даних засобами Excel. Фільтрація, тобто створення запитів на вибірку даних, сортування даних у таблицях. Форми»

Мета роботи: освоїти методи та засоби створення та редагування бази даних засобами Excel, а також сортування, фільтрування та пошуку записів в базі даних.

Завдання:

1.      Завантажити табличний редактор. Створити базу даних (БД), яка містить інформацію про працівників підприємства. Додати на панель швидкого доступу Форму (якщо вона відсутня).
2.      Файл документу зберегти під назвою Фільтр_вибірка_БД_Прізвище. Зберегти файл у папці класу або у папці за вказівкою вчителя.
3.      Ввести 10 записів про склад працівників підприємства які вказані за прикладом та додати за допомогою форми ще п’ять нових працівників (зробити скриншот введення інформації у базу даних да допомогою Форми).

Для збереження зображення Форми з даними у неї або з зображення вікна автофільтру з вибором критерія використати комбінацію клавіш Alt+PrintScrin (зображення форми потрапить у буфер обміну, потім закрити форму або вікно автофільтра та вибрати команду Вставить)

4.      Додати до таблиці ще один стовпчик за назвою «Рівень зарплати». Вказати рівень зарплати в залежності від окладу (використати формулу ЕСЛИ чи IF):

• якщо оклад менше чи дорівнює 3000 грн, тоді рівень низький;
• якщо оклад знаходиться в межах від 3000 грн до 5000 грн, тоді середній;
• якщо оклад більше чи дорівнює 5000 грн, тоді високий.

5.      Використовуючи фільтр, виконати пошук інформації в БД за такими критеріями:

• знайти всіх працівників, які мають оклад більше 5000 грн, вставити таблицю з вибіркою на окремий Аркуш2 (для того, щоб показати критерії фільтру - скопіювати зображення вікна автофільтру та вставити нижче або біля таблиці з вибіркою);
• знайти всіх працівників, які працюють в відділі Збуту вставити таблицю з вибіркою на окремий Аркуш2;
• знайти всіх працівників, які мають оклад більше 4000 грн, вставити таблицю з вибіркою на окремий Аркуш2;
• знайти середній оклад та суму окладу для вибраних працівників з окладом більше 4000 грв;
• виконати сортування записів в БД за спаданням по полю Оклад та зростанням по полю Відділ (для того, щоб показати критерії фільтру - скопіювати зображення вікна автофільтру та вставити біля таблиці).

7.      Результати роботи зберегти та продемонструвати для перевірки.

Послідовність виконання роботи:

Створити та зберегти базу даних (БД), яка містить інформацію про працівників підприємства.

Для створення бази даних у першому рядку робочого листа Excel ввести заголовний рядок із однорядковими та короткими іменами полів: «Прізвище», «Ім'я», «По батькові», «Відділ», «Посада», «Оклад». Далі потрібно замінити системне ім'я бази «Книга1» на призначене для користувача, наприклад на «БД Працівники», клацнувши мишею на кнопці Сохранить как. Також зберегти отриману базу ПІД назвою «БД1», вибравши команду Формулы/Присвоить имя (якщо існує команда).

Ввести 10 записів про склад працівників підприємства, додати форму

Після натиснення мишею на будь-якій із комірок таблиці командою Данные — Форма на екран викликається відповідне діалогове вікно, де відображається пусті поля для введення даних або список вже уведених даних (рис. 1).

Значення полів запису вводять до відповідних комірок діалогового вікна у тій послідовності, в якій вони сформовані в таблиці. Перехід між комірками виконується за допомогою клавіші <Таб> або клавіш керування курсором. Запис передають у кінець бази даних командою Добавить. Після цього вводять наступний запис і т. п. до кінця бази.

Рис. 1. Вікно команди Форма (режим уведення записів)

Командні кнопки вікна Форма дають змогу вилучити поточний запис (Удалить), скасувати в ньому будь-яку зміну (Вернуть), повернутися до попереднього запису (Назад) і перейти до наступного (Далее), виконати пошук записів за кількома критеріями (Критерии), а також відредагувати їх. Створення бази даних завершується командою Закрыть.

Для збереження зображення форми з у буфер обміну можна використати комбінацію клавіш Alt+PrintScrin.

Базу даних можна створити також, безпосередньо вводячи імена і значення полів записів у комірки робочого аркуша.

Додайте до таблиці ще один стовпчик за назвою «Рівень зарплати». Вказати рівень зарплати в залежності від окладу. Зразок:

Використовуючи фільтр, виконати пошук інформації в БД за критерієм:

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

Потрібну інформацію в базі зручно шукати за допомогою фільтрів. Виконання команди Данные – Фільтр – Автофильтр перетворює рядок назв полів у набір списків, що містять усі наявні значення кожного з полів бази даних. Клацання мишею на кнопці вибраного поля спричиняє появу на екрані спадного списку автофільтра, в якому містяться різні значення критеріїв вибраного поля і три спеціальні команди-опції: «Все», «Первые 10...» та «Условие ...» (рис. 2).

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

Рис. 2. База Працівники в режимі автофільтру

Початковий вигляд бази відновлюють за допомогою параметра «Все» або командою Данные—Фильтр — Отобразить все.

Вибравши у списку Условие…, можна задати необхідний користувацький критерій відбору даних із таблиці (рис. 3).

Рис. 3. Задання користувацького автофільтру

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

Виконати сортування записів в БД за зростанням або спаданню.

Командою Данные – Сортировка викликають відповідне діалогове вікно (рис. 8), де зазначають поля бази даних, за якими послідовно (від верхнього до нижнього) виконуватиметься сортування за зростанням чи спаданням. Значення опції Идентифицировать поля по визначає спосіб задавання полів: за їхніми назвами із рядка заголовку чи за ідентифікаторами стовпців. Кнопку Параметры використовують для задання додаткових параметрів сортування.

Для виконання сортування записів в БД по полях, наприклад, Прізвище, Ім’я, По батькові за зростанням виконуємо команду Данные – Сортировка та задаємо параметри.

Результати роботи представити викладачу для перевірки.