Як показати на діаграмі Excel дані з двох або більше робочих аркушів

Деякий час тому ми опублікували першу частину нашого самовчителя по, в якій була дана докладна інструкція, як побудувати графік в Excel. І найпершим питанням, заданим в коментарях, був: “Як показати на діаграмі дані, розташовані на різних робочих аркушах?“. Хочу подякувати читача, який задав це чудовий питання!

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

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

Припустимо, що на кількох аркушах Excel містяться дані про дохід за кілька років, і потрібно побудувати діаграму за цими даними, щоб показати загальну тенденцію.

1. Створіть діаграму за даними першого аркуша

Відкрийте перший робочий лист Excel, виділіть дані, які потрібно відобразити на діаграмі, відкрийте вкладку вставка (Insert) і в розділі діаграми (Charts) виберіть потрібний тип діаграми. Для нашого прикладу ми виберемо Об’ємна гістограма з накопиченням (Stack Column).

2. Додайте другий ряд даних з іншого листа

Клацніть по щойно створеної діаграмі, щоб на Стрічці меню з’явилася група вкладок Робота з діаграмами (Chart Tools), відкрийте вкладку конструктор (Design) і натисніть кнопку вибрати дані (Select Data). Або натисніть по іконці фільтри діаграми (Chart Filters) праворуч від діаграми і в самому низу меню, що, клікніть посилання вибрати дані (Select Data).

У діалоговому вікні Вибір джерела даних (Select Data Source) натисніть кнопку Додати (Add).

Тепер додамо другий ряд даних з іншого робочого листа. Цей момент дуже важливий, тому уважно дотримуйтесь інструкцій. Після натискання кнопки Додати (Add) відкриється діалогове вікно зміна ряду (Edit Series), тут потрібно натиснути іконку вибору діапазону поруч з полем значення (Series values).

Діалогове вікно зміна ряду (Edit Series) згорнеться. Клацніть по ярличку листа, що містить наступну частину даних, які потрібно показати на діаграмі Excel. При перемиканні на інший аркуш діалогове вікно зміна ряду (Edit Series) залишиться на екрані.

На другому аркуші виділіть стовпець або рядок даних, які потрібно додати на діаграму Excel, і знову на іконку вибору діапазону, щоб діалогове вікно зміна ряду (Edit Series) повернулося до початкового розміру.

Тепер клацніть по іконці вибору діапазону поруч з полем ім’я ряду (Series name) і виберіть клітинку, яка містить текст, який потрібно використовувати в якості імені ряду даних. Знову клацніть по іконці вибору діапазону, щоб повернутися до початкового діалогового вікна зміна ряду (Edit Series).

Перевірте посилання, які тепер з’явилися в полях ім’я ряду (Series name) і значення (Series values), і натисніть ОК.

Як видно на малюнку вище, ми зв’язали ім’я ряду з осередком B1, в якій міститься заголовок стовпчика. Замість посилання на заголовок стовпчика, можна ввести ім’я у вигляді текстового рядка, укладеної в лапки, наприклад:

= “Другий ряд даних”

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

3. Додайте ще більше рядів даних (за бажанням)

Якщо в діаграмі повинні бути показані дані більш, ніж з двох робочих аркушів, то повторіть попередній етап для кожного ряду даних, який потрібно додати на діаграму. Коли закінчите, натисніть ОК в діалоговому вікні Вибір джерела даних (Select Data Source).

Я для прикладу додав третій ряд даних, і моя діаграма тепер виглядає так:

4. Налаштуйте і поліпшите діаграму (за бажанням)

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

Виділіть діаграму, натисніть іконку елементи діаграми (Chart Elements) у вигляді зеленого хреста біля правого верхнього кута діаграми, і відзначте галочкою потрібні параметри:

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

Створення діаграми з підсумкової таблиці

Рішення, показане вище, зручно тільки в тому випадку, якщо дані, які потрібно відобразити на діаграмі, на всіх робочих аркушах збудовані в однаковому порядку, тобто в першому рядку – Oranges, у другий – Apples і т.д. В іншому випадку графіки перетворяться на щось нерозбірливе.

В даному прикладі розташування даних однаково на всіх трьох аркушах. Якщо потрібно побудувати графік з набагато більших таблиць, і немає впевненості в тому, що структура даних в цих таблицях однакова, то розумніше буде спочатку створити підсумкову таблицю, а вже з отриманої підсумкової таблиці створювати діаграму. Щоб заповнити підсумкову таблицю потрібними даними, можна використовувати функцію ВПР (VLOOKUP).

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

= ВПР (A3; ‘2014’! $ A $ 2: $ B $ 5; 2; ЛОЖЬ)
= VLOOKUP (A3, ‘2014’! $ A $ 2: $ B $ 5,2, FALSE)

І отримати ось такий результат:

Далі просто вибираємо підсумкову таблицю, відкриваємо вкладку вставка (Insert) і в розділі діаграми (Charts) вибираємо потрібний тип діаграми.

Налаштовуємо в Excel діаграму, створену з декількох робочих аркушів

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

В цілому, параметри для діаграми Excel, створеної з декількох робочих аркушів, нічим не відрізняються від параметрів для звичайної діаграми Excel. Можна використовувати групу вкладок Робота з діаграмами (Charts Tools), або контекстне меню, або іконки настройки в правому верхньому кутку діаграми, щоб налаштувати основні елементи, такі як назва діаграми, назви осей, легенду, стиль діаграми та інше. Покрокові інструкції по налаштуванню цих параметрів можна знайти в статті про налаштування діаграм в Excel.

Якщо ж потрібно змінити ряд даних, показаний на діаграмі, то зробити це можна одним з трьох способів:

Змінюємо ряд даних за допомогою діалогового вікна «Вибір джерела даних»

Відкрийте діалогове вікно Вибір джерела даних (Select Data Source), для цього на вкладці конструктор (Design) натисніть кнопку вибрати дані (Select data).

Щоб змінити ряд даних, клікніть по ньому, потім натисніть кнопку змінити (Edit) і відредагуйте параметри ім’я ряду (Series Name) або значення (Series Values), як ми. Щоб змінити порядок даних з діаграми, виділіть ряд даних і перемістіть його вгору або вниз за допомогою відповідних стрілок.

Щоб приховати ряд даних, просто приберіть галочку в списку елементи легенди (Legend Entries) в лівій частині діалогового вікна. Щоб видалити ряд даних з діаграми повністю, виділіть її та натисніть кнопку вилучити (Remove).

Приховуємо або показуємо ряди даних за допомогою іконки «Фільтри діаграми»

Інший спосіб керувати рядами даних, які відображаються на діаграмі Excel, це іконка фільтри діаграми (Chart Filters). Якщо клікнути по діаграмі, то ця іконка тут же з’явиться праворуч.

  • Щоб приховати дані, клікніть по іконці фільтри діаграми (Chart Filters) і приберіть галочку біля відповідного ряду даних або категорії.
  • Щоб змінити ряд даних, натисніть кнопку змінити ряд (Edit Series) праворуч від імені ряду. З’явиться вже знайоме діалогове вікно Вибір джерела даних (Select Data Source), в якому можна створити необхідні настройки. щоб кнопка змінити ряд (Edit Series) з’явилася, досить просто навести курсор миші на ім’я ряду. При цьому ряд даних, на який наведена миша, виділяється на діаграмі кольором, щоб було простіше зрозуміти, який елемент буде змінений.

Змінюємо ряд даних за допомогою формули

Як Ви, мабуть, знаєте, кожен ряд даних в Excel заданий формулою. Наприклад, якщо виділити один з даних з діаграми, яку ми тільки що створили, то формула ряду даних буде виглядати ось так:

= РЯД ( ‘2013’! $ B $ 1; ‘2013’! $ A $ 2: $ A $ 5; ‘2013’! $ B $ 2: $ B $ 5; 1)
= SERIES ( ‘2013’! $ B $ 1, ‘2013’! $ A $ 2: $ A $ 5, ‘2013’! $ B $ 2: $ B $ 5,1)

Кожна формула ряду даних складається з декількох основних елементів:

= РЯД ([імя_ряда]; [імя_категоріі];діапазон_данних;номер_ряда)

Тобто наша формула може бути розшифрована так:

  • Ім’я ряду ( ‘2013’! $ B $ 1) взято з осередку B1 на аркуші 2013.
  • Імена категорій ( ‘2013’! $ A $ 2: $ A $ 5) взяті з осередків A2: A5 на аркуші 2013.
  • Дані ( ‘2013’! $ B $ 2: $ B $ 5) взяті з осередків B2: B5 на аркуші 2013.
  • Номер ряду (1) говорить про те, що цей ряд займає на графіку перше місце.

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

На сьогодні все! Дякую за увагу!

Ссылка на основную публикацию