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

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

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

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

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

1. Клацніть на рядах, які потрібно змінити.
2. Виберіть Робота з діаграмами Конструктор Тип Змінити тип діаграми.
3. У діалоговому вікні Зміна типу діаграми виберіть піктограму, яка представляє тип діаграми для вибраного ряду.
4. Якщо ви бажаєте використовувати другу вертикальну вісь для перетвореного ряду, виберіть рядок і натисніть Ctrl+1для відкриття діалогового вікна Формат ряду даних; перейдіть до розділу Параметри рядута встановіть перемикач Побудувати ряду становище По допоміжній осі.

Діаграма на рис. 136.1 була стандартною стовпцевою діаграмою з двома рядами. Декілька клацань кнопкою миші, і один з рядів (Опадки в даному прикладі) перетворився зі стовпців на лінію. Я також додав другу вісь і помітив осі, щоб було зрозуміло, який масштаб для якого ряду використовується. Щоб додати назви осей, виконайте команду Робота з діаграмами Макет Назви осей.

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

На рис. 136.4 показано, як далеко можна зайти, використовуючи змішані діаграми. Ця діаграма поєднує п'ять різних типів діаграм: кругову, з областями, гістограму (стовпцеву), лінійчасту і точкову (XY). Я не уявляю, в якій ситуації така діаграма була б доречною, але це досить цікавий демонстраційний приклад.

У деяких випадках ви не можете змішувати типи діаграм. Наприклад, ви не можете створити змішану діаграму, яка включає бульбашкову і поверхневу (3D) діаграми. Якщо ви виберете несумісний тип діаграми для серії, Excel повідомить вас про це.

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

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

Як побудувати комбіновану діаграму в Excel

Способи побудови комбінованої діаграми в Excel:

• перетворення наявної діаграми на комбіновану;
• додавання допоміжної осі.

Створимо таблицю з даними, які необхідно відобразити на комбінованій діаграмі.

Виділимо стовпці діапазону, включаючи заголовки. На вкладці "Вставка" у групі "Діаграми" виберемо звичайний "Графік з маркерами".

В області побудови з'явилося два графіки, що відображають кількість проданих одиниць товару та обсяг продажів у рублях.

Як можна комбінувати різні типи діаграм? Клацніть правою кнопкою миші «К-во, шт.». У вікні виберемо «Змінити тип для ряду».

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

Натисніть ОК. За умовчанням висота стовпчиків відповідає вертикальній осі значень, на яку нанесено продаж. Але гістограма має відображати кількість.

Виділимо гістограму, клацнувши по ній мишкою. Перейдемо на вкладку "Макет". Група "Поточний фрагмент" - інструмент "Формат виділеного фрагмента".

Відкриється вікно Формат ряду даних. На вкладці "Параметри ряду" поставимо галочку навпроти "Побудувати ряд по допоміжній осі".

Натискаємо кнопку "Закрити".

Попрацюємо над зовнішнім виглядом комбінованої діаграми. Виділимо область побудови та перейдемо на вкладку «Конструктор». Змінимо стиль. Видалимо легенду (виділити – Delete). Додамо назву та підписи вертикальних осей.

Для основної та допоміжної осі вибираємо варіант розташування (окремо для кожної) та вводимо підпис. Тиснемо Enter.

У цьому прикладі ми використовували відразу два способи побудови комбінованих діаграм: змінювали тип для ряду і додавали допоміжну вісь.

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

Зміна одного ряду даних типу діаграми

Створимо змішану діаграму шляхом зміни одного з рядів типу.

Таблиця з вихідними даними:

Побудуємо звичайну гістограму на основі двох рядів даних:

Виділимо стовпчики гістограми, що відображають планові показники. На вкладці "Конструктор" у групі "Тип" натисніть кнопку "Змінити тип діаграми". Виберемо із запропонованих варіантів «З областями».

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

Увага! Не всі види діаграм можна поєднувати. Не можна поєднувати деякі об'ємні типи, бульбашкові з іншими діаграмами. Програма Excel у разі неможливих комбінацій видає помилку.

Таким чином, змішана діаграма будується на основі двох та більше рядів даних. У ньому використовуються різні типи діаграм. Або один тип (наприклад, гістограма), але міститься друга вісь значень.

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

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

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

Зрозумілим може бути питання: «а чому б просто два графіки не побудувати, навіщо два різні типи діаграм?» Відповідь: тому що одиниці виміру і, відповідно, сам порядок цифр можуть бути різні. Наприклад, температура лежить у межах 700-1000С, а зусилля прокатки в межах 10-70 МН. Нанесемо їх на один графік — і графік зусиль прокатки лежатиме значно нижче за графік температур. Потрібно, щоб кожного параметра була своя вертикальна вісь. Ось для цього потрібні графіки різних типів.

Тож поїхали.

1. Спочатку будуємо стовпчасту діаграму, яка міститиме інформацію про розподіл обтискань по проходах. Шлях: "вставка/гістограма/гістограма" (див. рис.).

2. Вибираємо дані: по горизонтальній осі – номери проходів (стовпець B), вертикальній – зусилля прокатки (стовпець C).

3. Тепер додамо другий ряд даних - температура металу на виході з валків (стовпець D). Поки що отримали не дуже зручну картинку, бо порядок цифр різний.

4. Тепер найголовніша дія: потрібно змінити тип діаграми температур. Клацаємо правою кнопкою мишки за будь-яким стовпцем діаграми температур, вибираємо меню «Змінити тип діаграми для ряду» і вибираємо тип «точкова діаграма».

5. Отримали ось що:

Бачимо, що тепер у нас є дві вертикальні та дві горизонтальні осі, кожна для свого графіка.

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

7. Ще можна нанести значення на графіки. Знову ж клацаємо правою кнопкою миші за потрібним графіком і вибираємо «додати підписи даних».


Ось що ми маємо в результаті:

От і все! Графік готові.

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

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

Вставка та побудова

Наприклад використовуємо таблицю виручки та витрат протягом року, виходячи з якої побудуємо простий график:

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

Перейдіть до створеної порожньої області, щоб з'явились додаткові вкладки стрічок. Одна з них називається "Конструктор" і містить область "Дані", на якій розташований пункт "Вибрати дані". Клік по ньому викликає вікно вибору джерела:

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

На згаданому вище вікні натисніть кнопку «Додати» у полі «Елементи легенди». З'явиться форма «Зміна ряду», де потрібно задати посилання на ім'я ряду (не є обов'язковим) та значення. Можна вказати усі показники вручну.

Після занесення необхідної інформації та натискання кнопки «OK», новий рядвідобразитися на діаграмі. Так само додамо ще один елемент легенди з нашої таблиці.

Тепер замінимо автоматично додані підписи горизонтальної осі. У вікні вибору даних є область категорій, а в ній кнопка "Змінити". Клацніть по ній та у формі додайте посилання на діапазон цих підписів:

Подивіться, що має вийти:

Елементи діаграми

За замовчуванням діаграма складається з наступних елементів:

• Ряди даних – становлять головну цінність, т.к. візуалізують дані;
• Легенда – містить назви рядів та приклад їх оформлення;
• Осі - шкала з певною ціною проміжних поділів;
• Область побудови – є тлом для даних;
• Лінії сітки.

Крім згаданих вище об'єктів, можуть бути додані такі як:

• Назви діаграми;
• Ліній проекції – низхідні рядів даних на горизонтальну вісь лінії;
• Лінія тренду;
• Підписи даних – числове значення точки даних ряду;
• І інші елементи, що нечасто використовуються.

Зміна стилю

Для зміни зовнішнього виглядудіаграми можна скористатися наданими за умовчанням стилями. Для цього виділіть її і виберіть вкладку «Конструктор», на якій розташована область «Стилі діаграм».

Часто наявних шаблонів достатньо, але якщо Ви хочете більшого, доведеться задати власний стиль. Зробити це можна клікнувши по об'єкту, що змінюється, діаграми правою кнопкою миші, в меню вибрати пункт «формат Ім'я_Елементу» і через діалогове вікно змінити його параметри.

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

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

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

Типи діаграм

Графік

Ідеально підходить для відображення зміни об'єкта в часі та визначення тенденцій.
Приклад відображення динаміки витрат та загального виторгу компанії за рік:

Гістограма

Добре підходить для порівняння кількох об'єктів та зміни їхнього стосунку з часом.
Приклад порівняння показника ефективності двох відділів поквартально:

Кругова

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

Діаграма з областями

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

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

Так як для нас першочергово бачити саме потенціал, цей ряд відображається першим. З нижче наведеної діаграми видно, що з 11 години до 16 години відділ не справляє з потоком клієнтів.

Крапкова

Є системою координат, де положення кожної точки задається значеннями по горизонтальній (X) і вертикальній (Y) осях. Добре підходить, коли значення (Y) об'єкта залежить від певного параметра (X).

Приклад відображення тригонометричних функцій:

Поверхня

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

Біржова

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

Зазвичай подібні діаграми відображають коридор коливання (максимальне та мінімальне значення) та кінцеве значення у певний період.

Пелюстка

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

На нижче наведеній діаграмі представлено порівняння 3-х організацій за 4 напрямками: Доступність; Цінова політика; Якість продукції; Клієнтоорієнтованість. Видно, що компанія X лідирує за першим та останнім напрямком, компанія Y за якістю продукції, а компанія Z надає кращі ціни.

Можна сказати, що компанія Х є лідером, т.к. площа її фігури на діаграмі є найбільшою.

Змішаний тип діаграм

Excel дозволяє поєднувати в одній діаграмі кілька типів. Як приклад сумісний тип графіка та гістограми.

Для початку всі ряди будуються із застосуванням одного виду, потім він змінюється для кожного ряду окремо. Клацнувши правою кнопкою миші по потрібному ряду, зі списку виберіть пункт «Змінити тип діаграми для ряду…», потім «Гістограма».

Іноді, через сильні відмінності значень рядів діаграми, використання єдиної шкали неможливе. Але можна додати альтернативну шкалу. Перейдіть в меню «Формат ряду даних…» та у розділі «Параметри ряду» перемістіть прапорець на пункт «По допоміжній осі».

Тепер діаграма набула такого вигляду:

Тренд Excel

Кожному ряду діаграм можна встановити свій тренд. Вони необхідні визначення основної спрямованості (тенденції). Але для кожної окремої нагоди необхідно застосовувати свою модель.

Виділіть ряд даних, для якого хочете побудувати тренд, і клацніть правою кнопкою миші. У меню виберіть пункт «Додати лінію тренда…».

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

• Експонентний тренд. Якщо значення вертикальної осі (Y) зростають з кожною зміною горизонтальної осі (X).
• Лінійний тренд використовується, якщо значення Y мають приблизно однакові зміни для кожного значення X.
• Логарифмічний. Якщо зміна осі Y уповільнюється з кожним зміною осі X.
• Поліноміальний тренд застосовується, якщо зміни Y відбуваються як у бік збільшення, так у зменшення. Тобто. Дані описують цикл. Добре підходить для аналізу великого набору даних. Ступінь тренду вибирається в залежності від кількості піків циклів:
  • Ступінь 2 – один пік, тобто. половина циклу;
  • Ступінь 3 – один повний цикл;
  • Ступінь 4 – півтора цикли;
  • і т.д.
• Ступінь тренд. Якщо зміна Y зростає з приблизно однаковою швидкістю при кожній зміні X.

Лінійна фільтрація Чи не застосовний для прогнозу. Використовується для згладжування змін Y. Збільшує зміну між точками. Якщо в налаштуваннях тренду параметра точки задати 2, то усереднення проводиться між сусідніми значеннями осі X, якщо 3, то через одну, через 4 через - дві і т.д.

Зведена діаграма

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

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

• Виділіть зведену таблицю;
• Пройдіть на вкладку "Аналіз" (в Excel 2007 вкладка "Параметри");
• У групі «Сервіс» клацніть на піктограмі «Зведена діаграма».

Для побудови зведеної діаграми з нуля необхідно на вкладці Вставка вибрати відповідний значок. Для додатка 2013 року він знаходиться в групі «Діаграми», для додатка 2007 року в групі таблиці, пункт списку «Зведена таблиця», що розкривається.

• < Назад
• Вперед >

Якщо матеріали сайту Вам допомогли, то підтримайте, будь ласка, проект, щоб я міг розвивати його далі.

Діаграми та графіки в MS Excel (входить до складу офісного пакету MS Office) служать для графічного відображення даних, що наочно з точки зору користувача. За допомогою діаграм зручно спостерігати за динамікоюзмін значень досліджуваних величин, проводити порівняння різних даних, уявлення графічної залежності одних величин від інших.

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

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

На даний момент використовуються версії додатків 2003, 2007, 2010, 2013, 2016. Процеси побудови графіків та діаграм у них мають деякі відмінності, перш за все в інтерфейсі. Основні з них вказуватимуться нижче.

Як побудувати графік в ексель

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

Щоб створити графік (як і діаграму) у MS Excel потрібно насамперед ввести числові даніна лист, на підставі яких він буде збудований. Зазвичай для графіка достатньо двох стовпців, один з яких використовуватиметься для осі X (аргументу), другий – для Y (функції) – це може бути виражено формулою або просто переліком залежних від аргументу даних.

Виділітьдіапазон даних. Потім вибравши потрібний тип графіка на вкладці Вставкау групі Діаграми- Натисніть Вставити графік(Для перегляду динаміки змін даних). Якщо хочете побудувати графік за точками – слід взяти Точкову діаграму(якщо у Вас є 2 ряди даних, один із яких залежить від другого).

Microsoft Excel 2003

Microsoft Excel 2013

Графік можна розмістити як на одному аркуші з даними, так і на окремому.

Як побудувати діаграму

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

Для такої діаграми одинстовпець – це мітки даних, другий- Сам числовий ряд даних.

Виділіть діапазон комірок з мітками та даними. Потім Вставка, натиснути Діаграмита вибрати відповідний Вашим вимогам тип діаграми(У нашому випадку Кругова).

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

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

Будуємо синусійду

Припустимо Вам потрібно побудувати графік функції, що представляє собою синусоїду. Для цього потрібно ввестиданісинусів кутів.

Щоб порахувати значення синусів, необхідно в першу комірку низки даних Sinввести формулу= SIN(РАДІАН(A3)), де A3- Відповідний аргумент. Після чого стовпець розтягнутиза правий нижній кут. Отримаємо шуканий діапазон значень.

Далі будуємо графік, натискаючи Вставка, Графік, так само як і раніше.

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

Як додати назву до діаграми

Якщо захочете змінити назву, зробити її зрозумілішою, або видалити її зовсім, то потрібно зробити наступні дії. У версії Excel 2003 потрібно натиснути в будь-якому місці цієї діаграми, після чого побачите панель Робота з діаграмамиз вкладками Макет, Форматі Конструктор. У групі Макет/ПідписиВиберіть Назвадіаграми. Змініть потрібні параметри.

Назва можна пов'язувати з будь-якою коміркою таблиці, відзначивши посилання на неї. Зв'язане значення назви автоматично змінюється при зміні таблиці.

Як налаштувати осі та додати назви

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

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

Додаємо або змінюємо легенду

Завдяки легенді на графіку визначається належність параметра до того чи іншого стовпця.

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

Перейдіть у вкладку Конструктор/Вибрати данідля версії 2003 або в контекстному меню Вибрати данідля версії 2013 року.

Відкриється вікно вибору джерела даних, у якому можна змінитидіапазон використовуваних даних, змінити підписи осей, та елементи легенди (ряди), параметри для кожного ряду окремо.

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