Гіпермаркет Знань>>Інформатика>>Інформатика 11 клас>> Інформатика: Практична робота №5. Аналіз даних за допомогою функцій табличного процесора.

Статті до предмету Інформатика 11 клас.

Тема «Бази даних у Ecxel».

Розгляд теми: Бази даних у Ecxel

                                                                         Тема 6. Бази даних у Ecxel

6.1. Поняття баз даних у Excel. Сортування баз даних.

База даних – впорядкована інформація, об’єднана в єдине ціле.

Рядки БД є записами, а стовпці – полями записів.

Поле запису – мінімальний елемент запису, який обов’язково має ім’я.

Запис – це група взаємопов’язаних полів, що має однакову структуру, тобто однакові кількість полів, типи даних відповідних полів.

Перехрестя стовпця та рядка утворюють комірку БД.

У загальному вигляді БД можна подати так:

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

Для розміщення записів БД у встановленому порядку використовується функція сортування.

Сортування можна проводити за одним, двома або трьома ключами.

Для здійснення сортування даних необхідно виділити базу даних разом з заголовком і викликати меню Данные/Сортировка. З’являється вікно сортування, зображене на малюнку.

Наступний крок – обрати ідентифікацію полів за підписами або за позначеннями стовпчиків. Краще це робити за підписами, тому що менше шанс помилитися при сортуванні.

Сортування здійснюється за ключами.

Введення першого ключа починається у поле Сортировать по. Ключ – підпис стовпця або позначення стовпця. Ліворуч обирається напрямок сортування: по возрастанию або по убыванию.

Потім можна обрати другий та третій ключі сортування.

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

У наведеній таблиці здійснене сортування: 1 ключ – за прізвищем (за зростанням), 2 ключ – за ім’ям (за зростанням), 3 ключ – за віком (за спаданням).

Сортування за другим ключем видно лише де збігаються значенням першого ключа. Сортування за третім ключем видно лише при збіганні першого та другого ключей.

6.2. Практичні завдання

Завдання 1. Використовуючи файл бази даних база.xls виконати впорядкування бази даних.

Хід роботи:

1.    Відкрити файл бази даних.
2.    Зберегти отриманий файл на мереженому диску WorkNet\Work\9 klas\ під своїм прізвищем.
3.    Скопіювати лист бази даних.
4.    Назву першого листа змінити на „Вихідна база”.
5.    Назву другого листа змінити на „Впорядкована база”
6.    На листі „Впорядкована база” впорядкувати базу за критеріями:

1 – зростання дати видавництва;
2 – зменшення ціни;
3 – автор за абеткою.

7.    Виділіть кольором рядки, де видна фільтрація за другим критерієм (один раз).
8.    Виділіть іншим кольором рядки де видна фільтрація за третім критерієм (один раз).
9.    Збережіть впорядковану базу.

6.3. Фільтрування баз даних

Фільтрація даних – пошук та виведення на екран тільки тих даних, які задовольняють поставленій умові.

Фільтрація здійснюється за допомогою автофильтру або розширеного фільтру.

Фільтрація за допомогою автофільтру.

Виділити базу даних і обрати в меню команду Данные/Фильтр/Автофильтр. Після чого заголовок бази даних буде мати кнопки списків (стрілка у правому нижньому кутку).

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

Якщо використовується фільтрація за зазначеними значеннями (Іванов, Петров, ...) то сортування виконується без додаткових параметрів, якщо ж треба відсортувати данні за умовою то користувач потрапляє до вікна Пользовательский автофильтр, зображене на малюнку.

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

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

Фільтрація за допомогою автофільтруздійснюється в тій самій таблиці, данні, що не війшли до відфільтрованої таблиці приховуються але не зникають, їх завжди можна поновити.

                                                     Фільтрація за допомогою розширеного фільтру.

Ця команда дає можливість створення нової бази зданих за обраним критерієм використовуючи данні існуючої БД.

Фільтрація за допомогою розширеного фільтру потребує попередньої підготовки – створення критерію умов. Критерій повинен розміщуватися поза значеннями БД. Він складається з назв полів та умови, записаної під відповідною назвою поля. Назви полів, за для цього, копіюють у вільне місце робочого листа, або, навіть, на інший робочий лист книги  а під ним створюють умови за якими будуть обиратись записи БД. Умови створюються за тими ж принципами, що і логічні функції.

Критерій умов на нашому прикладі розташований на 13, 14 рядках. комірка А14 містить функцію =”Иванов”, буде відображається лише Иванов. Комірка Вік містить граничне значення 20 років,  за яким здійснюється відбір людей.

Коли критерій умов сформований можна переходити до фільтрації бази за допомогою розширеного фільтру. Команда Данные/Фільтр/Расширенный фильтр.

Ми потрапляємо у вікно розширеного фільтру де у меню Обработка помічаємо Скопировать результат в другое место.

В якості Исходного диапазона буде виступати діапазон А1:С11 нашої бази даних, Диапазон русловий – критерій умов у комірках А13:С14. результат розмістимо починаючи з комірки А16.

Після введення цих даних і натискання клавіші ОК у комірках А16:С18 отримаємо нову базу даних.

Слід пом’ятати, що на відміну від автофільтру розширений фільтр створює нову базу даних, таким чином, якщо ви здійснюєте фільтрацію „на місці”, тоб-то у тих самих комірках, що і вихідна база, то данні з вихідної бази будуть втрачені. Для запобігання втрати вихідних даних скористайтеся флажком Скопировать результат в другое место.

6.4. Практичні завдання

Завдання 1. Фільтрація бази даних за допомогою автофільтру. Якщо завдання виконується одразу після першого починати з 5 пункту.

1.    Відкрити файл бази даних.
2.    Зберегти отриманий файл на мереженому диску WorkNet\Work\9 klas\ під своїм прізвищем.
3.    Назву першого листа змінити на „Вихідна база”.
4.    Скопіювати лист бази даних.
5.    Назву другого листа змінити на „Фільтрована база”
6.    Перейти на лист „Фільтрована база”
7.    Розташувавши курсор у середині бази даних відфільтруйте базу за допомогою авто фільтру за критеріями:

1 – вибрати всі видання, що були видані у 1975 році;
2 – вибрати всі видання, що видані видавництвом А;
3 – вибрати всі видання, кількість сторінок яких не перевищує 200;

4 – вибрати всі видання, що мають матову обкладинку;
8.    Збережіть відфільтровану базу.

Завдання 2. Фільтрація бази даних за допомогою розширеного фільтру. Якщо завдання виконується одразу після першого та другого завдання починати з 7 пункту.

1.    Відкрити файл бази даних.
2.    Зберегти отриманий файл на мереженому диску WorkNet\Work\9 klas\ під своїм прізвищем.
3.    Назву першого листа змінити на „Вихідна база”.
4.    Скопіювати лист бази даних.
5.    Назву другого листа змінити на „Розширений фільтр”
6.    Створіть новий лист і дайте йому ім’я „Таблиця критеріїв”
7.    Перейдіть до листа „Таблиця критеріїв” і створіть таблицю критеріїв для фільтрації даних за критеріями:

1 – ціна видання менша за 20 гривень;
2 – видавництво Б;
3 – дата видання до 1955 року.

8.     Перейдіть на лист „Розширений фільтр” і відфільтруйте базу даних за допомогою розширеного фільтру не змінюючи діапазону фільтрування.
9.    Збережіть відфільтровану базу.

6.5. Розрахунки проміжних підсумків

Проміжні підсумки розраховуються для полів, які мають значення, що повторюються. Такими підсумками можуть бути сума, кількість значень, мінімальне значення, максимальне значення тощо.

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

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

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

Далі клацнути в середини бази даних і виконати команду Данные/Итоги.

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

У категорії При каждом изменении в: потрібно вибрати значення Прізвище, так як саме за прізвищем ми будемо шукати вік молодшого представника родини.
У вікні Операция слід обрати операцію минимум, так як ми бажаємо отримати мінімальний вік людини.

У вікні Добавить итоги поставити відмітку до комірки Вік, тому, що потрібно проаналізувати вік людини.

При першому підбитті підсумків необхідно поставити відмітку у поля Заменить текущие итоги та Итоги под данными. При повторному підбитті підсумків на цій самій таблиці, якщо треба залишити попередні підсумки теж, слід видалити відмітку у полі Заменить текущие итоги.

Після того, як необхідні поля заповнені натиснути ОК.

Замість нашої таблиці з’явиться таблиця, що показана на малюнку. Більш жирним кольором у якій буде виділені рядки підсумків. Таким чином з’ясувалось, що наймолодший Иванов має вік 12 років, Петров – 8 років, а для Макєєва та Сергєєва наведені данні виходячи з того, що їх родини складаються з одної людини.

Поруч з таблицею проміжних підсумків з’явилася структура вкладеності даних (сіре поле ліворуч таблиці). Зверху структури проміжних підсумків знаходяться кнопки, за допомогою яких можна згортати та розгортати перегляд усіх даних цього рівня. При натисканні на кнопки можна переглядати дані кожного розрахункового поля окремо.

Для вилучення розрахованих підсумків слід встановити курсор у базі даних і активізувати команди Данные/Итоги/Убрать все.

Надіслала вчитель інформатики СЗШ № 54 Солом*янського району м. Києва Тальнова Олена Володимирівна.