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

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

Рисунок 3.29 - Исходные данные для построения линейной модели

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

, (3.4)

где n — число наблюдений (в данном случае n = 6).

Для решения этой задачи отведем под переменные m и b ячейки D3 и ЕЗ, соответственно, а в ячейку F3 введем минимизируемую функцию {=СУММКВРАЗН(В2: В7; E3+D3*A2: А7)}.

Функция суммквразн вычисляет сумму квадратов разностей для элементов указанных массивов.

Теперь выберем команду Сервис, Поиск решения и заполним открывшееся диалоговое окно Поиск решения, как показано на рисунке 3.30.

Рисунок 3.30 - Диалоговое окноПоиск решения для расчета уравнения регрессии

Отметим, что на переменные т и b ограничения не налагаются. В результате вычислений средство поиска решений найдет: т = 1, 88571 и b = 5, 400. Данные результаты приведены на рисунке 3.31.

Рисунок 3.31 – Оптимальное решение уравнения регрессии

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

Параметры т и b линейной модели из предыдущего раздела можно определить с помощью функций наклон и отрезок.

Наклон — это скорость изменения значений вдоль прямой. Функция наклон определяет коэффициент наклона линейного тренда. Синтаксис:

НАКЛОН (известные_значения_у; известные_значения х).

Функция отрезок (intercept) определяет точку пересечения линии линейного тренда с осью ординат.

Синтаксис:

ОТРЕЗОК (известные_значеыия_х; известные_значения_у).

Аргументы функций наклон и отрезок:

- известные_значения_у - массив известных значений зависимой наблюдаемой величины;

- известные_значения_х – массив известных значений независимой наблюдаемой величины. Если аргумент известные_значения_х опущен, то предполагается, что это массив {1; 2; 3; …} такого же размера, как и аргумент известные_значения__у

Функции наклон и отрезок вычисляются по следующим формулам:

, (3.5)

, (3.6)

где ,

В ячейках D2 и Е2, найдены т и b, соответственно, по формулам:

=НАКЛОН(В2: В7; А2: А7);

=ОТРЕЗОК(В2: В7; А2: А7).

Коэффициенты т и b можно найти и другим способом. Постройте точечный график по диапазону ячеек А2: В7, выделите точки графика двойным щелчком, а затем щелкните их правой кнопкой мыши. В раскрывшемся контекстном меню выберите командуЛинии тренда, как показано на рисунке 3.32.

Рисунок 3.32 - Начало построения линии тренда

В диалоговом окне Линия тренда на вкладке Тип в группе Построение линии тренда (аппроксимация и сглаживание) выберите параметр Линейная, а на вкладке Параметры установите флажки Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R^2) (то есть на диаграмму необходимо поместить значение квадрата коэффициента корреляции).

По коэффициенту корреляции можно судить о правомерности использования линейного уравнения регрессии. Если он лежит в диапазоне от 0, 9 до 1, то данную зависимость можно использовать для предсказания результата. Чем ближе к единице коэффициент корреляции, тем более обоснованно это указывает на линейную зависимость между наблюдаемыми величинами. Если коэффициент корреляции близок к -1, то это говорит об обратной зависимости между наблюдаемыми величинами.

Флажок Пересечение кривой с осью Y в точке, устанавливается только в случае, если эта точка известна. Например, если этот флажок установлен и в его поле введен 0, это означает, что ищется модель .

Результат выполнения команды Линии тренда приведен на рисунке 3.33.

Рисунок 3.33 - График линии тренда

Как видно из рисунка, квадрат коэффициента корреляции равен 0.9723, следовательно, линейная модель может быть использована для предсказания результатов.

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

=$D$2*A2+$E$2

Однако теоретическое значение у в фиксированной точке можно вычислить и без предварительного определения коэффициентов линейной модели с помощью функции ПРЕДСКАЗ.

Синтаксис:

ПРЕДСКАЗ(t; известные_значения_у; известные_значения_х).

Аргументы:

- t - точка данных, для которой предсказывается значение;

- известные_значения_у - массив известных значений зависимой наблюдаемой величины;

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

Например, теоретическое значение в ячейке С2 на рисунке 14 можно также определить по формуле

=ПРЕДСКАЗ(А2; $В$2: $В$7; $А$2: $А$7).

Функция ТЕНДЕНЦИЯ вычисляет значения уравнения линейной регрессии для целого диапазона значений независимой переменной как для одномерного, так и для многомерного уравнения регрессии. Многомерная линейная модель регрессии имеет вид:

Синтаксис:

ТЕНДЕНЦИЯ (известные_значения_у; известные_значения_ х; новые_значения_х; конст).

Аргументы:

- известные_значения_у - массив известных значений зависимой наблюдаемой величины;

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

- новые_значения_х - новые значения х, для которых функция тенденция возвращает соответствующие значения у;

- конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент конст имеет значение истина или опущен, то b вычисляется обычным образом. Если конст имеет значение ЛОЖЬ, то b полагается равным 0.

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

Функция ЛИНЕЙН возвращает массив значений параметров уравнения многомерной линейной регрессии.

Синтаксис:

ЛИНЕЙН (известные_значения_ у; известные_значения_ х; конст; статистика).

Аргументы:

- известные_значения_у - массив известных значений зависимой наблюдаемой величины;

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

- конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент конст имеет значение ИСТИНА или опущен, то b вычисляется обычным образом. Если конст имеет значение ЛОЖЬ, то b полагается равным 0;

- статистика - логическое значение, которое указывает, требуется ли вывести дополнительную статистику по регрессии, например, коэффициент корреляции. Если статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Если аргумент статистика имеет значение ЛОЖЬ или опущен, то функция ЛИНЕЙН возвращает только значения коэффициентов.

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

1. Какие существуют методы регрессионного анализа?

2. Какие функции участвуют в регрессионном анализе?

3. Назначение линии тренда?

4. Какие линии тренда можно использовать?