Использование возможностей Excel для решения задач моделирования

Статистический анализ данных с помощью Пакета анализа данных

В качестве инструментального средства удобно использовать MS Excel для проведения статистического или инженерного анализа надстройку «Пакет анализа». Чтобы выполнять статистический анализ с помощью этого пакета с применением подходящей статистической или инженерной макрофункции, необходимо произвести первоначальную загрузку и активацию Пакета анализа данных при использовании данного пакета впервые. Функции анализа данных можно применять только на конкретном листе. Чтобы провести анализ данных на всех листах, повторите процедуру для каждого листа в отдельности.

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

Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Надстройки (рис. 3.48).

Рис. 3.48. Выбор настроек для добавления Пакета анализа данных

Еслииспользуется Excel не старше 2007, нажмите кнопку Microsoft Office и далее — кнопку Параметры Excel. В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти. Если вы используете Excel для Мае, в меню «Файл» выберите Сервис, Добавить-которые запускаются Excel.

В диалоговом окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК (рис. 3.49). Если Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы выполнить поиск. Если выводится сообщение о том, что Пакет анализа не установлен на компьютере, нажмите кнопку Да, чтобы установить его.

Рис. 3.49. Добавление Пакета анализа данных в Главное меню

На рис. 3.50 представлены пример входа в пакет Анализа данных и меню выбора инструментов анализа данных. Например, выбран режим Регрессия.

Рис. 3.50. Инструменты анализа данных

Пример 3.2

Одной из возможных целей исследования может быть следующая формулировка, представленная на рис. 3.51.

Рис. 3.51. Изображение рабочего листа с целью и выбранными факторами статистического моделирования.

Решение любой задачи статистического анализа с использованием пакетов прикладных программ начинается с процедуры ввода данных.

В качестве исходных рассмотрены динамические ряды, построенные на реальных отчетных данных, (табл. 3.43), которые необходимо проверить, насколько они соответствуют поставленной цели исследования и могут ли быть включены в факторную систему. Для примера взят ведущий мультимедийный холдинг России — РБК как лидер среди новостных и бизнесмедиа компаний медиаиндустрии, а также в сегменте регистрации доменов и хостинга.

Таблица 3.43

Исходные данные для анализа изменения рентабельности продаж по РБК за 2014;2016 г.

Для ввода этих данных в файл Excel копируется таблица: в Excel заносится число изучаемых явлений (динамических рядов, в нашем случае — 6), в поле Годы заносим число моментов (интервалов) времени, на которых рассматриваются динамические ряды (в нашем случае число лет — 3) (рис. 3.52).

После создания рабочей книги сохраняем ее под уникальным названием в соответствующую папку.

Рис. 3.52. Изображение рабочего листа с исходными данными

В Excel для графического представления рядов динамики используется меню Вставка графика (рис. 3.53).

Рис. 3.53. Изображение рабочего листа с графиками

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

Рис. 3.54. Выбор свойств графического изображения динамических рядов данных

Основные настройки пользователь может освоить самостоятельно, они соответствуют настройкам основных Windows-приложений.

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

Рис. 3.55. Динамика цепных темпов роста факторного признака

«Рентабельность продаж»

В главном меню выбираем процедуру Данные/Анализ данных.

Затем, в появившемся контекстном окне, выбираем пункт Описательная статистика — описательные статистики (рис. 3.56).

Рис. 3.56. Окно выбора процедуры Описательная статистика в меню Данные/Анализ данных

Процедура Описательная статистика предлагает пользователю определенный набор функций. Для этого выбираются все нужные переменные (рис. 3.57).

Для запуска процедуры необходимо воспользоваться кнопкой ОК. Результаты расчета основных статистических характеристик представлены на рис. 3.58.

Отметим, что программа рассчитывает только средние арифметические невзвешенные для всех переменных.

На основе рассчитанных показателей можно сделать некоторые выводы. Например, в среднем ежегодный показатель рентабельности РБК составляет 0,086; средняя стоимость услуг — 5903,5 руб.

Средний уровень ряда говорит о том, что в период с 2013 по 2016 г. средняя себестоимость оказания услуг составляла 9384,5 тыс. руб. Однако данный показатель не имеет большого смысла сам по себе и может быть использован, например, в сравнении с другими показателями.

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

• • с 2011 no 2016 г. в целом наблюдалось повышение рентабельности продаж по сравнению с 2011 базисным годом;
• • в 2014 и 2016 гг. наблюдается рост рентабельности по продажам в рассматриваемом примере, а в 2015 г. наблюдается резкий спад.

Рис. 3.57. Окно выбора функций для расчета основных статистических

показателей динамического ряда

Рис. 3.58. Результаты расчета средних показателей динамики

Таким образом, можно выделить три периода:

• 1) 2011—2014 гг. — период роста;
• 2) 2015 г. — период заметного спада;
• 3) 2016 г. — период резкого роста.

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

В рамках работы проведено сглаживание динамического ряда трсхи четырехчленными скользящими средними (если это позволяет длина динамического ряда).

Для начала работы воспользуемся уже известным нам меню Данные/ Анализ данных/Скользящее среднее. Напомним, что работать необходимо с результирующей переменной, по которой будет вестись прогнозирование (рис. 3.59).

Рис. 3.59. Вид закладки Скользящее среднее в меню Данные/Анализ данных.

Для получения трехчленной скользящей средней в поле интервала сглаживания ставим цифру 3 (N = 3), выбираем выходной интервал и вывод графика и нажимаем на кнопку ОК (рис. 3.60).

Рис. 3.60. Заполнение закладки Скользящее среднее в меню Данные/Анализ данных.

Система автоматически строит графическое изображение, выраженное скользящей средней с заданными параметрами (рис. 3.61).

Рис. 3.61. Исходный динамический ряд, график и скользящая трехлетняя средняя

В пакете Анализа данных Excel все перечисленные этапы реализуются в рамках одной процедуры Скользящее среднее.

Для решения поставленной задачи по аналитическому сглаживанию динамических рядов с помощью Мастера диаграмм потребуется сделать активным с помощью правой кнопки мыши в зоне графика следующее меню (рис. 3.62).

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

В итоге получается следующий рабочий лист (рис. 3.63).

Рис. 3.62. Запуск процедуры Добавить линию тренда.

Рис. 3.63. Рабочий лист с вариантами выбора параметров линии тренда.

Данная процедура позволяет строить регрессионные модели как линейного, так и нелинейного тина (рис. 3.64).

Рис. 3.64. Исходный динамический ряд и основные модели тренда, предлагаемые в пакете Анализа данных Excel

Сравним полученные модели тренда (рис. 3.65).

Рис. 3.63. Сравнение полученных моделей тренда, но коэффициенту Фишера

На рис. 3.66—3.68 представлены этапы проверки статистической значимости построенных моделей.

Рис. 3.66. Расчет табличного коэффициента Фишера средствами Excel

Рис. 3.67. Сравнение полученных моделей тренда по коэффициенту Стьюдента

Рис. 3.68. Расчет табличного коэффициента Стьюдента.

Построение итоговых характеристик построенных уравнений тренда

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

Проведем прогнозирование на основе экстраполяции лучшей формы тренда (полиномиальной) для нашей переменной (рис. 3.69).

Рис. 3.69. Расчет рентабельности продаж по чистой выручке на 2017 и 2018 гг. по полиномиальной модели тренда

Для подсчета минимальных и максимальных значений доверительного интервала необходимо сначала найти стандартную ошибку. Для этого проводится регрессионный анализ (рис. 3.70).

Рис. 3.70. Запуск процедуры Данные/Анализ/Регрессия

Для запуска процедуры необходимо воспользоваться кнопкой ОК.

Результаты расчета регрессионной статистики представлены на рис. 3.71.

Рис. 3.71. Данные регрессионной статистики

Исходя из полученных данных можно провести расчет минимальных и максимальных значений доверительного интервала (приблизительный прогноз на 2017 и 2018 гг. Для более точного прогноза лучше воспользоваться пакетом STATISTICA Data Miner Release 7) (рис. 3.72, 3.73).

Рис. 3.72. Расчет минимальных значений доверительного интервала

составлять от 0,1878 до 0,3552; рентабельность продаж по чистой выручке в 2018 г. будет составлять от 0,2735 до 0,4409.

Рис. 3.75. Расчет ошибки аппроксимации.

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

Рис. 3.76. Графическое представление результатов моделирования.

Рис. 3.77. Графическое представление доверительных интервалов.

Для этого создаем новый рабочий лист уже известным способом и копируем на него данные исходного ряда, однако расширяем ось времени до горизонта планирования (2017 и 2018 гг). Далее добавляем еще две переменные, соответствующие нижней и верхней границам доверительных интервалов прогноза. В столбец с исходными данными в пустые строки вбиваем точечные прогнозы по уравнению тренда, а в столбцы с границами доверительных интервалов — соответствующие значения. Далее строим графическое изображение всех переменных.

При условии статистической значимости уравнения и параметров модель может быть использована для прогнозирования. В нашем случае этого нс наблюдается.

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

Приведено решение конкретного примера с использованием пакета Анализа данных Excel.