Управляемая самостоятельная работа №10

Управляемая самостоятельная работа №10

Тема: Множественный регрессионный анализ

Цель: изучить линейный регрессионный анализ, множественный регрессионный анализ; освоить инструмент анализа Регрессия и функцию ЛИНЕЙН.

Задача: Оцените влияние изменений расходов на рекламу и цены на объемы продаж в единицах продукции с использованием диаграмм (линий тренда) Excel, функций ЛИНЕЙН и ТЕНДЕНЦИЯ и инструмента анализа Регрессия.

Теоретическая часть

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

Наиболее полную статистическую информацию по линейной регрессии выдает функция ЛИНЕЙН. Кроме того, эта функция позволяет обрабатывать данные по линейным функциям нескольких переменных (многофакторный эксперимент). Поскольку функция возвращает не одно значение, а несколько, массив значений, она относится к формулам массива. Ввод этих формул имеет ряд особенностей:

· Начинать ввод следует с выделения массива ячеек, куда будут выводиться данные.

· Заканчивать ввод функции следует сочетанием клавиш Ctrl-Shift-Enter (или, удерживая нажатыми клавиши Ctrl-Shift щелкнуть мышкой Ok).

Встроенная функция ЛИНЕЙН решает простейшую задачу парной линейной регрессии:

– по заданным значениям , i =1, 2, …, n строит методом наименьших квадратов линейную функцию регрессии ;

– вычисляет некоторые статистики для анализа качества аппроксимации.

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

Ход работы:

1.Ввела данные столбцов «Смета на рекламу», «Цена продажи» и «Объемы продаж»(в единицах продукции для 18 видов продукции, проданной компанией)

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

3.Получила отдельные элементы регрессионного анализа для каждой из зависимостей. Использовала следующие функции:

· Для значения квадрата коэффициента корреляции – КОРРЕЛ;

· Для отрезка, отсекаемого на оси ординат, - ОТРЕЗОК;

· Для коэффициента наклона линейной регрессии – НАКЛОН.

Скопировала данные на следующий лист. Оценила совместное влияние изменений сметы на рекламу и продажных цен на объемы продаж с помощью функции ЛИНЕЙН. Ввела с помощью формулы массива в ячейки диапазона В26:D30 формулу: =ЛИНЕЙН(С3:С20;А3:В20;ИСТИНА;ИСТИНА). В качестве аргументов здесь указала: диапозон со значениями зависимой переменной, блок со значениями независимых переменных, необходимость, чтобы полученная зависимость проходила через начало координат и необходимость возвращения статистики.

В столбце D получила прогноз объема продаж с использованием полученных коэффициентов регрессии по формуле: =$D$26+($B$26*B2)+($C$26*A2).

Для прогнозирования с использованием множественной регрессии с помощью функции ТЕНДЕНЦИЯ ввела формулу массива =ТЕНДЕНЦИЯ(C3:С20;А3:В20; ИСТИНА) в ячейке Е3:Е20. Сравнила результат с тем, который получен с помощью уравнения множественной регрессии.

Ввела новые значения независимых переменных в диапазон А21:А23; B21:В23. В столбце D получила прогноз объема продаж с использованием полученных коэффициентов регрессии по формуле: =$D$26+($B$26*B21)+($C$26*A21).

Использовала инструмент Регрессия для проверки множественного регрессионного анализа. Параметры окна Регрессия: Входной интервал Y – С3:С20, Входной интервал Х – А3:В20. Указала вариант вывода результатов на новом листе.

Вывод:В ходе лабораторной работе изучила линейный регрессионный анализ, множественный регрессионный анализ; освоила инструмент анализа Регрессия и функцию ЛИНЕЙН и ТЕНДЕНЦИЯ и инструмента анализа Регрессия.