Линии тренда и другие аналитические элементы диаграмм

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

Линия тренда – непрерывная математическая кривая, аппроксимирующие точки графика (т.е. проходя­щие на макси­мально близком расстоянии от них, рис. 13.13, а). Линия тренда определяется математическим уравнением (эмпирической форму­лой). В Microsoft Excel поддерживаются следующие линии тренда:

Ø Линейная (уравнение вида );

Ø Полиноминальная n -й степени (; уравнение линии имеет вид );

Ø Степенная (уравнение вида , a и b – действительные числа);

Ø Экспоненциальная (уравнение вида , a и b – действительные числа);

Ø Логарифмическая (уравнение вида , a и b – действи­тель­ные числа);

Ø Линейная фильтрация – реальзуется алгоритм скользящего среднего по n точкам. Линия тренда не определена для первых n -1 точек, для каждой последующей точки она определяется как среднее арифметическое значений этой и n -1 предшествующих точек.

Каждый из рядов данных может иметь свою линию тренда.

                    а)                                              б)                                                 в)

                                                г)                                               д)

Рис. 13.13 Примеры аналитических элементов диаграмм Microsoft Excel

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

После добавления линии тренда становятся такими же объектами диаграммы, как и ряды данных. Они также отображаются в легенде диаграммы. Их также можно форматировать через диалоговое окно свойств объектов. В этом окне настраиваются тип и цвет линии, эффекты свечения и сглаживания.

Математические параметры линии задаются на вкладке «Параметры линии тренда» (рис. 13.14). Здесь выбирается тип линии тренда (для полиноминальной линии можно выбрать степень многочлена, для скользящего среднего – количество точек). Ниже можно задать имя кривой тренда, продлить линию на определенное количество категорий вперед и назад для прогно­зи­рования, принудительно за­дать точку пересечения с осью Y (только для линейных, полиноминальных, экспо­нен­циальных линий). Здесь же включается отображение математического уравнения линии тренда (уравнения регрессии) на диаграмме и коэффициента R ² достоверности аппроксимации (чем он ближе к 1, тем лучше линия тренда соответствует исходным данным). Уравнение и коэффициент достоверности недоступны для скользящего среднего.

Уравнение линии тренда (или уравнение с коэффициентом достоверности аппроксимации) является отдельным элементом диаграммы, по сути надписью. Через окно свойств можно задать числовой формат его коэффициентов, цвет заливки фона надписи, цвет и тип ее границ, тень и свечение границ, а также выравнивание уравнения внутри контура.

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

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

Линии максимума-минимума – вертикальные линии, связывающие на графике минимальное и максимальное значение в каждой категории (рис. 13.13, в). Добавляются также при помощи кнопки « Линии» (команда Коридор колебаний). Настраиваются аналогично линиям проекции.

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

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

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

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

Значения погрешностей (т.е. длины маркеров) в Microsoft Excel могут задаваться различными способами:

Ø Фиксированное значение – пользователь вручную задает величину погрешности (в единицах оси) для всех точек;

Ø Относительное значение – значение погрешности для всех точек задается в виде процента от значения точки. Соответственно, чем больше значение в точке, тем выше ее погрешность и длиннее маркер;

Ø Стандартное отклонение – погрешность для каждой точки откладывается от среднего арифметического всех точек графика; величина погрешности задается как произведение среднеквадратического отклонения (s) на число N, заданное пользователем. Например, часто можно утверждать, что случайная величина практически гарантировано попадает в коридор ±3s от ее средней величины (математического ожидания) – этот коридор можно отобразить на диаграмме;

Ø Стандартная погрешность – согласно справочной системе Microsoft Excel вычисляется по формуле , где  – значение точки,
n – количество точек в ряду. Погрешность откладывается из каждой точки симметрично: половина погрешности вниз и половина вверх;

Ø Пользовательская – пользователю необходимо сформировать два дополнительных столбца с данными. Один содержит положительные погрешности для каждой точки диаграммы, второй – отрицательные.

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

Маркеры погрешностей также являются отдельным элементом диаграммы, которые настраиваются через диалоговое окно свойств объекта. Как и для других объектов, можно настроить тип и цвет линии, тень и свечение. Вкладка параметров маркера показана на рис. 13.15. Здесь можно показать обе половины маркера или только одну из них (верхнюю или нижнюю), нарисовать маркер с засечками или без них, выбрать тип маркера. Если будет выбран пользовательский тип, кнопка «Укажите значение» открывает окно, в котором задаются две дополнительных группы ячеек с погрешностями (одна – с положительными, вторая – с отрицательными). В общем случае пользовательские погрешности могут не быть симметричны относительно самих точек.

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

Спарклайны

Помимо собственно диаграмм, доступных в большинстве старых версий Microsoft Excel, в Microsoft Excel 2010 появился новый инструмент графической визуализации данных – спарклайны (мини-диаграммы). Спарклайн – миниатюрная диаграмма, размещаемая Microsoft Excel внутри конкретной ячейки. По сути спарклайн является динамически обновляемым фоном ячейки. Благодаря этому в ячейку с мини-диаграммой также можно внести данные, хотя читабельность такой ячейки резко ухудшится. Пример мини-диаграмм показан на рис. 13.16.

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

Для создания мини-диаграмм служат кнопки « График», « Столбец» и « Выигрыш/проигрыш» в группе Спарклайны вкладки Вставка ленты инструменты. Любая из кнопок открывает диалоговое окно рис. 13.17, в верхнем поле которого выбирается один ряд данных, а в нижнем – ячейка, в которой будет размещена мини-диаграмма. Можно выбрать несколько рядов и такое же количество ячеек – будет сформировано несколько мини-диаграмм, которые будут автоматически сгруппированы.

Мини-диаграмма не имеет координатных осей, поэтому подписи оси категории при его построении не задаются.

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

После выделения ячейки со вставленной мини-диаграммой на экране отобразится группа лент «Работа со спарклайнами» с единственной вкладкой Конструктор (в дальнейшем – «Конструктор спарклайнов», рис. 13.18).

Рис. 13.18 Вкладка Конструктора спарклайнов

Команды кнопки « Изменить данные» позволяют повторно выбрать исходные данные. В частности, команда Изменить данные и расположение группы… открывает окно рис. 13.17. Для сгруппированных мини-диаграмм дополнительно доступна команда Изменить данные отдельного спарклайна…. Она открывает окно с единственным полем, в котором указаны ссылки на ряд данных для конкретной мини-диаграммы группы.

Команда Скрытые и пустые ячейки… открывает окно рис. 13.8 (команда доступна, если подобные ячейки в прямоугольнике данных есть), а команда Строка/Столбец транспонирует группу мини-диаграмм (команда доступна, если ячейки с исходными данными группы образуют квадрат).

Кнопки в группе Тип позволяют быстро изменить тип выделенной мини-диаграммы. Группа Стиль позволяет настроить цвет мини-диаграммы: выбрать его из галереи или задать непосредственно в выпадающем списке кнопки « Цвет спарклайна». Для мини-диаграммы типа «Выигрыш/проигрыш» цвет прямоугольников, отображающих отрицательные значения, задается в выпадающем списке «Отрицательные точки», который, в свою очередь, размещен в выпадающем списке кнопки « Цвет маркера».

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

Кнопки « Сгруппировать» и « Разгруппировать» позволяют, соответственно, объединить несколько мини-диаграмм в группу или разделить эту группу на отдельные мини-диаграммы. Если мини-диаграммы сгруппированы, инструменты в группах Тип, Показать и Стиль применяют форматирование сразу ко всей группе!

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

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

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

Команда Отобразить данные справа налево в выпадающем списке кнопки « Ось» меняет порядок отображения данных по горизонтали, зеркально отражая мини-диаграмму.

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