Эксель 2007 ящик с усами

Визуализация статистических данных с помощью диаграммы ящик с усами

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

Рис. 1. Визуальное представление выборки: (а) диаграмма разброса показывает все элементы выборки; (б) 5-числовая сводка в виде графика ящик с усами

Скачать заметку в формате Word или pdf, примеры в формате Excel

5-числовая сводка данных

Изучая выборку, мы можем визуализировать все ее значения (рис. 1а), а можем научиться выражать в сжатом виде наиболее часто встречающиеся общие особенности. В последнем случае используют сводки данных. Сегодня наиболее популярными сводками (или статистиками) являются среднее значение и среднеквадратичное отклонение (подробнее см. Определение среднего значения, вариации и формы распределения. Описательные статистики). Однако, во-первых, так было не всегда, а во-вторых, не во всех случаях эти статистики лучше всего описывают выборку.

Любопытный пример приводит Нассим Николас Талеб (см. Черный лебедь. Под знаком непредсказуемости). Если вы определяете средний рост 100 человек, то нахождение среди этой сотни самого высокого человека в мире (2 м 72 см) не сильно изменит среднее значение. А вот если вы определяете среднее богатство 100 человек, то наличие среди них Билла Гейтса ($86,8 млрд.) полностью исказит картину. В этих случаях более релевантной сводкой является медиана.

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

В своей, уже ставшей классической, работе Анализ результатов наблюдений. Разведочный анализ Джон Тьюки предлагает 5-числовые сводки, которые включают: минимальное значение, первый квартиль, медиану, третий квартиль, максимальное значение. Процесс нахождения медианы, а затем квартилей можно представить себе, как складывание листа бумаги. Поэтому эти новые значения Тьюки называет сгибами (подробнее см. КВАРТИЛЬ: какие формулы расчета использует Excel). Чтобы представить 5-чиловую сводку визуально, Тьюки предлагает построить график ящик с усами (рис. 1б).

Диаграмма ящик с усами в Excel 2016

В предыдущих версиях Excel приходилось шаманить, чтобы представить статистические данные (см., например, Биржевая диаграмма, она же блочная, она же ящичная). В качестве альтернативы я иногда использовал R (см. Алексей Шипунов. Наглядная статистика. Используем R!). Сейчас же визуализация статистических данных выполняется в несколько кликов. Очень удобно! Вот только Microsoft почему-то сделал весьма краткое описание того, что же собственно отражают элементы диаграммы ящик с усами (см. поддержку MS). Восполним же этот пробел))

Построим диаграмму ящик с усами на основе данных о средних температурах июля за последние 30 лет (рис. 2).

Рис. 2. Основные настройки диаграммы ящик с усами

На рисунке под диаграммой рассчитана 5-числовая сводка. На диаграмме:

• нижний ус соответствует минимальному значению в выборке данных,
• низ ящика – 1-му квартилю,
• линия внутри ящика – медиане,
• верх ящика – 3-му квартилю,
• верхний ус – второму максимальному значению (выбросы не учитываются); если бы данные не содержали выброса, верхний ус соответствовал бы максимальному значению,
• опциально отдельными точками показаны выбросы.

Рассмотрим параметры ряда подробнее (см. Формат ряда данных на правой части рис. 2). Боковой зазор определяет ширину ящика и ширину горизонтальных границ усов, а также расстояние между ящиками, если их несколько (рис. 3). Параметр Показать внутренние точки – говорит сам за себя (рис. 4). Показать точки выбросов – любопытно, что Excel просто перестает показывать выбросы, при этом никак не меняет параметры ящика и усов (рис. 5). Показать средние метки – наряду с медианной линией отражает в виде крестика среднее арифметическое значение по выборке (рис. 6). Видно, что среднее арифметическое смещено относительно медианы вверх (в сторону выброса). Среднее арифметическое более чувствительно к выбросам по сравнению с медианой. Показать среднюю линию – соединяет средние значения разных категорий. У меня не получилось отразить эти линии. А у Дмитрия Езипова получилось.

Рис. 3. Боковой зазор: слева – 300%, справа – 100%

Рис. 4. Показать внутренние точки; слева параметр выключен, справа – включен

Рис. 5. Показать точки выбросов; слева параметр выключен, справа – включен

Рис. 6. Показать средние метки; слева параметр выключен, справа – включен

Какой метод расчета квартилей предпочесть

И, наконец, самый непонятный параметр форматирования диаграммы ящик с усами – Расчет квартиля. В сообществе статистиков нет единого мнения, по какому алгоритму считать квартили. В Excel используется два алгоритма. Если вы хотите в них досконально разобраться, рекомендую КВАРТИЛЬ: какие формулы расчета использует Excel. Если кратко, то в Excel есть две функции КВАРТИЛЬ.ИСКЛ() и КВАРТИЛЬ.ВКЛ(). Первая использует алгоритм эксклюзивной медианы, вторая – инклюзивной. Алгоритм расчета квартилей в первом случае дает значения чуть более далекие от медианы, т.е. ящик немного более вытянутый. Визуально это не разглядеть, и только если добавить подписи данных, то числа покажут этот эффект (рис. 7).

Рис. 7. Расчет квартиля: (а) эксклюзивная медиана, (б) инклюзивная медиана

Если вы обрабатываете данные в разных программах (Excel, R, SAS…), то для совместимости, наверное, лучше использовать квартили, рассчитанные по методу эксклюзивной медианы. Поскольку этот метод используется во всех продуктах, вы получите одинаковые числа в разных программах. Если же вы работаете только в Excel, я бы рекомендовал метод инклюзивной медианы. Он более чувствителен к выбросам, т.е. при прочих равных КВАРТИЛЬ.ВКЛ() определит точку как выброс при меньшем отклонении от медианы (рис. 8).

Рис. 8. (б) Точка, которая идентифицируется функцией КВАРТИЛЬ.ВКЛ(), как выброс, в то время, как (а) КВАРТИЛЬ.ИСКЛ() еще не считает эту точку выбросом

Источник статьи: http://baguzin.ru/wp/vizualizatsiya-statisticheskih-dannyh/

Excel. Биржевая диаграмма, она же блочная, она же ящичная

В одном из комментариев на сайте меня попросили рассказать про ящичные диаграммы. На мой взгляд, эти диаграммы в повседневной офисной практике используются незаслуженно редко. И тому я вижу несколько объяснений:

• в Excel эти диаграммы носят название биржевые (рис. 1), что, казалось бы, сужает область их применения до специальных сфер деятельности;
• большинство менеджеров видят мир детерминированным, и потому использование диаграмм, отражающих вероятностный подход, даже не приходит им в голову;
• возможности Excel в построении таких диаграмм ограничены, а установка надстроек может вызывать затруднения.

Рис. 1. Меню выбора биржевой диаграммы

Скачать заметку в формате Word, примеры в формате Excel

Дополнение от 30 ноября 2016 г. В версии Excel 2016 появилась диаграмма «ящик с усами». Подробнее см. Новые диаграммы в Excel 2016

Можно выделить следующие области применения ящичных диаграмм [1]:

• изменение цен акций и объемов торгов;
• анализ статистических данных; например, в метеорологии или менеджменте качества;
• представление результатов маркетинговых исследований, социологических опросов;
• составление прогнозов.

В Excel доступны четыре типа биржевых диаграмм (см. нумерацию на рис. 1), содержащих от 3 до 5 набора данных:

№ на рис. 1	Ряды данных в порядке их расположения
1	Максимальное значение – минимальное – закрытие
2	Открытие – максимальное – минимальное – закрытие
3	Объем – максимальное – минимальное – закрытие
4	Объем – открытие – максимальное – минимальное – закрытие

В качестве категорий (ось Х) можно использовать даты или названия (например, акций). Так на рис. 1 использован первый тип диаграммы, и данные расположены в порядке: верхняя граница – нижняя граница – прогноз поступлений. А в качестве категории используется номер недели.

При использовании четырех наборов данных (тип 2) диаграмма оправдывает свое альтернативное название, так как на ней изображаются ящички/блоки. В качестве примера я взял многолетние данные наблюдений за температурой в Москве (рис. 2). Каждый «бочонок» соответствует совокупности наблюдений за один месяц в течение многих лет (если быть точным, 133 лет).

Рис. 2. Биржевая диаграмма второго типа; статистика среднемесячных температур в Москве за период наблюдений (1879–2012 гг.); на примере декабря: низ «усика» соответствует минимальной среднемесячной температуре, низ блока соответствует значению на одно стандартное отклонение ниже среднего (μ – σ), верх блока = μ + σ, верх «усика» – максимальная среднемесячная температура; в интервал μ ± σ, то есть внутрь блока, попадает 68,3% всех наблюдений

Внимание! Excel настроен на построение именно биржевых диаграмм. Из-за этого мастер диаграмм не всегда справляется с построением диаграммы, на основе данных, содержащих отрицательные значения. Например, если на рис. 2 вы возьмете только данные за январь, февраль и март, то Excel «ругнется», что размещение данных неверное. Если ваши данные содержат отрицательные числа, а строк меньше, чем параметров (которых, напомню, используется от 3 до 5), просто продублируйте строки, чтобы их стало больше, чем параметров, постройте диаграмму, а затем уменьшите ее область построения и удалите лишние строки.

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

Рис. 3. Биржевая диаграмма второго типа; динамика стоимости акций; 17 и 20 декабря цена закрытия больше цены открытия – ящички светлые, 18, 19 и 21 декабря цена закрытия меньше цены открытия – ящички темные

Третий и четвертый тип биржевой диаграммы в Excel аналогичен первому и второму типу плюс еще один параметр – объем торгов (размер выборки и т.п.). Поместите столбец с объемом сразу за столбцом категорий. Соответствующая диаграмма имеет вторую ось ординат, для отражения объема торгов или его аналога (рис. 4). Левая ось ординат для объема торгов, правая – для цены акций.

Рис. 4. Биржевая диаграмма четвертого типа; динамика торгов и стоимости акций

Использование блочных диаграмм в статистике было известно задолго до появления Excel. Английский термин – box-and-whisker diagram (диаграмма ящик с усами). Блочная диаграмма представляет собой удобное средство для изображения пяти базовых статистических показателей. Например, на рис. 5 изображена блочная диаграмма, иллюстрирующая показатели среднегодовой доходности 15 высокорисковых фондов. [2]

Рис. 5. Блочная диаграмма, иллюстрирующая показатели доходности 15 фондов с очень высоким уровнем риска; по оси Х среднегодовая доходность в процентах

Вертикальная линия, проведенная внутри прямоугольника, отмечает медиану. Левая сторона прямоугольника соответствует первому квартилю, Q_1, а правая сторона — третьему квартилю, Q₃. Таким образом, прямоугольник содержит средние 50% элементов выборки. Младшие 25% данных изображаются в виде линии (так называемый ус), соединяющей левую сторону прямоугольника с наименьшим выборочным значением X_min. Следовательно, старшим 25% данных соответствует линия, соединяющая правую сторону прямоугольника с наибольшим выборочным значением X_max.

Блочная диаграмма, представленная на рис. 5, демонстрирует, что показатели среднегодовой доходности 15 фондов с очень высоким уровнем риска имеют практически симметричное распределение, поскольку расстояние между медианой и наибольшим значением приблизительно равно расстоянию между наименьшим значением и медианой. Однако другие характеристики распределения указывают на несимметричность. Правый ус диаграммы длиннее левого, поскольку выборка содержит выброс, равный 18,5% (правая граница), а медиана расположена ближе к правой стороне диаграммы, чем к левой.

На рис. 6 изображены четыре типа распределений, а также соответствующие им блочные диаграммы.

Рис. 6. Четыре гипотетических распределения, исследованных с помощью блочной диаграммы. Область, расположенная под каждым ящиком, разбита квартилями, входящими в пятерку базовых показателей. А – колоколообразное распределение, Б – распределение с отрицательной асимметрией, В – распределение с положительной асимметрией, Г – прямоугольное распределение.

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

Если распределение данных имеет отрицательную асимметрию, как на рис. 6Б, среднее выборочное значение смещается вдоль левого хвоста. Отрицательная асимметрия проявляется в виде высокой концентрации данных в правой половине шкалы. При этом 75% всех данных расположены между левой стороной прямоугольника (первый квартиль, Q₁) и концом правого уса (наибольшее выборочное значение, X_max). Следовательно, вдоль длинного левого уса распределены всего 25% данных. Это свидетельствует о сильной асимметрии распределения.

Если распределение данных имеет положительную асимметрию, как на рис. 6В, пик распределения смещается влево. Теперь 75% всех данных расположены между началом левого уса (наименьшее выборочное значение, X_min) и правой стороной прямоугольника (третий квартиль, Q₃). Остальные 25% данных распределены вдоль длинного правого уса.

К сожалению, стандартные средства Excel не позволяют построить блочную диаграмму с пятью базовыми показателями статистики: X_min, Q₁, медиана, Q₃, X_max. Можно воспользоваться надстройкой PHStat2 или аналогичными. Я «исхитрился» и построил такую диаграмму, правда, не идеально красивую.

В качестве исходной возьмите диаграмму, как на рис. 2. Добавьте к данным еще один ряд – средние значения μ. Чтобы сделать это, для начала выделите диаграмму и правой кнопкой мыши вызовите контекстное меню (рис. 7А).

Рис. 7А. Добавляем на стандартную биржевую диаграмму (тип 2) ряд средних значений; А – выбрать данные

В открывшемся окне «Выбор источника данных» нажмите «Добавить» (рис. 7Б).

Рис. 7Б. Добавляем на стандартную биржевую диаграмму (тип 2) ряд средних значений; Б – добавить ряд

В открывшемся окне «Изменение ряда» выберите имя ряда и значения (рис. 7В). Нажмите 2 раза Ok.

Рис. 7В. Добавляем на стандартную биржевую диаграмму (тип 2) ряд средних значений; В – выбрать имя ряда и значения

Ряд на диаграмме добавился, но он не виден. Это связано с тем, что по умолчанию на биржевой диаграмме ряды не имеют ни цвета линий, ни маркеров. Все видимые элементы диаграммы выполнены с помощью полос повышения и планок погрешности; см. вкладку Excel «Работа с диаграммами» – «Макет», область Анализ (выделено в правой верхней части рис. 7Г). Обратите также внимание, что после добавления ряда μ ящички уменьшились по высоте (сравните размер ящичков на рисунках 7А и 7Г). Это связано с еще одной особенностью биржевых диаграмм – низ ящичков всегда соответствует значениям первого ряда. Первого – в окне «Выбор источника данных». В нашем случае (см. рис. 7Б) это ряд «μ–σ». Верх ящичков соответствует значениям последнего ряда. В нашем случае сначала это был ряд «μ+σ» (см. рис. 7Б), а после добавления ряда «μ», именно он стал последним. Чтобы исправить ситуацию, надо просто изменить порядок рядов в окне «Выбор источника данных». Откройте это окно еще раз, выделите ряд «μ», и передвиньте его вверх с помощью стрелки ↑. Ящички вернуться к исходному размеру.

Теперь, чтобы отформатировать вновь созданный ряд средних, выделите диаграмму, и пройдите по меню Работа с диаграммами – Макет. В области «Текущий фрагмент» щелкните на «Область диаграммы» и выберите ряд «μ» (рис. 7Г).

Рис. 7Г. Добавляем на стандартную биржевую диаграмму (тип 2) ряд средних значений; Г – активировать новый ряд на диаграмме

Ряд «μ» выделился (рис. 7Д). Это видно, во-первых, по тому, что на диаграмме появились точечки вокруг ряда (пока невидимого), а на листе выделен диапазон Т3:Т15. Щелкните кнопку «Формат выделенного фрагмента».

Рис. 7Д. Добавляем на стандартную биржевую диаграмму (тип 2) ряд средних значений; Д – формат выделенного фрагмента

В открывшемся окне «Формат ряда данных», установите Цвет линии маркера – Нет линий, Заливка маркера – Сплошная, цвет – черный, Параметры маркера – как на рис. 7Е

Рис. 7Е. Добавляем на стандартную биржевую диаграмму (тип 2) ряд средних значений; Е – параметры маркера

Итог усилий представлен на рис. 7. Стоит ли она затраченных усилий, решать вам.

Рис. 7. Ящичная диаграмма с пятью базовыми показателями статистики: X_min, Q₁, медиана, Q₃, X_max.

На закуску рассмотрим пример использования блочных диаграмма в практике «шесть сигм» (это одна из концепций менеджмента качества; для первого знакомства см., например, Пит Панде, Ларри Холп. Что такое «шесть сигм»?).

Допустим, ваша задача выявить из «моря» факторов (X₁, X₂… X_n) тот, который оказывает наибольшее влияние на объем производства (Y). Для начала соберите все данные, относящиеся к делу (рис. 8).

Рис. 8. Форма для регистрации данных наблюдений

Чтобы узнать, влияет ли наблюдаемый входной фактор (X_m) на выходной (Y), следует создать несколько диаграмм «ящик с усами» для выходного фактора таким образом, чтобы каждая отдельная диаграмма соответствовала одному условию входной переменной.

Данные на рис. 8 — это часть большого списка данных, собранных для одного процесса. Ключевой результат (Y) показывает часовую производительность в штуках. Возможные входные факторы влияния: департамент, выполняющий транзакцию (Х₁), час дня, когда транзакция была выполнена (Х₂), вид использованной рабочей системы (Х₃) и имя работника, выполнившего транзакцию (Х₄).

Какой эффект оказывает конкретный работник (Х₄) на производительность (Y)? На рис. 9 представлен набор ящичных диаграмм Y для каждого значения входного фактора Х₄.

Рис. 9. Влияние каждого отдельного работника на часовую производительность

Зависит ли количество выполненных за час транзакций от того, кто делан эту работу – Боб, Джоан или Салли? Из рис. 9 ясно видно, что операторы почти не разнятся между собой по производительности. У них приблизительно один и тот же средний уровень и вариация одной и той же величины. Из этого следует вывод, что переменная Х₄ (работник) не является ключевым фактором влияния на вариацию результата.

Что можно сказать о зависимости результата от того, какой департамент (Х₃) выполняет транзакции? На рис. 10 изображена еще одна группа диаграмм «ящик с усами» для двух департаментов.

Рис. 10. Влияние каждого департамента на часовую производительность

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

[1] Чтобы разнообразить изложение, я буду употреблять все три названия диаграмм в качестве синонимов. При написании заметки использованы материалы книг: Джон Уокенбах Microsoft Office Excel 2007. Библия пользователя, стр. 391, 392, Дэвид Левин и др. Статистика для менеджеров с использованием Microsoft Excel, стр. 214–217, Крейг Джиджи и др. Шесть сигм для «чайников», стр. 158–160, а также учебные материалы Microsoft: Представление данных в биржевой диаграмме, Создание биржевой диаграммы.

[2] Из книги Д. Левин и др. Статистика для менеджеров с использованием Microsoft Excel, стр. 214.

8 комментариев для “Excel. Биржевая диаграмма, она же блочная, она же ящичная”

Сергей Викторович, большое спасибо за ресурс!
У меня такой вопрос: а если на биржевых графиках присутствуют пробелы ( так как в эти дни выходные на бирже), как мне от них избавиться, если я эти даты не использовал в моих рядах?

Источник статьи: http://baguzin.ru/wp/excel-birzhevaya-diagramma-ona-zhe-blochnaya-on/