Метод наименьших квадратовЦель работы: изучение использования возможностей инструмента Поиск решений для определения параметров уравнения регрессии методом наименьших квадратов, закрепление навыков аппроксимации с помощью линейных и нелинейных функций и использования экстраполяции найденного тренда для прогнозирования.
Рассмотрим задачу построения регрессионной модели. С помощью средства поиска решений решим задачу нахождения уравнения регрессии для одной зависимой и одной независимой переменных. Данный подход позволяет исследовать любое уравнение регрессии. Используем функции рабочего листа, непосредственно вычисляющие различные характеристики линейного уравнения регрессии и экспоненциального уравнения регрессии, которые позволяют значительно упростить процедуру регрессионного анализа для наиболее часто встречающихся на практике моделей. 1. Общий подход к построению уравнения регрессии на примере линейной модели Рассмотрим, как решается задача нелинейной оптимизации с помощью средства поиска решений на примере построения линейного уравнения регрессии. Имеются две наблюдаемые величины х и у, например, объем реализации фирмы, торгующей подержанными автомобилями, за шесть недель ее работы. Значения этих наблюдаемых величин приведены на рис. 15, где x - отчетная неделя, а у - объем реализации за эту неделю.
Рис. 15. Исходные данные для построения линейной модели
Необходимо построить линейную модель у = mх + b, наилучшим образом описывающую наблюдаемые значения. Обычно m и b подбираются так, что бы минимизировать сумму квадратов разностей между наблюдаемыми и теоретическими значениями зависимой переменной у, т.е. минимизировать
где n - число наблюдений (в данном случае n = 6). Для решения этой задачи отведем под переменные b и m ячейки D3 и Е3, соответственно, а в ячейку F3 введем минимизируемую функцию { = СУММКВРАЗН (В2: В7; ЕЗ + D3 * А2: А7) }
Функция СУММКВРАЗН (SUMSQ) вычисляет сумму квадратов разностей для элементов указанных массивов. Теперь выберем команду Сервис, Поиск решения (Тоо1s, Sо1vеr) и заполним открывшееся диалоговое окно Поиск решения (Sо1vеr) как показано на рис. 16. Отметим, что на переменные b и m ограничения не налагаются. В результате вычислений средство Поиска решений найдет: b = 5, 400003 и m = 1, 886.
Рис. 16. Диалоговое окно Поиск решения для расчета уравнения регрессии
2. Функции рабочего листа для уравнения линейной регрессии Параметры m и b линейной модели у = mх + b из предыдущего раздела можно определить с помощью функций НАКЛОН (SLOPE) и ОТРЕЗОК (INTЕRСЕРТ).
Рис. 17. Диалоговое окно функции НАКЛОН
Функция НАКЛОН (SLOPE) определяет коэффициент наклона линейного тренда. Синтаксис НАКЛОН (известные_значения_ у; известные_значения_х)
Функция ОТРЕЗОК (INTЕRСЕРТ) определяет точку пересечения линии линейного тренда с осью ординат. Синтаксис: ОТРЕЗОК (известные_значения_х; известные_значения_ у)
Рис. 18. Диалоговое окно функции ОТРЕЗОК
Аргументы функций НАКЛОН (SLOPE) и ОТРЕЗОК (INTЕRСЕРТ): известные_значения_ у - Массив известных значений зависимой наблюдаемой величины известные_значения_ x - Массив известных значений независимой наблюдаемой величины. Если аргумент на известные_значения_ х опущен, то предполагается, что это массив {1 2; 3;...} такого же размера, как и аргумент известные_значения_ у Функции НАКЛОН и ОТРЕЗОК вычисляются по следующим формулам:
где В ячейках D2 и Е2 (рис. 1) найдены m и b, соответственно, по формулам: = НАКЛОН (В2: В7; А2: А7) = ОТРЕЗОК (В2: В7; А2: А7) Коэффициенты m и b можно найти и другим способом. Постройте точечный график по диапазону ячеек А2: В7, выделите точки графика двойным щелчком, а затем щелкните их правой кнопкой мыши. В раскрывшемся контекстном меню выберите команду Линии тренда (Тrеndline) (рис. 19).
Рис. 19. Начало построения линии тренда В диалоговом окне Линия тренда (Тrеndlinе) на вкладке Тип (Туре) в группе Построение линии тренда (аппроксимация и сглаживание) (Тrеnd/Rеgression tуре) выберите параметр Линейная (Linеаr) (рис. 20), а на вкладке параметры (Орtiоns) установите флажки Показывать уравнение на диаграмме (Disрlау Еquation оn Сhart) и Поместить на диаграмму величину достоверности аппроксимации (R2) (Disрlау R-squared) (т. е. на диаграмму необходимо поместить значение квадрата коэффициента корреляции) (рис. 21).
Рис. 20. Вкладка Тип диалогового окна Линия тренда
По коэффициенту корреляции можно судить о правомерности использования линейного уравнения регрессии. Если он лежит в диапазоне от 0 до 1, то данную зависимость можно использовать для предсказания результата. Чем ближе к единице коэффициент корреляции, тем более обоснованно это указывает на линейную зависимость между наблюдаемыми величинами. Если коэффициент корреляции близок к -1, то это говорит об обратной зависимости между наблюдаемыми величинами. Флажок Пересечение кривой с осью Y в точке (Set Intercept) (рис. 21) устанавливается только в случае, если эта точка известна. Например, если этот флажок установлен и в его поле введен 0, это означает, что ищется модель у = mх.
Рис. 21. Вкладка Параметры диалогового окна Линия тренда Результат выполнения команды Линии тренда (Тrеndlinе) приведен на рис. 22. Как видно из рисунка, квадрат коэффициента корреляции равен 0, 9023, следовательно, линейная модель может быть использована для предсказания результатов. На основе найденных коэффициентов уравнения регрессии можно определить теоретическое значение наблюдаемой величины у. Вычислим теоретическое значение у в ячейке С2 (рис. 15) при х из А2 по формуле = $D$2 * A2 + $E$2.
Рис. 22. График линии тренда Однако теоретическое значение у в фиксированной точке можно вычислить и без предварительного определения коэффициентов линейной модели с помощью функции
ПРЕДСКАЗ (FORECAST). Синтаксис: = ПРЕДСКАЗ (t; известные_значения_ у; известные_значения_х)
Аргументы: t - Точка данных, для которой предсказывается значение известные_значения_ у - Массив известных значений зависимой наблюдаемой величины известные_значения_ х - Массив известных значений независимой наблюдаемой величины. Если аргумент известные_значения_ х опущен, то предполагается, что это массив (1 2; 3;...} такого же размера, как и массив известные_значения_ у. Например, теоретическое значение в ячейке С2 (рис. 5.24) можно также определить по формуле
= ПРЕДСКАЗ (А2; $В$2: $В$7; $А$2: $А$7)
Функция ТЕНДЕНЦИЯ (TREND) вычисляет значения уравнения линейной регрессии для целого диапазона значений независимой переменной как для одномерного, так и для многомерного уравнения регрессии. Многомерная линейная модель регрессии имеет вид: у = m1х1 +... + mnхn + b. Свитаксис: = ТЕНДЕНЦИЯ (известные_значения_ у; известные_значения_х; новые_значения_х; конст)
Аргументы: известные_значения_ у - Массив известных значений зависимой наблюдаемой величины известные_значения_ х - Массив известных значений независимой наблюдаемой величины. Если аргумент известные_значения_ х опущен, то предполагается, что это массив {1; 2; 3;..} такого же размера, как и массив известные_значения_ у новые_значения_ х - Новые значения х, для которых функция ТЕНДЕНЦИЯ возвращает соответствующие значения у конст - Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент конст имеет значение ИСТИНА или опущен, то b вычисляется обычным образом. Если конст имеет значение ложь, то b полагается равным 0. Если строится многомерная линейная модель, то аргументы известные_значения _х и новые_значения_ х должны содержать столбец (или строку) для каждой независимой переменной. Если аргумент новые_значения_ х опущен, b то предполагается, что он совпадает с аргументом известные_значения_ х. Функция ЛИНЕЙН (LINЕSТ) возвращает массив {mn,..., m1, b} значений параметров уравнения многомерной линейной регрессии.
Синтаксис: = ЛИНЕЙН (известные_значения_у; известные_значения_х; конст; статистика) Аргументы: известные_значения_ у - Массив известных значений зависимой наблюдаемой величины известные_значения_ х - Массив известных значений независимой наблюдаемой величины. Если аргумент известные_значения_х опущен, то предполагается, что это массив {1; 2: 3;...} такого же размера, как и известные_значения_ у конст - Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент конст имеет значение ИСТИНА или опущен, то b вычисляется обычным образом. Если конст имеет значение ложь, то b полагается равным 0 статистика - Логическое значение, которое указывает, требуется ли вывести дополнительную статистику по регрессии, на пример, коэффициент корреляции. Если статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Если аргумент статистика имеет значение ЛОЖЬ или опущен, то функция ЛИНЕЙН возвращает только значения коэффициентов
3. Экспоненциальная модель Другой часто встречающейся на практике регрессионной моделью является экспоненциальная модель, которая описывается уравнением у = bmx Значения экспоненциального тренда можно предсказывать с помощью функции РОСТ (GROWTH). Синтаксис: = РОСТ (известные_значения_ у; известные_значения_х; новые_значения_х; конст)
Аргументы: известные_значения_ у -Массив известных значений зависимой наблюдаемой величины известные_значения_ х - Массив известных значений независимой наблюдаемой величины. Если аргумент известные_значения_ х опущен, то предполагается, что это массив {1; 2; 3...} такого же размера, как и известные_значения_ у новые_значения_ х, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения у конст - Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент конст имеет значение ИСТИНА или опущен, то b вычисляется обычным образом. Если конст имеет значение ЛОЖЬ то b полагается равным 0 Значения параметров зкспоненциальной модели определяются с помощью функции ЛГРФПРИБЛ (LOGRST)
Синтаксис: = ЛГРФПРИБЛ (известные_значения_ у; известные_значения_х; конст; статистика)
Аргументы: известные_значения_ у - Массив известных значений зависимой наблюдаемой величины известные_значения_ х - Массив известных значений независимой наблюдаемой величины. Если аргумент известные_значения_ х опущен, то предполагается, что это массив {1; 2; 3;...} такого же размера, как и известные_значения_ у конст - Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент конст имеет значение ИСТИНА или опущен, то b вычисляется обычным образом. Если конст имеет значение ЛОЖЬ то b полагается равным 0 статистика - Логическое значение, которое указывает, требуется ли вывести дополнительную статистику по регрессии, на пример, коэффициент корреляции. Если статистика имеет значение ИСТИНА, то функция ЛГРФПРИБЛ возвращает дополнительную регрессионную статистику. Если статистика имеет значение ЛОЖЬ или опущена, то функция ЛГРФПРИБЛ возвращает только значения коэффициентов Кроме того, одномерную экспоненциальную модель можно построить графически (рис. 20). На рис. 23 приведены результаты построения экспоненциального уравнения тренда продажи подержанных автомобилей за 7, 8 и 9-ю недели торговли.
Рис. 23. Экспоненциальная линия тренда
В диапазоне ячеек В8: В10 введена формула построения линейного тренда {= ТЕНДЕЦИЯ (B2: B7; A2: A7; A8: A10)}
В диапазоне ячеек D2: D10 введена формула построения экспоненциального тренда
{= РОСТ (B2: B7; A2: A7; A2: A10)}
Линейный и экспоненциальный тренды тесно связаны между собой. В диапазоне ячеек D2: D10 можно было бы получить такой же результат, введя формулу
{= ЕXР (ТЕНДЕНЦИЯ (LN (B2: B7); A2: A7; A2: A10))}
В диапазоны ячеек G7: H7 и G8: H8 введены формулы {= ЛИНЕЙН (В2: В2; А2: А7)} {= ЛГРФПРИБЛ (В2: В2; А2: А7)} для определения параметров линейной и экспоненциальной моделей. Квадрат коэффициента корреляции экспоненциальной модели равен 0, 947 (рис. 23) и меньше квадрата коэффициента корреляции линейной модели (0, 9923) (рис. 22). Таким образом, в данном примере линейная модель более достоверно описывает зависимость между наблюдаемыми величинами.
Порядок выполнения работы 1.Получить у преподавателя данные для расчета. 2.Ввести исходные данные в таблицу Excel. 3.Провести на ЭВМ серию экспериментов с различными типами моделей регрессии с определением прогнозных значений зависимой переменной, параметров регрессионных моделей и коэффициентов детерминации. 4.Зафиксировать результаты расчетов в тетради. 5.Сделать выводы по результатам моделирования и записать в тетради. Отчет по работе должен содержать 1.Название и цель работы. 2.Основные теоретические и методические положения. 3.Исходные данные для расчета. 4.Результаты расчета. 5.Выводы по результатам моделирования.
|
