Тема Робота з електронними таблицями як з базами даних
Работа добавлена на сайт samzan.net:
Поможем написать учебную работу
Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
от 25%
Подписываем
договор
Лабораторна робота № 3
Тема. Робота з електронними таблицями як з базами даних.
Мета роботи. Оволодіти технологією оброблення, пошуку та аналізу даних із використанням убудованих операцій оброблення баз даних.
Тривалість лабораторного заняття – 4 год.
Завдання:
- оволодіти технологією сортування записів таблиць баз даних за простим і складним ключем;
- оволодіти технологією використання функції Итоги для отримання проміжних та підсумкового результатів;
- оволодіти технологією вибірки даних із використанням фільтрів (автофільтра та розширеного фільтрів);
- опанувати технологію застосування функцій статистичного аналізу для оброблення баз даних;
- опанувати технологію побудови умов та використання розрахун кових критеріїв;
- здійснити копіювання вихідних документів, створених у ході виконання лабораторної роботи № 2, на окремі листи робочої книги та виконати в них сортування і групування даних за вказаним ключем (ключами) сортування;
- здійснити розрахунки проміжних та підсумкового результатів з використання функції Итоги;
- виконати аналіз даних вихідного документа, використовуючи функції оброблення баз даних – фільтри. Для кожного типу фільтрів створити окремий робочий лист, у якому відобразити результати фільтрації. результати використання автофільтра показати шляхом копіювання відфільтрованих даних у окремий робочий лист;
- здійснити оброблення табличних даних із використанням функції статистичного аналізу бази даних;
- здійснити вибірку табличних даних за розрахунковим критерієм із використанням статистичних функцій та функцій статистичного аналізу бази даних.
Технологія виконання завдання
Сортування табличних даних:
- створити новий лист робочої книги та надати йому назву Т7 Кварт відомість підсумки;
- скопіювати вихідний документ (Таблиця 5. «Квартальна відомість приготування страв») з листа Т5 Квартальна відомість на лист Т7 Кварт відомість підсумки. Таблицю при копіюванні розміс тити на новому листі в тому ж діапазоні комірок, із яких вона копіюється;
- у створеній копії вихідного документа здійснити сортування даних за зростанням одночасно по двох стовпцях: по Назві групи виробів (NG) та Назві виробу (NV). Для сортування треба виділити інформаційний масив таблиці, включаючи рядок ідентифікаторів, та викликати вікно Сортировка диапазона (команда Сортировка... меню Данные);
- у полі Сортировать по... вікна Сортировка диапазона вибрати із спливаючого списку назву поля NG та увімкнути опцію По возрастанию. У полі спливаючого списку Затем по... вибрати назву поля NV та увімкнути опцію По возрастанию (рис. 21);
- натиснути кнопку ОК (результати сортування представлені на рис. 22).
Рис. 21. Виділення табличних даних (показано фрагмент таблиці) та вікно Сортировка диапазона з вибіркою даних
Рис. 22. Результати сортування даних вихідного документа
Групування даних, проміжні та підсумкові розрахунки із використанням функції ИТОГИ():
- у попередньовідсортованій таблиці виділити інформаційний масив, включаючи ідентифікатори стовпців, та відкрити вікно Промежу точные итоги (команда Итоги... меню Данные);
- у полі спливаючого списку При каждом изменении в... вибрати ім’я стовпця, за яким групуються дані (стовпчик NG);
- у полі спливаючого списку Операция вибрати тип проміжних та підсумкових розрахунків (за умовами задачі це Сумма);
- у полі спливаючого списку Добавить итоги по... увімкнути індика тори назв стовпців, за якими обчислюються підсумки (рис. 23);
Рис. 23. Діалогове вікно функції Итоги...
- натиснути кнопку ОК, вихідний документ набуває такого вигляду, як показано на рис. 24.
Рис. 24. Вигляд підсумкового документа із згрупованими даними
за групою виробів (поле NG), проміжними та підсумковим результатами
(частина рядків на рисунку прихована)
Кнопки керування, які розміщені зліва від таблиці дозволяють приховувати проміжні результати.
Технологія вибірки даних із використанням фільтрів (автофільтра та розширеного фільтрів)
Автофільтр
Командою Данные/Фильтр/Автофильтр, зробити вибірку даних з вихідного документа Таблиця 5 «Квартальна відомість приготу вання страв» (рис. 20):
- для назви виробу М’ясний салат;
- для виробів фактична кількість (FAKT) перевищує 1000 та менша 500;
- для виробів, назва (NV) яких починається з літери «С»;
- для Закусок і страв з овочів (поле NG) фактична кількість яких (FAKT) перевищує 1000.
Перед тим, як установити автофільтр Таблиця 5 копіюється та зберігається на окремому листі робочої книги.
Технологія виконання автофільтра:
- виділити рядок ідентифікаторів Таблиці 5 (діапазон клітинок А3:F3, рис. 25);
- виконати команду Фільтр/Автофильтр меню Данные. В рядку ідентифікаторів з’являються кнопки керування;
- у полі, де встановлюється фільтр, кнопкою керування розкрити спливаючий список. Список включає конкретний перелік значень, а також команду Условие… (рис. 25);
Рис. 25. Поле зі списком автофільтра
- командою Условие… відкрити діалогове вікно Пользовательский автофильтр та встановити умови відбору по даному полю;
- для варіанта а вигляд вікна Пользовательский автофильтр з умовою відбору по полю NV та результати його виконання автофільтра представлені на рис. 26;
- для варіанта b вигляд вікна Пользовательский автофильтр з умовою відбору по полю (FAKT) та результати його виконання представлені на рис. 27;
- для варіанта c вигляд вікна Пользовательский автофильтр з умовою відбору по полю (NV) та результати його виконання представлені на рис. 28;
- для варіанта d вигляд вікон Пользовательский автофильтр з умовами відбору по полям KG та FAKT і результати виконання автофільтру представлені на рис. 29;
Рис. 26. Вигляд вікна Пользовательский автофильтр з умовою вибірки
по варіанту а та результат його виконання
Рис. 27. Вид вікна Пользовательский автофильтр з умовою вибірки по варіанту b та результат його виконання
Рис. 28. Вигляд вікна Пользовательский автофильтр з умовою вибірки
по варіанту c та результат його виконання
Рис. 29. Вигляд вікна Пользовательский автофильтр з умовою вибірки по варіанту d та результат його виконання
- у полях, де встановлено фільтр, стрілка на кнопці управління виділена синім кольором;
- щоб зняти фільтр у спливаючому списку слід вибрати команду Все;
- для зняття автофільтра з таблиці виконати команду Данные/Фильтр та відключити Автофильтр.
Розширений фільтр
Використовується при фільтрації даних БД за складним критерієм, наприклад, розрахунковим. Діалогове вікно Расширенный фильтр (рис. 30) викликається командою Фильтр/Расширенный фильтр (меню Данные).
Рис. 30. Діалогове вікно Расширенный фильтр
Для виконання розширеного фільтра слід визначити 3 критерії (діапазони), які задаються у діалоговому вікні Розширений фільтр:
- область пошуку у списку, який необхідно фільтрувати;
- діапазон умов, що задається таблицею критеріїв. Таблиця критеріїв будується у вільній частині листа та включає імена полів таблиці БД, за який визначаються умови для фільтрів;
- третій діапазон задається за умови, якщо результати вста новлення фільтра розміщуються в іншій частині робочого листа. Цей діапазон указується у випадку, коли вмикається опція Обра ботка/Скопировать результат в другое место діалогового вікна Расширенный фильтр.
За допомогою Розширеного фильтра зробимо вибірку даних у вихідному документі Таблиця 5 «Квартальна відомість приготування страв» (рис. 20) за вищенаведеними умовами a, b, c, d для автофільтра.
Перед виконанням кожного з цих завдань Таблиця 5 копіюється та зберігається на окремому листі робочої книги.
Технологія виконання розширеного фільтра:
- скопіювати Таблицю 5 на окремий лист робочої книги та надати йому відповідну назву (наприклад, Розширений фільтр а);
- справа від таблиці побудувати таблицю критеріїв (рис. 31). У таблиці критеріїв заголовок стовпця (стовпців) повинен точно збігатися із заголовком (ідентифікатором) стовпця таблиці. Краще ідентифікатор просто скопіювати;
- скопіювати рядок ідентифікаторів Таблиці 5, розмістивши його під самою таблицею (рис. 31). Тут, під рядком ідентифікаторів будуть відображені результатів фільтрації. (Це приклад виконання завдання для варіанта копіювання результатів в інше місце таблиці);
- командою Фильтр/Расширенный фильтр (меню Данные) відкрити вікно Расширенного фильтра;
- курсор миші помістити в поле Исходный диапазон діалогового вікна та виділити масив значень Таблиці 5, включаючи рядок ідентифікаторів (А3:F48);
- курсор миші помістити в поле Диапазон условий та виділити таблицю критеріїв, (Н3:Н4);
- увімкнути опцію Скопировать результат в другое место;
- помістити курсор в поле діалогового вікна Поместить результат в диапазон та виділити рядок ідентифікаторів під таб лицею (А50:F50) і натиснути кнопку ОК;
- заповнення вікна та результат дії розширеного фільтра представлені на рис. 31.
Рис. 31. Розширений фільтр для варіанта а
На рис. 32 представлені умови Расширенного фільтра для варіантів b, с та d.
|
NG
|
FAKT
|
|
Закуски і страви з овочів
|
>1000
|
Рис. 32. Таблиці критеріїв для варіантів b, с та d
Технологія виконання роширеного фільтра із використанням цих критеріїв аналогічна техології, описаній вище для варіанта а.
Застосування функцій статистичного аналізу
для оброблення баз даних
Для виконання цього завдання слід знову зробити копію Таблиці 5 на новому листі робочої книги.
Розрахувати загальну вартість виробів групи «Закуски і страви з овочів» із використанням функції для роботи з базою даних БДСУММ().
Технологія виконання завдання
- поряд з Таблицею 5 створити таблицю з критерієм вибірки (за аналогією з розширеними фільтром). Вона матиме такий вигляд:
|
NG
|
|
Закуски і страви з овочів
|
- табличний курсор помістити під стовпчиком VVIR (комірка F49);
- командою Вставка/Функція увімкнути Майстра функцій;
- у вікні Майстра функцій вибрати категорію функцій Работа с базой данных, та відкрити вікно конструктора БДСУММ();
- курсор миші помістити в поле База _данных вікна конструктора та виділити масив значень Таблиці 5, включаючи рядок ідентифікаторів (А3:F48);
- курсор миші помістити в поле Поле вікна конструктора та увести цифру 6 – порядковий номер стовпчика Таблиці 5, за яким проводиться розрахунок;
Рис. 34. Вікно конструктора функції БДСУММ()
- курсор миші помістити в поле Критерій вікна конструктора, виділити таблицю критеріїв та натиснути кнопку ОК;
- під стовпчиком VVIR, у комірці F49 з’являється результат розрахунку (рис. 35).
Рис. 35. Вікно конструктора функції БДСУММ () із введеними аргументами та результат табличних розрахунків
Технологія побудови умов та використання
розрахункових критеріїв
Зробити вибірку записів з Таблиці 5 Квартальної відомості приготування страв, у яких фактична кількість виробів менша за середнє значення фактичної кількості виробленої групи виробів «Закуски і страви з овочів» станом на 31.01.2012.
Критерії відбору сформувати двома способами:
1) із використанням функції СРЗНАЧ();
2) із використанням функції ДСРЗНАЧ().
Технологія виконання завдання із використанням функції СРЗНАЧ():
- для виконання цього завдання слід зробити копію Таблиці 5 на новому листі робочої книги;
- під копією Таблиці 5 сформувати критерій відбору із викорис танням функції СРЗНАЧ() так, як це показано на рис. 37;
Рис. 37. Вигляд вікна програми з сформованою таблицею критерію відбору
- для застосування розширеного фільтра і відображення резуль татів відбору розмістити також під таблицею копію рядка іденти фікаторів (рис. 37);
- командою меню Данные Фильтр/Расширенный фильтр відкрити вікно розширеного фільтра;
- заповнення вікна розширеного фільтра представлено на рис. 38, де Исходный диапазон – адреса діапазону, що містить дані Таблиці 5 та ідентифікатори стовпців ($А$3:$F$48); Диапазон условий – таблиця критеріїв (діапазон $В$50:$В$51); Поместить результат в диапазон – адреса скопійованого рядка ідентифікаторів (діапазон $А$53:$F$53).
Рис. 38. Вигляд заповненого вікна розширеного фільтра результатів виконання фільтрації
Технологія виконання завдання із використанням функції ДСРЗНАЧ():
- для виконання цього завдання слід зробити копію Таблиці 5 на новому листі робочої книги;
- створити критерії відбору для розрахунку середнього значення за допомогою функції ДСРЗНАЧ(). У даному випадку критерієм є назва групи виробів Закуски і страви з овочів (поле NG) та дата виробництва 31.01.2012 (поле DATA). Таблицю критеріїв створюємо на вільній частині робочого листа так, як це показано на рис. 39;
- розраховуємо середнє значення за допомогою функції ДСРЗНАЧ(). Вікно конструктора функції з уведеними аргументами показано на рис. 40. Результати розрахунку розмістити поряд з таблицею критеріїв (рис. 39);
- на цьому ж аркуші створюємо обчислювальні критерії для застосування розширеного фільтра (рис. 39);
- вибрати команду Данные/Фильтр/Расширенный фильтр і від крити вікно розширеного фільтра. У поля діалогового вікна розши реного фільтра увести відповідні значення: Исходный диапазон – адреса діапазону, що містить дані Таблиці 5 та ідентифікатори стовпців ($А$3:$F$48); Диапазон условий – таблиця критеріїв (діапа зон $І$49:$І$50); Поместить результат в диапазон – адреса скопійо ваного рядка ідентифікаторів (діапазон $А$51:$F$51). Результати фільтрації відображено на рис. 41.
Рис. 39. Вигляд вікна програми з побудовою умов
Рис. 40. Заповнення діалогового вікна розширеного фільтра
Рис. 41. Результати застосування фільтра за обчислювальним критерієм
Таблиця критеріїв
Результат виконання розширеного фільтра
Для умови b
Для умови с
Для умови d
Рис. 33. Умова для розрахунку БДСУММ()
Критерій відбору
Копія рядка ідентифікаторів
Таблиця критеріїв для ДСРЗНАЧ()
Таблиця критеріїв для розширеного фільтру
Розрахунок ДСРЗНАЧ()