Лабораторна робота 12 - Табличний процесор MS Excel та Функції для роботи з базою даних, НУДПСУ

ЛАБОРАТОРНА РОБОТА №12

Тема: "Табличний процесор MS Excel. Робота з базою даних: сортування, фільтрація даних, проміжні підсумки. Зведені таблиці та зведені діаграми. Функції для роботи з базою даних"

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

Хід роботи

Запишіть тему і мету роботи. Складіть звіт виконання практичної роботи.

1. Створіть документ MS Excel з ім’ям Лаб_12.

2. Послідовно виконайте Завдання 1, Завдання 2, Завдання 3, Завдання 4, Завдання 5, переходячи до робочих аркушів книги Практ_29  з аналогічними іменами.

МЕТОДИЧНІ РЕКОМЕНДАЦІЇ

База даних MS Excel є таблицею, в якій:

перший  рядок містить імена полів (перелік атрибутів сутності);

 інші рядки є записами (екземплярами сутності), в стовпцях — значення полів (значення атрибутів).

Завдання 1.  Сортування записів.

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

1. активна комірка має знаходитись в межах бази даних;

2. меню Данные=>Сортировка;

3. у діалоговому вікні Сортировка диапазона (рис. 1) задайте ключі сортування і порядок сортування;

4. встановіть альтернативний перемикач в положення Идентифицировать поля по подписям і натисніть кнопку ОК.

Робочий аркуш

Завдання 1

Завдання 1. Створіть базу даних у відповідності з таблицею1 і виконайте сортування записів. Перший ключ – назва фірми, за зростанням; другий ключ – інвестиційні витрати – за спаданням; третій ключ – термін окупності – за спаданням. Збережіть внесені зміни.

Робочий аркуш Завдання 2

Завдання 2.  Фільтрація даних

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

1) Створіть автофільтр і відберіть записи з інвестиційними витратами більше 100 тис. грн. і терміном окупності більше півроку;

2) Створіть розширений фільтр, який відбирає записи бази даних з назвою фірми "Лідер", інвестиційні витрати яких не менші 100 тис. грн. і  термін окупності більше 1 року.

 

МЕТОДИЧНІ РЕКОМЕНДАЦІЇ

Фільтрація записів виконується тільки у відсортованій базі даних.

Створення автофільтру.

Потрібно виконати: .

1.активна комірка має знаходитись в межах бази даних;

2. меню Данные=>Фильтр =>Автофильтр;

3. за допомогою кнопок полів (Рис.2) Інвестиційні витрати та Термін окупності виберіть пункт Условие і у діалоговому вікні Пользовательский фильтр задайте умову відбору записів спочатку по полю Інвестиційні витрати (Рис.3), а потім по полю Термін окупності (Рис. 4); натисніть кнопку ОК у діалогових вікнах Пользовательский фильтр;

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

Зауваження. Для відображення всіх записів базиданих виконайте пункт2.

Створення розширеного фільтру

Потрібно виконати: .

1.створити критерій фільтрації у комірках за межами бази даних (Рис.5);

2. активна комірка має знаходитись в межах бази даних;

3. меню Данные=>Фильтр =>Расширенный фильтр;

4. у діалоговому вікні Расширенный фильтр (Рис.6) задати необхідні параметри (исходный диапазон – це діапазон комірок бази даних, диапазон условий – діапазон комірок критерія); потім натисніть кнопку ОК.

5. Перегляньте і проаналізуйте результат фільтрації.

6. Збережіть зміни у документі.

Робочий аркуш Завдання 3

Завдання 2. Скопіюйте відсортовану базу даних з робочого аркуша Завдання 1 на цей робочий аркуш.

Створіть проміжні і загальні підсумки з групуванням по полю Назва фірми і груповою операцієюпо полю Інвестиційні витрати з метою обчислення сумарних інвестиційних витрат кожної фірми.

МЕТОДИЧНІ РЕКОМЕНДАЦІЇ

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

Алгоритм створення проміжних і загальних підсумків потрібно виконати: .

1.активна комірка має знаходитись в межах бази даних;

2. меню Данные=>Итоги;

3. у діалоговому вікні Промежуточные итоги (Рис.7) вкажіть поле групування (Назва фірми), ім’я операції (Сумма) і за допомогою прапорця вкажіть поле, по якому буде виконуватись задана операція (Інвестиційні витрати) та місце розміщення проміжних підсумків (Итоги под данными); натисніть кнопку ОК

4. зверніть увагу на кнопку Убрать все діалогового вікна Промежуточные итоги :  виконавши пункт 2. і натиснувши цю кнопку можна видалити всі проміжні і загальні підсумки;

5. перегляньте результат: він має бути таким як на Рис.8; згорніть і розгорніть структур;

6. збережіть зміни у документі.

Робочий аркуш Завдання 4

Завдання 4. Скопіюйте на цей робочий аркуш відсортовану базу даних з робочого аркуша аркуша Завдання 1.

Потрібно розрахувати максимальну виручку від реалізації по кожній фірмі і визначити відсоткове відношення суми поля "Інвестиційні витрати" кожної фірми до загальних витрат по цьому полю. Створіть для цього прикладу  зведену таблицю і зведену діаграму.

МЕТОДИЧНІ РЕКОМЕНДАЦІЇ

1.Активна комірка – в межах бази даних.

2.Меню Данные -> Сводная таблица

3.У діалоговому вікні майстра зведених таблиць і діаграм  встановіть альтернативні перемикачі в положення як на рис.9 і клацніть на кнопці Далее.

4.У наступному вікні майстра зведених таблиць і діаграм (Рис.10 перевірте правильність задання діапазону бази даних (якщо не виконали крок 1, то задайте цей діапазон)  і клацніть на кнопці Далее.

5. У діалоговому вікні майстра зведених таблиць і діаграм (Рис.11) встановіть альтернативний перемикач в положення Существующий лист і у текстовому полі вкажіть адресу лівої  верхньої комірки (клацніть на цій комірці) зведеної таблиці як на рис. 12 і клацніть на кнопці Макет.

6.У діалоговому вікні майстра зведених таблиць і діаграм Макет (Рис.12) перетягніть поле Назва фірми на область макета Столбец, поля Інвестиційні витрати та Виручка від реалізації – область Данные.

7.Двічі клацніть на полі Інвестиційні витрати в области Данные у діалоговому вікні майстра Макет.

8.У діалоговому вікні Вычисление поля сводной таблицы (Рис.13) оберіть операцію Сумма і клацніть на кнопці Дополнительно.

9.У діалоговому вікні Вычисление поля сводной таблицы змініть назву поля,  оберіть із списку Дополнительно операцію Доля от суммы по строке (Рис.14) і клацніть на кнопці ОК у двох наступних діалогових  вікнах, а у діалоговому вікні як на Рис.14 на кнопці Готово.

10.На поточному робочому аркуші з’явиться зведена таблиця (Рис.15). Збережіть зміни у документі.

11.Проаналізуйте зведену таблицю і проведіть дослідження її  можливостей. Спробуйте перетягувати поля бази даних на область зведеної діаграми із вікна списку полів.

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

12.Побудуйте зведену діаграму, клацнувши на кнопці Діаграма панелі інструментів Сводные таблицы. Проведіть дослідження можливостей зведеної діаграми.

13.Збережіть зміни у документі.

Робочий аркуш Завдання 5

Завдання 5. Скопіюйте на цей робочий аркуш відсортовану базу даних з робочого аркуша аркуша Завдання 1.

Використовуючи функції баз даних, обчисліть:

1) для кожної фірми сумарні інвестиційні витрати з терміном окупності більше півроку і з виручкою від реалізації більшою 150 тис. грн..

2) назву фірми, інвестиційні витрати якої перевищують 130 тис. грн., термін окупності більше півроку, а поточні витрати максимальні.

МЕТОДИЧНІ РЕКОМЕНДАЦІЇ

Для роботи з базами даних використовується категорія функцій Работа с базой данных, яка нараховує 12 стандартних функцій. . Розглянемо деякі важливі  функції цієї категорії.

БДПРОИЗВЕД(база_данних;поле;критерий)

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

БДСУММ(база_данних;поле;критерий)

Підсумовує числа в стовпці списку або бази даних, які задовольняють заданим умовам.

БИЗВЛЕЧЬ(база_данних;поле;критерий)

Вивидобуває записи бази даних, які задовольняє заданим умовам.

Зауваження.

• Якщо жоден із записів не задовольняє критерію, то функція БИЗВЛЕЧЬ повертає значення помилки #ЗНАЧ!.

• Якщо більш ніж один запис задовольняє критерію, то функція БИЗВЛЕЧЬ повертає значення помилки #ЧИСЛО!.

БСЧЕТ(база_данних;поле;критерий)

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

Аргумент «поле» не є обов'язковим. Якщо аргумент «поле» опущений, то функція БСЧЕТ підраховує кількість записів в базі даних, що відповідають критеріям.

БСЧЕТА(база_данних;поле;критерий)

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

Аргумент «поле» не є обов'язковим. Якщо аргумент «поле» опущений, то функція БСЧЕТА підраховує кількість записів в базі даних, що відповідають критеріям

ДМАКС(база_данних;поле;критерий)

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

ДМИН(база_данних;поле;критерий)

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

ДСРЗНАЧ(база_данних;поле;критерий)

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

З’ясуємо зміст аргументів функцій.

База_данних — це діапазон комірок, що належать базі даних.

Поле -  визначає поле, що використовується функцією. Аргумент «поле» може бути заданий як текст з назвою стовпця в подвійних лапках, наприклад «Назва фірми» в наведеному  прикладі бази даних, або як число, що задає положення стовпця в списку: 1 — для першого поля, 2 — для другого поля і так далі.

Критерий — діапазон комірок, який містить умови, що задаються. Може містити обчислювальне поле. Будь-який діапазон, який містить принаймні одну назву стовпця і принаймні одну комірку під назвою стовпця з умовою, може бути використаний як аргумент.

Крім цих функцій при роботі з базою даних можуть застосовуватись  також функції деяких інших категорій, наприклад функції СУММЕСЛИ та СЧЕТЕСЛИ.

Алгоритм розв’язання задачі 1.

1.Скопіюйте відсортовану базу даних з робочого аркуша Завдання 1 на цей аркуш і розмістіть, наприклад, в діапазоні A1:F21.

2.Далі створіть критерії відбору записів (Рис.14.14), для яких буде застосовуватись означена у назві функції операція.

3.Потім по черзі виділіть комірки, які будуть зберігати обчислені значення і введіть в них функцію БДСУММ(база_даних; поле; критерій)  ), послуговуючись майстром функцій (Рис.14.15).

4.Проаналізуйте отримані результати.

5.Збережіть внесені у документ зміни.

Алгоритм розв’язання задачі 2.

1.Створіть критерії відбору записів з обчислювальним полем Максимальний податок (Рис.14.16) у комірці N17.

2.У комірку G26 введіть функцію =МАКС($D$2:$D$19).

3.У комірку D26 введіть формулу =$G$26.

4.Виділіть комірку F28 і введіть  функцію =БИЗВЛЕЧЬ(A1:F21;A1;A25:G26)(Рис.14.16), послуговуючись майстром функцій.

5. Перегляньте і проаналізуйте результат: він має бути таким як на Рис.14.17.

6.Збережіть зміни у документі.