Як створити панельну діаграму в Excel

Цей підручник покаже, як створити панельну діаграму у всіх версіях Excel: 2007, 2010, 2013, 2016 та 2022 роки.

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

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

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

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

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

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

Починаємо

Щоб проілюструвати кроки, які ви повинні виконати, нам потрібно почати з деяких даних. Як ви вже здогадалися, ми збираємось порівняти історичний досвід франчайзі двох компаній, Orange Ltd та Banana Ltd, у чотирьох різних штатах-Айові, Міннесоті, Техасі та Юті.

З огляду на все сказане, розгляньте таку таблицю:

Розглянемо кожну колонку більш детально.

• Штат - У цьому стовпці представлені категорії, за якими діаграма буде розділена на менші діаграми (панелі). У нашому випадку кожна міні -лінійна діаграма ілюструє динаміку продуктивності для кожного із штатів.
• Рік - Цей стовпець визначає масштаб горизонтальної осі. Значення та форматування мають бути ідентичними на всіх панелях.
• Orange Ltd та Banana Ltd - Це ваші справжні цінності. Ми збираємось використовувати лише два набори даних для цілей ілюстрації, але з методом, показаним у цьому посібнику, небо є межею.

Зрештою, ми хочемо, щоб наші дані виглядали так:

Технічно ви можете маніпулювати даними вручну (якщо так, перейдіть до Крок 5). Цей підхід може бути кращим, якщо ваші дані прості. В іншому випадку ми покажемо вам, як використовувати зведену таблицю для маніпулювання даними у необхідному форматі.

Отже, давайте зануримось прямо.

Крок №1: Додайте роздільники.

Перш ніж створити панельну діаграму, потрібно правильно організувати свої дані.

По -перше, праворуч від ваших фактичних даних (колонка Е), налаштуйте допоміжний стовпець під назвою “Розділювач."Мета цього стовпця-розділити дані на дві чергуються категорії, виражені зі значеннями 1 і 2, щоб закласти основи для майбутньої зведеної таблиці.

Введіть "1”У кожну відповідну клітинку стовпця Розділювач які належать до першої категорії, штат Айова (E2: E7). Потім введіть «2”У всі відповідні клітини, які відносяться до другої категорії, штат Міннесота (E8: E13).

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

Крок 2: Додайте зведену таблицю.

Як тільки ви завершите Крок 1, створити зведену таблицю.

1. Виділіть будь -яку клітинку в діапазоні набору даних (A1: E25).
2. Перейдіть до Вставити вкладка.
3. Виберіть "Зведена таблиця.”

Коли Створіть зведену таблицю з'явиться діалогове вікно, виберіть "Існуючий робочий аркуш,”Виділіть будь -яку порожню клітинку біля ваших фактичних даних (G1) і натисніть «В ПОРЯДКУ.”

Крок №3: Створення макета зведеної таблиці.

Відразу після створення зведеної таблиці файл Поля зведеної таблиці з'явиться панель завдань. На цій панелі завдань змістіть елементи списку полів у такий порядок-важливий порядок-щоб змінити макет нової зведеної таблиці:

1. Перемістити "Держава”Та“Рік"До"Ряди.”
2. Перемістити "Розділювач"До"Колонки.”
3. Перемістити "ТОВ "Оранж"”Та“ТОВ "Банан""До"Цінності.”

На даний момент зведена таблиця має виглядати приблизно так:

Тепер змініть тип макета та видаліть зайві елементи таблиці.

1. Виберіть будь -яку клітинку у зведеній таблиці (G4: L26).
2. Перейдіть до Дизайн вкладка.
3. Натисніть «Макет звіту.”
4. Виберіть "Показати у формі таблиці.”

Все ще в Дизайн натисніть вкладку «Загальні підсумки"Значок і виберіть"Вимкнено для рядків і стовпців»Зі спадного меню.

Далі натисніть «Проміжні підсумки"І виберіть"Не показувати проміжні підсумки.”

Після того, як ви виконали всі кроки, зведену таблицю слід відформатувати таким чином:

Крок №4: Витягніть дані зі зведеної таблиці.

Оскільки ви не хочете створювати зведену діаграму (ні, ви цього не робите), настав час відокремити дані від зведеної таблиці. Скопіюйте всі значення зі зведеної таблиці (G4: L27) у порожні клітинки поруч із нею. (Порожній стовпець між наборами даних допоможе уникнути плутанини.)

Рядок заголовка (P3: S3) у вашій новоствореній таблиці відповідає за відображення елементів легенди діаграми, тому давайте також швидко їх додамо.

Оскільки зведена таблиця ефективно розбиває дані на дві квазілінійні діаграми, ваш рядок заголовка має виглядати так:

Крок 5: Створіть лінійну діаграму.

Нарешті, побудуйте звичайну лінійну діаграму і подивіться, що станеться.

1. Виділіть таблицю, що містить дані, витягнуті зі зведеної таблиці (N3: S27).
2. Перейдіть до Вставити вкладка.
3. Натисніть кнопку «Вставити лінійну або діаграму області”.
4. Виберіть "Лінія.”

Крок 6: Зробіть кольори ліній послідовними.

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

Клацніть правою кнопкою миші будь-який ряд даних і виберіть "Форматувати серію даних.”

В Форматувати серію даних панелі завдань, виконайте наведені нижче дії.

1. Перейдіть до Заповнити & Лінія вкладка.
2. Під "Лінія,"Виберіть"Суцільна лінія.”
3. Натисніть кнопку «Колір контуру”Та виберіть колір з палітри. (Повторіть для кожного рядка.)
4. Двічі перевірте легенду діаграми, щоб переконатися, що ви правильно підібрали кольори.

Крок №7: Створіть лінії розділення.

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

Отже, ми будемо використовувати вертикальні смуги помилок, які будуть розташовані між кожною панеллю, прямо там, де вісь Y перетинає вісь категорії.

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

1. Налаштуйте окремий фіктивний стіл точно так само як ви бачите на скріншоті нижче. Залишити клітинку U4 порожній, щоб все працювало, і введіть "Розділювачі”В V4.
2. Введіть "0”В V5 і скопіюйте його. Кількість рядків, що містять значення в таблиці, означає, скільки смуг помилок буде врешті -решт створено. У цьому випадку нам знадобляться три смуги помилок, тому буде три клітинки з “0" у них.
3. Введіть “=COUNTA (O4: O9) +0,5”В U5.

Формула обчислює кількість значень шкали осі x, використаних для однієї панелі, і обчислює її середню точку, щоб розташувати лінії на осі X прямо там, де зустрічаються дві осі.

Тепер введіть “=U5+COUNTA (O4: O9) » в U6 і скопіюйте його в U7.

Налаштувавши допоміжну таблицю, вставте ці дані у свою панельну діаграму.

1. Виділіть усі значення в допоміжній таблиці, крім рядка заголовка (U4: V7).
2. Виберіть область діаграми.
3. Перейдіть до Додому вкладка.
4. Натисніть «Вставити.”
5. Виберіть "Спеціальна вставка.”

У діалоговому вікні, що з’явиться, виконайте кілька простих кроків:

1. Під "Додати клітинки як,"Виберіть"Нова серія.”
2. Під "Значення (Y) у,"Вибрати"Колонки.”
3. Перевірте "Назви серій у першому рядку”Та“Категорії (мітки X) у першій колонці”Коробки.
4. Натисніть «В ПОРЯДКУ.”

Крок #8: Змініть тип діаграми фіктивної серії.

Клацніть правою кнопкою миші на нещодавно доданому ряду даних (Серія «Розділювачі») і виберіть "Змінити тип діаграми серії.”

За Серія «Розділювачі» зміна "Тип діаграми" до “Скаттер з прямими лініями.”

Крок 9: Змініть вторинні осі.

Спочатку видаліть вторинну горизонтальну вісь. Клацніть правою кнопкою миші на цифрах у верхній частині діаграми та виберіть "Видалити.”

Потім клацніть правою кнопкою миші на вторинній вертикальній осі вздовж правої сторони діаграми та виберіть "Формат осі.”

В Формат осі панель завдань, встановлена ​​в камені діапазони масштабування осі:

1. Перейдіть до Параметри осі вкладка.
2. Змінити Мінімальні межі значення "0.”
3. Встановіть Максимальні межі значення "1.”

На тій же вкладці прокрутіть униз до Мітки розділ і зміна "Положення мітки"До"Жодного”, Щоб приховати масштаб осі-якщо ви просто видалите його, це зіпсує діаграму.

Крок #10: Додайте смуги помилок.

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

1. Виберіть Серія «Розділювачі».
2. Натисніть на Елементи діаграми значок.
3. Натисніть стрілку поруч "Панелі помилок".
4. Виберіть "Більше опцій.”

В Панелі помилок форматування панелі завдань, змініть роздільники.

1. Перейдіть до Параметри панелі помилок вкладка.
2. Під "Напрямок,"Вибрати"Плюс.”
3. Під "Кінцевий стиль,"Виберіть"Без ковпачка.”
4. Під "Сума помилки,"Змінити"Фіксоване значення"До"1.”

Крок #11: Сховайте допоміжні елементи діаграми.

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

Давайте спочатку розберемося з фіктивними серіалами. Виберіть Серія «Розділювачі» перейдіть до Форматувати серію даних панелі завдань, а в Опції серії на вкладці виберіть "Без лінії.”

Тепер видаліть горизонтальні смуги помилок і кожен елемент легенди, збережіть для “ТОВ "Оранж"”Та“ТОВ "Банан"”, Вибравши кожен елемент, клацнувши правою кнопкою миші та вибравши“Видалити. ” Наприкінці цього кроку ваша панельна діаграма має виглядати так:

Змініть назву діаграми, і ваша приголомшлива панельна діаграма готова до роботи!