Заняття 6 з 12
Заняття 6 Створення діаграм
Секрети створення, вибору правильного типу та форматування діаграм в Excel.
Презентація
Speaker Deck
SlideShare
Основи роботи з діаграмами. Як правильно вибрати діаграму для своїх даних. Швидке форматування діаграм і змінення макета. Тонке налаштування діаграми. Використання інструментів швидкого аналізу.
Навички MOS 77-420:
| Назва навички | Номер навички |
|---|---|
| Додавання спарклайнів | 2.3.2 |
| Створення графіків і діаграм | 5.1.1 |
| Додавання додаткових рядів даних | 5.1.2 |
| Перемикання між рядами і стовпцями в джерелі даних | 5.1.3 |
| Демонстрація використання швидкого аналізу | 5.1.4 |
| Додавання легенди | 5.2.1 |
| Змінення розмірів діаграм і графіків | 5.2.2 |
| Змінення параметрів діаграм і графіків | 5.2.3 |
| Застосування налаштувань макета і стилів | 5.2.4 |
| Позиціонування діаграм і графіків | 5.2.5 |
Теорія:
1. Основи роботи з діаграмами (графіками)
Відеоверсія
Текстова версія
Трохи повернемося до правил розміщення інформації на аркушах Excel: ми вже знаємо, що аркуш книги Excel складається з клітинок, дані в яких бувають трьох видів: текст, формули або числа, але в Excel передбачено ще й так званий прихований шар, на якому розміщуються діаграми, зображення та все, що може вільно переміщуватися над поверхнею аркуша. Діаграми ще називають графіками.
В Excel закладено дуже багаті можливості побудови діаграм різних типів. Вивчити всі хитрощі практично нереально, це визнають світові гуру програми Excel, такі як Джон Уокенбах. Причина не стільки в багатстві налаштувань діаграм Excel, скільки в невичерпних можливостях їх застосування. Фактично це конструктор, який можна використовувати для реалізації ефектних діаграм, відсутніх у стандартному наборі Excel. Деякі такі реалізації ми розглянемо, можливо, вони підштовхнуть вас до створення власних шедеврів візуалізації відліків.
Початок роботи з діаграмами
За роботу з діаграмами відповідає вкладка "Вставлення" групи "Діаграми" і "Спарклайни" (їх ще називають інфолініями, міні-діаграми, розміщені в одній клітинці).

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

Можна викликати діалогове вікно "Вставлення діаграми", натиснувши на трикутник виклику в правому нижньому куті групи "Діаграми" вкладки "Вставлення" і вибрати звідти конкретний тип і підтип діаграми.

Після вставлення діаграми на аркуш Excel для її тонкого налаштування та керування даними користувачеві стають доступними дві вкладки: "Конструктор" і "Формат".
За допомогою вкладки "Конструктор" користувач може змінити тип діаграми, вибрати або змінити дані, додати або видалити певні елементи, а також вибрати стиль оформлення чи макет.

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

Microsoft максимально спростила процес додавання нових даних для візуалізації на діаграмі: користувачу достатньо встановити курсор у клітинку діапазону з даними і вибрати бажаний тип діаграми. Excel спробує визначити межі діапазону і вивести результат у вигляді діаграми на екран.
Однак це не найкращий спосіб: для простих діапазонів, звісно, підходить, але краще відразу виділити діапазон або діапазони з даними, а потім вказати бажаний тип діаграми — так можна бути впевненим, що в діаграмі будуть візуалізовані лише потрібні дані.
Для побудови діаграми можна використовувати кілька діапазонів, вони можуть бути як суміжними, так і знаходитися на відстані один від одного; у цьому випадку слід утримувати клавішу Ctrl при виборі діапазонів.

Одразу виділити кілька таких "розкиданих" діапазонів не вдасться — їх доведеться додавати вже після створення самої діаграми.
Додавання даних у діаграму після її побудови можна виділити як третій варіант створення діаграм.

Дані в діаграму додаються командою "Вибрати дані" з групи "Дані" вкладки "Конструктор" або з контекстного меню діаграми.

Не найкращий спосіб створення — краще вже відштовхуватися від заповнених даних.
Насправді розмежування способів побудови умовне, оскільки можна виділити дані, побудувати діаграму, а потім додати в неї додаткові діапазони.
Є ще четвертий спосіб створення діаграми — за допомогою інструмента швидкого аналізу; розглянемо його пізніше в рамках цього заняття.
Що виділяти?
Діаграми будуються на числових даних, які можуть бути як константами (введені безпосередньо в клітинку), так і результатом обчислень формул. Однак тут присутня й текстова інформація, яка використовується для заголовків, підписів осей або легенд. Більше того, виділяючи дані для діаграми, можна одразу захопити й текстові підписи діапазонів.

Неможливо не помітити, що Excel не лише правильно підібрав тип діаграми (комбінована з додатковою віссю), а й правильно поєднав назви з кількох клітинок. Залишилось додати назви й підписи осей (за потреби). Навіть досвідченому користувачеві на побудову такої діаграми пішов би час.
На замітку
Якщо діаграма виділена і натиснути швидкий друк, то на друк буде надіслана лише діаграма.
Переміщення діаграми та змінення розмірів діаграми
Як було зазначено раніше, діаграми в Excel, поряд з деякими іншими елементами, розміщуються на прихованому шарі аркуша — вони не прив'язані до клітинок, отже їх можна вільно переміщувати звичайним перетягуванням мишки.
При виділенні діаграми слід бути максимально уважним і кликати по порожній області всередині діаграми або по її краю, бо клік по елементу всередині діаграми, наприклад підпис осі або заголовок, спричинить виділення саме цього елемента і операції переміщення стосуватимуться вже його.
Гаряче поєднання
Переміщення діаграми з утриманням клавіші Ctrl призведе до її копіювання.
Якщо підвести курсор мишки до межі діаграми і потягнути, то відбудеться змінення розмірів діаграми; при цьому внутрішні елементи змінюватимуться пропорційно. Така настройка розмірів є грубою; якщо потрібно точно встановити ширину й висоту, це робиться в групі "Розмір" вкладки "Формат" при виділеній діаграмі.

За замовчуванням діаграма додається на той самий аркуш, з якого виконувалась команда її вставлення; такі діаграми, що не займають весь аркуш, називаються впровадженими. В Excel користувач може розмістити діаграму на окремому аркуші, це можна зробити такими шляхами:
- Побудувати впроваджену діаграму і перемістити її на окремий аркуш. Діалогове вікно "Перемістити діаграму" викликається або через контекстне меню на самій діаграмі, або з вкладки "Конструктор", команда "Перемістити діаграму".
- Діаграму на окремому аркуші можна побудувати відразу — достатньо виділити вихідні дані і натиснути функціональну клавішу "F11".
Описані операції працюють і в зворотному напрямку.
Рекомендовані діаграми
Одна з можливостей, вперше доданої в Excel 2013, називається «Рекомендовані» діаграми. Попередньо виділивши дані, слід виконати команду «Рекомендовані діаграми» з групи «Діаграми» вкладки «Вставлення».

З’явиться вже знайоме вікно вставки нової діаграми, відкрите на вкладці «Рекомендовані діаграми». Excel проаналізує виділений діапазон і запропонує кілька варіантів діаграм, які найкраще інтерпретують дані. Якщо жоден із варіантів не підійшов, можна скористатися вкладкою «Усі діаграми», або вибрати найбільш близький варіант, а потім виконати його тонке налаштування.
Алгоритми, покладені в основу створення рекомендованих діаграм, безумовно, не є досконалими, але для початківця — це корисна функція.
У контексті розгляду рекомендованих діаграм не зайвим буде згадати команду «Ряд/стовпець», яка в один клік поміняє дані по осях; наприклад, при аналізі доходів по шести продуктах вигаданої компанії Excel міг неправильно визначити осі.

Іноді поміняти рядки і стовпці місцями корисно з точки зору проводимого аналізу. Так, у першому випадку показана доходність продуктів по місяцях, тоді як у другому — порівняльний аналіз доходності різних продуктів по місяцях. Іншими словами, з першого графіка можна зробити висновок, що найдоходнішим виявився березень для всіх продуктів, а з другого — що п’ятий і другий продукти — лідери за доходністю.
2. Вибір правильного типу діаграми для візуалізації даних
Відеоверсія
Текстова версія
Вибір даних для представлення на діаграмі
Діаграми – це графічне представлення числових даних. Сприйняття графічної інформації людиною легше. Однак не менш важливу роль відіграє й правильний вибір даних та тип діаграми, яка їх представить.
Наприклад, на рисунку 3 варіанти подання даних про продажі за минулий рік.

На діаграмі першого варіанта, через суттєві відмінності в абсолютних значеннях категорій, вона не є показовою з точки зору аналізу. Незважаючи на значні зміни у відсотках продажів у різні періоди, на діаграмі це помітити дуже складно і весь сенс такого подання втрачається.
Другий варіант використовує дві діаграми: тут видно різницю в продажах, але, по-перше, у даному конкретному випадку можна обійтися однією діаграмою, а по-друге, для відображення тенденції краще підходить графік, а не гістограма.
Третій варіант, представлений комбінованою (змішаною) діаграмою, дозволяє не лише наочно оцінити абсолютні показники, але й чудово показує тенденцію, або її відсутність.
Діаграми створюються, як правило, щоб донести певне повідомлення. Саме повідомлення відображається в назві, а діаграма забезпечує наочність твердження. Щоб доповідача правильно зрозуміли, дуже важливо правильно вибрати тип діаграми, який найкраще представить дані. У цьому питанні заняття ми й розберемо, коли який тип діаграми використовувати.
Загальновизнаний гуру табличного процесора Excel — Джон Вокенбах відзначає, що в переважній більшості випадків повідомлення, яке потрібно донести за допомогою діаграми, є порівнянням, і виділяє такі типи порівнянь:
- Порівняння кількох елементів. Наприклад, продажі по регіонах компанії
- Порівняння даних у часі. Наприклад, обсяги продажів по місяцях і загальна тенденція розвитку компанії
- Відносне порівняння. Іншими словами, виділення частки в цілому. Тут найкращим варіантом буде кругова діаграма
- Порівняння співвідношення даних. Точкова діаграма може добре впоратися з демонстрацією різниці між доходами і витратами
- Порівняння за частотами. Гістограма може використовуватися для відображення кількості студентів, успішність яких знаходиться в певному діапазоні
- Визначення нестандартних показників. Якщо є багато експериментів, то за допомогою діаграми можна візуально визначити «аномалії» або значення, істотно відмінні від інших.
Як же правильно вибрати тип діаграми в Excel
Однозначно відповісти на це питання неможливо — усе залежить від того, яке саме повідомлення користувач хоче передати за допомогою діаграми. Наприклад, якщо у компанії є 6 продуктів, які вона продає, і є дані про доходи від продажу за минулий період, то тут можна використати два варіанти діаграми:
- Гістограма — якщо потрібно провести візуальне порівняння доходу від різних продуктів
- Кругова діаграма — якщо потрібно визначити частку кожного продукту в загальному доході компанії.
Цікаво, що, якщо скористатися рекомендованими діаграмами, Excel сам запропонує і перший, і другий тип. Тут, правда, є ще кілька варіантів, які використовувати абсолютно неправильно, наприклад, графік, воронка або область із накопиченнями.

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

В Excel 2016 існує 7 різних видів гістограм: із групуванням, із накопиченням, нормована з накопиченням, ті самі 3 види в об’ємному виконанні та просто об’ємна гістограма. З об’ємними діаграмами слід поводитися обережно: на перший погляд вони можуть здаватися привабливими, але в більшості випадків поступаються за інформативністю своїм двовимірним аналогам.
Стовпчикові діаграми
Якщо гістограму повернути на 90 градусів, отримаємо стовпчикову (лінійну) діаграму. Галузь застосування цих діаграм аналогічна гістограмам і, загалом, гістограми сприймаються краще, але якщо підписи категорій достатньо довгі, на стовпчиковій діаграмі вони виглядатимуть більш гармонійно.

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

Курси валют, доходи або збитки компанії за певний період часу, відвідуваність сайту та багато інших показників краще зобразити графіком.
В Excel можна побудувати 7 різних підтипів графіків, включно з тривимірними.
Кругові діаграми
Кругові діаграми використовуються для відображення пропорцій відносно цілого. Усі значення, що беруться для кругової діаграми, повинні бути додатними; від’ємні значення будуть перетворені Excel автоматично. Кругові діаграми зручно використовувати, коли потрібно відобразити: частку компанії на ринку, відсоток успішних/неуспішних студентів, нішу певної програми серед конкурентів тощо.

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

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

Точкові діаграми
Ще одним із поширених типів діаграм є точкові діаграми або діаграми розсіювання. Особливістю цього типу діаграм є те, що вони не використовують вісь категорій — по осі X і по осі Y відкладаються саме значення. Цей тип діаграм часто застосовують у статистичних дослідженнях для первісного визначення наявності/відсутності зв'язку між двома змінними (кількість заявок і продажі, відвідуваність і успішність студента, зріст спортсмена і швидкість бігу на 100 м, тривалість роботи в компанії і заробітна плата тощо).

В Excel представлено 7 видів точкових діаграм, і дві з них — бульбашкові, коли розмір бульбашки залежить від значення точки.
Бульбашкові діаграми не надто частий гість у звітах загального характеру: фактично бульбашкова діаграма — це точкова діаграма з додатковим рядом даних.
Цікавий приклад використання бульбашкової діаграми продемонстрував Джон Уокенбах, «намалювавши» мордочку миші; давайте відтворимо цей приклад і ми.

Щоб досягти такого ефекту, довелося змінити параметр розміру бульбашок на «діаметр», встановити масштаб 290 (за замовчуванням 100) і розфарбувати значення, оскільки за замовчуванням для всіх значень однаковий колір.
Діаграми з областями
Цей тип діаграм доволі рідкісний і являє собою графіки з зафарбованими областями. Якщо спробувати зобразити кілька рядів даних, можлива ситуація, коли під одним рядом не буде видно інший — у такому випадку краще використати простий графік або ж впорядкувати ряди даних так, щоб ряд із меншими значеннями опинився попереду.

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

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

Кількість кольорів на поверхневій діаграмі залежить від кроку основних поділок по осі значень: один колір — одне значення.
Поверхнева діаграма в Excel не є тривимірною в повному сенсі: на ній не можна зобразити графік із точок даних, представлених у системі координат x, y, z, за винятком випадку, коли «x» і «y» рівні між собою.
Пелюсткова діаграма
Пелюсткова діаграма є аналогом графіка в полярній системі координат. Пелюсткова діаграма має окрему вісь для кожної категорії — осі виходять із центра, а значення відзначається на відповідній осі.

Як приклад використання пелюсткової діаграми можна проаналізувати тижневу відвідуваність сайту msoffice-prowork.com за певний період — як видно, у вихідні кількість відвідувачів значно менша.
Якщо кілька рядів даних перекривають один одного, аналогічно до діаграми з областями, можна або зробити заповнення прозорим, або вибрати варіант пелюсткової діаграми без заповнення — тоді отримаємо просто графік у полярній системі координат.
Комбінована (змішана) діаграма
Комбінована або змішана діаграма — це діаграма, яка поєднує в собі два типи діаграм. Яскравим прикладом вдалого застосування такого типу діаграми є перший рисунок у цьому питанні, коли гістограмою показано дохід від продажів компанії, а лінійним графіком на тій же діаграмі — процентну зміну щодо попереднього року.
В нашому прикладі Excel коректно проаналізував вихідні дані і запропонував цей тип діаграми в рекомендованих. Якщо такої пропозиції від Excel не надійшло, то зробити комбіновану діаграму самостійно дуже просто:
- Виділяємо дані для побудови.
- Викликаємо діалогове вікно вставлення діаграми (вкладка "Вставлення", група "Діаграми", трикутник виклику діалогового вікна або команда "Рекомендовані діаграми" і переходимо на вкладку "Усі діаграми").
- Вибираємо тип "Комбінована".
- Угорі вибираємо підтип, а внизу вказуємо, для якого ряду яку діаграму побудувати.
- За потреби відмічаємо опцію використання додаткової осі (коли значення в рядах значно відрізняються).

Звісно, поєднати можна не всі типи діаграм — вибір обмежений різними типами гістограм і графіків.
Якщо ви використовуєте версію Excel старішу за 2013 р., то процес побудови комбінованої діаграми може відрізнятися.
Нові типи діаграм в Excel 2016
З виходом Office 2016 Microsoft додала в Excel шість нових типів діаграм: Деревоподібна, Сонячні промені, Каскадна, Гістограма (спеціально для статистичного аналізу), Парето та Коробка з вусами.
Усі нові діаграми будуть цікавими вузькому колу фахівців; наприклад, воронкоподібну діаграму Microsoft рекомендує використовувати для відображення кількості потенційних покупців на різних етапах продажів.

Наскільки така візуалізація наочна — кожен нехай вирішує сам.
Шаблони діаграм
Іноді на побудову та тонке налаштування діаграми витрачається багато часу. Якщо потрібно побудувати кілька однотипних і трудомістких діаграм, можна просто копіювати вихідну і заповнювати її щоразу новими даними.
Хоча такий спосіб має право на існування, для цих цілей краще зберегти вихідну діаграму як шаблон, а потім використовувати його так само, як і будь-який інший тип діаграми.
Щоб зберегти діаграму як шаблон, слід:
- Створити діаграму.
- Відформатувати діаграму, виконати налаштування.
- Виділити діаграму / викликати контекстне меню / вибрати команду "Зберегти як шаблон…"
- Дати ім'я шаблону.
Шаблони діаграм Excel мають розширення *.crtx. Усі шаблони, створені користувачем, можна знайти в групі Шаблони; відповідно, якщо потрібно змінити тип уже створеної діаграми, заздалегідь збережений шаблон слід шукати саме там.

3. Швидке форматування діаграм за допомогою стилів і макета
Відеоверсія
Текстова версія
Після створення діаграми ви можете змінити її параметри до невпізнання; детальні налаштування діаграми будуть розглянуті в наступному питанні, а зараз ми застосуємо до діаграми готові стилі оформлення і макети. За допомогою цих інструментів можна швидко підібрати підходящий вигляд діаграми.
Увесь необхідний інструментарій знаходиться на додатковій вкладці "Конструктор"; нам знадобляться команди груп "Макет діаграми" і "Стилі діаграм".

Стилі діаграм — це заздалегідь підготовлений набір візуальних параметрів діаграми.
Стандартні стилі діаграм відповідають за зовнішнє оформлення елементів діаграм, таких як: шрифти та колірна гама; додатково можна експериментувати з командою "Змінити кольори", яка дозволить у доповнення до стилю підібрати набір кольорів.
Стилі оформлення та колірні набори залежать від самої теми книги Excel.
Стилі діаграм не додають і не видаляють самі елементи діаграм — за це відповідає випадаюча команда Експрес-макет.

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

Для застосування певного стилю або набору кольорів, так само як і для додавання/видалення певних елементів діаграми, зовсім не обов'язково переходити на вкладку "Конструктор". Якщо виділити діаграму, поруч з'являться три спливаючі кнопки: верхня відповідає за видалення або додавання елементів діаграм, середня — за зміну стилю або колірної палітри.
Видалення будь-якого елемента діаграми можливо також натисканням клавіші Delete після попереднього виділення потрібного (або непотрібного, кому як більше подобається) елемента.

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

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

Прийнято також розрізняти область діаграми (chart area) та область побудови (plot area)

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

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

Форматування текстових міток
Текстові мітки — це підписи осей, назва діаграми, підписи рядів тощо; для форматування потрібно спочатку виділити потрібний елемент, а потім скористатися командами груп «Шрифт» і «Вирівнювання». За необхідності можна викликати діалогове вікно "Шрифт" і налаштувати ще більше параметрів оформлення текстової мітки. Форматування текстових міток особливо не відрізняється від форматування звичайного тексту в Excel.

Якщо потрібно змінити назву діаграми або підписи осей, зазвичай користувачі клікають по елементу і починають змінювати назву прямо в діаграмі. Так можна робити, але також можна клацнути по текстовій мітці (наприклад, назві діаграми), поставити знак "=" і клацнути по клітинці, де зберігається передбачувана назва. Такий спосіб зручний тим, що в клітинці назву можна міняти формулою й вона автоматично відобразиться на самій діаграмі.
Форматування рядів даних (серій даних)
За форматування рядів даних відповідає вкладка "Формат" із групи додаткових вкладок "Робота з діаграмами". Перш ніж приступати до форматування, необхідно навчитися правильно виділяти конкретний елемент ряду (стовпець, точку, сектор, рядок тощо).
Отже, перший клац лівою кнопкою миші по будь-якому елементу ряду виділить увесь ряд; виділити увесь ряд можна також за допомогою спадного списку з групи "Елементи діаграми", а друге клацання по конкретному елементу призведе вже до виділення лише цього елемента — відповідно, усе форматування буде застосоване лише до нього.
На замітку
Перше клацання по ряду даних виділить увесь ряд, а друге — вже конкретний елемент ряду. Його можна форматувати індивідуально.
Після виділення елемента подальше форматування здійснюється інструментами групи "Стилі фігур" вкладки "Формат". Тут можна вибрати один із передустановлених стилів для фігур або задати індивідуальні налаштування: товщина ліній, заливка й її колір, тіні, ефекти тощо. Глибше налаштування параметрів виконують за допомогою діалогового вікна форматування фігур, яке викликається клацанням по трикутнику у правому нижньому куті групи. До речі, назва цього вікна та перелік доступних у ньому команд для редагування залежать від того елемента, який наразі виділений.

Серед цікавих параметрів, які налаштовуються в цьому діалоговому вікні, — прозорість кольору заливки, що має не лише естетичну, а й практичну функцію. Прозорість буде корисна, якщо деякі ряди перекриваються.
Форматування легенди
Легенда на діаграмі Excel — це підпис ряду даних. Як і будь-який інший елемент діаграми, легенду можна видалити, розмістити в різних частинах діаграми або відформатувати.

Тонке налаштування зовнішнього вигляду легенди виконується у вже знайомому діалоговому вікні форматування легенди; тут, наприклад, можна змінити колір самого підпису у відповідність зі стилем ряду на графіку, додати дзеркальне відображення або інші ефекти.

Як правильно переміщати елементи всередині області діаграми
Переміщення елементів діаграми всередині області діаграми можна зробити двома шляхами: просто перетягти мишею або вибрати розташування командою "Додати елемент діаграми". Як краще обирати розміщення елемента?
Якщо потрібно зробити невелике переміщення, наприклад, зсунути легенду праворуч, а не по центру, це робиться мишею; якщо ж потрібно зробити кардинальні перестановки, наприклад, розмістити легенду не внизу області побудови, а вгорі під назвою або збоку, то краще зробити це за допомогою команди "Додати елемент діаграми", оскільки в цьому випадку всі інші елементи швидко підлаштуються під новий макет діаграми. Якщо виконувати такі маніпуляції мишею, доведеться неабияк потрудитися (змінити розмір і положення інших елементів вручну) і не факт, що результат вас влаштує.
Функція РЯД
На завершення розгляду цього питання слід зазначити функцію РЯД (SERIES) — вона з’являється в рядку формул при виділенні певного ряду даних.

Функція РЯД є неповноцінною функцією: її неможливо застосувати в клітинках робочого аркуша й як аргументи не можна використовувати інші функції, проте можна редагувати аргументи самої функції. Практична значущість такого прийому сумнівна, адже простіше все зробити через відповідні команди, але для загального розвитку ознайомитися буде корисно.
Синтаксис функції РЯД наступний:
=РЯД(имя_ряда; метки_категории; диапазон_данных;номер_ряда;размеры)
- имя_ряда – необов’язковий аргумент, містить посилання на клітинку, у якій записане ім’я ряду, що використовується в легенді. У нашому випадку це клітинка B1;
- метки_категории – необов’язковий аргумент, містить посилання на діапазон клітинок, де записані мітки для осі категорій. У нашому випадку це діапазон A2:A32;
- диапазон_данных – обов’язковий аргумент, містить посилання на діапазон клітинок з даними для ряду. Можна використовувати несуміжні діапазони — тоді їх потрібно розділити комами і взяти в круглі дужки;
- номер_ряда – обов’язковий аргумент, може бути лише константою (число безпосередньо в формулі). Цей аргумент має сенс лише за наявності кількох рядів даних на діаграмі і показує черговість промальовування; якщо оранжевій області присвоїти перший номер, то вона буде прихована за синьою областю;
- размеры – використовується лише для бульбашкових (пузиркових) діаграм, містить посилання на діапазон із розмірами бульбашок.
Як можна помітити, в функції РЯД посилання на діапазони й клітинки введені як абсолютні з обов’язковою вказівкою імені аркуша. На перший погляд це може трохи збити з пантелику, але якщо придивитися, то це стандартні абсолютні посилання з вказівкою аркуша. Тут, до речі, можуть бути й іменовані діапазони, але тоді потрібно обов’язково вказати ім’я книги.
5. Інструменти швидкого аналізу
Відеоверсія
Текстова версія
Починаючи з версії Excel 2013 з’явилася можливість провести швидкий візуальний аналіз даних певного діапазону. При виділенні діапазону в правому нижньому куті з’являється спеціальний значок; натискання на нього дозволяє викликати інструменти швидкого аналізу. Те саме можна зробити за допомогою гарячого поєднання Ctrl+Q.
Гаряче поєднання
Ctrl+Q викликає інструменти швидкого аналізу

Тут зосереджено одразу кілька інструментів швидкого аналізу виділеного діапазону.

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

Аналогічна ситуація і з іншими двома типами спарклайнів: «Гістограма» та «Виграш/програш».
Додавання спарклайнів на робочий аркуш
Перший і, мабуть, найшвидший спосіб створення спарклайнів — за допомогою інструмента швидкого аналізу, але побудувати спарклайни для виділеного діапазону можна й за допомогою вкладки «Вставлення», група «Спарклайни». У цьому випадку доведеться додатково вказати діапазон для виведення інфоліній; у випадку з інструментом швидкого аналізу спарклайни додаються праворуч від виділеного діапазону. Діапазон має відповідати кількості рядків.

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

Розглянемо спарклайни як інструмент швидкого аналізу детальніше.
Після додавання інфоліній на аркуш з'являється спеціальна вкладка «Конструктор» для роботи з міні-діаграмами.

Незважаючи на багатство налаштувань на вкладці, керованих параметрів не так уже й багато, в основному вони стосуються зовнішнього вигляду.
Команда «Змінити дані» дозволяє, окрім налаштувань даних для всіх або окремого спарклайна, налаштувати відображення пустих і прихованих клітинок. Ці параметри аналогічні тим, що для діаграм: за замовчуванням пусті значення ігноруються і лінія не переривається, але можна зробити так, щоб у місцях з пустими клітинками були розриви або щоб їх вважали нульовими значеннями.

У групі «Показати» можна ввімкнути відображення маркерів для окремих або всіх точок. Зручно, наприклад, переглянути максимум і мінімум.
Група «Стиль» повністю відведена для зміни зовнішнього вигляду спарклайнів; окремо можна налаштувати колір маркерів.
За допомогою останньої групи можна налаштувати так, щоб спарклайни сприймалися як єдине ціле і форматувалися відповідно, або їх можна розгрупувати — тоді кожен спарклайн можна налаштувати індивідуально.
Команда «Вісь» є найважливішою з точки зору подання даних.

За замовчуванням для кожного рядка будується своя міні-діаграма з власними максимальними та мінімальними значеннями, що зручно для загальної динаміки, але не для порівняння рядків. На рисунку в прикладі ті самі дані, тільки в нижньому прикладі встановлено однаковий масштаб осі для всіх спарклайнів. Через різницю в значеннях візуально простежити динаміку в рядках 2–10 неможливо.

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

Інструмент швидкого аналізу розмістив підсумки під даними, а спарклайни — зліва від даних.
Флешкартки
Так звані карти пам'яті: дивитеся на карту й намагаєтеся відповісти; по кліку на карту відобразиться правильна відповідь. Карти пам'яті корисні для запам'ятовування ключових позицій заняття. Усі заняття цього курсу супроводжені картами пам'яті.
Практика
У практичній частині ви знайдете завдання за пройденим заняттям. Після їх виконання у вас є можливість порівняти свій варіант із підготовленою викладачем відповіддю. Категорично рекомендується дивитися розв'язок лише після того, як ви виконали завдання самостійно. Для деяких завдань є невеликі підказки.
Завдання 1. Побудова простого графіка
- Відкрити книгу з попереднього заняття "Завдання 5.4 виконано".
- Побудувати графік динаміки заробітку в доларах США по днях.
- Додати назву діаграми, підписи осей, таблицю даних і легенду.
- Стилістичне оформлення підібрати самостійно. Порівняти отриманий результат із наведеним нижче.
- Зберегти книгу під іменем "Завдання 6.1 виконано".

Переглянути розв'язок
Завдання 2. Закріплення навичок використання шаблонів
- Відкрити книгу з попереднього завдання "Завдання 6.1 виконано".
- Створити новий аркуш і перейменувати його в «Мордочка миші» та ввести такі дані для бульбашкової діаграми:
- Побудувати бульбашкову діаграму на основі даних із таблиці. Результат порівняти з рисунком:
- Зберегти собі на комп'ютер шаблон діаграми Excel "Мордочка миші шаблон".
- Застосувати збережений шаблон до діаграми. Перефарбувати вуха миші в зелений колір і порівняти результат із рисунком:
- Зберегти свою діаграму як шаблон, придумати довільне ім'я.
- Зберегти книгу під іменем "Завдання 6.2 виконано".



Переглянути розв'язок
Завдання 3. Побудова кругової та кільцевої діаграми
На основі даних про кількість переглядів сайту з мобільних пристроїв необхідно побудувати кругову діаграму з вторинною лінійчастою. На вторинній має бути показано частки мобільних пристроїв по моделях.
- Відкрити книгу з попереднього завдання "Завдання 6.2 виконано".
- Створити додатковий аркуш, перейменувати його в «Статистика по пристроях» і внести дані з таблиці:
- Створити кругову діаграму з вторинною. На головній діаграмі повинні бути дві області: десктоп і мобільні, на вторинну кругову вивести типи мобільних пристроїв.
- У порожню клітинку аркуша ввести бажану назву діаграми.
- Додати елемент назва діаграми і зв'язати його з назвою, яке було введене в вільну клітинку на аркуші.
- Порівняти отриманий результат з рисунком:
- Зберегти книгу під іменем "Завдання 6.3 виконано".

Пояснення: мається на увазі не вводити назву вручну, а зробити посилання на клітинку аркуша.

Переглянути розв'язок
Завдання 4. Використання інструментів швидкого аналізу
- Відкрити книгу зі статистикою виручки по місяцях у розрізі працівників «Виручка за рік».
- Відкрити книгу з попереднього завдання «Завдання 6.3 виконано».
- Скопіювати аркуш із книги «Виручка за рік» у книгу «Завдання 6.3 виконано».
- Закрити книгу «Виручка за рік».
- За допомогою інструментів швидкого аналізу в підсумковий рядок додати суму виручки по місяцях.
- За допомогою інструментів швидкого аналізу в найправіший стовпець додати спарклайни, що відображають динаміку.
- Спарклайни з 6-го по 10-й зробити окремою групою. Має бути 2 групи спарклайнів: з 1-го по 5-й і з 6-го по 10-й.
- Для другої групи спарклайнів змінити їхній тип на «Гістограма».
- За допомогою умовного форматування виділити по двоє кращих працівників (з найвищими показниками) у кожному місяці.
- Порівняти результат роботи з рисунком:
- Зберегти книгу під іменем «Завдання 6.4 виконано».
Підказка
Підказка: скопіювати найшвидше перетягуванням ярлика аркуша з утримуваною клавішею Ctrl або за допомогою контекстного меню по ярлику аркуша.
Підказка
Підказка: виділити спарклайни 6–10, вибрати команду «розгрупувати», а потім знову їх виділити й вибрати «згрупувати». Команди «Групувати/Розгрупувати» знаходяться на додатковій вкладці Конструктор (Робота зі спарклайнами).
