Практическое занятие 10

Практическое занятие №10.

Тема: Решение задач прогнозирования: функции, линии тренда

Цель работы: Изучение информационной технологии решения задач прогнозирования с применением функций и построением тренда в Excel

Время работы: 2 часа.

Оборудование: Компьютер с установленным программным обеспечением и подключенный к Internet

Ход работы

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

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

доходом (откладывается по горизонтальной оси) и потреблением отдельного вида товара (например, продовольствия)^3.(Чтобы проанализировать взаимосвязь между переменными построим линию тренда (регрессии) и выведем уравнение регрессии.

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

Оценка надежности линии тренда к фактическим данным выполняется по показателю определенности или величине R в квадрате. R может изменяться от 0 до 1. Чем больше величина этого показателя, тем достовернее линия тренда. Значение R² автоматически рассчитывается Excel при подборе линии тренда к данным. Это значение можно отобразить на диаграмме.

После построения диаграммы, щелкнем на ней правой клавишей мыши и в появившемся контекстном меню выберем команду Добавить линию тренда (или воспользуемся командой Анализ , расположенной на закладке Макет в группе Работа с диаграммами). Определим тип аппроксимации (например, Линейное приближение). Открыв закладку Параметры, поставим флажки напротив команд Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации R^2. Щелкнем Ok

Мы построили линию тренда линейной регрессии. Уравнение y=0,5495x+1,201 – уравнение регрессии для приведенных данных. Оно показывает зависимость потребления продовольствия от личного располагаемого дохода населения. Для данного уравнения регрессии 0,5495

– угловой коэффициент, 1,202 – отрезок, отсекаемый на оси ординат. R² – квадрат коэффициента корреляции. Ему будет соответствовать следующая формула: =КОРРЕЛ(B3:B27;C3:C27)^2 или статистическая функция Excel: КВПИРСОН(B3:B27;C3:C27).

Отрезок, отсекаемый по оси ординат можно получить при помощи функции =ОТРЕЗОК (B3:B27; C3:C27). Коэффициент наклона вычисляется функцией =НАКЛОН(B3:B27; C3:C27). Для определения угла наклона и отрезка одновременно существует функция =ЛИНЕЙН(B3:B27; C3:C27).

Задание 2. Прогнозирование значений в рядах.

Для выполнения прогноза изменения данных на следующий временной период Еxcel располагает целым рядом инструментов.

1. Автоматическое заполнение ряда данных на основе арифметической и геометрической прогрессии. (Вкладка Главная - группа Редактирование - команда Заполнить).

2. Вычисление трендов с помощью добавления линии тренда на диаграмму.

Чтобы показать тенденцию или направление изменения данных на диаграмме, к ряду данных добавляют линию тренда. Линиями трендов можно дополнить ряды данных, представленные в виде диаграмм с областями, линейчатых диаграмм, гистограмм и точечных диаграмм. Нельзя дополнить линиями тренда ряды данных на объемных диаграммах, нормированных диаграммах, лепестковых диаграммах, круговых и кольцевых диаграммах. Например, если имеется созданная в Excel диаграмма, на которой показаны данные о продажах за определенный временной период, можно добавить к ней линию тренда, которая проиллюстрирует общую тенденцию продаж (рост, снижение или стабилизация) или продемонстрирует предполагаемый тренд на ближайшее время. См. таблицу

1. Прогнозирование значений с помощью функции.

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

Контрольные вопросы:

1. Каким образом можно создать числовую последовательность в таблице Excel?

2. Какие вычислительные возможности Excel Вам известны?