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

1. Для чего используется корреляционный анализ?

2. Дайте определение корреляции.

3. Как классифицируются связи между признаками и явлениями?

4. От чего не зависит значение коэффициента корреляции?

5. Что называется коэффициентом корреляции (коэффициент корреляции Пирсона)?

Практическое задание № 6 использование функции линейн

Цель: закрепить и углубить знания о понятии «регрессия»; научиться применять функцию «ЛИНЕЙН» для регрессионного анализа.

Теоретические сведения

Другой способ получения уравнения регрессии состоит в использовании встроенной статистической функции ЛИНЕЙН.

Она рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция рассчитывает массив, который описывает полученную прямую. Уравнение для прямой линии имеет вид:

или

(в случае нескольких интервалов значений x), где зависимое значение является функцией независимого значения . Значения m_i — это коэффициенты, соответствующие каждой независимой переменной x_i, а —постоянная.

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

Таблица 3

Порядок размещения дополнительной регрессионной статистики функции «ЛИНЕЙН».

mn	mn-1	…	m1	b
sen	Sen-1	…	se1	Seb
r2	Sey
F	Df
ssreg	Ssresid

Дополнительная регрессионая статистика рассчитывает следующие величины:

se1, se2, ..., sen — стандартные значения ошибок для коэффициентов m1,m2,...,mn.

Seb — стандартное значение ошибки для постоянной b (seb = #Н/Д, если константа имеет значение ЛОЖЬ).

R² — коэффициент детерминации.

Sey — cтандартная ошибка для оценки y.

F — F-статистика. Используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет.

Df — степени свободы. Полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, расчитываемой функцией «ЛИНЕЙН».

Ssreg — регрессионая сумма квадратов.

Ssresid — остаточная сумма квадратов. Для каждой точки вычисляется квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей и называется остаточной суммой квадратов.

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

Упражнение 1

Рассчитать статистику для ряда с использованием встроенной статистической функции ЛИНЕЙН. В качестве исходных значений используйте данные рис. 16.

Последовательность действий:

1. Открыть рабочую книгу Анализ-ФинА-Иванов.х1sх.

2. Создайте новый лист и переименуйте его в Линейн. Заполните этот лист данными рис. 16.

3. Выделить на листе блок свободных ячеек, куда будет направлен результат вычислений функции. Число столбцов блока равно числу оцениваемых факторов плюс один для размещения значения константы. В нашем случае число столбцов будет равно 3, так как мы имеем два оцениваемых фактора (затраты на рекламу и цена за 1 изделие). Число строк должно быть равно 5 (см. рис. 16). В данном случае блок ячеек Е2:G7.

4. Щелкнуть на кнопке Вставить функцию.

5. В открывшемся диалоговом окне Мастер функций – шаг 1 из 2 в списке функций категории Статистические выбрать функцию ЛИНЕЙН и нажать на кнопку ОК.

6. В диалоговом окне Аргументы функции (рис. 21) указать исходные параметры для расчета:

Рис. 21

Известные значения y — это множество значений y, для которые уже известно соотношение y = mx + b. В нашем случае это значения Столбца объем продаж, т.е. диапазон С2:С19.

Известные значения x — это необязательное множество значений x, для которых, возможно, уже известно соотношение y = mx + b. Для расматриваемого примера это значения столбцов Затраты на рекламу и Цена за 1 шт. продукции, т.е. диапазон А2:В19.

Известные значения y и x могут быть массивами любой формы при условии, что они имеют одинаковую размерность. Если известные значения x опущены, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные значения y.

Конст — необязательный аргумент. Это логическое значение, которое указывает, как вычисляется константа b. Если для аргумента Конст задано значение ИСТИНА или он опущен (не указан), то константа b вычисляется обычным образом. А если аргумента Конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение y = mx. В нашем случае введите значение ИСТИНА.

Статистика — необязательный аргумент. Это логическое значение, которое указывает, требуется ли возвратить дополнительную регрессионную статистику. Если этот аргумент имеет значение ЛОЖЬ, то функция ЛИНЕЙН расчитывает только коэффициенты m и постоянную b. В противном случае функция ЛИНЕЙН расчитывает дополнительную регрессионную статистику.

Для рассматриваего примера задайте для аргумента Статистика значение ИСТИНА.

Для того, чтобы в блоке расчетных данных было видно не только значение первой ячейки, ввод завершите одновременным нажатием комбинации клавиш Ctrl+Shift+Enter.

Результаты расчета дополнительной статистики функции «ЛИНЕЙН» для данного примера представлены на рис. 22.

Рис. 22

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

y = bm^x

или y = bm₁^x1m₂^x2… m_n^xn (при наличии нескольких значений x),

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

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

Индивидуальные задания

Задача № 6. (Приложение Б).

Задача № 6. (Приложение В).