Методика обработки статистических данных в среде Excel
Среди различных методов прогнозирования нельзя не выделить аппроксимацию. С её помощью можно производить приблизительные подсчеты и вычислять планируемые показатели, путем замены исходных объектов на более простые. В Excel тоже существует возможность использования данного метода для прогнозирования и анализа.
Наименование данного метода происходит от латинского слова proxima – «ближайшая» Именно приближение путем упрощения и сглаживания известных показателей, выстраивание их в тенденцию и является его основой. Но данный метод можно использовать не только для прогнозирования, но и для исследования уже имеющихся результатов. Ведь аппроксимация является, по сути, упрощением исходных данных, а упрощенный вариант исследовать легче.
Главный инструмент, с помощью которого проводится сглаживания в Excel – это построение линии тренда. Суть состоит в том, что на основе уже имеющихся показателей достраивается график функции на будущие периоды. Основное предназначение линии тренда, как не трудно догадаться, это составление прогнозов или выявление общей тенденции.
Линия тренда может быть построена с применением одного из пяти видов аппроксимации:
– линейной;
– экспоненциальной;
– логарифмической;
– полиномиальной;
– степенной.
Рассмотрим данный метод чтобы произвести прогнозирование роста трафика по магистральным линиям связи на основе имеющихся статистических данных.
|
|
|
Исходные данные приведены в таблице 1.
Линейная регрессия
Рассмотрим самый простой вариант аппроксимации с помощью линейной функции. Построим график, на основании которого будем проводить процедуру сглаживания. Для построения графика возьмем исходные данные из таблицы 1. Графическая функция, которую мы построим, будет отображать зависимость объема трафика по магистральным линиям связи от временного интервала в течение 9 лет.
Для построения графика, прежде всего выделяем столбцы "год" и столбец с объемом трафика. После этого перемещаемся во вкладку "Вставка". Далее на ленте в блоке инструментов "Диаграммы" щелкаем по кнопке "График". В открывшемся списке выбираем наименование "График с маркерами" (рисунок 14). Именно данный вид диаграмм наиболее подходит для работы с линией тренда, а значит, и для применения метода аппроксимации в Excel.
Рисунок 14 – Алгоритм построения исходного графика
Исходный график представлен на рисунке 15.
Рисунок 15 – Исходный график
Для добавления линии тренда выделяем исходный график кликом правой кнопки мыши. Появляется контекстное меню. Выбираем в нем пункт "Добавить линию тренда…" (рисунок 16).
|
|
|
Рисунок 16 – Добавление линии тренда
Далее откроется окно формата. В блоке параметров «Параметры линии тренда» устанавливаем переключатель в позицию «Линейная».
Также устанавливаем галочку около позиции «Показывать уравнение на диаграмме». После этого на диаграмме будет отображаться уравнение сглаживающей функции.
Также в нашем случае для сравнения различных вариантов аппроксимации важно установить галочку около пункта «Поместить на диаграмму величину достоверной аппроксимации (R^2)». Данный показатель может варьироваться от 0 до 1. Чем он выше, тем аппроксимация качественнее (достовернее). Считается, что при величине данного показателя 0,85 и выше сглаживание можно считать достоверным, а если показатель ниже, то – нет.
После того, как провели все вышеуказанные настройки. Жмем на кнопку «Закрыть», размещенную в верхней части окна (рисунок 17).
Рисунок 17 – Формат линии тренда
Как видим, на графике линия тренда построена. При линейной аппроксимации она обозначается синей пунктирной прямой полосой. График линейной аппроксимации приведен на рисунке 18.
|
|
|
Рисунок 18 – График линейной аппроксимации
Сглаживание, которое используется в данном случае, описывается следующей формулой (2):
, (2)
В конкретно нашем случае формула принимает такой вид:
В данном случае коэффициент детерминации при линейной аппроксимации равен R2=0,722, что характеризует выбранную модель, как модель приемлемого качества.
Существует еще один способ вычисления коэффициентов линейной регрессии и коэффициента детерминации – с помощью пакета анализа данных.
Кликаем по кнопке «Анализ данных». Она размещена во вкладке «Данные» в блоке инструментов «Анализ» (рисунок 19).
Рисунок 19 – Анализ данных
Открывается небольшое окошко. В нём выбираем пункт «Регрессия». Жмем на кнопку «OK» (рисунок 20).
Рисунок 20 – Анализ данных
Открывается окно настроек регрессии. В нём обязательными для заполнения полями являются «Входной интервал Y» и «Входной интервал X». Все остальные настройки можно оставить по умолчанию.
В поле «Входной интервал Y» указываем адрес диапазона ячеек, где расположены переменные данные, влияние факторов на которые мы пытаемся установить. В нашем случае это будут ячейки столбца «Объем трафика». Адрес можно вписать вручную с клавиатуры, а можно, просто выделить требуемый столбец. Последний вариант намного проще и удобнее.
|
|
|
В поле «Входной интервал X» вводим адрес диапазона ячеек, где находятся данные того фактора, влияние которого на переменную мы хотим установить. Как говорилось выше, нам нужно установить влияние числа кварталов на объем трафика, а поэтому вводим адрес ячеек в столбце «X=» (рисунок 21).
Рисунок 21 – Ввод входных данных
С помощью других настроек можно установить метки, уровень надёжности, константу-ноль, отобразить график нормальной вероятности, и выполнить другие действия. Но, в большинстве случаев, эти настройки изменять не нужно. Единственное на что следует обратить внимание, так это на параметры вывода. По умолчанию вывод результатов анализа осуществляется на другом листе, но переставив переключатель, можно установить вывод в указанном диапазоне на том же листе, где расположена таблица с исходными данными, или в отдельной книге, то есть в новом файле.
После того, как все настройки установлены, жмем на кнопку «OK» (рисунок 21).
Результаты регрессионного анализа выводятся в виде таблицы на новом рабочем листе (рисунок 22).
Рисунок 22 – Результаты регрессионного анализа
Коэффициент детерминации (R-квадрат), а также коэффициенты линейной регрессии совпадает с значением, полученным при построении линии тренда.
Экспоненциальная регрессия
Рассмотрим экспоненциальный тип аппроксимации в Excel.
Для того, чтобы изменить тип линии тренда, выделяем её кликом правой кнопки мыши и в раскрывшемся меню выбираем пункт «Формат линии тренда…» (рисунок 23).
Рисунок 23 – Формат линии тренда
После этого запускается уже знакомое нам окно формата. В блоке выбора типа аппроксимации устанавливаем переключатель в положение «Экспоненциальная». Остальные настройки оставим такими же, как и в первом случае. Щелкаем по кнопке «Закрыть» (рисунок 24).
Рисунок 24 – Формат линии тренда
После этого линия тренда будет построена на графике (рисунок 25). Как видим, при использовании данного метода она имеет несколько изогнутую форму. При этом уровень достоверности равен 0,7821, что выше, чем при использовании линейной аппроксимации.
Рисунок 25 – График экспоненциальной аппроксимации
Общий вид функции сглаживания при этом такой (3):
, (3)
где e – это основание натурального логарифма.
В конкретно нашем случае формула приняла следующую форму:
В данном случае коэффициент детерминации при экспоненциальной аппроксимации равен R2=0,7821, что характеризует выбранную модель, как модель приемлемого качества.
Логарифмическая регрессия
Рассмотрим метод логарифмической аппроксимации в Excel.
Тем же способом, что и в предыдущий раз через контекстное меню запускаем окно формата линии тренда. Устанавливаем переключатель в позицию «Логарифмическая» и жмем на кнопку «Закрыть» (рисунок 26).
Рисунок 26 – Формат линии тренда
Происходит процедура построения линии тренда с логарифмической аппроксимацией. График логарифмической аппроксимации приведен на рисунке 27.
Рисунок 27 – График логарифмической аппроксимации
В общем виде формула сглаживания выглядит так (4):
, (4)
где ln – это величина натурального логарифма. Отсюда и наименование метода.
В нашем случае формула принимает следующий вид:
В данном случае коэффициент детерминации при логарифмической аппроксимации равен R2=0,4229, что характеризует выбранную модель, как модель плохого качества и говорит о невозможности её использования для прогноза.
Полиномиальная регрессия
Рассмотрим метод полиномиального сглаживания в Excel.
Переходим в окно формата линии тренда. В блоке параметров «Параметры линии тренда» устанавливаем переключатель в позицию «Полиномиальная». Справа от данного пункта расположено поле «Степень». При выборе значения «Полиномиальная» оно становится активным. Здесь можно указать любое степенное значение от 2 (установлено по умолчанию) до 6. Данный показатель определяет число максимумов и минимумов функции. При установке полинома второй степени описывается только один максимум, а при установке полинома шестой степени может быть описано до пяти максимумов. Оставим настройки по умолчанию, то есть, укажем вторую степень. Остальные настройки оставляем такими же, какими мы выставляли их в предыдущих способах. Жмем на кнопку «Закрыть» (рисунок 28).
Рисунок 28 – Формат линии тренда
Линия тренда с использованием данного метода представлена на рисунке 29. Как видим, она ещё более изогнута, чем при использовании экспоненциальной аппроксимации.
Рисунок 29 – График полиномиальной аппроксимации
Данный метод наиболее успешно можно применять в том случае, если данные носят постоянно изменчивый характер. Функция, описывающая данный вид сглаживания, выглядит таким образом (5):
, (5)
В нашем случае формула принимает следующий вид:
В данном случае коэффициент детерминации при полиномиальной аппроксимации равен R2=0,9163, что характеризует выбранную модель, как модель хорошего качества.
Степенная регрессия
Рассмотрим метод степенной аппроксимации в Excel.
Перемещаемся в окно «Формат линии тренда». Устанавливаем переключатель вида сглаживания в позицию «Степенная». Показ уравнения и уровня достоверности оставляем включенными. Жмем на кнопку «Закрыть» (рисунок 30).
Рисунок 30 – Формат линии тренда
Линия тренда с использованием данного метода представлена на рисунке 31.
Рисунок 31 – График степенной аппроксимации
Общая формула, описывающая данный метод имеет такой вид (6):
, (6)
В конкретно нашем случае она выглядит так:
В данном случае коэффициент детерминации при степенной аппроксимации равен R2=0,5034, что характеризует выбранную модель, как модель плохого качества и говорит о невозможности её использования для прогноза.
График со всеми видами аппроксимации представлен на рисунке 32.
Рисунок 32 – График всех видов аппроксимации
На основе найденных статистических закономерностей определим ожидаемое значение объема трафика по магистральным линиям связи в период с 37 по 40 квартал т.е. на год вперед. Как видим на 40 квартале объем трафика составит 570 единиц, если использовать полиномиальную аппроксимацию.
График прогноза приведен на рисунке 33.
Рисунок 33 – График прогноза
Заключение
В 1 разделе были рассмотрены требования к прогнозу, цель и задачи прогнозирования. Также были рассмотрены принципы, на которых основан процесс прогнозирования.
Во 2 разделе были рассмотрены методы статистической обработки данных.
В 3 разделе была изучена методика обработки статистических данных в среде MathCAD с использованием регрессионного анализа. Были построены графики и рассчитаны коэффициенты детерминации для линейной, экспоненциальной, логарифмической, полиномиальной и экспоненциальной регрессий.
При использовании линейной регрессии коэффициент детерминации составил kd=0,772, что характеризует выбранную модель, как модель приемлемого качества.
При использовании экспоненциальной регрессии коэффициент детерминации составил kd=0,859, что характеризует выбранную модель, как модель хорошего качества.
При использовании логарифмической регрессии коэффициент детерминации составил kd=0,423, что характеризует выбранную модель, как модель плохого качества и говорит о невозможности её использования для прогноза.
При использовании полиномиальной регрессии коэффициент детерминации составил kd=0,916, что характеризует выбранную модель, как модель хорошего качества.
При использовании степенной регрессии коэффициент детерминации составил kd=0,94, что характеризует выбранную модель, как модель хорошего качества.
Наибольший уровень достоверности в среде MathCAD показал метод степенной регрессии (kd=0,94), наименьший уровень достоверности у логарифмической регрессии (kd=0,423).
На основе найденных статистических закономерностей определено ожидаемое значение объема трафика по магистральным линиям связи в период с 37 по 40 квартал т.е. на год вперед. На 40 квартале объем трафика составил 700 единиц, при использовании степенного уравнения регрессии.
В 4 разделе была изучена методика обработки статистических данных в среде Excel с использованием метода аппроксимации с построением линии тренда. Были построены графики и рассчитаны коэффициенты детерминации для линейной, экспоненциальной, логарифмической, полиномиальной и экспоненциальной регрессий.
При использовании линейной аппроксимации коэффициент детерминации составил kd=0,772, что характеризует выбранную модель, как модель приемлемого качества.
При использовании экспоненциальной аппроксимации коэффициент детерминации составил kd=0,7821, что характеризует выбранную модель, как модель приемлемого качества.
При использовании логарифмической регрессии коэффициент детерминации составил kd=0,4229, что характеризует выбранную модель, как модель плохого качества и говорит о невозможности её использования для прогноза.
При использовании полиномиальной регрессии коэффициент детерминации составил kd=0,9163, что характеризует выбранную модель, как модель хорошего качества.
При использовании степенной регрессии коэффициент детерминации составил kd=0,5034, что характеризует выбранную модель, как модель плохого качества и говорит о невозможности её использования для прогноза.
Наибольший уровень достоверности в среде Excel показал метод полиномиальной аппроксимации (kd=0,9163), наименьший уровень достоверности у логарифмической регрессии (kd=0,4229).
На основе найденных статистических закономерностей определено ожидаемое значение объема трафика по магистральным линиям связи в период с 37 по 40 квартал т.е. на год вперед. На 40 квартале объем трафика составил 570 единиц, при использовании полиномиальной аппроксимации.
Дата добавления: 2021-06-02; просмотров: 349; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!