зміст
При створенні діаграм в Excel вихідні дані для неї не завжди знаходяться на одному аркуші. На щастя, Microsoft Excel надає можливість відображати дані з двох або більше різних аркушів на одній діаграмі. Докладні інструкції див. нижче.
Як створити діаграму з даних на кількох аркушах у Excel
Припустимо, що в одному файлі електронної таблиці є кілька аркушів з даними про доходи за різні роки. Використовуючи ці дані, вам потрібно побудувати діаграму, щоб візуалізувати загальну картину.
1. Будуємо діаграму за даними першого аркуша
Виберіть дані на першому аркуші, які потрібно відобразити на діаграмі. Далі відкрити кладку Insert. В групі Діаграми Виберіть потрібний тип діаграми. У нашому прикладі ми використовуємо Об'ємна складена гістограма.
Саме гістограма з накопиченням є найпопулярнішим типом діаграм.
2. Вводимо дані з другого аркуша
Виділіть створену діаграму, щоб активувати міні-панель зліва Інструменти діаграм. Потім виберіть Конструктор і натисніть на піктограму Виберіть дані.
Ви також можете натиснути кнопку Фільтри діаграм 
. Праворуч, у самому низу списку, що з’явиться, натисніть Виберіть дані.
У вікні, що з'явиться Вибір джерела дані за посиланням Додати.
Додаємо дані з другого аркуша. Це важливий момент, тому будьте обережні.
Коли ви натискаєте кнопку Додати, з’явиться діалогове вікно Зміна рядків. Біля поля значення потрібно вибрати значок діапазону.
Window Зміна рядків скручуватися. Але при переході на інші аркуші він залишиться на екрані, але не буде активним. Потрібно вибрати другий аркуш, з якого потрібно додати дані.
На другому аркуші необхідно виділити дані, внесені в діаграму. До вікна Зміни рядків активовано, вам просто потрібно клацнути на ньому один раз.
Щоб клітинка з текстом була назвою нового рядка, потрібно виділити діапазон даних біля значка Назва рядка. Згорніть вікно діапазону, щоб продовжити роботу на вкладці Зміни рядків.
Переконайтеся, що посилання в рядках Назва рядка и Значення вказано правильно. Натисніть OK.
Як ви можете бачити на зображенні вище, назва рядка пов’язана з коміркою V1де написано. Замість цього назву можна ввести як текст. Наприклад, другий рядок даних.
Назви серій відображатимуться в легенді діаграми. Тому краще давати їм осмислені назви.
На цьому етапі створення діаграми робоче вікно має виглядати так:
3. Додайте більше шарів, якщо необхідно
Якщо все ж потрібно вставити дані в діаграму з інших аркушів Excel, потім повторіть усі кроки з другого абзацу для всіх вкладок. Потім натискаємо OK у вікні, що з’явиться Вибір джерела даних.
У прикладі є 3 рядки даних. Після всіх кроків гістограма виглядає так:
4. Налаштуйте та покращте гістограму (необов’язково)
Під час роботи в версіях Excel 2013 і 2016 заголовок і легенда автоматично додаються під час створення гістограми. У нашому прикладі вони не були додані, тому зробимо це самі.
Виберіть діаграму. У меню, що з’явиться Елементи діаграми натисніть зелений хрестик і виділіть всі елементи, які необхідно додати до гістограми:
Інші налаштування, такі як відображення міток даних і формат осей, описані в окремій публікації.
Складаємо діаграми із сумарних даних у таблиці
Наведений вище спосіб побудови діаграми працює, лише якщо дані на всіх вкладках документа знаходяться в одному рядку чи стовпці. Інакше діаграма буде нерозбірливою.
У нашому прикладі всі дані розташовані в однакових таблицях на всіх 3 аркушах. Якщо ви не впевнені, що структура в них однакова, то краще спочатку скласти підсумкову таблицю, виходячи з наявних. Це можна зробити за допомогою функції ВЛООКУП or Майстри злиття таблиць.
Якби в нашому прикладі всі таблиці були різними, то формула була б такою:
=ВПР (A3, '2014'!$A$2:$B$5, 2, FALSE)
Це призведе до:
Після цього просто виберіть отриману таблицю. У вкладці Insert знаходити Діаграми і виберіть потрібний тип.
Редагування діаграми, створеної з даних на кількох аркушах
Буває і так, що після побудови графіка потрібні зміни даних. У цьому випадку простіше редагувати наявну діаграму, ніж створювати нову. Це робиться через меню. Робота з діаграмами, що нічим не відрізняється для графіків, побудованих з даних однієї таблиці. Налаштування основних елементів графіка показано в окремій публікації.
Є кілька способів змінити дані, які відображаються на самій діаграмі:
- через меню Вибір джерела даних;
- через фільтри
- Я посередник Формули рядів даних.
Щоб відкрити меню Вибір джерела даних, необхідний у табл Конструктор натисніть підменю Виберіть дані.
Щоб редагувати рядок:
- вибрати рядок;
- натисніть вкладку Редагувати;
- зміна значення or Ім'я, як ми робили раніше;
Щоб змінити порядок рядків значень, потрібно виділити рядок і перемістити його за допомогою спеціальних стрілок вгору або вниз.
Щоб видалити рядок, потрібно просто виділити його та натиснути на кнопку Видалити. Щоб приховати рядок, його також потрібно виділити та зняти прапорець у меню елементи легенди, який знаходиться в лівій частині вікна.
Змінення рядів за допомогою фільтра діаграми
Усі налаштування можна відкрити, натиснувши на кнопку фільтра . Він з’являється, як тільки ви клацаєте по діаграмі.
Щоб приховати дані, просто натисніть фільтр і зніміть прапорці з рядків, яких не повинно бути на діаграмі.
Наведіть вказівник на рядок, і з’явиться кнопка Змінити рядок, натисніть на нього. З’являється вікно Вибір джерела даних. Робимо в ньому необхідні налаштування.
Примітка! Коли ви наводите курсор миші на рядок, він підсвічується для кращого розуміння.
Редагування ряду за допомогою формули
Усі ряди на графіку визначаються формулою. Наприклад, якщо ми виберемо ряд у нашій діаграмі, він виглядатиме так:
=SERIES(‘2013′!$B$1,’2013′!$A$2:$A$5,’2013’!$B$2:$B$5,1)
Будь-яка формула складається з 4 основних компонентів:
=SERIES([Назва серії], [значення x], [значення y], номер рядка)
Наша формула в прикладі має таке пояснення:
- Назва серії ('2013'!$B$1) взята з клітинки B1 на аркуші 2013.
- Значення рядків ('2013'!$A$2:$A$5), взяте з клітинок A2: A5 на аркуші 2013.
- Значення стовпців ('2013'!$B$2:$B$5) взято з клітинок В2: В5 на аркуші 2013.
- Цифра (1) означає, що вибраний рядок займає перше місце в діаграмі.
Щоб змінити певний ряд даних, виберіть його на діаграмі, перейдіть до панелі формул і внесіть необхідні зміни. Звичайно, ви повинні бути дуже обережними під час редагування формули ряду, оскільки це може призвести до помилок, особливо якщо вихідні дані знаходяться на іншому аркуші, і ви не можете їх побачити під час редагування формули. Тим не менш, якщо ви досвідчений користувач Excel, вам може сподобатися цей метод, який дозволяє швидко вносити невеликі зміни до ваших діаграм.