Лабораторна Робота №5

Тема. Побудова діаграм у табличному процесорі MS Excel.

Мета.

1) Набути навичок побудови діаграм.

2) Оволодіти вміннями форматування діаграм.

3) Навчитись проводити графічний аналіз економічних показників за допомогою діаграм різного типу.

І. КОРОТКІ ТЕОРЕТИЧНІ ВІДОМОСТІ

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

1. Основи побудови діаграм в Excel

1.1. Типи діаграм

Excel надає можливість створювати діаграми різних типів. Тип діаграми, що використовується, обирається в залежності від самих даних і від того, яким чином потрібно їх подати.

Таблиця 5.1

Деякі типи стандартних діаграм

Тип діаграми	Призначення
Кругова	Призначена для порівняння частин, що складають ціле
Лінійна	Використовується для порівняння величин, що змінюються в часі
Гістограма	Схожа на лінійну діаграму. Використовується для порівняння декількох наборів даних
Графік	Використовується тоді, коли необхідно простежити зміну деякого параметра протягом певного періоду часу
Крапкова	Схожа на графік. Використовується для порівняння декількох (часто експериментальних) наборів даних, що виводяться у вигляді крапок
З областями	Також схожа на графік. Допомагає аналізувати зміну значень деякого параметру на визначеному проміжку часу

Більшість вищезазначених діаграм можуть бути тривимірними.

Вбудована діаграма – це діаграма, яка розташовується на тому ж аркуші, що і дані, на основі яких вона побудована. Але її можна винести на окремий аркуш. Вбудована діаграма використовується тоді, коли необхідно розмістити поруч таблицю даних та їх графічне подання.

1.2. Елементи діаграми

Основні елементи діаграми показані на рис. 5.1.

Рис. 5.1. Елементи діаграми на прикладі об’ємної гістограми

Ряд – набір зв’язаних між собою елементів даних, які відображаються на діаграмі. Кожному ряду даних на діаграмі відповідає окремий колір чи спосіб позначення, вказаний у легенді діаграми. Діаграми усіх типів, крім кругової, можуть містити декілька рядів даних. Зазвичай, кожний ряд відповідає серії даних, занесених в окремий стовпець чи рядок таблиці, і тому часто назви рядів співпадають із заголовками стовпців чи рядків у таблицях.

Легенда – це підпис, який визначає колір (візерунок) рядів чи категорій даних на діаграмах і допомагає їх розрізняти. Наприклад, для кругової діаграми легенда зображається у вигляді виносок з підписами до кожного її сектору.

Вісі – це шкали, вздовж яких змінюється діаграма. Зазвичай, вони обмежують область побудови діаграми і використовуються як система координат у вибраних одиницях вимірювання. У випадку двовимірної діаграми їх дві: вісь х (зазвичай, горизонтальна) і вісь у (зазвичай, вертикальна). Вздовж осі у будуються дані, а вздовж осі х – розміщуються категорії. У тривимірній діаграмі вісь z направлена вверх, вісь у відображає глибину діаграми, а вздовж осі х вказуються категорії.

Лінії сітки – допомагають аналізувати значення даних. За допомогою горизонтальних ліній сітки можна точніше визначити величину координат на діаграмі.

1.3. Створення діаграми

Можна розмістити діаграму на тому ж аркуші, що і дані, на основі яких вона побудована, або винести її на окремий аркуш. У будь-якому випадку діаграма залежить від даних таблиці, і тому, якщо їх змінити, то діаграма теж автоматично зміниться.

Послідовність побудови діаграми за допомогою Мастера диаграмм:

1. Виділити дані, на основі яких слід побудувати діаграму. Якщо потрібно включити в діаграму імена груп комірок (наприклад, План, Факт) або інші позначки, слід переконатися в тому, що вони також виділені.

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

2. Натиснути на кнопку Мастер диаграмм, що розташована на стандартній панелі, або скористатися командою Вставка – Диаграмма.

3. З’явиться діалогове вікно майстра з переліком типів діаграм. У списку Тип потрібно вказати тип діаграми, а у вікні Вид – необхідний її вигляд. Після цього натиснути на кнопку Далее.

4. З’явиться друге діалогове вікно майстра,в якому треба вказати (змінити) джерело даних діаграми у вигляді діапазону.

5. За замовчуванням кожний рядок даних сприймається як новий діапазон. У списку Ряды в: потрібно вибрати опцію столбцах. У цьому випадку кожний діапазон даних буде відповідати значенням в одному стовпчику, а не в рядку. Після цього знов натиснути на кнопку Далее.

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

7. В останньому діалоговому вікні Мастера диаграмм потрібно вказати місце розташування діаграми – на окремому листі або на поточному. Вказавши лист, потрібно натиснути кнопку Готово. На екрані з’явиться діаграма, яка відповідає певним даним і зробленим установкам.

Швидке створення діаграм. Для цього потрібно виділити дані, на основі яких потрібно її побудувати, і натиснути F11. За замовчуванням, буде побудована плоска гістограма, розташована на окремому аркуші.

1.4. Переміщення діаграми, зміна її розмірів та друк

Для того, щоб перемістити вбудовану діаграму, потрібно встановити на ній покажчик мишки, натиснути ліву кнопку і перемістити її в необхідну область. Щоб змінити розміри діаграми, слід скористатись відповідними маркерами (чорними квадратиками на її межах).

Для модифікації діаграми слід скористатися кнопками панелі Диаграммы (рис. 5.2). Якщо дана панель не відображена на екрані, потрібно виконати команду Вид – Панели инструментов – Диаграммы.

Рис. 5.2. Панель інструментів Диаграммы

Збереження діаграм. Створена діаграма є частиною робочої книги, тому зберігається разом з нею.

Друк діаграм. Якщо діаграма вбудована, то можна роздрукувати її разом з таблицею даних на тому ж аркуші. Якщо ж необхідно роздрукувати тільки вбудовану діаграму, то спочатку потрібно виділити її, а після цього виконати команду Файл – Печать, переконатися в тому, що вибрана опція Вывести на печать выделенную диаграмму і натиснути ОК. Якщо діаграма розташована на окремому аркуші, то слід просто вивести на друк відповідний аркуш робочої книги.

2. Модифікація зовнішнього вигляду діаграм

2.1. Виділення частини діаграми

Діаграма сама по собі складається з декількох незалежних один від одного елементів. Перш ніж змінити будь-яку частину діаграми, необхідно спочатку її виділити. Для цього просто потрібно натиснути лівою кнопкою миші на частині, що цікавить, або вибрати її зі списку Элементы диаграммы на панелі інструментів Диаграммы. Вибраний елемент діаграми обмежується прямокутником з розташованими на ньому маркерами переміщення (чорні квадратики). Виділену частину діаграми можна перемістити на нове місце за допомогою мишки або змінити її розміри, використовуючи відповідні маркери.

2.2. Модифікація типу діаграми

Щоб змінити тип діаграми, потрібно спочатку виділити за допомогою мишки всю діаграму (а не її окремий елемент) та скористатись командою Тип диаграммы з контекстного меню. У діалоговому вікні, що з’явиться, буде запропонований список типів діаграм.

Ті ж дії можна здійснити і за допомогою кнопки Тип диаграммы на панелі інструментів Диаграмма, або виконати команду Диаграмма – Тип диаграммы з головного меню.

2.3. Додавання легенди і заголовків

Можна додати до діаграми заголовки, щоб описати дані, наведені в ній. Заголовок діаграми розміщується над нею. Також можна відобразити заголовки осей діаграми і додати до неї легенду. Для цього треба:

1) Виділити діаграму мишкою.

2) Викликати контекстне меню діаграми та обрати команду Параметры диаграммы. З’явиться відповідне діалогове вікно.

3) Перейти на вкладку Заголовки, якщо необхідно додати заголовки діаграми та осей, і ввести їх у відповідні поля.

4) Якщо потрібно додати легенду, слід перейти на вкладку Легенда і встановити прапорець опції Добавить легенду, а після цього вказати розташування легенди на аркуші.

5) Виконавши всі необхідні установки, натиснути на кнопку ОК.

2.4. Форматування текстових і числових даних

Будь-який текст, розміщений на діаграмі, заноситься у власне прямокутне поле. Щоб змінити формат тексту, розташованого на діаграмі, або числа, на основі яких вона побудована, треба виконати такі дії.

1. Двічі клацнути на тексті, що необхідно відформатувати.

2. У діалоговому вікні, що з’явилося, обрати закладку Шрифт. Вона містить опції, за допомогою яких змінюються параметри шрифту.

3. Щоб змінити розташування тексту всередині прямокутного поля, потрібно натиснути на вкладку Выравнивание. При необхідності можна відобразити текст на аркуші і під кутом.

4. Щоб змінити формат чисел, потрібно натиснути на вкладку Число. У списку Числовые форматы вибрати бажаний.

5. Змінивши формат, потрібно натиснути ОК або Enter.

2.5. Модифікація елементів діаграми

Щоб покращити зовнішній вигляд діаграми, можна додати рамку навколо неї, змінити фон області, на якій розташована діаграма, колір рядів, заливку області побудови діаграми і т.п. Для цього слід:

1. Двічі клацнути мишкою на потрібному елементі діаграми або виконати команду Формат... з контекстного меню відповідного елемента.

2. Скористатись діалоговим вікном Формат..., задавши усі необхідні установки.

3. Додаткові засоби форматування діаграм

3.1. Модифікація сітки

За замовчуванням на діаграмах зображаються тільки горизонтальні лінії сітки. Можна встановити і вертикальні лінії. Орієнтуючись по основних лініях сітки, можна дізнатися приблизні значення даних, виведених на діаграму. Проміжні лінії дадуть можливість більш точно оцінити їх значення. Вони розміщуються між основними. Потрібні настройки задаються на вкладці Линии сетки вікна Параметры диаграммы.

3.2. Модифікація шкали значень

Можна змінити початкове і кінцеве значення шкали осі значень (звичайно це y -вісь), а також відстань між сусідніми лініями сітки. Для цього потрібно:

1. Двічі клацнути на потрібній осі. З’явиться діалогове вікно Формат оси.

2. Вибрати вкладку Шкала.

3. Вказати у відповідних полях максимальне і мінімальне значення даних, що виводяться.

4. Вказати інтервал між сусідніми лініями сітки у полі Цена основных делений.

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

6. Щоб вивести діаграму з логарифмічною шкалою, потрібно встановити прапорець опції Логарифмическая шкала. Якщо на діаграмі відображаються і від’ємні значення, то опцією Логарифмическая шкала користуватися не можна.

7. Щоб змінити напрямок вісі значень на протилежний, потрібно вибрати зворотний порядок значень.

8. Після вибору всіх установок натиснути на кнопку ОК.

3.3. Модифікація тривимірних діаграм

На відміну від двовимірних діаграм, тривимірні дають об’ємне подання. Щоб відобразити на екрані такі діаграми більш вдало, треба змінювати кут їх нахилу або обертати їх. Для цього можна скористатись командою Объемный вид з контекстного меню діаграми.

ІІ. ПРАКТИЧНА ЧАСТИНА

Хід роботи

1. Опрацювати теоретичні відомості.

2. Відкрити робочу книгу Прізвище_Excel, яка містить лист Лаб3-4 з таблицею Аналіз зросту і ваги юнаків групи.

3. Проаналізувати дані таблиці за допомогою плоскої гістограми. При побудові діаграми слід скористатися наступними рекомендаціями:

3.1. Виділити дані (ряди і підписи категорій), які будуть відображені на діаграмі. У нашому випадку це комірки В5:D14. Оскільки таблиця містить об’єднані комірки, то заголовки стовпців не виділяються.

3.2. Викликати майстра побудови діаграм, натиснувши відповідну кнопку на стандартній панелі інструментів (або іншим способом).

3.3. На першому кроці роботи майстра обрати тип діаграми. Наприклад, Стандартная, Гистограмма, Вид 1. Натиснути Далее.

3.4. На другому кроці роботи майстра на закладці Диапазон данных обрати, яким чином розташовані дані у виділеному діапазоні (в рядках чи стовпцях) та переглянути попередній результат (рис. 5.3).

Рис. 5.3. Другий крок побудови діаграми за допомогою майстра

3.5. Перейти на закладку Ряд цього ж самого діалогового вікна та задати (відкоригувати) дані:

- імена рядів (будуть відображатись у легенді діаграми);

- значення рядів (якщо не всі дані попередньо були виділені);

- підписи по осі Х (якщо не були виділені завчасно);

- додати (видалити) нові (зайві) ряди.

У нашому випадку слід задати імена рядів (замість Ряд1, Ряд2 встановити Зріст, см та Вага, кг), записавши їх з клавіатури у полі Имя:, по черзі виділяючи кожний ряд, або вказавши мишкою на комірки таблиці, що містять ці підписи (див. рис. 5.4). Внесені зміни одразу відображаються у вікні роботи майстра.

Рис. 5.4. Встановлення параметрів для рядів даних

Натиснути кнопку Далее.

3.6. На третьому кроці роботи майстра діаграм потрібно задати основні параметри діаграми, що будується:

- назву діаграми;

- заголовки осей;

- місце розташування легенди;

- наявність основних та проміжних ліній сітки;

- встановлення підписів даних тощо.

Для нашої діаграми потрібно задати (рис. 5.5):

- заголовок: Аналіз зросту та ваги юнаків групи;

- підпис осі Х (категорій): Прізвища студентів;

- місце розташування легенди: справа;

- лінії сітки по осі Y: основные линии.

Рис. 5.5. Додавання заголовків у діаграмі

3.7. Натиснувши кнопку Далее, переходимо до останнього кроку побудови діаграми. Залишилось вказати, де буде розташована наша діаграма, розмістивши діаграму на цьому ж листі (рис. 5.6).

Рис. 5.6. Останній крок побудови діаграми за допомогою майстра

3.8. Залишилось натиснути кнопку Готово і переглянути отриманий результат (рис. 5.7). При необхідності можна змінити місце розташування деяких елементів діаграми, а також розмір шрифту, яким зроблені підписи.

3.9. Провести аналіз даних за допомогою діаграми (визначити студента з найбільшим та найменшим зростом, найбільшою та найменшою вагою).

Рис. 5.7. Приклад побудованої плоскої гістограми

4. Скопіювати отриману діаграму на лист Лаб5 та змінити її вигляд на об’ємну гістограму (вид 4).

Для копіювання діаграми потрібно виділити її та скористатись командами Правка – Копировать / Правка – Вставить. Для зміни типу діаграмиможна скористатись контекстним меню діаграми, з якого обрати команду Тип диаграммы (див. рис. 5.8).

Рис. 5.8. Контекстне меню діаграми

5. Внести корективи у зовнішній вигляд об’ємної гістограми за допомогою команди Объемный вид з контекстного меню діаграми (рис. 5.9).

Рис. 5.9. Вікно форматування тривимірної діаграми

Отримана діаграма матиме вигляд (рис. 5.10):

Рис. 5.10. Об’ємна гістограма після модифікації

6. Зробити копію побудованої діаграми на листі Лаб5. Оскільки дані, що відображені на діаграмі, є різними характеристиками одного і того ж самого об’єкта, а також мають різні одиниці вимірювання, зручно використовувати діаграми з двома шкалами значень. Для цього потрібно змінити тип діаграми на змішану "График | гистограмма 2", як показано на рис. 5.11. Задати підписи по першій і другій осі Y (кг та см), у зв’язку з чим змінити назви рядів на Вага і Зріст (див. рис. 5.11).

Рис. 5.11. Змішана діаграма до форматування

7. Провести форматування елементів діаграми (фону, шрифтів, вирівнювання, візерунків, виду заливки тощо), як показано на рис. 5.12.

Рис. 5.12. Змішана діаграма після форматування

8. Відмітити на діаграмі студента, який має найменший зріст (або найменшу вагу), виділивши іншим кольором його дані та підписавши значення (див. рис. 5.12). Для цього слід скористатись контекстним меню точки даних, попередньо виділивши її послідовним повільним натисканням лівої кнопки миші.

9. Підготувати звіт про виконання лабораторної роботи.

10. Ознайомитися з питаннями для самоконтролю та тестами.

11. Захистити лабораторну роботу викладачу.

ІІІ. ПИТАННЯ ДЛЯ САМОКОНТРОЛЮ

1. Які стандартні типи діаграм існують в Excel?

2. Опишіть структуру діаграми.

3. Який вигляд має гістограма?

4. Чим визначається число стовпців у групах в гістограмі?

5. Де може розміщуватися легенда в гістограмі?

6. Що вказує легенда в діаграмі?

7. Що позначають мітки легенди?

8. Опишіть роботу з панеллю інструментів Диаграммы.

9. Охарактеризуйте етапи створення діаграм за допомогою Мастера диаграмм.

10. Вкажіть способи виклику Мастера диаграмм.

11. За допомогою якої функціональної клавіші можна побудувати діаграму без виклику майстра? Діаграма якого типу буде побудована в цьому випадку?

12. Які діаграми належать до стандартних?

13. Які типи діаграм відносяться до нестандартних?

14. Охарактеризуйте найпоширеніші типи стандартних діаграм та їх різновиди.

15. Чим відрізняється тип діаграми График від Точечная?

16. Які діаграми належать до категорії Дополнительные?

17. Опишіть порядок вибору даних для побудови діаграми.

18. Опишіть процедуру встановлення параметрів діаграми.

19. Які існують варіанти розміщення діаграми?

20. Опишіть порядок форматування областей діаграми?

21. Яка вкладка дає змогу встановити діапазон зміни значень даних?

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

23. Як змінити розміри діаграми?

24. Яким чином можна змінити орієнтацію об’ємних діаграм?

25. Яким чином можна вивести діаграму на друк?

ІV. ПЕРЕВІРОЧНІ ТЕСТИ

1) Як відображається зміна даних таблиці Excel на відповідній діаграмі?

a) У діаграмі змін немає

b) Діаграма автоматично змінюється

c) Залежить від задачі

d) За бажанням користувача

2) Як називають опис рядів даних?

a) Легендою

b) Заголовком

c) Діаграмою

d) Міткою

3) Як називають послідовність значень даних на діаграмі?

a) Рядом даних

b) Категорією

c) Легендою

d) Сіткою

4) Вказати правильний спосіб зміни розміру діаграми в Excel:

a) За допомогою команди Формат

b) Пересуваючи мишею маркери виділення діаграми

c) Розмір змінити не можна

d) За допомогою команди Вставка

5) Що таке ряд даних?

a) Розміщені в рядок дані

b) Розміщені стовпчиком дані

c) Дані, відібрані по принципу спорідненості

d) Дані, відібрані по збільшенню номера розташування комірки у таблиці

6) Що таке точка даних?

a) Одне з числових значень, відібраних для відображення на діаграмі

b) Точка на точковій діаграмі

c) Точка на осі діаграми

d) Підпис осі діаграми

7) Що таке ім’я ряду?

a) Назва ряду, введена з клавіатури у діалоговому вікні

b) Назва ряду, вибрана з підписів шапки таблиці

c) Значення першого елементу ряду даних

d) Назва, введена з клавіатури або вибрана з підписів шапки таблиці

8) Для яких даних доцільно використати діаграму-графік?

a) Числових

b) Таких, що передбачають можливість проміжних даних між відображуваними

c) Непов’язаних дискретних вимірів

d) Таких, що становлять частини одного цілого

9) Скільки рядів даних може мати кругова діаграма?

a) Два

b) Один

c) Три

d) Скільки задав користувач

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

a) Доля

b) Значення

c) Категорія

d) Легенда

11) Як виділити точку даних на діаграмі?

a) Подвійним клацанням мишки на точці даних

b) Натисканням правої клавіші мишки на точці даних

c) Послідовним виділенням лівою кнопкою ряду даних і потрібної точки даних

d) Охопленням точки даних прямокутником виділення

12) Коли використовується зміна початкового значення осі Y?

a) При великих значеннях даних у ряду

b) Коли великі абсолютні значення точок у ряду даних і малі відмінності між ними

c) При малих значеннях у ряду даних

d) Коли будуються діаграми дискретних значень вимірів

13) Що таке ключ легенди?

a) Позначка елемента легенди

b) Елемент діаграми, відповідальний за згортання чи розгортання змісту легенди

c) Елемент діаграми, що дозволяє зміну легенди

d) Захист легенди від внесення змін

14) Коли використовують діаграми з двома осями?

a) Коли дані рядів дуже різняться за діапазоном значень

b) Коли дані рядів мають різні одиниці виміру

c) В першому і в другому випадках

d) Немає правильної відповіді

15) Яку діаграму будує система за замовчуванням після першого кроку побудови?

a) Гістограму з мітками осей i легендою

b) Останню перед цим побудовану діаграму

c) Об’ємну гістограму з мітками осей i з легендою

d) Плоску лінійну діаграму

16) Чи можна в одній діаграмі об’єднувати плоскі i об’ємні графіки?

a) Не можна

b) Можна, побудувавши її вручну

c) Можна, за умови встановлення спеціальної надбудови

d) Можна, така діаграма відноситься до нестандартного типу

17) Як відіб’ється зміна графіка (кольору, товщини або типу лінії, маркера) на його легенді?

a) Точно також зміниться умовна позначка даного графіка в легенді

b) У легенді жодних змін не відбудеться

c) Система виведе повідомлення про зміну вигляду графіка i необхідності ввести відповідні зміни в легенді

d) Система не дозволить цього зробити

18) До якої категорії діаграм за замовчуванням відноситься змішана діаграма?

a) Стандартные

b) Нестандартные

c) Дополнительные

d) Не існує такого виду діаграми

19) Чи можна створити власний тип діаграм?

a) Ні, оскільки Excel містить певний перелік вбудованих діаграм

b) Можна

c) Можна тільки у випадку, коли встановлена спеціальна надбудова

20) Яким чином виділяються несуміжні діапазони даних для побудови діаграми?

a) За допомогою лівої кнопки миші, утримуючи клавішу Ctrl

b) За допомогою правої кнопки миші, утримуючи клавішу Ctrl

c) За допомогою лівої кнопки миші, утримуючи клавішу Shift

d) За допомогою лівої кнопки миші, утримуючи клавішу Alt

21) Як вивести цифрові значення найбільших елементів рядів побудованої діаграми?

a) Їх слід було задати на третьому кроці побудови діаграми за допомогою майстра

b) За допомогою команди Параметри діаграми контекстного меню діаграми

c) За допомогою команди Формат точки данных контекстного меню найбільшого елементу

d) Усі відповіді вірні

ІНДИВІДУАЛЬНО-ПРАКТИЧНЕ ЗАВДАННЯ №1

Тема. Виконання розрахунків економічних показників та їх оцінка за допомогою формул та діаграм у Excel.

Мета. Закріпити навички, засвоєні у процесі виконання лабораторних робіт №1-5, та застосувати їх для розв’язування ситуаційних фахових задач.