Лабораторна робота № 12 Бази даних в МS Ехсеl
Теоретичні відомості
Термін база даних можна застосувати до будь-якої сукупності зв'язаної інформації, об'єднаної за певною ознакою. Основним призначенням баз даних є швидкий пошук інформації. При цьому стовпці називаються полями, а рядки – записами. Стовпцям присвоюються імена, які будуть використовуватися як імена полів записів. Інформація в базах даних має постійну структуру. Кожен рядок можна розглядати як одиничний запис. Побудову списку слід почати з проектування його структури.
Існують обмеження, що накладаються на структуру бази даних:
- перший ряд бази даних повинен містити неповторювані імена полів і розташовуватися в одному рядку;
- у списку не повинно бути порожніх рядків і стовпців;
- для імен полів слід використовувати шрифт, формат, рамку, відмінні від тих, які використовуються для даних у записах;
- таблицю слід відокремити від інших даних робочого листа порожнім стовпцем і порожній рядком;
- інформація по полях повинна бути однорідною, тобто тільки цифри або тільки текст.
Робота з будь базою даних полягає в пошуку інформації за певними критеріями, перегрупуванні та обробці інформації.
Розглянемо на прикладі створення бази даних.
Створення бази даних (списку)
Приклад 11.1.
1. Спроектувати структуру списку згідно наступного завдання.
Визначити межу міцності деревини при сколюванні вздовж волокон з вологістю в момент випробування W:
де 
- максимальне зусилля, Н;
b – товщина зразка, мм;
l – довжина сколювання, мм.
Отримане значення перераховують на стандартну вологість 12% за формулою
де 
– поправочний коефіцієнт на вологість, рівний 0,03 для всіх порід на 1% вологості;
– вологість зразка на час сколювання, % (див. лаб.роб.9).
2. Щоб швидко поміняти місцями назви рядків та стовпців та відповідні дані, отримані в лабораторній роботі №9, скопіюйте потрібну таблицю, клацніть правою кнопкою миші, виберіть в контектному меню пункт Специальная вставка і у діалоговому вікні встановіть галочку Транспортировать (рис.12.1).
Рис.12.1 – Перевертання таблиці
2. На вкладці Вставка виконати команду Таблица. У вікні Создание таблицы вказати діапазон майбутнього списку і встановити прапорець Таблица с заголовками (рис. 12.2).
Рис.12.2 – Створення таблиці
3. Додайте до таблиці поле «Дата дослідження» (рис.12.3).
Рис.12.3 – Таблиця з заголовками
4. При введенні даних бажано контролювати тип введеної інформації і зводити до мінімуму помилки введення. У Ехсеl виконання подібних умов перевіряється за допомогою засобу, який називається перевіркою даних. Для цього необхідно виконати наступні дії.
- виділити комірки стовпчика, для якого встановлюється перевірка введення даних;
- на вкладці Данные виконати команду Проверка данных;
- на вкладці Параметры в області Условие проверки вибрати Тип данных: Любое значение (використовується для скасування перевірки вводу), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для якого можна задати власну формулу, наприклад, "ч"або"ж"). При виборі значення внизу вікна з'являються додаткові поля для вводу умов або обмежень (наприклад, мінімального і максимального допустимих значень);
- на вкладці Сообщение для ввода можна встановити прапорець Отображать подсказку, если ячейка является текущей і ввести повідомлення, щоб воно з'являлося на екрані при введенні даних, які не відповідають умові перевірки.
Встановимо перевірку даних при їх введенні в стовпець «Дата дослідження», для цього виділимо його і виконаємо команди Данные ®Проверка данных (рис. 12.4).
Рис.12.4 – Налаштування перевірки даних при введенні
5. Нижче заголовків внести в список записи даних. Для зміни розміру таблиці необхідно натиснути на маркер і протягнути його вправо і вниз для збільшення таблиці і відповідно вліво і вгору – для її зменшення (рис. 12.5).
Рис.12.5 – Створена база даних
Для перетворення бази даних в просту таблицю необхідно встановити курсор усередині списку і на вкладці Конструктор виконати команду Преобразовать в диапазон.
Сортування даних у списку
Команда Сортировка дозволяє переставити записи в іншому порядку на підставі значень одного або декількох стовпців. Записи сортуються за зростанням/спаданням або в обраному користувачем порядку (наприклад, по днях тижня).
Порядок сортування у списку:
1. Встановити курсор в комірку списку.
2. Виконати команду Сортировка на вкладці Данные в групі Сортировка и фильтр (рис. 12.6).
Рис.12.6 – Командт сортування та фільтрування даних
З. У діалоговому вікні Сортировка вибрати поле, за яким буде відбуватися сортування, тип сортування (за значенням, колір комірки, колір шрифту, значок комірки) і порядок (за зростанням, спаданням, що настроюється) (рис. 12.7).
Рис.12.7 – Діалогове вікно сортування даних
Автофільтр
Відфільтрувати список – значить показати тільки ті записи, які задовольняють заданому критерію.
Щоб встановити або прибрати автофільтр, слід на вкладці Данные в групі Сортировка и фильтр виконати команду Фильтр (див. рис. 12.6). Після цього натиснути кнопку зі стрілкою біля назви якого-небудь поля, щоб розкрити список його елементів і вибрати відображаються значення або задати умову відбору (рис. 12.8). На екрані з'являться тільки ті записи, які відповідають заданій умові. У разі необхідності можна продовжити фільтрацію, натискаючи кнопки зі стрілками на інших полях.
Рис.12.8 – Фільтрування даних
Для фільтрації записів за більш складних умов відбору, ніж автофільтр, застосовується команда Дополнительно, яка розташована на вкладці Данные. Команда Дополнительно аналогічна команді "Розширений фільтр" в Excel 2003.
Приклад 12.2.
Вибрати хвойні породи деревини, у яких вологість більша за 9%, і до яких прикладене зусилля, більше за 4300 Н.
1. В діапазон А16:І16 скопіюємо назви полів записів (діапазон А3:І3).
2. В комірки В17:В19 скопіюємо назви хвойних порід деревини, в комірки D17:D19 введемо умову для відбору вологості (>9), а в комірки F17:F19 – умову для відбору зусиль (>4300).
3. На вкладці Данные вибираємо команду Дополнительно, після чого з’явиться вікно Расширенный фільтр (рис.12.9), в якому вказуємо вихідний діапазон (нашу базу даних), діапазон умов (комірки А16:І19) і якщо хочемо скопіювати результат в інше місце, то потрібно вказати ще діапазон для розміщення результату (рис.12.10).
Рис.12.9 – Розширений фільтр
Рис.12.10 – Приклад застосування розширеного фільтру
Зведені таблиці
Звіт зведеної таблиці являє собою інтерактивний метод швидкого підсумовування великих обсягів даних. Звіт зведеної таблиці використовується у випадках, коли потрібно проаналізувати пов'язані підсумки, особливо для порівняння декількох фактів по кожному числу зі списку узагальнюючих чисел.
Приклад 12.3.
Створити зведену таблицю, в якій по двом датам випробувань, 01.05.2014 і 06.08.2014 будуть підсумовуватися товщини зразків і буде знайдено середнє значення по максимальному зусиллю.
1. Щоб створити зведену таблицю, на вкладці Вставка в групі Таблицы необхідно вибрати пункт Сводная таблица. На екран буде виведено діалогове вікно Создание сводной таблицы (рис. 12.11), в якому слід вказати діапазони вихідної і зведеної таблиць.
Рис.12.11 – Створення зведеної таблиці
2. Перемістити мишкою на макет зазначені поля (рис. 12.12).
Рис.12.12 – Макет зведеної таблиці
3. У полі дата випробувань встановити фільтр по двох зазначених датах (рис. 12.13).
Рис.12.13 – Встановлення фільтру
4. Внизу у вікні Список полей сводной таблицы, клацнувши по полю Товщина зразка:
- Виконати в меню команду Параметры поля (рис. 12.14);
Рис.12.14 – Виклик команди Параметры поля
- У вікні Параметры поля вибрати функцію Сумма;
- У списку поля Товщина зразка виконати команду Переместить в значения.
Для поля максимальне зусилля вибрати функцію Среднее і виконати команду Переместить в значения.
Отримаємо макет зведеної таблиці із заданими параметрами полів (рис. 12.15).
Рис.12.15 – Макет зведеної таблиці із заданими параметрами полів
Звіт отриманої зведеної таблиці представлений на рис. 12.16.
Рис.12.16 – Зведена таблиця
Хід роботи
1. Сформувати базу даних «Теплопровідність і структурні характеристики будівельних матеріалів», представлену в табл. 12.1 використовуючи обчислення пористості за формулою
.
Таблиця 12.1
Теплопровідність і структурні характеристики будівельних матеріалів
| Назва матеріалу | Теплопровідність l | Густина, кг/м3 | Пористість П, % | |
| середня r0 | істинна r | |||
| Сталь | ||||
| Мідь | ||||
| Алюміній | ||||
| Граніт | 3,1 | |||
| Важкий бетон | 1,4 | |||
| Керамзитобетон | 0,42 | |||
| Пористий бетон | 0,325 | |||
| Цегла силікатна | 1,08 | |||
| Цегла керамічна | 0,85 | |||
| Піноскло | 0,095 |
Кожне завдання виконувати на окремому листі, попередньо скопіювавши туди базу даних.
Завдання 1.
1. Набрати заголовки таблиці.
2. Створити з них список.
З. До стовпцю Середня густина застосувати перевірку даних: дані, що вводяться, повинні бути більші за 250 і менші за 9000.
4. Заповнити список даними.
5. Комірки заголовків (поля списку) заповнити кольором.
Завдання 2.
1. Відсортувати дані списку по назві матеріалу за алфавітом.
2. Відсортувати дані списку за середньою густиною за спаданням.
Завдання 3.
Використовуючи команду Автофільтр, вибрати наступні дані зі списку:
- теплопровідність більше 10;
- пористість вище середнього значення пористості,
- середню густину більше 1000 і менше 2000.
Завдання 4.
Створити зведену таблицю, в якій по істинній густині r£2650 будуть підсумовуватися теплопровідності і буде знайдено середнє значення по пористості.
2. Створіть книгу під ім’ям «Будівельна фірма».
2.1. Створіть на листі 1 таблицю «Діяльність будівельної фірми».
2.2. Вирахуйте затрати на будівельні матеріали.
2.3. Вирахуйте витрати на заробітну плату.
2.4. Вирахуйте прибуток за кожен місяць.
2.5. За допомогою автофільтру відфільтруйте дані, сума яких за січень місяць більша 1000.
2.6. Виведіть всю таблицю.
2.7. Посортуйте Послуги за зростанням.
2.8. Задайте назву аркуша Фірма.
2.9. Створіть діаграму прибутків за всі місяці.
2.10. Збережіть документ в своїй папці.
Контрольні питання
1. Як створюються зведені таблиці?
2. Що таке список в Excel?
3. Як відсортувати списки за власним порядком сортування?
4. Як відсортувати записи списку за більш як трьома полями?
5. Як забезпечити можливість відновлення початкового порядку записів списку?
6. Можливість автофільтра.
7. Як за допомогою автофільтра відібрати 5% записів, що мають найбільше значення за певним числовим полем?
8. Як за допомогою автофільтра відібрати записи, де значення деякого числового поля належать до певного інтервалу?
9. Як за допомогою автофільтра відібрати записи, де слова певного текстового поля містять “в” на третій позиції?