Відновлення вихідної бази даних

Лабораторна робота №17

Тема: Створення БД та робота з ними в середовищі табличного процесора.

Мета:придбати навички створення та роботи з базами даних в MS Excel.

Теоретичні відомості

З електронними таблицями Excel можна виконувати цілий ряд дій, характерних для робіт з базами даних (БД). При виконанні цих дій стовпці таблиці будемо називати, як це прийнято в БД, полями, а рядка — записами. Основні роботи , виконувані із БД, що випливають:

v сортування;

v пошук записів за прийнятим критерієм;

v дії із записами, що задовольняють прийнятому критерію.

Для того щоб виконати сортування елементів стовпців, необхідно:

1. Створити БД.

2. Поставити курсор у будь-який осередок БД.

3. Вибрати Дані, Сортування...

На екрані з’явиться діалогове вікно Сортування діапазону.

4.Увести імена полів і напрямок сортування, які відображаються в діалоговому вікні.

5.Натиснути ОК.

На екрані: результат сортування .

Для того щоб виконати сортування елементів рядків, необхідно:

1. Викликати БД для сортування рядків.

2.Виділити частина БД, яку необхідно сортувати: комірці C2:F4.

3.Вибрати Дані, Сортування...

4.Вибрати Параметри...

5.Сортувати: стовпці (!) діапазону.

6.Натиснути ОК.

7.Увести:

Ø номер рядка електронної таблиці Excel (а не порядковий номер рядка в БД);

Ø напрямок сортування.

8. Натиснути ОК.

На екрані: результат сортування.

Сортування за допомогою кнопок панелі інструментов

За допомогою кнопок можна виконати сортування по елементах тільки одного стовпця. Для цього необхідно виконати такі дії:

1. Помістити Курсор в комірку стовпця, по якому виконується сортування.

2. Курсор на кнопку По зростанню або По спаданню.

4. М1.

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

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

Транспонування бази даних

1.Створити БД.

2.Виділити всю БД, тобто B4:F9.

3.Виправлення, Копіювати...

4.Курсор у лівий верхній кут де розташована транспонуюча БД, в В13.

5. Виправлення, Спеціальна вставка...

6.Транспонувати.

7.ОК.

На екрані: в осередках B13;G 17 перебуває транспонована БД, у якій рядки вихідної БД (B4:F9) стали стовпцями, а стовпці - рядками. При необхідності в транспонування БД можна змінити ширину стовпців, після чого транспонована БД буде готова до пошуку потрібної інформації.

Пошук

Під пошуком, що часто називають фільтрацією, будемо розуміти знаходження в БД таких записів, які задовольняють вимогам, що вводять для полів. Ці вимоги називаються критерієм пошуку (фільтрації). В Excel передбачено 2 види пошуку:

§ автофильтром;

§ розширеним фільтром.

Пошук автофильтром

1.Викликати на екрані БД.

2.Курсор у будь-який осередок БД.

3.Дані, Фільтр, Автофильтр.

На екрані: у кожному імені поля з'явилася стрілка.

4.Курсор на стрілку того поля, по якому хочемо зробити пошук (у прикладі: Вартість).

5.М1.

На екрані: перелік значень всіх записів у цьому полі.

5.Увести значення, по якому потрібно виконати пошук.

На екрані: всі записи, що задовольняють уведеному значенню. Але це ще не все.

6.Якщо потрібно виконати пошук за таким значенням, якого немає в жодному записі, то необхідно:

7.Условие...

На екрані: діалогове вікно Користувальницький автофильтр.

8.Увести необхідний критерій пошуку. Як приклад виберемо критерій:

200 ≤ Вартість ≤ 400 (рис. 7)

9. ОК.

На екрані: результат пошуку.

Такий пошук можна послідовно виконувати по декількох полях.

Відновлення вихідної бази даних

1. Дані, Фільтр, показати всі.

На екрані; БД зі стрілками.

2. Дані, Фільтр, Автофильтр.

На екрані: вихідна БД.

Крім розглянутого пошуку Автофильтром в Excel є режим пошуку Розширеним фільтром, що дозволяє робити наступні дії:

v одночасний пошук по декількох полях;

v пошук за критерієм.

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

Пошук розширеним фільтром

1. Викликати БД (рис. 1) в осередки B3:J8.

2.Сформувати критерій пошуку.

3.Визначити координати осередків, у яких сформований критерій (у прикладі: DILE13).

4.Визначити комірці в БД для подання результатів пошуку; приймаємо B16:J22.

5.Курсор у будь-який осередок БД.

6.Дані, Фільтр, Розширений фільтр.

На екрані: діалогове вікно Розширений фільтр.

7.Увести необхідні дані.

8.ОК.

На екрані: результат пошуку в осередках B16:J20.

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

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

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

v формули, складені користувачем;

v формули, що включають функції Excel.

Робота з обчислюють критериями

v у С4 :К9 уведена вихідна база даних;

v в F12- ім'я критерію, що обчислює, при обчисленні формули. (Це ім'я може бути будь-яким, але не повинне повторювати імені поля у вихідної БД.)

v в F13- уведена формула, що є критерієм пошуку.

Як видно з формули, у неї вводяться залежності для першого рядка БД, що містять конкретні значення. При цьому, якщо вводять условия, що, для першого рядка справедливі, в комірці уведення F13 з'являється ІСТИНА, у противному випадку - НЕПРАВДА.

Пошук проводиться по описаному вище алгоритму. Результати пошуку наведені в осередках С16:К17. Далі в осередках виробляється пошук за критерієм, що включає функцію Excel. Як приклад функції Excel прийнята функція СЬОГОДНІ(), що використовує системну дату.

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

Очевидно, що пошук за таким критерієм має винятково важливе значення на практиці при контролі за ходом виконання запланованих робіт.

ХІД РОБОТИ

Завдання

1. Заповнити таблицю даними.

2. Виконати фільтрацію даних за допомогою розширеного фільтра згідно таких умов:

Вивести інформацію про жанр Quest обсяг продажу яких перевищує 20 штук;

Вивести інформацію про жанри, які розпочинаються з літери А;

Вивести інформацію про ігри в назвах яких немає літери о;

Вивести інформацію про ігри магазину "Гейм", ціна яких не перевищує 20$, або про ігри магазину "Обрій", ціна яких перевищує 20$;

Вивести інформацію про всі жанри, окрім тих, що починаються з літери S.

Вивести інформацію про жанр Quest обсяг продажу яких перевищує 20 штук або жанр Strategy обсяг продажу яких не перевищує 20 штук.

Вивести інформацію про ігри обсяг продажу яких перевищує 200 і менший від 400

3. Перейти на інший лист.

4. Вирішити задачу

Задача1. Є таблиця з оцінками студентів з деякого предмета. Створити:

а) таблицю з даними про студентів, в кого оцінка задовольняє подвійну нерівність 5<b<10 (тобто 6, 7, 8, 9);

б) таблицю з даними про студентів, в кого оцінка менша, ніж 5, або більша, ніж 10.

Задача 2. У спортивній секції чи у гуртку займаються 8 учнів з трьох класів: 10А, 10Б, 10В. На змаганнях вони отримали певну кількість балів. З'ясувати:

а) учні якого класу одержали найбільшу кількість балів;

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

Розв’язання задач

Створіть таблицю з оцінками студентів, як показано на рисунку. Оцінки подано у стовпці з назвою b.

Для математичної нерівності 5<b<10 створити розширений фільтр (він введений з клавіатури у діапазон D1:Е2 на рисунку).

Для сукупності нерівностей b<5 або b>10 створити розширений фільтр (він є в діапазоні G1:G3).

До вибраної таблиці застосувати команди Дані => Фільтр... => Розширений фільтр.

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

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

Заповнити діапазон умов. Для цього треба клацнути на значку вибору діапазону, вибрати мишею діапазон з розширеним фільтром і закрити віконце введення діапазону (інший спосіб: клацнути у полі «Діапазон умов» і вибрати мишею діапазон з розширеним фільтром).

Заповнити діапазон для результату. Для цього треба клацнути на значку вибору діапазону і вибрати мишею порожній діапазон, наприклад, А10:В16 — ця адреса автоматично буде занесена у відповідне поле як $А$10:$В$16 (або застосувати інший спосіб).

Натиснути ОК — отримаємо перший результат.

Щоб розв'язати задачу 2, потрібно створити вихідну таблицю, як показано на рисунку.

Зробити праворуч копію цієї таблиці і по групувати рядки.

З умови задачі випливає, що рядки можна по групувати за ознакою належності учнів до одного класу. Тому у копії вибрати рядки з даними (без заголовків) і виконати команду Дані => Сортувати за стовпцем Н (чи Клас) за зростанням. Отримаємо таблицю з по групованими рядками (див. рис., копія таблиці). Цю таблицю скопіювати під вихідну таблицю.

Виокремити дані разом із заголовками стовпців і виконати команду Дані => Підсумки... Заповнити діалогове вікно Проміжні підсумки, як зображено на рисунку, і виконати команду ОК.

На місці вибраної таблиці отримаємо результат (на рисунку він ліворуч від діалогового вікна). Тепер легко з'ясувати, що учні 10А отримали найбільше балів.

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

Якщо клацнути на значках «-» ліворуч від підсумкової таблиці, то можна згорнути рівні, і таблиця набуде компактного вигляду (відображатимуться лише рядки з проміжними підсумками). Щоб розгорнути рівні, потрібно клацнути на значках «+».

5. Оформіть звіт.

Контрольні запитання.