Збірник_Excel
.pdf
№10.
3.Побудувати об’ємний графік, на якому порівняти плановий та фактичний обсяг закупівлі товарів у 2003 році (рис. 11.1.). Додати до діаграми заголовок "Порівняльний аналіз обсягів планової та фактичної закупівлі товарів у 2003 році". Розмістити діаграму на робочому листі Лист3. Змінити назву робочого листа Лист3 на Нові_Діаграми.
3.1.Заливку стінок діаграми зробити градієнтною, тип штрихування – вертикальний.
3.2.Змінити кольори відображення рядів даних за власним бажанням.
3.3.Заголовок відобразити курсивом, розміром у 8 пт.
Рис. 11.1. Діаграма "Порівняльний аналіз обсягів планової та фактичної закупівлі товарів у 2003 році".
4.Побудувати діаграму у вигляді блоків з областями, на якій відобразити плановий та фактичний обсяг закупівлі товарів у 2004 році (рис. 11.2.).
Додати до діаграми заголовок "Порівняльний аналіз обсягів планової та фактичної закупівлі товарів у 2004 році". Розмістити діаграму на робочому листі Нові_Діаграми.
4.1.Відобразити заголовок діаграми у рамці, курсивом, розміром у 9 пт.
4.2.Змінити формат об’ємної проекції.
Вказівки до виконання:
yвиконати команду Объемный вид контекстного меню області побудови діаграми.
4.3.Змінити заливку рядів даних.
4.4.Відобразити легенду діаграми у рамці.
31
Рис. 11.2. Діаграма "Порівняльний аналіз обсягів планової та фактичної закупівлі товарів у 2004 році".
5.Побудувати вторинну кругову діаграму, з частиною даних винесених у іншу кругову діаграму, на якій відобразити асортиментну структуру прибутку у 2003 році (рис. 11.3.). Додати до діаграми заголовок
"Асортиментна структура прибутку у 2003 році". Розмістити діаграму на робочому листі Нові_Діаграми.
5.1.Відобразити заголовок діаграми напівжирним курсивом, розміром у
11 пт.
5.2.У другу кругову діаграму винести точки даних, значення яких менше 4.
Рис. 11.3. Діаграма "Асортиментна структура прибутку у 2003 році".
6.Побудувати вторинну кругову діаграму, з частиною даних винесених у гістограму, на якій відобразити асортиментну структуру прибутку у 2004 році (рис. 11.4.). Додати до діаграми заголовок "Асортиментна структура прибутку у 2004 році". Розмістити діаграму на робочому листі Нові_Діаграми.
6.1.Відобразити заголовок діаграми курсивом, розміром у 11 пт.
32
6.2.Винести у гістограму дані про категорії "м’ясо-ковбасні вироби", "кондитерські вироби", "консервовані вироби".
6.3.Відформатувати діаграму згідно зразка (рис 11.4.)
Рис. 11.4. Діаграма "Асортиментна структура прибутку у 2004 році".
7.Побудувати нормовану гістограму, на якій відобразити питому вагу груп товарів у загальному товарообігу за 2003-2004 рр. (рис. 11.5.). Додати до діаграми заголовок "Відображення долі кожної групи товарів у загальному товарообігу". Вивести на діаграмі проміжні лінії осі значень. Розмістити діаграму на робочому листі Нові_Діаграми.
7.1.Відобразити заголовок діаграми курсивом, розміром у 11 пт.
7.2.Встановити ціну проміжних поділів – 5.
7.3.Змінити тип проміжних ліній сітки.
Рис. 11.5. Діаграма "Відображення долі кожної групи товарів у загальному товарообігу".
8.Створити діаграму, на якій відобразити плановий та фактичний обсяги закупівлі товарів та прибуток у 2003 році. (рис. 11.6.).
Вказівки до виконання:
yскопіювати діаграму „Порівняльний аналіз обсягів планової та фактичної закупівлі товарів у 2003 році”;
yзмінити тип діаграми на звичайну гістограму;
33
yдодати новий ряд даних „Прибуток (тис. грн.)”;
yзмінити тип нового ряду даних на графік з маркерами. Довільно вибрати тип та колір маркеру, а також колір лінії;
yзмінити заголовок діаграми на "Порівняльний аналіз обсягів планової
і фактичної закупівлі товарів та прибутку у 2003 році";
yрозмістити діаграму на робочому листі Нові_діаграми.
Рис. 11.5. Діаграма "Відображення долі кожної групи товарів у загальному товарообігу".
9.Роздрукувати створені діаграми.
Контрольні питання
1.Зміна кольору, виду і границь об’єктів діаграми.
2.Виділення рядів даних та окремих точок даних.
3.Форматування рядів даних.
4.Налаштування осей. Вибір шкал осей.
5.Форматування тривимірних діаграм.
6.Форматування текстових елементів діаграм.
Лабораторна робота №12.
Тема: Робота зі списками в MS Excel. Сортування даних та обчислення проміжних підсумків.
Мета: Закріпити навички створення та впорядкування списків. Набути навички обчислення простих та складних проміжних підсумків, створення структури робочого листа.
Хід роботи:
1.Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2.Зберегти створений документ під іменем ЛР12_Прізвище.xls.
3.На робочому листі Лист1 створити таблицю "Перелік товарів на складі
34
фірми "Веселка", наведену на рис. 12.1. Змінити назву робочого листа Лист1 на Склад. Для заповнення комірок поля Фасування створити власний формат даних.
Вказівки до виконання:
yвиділити діапазон комірок поля Фасування;
yвиконати команду Формат→Ячейки…, активізувати вкладку Число, зі списку Числовые форматы вибрати елемент Все форматы;
yу полі Тип ввести маску 0,0_\л.
4.Використовуючи функції СЛЧИС() і ОКРУГЛ() заповнити даними комірки поля Залишок по складу так, щоб значення знаходились в межах від 100 до 1000. Замінити формули на її результат, інакше значення у цьому стовпці будуть постійно змінюватися.
Вказівки до виконання:
для заміни формули її значенням необхідно:
yвиділити діапазон комірок з формулами;
yскопіювати виділений діапазон у буфер обміну виконавши відповідну команду контекстного меню виділеного діапазону;
yвиконати команду Правка→Специальная вставка
yвставити інформацію з буферу на теж саме місце, вказавши параметр вставки – Значения.
5.Обчислити вартість залишків всіх товарів на складі.
Рис. 12.1. Таблиця "Перелік товарів на складі фірми "Веселка ".
35
6.Скопіювати створений список на робочі листи Лист2-Лист4.
7.На робочому листі Склад впорядкувати список за полем Ціна за одиницю у порядку зменшення значень.
8.Роздрукувати впорядкований список.
9.На робочому листі Склад впорядкувати список за складним ключем: за полями Виробник, Група – у алфавітному порядку, за полями Фасування, Вартість – у порядку зменшення значень. Роздрукувати впорядкований список.
Вказівки до виконання:
yвиділити діапазон комірок списку;
yвиконати команду Данные→Сортировка...;
yу діалоговому вікні Сортировка диапазона встановити необхідні параметри для впорядкування списку за полями Фасування і Вартість;
Рис. 12.2. Діалогове вікно Сортировка диапазона.
y натиснути кнопку Ок для завершення виконання команди
Данные→Сортировка...;
yвиділити будь-яку комірку в межах списку;
yвиконати команду Данные→Сортировка...;
yу діалоговому вікні Сортировка диапазона встановити необхідні параметри для впорядкування списку за полями Виробник і Група;
yнатиснути кнопку Ок для завершення виконання команди
Данные→Сортировка...
10.Перейменувати робочий лист Лист2 на Загальна_вартість.
11.На робочому листі Загальна_вартість обчислити загальну вартість залишків товарів на складі кожного виробника.
Вказівки до виконання:
yвпорядкувати список за значеннями поля Виробник;
yвиконати команду Данные→Итоги...;
yу діалоговому вікні Промежуточные итоги встановити необхідні параметри для обчислення загальної вартості товарів кожного
36
виробника.
Рис. 12.3. Діалогове вікно Промежуточные итоги для обчислення загальної вартості залишків товарів.
12.За отриманими підсумковими значеннями у п.11 побудувати кругову діаграму, на якій відобразити долю кожного виробника у загальному залишку товару на складі. Додати до діаграми назву "Порівняльний аналіз обсягів товарів різних виробників на складі фірми "Веселка",
підписи даних – долі. Розмістити діаграму на робочому листі
Загальна_вартість.
Вказівки до виконання:
yна робочому листі Загальна_вартість відобразити обчислені проміжні підсумки використовуючи відповідні елементи управління структурою робочого листа;
yвикористовуючи клавішу Ctrl виділити комірки зі значеннями проміжних підсумків;
yвикликати майстер діаграм і побудувати діаграму.
13.Роздрукувати побудовану діаграму.
14.Перейменувати робочий лист Лист3 на Граничні_значення.
15.Використовуючи команду Данные→Итоги… на робочому листі Граничні_значення визначити середню вартість залишків товарів кожної групи, а в межах групи – мінімальну ціну товару в залежності від фасування.
Вказівки до виконання:
yвпорядкувати список за значеннями полів Група і Фасування;
yвиконати команду Данные→Итоги… для визначення середньої вартості залишків товарів на складі;
37
Рис. 12.4. Діалогове вікно Промежуточные итоги для обчислення середньої вартості залишків товарів.
yвиділити будь-яку комірку в межах списку;
yвиконати команду Данные→Итоги… для визначення мінімальної ціни товару;
yдля збереження обчислених раніше підсумків у діалоговому вікні
Промежуточные итоги зняти прапорець Заменить текущие итоги.
Рис. 12.5. Діалогове вікно Промежуточные итоги для обчислення мінімальної ціни товару.
16.На робочому листі Граничні_значення відобразити проміжні підсумки для груп Морс, Напій і Сік, для групи Мінеральна вода відобразити дані і обчислені проміжні підсумки.
17.Перейменувати робочий лист Лист4 на Кількість.
18.Використовуючи команду Данные→Итоги… на робочому листі Кількість визначити кількість найменувань товарів кожної групи, що залишилися на складі, в межах групи – кількість товарів, дата виготовлення яких знаходиться в межах одного з місяців року.
Вказівки до виконання:
yзмінити формат представлення даних поля Дата виготовлення так, щоб відображалися тільки місяць і рік введених дат;
yвпорядкувати список за значеннями полів Група і Дата виготовлення;
y виконати команду Данные→Итоги… для обчислення необхідних
38
підсумків.
19. Роздрукувати отримані результати.
Контрольні питання
1.Створення форматів користувача. Призначення символів, які використовуються у масках форматів.
2.Фіксування областей робочого листа.
3.Розділення робочого листа на підобласті.
4.Поняття структури і структуризації робочого листа.
5.Створення структури робочого листа вручну.
6.Елементи управління структурою робочого листа.
Лабораторна робота №13.
Тема: Робота зі списками в MS Excel. Сортування та фільтрація даних.
Мета: Закріпити навички створення та впорядкування списків. Набути навички відбору інформації за різними критеріями.
Хід роботи:
1.Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).
2.Зберегти створений документ під іменем ЛР13_Прізвище.xls.
3.На робочому листі Лист1 створити таблицю "Перелік товарів на складі фірми "Мінос", наведену на рис. 13.1. Змінити назву робочого листа
Лист1 на Склад.
4.Додати до списку поле Вартість. Обчислити вартість поставлених товарів.
5.Скопіювати створений список на робочі листи Лист2, Лист3, Лист4.
6.На робочому листі Склад відобразити першу половину отриманого списку.
Вказівки до виконання:
yвиділити будь-яку комірку списку;
yвиконати команду Данные→Фильтр→Автофильтр;
yу заголовку поля № п/п натиснути кнопку зі стрілкою;
yзі списку, що відкрився вибрати елемент (Первые 10…);
yу діалоговому вікні Наложение условий по списку встановити
відповідні параметри для відображення першої половини списку
(рис. 13.1.).
39
Рис. 13.1. Таблиця "Перелік товарів на складі фірми "Мінос".
Рис. 13.2. Діалогове вікно "Наложение условия по списку".
7.Впорядкувати отриманий список за значеннями поля Виробник в алфавітному порядку і поля Ціна в порядку зменшення значень. Роздрукувати результат.
8.Назвати робочий лист Лист2 – Підсумки.
9.На робочому листі Підсумки визначити загальну суму поставлених товарів кожною з фірм-постачальників. Впорядкувати список за зменшенням загальної вартості поставлених товарів.
Вказівки до виконання:
yвикористати можливість обчислення проміжних підсумків у списку для визначення загальної суми поставлених товарів;
yдля сортування списку в залежності від обчислених підсумків необхідно згорнути рівень структури робочого листа з даними, потім виконати відповідну команду сортування.
40