зміст
Дане керівництво розповідає, як в Excel створити лінійну діаграму зі значеннями, автоматично відсортованими за зменшенням або за збільшенням, як створити лінійну діаграму з негативними значеннями, як налаштувати ширину смуги лінійної діаграми та багато іншого.
Стовпчасті діаграми, поряд із круговими, є одними з найпопулярніших діаграм. Стовпчасту діаграму побудувати не складно, але ще легше зрозуміти. Для яких даних найкраще використовувати гістограму? Для будь-яких числових даних, які потрібно порівняти: чисел, відсотків, температур, частот та інших вимірювань. Загалом, гістограма підходить для порівняння окремих значень кількох категорій даних. Особливий вид стовпчастої діаграми, діаграма Ганта, часто використовується в програмах управління проектами.
У цьому підручнику ми розглянемо такі запитання щодо гістограм в Excel:
Лінейчаті діаграми в Excel – основи
Гістограма це графік, що показує різні категорії даних у вигляді прямокутних смужок (смуг), довжина яких пропорційна значенням елементів даних, які вони представляють. Такі смуги можна розташовувати горизонтально (линейчатая диаграмма) або вертикально. Вертикальна стовпчаста діаграма — це окремий тип діаграми в Excel, який називається гістограма.
Щоб спростити подальше вивчення цього посібника і точно знати, що ми правильно розуміємо один одного, давайте визначимо основні терміни, що позначають елементи стовпчастої діаграми в Excel. На наступному малюнку показано стандартну згруповану гістограму, яка містить 3 ряди даних (сірий, зелений і синій) і 4 категорії даних (січень, лютий, березень і квітень).
Як побудувати стовпчасту діаграму в Excel
Немає нічого простішого, ніж побудувати стовпчасту діаграму в Excel. Спочатку виберіть дані, які потрібно відобразити на діаграмі, а потім на вкладці Insert (Вставити) в розд Діаграми (Діаграми) Натисніть піктограму гістограми та виберіть підтип, який потрібно створити.
У цьому прикладі ми створюємо саму просту діаграму – Згрупована лінія (2-D кластеризована панель):
Згрупована гістограма, вставлений на аркуш Excel, виглядатиме приблизно так:
Гістограма Excel, показана на малюнку вище, відображає лише один ряд даних, оскільки вихідні дані містять лише один стовпець чисел. Якщо у вихідних даних є два або більше стовпців з числами, то стовпчаста діаграма буде містити кілька рядів даних, забарвлених різними кольорами:
Як переглянути всі доступні типи гістограм
Щоб переглянути всі типи гістограм, доступні в Excel, натисніть посилання Інші стовпчасті діаграми (More Column Charts) і в діалоговому вікні, що відкрилося Вставте діаграму (Вставити діаграму) виберіть один із доступних підтипів діаграми.
Вибір макета та стилю гістограми
Якщо вам не подобається типовий макет або стиль гістограми, вставленої на ваш аркуш Excel, виберіть його, щоб відобразити групу вкладок на стрічці меню Робота з діаграмами (Інструменти для діаграм). Після цього на табл Конструктор (Дизайн), ви можете зробити наступне:
- У розділі Макети діаграм (Chart Layouts) натисніть кнопку Експрес-верстка (Швидкий макет) і спробуйте різні готові макети гістограм;
- Або поекспериментуйте зі стилями гістограми в розділі Стилі діаграм (Стилі діаграм).
Типи лінійчатих діаграм в Excel
Під час створення гістограми в Excel можна вибрати один із наступних підтипів:
Згрупована лінія
Згрупована гістограма (2-D або 3-D) порівнює значення в категоріях даних. У згрупованій гістограмі категорії зазвичай відкладаються по вертикальній осі (вісь y), а значення – по горизонтальній осі (вісь x). Згрупована тривимірна гістограма не відображає третю вісь, а просто робить смужки графіка 3D.
Лінійчасті складені
Стовпчаста гістограма показує пропорції окремих елементів по відношенню до цілого. Як і згрупована гістограма, вона може бути плоскою (2-D) або 3-D (XNUMX-D):
Нормированная линейная с накоплением
Цей тип гістограми подібний до попереднього, але показує відсоток кожного елемента відносно цілого для кожної категорії даних.
Циліндри, конуси та піраміди
Крім стандартних прямоугольників, для побудови всіх перечислених підтипів лінійчатої діаграми можна використовувати циліндри, конуси або піраміди. Розниця тільки у формі фігури, яка відображає вихідні дані.
У Excel 2010 і попередніх версіях ви можете створити циліндр, конус або піраміду, вибравши відповідний тип діаграми на вкладці. Insert (Вставити) в розд Діаграми (Діаграми).
На стрічці меню Excel 2013 і Excel 2016 немає циліндрів, конусів або пірамід. За словами Microsoft, ці типи діаграм було видалено, оскільки занадто багато типів діаграм у попередніх версіях Excel ускладнювали вибір правильного типу для користувача. Однак можливість використовувати циліндр, конус або піраміду також доступна в сучасних версіях Excel, хоча для цього потрібно виконати кілька додаткових кроків.
Як використовувати циліндр, конус або піраміду під час побудови діаграми в Excel 2013 і 2016
Щоб використовувати циліндр, конус або пірамідну діаграму в Excel 2013 і 2016, створіть XNUMX-D стовпчасту діаграму потрібного типу (згруповану, складену або нормалізовану складену), а потім змініть форми, які використовуються для побудови ряду:
- Виділіть усі стовпчики на діаграмі, клацніть на них правою кнопкою миші та клацніть у контекстному меню Формат ряду даних (Формат ряду даних) або просто двічі клацніть на панелі графіка.
- На панелі, що з’явиться, у розділі Параметри рядка (Параметри серії) виберіть відповідну Рисунок (Форма стовпчика).
Примітка: Якщо на діаграмі побудовано кілька рядів даних, описану процедуру необхідно повторити для кожного ряду окремо.
Налаштування стовпчастих діаграм в Excel
Подібно до інших типів діаграм Excel, гістограми забезпечують багато налаштувань для таких елементів, як заголовок діаграми, осі, мітки даних тощо. Ви можете знайти більш детальну інформацію за посиланнями нижче:
Тепер давайте розглянемо деякі конкретні прийоми, які застосовуються до гістограм в Excel.
Змініть ширину стовпчиків і відстань між стовпчиками діаграми
Стовпчаста діаграма, створена в Excel із використанням параметрів за замовчуванням, має забагато пустого простору між стовпчиками. Щоб зробити смуги ширше і візуально ближче один до одного, виконайте наступні дії. Таким же чином можна зробити смужки вже і збільшити відстань між ними. На плоскій 2-D діаграмі стовпчики можуть навіть перекриватися.
- На гістограмі Excel клацніть правою кнопкою миші будь-який ряд даних (стовпчик) і в контекстному меню клацніть Формат ряду даних (Формат ряду даних)
- На панелі, що з’явиться, у розділі Параметри рядка (Параметри серії) виконайте одну з таких дій:
- Для квартирабо для діаграми XNUMXD: щоб змінити ширину смуги і проміжок між категоріями, перемістіть ползунок параметра Бічний просвіт (Gap Width) або введіть у поле введення відсоткове значення від 0 до 500. Чим менше значення, тим тонші смуги і менший проміжок між ними, і навпаки.
- Для плоскої діаграми: щоб змінити зазор між рядами в одній категорії, перемістіть параметр параметра перекриття рядів (Series Overlap) або введіть відсоткове значення від -100 до 100 у полі введення. Чим більше значення, тим більше перекриття серії. Від’ємне значення призведе до проміжку між рядками, як на малюнку нижче:
- Для тривимірної (3-D) діаграми: щоб змінити відстань між рядами даних, перемістіть повзунок параметрів Передній просвіт (Gap Depth) або введіть значення від 0 до 500 відсотків. Чим більше значення, тим більше відстань між полосами. Зміна фронтального зазору помітно відображається в більшості лінійчатих діаграм Excel, але краще всього – в об’ємній гістограмі, як показано на наступній картинці:
- Для квартирабо для діаграми XNUMXD: щоб змінити ширину смуги і проміжок між категоріями, перемістіть ползунок параметра Бічний просвіт (Gap Width) або введіть у поле введення відсоткове значення від 0 до 500. Чим менше значення, тим тонші смуги і менший проміжок між ними, і навпаки.
Побудова стовпчастої діаграми з від’ємними значеннями
Щоб побудувати гістограму в Excel, вихідні значення не повинні бути більше нуля. В цілому Excel не має проблем з відображенням негативних значень на стандартній гістограмі, але зовнішній вигляд діаграми, вставленої за замовчуванням на аркуші Excel, змушує задуматися про редагування макета та дизайну.
Щоб хоч якось поліпшити вигляд діаграми на малюнку вище, по-перше, непогано було б зрушити позначки вертикальної осі вліво, щоб вони не перекривалися смугами негативних значень, а по-друге, можна використовувати інші кольори для від’ємних значень.
Налаштовуємо підписи вертикальної осі
Щоб змінити вигляд вертикальної осі, клацніть на будь-якому з її міток і в контекстному меню натисніть Формат осі (Format Axis), або просто два рази клацніть за підписами осі. У правій частині робочого листа з'явиться панель.
Натисніть Параметри осі (Параметри осі), розгорніть розділ підпису (Мітки) і встановіть параметри Позиція ярлика (Labels Position) значення На дні (Низький).
Змінюємо колір заливки для негативних значень
Якщо ви хочете привернути увагу до від'ємних значень на діаграмі, то це можна успішно зробити, якщо стовпчики від'ємних значень пофарбувати в інший колір.
Якщо в лінійній діаграмі Excel побудовано тільки один ряд даних, то негативні значення, як це часто робиться, можна окрасити в червоний колір. Якщо рядів даних на діаграмі кілька, то негативні значення в кожному з них потрібно окрасити у свій колір. Наприклад, можна використовувати певні кольори для позитивних значень, а для негативних — їх більш бледні відтінки.
Щоб змінити колір негативних смуг, виконайте такі дії:
- Натисніть правою кнопкою миші на будь-якій смузі ряду даних, колір якого потрібно змінити (у нашому прикладі це оранжева смуга) і в контекстному меню натисніть Формат ряду даних (Форматувати ряд даних).
- В появившейся панелі на вкладці Затінення та межі (Заливка та лінія) поставте прапорець Інверсія для чисел <0 (Інвертувати, якщо негативний)
- Відразу після цього з’являться два поля вибору кольорів – для додатних і для від’ємних значень.
Порада: Якщо друга панель вибору кольору не з’явилася, клацніть маленьку чорну стрілку в єдиному доступному на даний момент полі та вкажіть колір для додатних значень (ви можете вибрати той самий, що й за замовчуванням). Відразу після цього з'явиться друге поле вибору кольору для від'ємних значень:
Сортування даних у гістограмах Excel
При створенні лінійної діаграми в Excel категорії даних за замовчуванням вибудовуються в зворотному порядку. Тобто, якщо вихідні дані відсортовані від А до Я, то в лінійній діаграмі вони будуть розташовані від Я до А. Чому так прийнято в Excel? Нікто не знає. Тому ми знаємо, як це відправити.
Примітка перекладача: Автор не знає, чому так, хоча з точки зору математики все цілком логічно: Microsoft Excel будує графік від початку координат, який знаходиться стандартно в лівому нижньому куті. Це перше значення, верхнє в таблиці даних, буде відкладено першу снизу і так далі. Це працює і для діаграми, побудованої на трьох осях. У такому випадку перший ряд даних у таблиці буде відкладено ближнім по осі Z і далі за порядком.
Найпростіший спосіб змінити порядок категорій на гістограмі — змінити сортування вихідної таблиці.
Давайте проілюструємо це простим набором даних. Робочий аркуш містить список 10 найбільших міст світу, розташованих у порядку зменшення кількості населення. На гістограмі дані будуть розташовані зверху вниз у порядку зростання.
Щоб на діаграмі Excel дані розташувалися від більших до меншого верху вниз, достатньо упорядкувати їх у вихідній таблиці в зворотному порядку, т.е. від менших до більших:
Якщо з якоїсь причини сортування даних у таблиці неможливе, далі буде показано спосіб змінити порядок даних у діаграмі Excel без зміни порядку даних у вихідній таблиці.
Ми впорядковуємо дані в гістограмі Excel у порядку зростання/спадання без сортування вихідних даних
Якщо порядок вихідних даних на робочому аркуші має значення і не може бути змінений, давайте зробимо так, щоб стовпчики діаграми відображалися в тому самому порядку. Все просто – потрібно лише включити пару додаткових опцій у налаштуваннях налаштувань.
- На гістограмі Excel клацніть правою кнопкою миші на будь-якій мітці вертикальної осі та в контекстному меню виберіть Формат осі (Осі формату). Або просто двічі клацніть на мітках вертикальної осі.
- На панелі, що з’явиться, у розділі Параметри осі (Параметри осі) налаштуйте такі параметри:
- В групі Пересечение с горизонтальной осью (Горизонтальна вісь перетинає) вибрати У точці з найвищим значенням категорії (На максимальну категорію).
- В групі Положення осі (Положення осі) вибрати Зворотний порядок категорій (Категорії у зворотному порядку).
Готово! Дані в лінійній діаграмі Excel негайно вибудовуються в тому ж порядку, що і у вихідній таблиці, – за збільшенням або за зменшенням. Якщо порядок вихідних даних буде змінений, то дані на діаграмі автоматично повторять ці зміни.
Змініть порядок рядів даних на гістограмі
Якщо гістограма Excel містить кілька рядів даних, за замовчуванням вони також будуються у зворотному порядку. Зверніть увагу на малюнок нижче: регіони в таблиці та на діаграмі розташовані в протилежному порядку.
Щоб упорядкувати ряди даних на гістограмі в тому самому порядку, що й на аркуші, скористайтеся параметрами У точці з найвищим значенням категорії (На вищу категорію) и Зворотний порядок категорій (Категорії у зворотному порядку), як ми це зробили в попередньому прикладі. Порядок побудови категорії при цьому також змінюється, як видно на наступному малюнку:
Якщо потрібно розставити ряди даних в лінійній діаграмі в порядку, що відрізняється від порядку даних на робочому листі, це можна зробити за допомогою:
Зміна порядку рядів даних за допомогою діалогового вікна «Вибір джерела даних».
Цей метод дозволяє змінювати порядок побудови рядів даних у стовпчастій діаграмі для кожної серії окремо та водночас залишати незмінним порядок вихідних даних на аркуші.
- Виберіть діаграму, щоб відобразити групу вкладок на стрічці меню Робота з діаграмами (Інструменти для діаграм). Відкрийте вкладку Конструктор (Дизайн) і в розд дані (Дані) натисніть кнопку Виберіть дані (Виберіть Дані). Або натисніть значок Фільтри діаграм (Фільтри діаграм) праворуч від діаграми та натисніть посилання Виберіть дані (Виберіть Дані) внизу меню, що відкриється.
- У діалоговому вікні Вибір джерела даних (Виберіть джерело даних) виберіть ряд даних, для якого потрібно змінити порядок побудови, і перемістіть його вгору або вниз за допомогою стрілок:
Змінюємо порядок рядів даних за допомогою формули
Кожен ряд даних у діаграмах Excel (не лише гістограми – будь-яка діаграма) визначається формулою, тому ви можете змінити ряд даних, змінивши його формулу. У рамках цієї статті нас цікавить лише останній аргумент цієї формули, який визначає порядок побудови рядів. Ви можете прочитати більше про формулу ряду даних у цій статті.
Наприклад, ряд даних сірої гістограми в наступному прикладі посідає третє місце.
Щоб змінити порядок побудови даного ряду даних, виділіть його в діаграмі, потім у рядку формули змініть останній аргумент формули на будь-яке інше число. У нашому прикладі, щоб перемістити ряд даних на одну позицію вгорі, введіть «2», а щоб зробити його першим у діаграмі, введіть «1» у якості останнього аргументу.
Як і налаштувати параметри в діалоговому вікні Вибір джерела даних (Виберіть джерело даних), редагування формули ряду даних змінює порядок рядів даних лише на діаграмі; вихідні дані на аркуші залишаються незмінними.
Так будується стовпчаста діаграма в Excel. Для тих, хто хоче більше дізнатися про діаграми Excel, рекомендую вивчити статті з цього розділу сайту. Дякую за увагу!