Как сделать коробку с усами в Microsoft Excel • Оки Доки

Если вы работаете с данными в Microsoft Excel, то создание диаграммы – это чистый и привлекательный способ отображения этих данных. Excel предлагает множество

Настройки диаграммы «ящик с усами»

Общий вид диаграммы настраивается стандартно. Можно менять цвет, добавлять подписи и т.д. Для этого есть две контекстные вкладки на ленте (Конструктор и Формат). Но есть настройки, предназначенные специально для этой диаграммы.

Выбираем какой-либо ряд и жмем Ctrl+1. Либо два раза кликаем по какому-нибудь «ящику». Можно через правую кнопку Формат ряда данных…. Справа вылазит панель настроек.

Панель настроек для диаграммы ящик с усами

Рассмотрим по порядку.

Боковой зазор – регулирует ширину ящиков и расстояние между ними.

Показывать внутренние точки. Если поставить галочку, то на оси, где расположены «усы», точками будут показаны все значения. Так хорошо видно распределение внутри групп.

Все точки на диаграмме ящик с усами

Показывать точки выбросов – отражать экстремальные значения.

Выбросы

Выбросы – это точки, выходящие за пределы 1,5 межквартильных размаха.

Показать средние метки – среднее арифметическое (крестики). Стоят по умолчанию, но можно скрыть.

Показать среднюю линию – только для различных категорий. Показывает изменения по категориям.

Изменения в категориях

Если добавить линии, то изменения после эксперимента станут видны еще лучше. В справке написано, что соединяются медианы, но на графике почему-то соединяются средние. Чудеса.

Инклюзивная медиана или эксклюзивная медиана. Инклюзивная медиана включает в «ящик» квартильные значения , а эксклюзивная медиана не включает. При выборе «эксклюзивной медианы» верх и низ «ящика» соответствует средней между квартильным и следующим (от центра) значением. По умолчанию стоит «эксклюзивная». Пусть стоит дальше. Причем тут медиана, вообще не понял, – речь ведь про квартиль. Думал, криво перевели, но в английской версии те же названия. В общем, здесь лучше ничего не менять.

Своевременное использование диаграммы «ящик-усы» может дать весьма ценную и наглядную информацию. Аналитику, который использует специализированные программы или трудоемкие настройки Excel, будет очень приятно иметь такую диаграмму под рукой.

Как показано в ролике ниже, все делается очень быстро и просто.

Поделиться в социальных сетях:

Подготовка данных

Чтобы лучше понять материал и работать с одними и теми же цифрами, скачайте книгу Excel с примером Диаграмма ящик с усами.xlsx.

Данные, используемые в примере имеют нормальное распределение со средним значением равным 10 и стандартным отклонением равным 5-ти. Данные имеют четыре столбца по 20 значений.

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

91-2-Данные для boxplot

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

91-3-подготовка данных для boxplot

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

91-4-данные для boxblop

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

91-5-данные для boxblop

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

91-6-данные для boxblop

Что такое коробка и вискер?

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

Для получения дополнительной информации о том, когда использовать какой тип диаграммы Excel

8 типов диаграмм и графиков Excel и когда их использовать


8 типов диаграмм и графиков Excel и когда их использовать
Графика легче понять, чем текст и цифры. Графики являются отличным способом визуализации чисел. Мы покажем вам, как создавать диаграммы в Microsoft Excel и когда лучше всего их использовать.
Прочитайте больше

Посмотрите наше полезное руководство.

При определении рамочного графика, вот как объясняет это Наука о данных:

Квадратный график — это стандартизированный способ отображения распределения данных на основе сводки из пяти чисел («минимум», первый квартиль (Q1), медиана, третий квартиль (Q3) и «максимум»).

Для просмотра графика с усами в окне показан первый квартиль до третьего квартиля с линией, проходящей через центр по медиане. Усы идут от каждого квартиля до минимума или максимума.

  • минимальный: Наименьшее значение в наборе данных.
  • Первый квартиль: Среднее значение между минимумом и медианой — 25-й процентиль.
  • медиана: Среднее значение набора данных.
  • Третий квартиль: Среднее значение Медиана и Максимум — 75 процентиль.
  • максимальная: Наибольшее значение в наборе данных.

Объяснение Excel Box и сюжет Whisker

Столбцы

В подавляющем большинстве случаев гистограмма определена на отрезке

I=[min(X)-varepsilon_1; max(X)+varepsilon_2]

, где

X

— исходная выборка,

varepsilon_1, varepsilon_2

вспомогательные константы, округляющие до ближайших “читаемых” чисел, которые в каждом случае зависят от масштаба и, обычно, это делители десятки в масштабе исходных данных. Если вдруг стало интересно, как ставить отсечки в данных, то можно посмотреть ссылку:

R (pretty)

.

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

где

n

— число столбцов,

N

— размер исходной выборки,

hat{sigma}

— оценка стандартного отклонения,

IQR=X_{[3/4N]}-X_{[1/4 N]}

— интерквартильное расстояние, которое еще встретится ниже.

Так же можно отметить несколько правил здравого смысла:

  • хорошо чтобы в большинстве столбцов было больше одного исходного значения
  • каждый столбец гистограммы требует хотя бы одного пикселя по ширине, и в целом ограничение “не более 200” столбцов достаточно распространено

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

Штрихкод

Блочная диаграмма в EXCEL

Блочная диаграмма (диаграмма размаха, ящик с усами) является наглядным и компактным способом представить распределение данных в выборке.

Чтобы для заданной выборки построить в MS EXCEL классическую блочную диаграмму (иногда она называется Диаграмма размаха или Ящик с усами, англ . box plot, box-and-whiskers diagram) необходимо предварительно вычислить пять показателей: медиану выборки , 1-й и 3-й квартиль , а также минимальное и максимальное значение.

Примечание : Определение квартилей дано в статье Квартили и интерквартильный интервал (IQR) в MS EXCEL . Там же показано как их вычислять в MS EXCEL.

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

Примечание : Характеристикой разброса значений в выборке является также дисперсия и стандартное отклонение .

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

Построим блочную диаграмму с помощью Гистограммы с накоплением . stat-48.png

СОВЕТ : Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм .

Примечание : Блочная диаграмма может строиться как вертикально (см. рисунок выше), так и горизонтально (см. ниже).

Для построения блочной диаграммы нам потребуются 5 наборов значений в строках 45-78 файла примера , лист Box-plot.

При вертикальном расположении нижняя сторона блока (ящика) соответствует Q1 (первому квартилю ), а верхняя сторона – Q3 (третьему квартилю ). Соответственно, высота блока равна интерквартильному размаху IQR . Линия внутри блока соответствует медиане (Q2). stat-49.png

Примечание : Чтобы вычислить квартили используйте функцию КВАРТИЛЬ.ВКЛ() .

Если для построения самого блока используется Гистограмма с накоплением , то для усов используются вертикальные планки погрешностей . Усы сверху и снизу блока заканчиваются соответственно максимальным и минимальным значением из набора.

Чтобы построить нижний ус необходимо выделить нижнюю (невидимую) часть гистограммы (см. рисунок выше). Затем, во вкладке Макет в группе Анализ выбрать команду Планки погрешностей , и, наконец, после раскрытия подменю, пункт Дополнительные параметры планок погрешностей … После этого откроется диалоговое окно. stat-50.png

Установите направление «Минус», выберите пользовательскую величину погрешности и нажмите кнопку Укажите значение . В появившемся окне, в поле Отрицательное значение ошибки, введите ссылку на ячейки содержащие разность значений Q1 и минимальных значений для каждого набора данных. stat-51.png

Для верхнего уса нужно проделать подобные действия, предварительно выделив часть гистограммы соответствующую верхней части блока.

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

Предположим, что необходимо сравнить 5 различных ресторанов по доставке пиццы. В каждом ресторане было сделано по 30-20 заказов, а срок доставки пиццы фиксировался в таблице (в минутах). Пусть наши 5 блочных диаграмм соответствуют этим 5 наборам значений.

Для примера сравним первые 2 ресторана ( выборка №1 и №2). stat-48.png

Очевидно, что второй ресторан справляется гораздо лучше второго: типичное время доставки 1-го ресторана ( медиана ) чуть больше 60 минут, а у второго только 20. Кроме того, IQR (высота блока, соответствующая разбросу значений) у второго ресторана в несколько раз меньше, то есть его срок доставки гораздо более предсказуем, чем у первого. Очевидно, что сравнивать рестораны, используя блочные диаграммы, гораздо проще, чем анализировать данные непосредственно в таблице.

В файле примера также построены Блочная диаграмма с подписями и Блочная диаграмма с горизонтальным размещением .

Блочная диаграмма с подписями построена с помощью обычной гистограммы с группировкой (использованы ряды с перекрытием). stat-52.png

Кроме того, на диаграмме в качестве целевого значения, выведена горизонтальная линия (зеленая). Это целевое значение можно использовать в качестве порога: если типичное значение выборки ниже или выше порогового значения, то соответствующий объект не соответствует некому требованию.

Горизонтальное размещение диаграмм построено на основе диаграммы Линейчатая с группировкой . stat-53.png

Примечание : В файле примера для удобства написания формул использована функция ДВССЫЛ() . Подробнее об этой функции можно прочитать в статье Функция ДВССЫЛ() в MS EXCEL .

Источник статьи: http://excel2.ru/articles/blochnaya-diagramma-v-ms-excel

Построение диаграммы ящик с усами

Выделите заголовок таблицы с расчетами, затем удерживая клавишу Ctrl, выделите три строки содержащие данные Низ, 2Q Коробка и 3Q Коробка. Этот диапазон с несколькими площадями выделен оранжевым на рисунке ниже.

91-7-построение ящика с усами

Во вкладке Вставка перейдите в группу Диаграммы и выберите Вставить гистограмму –> Гистограмма с накоплением.

91-8-первоначальная диаграмма коробки с усами

Чтобы добавить усы, выделите ряд данных Низ. Перейдите во вкладку Работа с диаграммами -> Конструктор в группу Макеты диаграмм. Нажмите кнопку Добавить элемент диаграммы, в выпадающем меню выберите Предел погрешностей -> Дополнительные параметры предела погрешностей. В появишейся справа панели Формат предела погрешностей в поле Направление установите маркер Минус, а в поле Величина погрешности установите маркер Пользовательская и нажмите кнопку Укажите значение. В появившемся диалоговом окне Настраиваемый предел погрешности поле Положительные значения ошибки оставьте без изменений, а для Отрицательные значения ошибки укажите диапазон B14:E14, который называется Усы-.

91-9-Настраиваемый предел погрешностей

Нажимаем OK и получаем диаграмму, имеющую следующий вид.

91-10-ящик с нижними усами

Теперь необходимо добавить верхние усы. Для этого выделяем ряд данных 3Q Коробка и повторяем действия описанные выше, только теперь в поле Направление панели Формат предела погрешностей устанавливаем маркер Плюс. А в диалоговом окне Натраиваемый предел погрешностей поле Отрицательное значение ошибки оставляем неизменным, а в поле Положительное значение ошибки указываем диапазон B15:E15, который называется Усы+. Жмем ОК и получам следующую диаграмму ящика с усами.

91-11-ящик с верхними усами

Осталось навести антураж и отформатировать нашу таблицу. Выделяем ряд данных Низ и убираем заливку и границы ряда данных. Для ряда данных 2Q Коробка и 3Q Коробка задаем светло серую заливку и темный контур. Удаляем легенду и название диаграммы.

91-13-форматирование диаграммы

Группировка данных

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

Гистограмма с тремя группами c уклонением

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

Гистограмма с тремя группами с перекрытием

Добавление среднего значения

Чтобы добавить данные со средним значение к каждому ящику, выделите ряд под названием Среднее. На картинке выделено голубым.

91-12-данные для среднего

Скопируйте выделенные данные в буфер обмена с помощью сочетания клавиш Ctrl+C. Затем выделите диаграмму и вставьте скопированные данные с помощью клавиш Ctrl+V. У вас должна получиться следующая картинка.

91-13-построение среднего

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

91-14-финальная диаграмма ящик с усами Excel

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

Теперь научитесь делать круговые диаграммы в Microsoft Excel

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

И если вы часто работаете с Excel и хотели бы сделать круговую диаграмму

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


Как создать круговую диаграмму в Microsoft Excel
Каждый может создать простую круговую диаграмму. Но вы можете отформатировать его до совершенства? Мы проведем вас через процесс, один шаг за раз.
Прочитайте больше

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

Узнайте больше о: Microsoft Excel, Microsoft Office 2016, Microsoft Office 2019, Microsoft Office 365, Советы по Microsoft Office.

Как объединить файлы Microsoft Outlook PST: 5 простых методов Превратить Microsoft Excel в налоговый калькулятор с помощью этих шаблонов

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...