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

В этом руководстве будет показано, как создать график из стволов и листьев во всех версиях Excel: 2007, 2010, 2013, 2016 и 2022.

Отображение «стебель-лист» (также известное как «стержневой график») - это диаграмма, предназначенная для того, чтобы вы могли быстро оценить распределение данного набора данных. По сути, сюжет делит двузначные числа пополам:

• Стебли - Первая цифра
• Листья - Вторая цифра

В качестве примера посмотрите на диаграмму ниже. Диаграмма отображает возрастную разбивку небольшой группы населения. Основа (черная, ось Y) показывает первую цифру возраста, а красные точки данных показывают вторую цифру.

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

Однако эта диаграмма не поддерживается в Excel, а это означает, что вам придется вручную построить ее с нуля. Ознакомьтесь с нашей надстройкой Chart Creator, инструментом, который позволяет вам составлять впечатляющие расширенные диаграммы Excel всего за несколько кликов.

В этом пошаговом руководстве вы узнаете, как с нуля создать динамический график в виде стержня и листа в Excel.

Начиная

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

Техника, которую вы собираетесь изучить, жизнеспособна, даже если в вашем наборе данных есть сотни значений. Но для этого нужно сначала заложить фундамент.

Итак, приступим.

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

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

1. Выберите любую ячейку в диапазоне набора данных (A2: A25).
2. Перейти к Данные таб.
3. Щелкните значок «Сортировать" кнопка.
4. В каждом раскрывающемся меню отсортируйте по следующему:
   1. Для "Столбец," Выбрать "Возраст клиента»(Колонка А).
   2. Для "Сортировать по," Выбрать "Значения »/« Значения ячеек.”
   3. Для "Порядок," Выбрать "От наименьшего к наибольшему.”

Шаг № 2: Настройте вспомогательную таблицу.

После сортировки столбца данных настройте отдельную вспомогательную таблицу для хранения всех данных диаграммы следующим образом:

Несколько слов по каждому элементу таблицы:

• Стебель (столбец C) - Он будет содержать первую цифру всех возрастов.
• Лист (столбец D) - Он будет содержать вторую цифру всех возрастов.
• Положение листа (столбец E) - Этот вспомогательный столбец поможет расположить листья на диаграмме.

Шаг № 3: Найдите значения стержня.

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

Введите эту формулу в ячейку C2:

1	= ЗНАЧЕНИЕ (ЛЕВО (A2; 1))

Найдя свое первое значение стержня, перетащите маркер заполнения в нижнюю часть столбца, чтобы выполнить формулу для оставшихся ячеек (C3: C25).

Шаг № 4: Найдите значения Leaf.

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

Введите следующую функцию в ячейку D2:

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

Когда формула будет в ячейке, перетащите ее через остальные ячейки (D3: D25).

Шаг № 5: Найдите значения положения листа.

Поскольку для построения отображения «стебель-лист» будет использоваться точечная диаграмма, чтобы все упало на свои места, вам необходимо присвоить каждому листу номер, обозначающий его положение на диаграмме, с помощью функции СЧЁТЕСЛИ.

Введите эту формулу в ячейку E2:

1	= СЧЁТЕСЛИ ($ C $ 2: C2; C2)

На простом английском языке формула сравнивает каждое отдельное значение в столбце Корень (Столбец C) друг с другом, чтобы обнаруживать и отмечать повторяющиеся вхождения, эффективно присваивая уникальные идентификаторы листьям, имеющим общий стебель.

Снова скопируйте формулу в остальные ячейки (E3: E25).

Шаг № 6: Постройте точечный график XY.

Теперь вы собрали все части головоломки, необходимые для создания диаграммы разброса. Соберем их вместе.

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

Шаг №7: Измените значения X и Y.

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

Затем щелкните значок «Редактировать" кнопка.

Оказавшись там, вам нужно вручную изменить значения X и Y:

1. Для "Значения серии X,»Выберите все значения из столбца Положение листа (E2: E25).
2. Для "Значения серии Y,»Выделите все значения из столбца Корень (C2: C25).
3. Нажмите «Ok»Для диалогового окна« Редактировать серию ».
4. Нажмите «Ok»Для диалогового окна« Выбор источника данных ».

Шаг № 8: Измените вертикальную ось.

После того, как вы переставили диаграмму, вам нужно снова перевернуть ее, чтобы отсортировать основы в порядке возрастания. Щелкните правой кнопкой мыши вертикальную ось и выберите «Ось формата.”

Оказавшись там, выполните следующие простые шаги:

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

Шаг № 9: Добавьте и измените отметки оси.

Отметки, расположенные вдоль вертикальной оси, будут использоваться в качестве разделителя между стеблями и листьями. Не закрывая «Ось форматаПанель задач, прокрутите вниз до Отметки раздел и рядом с "Основной тип," выбирать "Внутри.”

Выделите отметки, изменив их цвет и ширину.

1. Перейдите к Заливка и линия таб.
2. Под строкой щелкните значок «Цвет заливки», Чтобы открыть цветовую палитру и выбрать черный.
3. Установить Ширина к "3 пт.”

Теперь вы можете удалить горизонтальную ось и линии сетки. Просто щелкните каждый элемент правой кнопкой мыши и выберите «Удалить.»Кроме того, увеличьте шрифт номеров стержней и сделайте их жирным, чтобы их было легче увидеть (выберите вертикальную ось и перейдите к Дом > Шрифт).

Шаг № 10: Добавьте метки данных.

По мере того, как вы приближаетесь к финишу, давайте добавим листья на диаграмму. Для этого щелкните правой кнопкой мыши любую точку, представляющую Серия «Серия 1» и выберите «Добавьте метки данных.”

Шаг № 11: Настройте метки данных.

Оказавшись там, избавьтесь от меток по умолчанию и добавьте значения из столбца Лист (Столбец D) вместо. Щелкните правой кнопкой мыши любую метку данных и выберите «Отформатируйте метки данных.”

Когда появится панель задач, выполните несколько простых шагов:

1. Переключитесь на Параметры метки таб.
2. Проверить "Значение из ячеек" коробка.
3. Выделите все значения в столбце Лист (D2: D25).
4. Нажмите «Ok.”
5. Снимите флажок "Значение Y" коробка.
6. Снимите флажок "Показать линии выноски" коробка.

Измените цвет и размер шрифта листьев, чтобы их можно было отличить от стеблей, и не забудьте также выделить их жирным шрифтом (Дом > Шрифт).

Шаг № 12: Скройте маркеры данных.

Маркеры данных (точки) сослужили вам хорошую службу, но они вам больше не нужны, кроме как для позиционирования меток данных. Так что давайте сделаем их прозрачными.

Щелкните правой кнопкой мыши любую точку, изображающую Серия «Серия 1» и выберите «Форматировать ряд данных.”

Когда появится панель задач, сделайте следующее:

1. Щелкните значок «Заливка и линия" значок.
2. Переключитесь на Маркер таб.
3. Под "Параметры маркера," выбирать "Никто.”

И не забудьте изменить название диаграммы.

Шаг № 13: Добавьте заголовки осей.

Используйте заголовки осей, чтобы пометить оба элемента диаграммы.

1. Выберите график диаграммы.
2. Перейти к Дизайн таб.
3. Нажмите «Добавить элемент диаграммы.”
4. Выбирать "Названия осей.”
5. Выбирать "Первичный горизонтальный" а также "Первичная вертикаль.”

Как видите, названия осей перекрывают график диаграммы. Чтобы устранить проблему, выберите график диаграммы и отрегулируйте ручки, чтобы изменить размер области графика. Теперь вы можете изменить названия осей.

Шаг № 14: Добавьте текстовое поле.

Наконец, добавьте текстовое поле с ключом, чтобы облегчить чтение основного графика.

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

В текстовом поле приведите пример из ваших данных, объясняющий, как работает график, и все готово!