Построение линии тренда (MS Excel)
Постройте в MS Excel диаграммы и линии регрессии (тренда) всех типов (линейную, полиномиальную, логарифмическую, степенную, экспоненциальную) с прогнозом на несколько лет, уравнением и коэффициентом детерминации для данных, представленных в таблице.
Выберите среди них наилучшую линию, обоснуйте выбор.
Итак, копируем таблицу в новый документ Excel. Выделяем диапазон А1О2 (столбец В удаляем предвариетльно, х и у не нужны при построении) и вызываем построитель диаграмм (кнопка Мастер диаграмм на панели интрументов). Выбираем тип диаграммы – График
Нажимаем кнопку «Далее» и затем переходим на вкладку «Ряд». Здесь нужно удалить ряд «Год» (это лишний график), нажимаем кнопку «Далее», убираем вывод легенды, в различных вкладках оформляем заголовки, подписи и прочее, на свой вкус. Далее выбираем, где поместить диаграмму. После того, как диаграмма создана, можно изменить заливку области диаграммы и области построения (правая кнопка мыши – Формат области диаграммы) или выделить область диаграммы или область построения и на панели инструментов воспользоваться заливкой, как обычно. Можно оформить градиентом или узором. Линии и маркеры тоже можно поменять.
Нужно добавить линию тренда. Для этого ПКМ на линии данных и выбираем Добавить линию тренда
Выберем первый вариант – линейная линия тренда (регрессии).
Перейдем на другую вкладку и установим параметры:
Прогноз на три периода, показывать уравнение и выводить величину достоверности аппроксимации (коэффициент детерминации R^2).
Точно так же поступаем с остальными четырьмя типами линий тренда.
Результаты:
Нужно выбрать наилучшую линию из всех. Будем выбирать по коэффициенту детерминации. Чем ближе этот коэффициент к единице, тем лучше уравнение описывает зависимость.
Получается, что лучше всего – степенная линия тренда, ее коэффициент детерминации равен 0,9118. Хуже всего – экспоненциальная, коэффициент – 0,7562.