Новая версия табличного редактора фирмы Microsoft, вышедшая в 2015 году для платформ Windows и OS X и получившая наименование Microsoft Excel 2016 получила ряд новых функций, в том числе в области анализа и визуализации данных.
Так в версии для Mac, финальный релиз которой был представлен в июле 2015 года [1] появились новые функции: рекомендуемые диаграммы, срезы сводной таблицы и пакет анализа. Больший функционал получила, вышедшая двумя месяцами позже версия для Windows [2]. Так при наличии данных в виде временных рядов стандартные средства новой версии табличного редактора позволяют строить прогнозы.
Создание прогноза
Для создание прогноза необходимо наличие данных в виде временного ряда. Стандартный временной ряд содержит набор значений даты или времени и соответствующий этим значениям набор данных показателя. Прогнозирование временного ряда заключается в построении его продолжения на заданный интервал времени.
Для временного ряда устанавливаются следующие требования:
одинаковый интервал между точками наблюдений;
количество пробелов данных на уровне не более 30%
Для построения прогноза используется инструмент «Лист прогноза», добавленный на вкладку данные в новой версии программы.
Рисунок 1 – Интерфейс части вкладки «Данные» для MS Excel 2016
Визуализация и настройка прогноза
Для отображения прогноза используется два стандартных методы: график и гистограмма (рисунок 2)
Рисунок 2 – Построение прогноза численности населения России средствами MS Excel 2016
Представление данных не несет в себе принципиальных различий, в обоих случаях содержательной частью визуализации являются: представление данных о прогнозном значении и об интервалах, в котором данное значение может изменятся с заданной вероятностью.
Прогноз содержит 8 настроек (таблица 1):
Таблица 1 – Использование дополнительных параметров прогноза
|
Параметр прогноза |
Описание параметра |
Особенности настройки |
| Начало прогноза | Дата или время с которого начинается прогнозирование данных | В случае установления даты до окончания имеющегося временного ряда используются данные, предшествующие дате начала прогноза. Данный способ используется для так называемого «ретроспективного прогноза». При ретроспективном прогнозе возможно сравнение данных полученных прогнозным путем с фактическими данными |
| Доверительный интервал | Диапазон вокруг каждого предсказанного значения, с заданной вероятностью для которого, фактическое значение будет попадать в его границы | Доверительный интервал может быть убран из элементов визуализации, если надобность в нем отсутствует. По умолчанию значение показателя находится на уровне 95%, однако возможно изменение данного показателя на нужное значение, при этом увеличение параметра расширяет диапазон данных. Границы изменения вероятности от 0 до 99,99% |
| Сезонность | Длина (количество точек) сезона. | Возможно автоматическое или ручное определение длины сезона. Так, если заранее известно, что данные представляют собой ежегодный цикл, разбитый по месяцам, то целесообразно установить значение для сезонности равное 12. В случае не определения сезонной составляющей предсказанная часть временного ряда примет форму линейного тренда |
| Диапазон временной шкалы | Диапазон значений временной шкалы | Диапазоны данных параметров должны соответствовать по длине друг другу |
| Диапазон значений | Диапазон значений показателя | |
| Заполнить отсутствующие точки с помощью | Метод заполнения отсутствующих значений | На выбор пользователя предлагается заполнить пустые значения показателя или нулями или значениями полученными методом интерполяции. В случае интерполяции отсутствующие значения заполняются на основании соседних |
| Объединить дубликаты с помощью | Метод исключения повторных значений, имеющих одну временную метку. | При наличии значений с одной временной меткой, имеющие разные показатели, для прогноза используется или среднее этих значений или другая характеристика на выбор пользователя (максимум, минимум, медиана, сумма и т.д.) |
| Включить статистические данные прогноза | Отображение статистики по прогнозу | При установке флажка напротив данного параметра на новом листе выводится дополнительная статистическая информация, созданная функцией «ПРЕДСКАЗ.ETS.СТАТ», а также метрики ошибки и коэффициенты сглаживания |
Формулы, используемые для прогноза
При прогнозировании строится таблица, содержащая как исходные данные, так и прогнозные и границы доверительных интервалов. Прогноз представляет собой значения рассчитанные при помощи алгоритма экспоненциального сглаживания (ETS) по версии ААА [3].
Прогнозируемые значения строятся посредством функции «ПРЕДСКАЗ.ETS», столбцы для определения границ доверительных интервалов вычисляются «ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ»
Вывод
Введение новой функции «Лист прогноза» представляет собой реализацию анализа временных рядов методом экспоненциального сглаживания. Данный метод не является единственным в анализе временных рядов, однако он выбран фирмой Microsoft в качестве единственного для прогноза. Наличие настроек прогноза позволяет не только улучшить итоговый вывод результата, но и повысить его точность. Однако отсутствие выбора метода прогнозирование в значительной степени снижает ценность нововведения MS Excel 2016.
Библиографический список
- Microsoft Office 2016 для Mac OS Office 365 [электронный ресурс]. URL: https://products.office.com/ru-ru/mac/microsoft-office-for-mac (дата обращения: 22.08.2016).
- Новые и улучшенные возможности в Office 2016 с Office 365 [электронный ресурс]. URL: https://support.office.com/ru-ru/article/Новые-и-улучшенные-возможности-в-Office-2016-с-Office-365-95c8d81d-08ba-42c1-914f-bca4603e1426 (дата обращения: 22.08.2016).
- Грешилов, А.А. Математические методы построения прогнозов. / А.А. Грешилов, В.А. Стакун, А.А. Стакун – М.: Радио и связь, 1997. – 112 с. – ISBN 5-256-01352-1