Как да създадете график на стъбло и лист в Excel

Този урок ще покаже как да създадете диаграма на стъбло и лист във всички версии на Excel: 2007, 2010, 2013, 2016 и 2022.

Екранът на стъбло и лист (известен също като стемплот) е диаграма, предназначена да ви позволи бързо да оцените разпределението на даден набор от данни. По принцип сюжетът разделя двуцифрените числа наполовина:

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

Като пример, погледнете таблицата по -долу. Диаграмата показва възрастовата разбивка на малка популация. Стъблото (черно, ос Y) показва първата цифра от възрастта, докато червените точки от данни показват втората цифра.

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

Диаграмата обаче не се поддържа в Excel, което означава, че ще трябва да я изградите ръчно от нулата. Вижте нашата добавка за създаване на диаграми, инструмент, който ви позволява да съберете впечатляващи разширени диаграми на Excel само с няколко кликвания.

В този урок стъпка по стъпка ще научите как да създадете динамичен сюжет от стъбло и листа в Excel от нулата.

Приготвяме се да започнем

Да предположим, че имате 24 точки за данни, съдържащи възрастта на вашите клиенти. За да визуализирате кои възрастови групи се открояват от тълпата, вие се заехте да изградите стемплот:

Техниката, която ще научите, е жизнеспособна, дори ако вашият набор от данни съдържа стотици стойности. Но за да го постигнете, първо трябва да положите основите.

Така че, нека се потопим направо.

Стъпка #1: Сортирайте стойностите във възходящ ред.

За начало сортирайте действителните си данни във възходящ ред.

1. Изберете всяка клетка в обхвата на набора от данни (A2: A25).
2. Отидете на Данни раздел.
3. Щракнете върху „Вид”Бутон.
4. Във всяко падащо меню сортирайте по следното:
   1. За "Колона,"Изберете"Възраст на клиента”(Колона А).
   2. За "Сортиране на,"Изберете"Стойности ” /„ Клетъчни стойности.”
   3. За "Поръчка,"Изберете"От най -малкия до най -големия.”

Стъпка 2: Настройте помощна маса.

След като колоната с данни бъде сортирана, настройте отделна помощна таблица за съхраняване на всички данни от диаграмата, както следва:

Няколко думи за всеки елемент от таблицата:

• Стъбло (колона В) - Това ще съдържа първата цифра от всички възрасти.
• Лист (колона D) - Това ще съдържа втората цифра за всички възрасти.
• Положение на листата (колона E) - Тази помощна колона ще ви помогне да позиционирате листата на диаграмата.

Стъпка #3: Намерете стойностите на стъблото.

Първо, изчислете Стъбло стойности (Колона С) с помощта на функциите LEFT и VALUE. Функцията LEFT-която връща определения брой знаци от началото на клетка-ще ни помогне да извлечем първата цифра от всяка стойност, докато функцията VALUE форматира изхода на формулата като число (това е от решаващо значение).

Въведете тази формула в клетката C2:

1	= VALUE (НАЛЯВО (A2,1))

След като намерите първата си стойност на стъбло, плъзнете дръжката за запълване до дъното на колоната, за да изпълните формулата за останалите клетки (С3: С25).

Стъпка #4: Намерете стойностите на листата.

Следващата ни стъпка е намирането на стойностите за Лист колона (Колона D) чрез издърпване на последната цифра от всяко число от колоната с първоначалните данни (колона А). За щастие, функцията НАДЕСНО може да свърши мръсната работа вместо вас.

Въведете следната функция в клетката D2:

1	= ДЯСНО (A2,1)

След като имате формулата в клетката, плъзнете я през останалите клетки (D3: D25).

Стъпка #5: Намерете стойностите на Leaf Position.

Тъй като диаграма на разсейване ще се използва за изграждане на дисплея на стъблото и листата, за да падне всичко на мястото си, трябва да присвоите на всеки лист номер, обозначаващ позицията му в диаграмата, с помощта на функцията COUNTIF.

Въведете тази формула в клетката E2:

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

На обикновен английски език формулата сравнява всяка отделна стойност в колона Стъбло (Колона С) помежду си, за да забележат и маркират дублиращи се събития, като ефективно приписват уникални идентификатори на листата, които споделят общо стъбло.

Отново копирайте формулата в останалите клетки (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,”Маркирайте всички стойности от колоната Стъбло (С2: С25).
3. Щракнете върху „Добре”За диалоговия прозорец Редактиране на серия.
4. Щракнете върху „Добре”За диалоговия прозорец Избор на източник на данни.

Стъпка #8: Променете вертикалната ос.

След като пренаредите диаграмата, трябва да я обърнете отново, за да сортирате стъблата във възходящ ред. Щракнете с десния бутон върху вертикалната ос и изберете „Форматиране на оста.”

След като сте там, следвайте тези прости стъпки:

1. Придвижете се до Опции на оста раздел.
2. Променете Максимални граници стойност на „6”, Защото най -големият брой в набора от данни е 60.
3. Нагласи Основни единици стойност на „1.”
4. Проверете "Стойности в обратен ред" кутия.

Стъпка #9: Добавяне и промяна на отметките по оста.

Бележките, поставени по вертикалната ос, ще се използват като разделител между стъблата и листата. Без да затваряте „Форматиране на оста”, Превъртете надолу до Отбележете марки раздел и до „Основен тип," избирам "Вътре.”

Направете отметките да се открояват, като промените цвета и ширината им.

1. Придвижете се до Fill & Line раздел.
2. Под линия щракнете върху „Цвят на запълване”, За да отворите цветовата палитра и да изберете черно.
3. Нагласи Ширина да се "3 точки”

Вече можете да премахнете хоризонталната ос и линиите на мрежата. Просто щракнете с десния бутон върху всеки елемент и изберете „Изтрий.”Също така увеличете шрифта на номерата на стъблата и ги направете удебелени, за да се виждат по -лесно (изберете вертикалната ос и отидете до У дома > Шрифт).

Стъпка #10: Добавете етикети с данни.

Докато се придвижвате към финалната линия, нека добавим листа към диаграмата. За да направите това, щракнете с десния бутон върху всяка точка, представляваща Серия „Серия 1“ и изберете „Добавяне на етикети на данни.”

Стъпка #11: Персонализирайте етикетите с данни.

Веднъж там, отървете се от етикетите по подразбиране и добавете стойностите от колоната Лист (Колона D) вместо. Щракнете с десния бутон върху произволен етикет с данни и изберете „Форматиране на етикети на данни.”

Когато се появи прозорецът със задачи, следвайте няколко прости стъпки:

1. Превключете към Опции за етикети раздел.
2. Проверете "Стойност от клетки" кутия.
3. Маркирайте всички стойности в колоната Лист (D2: D25).
4. Щракнете върху „Добре.”
5. Премахнете отметката от „Y стойност" кутия.
6. Премахнете отметката от „Показване на водещи линии" кутия.

Променете цвета и размера на шрифта на листата, за да ги различите от стъблата-и не забравяйте да ги направите и смели (У дома > Шрифт).

Стъпка #12: Скриване на маркерите за данни.

Маркерите за данни (точките) са ви служили добре, но вече нямате нужда от тях, освен като средство за позициониране на етикетите на данните. Нека ги направим прозрачни.

Щракнете с десния бутон върху всяка точка, илюстрираща Серия „Серия 1“ и изберете „Форматиране на серия от данни.”

След като се появи прозорецът със задачи, направете следното:

1. Щракнете върху „Fill & Line”Икона.
2. Превключете към Маркер раздел.
3. Под „Опции за маркери," избирам "Нито един.”

И не забравяйте да промените заглавието на диаграмата.

Стъпка #13: Добавете заглавията на оста.

Използвайте заглавията на оста, за да маркирате двата елемента на диаграмата.

1. Изберете графика на диаграмата.
2. Отидете на Дизайн раздел.
3. Щракнете върху „Добавяне на елемент на диаграма.”
4. Изберете „Заглавия на оста.”
5. Избирам "Първичен хоризонтален" и "Основен вертикален.”

Както можете да видите, заглавията на осите припокриват диаграмата на диаграмата. За да отстраните проблема, изберете графиката на диаграмата и регулирайте дръжките, за да преоразмерите областта на графиката. Вече можете да промените заглавията на оста.

Стъпка #14: Добавете текстово поле.

Накрая добавете текстово поле с ключ, за да улесните четенето на стемплота.

1. Изберете графика на диаграмата.
2. Отидете на Вмъкване раздел.
3. В Текст група, изберете „Текстово поле.”

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