ЛАБОРАТОРНА РОБОТА № 7
Тема: Робота з базами даних в MS Excel.
Мета: Створення БД та найпростіші операції з ними в MS Excel.
За допомогою Microsoft Excel можна створювати і обробляти дані, що представлено у вигляді таблиці, що інтерпретується як БД. .
База даних (БД) в MS Excel – таблиця, що складається з однотипних записів (рядків). Стовпці таблиці є полями запису БД. Під імена полів відводиться перший рядок у базі. Наприклад, якщо базою даних вважати прайс товарів в магазині, то полями запису будуть: Код товару, Назва товару, Ціна, Кількість та Знижка.
Рис. 1
Для роботи з базою даних необхідно спочатку створити відповідну таблицю. Якщо виділити комірку в таблиці і вибрати одну з команд обробки баз даних у меню Данные, Microsoft Excel автоматично визначає й обробляє всю таблицю. Дані, розташовані в стовпцях і рядках робочого аркушу, обробляються як набір полів, що утворюють записи(Рис.1).
Сортування даних
Сортування дозволяє перевпорядкувати рядки в таблиці по будь-якому полі. Наприклад, щоб упорядкувати дані за ціною виробу. Для сортування даних слід виділити будь-яку комірку таблиці і викликати команду Сортировка із менюДанные.
У полі прихованого переліку Сортировать по вибирається поле, за яким будуть відсортовані дані, і тип сортування.
Можна додати наступний рівень сортування обравши опцію Добавить уровень
У полі прихованого переліку Затем по указується поле, по якому будуть відсортовані дані, що мають однакові значення в першому ключовому полі. У другому полі Затем по указується поле, по якому будуть відсортовані дані, що мають однакові значення в перших двох ключових полях тощо.
Для сортування даних також використовуються кнопки
. Перед їх використанням слід виділити стовпець, по якому необхідно сортувати таблицю.При сортуванні по однім стовпці, рядки з однаковими значеннями в цьому стовпці зберігають колишнє упорядкування. Рядки з порожніми комірками в стовпці, по якому ведеться сортування, розташовуються наприкінці переліку, що сортується. Microsoft Excel дозволяє також сортувати не всю таблицю, а тільки виділені рядки або стовпці.
Форми даних
В Excel не слід робити нічого особливого для того, щоб використовувати перелік в якості БД. При виконанні характерних для баз даних операцій, таких як пошук, сортування, підведення підсумків, Microsoft Excel автоматично розглядає таблицю як базу даних.
При перегляді, зміні, додаванні і видаленні запису в базі даних, а також при пошуку конкретних записів за визначеним критерієм зручно використовувати Форми даних. При звертанні до команди Форма меню Данные Excel читає дані й створює діалогове вікно форми даних (Рис.2).
Рис.2
На стандартній стрічці меню Форми може не бути, тоді налаштуємо панель під себе (права кнопка миші на панелі Данные, після чого Настройка ленты):
У формі даних на екран виводиться один запис. При уведенні або зміні даних у полях цього вікна змінюється вміст відповідних полів у базі даних.
Для використання форм даних таблиця повинна мати імена стовпців. Імена стовпців стають іменами полів у формі даних. Поле відповідає кожному стовпцю в таблиці. Форма даних автоматично розгортається так, щоб вивести на екран відразу усі поля в даній таблиці, до 32 полів за один раз. За допомогою смуги прокручування можна прокручувати записи в базі даних. Позиція виведеного запису вказується у верхньому правом куті. Пересуватись по полях форми можна за допомогою миші та клавіш Tab (вниз), Shift+Tab (вверх).
На формі (Рис.2) розташовані такі кнопки.
Добавить – очищує поля для введення нового запису бази даних. Якщо знову натиснути кнопку Добавить , то уведені дані будуть додані як новий запис у кінець бази даних.
Удалить – видаляє виведений запис, інші записи бази даних зсуваються. Видалені записи не можуть бути відновлені.
Вернуть – відновлює відредаговані поля у виведеному запису, видаляючи зроблені зміни. Щоб відновити запис, необхідно зробити це перед натисканням клавіші Enter або перед переходом до іншого запису.
Назад – виводить попередній запис у переліку. Якщо був визначений критерій за допомогою кнопки Критерии, то кнопка Назад виведе попередній запис із тих, що задовольняють заданому критерію.
Далее – виводить наступний запис у базі даних.
Критерии – очищає поля для уведення критеріїв порівняння з операторами порівняння для пошуку необхідної підмножини записів.
Правка – слугує для виходу з режиму уведення критеріїв. Доступна тільки тоді, коли натиснута кнопка Критерии .
Очистить – видаляє існуючий критерій із вікна діалогу. Доступна тільки тоді, коли натиснута кнопка Критерии .
Закрыть – закриває форму даних.
Установлення інтервалу критеріїв
Критерії бувають двох типів.
1. Критерії обчислення – це критерії, що є результатом обчислення формули. Наприклад, інтервал критеріїв =F7>СРЗНАЧ($F$7:$F$21) виводить на екран рядки, що мають у стовпці F значення більше, ніж середнє значення розмірів у вічках F7:F21. Формула повинна повертати логічне значення ЛОЖЬ або ИСТИНА. При фільтрації будуть доступні тільки ті рядки, значення яких будуть додавати формулі значення ИСТИНА .
2. Критерії порівняння – це набір умов для пошуку, використовуваний для витягу даних при запитах за прикладом. Критерій порівняння може бути послідовністю символів (константою) або вираженням (наприклад, Ціна > 700 ).
Щоб знову одержати доступ до усіх записів переліку необхідно натиснути кнопку Критерии , а потім натиснути кнопку Правка.
Команда Фильтр меню Данные дозволяє відшукувати і використовувати потрібну підмножину даних у переліку. У відфільтрованому переліку виводяться на екран тільки ті рядки, що містять визначене значення або відповідають визначеним критеріям, при цьому інші рядки виявляються приховані. Для фільтрації даних використовуються команди Автофильтр і Расширенный фильтр з пункту Фильтр меню Данные .
Итоги
Команда Данные>Промежуточные итоги – один зі способів аналізу БД
Функції роботи з базами даних
В MS Excel є цілий набір функцій, які призначені для роботи з базами даних. Викликаємо Мастер функций серед категорій обираєте Работа с базой данных. Ця група функцій працює тільки з тими даними, які підходять під певний критерій.
Усі ці функції будуються за одним типом:
= Назва функції (база_даних; поле; критерій)
база_даних– всі комірки бази, разом із заголовками полів. Щоб кожного разу не задавати діапазон, - бажано привласнити БД певне ім’я (Формулы > Присвоить имя);
поле визначає той стовпчик, для якого робитимуться обчислення;
к
ритерій задає критерій, по якому відбиратимуться дані для обчисленьНаприклад, якщо критерієм буде знижка більше за 5%, то треба створити окрему пару комірок: Назва поля для критерію та сам критерій:
ВАЖЛИВО!!! Зверніть увагу, що назва критерію має співпадати з назвою поля, для якого цей критерій буде застосовуватись, тобто заголовок стовпчика з критерієм має бути таким же, як заголовок стовпчика, в якому ви збираєтеся підраховувати дані.
Завдання:
Заповнити БД записами (≈15) використовуючи 2 способи:
Табличний – безпосередній ввід даних записів в таблицю. При цьому створити одне з полів БД, що заповнюється, як значення зі списку (у вкладці Данные в групі Работа с данными команда Проверка даннях, тип даних – Список)
Данні вводити за допомогою Форми.
Відсортувати записи по кількості товарів, а в межах кількості – по назві у алфавітному порядку.
Використовуючи тільки!!! апарат Функції по роботі з базами даних обчислити:
1) кількість товарів зі знижкою;
2) загальну суму залишків по кожному з товарів;
3) товар, ціна на який найвища;
4) вивести повну інформацію про товар на який є 10% знижка
Використовуючи функцію ВПР знайти в таблиці ціну на вказаний товар
Продемонструвати роботу функції ГПР (придумати власний приклад)