Лабораторна (практична) робота 

Тема: Робота з даними в ТП MS Excel. Створення зведеної таблиці та діаграми.  Мета роботи: Набути практичні навики роботи по створенню бази даних, сортуванню, фільтрації та аналізу даних таблиці. Одержати навички побудови зведених таблиць та зведених діаграм засобами ТП MS Excel. 

Обладнання:зошит, ПК.

Робоче місце: ПК №____. 

$ Завдання:

1.           Створіть таблицю за зразком.

2.           Проведіть необхідні обчислення в таблиці та фільтрування й сортування записів.

3.           Побудуйте зведену таблицю та зведену діаграму за заданими умовами. 

4.           Ознайомтеся з поняттям консолідації даних.

Виконання роботи

1.     Створіть в папці зі своїм прізвищем папку Excel. 

2.     Завантажте табличний процесор MS Excel. 

3.     Створіть на першому аркуші робочої книги таблицю 1 за зразком.

Таблиця 1

4.     На цьому ж аркуші, відступивши стовпчик вправо, створіть таблицю 2 наступного змісту

Таблиця 2

5.     Виділіть стовпець «Група товарів», встановіть «перевірку даних» та тип даних «список». Як джерело даних вкажіть групи товарів з першої таблиці.

6.     Виділіть стовпець «Одиниця виміру», встановіть «перевірку даних» та тип даних «список». Як джерело даних вкажіть: шт.;кг.;пакет. При введенні встановіть як підказку повідомлення «Оберіть одиницю виміру із списку», а як помилку вводу «Оберіть одиницю виміру із наданого списку».

7.     В клітинці J1 встановіть сьогоднішню дату за допомогою функції Сьогодні().

8.     Для стовпчиків «Ціна за одиницю, грн.», «Відпускна ціна за одиницю, грн.» та «Сума куплених товарів, грн.» встановіть числовий формат даних з двома десятковими знаками, крім діапазону клітинок H1:J2. 

9.     Заповніть таблицю 2 даними згідно зразка.

Зразок (Таблиця 2)

10.Задайте колір клітинок та тип ліній таблиці самостійно.

11.Аркуш перейменуйте в Закупівля товарів.

12.Виконайте розрахунки в таблиці за формулами:

 Відпускна ціна за одиницю, грн. = Ціна за одиницю, грн. – Ціна за одиницю, грн. * Знижку(15%).

Примітка: Якщо ви хочете скопіювати введену формулу або використати засіб автозаповнення, то потрібно при посиланні на комірку зі знижкою встановити абсолютну адресу комірки (клавіша F4), тобто формула в клітинці І3 повинна мати вигляд:  =H3-H3*$H$1  Сума куплених товарів, грн. = Ціна за одиницю, грн. * Кількість.

13.Сформуйте скорочений запис Інформації по чеку, який буде складатись з таких позицій:  символи від Групи товарів _ 4 символи від Особливих характеристик _ 4 символи від Найменування _ Ціна за одиницю * Кількість = Сума

Наприклад:   Хліб_бато_Гірч_20,30*22=350,28

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

Примітка: Для комірки К3 формула повинна мати вигляд: 

=ЛЕВСИМВ(C3;4)&"_"&ЛЕВСИМВ(E3;4)&"_"&ЛЕВСИМВ(D3;4)&"_"&H3&"*"&G3&"="&

ТЕКСТ(J3; "0,00")

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

Для цього потрібно виділити таблицю без першого рядка, відкрити меню Дані, вибрати команду Сортування, вказати Сортувати по групі товарів, порядок від А до Я.

15.За допомогою команди Фільтр виведіть інформацію про всі товари:

a.      групи кондитерські, які продаються дорожче 125 грн. Скопіюйте одержані

дані на новий аркуш;

Для цього потрібно виділити таблицю без першого рядка, відкрити меню Дані, вибрати команду Фільтр, натиснути на трикутничок біля назви стовпця «Група товарів» зняти усе виділення крім Кондитерські, натиснути на трикутничок біля назви стовпця «Ціна за одиницю, грн.» вибрати Числові фільтри/більше  125.

b.     групи м’ясні, яких у заданому магазині було продано на суму більше

15000грн. Скопіюйте одержані дані на новий аркуш;

c.      цукерки, кількість яких менша 15 кг.

16.Створіть аркуш «Зведені дані» на якому побудуйте зведену таблицю по групах товарів, з найменуванням та характеристиками товарів та підбитими підсумками по полях «Кількість» і «Сума». 

При побудові таблиці у рядки перетягніть поля: «Група товарів», «Особливі характеристики товарів» і «Найменування товарів», у значення зведеної таблиці перетягніть поля «Кількість» і «Сума куплених товарів, грн.». Зверніть  увагу, що зведена таблиця повинна відповідати зразку.

Зразок зведеної таблиці

17.Скопіюйте аркуш зі зведеною таблицею. Перейменуйте його в «Дані для діаграми». Приберіть з таблиці поля «Особливі характеристики товарів», дає можливість порівняти суми куплених товарів, грн. по групах товарів. Діаграма має розміщуватись на окремому аркуші «Витрачено грн.», кожна доля діаграми повинна мати підпис у відсотковому значенні. 

18.Зробіть копію аркуша з діаграмою. Перейменуйте отриманий аркуш в Експеримент. Відформатуйте окремі елементи діаграми. 

19.Збережіть свою роботу. Завершіть роботу з табличним процесором.

20.Напишіть висновок по роботі. Дайте відповіді на контрольні питання: ü Що таке електронна таблиця?

üЯких правил потрібно дотримуватись, щоб робочий аркуш виглядав як база даних?

üЯк відсортувати дані одного поля списку?

üДля чого при роботі зі списками застосовуються фільтри?

üЩо таке зведена таблиця?

üЯкі дії потрібно виконати для створення зведеної таблиці?

üЯк побудувати зведену діаграму?