Цель работы - Ознакомиться с анализом и прогнозированием данных на примере трендового

Лабораторная работа

Тема:

Трендовый анализ

Цель работы - Ознакомиться с анализом и прогнозированием данных на примере трендового

анализа спроса

Результат выполнения работы

Порядок выполнения работы

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

2. Ознакомиться с постановкой задачи и заданием.

3. Самостоятельно выполнить практическое задание согласно требованиям отчёта.

4. Защитить работу преподавателю.

Общие сведения о трендовом анализе

Прогнозирование и регрессионный анализ

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

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

Линии тренда на диаграмме

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

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

Скользящее среднее

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

Типы диаграмм, поддерживающие линии тренда

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

Создание линии тренда без диаграммы

Создание линии тренда без диаграммы возможно при использовании автозаполнения или одной из статистических функций, например РОСТ или ТЕНДЕНЦИЯ

Выбор наиболее подходящей линии тренда для данных

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

Надежность линии тренда R2

Наиболее надежна линия тренда, для которой значение R-квадрат равно или близко к 1.
R в квадрате, или показатель определенности — это число от 0 до 1, которое отражает близость значений линии тренда к фактическим данным. Чем больше величина этого показателя, тем достовернее линия тренда.
При подборе линии тренда к данным Excel автоматически рассчитывает значение R2. Можно отобразить это значение на диаграмме.

Формулы:

Примечание. Отображаемое вместе с линией тренда значение величины R-квадрат не является корректным. Для логарифмической, степенной и экспоненциальной линий тренда в Microsoft Excel используется несколько видоизмененная модель регрессии.

Виды (типы) линий тренда

Арифметическая (линейная)

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

Формула:

где m - угол наклона и b - координата пересечения оси абсцисс.

В приведенном ниже примере линейное приближение показывает равномерное увеличение объема продаж холодильников в течение 13 лет. Следует заметить, что значение R-квадрат в данном случае составляет 0,9036. Это свидетельствует о достаточно хорошем согласовании линии аппроксимации с фактическими данными.

Логарифмическая

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

Формула:

где c и b - константы, ln - функция натурального логарифма.

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

Полиномиальная

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

Формула:

где b и — константы.

Ниже на примере аппроксимации полиномом второго порядка (одна вершина) показана зависимость скорости от потребления топлива. Следует заметить, что значение R-квадрат в данном случае составляет 0,9474. Это достаточно хорошо согласуется с фактическими данными.

Степенная

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

Формула:

где c и b - константы.

Ниже показан пример зависимости пройденного расстояния от времени (в секундах). По степенной линии тренда ясно видно увеличение ускорения. Обратите внимание, что значение R-квадрат в данном примере равно 0,9923. Это говорит о высокой точности используемого приближения.

Экспоненциальная

Экспоненциальное приближение следует использовать в том случае, если скорость изменения данных непрерывно возрастает. Однако для данных, которые содержат нулевые или отрицательные значения, этот вид приближения неприменим.

Формула:

где c и b - константы, e - основание натурального логарифма.

На приведенном ниже на примере экспоненциальное приближение иллюстрирует процесс распада углерода 14. Следует заметить, что значение R-квадрат здесь равно 1, то есть линия приближения идеально соответствует данным.

Скользящее среднее (линейная фильтрация)

Использование в качестве приближения скользящего среднего позволяет сгладить колебания данных и таким образом более наглядно показать характер зависимости. Такая линия тренда строится по определенному числу точек (оно задается параметром Шаг). Элементы данных усредняются, и полученный результат используется в качестве среднего значения для приближения. Так, если Шаг равен 2, первая точка сглаживающей кривой определяется как среднее значение первых двух элементов данных, вторая точка — как среднее следующих двух элементов и так далее. Для скользящего среднего значение R-квадрат не может быть отображено.

Формула:

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

В следующем примере показана зависимость числа продаж на протяжении 26 недель, полученная путем расчета скользящего среднего.

Добавление и редактирование линии тренда

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

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

На вкладке Тип выберите нужный тип регрессионной линии тренда. или линии скользящего среднего (линейная фильтрация).

При выборе типа Полиномиальная введите в поле Степень наибольшую степень для независимой переменной.

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

Примечания

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

Если вариант «Скользящее среднее (линейная фильтрация)» выбран для точечной диаграммы, результат будет зависеть от порядка расположения значений X во входном диапазоне. Чтобы получить правильный результат, необходимо отсортировать значения X перед построением линии скользящего среднего.

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

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

Для вывода формулы и значения R-квадрат откройте вкладку Параметры и установите необходимые параметры: Показать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R^2).

Линию тренда можно форматировать. Выделите линию тренда, щелкнув но ней мышью, затем щелкните правой кнопкой мыши и выберите пункт Форматирование линии тренда, установите желаемый Вид тренда, например, выберите красный пунктирный вид линии.

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

Практическое задание.

Открыть свою папку. Открыть свой файл Фамилия.xls.

Создать или выбрать Лист 1.

Набрать заголовки согласно Рис. примера. Выбрать самостоятельно фирму, товар и т.п.

Создать таблицу продаж, спроса и т.п. по месяцам, годам, кварталам и т.п.

Ввести данные. Допускается использовать данные из примера, выбирать самостоятельно или использовать данные, предоставленные преподавателем.

Создать не менее трёх линий тренда различного типа с прогнозами.

Отформатировать диаграмму и линии тренда согласно примера.

Требования к отчёту

Отчёт - отдельный файл формата электронных таблиц с именем Фамилия.xls, содержащий на листе 1 заголовки, график с тремя линиями тренда, формулами и критерием R-квадрат для них. Пример результата:

Ещё один пример данных и построения простой линии тренда (линейная апроксимация):

Затраты на рекламу	Продано продукции

Файл электронных таблиц создаётся на КАЖДОГО из студентов, работавших за одним компьютером.

Дата добавления: 2015-08-27