Цей практичний посібник пояснює, що таке зведена діаграма, як її створювати та налаштовувати. Ми також побачимо різницю між зведеною діаграмою та таблицею:
Діаграми вважаються одним з найкращих способів представлення звіту. Вони допомагають нам зрозуміти і простіше проаналізувати дані. Зведені діаграми в Excel дають нам візуальне представлення даних у різних варіантах.
У цьому уроці ми дізнаємося всі деталі, необхідні для роботи зі зведеними діаграмами в Excel. Створення діаграм різних типів, форматування їх макета, додавання фільтрів, додавання користувацьких формул, а також використання формату однієї діаграми для інших діаграм, що належать до різних зведених таблиць.
Що таке зведена діаграма в Excel
Зведена діаграма в Excel - це візуальне представлення даних. Вона дає вам повну картину ваших необроблених даних. Вона дозволяє аналізувати дані за допомогою різних типів графіків і макетів. Вважається найкращою діаграмою під час бізнес-презентації, яка включає в себе великі обсяги даних.
Зведена діаграма в порівнянні з таблицею
Зведена таблиця надає нам спосіб узагальнення великих даних у сітчастій матриці. Ви можете вибрати поля, які ви хочете використовувати в таблиці для рядків і стовпців. Зведена діаграма надає нам графічне представлення зведеної таблиці. Ви можете вибрати один з декількох макетів і типів діаграм.
Ця діаграма також підсумовує дані. Ви можете створити як зведену таблицю, так і діаграму для джерела даних і працювати з ними одночасно. Це означає, що зміни, внесені в зведену таблицю, відображатимуться на діаграмі і навпаки.
Джерело даних
Нижче наведено зразок джерела даних, який буде використано в цьому посібнику. Клацніть на посилання, щоб завантажити зведену діаграму "Зразок_даних
| Ідентифікатор замовлення | Дата замовлення | Назва продукту | Регіон | Місто | Кількість | Загальна ціна |
|---|---|---|---|---|---|---|
| 1 | 03-01-2020 | Просте печиво | Північ | Нью-Йорк | 33 | 444.66 |
| 2 | 04-02-2012 | Цукрове печиво | Південь | Ліма | 432 | 346.33 |
| 3 | 05-04-2018 | Вафлі | Схід | Бостон | 33 | 32.54 |
| 4 | 06-05-2019 | Шоколад | Захід | Оук Ленд | 245 | 543.43 |
| 5 | 07-07-2020 | Морозиво. | Північ | Чикаго | 324 | 223.56 |
| 7 | 09-09-2020 | Просте печиво | Схід | Вашингтон | 32 | 34.4 |
| 8 | 10-11-2020 | Цукрове печиво | Захід | Сіетл | 12 | 56.54 |
| 9 | 11-12-2017 | Вафлі | Північ | Торонто | 323 | 878.54 |
| 10 | 12-14-2020 | Шоколад | Південь | Ліма | 232 | 864.74 |
| 11 | 01-15-2020 | Морозиво. | Схід | Бостон | 445 | 457.54 |
| 13 | 03-18-2018 | Солоне печиво | Північ | Нью-Йорк | 5454 | 34546 |
| 14 | 04-18-2017 | Сирне печиво | Південь | Ліма | 5653 | 3456.34 |
| 15 | 05-19-2016 | Солоне печиво | Схід | Вашингтон | 4 | 74.4 |
| 16 | 06-20-2015 | Сирне печиво | Захід | Оук Ленд | 545 | 876.67 |
Створіть зведену діаграму
Існує 2 способи створення зведеної діаграми в Excel.
#1) Створити з джерела даних
Ми можемо створити діаграму безпосередньо з таблиці даних без зведеної таблиці.
Щоб досягти цього, виконайте наступні кроки.
#1) Виберіть будь-яку комірку в таблиці.
#2) Перейдіть до Вставити -> Зведену діаграму
#3) Ви можете створити новий аркуш або вказати діапазон таблиці, в якому ви хочете розмістити діаграму, у розділі "Існуючий аркуш".
#4) Натисни ОК.
Буде створено порожню зведену діаграму і пов'язану з нею зведену таблицю. Ви можете додати потрібні поля для створення звіту і діаграми.
#2) Створити зі зведеної таблиці
Якщо ви вже створили зведену таблицю, ви можете використати її для створення зведеної діаграми. Ми створили зразок зведеної таблиці, як показано нижче.
Створити діаграму.
#1) Виділіть будь-яку клітинку зведеної таблиці.
#2) Перейдіть до Вставка-> Зведена діаграма
#3) Ви побачите список доступних графіків, виберіть потрібний графік.
#4) Натисни "ОК".
Це створить діаграму з даними, взятими зі зведеної таблиці. Приклад зведеної діаграми показано нижче.
Зауважте: Ви також можете скористатися комбінацією клавіш F11. Клацніть на зведеній таблиці та натисніть F11 на клавіатурі.
Налаштування діаграми
Ви можете налаштувати діаграму за допомогою значка "+" та піктограми фарби, розташованої праворуч від діаграми.
+ Кнопка - Дозволяє додавати або видаляти елементи діаграми, такі як заголовки, лінії сітки, легенди тощо, а також визначати їхнє розташування.
Ви можете додати назву діаграми, згадати назви осей і т.д. Для прикладу ми додали назву діаграми і назву осі.
Стиль діаграми - Ви можете змінити стиль і колір діаграми, натиснувши на піктограму пензля.
Ви також можете змінити колір діаграми за власним бажанням у розділі "Колір".
Рекомендовані графіки
Excel надає нам рекомендовані зведені діаграми, які дозволяють швидко вибрати тип зведеної діаграми, що відповідає вашим бізнес-вимогам.
#1) Виберіть таблицю джерела даних.
#2) Перейдіть до Вставити -> Рекомендовані графіки .
#3) Клацніть на Рекомендовані графіки.
#4) Натисніть на потрібний графік.
#5) Натисни ОК.
Отримані зведена таблиця і діаграма будуть створені на новому аркуші, і ви зможете надалі налаштувати їх за потреби.
Поля зведеної діаграми
Вона має 4 поля, як показано нижче.
1. Фільтри: Поля під ним дають нам можливість додавати фільтри для звітів.
2. Легенди (серія) Поля під цим полем представляють заголовки стовпців у зведеній таблиці.
3. Вісь (Категорії): Це рядки зведеної таблиці. Ці поля показані на вісьовій діаграмі в стовпчику.
4. Цінності: Використовується для відображення підсумкових числових значень.
Інструменти зведених діаграм
Проаналізуй: Існують різні варіанти, щоб зробити діаграму більш зручною для користувача.
Назва діаграми: Це назва діаграми, яка використовується під час написання коду VBA, а також на панелі виділення. Вона доступна в Excel 2010 і новіших версіях.
Варіанти: З'явиться діалогове вікно Параметри зведеної таблиці, в якому ви можете задати макет і формат, показати/приховати загальний підсумок, встановити параметри сортування, відображення тощо.
Активне поле: Ви можете змінити назву стовпця в таблиці. Наприклад , Загальна сума до Фінальної суми і т.д., і те ж саме оновиться в Таблиці та Графіку.
Розширити поле: Це використовується для автоматичного розгортання всіх значень.
Якщо у вас є кілька полів, таких як Роки, Квартали і Дата, то замість того, щоб розгортати кожне з них окремо, ви можете натиснути на Розгорнути поле.
Поле колапсу: Натисніть навпроти кнопки Розгорнути поле, щоб згорнути розгорнуті поля і представити компактну діаграму.
Розгорнути приклад
Приклад згортання
Зауважте: Припустимо, у вас є лише одне поле в Рядах, тоді, натиснувши на кнопку Розгорнути поле, ви отримаєте діалогове вікно з усіма полями і зможете вибрати потрібне поле. Вибране поле буде додано до розділу Ряди, і діаграма буде автоматично оновлена.
Вставна слайсерка
Ви можете вставити зріз у діаграму так само, як і у зведену таблицю.
Щоб інтегрувати слайсер з діаграмою, виконайте наведені нижче дії.
- Натисніть на зведену діаграму.
- Перейдіть на сторінку Вкладка "Аналіз" -> "Вставити зріз .
- У діалоговому вікні Вибір полів вам потрібно створити зрізки.
- Натисни ОК.
Це призведе до вставки вікна слайсера, як показано нижче. Ми бачили, як користуватися слайсером у нашому попередньому уроці.
Вставити часову шкалу
Ви можете вставити часову шкалу в діаграму так само, як і в зведену таблицю.
Щоб інтегрувати шкалу часу з діаграмою, виконайте наведені нижче дії.
- Натисніть на зведену діаграму.
- Перейдіть на сторінку Вкладка "Аналіз" - "Вставити часову шкалу".
- Виберіть потрібне поле Дата.
- Натисни ОК.
Це призведе до вставки шкали часу, як показано нижче. Ми бачили, як користуватися шкалою часу в нашому попередньому уроці.
Результат на основі часової шкали оновлюється як у зведеній таблиці, так і на діаграмі.
Підключення фільтра
Ти можеш зв'язати слайсер або часову шкалу до кількох зведених діаграм. Наприклад, ми створили 2 зведені таблиці та 1 зрізку. Ви застосовуєте зрізку до обох діаграм.
- Клацніть зведену діаграму, до якої зрізний пристрій наразі не підключено.
- Перейдіть до Аналізувати -> Фільтрувати з'єднання
- Виберіть слайсер, який потрібно підключити.
- Натисни Гаразд.
Тепер ви можете обробляти обидва графіки за допомогою одного слайсера.
Розрахунки
Якщо ви хочете додати користувацькі формули, ви можете зробити це за допомогою поля обчислення.
Приклад:
#1) Виберіть зведену діаграму, до якої потрібно додати кастомні формули.
#2) Перейдіть до Аналіз - Поля - Елементи - Множини
#3) Виберіть Обчислювані поля.
#4) У полі "Ім'я" введіть бажане ім'я.
#5) У полі Формула додайте власну формулу. Якщо ви надаєте знижку 10% від загальної суми, ви можете додати формулу, як показано нижче.
#6) Зведена таблиця, зведені поля та діаграма будуть оновлені відповідно.
Оновити
Щоразу, коли ви змінюєте значення в джерелі даних, клацніть будь-де на зведеній діаграмі, клацніть правою кнопкою миші і виберіть "Оновити" або перейдіть в меню "Аналіз" - "Оновити". Оновлення зведеної таблиці також оновить діаграму.
Змінити джерело даних
Щоразу, коли ви додаєте нові рядки до джерела даних, діаграма не буде відображати додані рядки, оскільки ми визначили діапазон під час створення діаграми.
Включити нові рядки:
- Клацніть будь-де на зведеній діаграмі.
- Перейдіть до Проаналізуйте - змініть джерело даних
- З'явиться діалогове вікно Зміна джерела даних зведеної таблиці, в якому ви можете ввести новий діапазон даних.
- Натисни Гаразд.
Переконайтеся, що ви виконали наведені вище дії для всіх діаграм окремо.
Чисто
За допомогою кнопки Очистити ви можете очистити всю зведену діаграму. Це буде порожня діаграма і таблиця.
- Натисніть на зведену діаграму
- Проаналізувати - Очистити - Очистити все
Ви також можете очистити всі застосовані фільтри за допомогою Аналізувати - Очистити - Очистити фільтри
Діаграма переміщення
Після створення діаграми ви можете перемістити її в потрібне місце.
Виконайте наведені нижче кроки:
- Натисніть на зведену діаграму.
- Перейдіть до Аналіз -> Діаграма переміщення
- Виберіть потрібну опцію в діалоговому вікні:
- Новий аркуш: Аркуш буде автоматично створено, і на ньому відобразиться діаграма.
- Ввести об'єкт: Ви можете вибрати один з доступних аркушів, і діаграма буде переміщена на вибраний аркуш.
Список полів: Ви можете показати/приховати панель "Поля зведеної діаграми".
Польові кнопки: Ви можете показати/приховати на діаграмі поле легенди, поле осі, поле значення, фільтр звіту тощо.
Дизайн
На цій вкладці є кілька варіантів оформлення діаграми.
Додати елемент діаграми: Це дає нам ті ж опції, що й при натисканні на кнопку + поруч зі зведеною діаграмою. Вони допомагають нам додавати елементи до діаграми, такі як заголовок, смуга помилок тощо.
Швидкий макет: Ви можете змінити макет за замовчуванням і вибрати один з доступних попередньо визначених макетів. Наприклад, ми перемістили макет регіону вгорі замість правого боку.
Змінити кольори: Виберіть різні кольори для вашої діаграми.
Стиль діаграми: Виберіть стиль для вашої діаграми з-поміж доступних.
Перемикання рядка/стовпчика: Ви можете легко перемикати рядки і стовпці одним клацанням миші, і зведена таблиця і діаграма будуть оновлені автоматично.
Перед перемиканням рядка/стовпця
Після перемикання рядка/стовпця
Виберіть "Дані": Припустимо, ви витратили багато часу на форматування зведеної діаграми відповідно до стандартів вашої компанії, і всі ваші діаграми повинні мати однаковий формат. Тоді ця опція стане в нагоді. Ви не можете безпосередньо скопіювати зведену діаграму і змінити джерело даних. Потрібно виконати кілька кроків.
#1) Виберіть потрібну зведену діаграму та скопіюйте область діаграми.
#2) Відкрийте новий робочий зошит. Файл -> Нова книга
#3) Вставте скопійовану діаграму. Ви можете помітити, що в рядку меню написано "Інструменти діаграми", а не "Інструменти зведеної діаграми".
#4) Тепер виберіть область діаграми і натисніть кнопку "Вирізати".
#5) Перейдіть до книги, в якій ви хочете використовувати цю діаграму.
#6) Зауважте: У вас вже має бути створена зведена таблиця.
#7) Вставте діаграму з кроку 4.
#8) Перейдіть до пункту Дизайн у розділі Інструменти діаграми. Натисніть на вкладку Вибрати дані.
#9) Клацніть на будь-яку клітинку зведеної таблиці.
Зведену діаграму буде створено з даними, що містяться в новій зведеній таблиці, але формат залишиться таким самим, як і раніше. Ви можете змінити Вісь і Легенду для нової таблиці за потреби.
Нижче показана діаграма для нової зведеної таблиці.
Змінити тип діаграми: Ви можете змінити тип стовпчастої діаграми за замовчуванням на потрібний тип, як показано нижче.
Діаграма автоматично оновиться відповідно до вибору.
Кругова діаграма
Гістограма
Формат
Вони в основному використовуються для спеціального форматування тексту всередині діаграми.
Поточний вибір: Буде показано всі елементи, присутні в таблиці, і ви зможете вибрати той, для якого ви хочете змінити стиль форматування. Наприклад, Виберемо заголовок діаграми і змінимо його стиль.
#1) Виберіть "Назва діаграми" у випадаючому списку.
#2) Натисніть "Вибір формату".
#3) На правій панелі відкриється вікно Формат назви діаграми.
#4) Вибирайте колір, стиль, рамку тощо за власним бажанням.
Після кількох базових форматувань заголовок діаграми виглядатиме так, як показано нижче.
Скинути до відповідного стилю: Це скине всі зміни і поверне стиль за замовчуванням.
Вставити фігури: Ви можете вставити фігури, такі як лінії, стрілки, а також текстове поле для кращого пояснення.
Форма, стиль: Ви можете вибрати різні стилі для області діаграми. Виділіть область, стиль якої ви хочете змінити, і натисніть на стиль.
Після застосування стилів до всієї діаграми стовпчики і рядки виглядають так, як показано нижче.
Домовилися: Якщо є кілька зведених діаграм і вони накладаються одна на одну за цими параметрами.
Вперед!
- Виберіть діаграму, яку ви хочете вивести на екран.
- Натисніть на опцію "Пересунути вперед", щоб пересунути діаграму на один крок вперед.
"Вивести на передній план": Ця опція підніме ваш графік над усіма іншими графіками.
Надіслати назад
- Виберіть діаграму, яку ви хочете відправити назад.
- Натисніть на опцію відправити назад, щоб відправити графік на один рівень назад.
Відправте назад: Використовується для надсилання вибраного графіка назад до всіх інших графіків.
Панель вибору
Ви можете визначити видимість діаграми за допомогою панелі вибору. На цій сторінці показано всі доступні діаграми та зрізів, і ви можете натиснути на піктограму ока, щоб вирішити, чи має цей елемент бути видимим на аркуші, чи ні.
Розмір: Використовується для налаштування висоти, ширини зведеної діаграми, висоти шкали, ширини шкали тощо.
Поширені запитання
З #1) Як створити зведену діаграму в Excel?
Відповідай: Існує 2 способи створення зведених діаграм.
#1) Створити з джерела даних
- Виберіть будь-яку комірку в таблиці джерел даних.
- Перейдіть до Вставити -> Зведену діаграму
- Виберіть діапазон.
Це створить порожню зведену таблицю і зведену діаграму.
#2) Створити зі зведеної таблиці
Якщо у вас вже є зведена таблиця:
- Виберіть будь-яку клітинку зведеної таблиці.
- Перейдіть до Вставити -> Зведену діаграму
- Ви побачите список доступних графіків, виберіть потрібний графік.
Це створить діаграму з даними, що стосуються зведеної таблиці.
Q #2) Чому ми використовуємо зведену діаграму в Excel?
Відповідай:
Використання зведених діаграм має багато переваг:
- Це ефективний і простий спосіб представлення даних у графічному вигляді.
- Ви можете легко узагальнити дані, перетягнувши потрібні поля в будь-який з 4 доступних розділів таблиці.
- Надає ефективний спосіб перетворення необроблених даних в організований формат, маніпулюючи ними за допомогою легкої фільтрації, вирівнювання, кастомізації, обчислень тощо.
З #3) Як відформатувати зведену діаграму?
Відповідай: Ви можете відформатувати діаграму за допомогою різних опцій, доступних в розділі Інструменти зведеної діаграми. Тут ви можете додати нові поля, змінити колір, шрифт, фон тощо, щоб зробити вашу діаграму більш інтерактивною та презентабельною. Клацніть будь-де на зведеній діаграмі, щоб відкрити розділ Інструменти.
З #4) Чи можна додати зріз до зведених діаграм?
Відповідай: Так, до зведених діаграм можна додавати зрізи та часові шкали. Це допоможе нам фільтрувати як діаграму, так і пов'язану з нею зведену таблицю одночасно.
- Натисніть на зведену діаграму.
- Перейдіть на сторінку Вкладка "Аналіз" -> "Вставити зріз .
- У діалоговому вікні Вибір полів ви хочете створити зрізки.
- Натисни ОК.
Потім ви можете додати з'єднання Фільтр, щоб зв'язати один зріз з кількома діаграмами.
Висновок
У цьому уроці ми дізналися про зведені діаграми Excel. Це візуальне представлення зведеної таблиці або джерела даних. Воно допомагає нам переглядати зведені дані в графічному форматі за допомогою різних типів діаграм.
Існує безліч варіантів фільтрації, форматування, налаштування діаграм і додавання різних макетів за вашим бажанням. Зведена діаграма в Excel корисна, коли ви маєте справу з великою кількістю даних. Вона дуже корисна під час бізнес-презентації з фільтрацією в один клік, фільтрацією за часом, індивідуальними розрахунками тощо.