Як створити ділянку стебла і листя в Excel

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

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

• Стебла - Перша цифра
• Листя - Друга цифра

Як приклад, подивіться на таблицю нижче. Графік показує вікову разбивку невеликого населення. Стебло (чорне, вісь y) показує першу цифру віку, тоді як червоні точки даних показують другу цифру.

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

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

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

Починаємо

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

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

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

Крок №1: Сортуйте значення в порядку зростання.

Для початку відсортуйте фактичні дані за зростанням.

1. Виберіть будь -яку клітинку в діапазоні набору даних (А2: А25).
2. Перейдіть до Дані вкладка.
3. Натисніть кнопку «Сортувати”.
4. У кожному спадному меню відсортуйте за таким:
   1. За “Стовпчик,"Виберіть"Вік клієнта»(Стовпець А).
   2. За “Сортувати по,"Виберіть"Значення » /« Значення комірки.”
   3. За “Замовлення,"Виберіть"Від найменшого до найбільшого.”

Крок 2: Налаштуйте таблицю помічників.

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

Кілька слів про кожен елемент таблиці:

• Стебло (стовпець C) - Це буде містити першу цифру всіх віків.
• Лист (стовпець D) - Це буде містити другу цифру всіх віків.
• Положення листа (стовпець E) - Цей допоміжний стовпець допоможе розташувати листочки на діаграмі.

Крок №3: Знайдіть значення Stem.

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

Введіть цю формулу в клітинку C2:

1	= VALUE (ВЛІВО (A2,1))

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

Крок 4: Знайдіть значення Leaf.

Наступний наш крок - пошук значень для Листя стовпець (Стовпець D.), витягнувши останню цифру кожного числа зі стовпця вихідних даних (стовпець А). На щастя, функція ПРАВО може зробити за вас брудну роботу.

Введіть у комірку наступну функцію D2:

1	= ПРАВО (A2,1)

Після того, як формула є у клітинці, перетягніть її по решті клітинок (D3: D25).

Крок 5: Знайдіть значення положення листя.

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

Введіть цю формулу в клітинку E2:

1	= COUNTIF ($ C $ 2: C2, C2)

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

Знову скопіюйте формулу в інші клітинки (E3: E25).

Крок №6: Побудуйте діаграму розсіювання XY.

Тепер ви зібрали всі фрагменти головоломки, необхідні для створення діаграми розкиду. Давайте об'єднаємо їх разом.

1. Виділіть усі значення у стовпцях Стебло та Положення листа вибравши комірки даних зі стовпця С, а потім утримуючи клавішу Контроль клавіші, коли ви вибираєте клітинки даних у стовпці E, залишаючи осередки рядка заголовка (C2: C25 та E2: E25). ПРИМІТКА. Наразі ви не вибираєте стовпець D.
2. Перейдіть до Вставити вкладка.
3. Натисніть кнопку «Вставте розсіяну (X, Y) або бульбашкову діаграму”.
4. Виберіть "Розкидати.”

Крок #7: Змініть значення X і Y.

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

Далі натисніть кнопку «Редагувати”.

Потрапивши туди, вам потрібно вручну змінити значення X і Y:

1. За “Значення серії X,”Виберіть усі значення зі стовпця Положення листа (E2: E25).
2. За “Значення серії Y,”Виділити всі значення зі стовпця Стебло (С2: С25).
3. Натисніть «в порядку”Для діалогового вікна Редагувати серію.
4. Натисніть «в порядку”Для діалогового вікна Вибір джерела даних.

Крок #8: Змініть вертикальну вісь.

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

Потрапивши туди, виконайте ці прості дії:

1. Перейдіть до Параметри осі вкладка.
2. Змінити Максимальні межі значення "6», Оскільки найбільше число в наборі даних - 60.
3. Встановіть Основні одиниці значення "1.”
4. Перевірте "Значення в зворотному порядку”Коробка.

Крок 9: Додайте та змініть позначки осі.

Позначки, розміщені вздовж вертикальної осі, будуть використовуватися як роздільник між стеблами і листям. Не закриваючи "Формат осі”, Прокрутіть униз до Позначте позначки розділ і поруч із "Основний тип, "Вибрати"Всередині.”

Виділіть позначки, змінивши їх колір і ширину.

1. Перейдіть до Заповнити & Лінія вкладка.
2. Під рядком натисніть кнопку «Колір заливки”, Щоб відкрити палітру кольорів та вибрати чорний.
3. Встановіть Ширина до “3 пт.”

Тепер можна видалити горизонтальну вісь і лінії сітки. Просто клацніть правою кнопкою миші кожен елемент і виберіть "Видалити.”Також збільште шрифт номерів стебла та зробіть їх жирними, щоб їх було легше бачити (виберіть вертикальну вісь і перейдіть до Додому > Шрифт).

Крок #10: Додайте мітки даних.

Підходячи до фінішу, додамо листочки до діаграми. Для цього клацніть правою кнопкою миші будь-яку точку, що представляє Серія "Серія 1" і виберіть "Додати мітки даних.”

Крок 11: Налаштуйте мітки даних.

Потрапивши туди, позбудьтеся міток за замовчуванням і додайте значення зі стовпця Листя (Стовпець D.) замість. Клацніть правою кнопкою миші будь-яку мітку даних і виберіть "Форматувати мітки даних.”

Коли з'явиться панель завдань, виконайте кілька простих кроків:

1. Перейдіть до Параметри міток вкладка.
2. Перевірте "Значення з клітин”Коробка.
3. Виділіть усі значення у стовпці Листя (D2: D25).
4. Натисніть «в порядку.”
5. Зніміть прапорець "Значення Y”Коробка.
6. Зніміть прапорець "Показувати лінії лідера”Коробка.

Змініть колір і розмір шрифту листя, щоб відрізняти їх від стебел-і не забудьте також зробити їх жирними (Додому > Шрифт).

Крок 12: Приховати маркери даних.

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

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

Після того, як панель завдань спливе, виконайте наведені нижче дії.

1. Натисніть кнопку «Заповнити & Лінія”.
2. Перейдіть до Маркер вкладка.
3. Під "Параметри маркерів,"Вибрати"Жодного.”

І не забудьте змінити назву діаграми.

Крок 13: Додайте заголовки осей.

Використовуйте заголовки осей для позначення обох елементів діаграми.

1. Виберіть графік діаграми.
2. Перейдіть до Дизайн вкладка.
3. Натисніть «Додати елемент діаграми.”
4. Виберіть "Назви осі.”
5. Виберіть "Первинна горизонтальна”Та“Основна вертикаль.”

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

Крок 14: Додайте текстове поле.

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

1. Виберіть графік діаграми.
2. Перейдіть до Вставити вкладка.
3. В Текст групи, виберіть "Текстове вікно.”

У текстовому полі наведіть приклад зі своїх даних, що пояснює, як працює графік, і все готово!