webdonsk.ruКак сделать ящик с усами в excel
Добавил пользователь Владимир З. Обновлено: 08.10.2024
Например, у нас есть несколько рядов данных из какой-то области деятельности человека (промышленности, медицины, государственного управления,…) , и мы хотим сравнить, насколько они похожи или, наоборот, чем одни показатели выделяются по сравнению с другими.
Для простоты восприятия возьмем данные более простые, универсальные и нейтральные — высоту в холке и вес нескольких пород собак по сведениям Американского клуба собаководства (American Kennel Club). Данные по размерам пород в среднем можно найти здесь. Прибавим к ним функцию random.uniform из Python-библиотеки numpy, переведем дюймы в сантиметры, а фунты в килограммы, и вот мы получаем реалистично выглядящий набор данных по размерам собак нескольких пород, с которым можно работать. В нашем примере это чихуахуа, бигли, ротвейлеры и английские сеттеры.
Видим, что половина встреченных нами чихуахуа имеет высоту в холке не больше 18 см, бигль значительно выше – в районе 41 см, и следующие по размерам – ротвейлер и английский сеттер, которые отличаются по росту незначительно: 58 и 63 см.
Но при этом диаграмма размаха содержит дополнительную информацию о том, как данные распределены внутри ряда: нижняя граница прямоугольника (ящика) – это первый квартиль (величина, превосходящая 25% значений ряда), а верхняя граница – третий квартиль (величина, превосходящая 75% значений).
Скрипичный график (violinplot) из той же библиотеки seaborn дает нам еще больше информации о структуре рассматриваемых данных. Ниже на Рисунке 4 представлены все три графика, где породы идут каждый раз в одинаковом порядке, а цвет для соответствующего ряда сохраняется.
Рисунок 4. Сравнение столбчатой диаграммы, диаграммы размаха и скрипичного графика, построенных для одного и того же набора данных
Например, зеленым показаны данные о ротвейлерах.
Более ярко эту мысль можно увидеть на следующем графике (Рисунок 6), где данные по двум группам ротвейлеров отличаются, но подобраны таким образом, что медианы совпадают (крайний слева график) и даже больше – диаграммы размаха (в центре) тоже совпадают! И только скрипичный график (крайний справа) показывает нам, что на самом деле структура данных значительно отличается.
Рисунок 6. Пример, когда только скрипичные график позволяет нам увидеть отличия во внутренней структуре рассматриваемых данных
Используя кластеризацию К-средних (cluster.KMeans) из модуля sklearn, мы можем визуально представить сгруппированные данные, построив диаграмму разброса с помощью функции scatterplot модуля seaborn. Здесь цвет отделяет один кластер, созданный ML-алгоритмом, от другого, а форма маркера показывает исходную принадлежность к той или иной группе. Понижать размерность с помощью PCA или какого-либо другого метода здесь было не нужно, т.к. данные изначально 2D.
Код для кластеризации и построения диаграммы разброса:
Таким образом, на примере данных о высоте в холке нескольких пород собак мы познакомились с некоторыми статистическими характеристиками числовых рядов и инструментах их визуализации. Простой инструмент дает понятную метрику, но не дает полной картины. Более сложные инструменты дают более глубокую картину данных, но и воспринимаются также сложнее в силу увеличения количества информации на графике. И здесь важно выбирать инструмент под конкретную задачу, чтобы находить баланс между требующейся полнотой информации и простотой ее восприятия на графике.
Реальная рабочая схема попадания в черный список телефонных спамеров. Сработала против спама от Совкомбанка, Альфа-банка, Дом.ру, Билайна, ремонта оконных рам, расчетных счетов для бизнеса и не только.
Описывается назначение и построение диаграммы ящик с усами в Excel.
Дмитрий Езепов
Это канал о статистическом анализе данных и эффективных приемах работы в MS Excel. Здесь я, Езепов Дмитрий, публикую видеоуроки о секретах, трюках и хитростях MS Excel. Рассматриваемые приемы значительно облегчают жизнь тем, кто регулярно анализирует данные. Основными темами уроков являются: - формулы (функции) Excel - форматирование (включая условное форматирование) - диаграммы (графики, гистограммы и др. типы) - сводные таблицы - анализ больших массивов данных (сортировка, фильтр, выпадающие списки и проч.) - приемы эффективного анализа данных - работа с ячейками, строками, столбцами, листами Excel - статистические методы, реализованные в MS Excel - трюки и нестандартные приемы в Excel - и др. Обучение по моим урокам отлично подойдет как начинающим, так и продвинутым пользователям.
Предположим, что у нас есть данные по месячной заработной плате сотрудников на 19м грейде:
Крестик посередине – это среднее арифметическое по выборке.
Линия чуть выше или ниже крестика – медиана.
Нижняя и верхняя грань ящика соответствует первому и третьему квартилю (значениям, отделяющим 1/4 и 3/4 выборки). Расстояние между 1-м и 3-м квартилем – это межквартильный размах (или расстояние).
Отдельные точки – это выбросы, которые показываются по умолчанию. Их можно скрыть в настройках.
Получаем вот такой отформатированный график:
Что мы видим на диаграмме? Медианная и средняя зарплата у мужчин на 19м грейде явно выше, чем у женщин, также как и размер минимальной зарплаты, размер заработной платы у женщин более неоднороден. При этом есть одна представительница прекрасного пола, которая все же получает больше мужчин (максимум у розового ящика выше). Кажется, что в нашей компании из примера существует дискриминация по заработной плате? Может быть, это частный случай, который мы наблюдаем только на 19м грейде, для которого есть объяснение? Это можно проверить, если мы сделаем диаграмму box-plot для всех грейдов. Пример такой диаграммы для грейдов 15-21 представлен на рис ниже:
Наши опасения, увы, подтвердились. Странно, что вообще наблюдается такая вариация в зарплате внутри грейда. (Но не будем забывать, что компания в примере гипотетическая).
На практике можно посмотреть разброс зарплаты не только в разрезе грейдов, но и в разрезе стажа, уровня образования, возраста и т.п. Кроме того, было бы несправедливо ограничиваться только задачами из сферы оплаты труда. Подумайте, какие данные вы могли бы проанализировать с помощью ящика с усами в подборе и обучении. И незамедлительно приступайте к анализу на практике.
Общий вид диаграммы настраивается стандартно. Можно менять цвет, добавлять подписи и т.д. Для этого есть две контекстные вкладки на ленте (Конструктор и Формат). Но есть настройки, предназначенные специально для этой диаграммы.
Рассмотрим по порядку.
Боковой зазор – регулирует ширину ящиков и расстояние между ними.
Показывать точки выбросов – отражать экстремальные значения.
Выбросы – это точки, выходящие за пределы 1,5 межквартильных размаха.
Показать средние метки – среднее арифметическое (крестики). Стоят по умолчанию, но можно скрыть.
Показать среднюю линию – только для различных категорий. Показывает изменения по категориям.
Если добавить линии, то изменения после эксперимента станут видны еще лучше. В справке написано, что соединяются медианы, но на графике почему-то соединяются средние. Чудеса.
Как показано в ролике ниже, все делается очень быстро и просто.
Подготовка данных
Чтобы лучше понять материал и работать с одними и теми же цифрами, скачайте книгу Excel с примером Диаграмма ящик с усами.xlsx.
Данные, используемые в примере имеют нормальное распределение со средним значением равным 10 и стандартным отклонением равным 5-ти. Данные имеют четыре столбца по 20 значений.
Все значения положительные, так как при смешанном (положительные и отрицательные значения) виде, данная методика требует некоторых модификаций.
Прежде чем начать строить диаграмму, нам необходимо произвести некоторые расчеты и подготовить данные. Для этого вставьте пустые строки над массивом и заполните заголовки показателями, которые потребуются для построения диаграммы, как показано на рисунке.
Для начала рассчитаем некоторые простые статистические меры, такие как количество значений, среднее и стандартное отклонение. Формулы, используемые для расчетов отображены на рисунке ниже.
Теперь рассчитаем минимум, максимум, медиану, значение первого и третьего квартиля.
Наконец, давайте определим значения, которые станут основой построения. Наша диаграмма имеет коробку второго квартиля, которая отображает разницу между медианой и первым квартилем, значения которых мы рассчитали ранее. Также имеется коробка третьего квартиля – рассчитывается как разница между значением третьего квартиля и медианой. Нижняя часть коробки опирается на первый квартиль. Длина нижних усов равняется значению первого квартиля минус минимальное значение, длина верхних усов – максимум минус значение третьего квартиля.
Что такое коробка и вискер?
График с рамками и усами, или блок-графа, представляет собой диаграмму, которая используется для отображения пятизначной сводки данных. Этот тип диаграммы хорошо подходит для отображения статистических данных, таких как школьные оценки или оценки, до и после изменений процесса или аналогичные ситуации для сравнения числовых данных.
Для получения дополнительной информации о том, когда использовать какой тип диаграммы Excel
8 типов диаграмм и графиков Excel и когда их использовать
8 типов диаграмм и графиков Excel и когда их использовать
Графика легче понять, чем текст и цифры. Графики являются отличным способом визуализации чисел. Мы покажем вам, как создавать диаграммы в Microsoft Excel и когда лучше всего их использовать.
Прочитайте больше
Посмотрите наше полезное руководство.
При определении рамочного графика, вот как объясняет это Наука о данных:
Для просмотра графика с усами в окне показан первый квартиль до третьего квартиля с линией, проходящей через центр по медиане. Усы идут от каждого квартиля до минимума или максимума.
- минимальный: Наименьшее значение в наборе данных.
- Первый квартиль: Среднее значение между минимумом и медианой — 25-й процентиль.
- медиана: Среднее значение набора данных.
- Третий квартиль: Среднее значение Медиана и Максимум — 75 процентиль.
- максимальная: Наибольшее значение в наборе данных.
Столбцы
В подавляющем большинстве случаев гистограмма определена на отрезке
вспомогательные константы, округляющие до ближайших “читаемых” чисел, которые в каждом случае зависят от масштаба и, обычно, это делители десятки в масштабе исходных данных. Если вдруг стало интересно, как ставить отсечки в данных, то можно посмотреть ссылку:
Так же обычно гистограммы делят отрезок I на подотрезки равной длины и, вот, выбор числа отрезков является искусством, хотя можно привести несколько формул:
— размер исходной выборки,
— оценка стандартного отклонения,
— интерквартильное расстояние, которое еще встретится ниже.
Так же можно отметить несколько правил здравого смысла:
- хорошо чтобы в большинстве столбцов было больше одного исходного значения
- каждый столбец гистограммы требует хотя бы одного пикселя по ширине, и в целом ограничение “не более 200” столбцов достаточно распространено
В противном случае, если число столбцов избыточно, а исходных данных мало, гистограмма будет напоминать штрих-код, как например на рисунке ниже.
Блочная диаграмма в EXCEL
Блочная диаграмма (диаграмма размаха, ящик с усами) является наглядным и компактным способом представить распределение данных в выборке.
Чтобы для заданной выборки построить в MS EXCEL классическую блочную диаграмму (иногда она называется Диаграмма размаха или Ящик с усами, англ . box plot, box-and-whiskers diagram) необходимо предварительно вычислить пять показателей: медиану выборки , 1-й и 3-й квартиль , а также минимальное и максимальное значение.
Примечание : Определение квартилей дано в статье Квартили и интерквартильный интервал (IQR) в MS EXCEL . Там же показано как их вычислять в MS EXCEL.
Блочная диаграмма используется для оценки разброса значений в небольших выборках или для сравнения нескольких выборок .
Примечание : Характеристикой разброса значений в выборке является также дисперсия и стандартное отклонение .
Блочная диаграмма может быть полезна, когда количества значений в выборке недостаточно для построения полноценной гистограммы.
Построим блочную диаграмму с помощью Гистограммы с накоплением .
СОВЕТ : Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм .
Примечание : Блочная диаграмма может строиться как вертикально (см. рисунок выше), так и горизонтально (см. ниже).
Для построения блочной диаграммы нам потребуются 5 наборов значений в строках 45-78 файла примера , лист Box-plot.
При вертикальном расположении нижняя сторона блока (ящика) соответствует Q1 (первому квартилю ), а верхняя сторона – Q3 (третьему квартилю ). Соответственно, высота блока равна интерквартильному размаху IQR . Линия внутри блока соответствует медиане (Q2).
Примечание : Чтобы вычислить квартили используйте функцию КВАРТИЛЬ.ВКЛ() .
Если для построения самого блока используется Гистограмма с накоплением , то для усов используются вертикальные планки погрешностей . Усы сверху и снизу блока заканчиваются соответственно максимальным и минимальным значением из набора.
Чтобы построить нижний ус необходимо выделить нижнюю (невидимую) часть гистограммы (см. рисунок выше). Затем, во вкладке Макет в группе Анализ выбрать команду Планки погрешностей , и, наконец, после раскрытия подменю, пункт Дополнительные параметры планок погрешностей … После этого откроется диалоговое окно.
Для верхнего уса нужно проделать подобные действия, предварительно выделив часть гистограммы соответствующую верхней части блока.
Теперь, используя блочную диаграмму, попытаемся сравнить несколько наборов данных.
Предположим, что необходимо сравнить 5 различных ресторанов по доставке пиццы. В каждом ресторане было сделано по 30-20 заказов, а срок доставки пиццы фиксировался в таблице (в минутах). Пусть наши 5 блочных диаграмм соответствуют этим 5 наборам значений.
Для примера сравним первые 2 ресторана ( выборка №1 и №2).
Очевидно, что второй ресторан справляется гораздо лучше второго: типичное время доставки 1-го ресторана ( медиана ) чуть больше 60 минут, а у второго только 20. Кроме того, IQR (высота блока, соответствующая разбросу значений) у второго ресторана в несколько раз меньше, то есть его срок доставки гораздо более предсказуем, чем у первого. Очевидно, что сравнивать рестораны, используя блочные диаграммы, гораздо проще, чем анализировать данные непосредственно в таблице.
В файле примера также построены Блочная диаграмма с подписями и Блочная диаграмма с горизонтальным размещением .
Блочная диаграмма с подписями построена с помощью обычной гистограммы с группировкой (использованы ряды с перекрытием).
Кроме того, на диаграмме в качестве целевого значения, выведена горизонтальная линия (зеленая). Это целевое значение можно использовать в качестве порога: если типичное значение выборки ниже или выше порогового значения, то соответствующий объект не соответствует некому требованию.
Горизонтальное размещение диаграмм построено на основе диаграммы Линейчатая с группировкой .
Примечание : В файле примера для удобства написания формул использована функция ДВССЫЛ() . Подробнее об этой функции можно прочитать в статье Функция ДВССЫЛ() в MS EXCEL .
Построение диаграммы ящик с усами
Выделите заголовок таблицы с расчетами, затем удерживая клавишу Ctrl, выделите три строки содержащие данные Низ, 2Q Коробка и 3Q Коробка. Этот диапазон с несколькими площадями выделен оранжевым на рисунке ниже.
Во вкладке Вставка перейдите в группу Диаграммы и выберите Вставить гистограмму –> Гистограмма с накоплением.
Нажимаем OK и получаем диаграмму, имеющую следующий вид.
Теперь необходимо добавить верхние усы. Для этого выделяем ряд данных 3Q Коробка и повторяем действия описанные выше, только теперь в поле Направление панели Формат предела погрешностей устанавливаем маркер Плюс. А в диалоговом окне Натраиваемый предел погрешностей поле Отрицательное значение ошибки оставляем неизменным, а в поле Положительное значение ошибки указываем диапазон B15:E15, который называется Усы+. Жмем ОК и получам следующую диаграмму ящика с усами.
Осталось навести антураж и отформатировать нашу таблицу. Выделяем ряд данных Низ и убираем заливку и границы ряда данных. Для ряда данных 2Q Коробка и 3Q Коробка задаем светло серую заливку и темный контур. Удаляем легенду и название диаграммы.
Группировка данных
До сих пор был рассмотрен случай, когда у нас есть характеристика, на которую мы просто хотим взглянуть, обычно намного более интересно сравнивать поведение одной и той же характеристики для различных подгрупп. В таком случае гистограмма будет иметь следующий вид.
В данном случае, ширина каждого столбца для каждой группы уменьшается пропорционально числу групп и слегка сдвигаются друг относительно друга, в качестве альтернативы можно рассмотреть полупрозрачное перекрытие, которое будет выглядеть следующим образом для тех же данных.
Добавление среднего значения
Чтобы добавить данные со средним значение к каждому ящику, выделите ряд под названием Среднее. На картинке выделено голубым.
Скопируйте выделенные данные в буфер обмена с помощью сочетания клавиш Ctrl+C. Затем выделите диаграмму и вставьте скопированные данные с помощью клавиш Ctrl+V. У вас должна получиться следующая картинка.
Щелкните по новому ряду данных правой кнопкой мыши и выберите Изменить тип диаграммы для ряда. В появившемся диалоговом окне Изменение типа диаграммы найдите рад данных Среднее, поменяйте тип диаграммы на Точечная и снимите маркер Вспомогательная ось, если он был установлен.
Наша финальная диаграмма ящик с усами готова. На ней можно увидеть распределение данных от первого до третьего квартиля, медиану и среднее значение.
Теперь научитесь делать круговые диаграммы в 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 в налоговый калькулятор с помощью этих шаблонов
Читайте также: