Лабораторна робота №4, Побудова діаграм у середовищі MS Excel

« Назад

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

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

На відміну від попередніх версій на цей час в MS Excel відсутній майстер діаграм. Щоб створити базову діаграму, необхідно виділити потрібний діапазон комірок та вибрати тип діаграми на вкладці Вставка в групі Диаграммы.

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

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

- область діаграми;

- область побудови діаграми;

- точки даних що відмічені на діаграмі;

- вісь категорій (горизонтальна) і значень (вертикальна), уздовж яких будується діаграма;

- легенда діаграми;

- назви діаграми і осей, які можна використовувати в діаграмі;

- підпис даних, за допомогою яких можна позначати відомості точки даних в ряду даних.

Створивши діаграму, можна змінити будь-який з її елементів. Наприклад, можна змінити вид осей, додати назву діаграми, перемістити або приховати легенду, а також додати додаткові елементи. Щоб змінити діаграму, потрібно виконати наступні дії:

- Зміна виду осей діаграми. Можна вказати масштаб осей і змінити проміжки між значеннями або категоріями. Для зручності читання діаграми ви можете додати на осі ділення і вказати величину проміжків між ними.

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

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

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

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

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

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

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

Всі об'єкти діаграми можна переміщати, а також змінювати їхні розміри. Вбудовану діаграму можна переміщати по робочому листу. Виділену діаграму також можна видалити, натиснувши клавішу Del. Для форматування виділеного елемента необхідно скористатися командою Формат<ім’я об’єкту> контекстного меню об'єкту.

Завдання 1

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

Для розрахунків використати формулу:

Порядок виконання

Відкрийте табличний процесор Microsoft Excel, виконавши команду Пуск — Все Программы — Microsoft Office — Microsoft Excel.

1. Змінити ім’я поточного робочого аркуша на ім’я Відомості та встановити колір ярличка за Вашим бажанням.

2. Створити на робочому аркуші таблицю згідно ескізу. Для об’єднання комірок використовується кнопка Объединить и поместить в центре . Для переносу тексту в комірці на наступну строку необхідно у місці розриву натиснути Alt+Enter (або скористатися кнопкою Перенести текст ).

3. Встановити шрифт назви таблиці напівжирний, розмір 14 пунктів і розташувати його у центрі таблиці; установити шрифт для символів шапки таблиці (А4:Е6) напівжирний, розмір 12 пунктів і розташувати дані, які містяться в цих комірках, по центру. Інше форматування встановити згідно ескізу. Для встановлення форматування тексту треба скористатися відповідними кнопками групи Шрифт та Выравнивание на вкладці Главная.

4. Налаштувати ширину стовбців (шляхом перетягування правої границі заголовка стовпця) та висоту рядків таблиці для коректного відображення даних.

5. Для обчислення сумарної кількості пожеж за поточний рік встановіть рамку виділення у комірку C16 та клацніть на кнопці Сумма  (вкладка Главная, група Редактирование). Перевірте правильність формули =СУММ(C7:C15) та натисніть клавішу Enter.

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

7. Для обчислення відсотка зміни кількості пожеж потрібно в комірку Е7 ввести відповідну формулу: =(C7-D7)/D7.

8. Встановити процентний формат з двома знаками після коми для комірки Е7, для цього встановіть рамку виділення у комірку Е7 та натисніть кнопку Процентный формат , для зміни кількості знаків після коми потрібно скористатися кнопками Увеличить разрядность  та Уменьшить разрядность  (вкладка Главная, група Число).

 

Важливо

1. При встановленні процентного формату автоматично здійснюється множення на 100 ( та встановлюється знак %), тому при введенні формули в комірку Е7 множення на 100 відсутнє.

2. Форматування комірок також можна здійснити по команді Формат  - Формат ячеек… (вкладка Главная, група Ячейки), або за допомогою контекстного меню комірки – команда Формат ячеек.

 

9. Діючи аналогічно, встановіть для комірок діапазону В3:F3 формати.

10. За допомогою маркера заповнення виконати копіювання формул у відповідні комірки таблиці. Для цього виділити комірку Е7 і маркер заповнення відбуксирувати на комірку Е16. Відпустити ліву клавішу миші.

11. Виконати обрамлення таблиці лініями згідно ескізу за допомогою кнопки Границы  (вкладка Главная, група Шрифт).

12. Зберегти робочу книгу (Файл – Сохранить) у власній папці на жорсткому диску під ім’ям Діаграми.

Завдання 2

За допомогою об'ємної гістограми представити дані про кількість пожеж по районах міста за поточний та минулий роки. Створену діаграму помістити на окремий аркуш з ім’ям Гістограма робочої книги Діаграми.

Порядок виконання

1. Перейдіть на робочий аркуш з ім’ям Відомості та виділити діапазон комірок С7:D15 (числові дані про кількість пожеж за поточний та минулий роки).

2. Перейдіть на вкладку Вставка та в групі Диаграммы натисніть на кнопку Гистограмма  та з списку, що випадає, вибрати її вид: Объемная гистограмма с группировкой. Побудована за замовчуванням діаграма з’явиться на робочому аркуші.

На стрічці команд з’явилась група вкладок Работа с диаграммами, яка має три вкладки: Конструктор, Макет та Формат.

3. На вкладці Конструктор в групі Данные клацніть на кнопці Выбрать данные . Відкриється вікно Выбор источника данных (рис. 2.11) в якому потрібно встановити назву елементів легенди та підписи осі категорій.

4. Для встановлення назви рядків з даними, потрібно у переліку Элементы легенды (ряды) вибрати пункт Ряд1 натиснути кнопку Изменить  та у полі Имя ряда ввести з клавіатури назву Поточний рік (або натиснути на кнопку мінімізації  поля Имя ряда й виділити комірку C5 у вихідній таблиці). Підтвердити вибір натиснувши ОК.

5. Діючи за аналогією перейменувати Ряд 2 на Минулий рік.

6. Для встановлення підписів категорій, потрібно у переліку Подписи горизонтальной оси (категории) натиснути кнопку Изменить . Мінімізувати вікно, натиснувши кнопку мінімізації вікна . Потім на робочому аркуші виділити діапазон комірок В7:В15 (цей діапазон автоматично введеться у поле уведення). Підтвердити вибір натиснувши ОК.

7. Закрити вікно Выбор источника данных (рис. 2.11) натиснувши ОК та переглянути отриману діаграму.

Рис. 2.11 - Вікно Выбор источника данных

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

9. Діючи за аналогією встановити назву вертикальної осі – Кількість пожеж.

10. Щоб встановити назву діаграми треба натиснути на кнопку Название диаграммы (вкладка Макет, група Подписи) та із списку, що випадає, вибрати її місце розташування. Змінити назву діаграми на Динаміка кількості пожеж по районах м. Харкова.

11. Розташувати легенду зверху діаграми. Для цього з списку кнопки Легенда, що випадає, вибрати пункт Легенда та вибрати варіант розміщення зверху.

12. Перемістимо діаграму на окремий аркуш. Для цього натиснемо на кнопку Переместить диаграмму  (вкладка Конструктор, група Расположение). У діалоговому вікні виберемо пункт – на отдельном листе. Увести назву ярличка листа – Гістограма та натиснути кнопку ОК. Діаграма буде переміщена на окремий лист з назвою Гістограма (рис. 2.12).

13. Якщо необхідно, провести форматування елементів створеної діаграми для більш наочного представлення у відповідності до зразка (рис. 2.12).

Рис. 2.12 – Гістограма

14. Зберегти робочу книгу, виконавши команду Файл – Сохранить.

Також для придання наочності діаграмам можливо скористатися експрес-макетами, які розташовані у відповідному списку, що випадає (вкладка Конструктор, група Макеты Диаграммы).

Завдання 3

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

Порядок виконання

1. Перейдіть на робочий аркуш з ім’ям Відомості та виділити діапазон комірок В7:С15 (числові дані про кількість пожеж за поточний рік та назви районів).

2. Перейти на вкладку Вставка та в групі Диаграммы натиснути на кнопку Круговая  та зі списку, що випадає, вибрати її вид: Объемная круговая. Побудована за замовчуванням діаграма з’явиться на робочому аркуші.

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

Для придання наочності діаграмі можливо скористатися експрес-макетами (наприклад макет2 або макет6), які розташовані у відповідному списку, що випадає (вкладка Конструктор, група Макеты диаграмм).

4. За допомогою кнопки Название диаграммы  з списку, що випадає, вибрати пункт Над диаграммой (вкладка Макет, група Подписи). Змінити назву діаграми на Кількість пожеж по районах м. Харкова за поточний рік.

5. Поставити легенду знизу діаграми. Для цього з списку кнопки Легенда , що випадає, вибрати варіант розміщення знизу.

6. Перемістимо діаграму на окремий аркуш. Для цього натиснемо на кнопку Переместить диаграмму  (вкладка Конструктор, група Расположение). У діалоговому вікні виберемо пункт – на отдельном листе. Увести назву ярличка листа – Кругова та натиснути кнопку ОК. Діаграма буде

переміщена на окремий лист з назвою Кругова (рис. 2.13).

7. Якщо необхідно, провести форматування елементів створеної діаграми для більш наочного представлення у відповідності до зразка (рис. 2.13).

Рис. 2.13 – Кругова діаграма

8. Зберегти робочу книгу, виконавши команду Файл – Сохранить.

Завдання 4

За допомогою графіка провести аналіз співвідношення кількості пожеж за минулий та поточний рік по районах м. Харкова. Створений графік помістити на окремий аркуш з ім’ям Графік робочої книги Діаграми.

Порядок виконання

1. Перейти на робочий аркуш з ім’ям Відомості та виділити діапазон комірок В7:D15 (числові дані про кількість пожеж за минулий та поточний рік та назви районів).

2. Перейти на вкладку Вставка та в групі Диаграммы натиснути на кнопку График  та з списку, що випадає, вибрати його вид: график с маркерами. Побудований за замовчуванням графік з’явиться на робочому аркуші.

3. Перейдіть на вкладку Конструктор та в групі Данные клацніть на кнопці Выбрать данные . Відкриється вікно Выбор источника данных (рис. 2.11). Переконатися що підписи горизонтальної осі (категорії) автоматично введені.

4. Встановити назви рядків з даними, для цього потрібно у переліку Элементы легенды (ряды) вибрати пункт Ряд1 натиснути кнопку Изменить  та у полі Имя ряда ввести з клавіатури назву Поточний рік (або натиснути на кнопку мінімізації  поля Имя ряда й виділити комірку C5 у вихідній таблиці). Підтвердити вибір натиснувши ОК. Діючи за аналогією перейменувати Ряд 2 на Минулий рік.

5. Закрити вікно Выбор источника данных натиснувши ОК та переглянути отриманий графік.

6. Використовуючи кнопку Названия осей  (вкладка Макет, група Подписи) добавити на графік назву горизонтальної осі – Назва районів та назву вертикальної осі – Кількість пожеж. Встановити назву графіка – Динаміка кількості пожеж по районах м. Харкова.

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

8. Перемістити діаграму на окремий аркуш. Для цього натисніть на кнопку Переместить диаграмму  (вкладка Конструктор група Расположение). вибрати пункт – на отдельном листе. Ввести назву листа – Графік. Натиснути кнопку ОК. Графік буде побудована на окремому листі з назвою Графік (рис. 2.14).

9. Якщо необхідно, провести форматування елементів створеного графіку для більш наочного представлення у відповідності до зразка (рис. 2.14).

Рис. 2.14 – Графік

10. Зберегти робочу книгу, виконавши команду Файл – Сохранить.

Завдання для самостійної роботи

За допомогою кругової об'ємної діаграми виконати графічний аналіз співвідношення кількості пожеж у минулому році по районах м. Харкова (діапазон D7:D15).

За допомогою лінійчатої діаграми провести аналіз зміни у відсотках кількості пожеж за минулий та поточний рік по районах м. Харкова (діапазон Е7:Е15).

Створені діаграми помістити на окремих аркушах з ім’ям Самостійно1 та Самостійно2 робочої книги Діаграми.

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

Результат роботи

Робоча книга Діаграми, у який присутні аркуші: Відомості, Гістограма, Кругова, Графік. На цих листах повинні бути таблиця з обчисленнями, відповідні графіки та діаграми.

Питання для самоконтролю

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

2. Які елементи діаграм можна встановити та як?

3. Як задати діапазон комірок для створення діаграм? Для чого призначені кнопки мінімізації?

4. Що таке Ряд даних та Категорія? Як задати імена рядів даних?

5. Як здійснити форматування елементів діаграми?

6. Як розмістити діаграму на окремому аркуші?