Лабораторні роботи бази даних в excel

Поточна і дві наступні лабораторні роботи присвячені вивченню засобів і методів роботи з таблицями даних, що надаються табличним процессоромExcel. Роботи можуть проводитися на комп'ютерах з різними операційними системами. Тому велика частина малюнків представлена ​​як для ОСWindowsтак і для ОСMac-OS-X. Малюнки для ОСMac-OS-Xімеют ті ж номери що і для ОСWindows, але завершуються позначкою «mac».

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

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

Початок роботи: Відкрити программуWordі створити файл «Звіт по лабораторії №1»; Відкрити программуExcel і створити файл «Лабораторна робота №1».

Відкрити Інтернет для пошуку значень команд по довідкових систем.

Вивчити теоретичне обгрунтування.

Створити власну таблицю на основі структури таблиці №1 і наповнити її даними з таблиці №1. Окремо зберегти копію створеної таблиці. Зберегти файл. Таблицю або копію екрану помістити в файл звіту. Дати пояснення кожної копії і команді.

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

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

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

Виконати Розширену фільтрацію на основі заданих в роботі критерій. Таблицю з заданих умов для розширеної фільтрації і отриману таблицю помістити в звіт. Дати пояснення кожної копії і команді.

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

Оформити звіт: TimesNewRoman, 14; поля: верхнє - 2, нижнє - 1,5 ліве - 3, праве - 1,5. Загальна сторінка - не менше 15 стор.

Захищати Звіт у викладача.

В результаті виконання лабораторної роботи ви повинні знати і вміти виконувати:

пошук даних в базі,

сортування і фільтрацію даних,

підведення проміжних підсумків за даними бази.

Таблиці Excelможно використовувати в якості баз даних. У цьому випадку застосовується наступна термінологія: стовпчики таблиці називаються полями, верхній рядок - заголовками полів, інші рядки - записами (рис.1).

Основними завданнями при роботі з базами даних є:

сортування і фільтрація даних,

підведення проміжних підсумків за даними бази.

Ці операції доступні через пункт меню ДАНІ (DATA) (рис.2).

Виділяється будь-яка комірка таблиці

Вибирається пункт меню: ДАНІ -> Форма (DATA-Form ...).

На екрані з'явиться форма, наведена на рис.3.

Переміщення по записах:

Стрілки вгору і вниз.

Перехід до першої або останньої записи:

Ctrl + стрілка вгору, Ctrl + стрілка вниз.

Переміщення по полях форми:

Додавання або видалення запису:

Кнопки Додати (New) іУдаліть (Delete).

Ctrl + лапки (клавіша з літерою Е).

Пошук даних (записів) за критерієм.

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

Перехід в режим пошуку записів за критерієм здійснюється вибором команд: ДАНІ -> Форма -> Критерії (DATA-Form ... -Criteria). Потім слід задати в полях умови пошуку. При цьому можна використовувати такі символи-замінники:

? - замінює один символ,

* - замінює будь-яку кількість символів,

<> <=>= = Використовуються для запису умов.

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

Для нашого прикладу таблиці (рис.1) при завданні критеріїв як на рис.4 відібрана буде тільки 1-я запис (рис.3).

Для упорядкування даних служить операціясортіровкі: ДАНІ -> Сортування ... (DATA-Sort ...)

Перед викликом команди треба поставити курсор в одне з полів таблиці. Після вибору команди ДАНІ -> Сортування (DATA-Sort ...) відкриється діалогове вікно рис.5.

Як видно в цьому вікні можна задати три критерії сортування. У полях Сортувати по (Sort by), Потім по (Then by), В останню чергу по (Then by) задаються назви сортируемих полів. Справа розміщені перемикачі напряму сортування: по зростанню (Ascending) - від першої літери алфавіту до останньої, від меншого числа до більшого, від більш ранньої дати до більш пізньої і по спадаючій (Descending) - в зворотному напрямку. Перемикачі Ідентифікувати поля за підписами і Ідентифікувати поля по позначенням дозволяють відповідно включати і не включати верхній рядок таблиці в процесі сортування. Кнопка Параметри відкриває діалогове вікно, що дозволяє встановити послідовність нестандартної сортування.

Для бази представленої на рис.1 задамо критерії сортування рис.6.

Сортування буде здійснюватися по полю Жанру потім Ціни і в останню чергу по-сті замовлених примірників. Результат сортування представлений на рис.7.

Для + заданим умовам і перекладу інших в приховане стан служить фільтрація даних: дані-> Фільтр-> Автофільтр (Data-Filter-AutoFilter) іліДАННИЕ-> Фільтр-> Розширений фільтр ... (Data-Filter-AdvancedFilter ...)

Розглянь спочатку роботу з інтелектуального фільтра. При виборі команди дані-> Фільтр-> Автофільтр (Data-Filter-AutoFilter) в рядку назв полів розміщуються кнопки списків, що розкриваються (рис.8). За допомогою цих кнопок у випадних вікнах можна задавати критерії відбору рядків (фільтрації). При завданні критеріїв в декількох полях вони об'єднуються за принципом логічного множення.

Крім вибору одного критерію в випадаючому вікні (наприклад, ЖЗЛ на рис.8) можна вибрати пункт Условие (Custom Filter ...). В цьому випадку відкриється вікно, в якому можна задати більш складний критерій вибору для поточного поля. На рис.9 для поля Ціна заданий діапазон цін більше 80 і по 240 рублів.

Результат виконання складного умови фільтрації представлений на рис.10.

Кнопки полів, для яких виконана фільтрація, виділяються синім кольором. Щоб скасувати автофильтр зніміть прапорець Автофільтр (Data-Filter-AutoFilter) (повторне виконання командиДАННИЕ-> Фільтр-> Автофільтр).

Тепер розглянемо використання розширеного фільтра (дані-> Фільтр-> Розширений фільтр ...) (Data-Filter-Advanced Filter ...) для більш складних критеріїв фільтрації.

Можливості розширеної фільтрації розглянемо на прикладі рішення наступної задачі.

а також (або) всі замовлення на книги Видавництва Кримський міст-9Д.

Для вирішення завдання необхідно виконати наступні дії:

Створити область критерію. Для цього заповнити осередки як показано на рис.11.

Тут стовпці відповідають критеріям, відібраним для фільтрації. Кожен рядок описує відповідне умова з чотирьох умов фільтрації сформульованих вище. Умови, що записуються в одному рядку, об'єднуються умовою І. Умови, записані в різних рядках, об'єднуються умовою АБО.

Таку таблицю зручно розташувати на окремому аркуші. Ми так і зробимо. Створимо таку таблицю на аркуші 2.

Встановлюємо курсор на будь-яку клітинку бази даних і викликаємо діалогове вікно Розширений фільтр (дані-> Фільтр-> Розширений фільтр ...) (Data-Filter-Advanced Filter ...) (Рис. 13).

Встановлюємо перемикач скопіювати результат в інше місце. В цьому випадку ми зможемо бачити і вихідну базу і таблицю з відфільтрованими даними. Задаємо вихідний діапазон (відповідає координатам бази). Задаємо діапазони умов і розміщення результату. Для розуміння координат см. Рис.11 і рис.12.

Натискаємо OKі отримуємо результат представлений на рис.14.

Тепер сформулюємо завдання отримання підсумків по базі даних. Потрібно забрати результати продажів книг в натуральному і грошовому еквіваленті по книгам кожного жанру окремо і разом. Для вирішення даного завдання спочатку відсортуємо порядок записів по полю Видавництво (ДАНІ -> Сортування ...) (Data-Sort ...). Результат на рис.16.

Тепер викликаємо команду Підсумки ... (ДАНІ -> Підсумки ...) (Data-Subtotals ...). Результатом стане поява діалогового вікна рис.17. Задаємо значення полів як показано на малюнку. У полеПрі кожній зміні в: (At each change in) вказуємо ім'я поля по групах позицій якого вимагається отримання підсумків. У полеОперація (Use function) вказуємо операцію над значеннями осередків. У нашому випадку це сума як для кількостей книг так і для величин сум замовлень. В поле додати підсумки по: вказуємо ті поля для яких власне потрібне отримання підсумків. ЕтоКолічество іСумма замовлення. НажімаемOKі отримуємо результат рис.18.

Зліва з'явилася карта структури бази даних. Кнопки зі знаком мінус дозволяють згорнути відповідну їй групу даних, від якої залишається тільки одна підсумкова рядок, а на кнопці з'являється знак плюс (рис.19).

Контрольні питання до лабораторної роботи №1

У чому полягає мета даної лабораторної роботи?

Дайте визначення команди «Сортування»;

Що таке команда «Фільтр»?

У чому полягають відмінності між командами «Сортування» і «Фільтр»?

Які вигляді фільтрації виробляються в даній лабораторній роботі?

Чим відрізняються фільтри «Автофільтр» і «Розширений фільтр»?

Що таке команда «Проміжні підсумки»?

Як формуються умови для створення таблиці «Проміжні підсумки?

Поясніть, чим відрізняється отримана вами таблиця «Проміжні підсумки» від основної (базової) таблиці.