KB0229. Как создать диаграмму «ящик с усами»?
Диаграммы «ящик с усами» или блочные диаграммы можно создавать с помощью ссылок на данные Excel в think-cell (см. Ссылки на данные Excel). Загрузить шаблоны файлов Excel и PowerPoint можно по следующей ссылке: box_and_whisker.zip.
Просто извлеките содержимое ZIP-файла и следуйте инструкциям по добавлению данных в книге Excel. Шаблон подсчитывает пятизначный итог по вашим исходным данным и представляет его в виде источника данных для Excel в think-cell. Привязанная диаграмма в PowerPoint автоматически обновится, и будет показана диаграмма «ящик с усами» на основании ваших данных.
Построение графика «ящик с усами»
В программах Excel ниже 2010 не представлялось возможным построение графиков box-plot («ящик с усами») — графическое представление средних, медианы и доверительных интервалов, подобных программам Statistica, Statgraphics. Однако Excel-2016 обогатился новыми типами диаграмм, одной из которых является «ящик с усами», или «коробчатая диаграмма» (box-and-whiskers plot или boxplot). Такие диаграммы очень удобны при графическом сравнении двух или большего числа вариантов между собой.
Для построения указанной диаграммы обратимся к нашему примеру по содержанию белка у сортов «Престиж» и «Немчинов-ская-57».
1. Выберем с помощью левой кнопки мыши диапазон А14:В15, включающий наименование сортов и их средние значения, затем в строке меню нажимаем на вкладку Вставить, далее в списке рекомендуемых диаграмм отметим Ящик с усами. После нажатия на кнопку ОК появляются стандартные формы двух коробчатых диаграмм, которые необходимо настроить (рис. 5.11).
Рис. 5.11. Выбор диаграммы «ящик с усами» в программе Excel-2016
2. Общий вид диаграммы настраивается стандартно. Можно менять цвет, добавлять подписи и т. д. Для этого есть две контекстные вкладки Конструктор и Формат, или можно воспользоваться настройками, предназначенными специально для этой диаграммы. Для этого выбираем какой-либо ряд и нажимаем на СЫ+1 или левой кнопкой мыши подводим курсор на «ящик», справа появляется панель настроек Формат ряда данных (рис. 5.12).
Рис. 5.12. Настройка диаграммы «ящик с усами»
С помощью настроек можно форматировать следующие параметры.
Боковой зазор регулирует ширину ящиков и расстояние между ними.
Показывать внутренние точки. Если поставить галочку, то на оси, где расположены «усы», точками будут показаны все значения по данному ряду.
Показать средние метки — среднее арифметическое (крестики).
Показать среднюю линию — только для различных категорий.
Показывать точки выбросов — отражать экстремальные значения.
Инклюзивная медиана или эксклюзивная медиана. Инклюзивная медиана включает квартильные значения, а эксклюзивная медиана их не включает.
При нажатии на знак «+» в правом углу рабочей области диаграммы, появляется окно Элементы диаграммы, в котором галочками отметим нужные для нас параметры диаграммы. После настроек параметров «ящика с усами» получаем следующий график (рис. 5.13).
Рис. 5.13. «Ящики с усами» содержания белка у сортов «Престиж» (ряд 1) и «Немчиновская-57» (ряд 2)
На диаграмме показана следующая информация.
Крестик посередине — это среднее арифметическое или среднее выборочное по каждому сорту: 19,05 и 17,06.
Линия чуть выше или ниже крестика — медиана (19,2 для сорта «Престиж» и 16,9 для сорта «Немчиновская-57»).
Нижняя и верхняя грань прямоугольника (типа ящика) соответствует первому и третьему квартилям (значениям, отделяющим 1/4 и 3/4 выборки). Расстояние между 1-м и 3-м квартилями — это межквартильный размах (или расстояние).
Горизонтальные черточки на конце «усов» — максимальное и минимальное значения (без учета выбросов).
По представленным диаграммам в виде «ящиков с усами» можно проверить нулевую гипотезу об отсутствии различий между выборками (вариантами). При этом тестируемые данные могут подчиняться нормальному распределению или же отличаться от него. Сравнение проводится по значениям квартилей: если нижняя грань прямоугольника, соответствующая первому квартилю одного варианта, заходит за верхнюю грань другого прямоугольника, соответствующему третьему квартилю другого варианта, нулевая гипотеза принимается и различия не существенны.
В нашем примере так как нижний квартиль сорта «Престиж» равен 18,65, а верхний квартиль сорта «Немчиновская-57» равен 17,425 (ящики не прикасаются между собой), значит, можно предполагать, что различия по содержанию белка между указанными сортами существенны на 0,05 уровне значимости.
б. ОЦЕНКА СРЕДНЕЙ РАЗНОСТИ ПРИ КОЛИЧЕСТВЕННОЙ ИЗМЕНЧИВОСТИ ПРИЗНАКОВ ДЛЯ ЗАВИСИМЫХ ВЫБОРОК.
Как построить диаграмму ящик с усами в excel
В Excel прямоугольная и усовая диаграмма, также называемая коробчатой диаграммой, используется для отображения статистического анализа, который помогает показать, как числа распределяются в наборе данных. Например, с помощью прямоугольной диаграммы и диаграммы с усами вы можете отобразить статистические данные о результатах тестов по различным предметам, чтобы определить, какой предмет требует большего внимания от учеников.
В этой статье я расскажу о том, как создать прямоугольную диаграмму и диаграмму усов в каждой версии Excel.
- Создание прямоугольной и усовой диаграммы в Excel 2016 и более поздних версиях
- Создание прямоугольной и усовой диаграммы в Excel 2013 и более ранних версиях
- Скачать образец файла Box and Whisker Chart
- Видео: создание прямоугольной и усовой диаграммы в Excel
В прямоугольной диаграмме и диаграмме усов он показывает сводку данных из пяти цифр — минимальное значение, первый квартиль (Q1), медиана, третий квартиль (Q3) и максимальное значение.
Нижняя строка соединяется с прямоугольником, представляющим минимальное значение, верхняя линия соединяется с прямоугольником — это максимальное значение в данных, поле указывает диапазон между первым и третьим квартилем, линия медианы делит прямоугольник на межквартильный диапазон.
Объяснение каждых пяти чисел:
- Минимальное значение: Минимальное или наименьшее значение в наборе данных;
- Первый квартиль: Среднее значение между минимумом и медианой — 25-й процентиль;
- Медиана: Среднее значение в наборе данных;
- Третий квартиль: Среднее значение медианы, а максимальное — 75-й процентиль;
- Максимальное значение: Максимальное или наибольшее значение в наборе данных.
Создание прямоугольной и усовой диаграммы в Excel 2016 и более поздних версиях
В Excel 2016, 2019 и Office 365 для пользователей Excel поддержана встроенная диаграмма Box and Whisker. Чтобы быстро и легко создать коробку и диаграмму усов, выполните следующие действия:
1. Выберите данные, на основе которых вы хотите создать прямоугольную и усовую диаграмму.
2. Затем нажмите Вставить > Вставить статистическую диаграмму > Коробка и ус, см. снимок экрана:
3. И тогда сразу будут вставлены прямоугольник и диаграмма усов, см. Снимок экрана:
4. Затем выберите и удалите горизонтальную ось, и вы также можете отредактировать заголовок диаграммы и вставить легенду, как вам нужно, см. Снимок экрана:
Создание прямоугольной и усовой диаграммы в Excel 2013 и более ранних версиях
В Excel 2013 и более ранних версиях отсутствует тип диаграммы «Коробка и усы», в этом случае вам следует создать диаграмму «Коробка и усы» шаг за шагом.
Шаг 1. Подготовьте диапазон данных для создания прямоугольной и усовой диаграммы на основе
Предположим, у вас есть следующий диапазон данных:
1. Создайте вторую таблицу для расчета минимального значения, значения первого квартиля, медианного значения, значения третьего квартиля и максимального значения, используя следующие формулы:
2. После вставки формул, и вы получите следующий результат:
3. Затем создайте третью таблицу на основе второй таблицы, чтобы рассчитать различия, примените следующие формулы:
4. После применения формул вы получите результат, как показано на скриншоте ниже, этот диапазон данных будет использоваться для создания прямоугольной диаграммы и диаграммы усов.
Шаг 2. Создайте столбчатую диаграмму с накоплением
5. После получения диапазона данных для создания прямоугольной и усовой диаграммы выберите данные третьей таблицы, а затем нажмите Вставить > Вставить столбец или гистограмму > Столбец с накоплением, см. снимок экрана:
6. И столбчатая диаграмма с накоплением была вставлена в рабочий лист, как показано ниже:
7. Щелкните, чтобы выбрать диаграмму, а затем щелкните Переключить строку / столбец вариант под Дизайн вкладка, и диаграмма будет преобразована в диаграмму ниже, см. скриншоты:
 |
 |
 |
Шаг 3. Отформатируйте столбчатую диаграмму с накоплением в прямоугольную и усовую диаграмму.
8. Теперь вы должны скрыть нижний ряд данных, щелкнуть правой кнопкой мыши любой из нижних рядов данных и выбрать Заполнять > Без заливки из правого меню, см. снимок экрана:
9. Затем нижние ряды данных теперь невидимы, и вы также можете удалить плитку диаграммы и легенду, см. Снимок экрана:
10. Затем вы должны создать усы, заменив самый верхний и второй снизу сегменты, щелкните правой кнопкой мыши верхнюю полосу диаграммы и затем выберите Заполнять > Без заливки, см. снимок экрана:
11. Теперь продолжайте нажимать Дизайн на ленте, а затем щелкните Добавить элемент диаграммы > Полосы ошибок > Стандартное отклонение, и вы получите следующий результат:
12. Затем щелкните правой кнопкой мыши панель ошибок и выберите Полосы ошибок формата из контекстного меню см. снимок экрана:
13. В Полосы ошибок формата панель, под Параметры панели ошибок Вкладка, выполните следующие действия:
- Выберите Минус из файла Руководство раздел;
- Выберите Нет крышки из Конец Стиль раздел;
- Из Сумма ошибки разделом, щелкните Процент вариант и введите 100 в текстовое поле.
14. Затем вы также можете отформатировать строки, продолжайте нажимать Заливка и линия tab выполните следующие операции:
- Выберите SOLID линия под линия раздел;
- Укажите нужный вам цвет из Цвет раскрывающийся список;
- Затем введите 1.5pt в Ширина текстовое окно.
15. Затем повторите шаги 10–14, указанные выше для второго нижнего ряда данных. См. Демонстрацию ниже:
16. И вы получите коробку и диаграмму усов, как показано на скриншоте ниже:
17. Ящик и диаграмма усов обычно рисуются одним цветом заливки, поэтому вам следует изменить цвет заливки и цвет границы для ящиков, сделайте следующее:
- Щелкните правой кнопкой мыши, чтобы выбрать верхние поля, и выберите Заполнять, затем выберите один понравившийся цвет заливки, например светло-коричневый.
- Затем выберите один темно-синий из очертание раскрывающийся список, продолжайте выбирать 1½ пт из Вес .
18. Повторите шаг 17, описанный выше, для форматирования нижних полей. И теперь диаграмма ящиков и усов успешно создана, см. Снимок экрана:
Скачать образец файла Box and Whisker Chart
Видео: создание диаграммы в виде прямоугольников и усов в Excel
Лучшие инструменты для работы в офисе
Kutools for Excel — Помогает вам выделиться из толпы
Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает 300 мощных расширенных функций (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.
Как создавать и интерпретировать диаграммы в Excel
Коробчатая диаграмма — это тип графика, который мы можем использовать для визуализации пятизначной сводки набора данных, которая включает в себя:
- Минимум
- Первый квартиль
- медиана
- Третий квартиль
- Максимум
В этом руководстве объясняется, как создавать и интерпретировать ящичные диаграммы в Excel.
Как создать коробочную диаграмму в Excel
Выполните следующие шаги, чтобы создать коробчатую диаграмму в Excel.
Шаг 1: Введите данные.
Введите данные в один столбец.
Шаг 2: Создайте блочную диаграмму.
Выделите все значения данных.
На вкладке « Вставка » перейдите в группу « Диаграммы » и щелкните символ « Статистическая диаграмма ».
Щелкните Коробка и ус.Автоматически появится блочная диаграмма:
Чтобы увидеть фактические значения, суммированные на диаграмме, щелкните по диаграмме. Затем нажмите зеленый знак плюса, который появится в правом верхнем углу. Затем установите флажок рядом с Метки данных.Следующие ярлыки появятся автоматически:
Вот как интерпретировать метки на блочной диаграмме:
Мин: 3.Это наименьшее значение в наборе данных, которое не считается выбросом. Для этого конкретного примера нет никаких выбросов.
Q1: 5,25. Это значение первого квартиля в наборе данных.
Медиана: 15. Это среднее значение в наборе данных.
Среднее значение: 14,75. Крошечный «x» в середине поля представляет собой среднее значение набора данных.
Q3: 23,75.Это значение третьего квартиля в наборе данных.
Максимум: 29.Это наибольшее значение в наборе данных, которое не считается выбросом. Для этого конкретного примера нет никаких выбросов.
На снимке экрана ниже показаны точные формулы, которые вы можете использовать в Excel для нахождения каждого из этих значений:
Примечание о выбросах
Межквартильный размах (IQR) — это расстояние между третьим квартилем и первым квартилем. Excel считает любое значение данных «выбросом», если оно в 1,5 раза превышает IQR третьего квартиля или в 1,5 раза меньше IQR первого квартиля.
В этом примере IQR составляет 23,75 – 5,25 = 18,5. Таким образом, любое значение меньше 5,25 – (18,5 * 1,5) = -22,5 или любое значение больше 23,75 + (18,5 * 1,5) = 51,5 будет считаться выбросом.
Поскольку в наборе данных нет значений меньше -22,5 или больше 51,5, на блочной диаграмме нет точек, обозначающих выбросы. Однако, если бы наше самое большое значение было на самом деле 52, то на блочной диаграмме была бы точка, указывающая на выброс:
Как создать несколько диаграмм в Excel
Вы можете легко создать несколько блочных диаграмм в Excel, просто введя несколько наборов данных в отдельные столбцы.
Например, предположим, что у нас есть два набора данных. Чтобы создать ящичковую диаграмму для каждого набора данных, мы просто выделим оба столбца данных:
Затем еще раз на вкладке « Вставка » перейдите в группу « Диаграммы » и щелкните символ « Статистическая диаграмма ».
Щелкните Коробка и ус.Коробчатая диаграмма для каждого набора данных появится автоматически:
Это особенно полезно, если мы хотим быстро визуализировать распределения двух или более наборов данных.