Если вы выполняете статистический анализ, вам может потребоваться создать стандартную блочную диаграмму, чтобы показать распределение набора данных. На такой диаграмме числовые данные разделены на квартили, а между первым и третьим квартилем находится прямоугольник с дополнительной линией, проходящей через второй квартиль и обозначающей медиану. На некоторых блочных диаграммах минимальные и максимальные значения, которые выходят за пределы первого и третьего квартилей, представлены в виде линий, которые часто называют усами.
Хотя в Excel 2013 нет шаблона для блочной диаграммы, вы можете создать ее, выполнив следующие действия:
-
Рассчитайте значения квартилей на основе исходного набора данных.
-
Вычислите разницу между квартилями.
-
Создайте гистограмму с накоплением из диапазонов квартилей.
-
Преобразуйте гистограмму в блочную диаграмму.
В нашем примере исходный набор источник содержит три столбца. Каждый столбец включает по 30 элементов из следующих диапазонов:
-
Столбец 1 (2013 г.): 100–200
-
Столбец 2 (2014 г.): 120–200
-
Столбец 3 (2015 г.): 100–180
В этой статье
Шаг 1. Вычисление значений квартилей
Прежде всего необходимо рассчитать минимальное, максимальное значение и медиану, а также первый и третий квартили для набора данных.
-
Для этого создайте вторую таблицу и заполните ее следующими формулами:
Значение
Формула
Минимальное значение
МИН(диапазон_ячеек)
Первый квартиль
КВАРТИЛЬ.ВКЛ (диапазон_ячеек; 1)
Медиана
КВАРТИЛЬ.ВКЛ (диапазон_ячеек; 2)
Третья квартиль
КВАРТИЛЬ.ВКЛ (диапазон_ячеек; 3)
Максимальное значение
МАКС(диапазон_ячеек)
-
В результате должна получиться таблица, содержащая нужные значения. При использовании примера данных получаются следующие квартили:
Шаг 2. Вычисление разницы между квартилями
Затем нужно вычислить разницу между каждой парой показателей. Необходимо рассчитать разницу между следующими значениями:
-
первым квартилем и минимальным значением;
-
медианой и первым квартилем;
-
третьим квартилем и медианой;
-
максимальным значением и третьим квартилем.
-
Для начала создайте третью таблицу и скопируйте в нее минимальные значения из последней таблицы.
-
Вычислите разницу между квартилями с помощью формулы вычитания Excel (ячейка1 — ячейка2) и заполните третью таблицу значениями разницы.
Для примера данных третья таблица выглядит следующим образом:
Шаг 3. Создание гистограммы с накоплением
Данные в третьей таблице хорошо подходят для построения блочной диаграммы. Создадим гистограмму с накоплением, а затем изменим ее.
-
Выделите все данные в третьей таблице и щелкните Вставка > Вставить гистограмму > Гистограмма с накоплением.
Пока диаграмма не похожа на блочную, так как Excel по умолчанию рисует столбцы с накоплением на основе наборов данных по горизонтали, а не по вертикали.
-
Чтобы поменять местами оси диаграммы, щелкните ее правой кнопкой мыши и выберите Выбор данных.
-
Щелкните Строка/столбец.
Советы:
-
Чтобы переименовать столбцы, в области Подписи горизонтальной оси (категории) щелкните Изменить, выберите в третьей таблице диапазон ячеек с нужными именами категорий и нажмите кнопку ОК.
-
Для переименования записей легенды в разделе Элементы легенды (ряды) щелкните Изменить и введите нужное значение.
-
-
Нажмите кнопку ОК.
График должен выглядеть так, как показано ниже. В этом примере также было изменено название диаграммы, а легенда скрыта.
Шаг 4. Преобразование гистограммы с накоплением в блочную диаграмму
Скрытие нижнего ряда данных
Чтобы преобразовать гистограмму с накоплением в блочную диаграмму, сначала нужно скрыть нижний ряд данных:
-
Выберите нижнюю часть столбцов.
Примечание: При щелчке одного из столбцов будут выбраны все вхождения того же ряда.
-
Щелкните Формат > Текущий фрагмент > Формат выделенного фрагмента. В правой части появится панель Формат.
-
На вкладке Заливка панели Формат выберите пункт Нет заливки.
Нижний ряд данных будет скрыт.
Создание усов для построения блочной диаграммы
Далее нужно заменить верхний и второй снизу ряды (выделены на рисунки темно-синим и оранжевым) линиями (усами).
-
Выберите верхний ряд данных.
-
На вкладке Заливка панели Формат выберите пункт Нет заливки.
-
На ленте щелкните Конструктор > Добавить элемент диаграммы > Предел погрешностей > Стандартное отклонение.
-
Выберите один из нарисованных пределов погрешностей.
-
Откройте вкладку Параметры предела погрешностей и в области Формат задайте следующие параметры:
-
В разделе Направление установите переключатель Минус.
-
Для параметра Конечный стиль задайте значение Без точки.
-
Для параметра Величина погрешности укажите процент, равный 100.
-
-
Повторите предыдущие действия для второго снизу ряда данных.
Гистограмма с накоплением теперь должна напоминать блочную диаграмму.
Закрашивание центральных областей
Блочные диаграммы обычно отображаются с одним цветом заливки и тонкими границами. Ниже описано, как завершить их оформление.
-
Выберите верхнюю часть блочной диаграммы.
-
На вкладке "Заливка & линии" на панели "Формат" нажмите кнопку "Сплошная заливка".
-
Выберите цвет заливки.
-
Щелкните Сплошная линия на этой же вкладке.
-
Выберите цвет контура, а также ширину штриха.
-
Задайте те же значения для других областей блочной диаграммы.
В результате должна получиться блочная диаграмма.
