Збірник_Excel
.pdf
Рис. 16.1. Таблиці з даними про використані будівельні матеріали.
4.На робочому листі Лист5 створити таблицю, в якій відобразити дані про загальну кількість використаних будівельних матеріалів на всіх об’єктах.
Вказівки до виконання:
yвиділити комірку А2 на робочому листі Лист5;
yвиконати команду Данные→Консолидация…;
yу полі Функция діалогового вікна Консолидация вибрати елемент
Сумма;
yу полі Ссылка діалогового вікна Консолидация вказати адресу діапазону комірок з робочого листа Об’єкт1, який містить поля Назва будівельного матеріалу і Кількість;
yнатиснути кнопку Добавить для включення вказаного діапазону у діапазон консолідації;
yаналогічно додати до діапазону консолідації дані з робочих листів
Об’єкт2-Об’єкт4.
yвстановити параметри Подписи верхней строки, Значение левого столбца;
yнатиснути конку Ок для здійснення консолідації даних.
51
Рис. 16.2. Діалогове вікно Консолидация з визначеними параметрами.
5.Змінити назву робочого листа Лист5 на Звіт.
6.До таблиці на робочому листі Звіт додати поле Ціна за одиницю. Заповнити створене поле, використовуючи дані таблиці наведеній на рис 16.3.
Назва будівельного |
Ціна за |
|
матеріалу |
одиницю |
|
Цемент М-300 |
115,5 |
грн. |
Цемент М-500 |
135,6 |
грн. |
Вапно |
70,0 |
грн. |
Пісок |
65,4 |
грн. |
Будівельна суміш №1 |
200,0 |
грн. |
Шпаклівка |
40,3 |
грн. |
Клей для плитки |
25,0 |
грн. |
Вирівнювач підлоги |
35,5 |
грн. |
Плитка |
65,0 |
грн. |
Фарба водоемульсійна |
31,0 |
грн. |
Фарба олійна |
61,7 |
грн. |
Будівельна суміш №2 |
214,0 |
грн. |
Суміш для затирання швів |
25,0 |
грн. |
Плитка для підлоги |
48,4 |
грн. |
Рис. 16.3. Таблиця з даними про ціни будівельних матеріалів.
7.Обчислити загальну вартість використаних будівельних матеріалів.
8.Роздрукувати дані робочого листа Звіт.
Контрольні питання
1.Способи зв’язування комірок і діапазонів.
2.Зміна і видалення зв’язків.
3.Зв’язані зображення комірок. Створення зв’язаного зображення комірок.
4.Зміна зв’язку з зображенням.
5.Основні поняття консолідації даних.
52
Лабораторна робота №17.
Тема: Консолідація даних.
Мета: Закріпити навички консолідації даних в MS Excel.
Хід роботи:
1.Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2.Зберегти створений документ під іменем ЛР17_Прізвище.xls.
3.На робочих листах Лист1-Лист4 створити таблиці з даними про вартість
послуг відпочинку туристичною фірмою "Круїз-2000", наведені на рис. 17.1.-17.4.
Рис. 17.1. Таблиця з даними про вартість відпочинку у 2-місному номері.
Рис. 17.2. Таблиця з даними про вартість відпочинку у 2-місному номері покращеного планування.
Рис. 17.3. Таблиця з даними про вартість відпочинку у 3-місному номері.
53
Рис. 17.4. Таблиця з даними про вартість відпочинку у 3-місному номері покращеного планування.
4.Змінити назви робочих листів Лист1-Лист4 відповідно 2-х, 2-х(покращ),
3-х, 3-х(покращ).
5.На робочому листі Лист5 створити таблицю, в якій відобразити дані про середню вартість відпочинку, в залежності від дати.
Вказівки до виконання:
yвиділити комірку А2 на робочому листі Лист5;
yвиконати команду Данные→Консолидация…;
yу полі Функция діалогового вікна Консолидация вибрати елемент
Среднее;
yу полі Ссылка діалогового вікна Консолидация вказати адресу діапазону комірок з робочого листа 2-х, який містить поля Пансіонат, 01.06-30.06, 01.07-31.07, 01.08-31.08;
yнатиснути кнопку Добавить для включення вказаного діапазону у діапазон консолідації;
yаналогічно додати до діапазону консолідації дані з робочих листів 2-х (покращ), 3-х, 3-х(покращ).
yвстановити параметри Подписи верхней строки, Значение левого столбца, Создавать звязи с исходными данными;
yнатиснути конку Ок для здійснення консолідації даних.
Рис. 17.5. Діалогове вікно Консолидация з визначеними параметрами.
6.Змінити назву робочого листа Лист5 на Зведений.
7.Відформатувати створену таблицю відповідно до поданого на рис. 17.6. зразка.
54
Рис. 17.6. Консолідовані дані на робочому листі Зведений.
10.Відобразити детальні дані використовуючи елементи управління структурою робочого листа.
11.Змінити дані комірок поля Номер відповідно до таблиці на рис. 17.7.
Рис. 17.7. Таблиця консолідованих даних з відображеними детальними даними.
Вказівки до виконання:
yпри заповненні комірок даними використати можливість копіювання даних або вибору даних зі списку, що розкривається.
12.Використовуючи маркер автозаповнення заповнити комірки поля Пансіонат відповідними даними.
13.На робочому листі Зведений відобразити дані про вартість 2-місних
55
номерів. Порівняти отриманий результат з даними, що містяться на робочому листі 2-х.
14.На основі даних робочого листа 2-х побудувати нестандартну діаграму (гістограма/області). Змінити тип діаграми для ряду даних 01.07-31.07.
Додати заголовок "Вартість відпочинку (2-місні номери)". Розмістити діаграму на робочому листі 2-х.
Рис. 17.8. Діаграма " Вартість відпочинку (2-місні номери)". 15. Створити зв’язне зображення діапазону комірок поля 01.08-31.08.
Вказівки до виконання:
yвиділити діапазон комірок поля 01.08-31.08;
yвиконати команду Правка→Копировать;
yнатиснути клавішу Shift, утримувати її і відкрити пункт меню Правка;
yвиконати команду Вставить связь с рисунком відкритого меню.
16.Перенести створений рисунок на діаграму.
17.Згрупувати діаграму і зв’язане зображення діапазону (рис. 17.8.).
Рис. 17.7. Побудована діаграма і зв’язане зображення діапазону.
56
18. Роздрукувати дані робочого листа Зведений і побудовану діаграму.
Контрольні питання
1.Методи консолідації даних в MS Excel.
2.Параметри діалогового вікна Консолидация. Призначення параметрів цього діалогового вікна.
3.Робота з панеллю інструментів Рисование.
4.Налаштування параметрів зображення.
Лабораторна робота №18.
Тема: Аналіз даних з допомогою зведених таблиць.
Мета: Закріпити навички створення та впорядкування списків. Набути навички створення і редагування зведених таблиць.
Хід роботи:
1.Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2.Зберегти створений документ під іменем ЛР18_Прізвище.xls.
3.На робочому листі Лист1 створити таблицю з даними про наявність компакт-дисків на прокатних пунктах, наведену на рис. 18.1. Назвати робочих лист зі створеною таблицею, Список.
Код |
|
Назва |
|
|
Жанр |
|
Рік випуску |
|
Прокатний |
|
|
Ціна |
|
Кількіс ть CD |
|
|
|
|
|
пункт |
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
26 |
|
Мауглі |
|
Мультфільм |
2002 |
|
Азбука |
|
14,00 грн. |
|
3 |
|||
26 |
|
Мауглі |
|
Мультфільм |
2002 |
|
Аудіо-відео |
|
14,00 грн. |
|
20 |
|||
26 |
|
Мауглі |
|
Мультфільм |
2002 |
|
Буковина |
|
14,00 грн. |
|
13 |
|||
26 |
|
Мауглі |
|
Мультфільм |
2002 |
|
Відео плюс |
|
14,00 грн. |
|
35 |
|||
26 |
|
Мауглі |
|
Мультфільм |
2002 |
|
Динамо |
|
14,00 грн. |
|
25 |
|||
24 |
|
Шрек 2 |
|
Мультфільм |
2003 |
|
Азбука |
|
18,00 грн. |
|
38 |
|||
24 |
|
Шрек 2 |
|
Мультфільм |
2003 |
|
Аудіо-відео |
|
18,00 грн. |
|
46 |
|||
24 |
|
Шрек 2 |
|
Мультфільм |
2003 |
|
Буковина |
|
18,00 грн. |
|
6 |
|||
24 |
|
Шрек 2 |
|
Мультфільм |
2003 |
|
Відео плюс |
|
18,00 грн. |
|
49 |
|||
24 |
|
Шрек 2 |
|
Мультфільм |
2003 |
|
Динамо |
|
18,00 грн. |
|
31 |
|||
5 |
|
Острів скарбів |
|
Мультфільм |
2001 |
|
Азбука |
|
21,00 грн. |
|
11 |
|||
5 |
|
Острів скарбів |
|
Мультфільм |
2001 |
|
Аудіо-відео |
|
21,00 грн. |
|
36 |
|||
5 |
|
Острів скарбів |
|
Мультфільм |
2001 |
|
Буковина |
|
21,00 грн. |
|
5 |
|||
5 |
|
Острів скарбів |
|
Мультфільм |
2001 |
|
Відео плюс |
|
21,00 грн. |
|
39 |
|||
5 |
|
Острів скарбів |
|
Мультфільм |
2001 |
|
Динамо |
|
21,00 грн. |
|
46 |
|||
18 |
|
Гарі Поттер 4 |
|
Дит. фільм |
2005 |
|
Азбука |
|
22,00 грн. |
|
50 |
|||
18 |
|
Гарі Поттер 4 |
|
Дит. фільм |
2005 |
|
Аудіо-відео |
|
22,00 грн. |
|
6 |
|||
57
18 |
Гарі Поттер 4 |
Дит. фільм |
2005 |
Буковина |
22,00 грн. |
36 |
18 |
Гарі Поттер 4 |
Дит. фільм |
2005 |
Відео плюс |
22,00 грн. |
20 |
18 |
Гарі Поттер 4 |
Дит. фільм |
2005 |
Динамо |
22,00 грн. |
16 |
10 |
Такси 3 |
Комедія |
2003 |
Азбука |
26,00 грн. |
26 |
10 |
Такси 3 |
Комедія |
2003 |
Аудіо-відео |
26,00 грн. |
7 |
10 |
Такси 3 |
Комедія |
2003 |
Буковина |
26,00 грн. |
42 |
10 |
Такси 3 |
Комедія |
2003 |
Відео плюс |
26,00 грн. |
18 |
10 |
Такси 3 |
Комедія |
2003 |
Динамо |
26,00 грн. |
42 |
20 |
Великі неприємності |
Комедія |
1999 |
Азбука |
18,00 грн. |
28 |
20 |
Великі неприємності |
Комедія |
1999 |
Аудіо-відео |
18,00 грн. |
8 |
20 |
Великі неприємності |
Комедія |
1999 |
Буковина |
18,00 грн. |
7 |
20 |
Великі неприємності |
Комедія |
1999 |
Відео плюс |
18,00 грн. |
33 |
20 |
Великі неприємності |
Комедія |
1999 |
Динамо |
18,00 грн. |
16 |
22 |
Доктор Дуллітл 2 |
Комедія |
2000 |
Азбука |
11,00 грн. |
21 |
22 |
Доктор Дуллітл 2 |
Комедія |
2000 |
Аудіо-відео |
11,00 грн. |
12 |
22 |
Доктор Дуллітл 2 |
Комедія |
2000 |
Буковина |
11,00 грн. |
39 |
22 |
Доктор Дуллітл 2 |
Комедія |
2000 |
Відео плюс |
11,00 грн. |
35 |
22 |
Доктор Дуллітл 2 |
Комедія |
2000 |
Динамо |
11,00 грн. |
10 |
14 |
Дублери |
Комедія |
2002 |
Азбука |
29,00 грн. |
17 |
14 |
Дублери |
Комедія |
2002 |
Аудіо-відео |
29,00 грн. |
39 |
14 |
Дублери |
Комедія |
2002 |
Буковина |
29,00 грн. |
26 |
14 |
Дублери |
Комедія |
2002 |
Відео плюс |
29,00 грн. |
18 |
14 |
Дублери |
Комедія |
2002 |
Динамо |
29,00 грн. |
26 |
27 |
Спасти від нареченої |
Комедія |
2001 |
Азбука |
18,00 грн. |
36 |
27 |
Спасти від нареченої |
Комедія |
2001 |
Аудіо-відео |
18,00 грн. |
47 |
27 |
Спасти від нареченої |
Комедія |
2001 |
Буковина |
18,00 грн. |
17 |
27 |
Спасти від нареченої |
Комедія |
2001 |
Відео плюс |
18,00 грн. |
35 |
27 |
Спасти від нареченої |
Комедія |
2001 |
Динамо |
18,00 грн. |
43 |
13 |
Вассабі |
Комедія |
2001 |
Азбука |
21,00 грн. |
9 |
13 |
Вассабі |
Комедія |
2001 |
Буковина |
21,00 грн. |
50 |
13 |
Вассабі |
Комедія |
2001 |
Відео плюс |
21,00 грн. |
8 |
12 |
Осінь в Нью-Йорку |
Мелодрама |
2000 |
Азбука |
15,00 грн. |
5 |
12 |
Осінь в Нью-Йорку |
Мелодрама |
2000 |
Аудіо-відео |
15,00 грн. |
18 |
12 |
Осінь в Нью-Йорку |
Мелодрама |
2000 |
Буковина |
15,00 грн. |
38 |
12 |
Осінь в Нью-Йорку |
Мелодрама |
2000 |
Відео плюс |
15,00 грн. |
6 |
12 |
Осінь в Нью-Йорку |
Мелодрама |
2000 |
Динамо |
15,00 грн. |
18 |
15 |
Солодкий листопад |
Мелодрама |
2000 |
Азбука |
14,00 грн. |
6 |
15 |
Солодкий листопад |
Мелодрама |
2000 |
Аудіо-відео |
14,00 грн. |
37 |
15 |
Солодкий листопад |
Мелодрама |
2000 |
Буковина |
14,00 грн. |
21 |
15 |
Солодкий листопад |
Мелодрама |
2000 |
Відео плюс |
14,00 грн. |
27 |
15 |
Солодкий листопад |
Мелодрама |
2000 |
Динамо |
14,00 грн. |
24 |
8 |
Там, де тече ріка |
Мелодрама |
2001 |
Азбука |
28,00 грн. |
42 |
8 |
Там, де тече ріка |
Мелодрама |
2001 |
Аудіо-відео |
28,00 грн. |
46 |
8 |
Там, де тече ріка |
Мелодрама |
2001 |
Буковина |
28,00 грн. |
6 |
8 |
Там, де тече ріка |
Мелодрама |
2001 |
Відео плюс |
28,00 грн. |
12 |
8 |
Там, де тече ріка |
Мелодрама |
2001 |
Динамо |
28,00 грн. |
13 |
58
3 |
Епоха |
Фентезі |
2001 |
Азбука |
27,00 грн. |
13 |
|
3 |
Епоха |
Фентезі |
2001 |
Аудіо-відео |
27,00 грн. |
9 |
|
3 |
Епоха |
Фентезі |
2001 |
Буковина |
27,00 грн. |
28 |
|
3 |
Епоха |
Фентезі |
2001 |
Відео плюс |
27,00 грн. |
40 |
|
3 |
Епоха |
Фентезі |
2001 |
Динамо |
27,00 грн. |
19 |
|
4 |
Планета К-Пекс |
Фентезі |
1999 |
Азбука |
25,00 грн. |
19 |
|
4 |
Планета К-Пекс |
Фентезі |
1999 |
Аудіо-відео |
25,00 грн. |
34 |
|
4 |
Планета К-Пекс |
Фентезі |
1999 |
Буковина |
25,00 грн. |
20 |
|
4 |
Планета К-Пекс |
Фентезі |
1999 |
Відео плюс |
25,00 грн. |
32 |
|
4 |
Планета К-Пекс |
Фентезі |
1999 |
Динамо |
25,00 грн. |
10 |
|
18 |
Хроніки Ріддіка |
Фентезі |
1999 |
Азбука |
22,00 грн. |
44 |
|
18 |
Хроніки Ріддіка |
Фентезі |
1999 |
Аудіо-відео |
22,00 грн. |
42 |
|
18 |
Хроніки Ріддіка |
Фентезі |
1999 |
Буковина |
22,00 грн. |
11 |
|
18 |
Хроніки Ріддіка |
Фентезі |
1999 |
Відео плюс |
22,00 грн. |
40 |
|
18 |
Хроніки Ріддіка |
Фентезі |
1999 |
Динамо |
22,00 грн. |
35 |
|
11 |
Гаттака |
Наукова |
2002 |
Азбука |
15,00 грн. |
20 |
|
фантастика |
|||||||
|
|
||||||
11 |
Гаттака |
Наукова |
2002 |
Аудіо-відео |
15,00 грн. |
31 |
|
фантастика |
|||||||
|
|
||||||
11 |
Гаттака |
Наукова |
2002 |
Буковина |
15,00 грн. |
44 |
|
фантастика |
|||||||
|
|
||||||
11 |
Гаттака |
Наукова |
2002 |
Відео плюс |
15,00 грн. |
11 |
|
фантастика |
|||||||
|
|
||||||
11 |
Гаттака |
Наукова |
2002 |
Динамо |
15,00 грн. |
43 |
|
фантастика |
|||||||
|
|
||||||
6 |
Хижак 2 |
Наукова |
2000 |
Азбука |
12,00 грн. |
6 |
|
фантастика |
|||||||
|
|
||||||
6 |
Хижак 2 |
Наукова |
2000 |
Аудіо-відео |
12,00 грн. |
26 |
|
фантастика |
|||||||
|
|
||||||
6 |
Хижак 2 |
Наукова |
2000 |
Буковина |
12,00 грн. |
9 |
|
фантастика |
|||||||
|
|
||||||
6 |
Хижак 2 |
Наукова |
2000 |
Відео плюс |
12,00 грн. |
29 |
|
фантастика |
|||||||
|
|
||||||
6 |
Хижак 2 |
Наукова |
2000 |
Динамо |
12,00 грн. |
26 |
|
фантастика |
|||||||
|
|
||||||
9 |
Від колиски до могили |
Бойовик |
2002 |
Азбука |
17,00 грн. |
39 |
|
9 |
Від колиски до могили |
Бойовик |
2002 |
Аудіо-відео |
17,00 грн. |
8 |
|
9 |
Від колиски до могили |
Бойовик |
2002 |
Буковина |
17,00 грн. |
3 |
|
9 |
Від колиски до могили |
Бойовик |
2002 |
Відео плюс |
17,00 грн. |
34 |
|
9 |
Від колиски до могили |
Бойовик |
2002 |
Динамо |
17,00 грн. |
18 |
|
17 |
Хел Бой |
Фільм жахів |
1999 |
Азбука |
30,00 грн. |
7 |
|
17 |
Хел Бой |
Фільм жахів |
1999 |
Аудіо-відео |
30,00 грн. |
24 |
|
17 |
Хел Бой |
Фільм жахів |
1999 |
Буковина |
30,00 грн. |
19 |
|
17 |
Хел Бой |
Фільм жахів |
1999 |
Відео плюс |
30,00 грн. |
14 |
|
17 |
Хел Бой |
Фільм жахів |
1999 |
Динамо |
30,00 грн. |
24 |
Рис. 18.1. Таблиця з даними про наявність компакт-дисків.
59
4.Додати до таблиці нове поле Вартість. Обчислити вартість наявних компакт-дисків.
5.На основі створеного списку побудувати зведену таблицю, в якій в області рядків розмістити поле Жанр, в області стовпців – поле Рік випуску, в області даних – поле Кількість CD, в області сторінок – поле Прокатний пункт. Визначити суму, як операцію в області даних. Розмістити зведену таблицю на новому робочому листі, назвати цей робочий лист Табл1.
Вказівки до виконання:
yвиділити будь-яку комірку в межах створеного списку;
yвиконати команду Данные→Сводная таблица… для початку роботи з майстром зведених таблиць;
yу першому діалоговому вікні майстра Шаг 1 из 3 встановити значення параметрів: Создать таблицу на основе данных, находящихся – в списке или базе данных MS Office Exce, Вид создаваемого отчета
– сводная таблица;
yна другому кроці майстра Шаг 2 из 3 прийняти запропонований виділений діапазон;
yна третьому кроці майстра Шаг 3 из 3 встановити параметр Поместить таблицу – новый лист, натиснути кнопку Макет… для визначення структури зведеної таблиці;
yу діалоговому вікні Мастер сводных таблиц и диаграм – макет
розмістити поля списку у відповідних розділах зведеної таблиці;
Рис. 18.2. Діалогове вікно Мастер сводных таблиц и диаграмм - макет.
yзакрити діалогове вікно Мастер сводных таблиц и диаграм – макет,
натиснувши кнопку Ок;
yнатиснути кнопку Готово для завершення роботи з майстром зведених таблиць.
6.Змінити назву поля області даних назвавши його Загальна кількість,
назву Общий итог на Всього.
Вказівки до виконання:
yвиділити комірку Сумма по полю Кількість СD;
60