Возможности MS EXCEL для решения задач корреляционно-регрессионного анализа

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

Реализация задач корреляционно-регрессионного анализа

с помощью табличного процессора Excel»

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

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

Одной из широко распространенных задач в экономических исследованиях является задача обработки данных, а именно представление их совокупности некоторой функцией y(x). Задача регрессии заключается в получении параметров этой функции такими, чтобы функция приближала бы «облако» исходных точек с наименьшей среднеквадратичной погрешностью. Чаще всего используется линейная регрессия, при которой функция y(x) имеет следующий вид:

y(x) = b + m∙ x,

где m – угловой коэффициент линии регрессии;

b – свободный член линейной регрессии.

Данная функция описывает отрезок прямой. К линейной регрессии можно свести многие виды нелинейной регрессии при двухпараметрических зависимостях y(x). Теоретическая линия регрессии должна отображать изменение средних величин результативного признака y по мере изменения факторного признака х при условии полного взаимопогашения всех прочих – случайных по отношению к фактору х – причин.

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

Возможности MS EXCEL для решения задач корреляционно-регрессионного анализа

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

Функции:

ЛИНЕЙН (известные_значения_y; известные_значения_x; конст; статистика) - рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива.

Параметры функции:

Известные_значения_y — множество значений y, которые уже известны для соотношения y = mx + b.

· Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

· Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.

Известные_значения_x — необязательное множество значений x, которые уже известны для соотношения y = mx + b.

· Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут иметь любую форму, при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец).

· Если известные_значения_x опущены, то предполагается, что это массив {1; 2; 3;...} такого же размера, как и известные_значения_y.

Конст — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

· Если конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом.

· Если аргумент конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение y = mx.

Статистика — логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.

· Если аргумент статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику, так что возвращаемый массив будет иметь вид: {mn; mn-1;...; m1; b: sen; sen-1;...; se1; seb: r2; sey: F; df: ssreg; ssresid}.

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

Уравнение для прямой линии имеет следующий вид:

y = mx + b или

y = m1x1 + m2x2 +... + b (в случае нескольких диапазонов значений x),

где зависимое значение y — функция независимого значения x, значения m — коэффициенты, соответствующие каждой независимой переменной x, а b — постоянная.

Заметим, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив {mn; mn-1;...; m1; b}. ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

НАКЛОН (известные_значения_y; известные_значения_x) – возвращает наклон линейной регрессии (коэффициент m). Наклон определяется как частное от деления расстояния по вертикали на расстояние по горизонтали между двумя любыми точками прямой, то есть наклон — это скорость изменения значений вдоль прямой.

Известные_значения_y — массив или интервал ячеек, содержащих числовые зависимые точки данных.

Известные_значения_x — множество независимых точек данных.

· Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.

· Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, эти значения игнорируются; ячейки, содержащие нулевые значения, учитываются.

· Если известные_значения_y и известные_значения_x пусты или содержат различное число точек данных, то функция НАКЛОН возвращает значение ошибки #Н/Д.

ОТРЕЗОК (известные_значения_х; известные_значения_у) - вычисляет точку пересечения линии с осью y, используя известные_значения_x и известные_значения_y. Точка пересечения находится на оптимальной линии регрессии, проведенной через известные_значения_x и известные_значения_y. Функция ОТРЕЗОК используется, когда нужно определить значение зависимой переменной при значении независимой переменной, равном 0 (нулю). Например, функцию ОТРЕЗОК можно использовать, чтобы предсказать электрическое сопротивление металла при температуре 0°C, если имеются данные измерений при комнатной температуре и выше.

Известные_значения_y — это зависимое множество наблюдений или данных.

Известные_значения_x — это независимое множество наблюдений или данных.

· Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.

· Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, эти значения игнорируются; ячейки, содержащие нулевые значения, учитываются.

· Если известные_значения_y и известные_значения_x содержат различное количество точек данных или вовсе не содержат точек данных, то функция ОТРЕЗОК возвращает значение ошибки #Н/Д.

ТЕНДЕНЦИЯ (известные_значения_y; известные_значения_x; новые_значения_x; конст) - возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы известные_значения_y и известные_значения_x. Возвращает значения y, в соответствии с этой прямой для заданного массива новые_значения_x.

Известные_значения_y — множество значений y, которые уже известны для соотношения y = mx + b.

· Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

· Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.

Известные_значения_x — необязательное множество значений x, которые уже известны для соотношения y = mx + b.

· Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут иметь любую форму, при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец).

· Если известные_значения_x опущены, то предполагается, что это массив {1; 2; 3;...} такого же размера, как и известные_значения_y.

Новые_значения_x — новые значения x, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения y.

· Новые_значения_x должны содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_x. Таким образом, если известные_значения_y — это один столбец, то известные_значения_x и новые_значения_x должны иметь такое же количество столбцов. Если известные_значения_y — это одна строка, то известные_значения_x и новые_значения_x должны иметь такое же количество строк.

· Если новые_значения_x опущены, то предполагается, что они совпадают с известные_значения_x.

· Если опущены оба массива известные_значения_x и новые_значения_x, то предполагается, что это массив {1; 2; 3;...} такого же размера, что и известные_значения_y.

Конст — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

· Если конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом.

· Если конст имеет значение ЛОЖЬ, то b полагается равным 0, и значения m подбираются таким образом, чтобы выполнялось соотношение y = mx.

Любую прямую можно описать ее наклоном и пересечением с осью y:

Наклон (m): Для того, чтобы определить наклон прямой, обычно обозначаемый через m, нужно взять две точки прямой (x1, y1) и (x2, y2); тогда наклон равен (y2 - y1)/(x2 - x1).

Y-пересечение (b): Y-пересечением прямой, обычно обозначаемым через b, является значение y для точки, в которой прямая пересекает ось y.

Уравнение прямой имеет вид y = mx + b. Если известны значения m и b, то можно вычислить любyю точку на прямой, подставляя значения y или x в уравнение. Можно также использовать функцию ТЕНДЕНЦИЯ.

· Если имеется только одна независимая переменная x, можно получить наклон и y-пересечение непосредственно, используя следующие формулы:

Наклон: ИНДЕКС(ЛИНЕЙН(известные_значения_y; известные_значения_x); 1)

Y-пересечение: ИНДЕКС(ЛИНЕЙН(известные_значения_y; известные_значения_x); 2)

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

ПРЕДСКАЗ (x; известные_значения_y; известные_значения_x) - вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое значение — это y-значение, соответствующее заданному x-значению. Известные значения — это x- и y-значения, а новое значение предсказывается с использованием линейной регрессии. Эту функцию можно использовать для предсказания будущих продаж, потребностей в оборудовании или тенденций потребления.

Параметры этой функции:

x — это точка данных, для которой предсказывается значение.

Известные_значения_y — это зависимый массив или интервал данных.

Известные_значения_x — это независимый массив или интервал данных.

Примечание:

· Если x не является числом, то функция ПРЕДСКАЗ возвращает значение ошибки #ЗНАЧ!.

· Если известные_значения_y и известные_значения_x пусты или содержат различное количество точек данных, то функция ПРЕДСКАЗ возвращает значение ошибки #Н/Д.

· Если дисперсия аргумента известные_значения_x равна нулю, то функция ПРЕДСКАЗ возвращает значение ошибки #ДЕЛ/0!

Функция ТЕНДЕНЦИЯ вычисляет прогнозы, основанные на линейной связи между результатом наблюдения и временем, когда это наблюдение было зафиксировано. Если эта взаимосвязь носит линейный характер, то линия на графике будет либо прямой, либо слегка наклоненной в одну или другую сторону, либо горизонтальной. Это и будет лучшей подсказкой о том, что взаимосвязь является линейной, и потому в данном случае функция ТЕНДЕНЦИЯ — самый удобный способ регрессивного анализа.

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

РОСТ (известные_значения_y; известные_значения_x; новые_значения_x; конст) - рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих x- и y-значений. Функция рабочего листа РОСТ может применяться также для аппроксимации существующих x- и y-значений экспоненциальной кривой.

Параметры этой функции:

Известные_значения_y — это множество значений y, которые уже известны в соотношении y = b*m^x.

· Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

· Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.

· Если какие-либо числа в массиве известные_значения_y равны 0 или отрицательны, то функция РОСТ возвращает значение ошибки #ЧИСЛО!.

Известные_значения_x — это необязательное множество значений x, которые уже известны для соотношения y = b*m^x.

· Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут иметь любую форму, при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец).

· Если известные_значения_x опущены, то предполагается, что это массив {1; 2; 3;...} такого же размера, как и известные_значения_y.

Новые_значения_x — это новые значения x, для которых РОСТ возвращает соответствующие значения y.

· Новые_значения_x должны содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_x. Таким образом, если известные_значения_y — это один столбец, то известные_значения_x и новые_значения_x должны иметь такое же количество столбцов. Если известные_значения_y — это одна строка, то известные_значения_x и новые_значения_x должны иметь такое же количество строк.

· Если аргумент новые_значения_x опущен, то предполагается, что он совпадает с аргументом известные_значения_x.

· Если оба аргумента известные_значения_x и новые_значения_x опущены, то предполагается, что это массив {1; 2; 3;...} такого же размера, как и известные_значения_y.

Конст — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 1.

· Если конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом.

· Если конст имеет значение ЛОЖЬ, то b полагается равным 1, а значения m подбираются так, чтобы y = m^x.

Примечание:

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

· При вводе константы массива для аргумента, такого как известные_значения_x, следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк.