Основы работы с табличным процессором Excel

     Подобранная прямая называется линией регрессии y на x, a и b называются коэффициентами регрессии.

     Чем меньше величина

     тем более обосновано предположение, что  табличная зависимость описывается  линейной функцией. Существует показатель, характеризующий тесноту линейной связи между x и y. Это коэффициент корреляции. Он рассчитывается по формуле:

     Коэффициент корреляции r и коэффициент регрессии a связаны соотношением:

     где Dy, Dx - среднеквадратичное отклонение значений x и y.

     Значение  коэффициента корреляции удовлетворяет  соотношению -1 ≤ r ≤ 1. Чем меньше отличается абсолютная величина r от единицы, тем ближе к линии регрессии располагаются экспериментальные точки. Если коэффициент корреляции равен нулю, то переменные x, y называются некоррелированными. Если r = 0, то это только означает, что между x, y не существует линейной связи, но между ними может существовать зависимость, отличная от линейной.

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

     Значение  t сравнивается со значением, взятым из таблицы распределения Стьюдента в соответствии с уровнем значимости a и числом степеней свободы n-2. Если t больше табличного, то коэффициент корреляции значимо отличен от нуля. 
 
 
 
 
 
 
 
 
 
 
 
 
 

2.Реализация  МНК средствами Excel

Рассмотрим  метод наименьших квадратов на примере  прямой линии Y=aX + b, наилучшим образом аппроксимирующей эти данные по критерию наименьших квадратов.

Задача

Для данных, представленных в таблице, найти  в MS Excel коэффициенты a и b прямой линии  y = ax + b. 

     1 способ:

     Создаем новую рабочую книгу, сохраняем ее под именем Глава 2. В ячейки А1 и В1 впишем текстовые обозначения X и Y. Разместим координаты точек в диапазоне А2:В6. В ячейки А8 и В8 впишем текстовые обозначения a и b. Ячейкам А9 и В9 дадим имена коэффициентов а и b, вписывая эти буквы в поле имени ячейки и нажимая затем клавишу Enter. В ячейках А9 и В9 поместим начальные значения коэффициентов a и b , равные нулю (=0 и клавиша Enter).

     В ячейках С2:С6 будем вычислять Y_i =a X_i + b . Для этого в ячейку С2 впишем формулу =a*A2+b и нажмем клавишу Enter. Аналогично в С3 впишем формулу =a*A3+b и нажмем клавишу Enter. Аналогично для ячеек С4, С5, С6. Начиная с ячейки С3 операция вписывания формул можно проделать копированием содержимого ячейки С2 в ячейки С3 – С6 (кнопки Копировать и Вставить на панели инструментов).

     В ячейках D2:D6 будем вычислять ошибки приближения Y_i – (a X_i + b). Для этого в D2 поместим формулу =В2-С2 (Enter). В в D3 поместим формулу =В3-С3 (Enter) и т.д. Начиная с ячейки D3 операция вписывания формул можно проделать копированием содержимого ячейки D2 в ячейки D3 – D6 (кнопки Копировать и Вставить на панели инструментов).

     В ячейку D8 вписываем текст «Сумма квадратов отклонений». Наконец, в ячейке D9 вычислим сумму квадратов отклонений. Воспользуемся функцией СУММКВ(диапазон_ячеек_D2:D6). Для этого вызовем мастер функций, нажав на кнопку со значком f_x, расположенную на панели инструментов. Появится диалоговое окно мастера функций. В списке, расположенном слева, выделим Математические функции. Затем в списке расположенном справа с помощью линии прокрутки найдем функцию СУММКВ и выделим ее. Ниже этих списков Мы видим синтаксис применения этой функции. У нее может быть несколько аргументов, разделяемых точкой с запятой. У нас таких аргументов 5 (ячейки D2 - D6). Однако, можно воспользоваться одним аргументом, указывая сразу диапазон ячеек D2:D6, содержимое каждой из ячеек должно возводиться в квадрат и суммироваться.  

     После выделения функции СУММКВ нажимаем кнопку ОК. Откроется следующее окно, в котором в окошке Число 1 и следует указать диапазон ячеек D2:D6. Программа подсчитала результат – 263,5 для начальных данных. Однако диапазон ячеек она указала не совсем точно. Исправляем неточность или вводим в первое окошко символы $D$2:$D$6 сами. Нажимаем кнопку ОК.  

     Окно  закроется и в ячейке D9 появится результат вычисления суммы квадратов отклонений для заданных нами начальных данных a=0 и b=0, (для уравнения линии Y=0), равный 263,5.

     Теперь  все готово для решения задачи оптимизации. Выделим ячейку D9 и  вызовем Решатель (меню Сервис – Поиск решения). В появившемся окне абсолютный адрес $D$9 целевой ячейки уже установлен.  

     Устанавливаем флажок Равной минимальному значению. Введем в окошко Изменяя ячейки абсолютные адреса $A$9:$B$9 диапазона ячеек, по которым будет минимизироваться значение целевой функции (неизвестные параметры a и b). Это можно сделать с клавиатуры, а также выделяя диапазон ячеек А9:В9 мышью. Ограничений в данной оптимизационной задаче нет.

     Нажимаем  кнопку Выполнить. Хотя процесс решения задачи еще не закончен, все вычисления уже произведены и оптимальные значения уже представлены в соответствующих ячейках. Теперь следует только подтвердить их приемлемость. В появившемся диалоговом окне Результаты поиска решения установлен флажок Сохранить найденное решение. Если это не так, то установим его сами. Если же по каким-либо причинам (например, нужно исправить допущенную ошибку или изменить числовые значения) требуется вернуться к начальным данным, то установим флажок Восстановить исходные значения.

     Нажимаем  кнопку ОК. Результат вычислений представлен ниже.

     Таким образом, оптимальные значения коэффициентов  линейной функции по критерию суммы  квадратов отклонений равны а=-1,5 и b=28,5 . Следовательно, оптимальной  является линия Y=-1,5x+28,5b.

Также оптимальную линию можно найти при помощи другого способа.

2 способ:

Сначала находим неизвестные коэффициенты a и b линейной регрессии y=ax+b по известным  формулам: 

(2.1) 

Составим  таблицу с расчетными данными  и вычислим коэффициенты a и b:

 

То есть имеем следующие уравнение: y=-1,5x+28,5.

Вывод: Ответы у этих решений сошлись абсолютно. Если сравнивать по времени, затраченному на выполнение, то можно сказать, что первый способ не такой трудоемкий как второй. Не нужно составлять какие-либо формулы. 
 
 
 
 
 

3. Реализация МНК средствами математического редактора Mathcad2000

     Пусть в результате эксперимента были получены некоторые значения, представленные в виде таблицы.

     Расстояние  между аргументами произвольное.

     Нужно найти функцию g(x), приближенно описывающую функцию f(x).

     Функция g(x) может выглядеть в виде линейной, квадратичной, степенной и так  далее.

     Рассмотрим  линейную функцию:

     В общем виде она выглядит: g=ax+b.

     Запишем для данного случая систему:

 

Рассмотрим  квадратичную функцию.

В общем  виде она выглядит: g=ax₂+bx+c

Запишем для данного случая систему:

_{- Вычисляем определитель}

_{- Вычисляем определитель  перого порядка}

_{- Вычисляем определитель  второго порядка}

    

На практике данный способ реализуется следующим  образом: Для данных, заданных в таблице установить линейную зависимость: g=ax+b.

1. В поле ввода вводим n=4.
2. Строчкой ниже вводим T:=, на панеле "Матрицы" выбираем кнопку "Создать матрицу или вектор", выбираем количество строк=2, а количество столбцов=5.
3. В каждый квадрат первой строчки матрицы по порядку вводим цифры: 1, 2, 3, 4, 5.
4. В каждый квадрат второй строчки матрицы по поряду вводим цифры: 26, 32, 12, 31, 19.
5. В следующей строчке вводим Mx:=, на панели ""Матанализ" выбираем кнопку "Суммирование", в нижнем квадрате водим i=0, а в верхнем - n, в следующем квадрате вводим T, на панели "Матрицы" выбираем кнопку "Нижний индекс" и в появившемся квадрате вводим 0,i.
6. Рядом вводим Mx=.
7. В следующей строчке вводим Mx2:=, на панели ""Матанализ" выбираем кнопку "Суммирование", в нижнем квадрате вводим i=0, а в верхнем - n, в следующем квадрате вводим скобки и в них вводим T, на панели "Матрицы" выбираем кнопку "Нижний индекс" и в появившемся квадрате вводим 0,i, за скобками на панели "Арифметика" выбираем кнопку "Возведение в степень", в появившемся квадрате вводим 2.
8. Рядом вводим Mx2=.
9. В следующей строчке ввести My:=, на панели ""Матанализ" выбираем кнопку "Суммирование", в нижнем квадрате вводим i=0, а в верхнем - n, в следующем квадрате вводим T, на панели "Матрицы" выбираем кнопку "Нижний индекс" и в появившемся квадрате вводим 1,i.
10. Рядом вводим My=.
11. В следующей строчке вводим Mxy:=, на панели ""Матанализ" выбираем кнопку "Суммирование", в нижнем квадрате вводим i=0, а в верхнем - n, в следующем квадрате вводим скобки, в них T_0,i умножить T_1,i.
12. Рядом вводим Mxy=.
13. В следующей строчке вводим D:=, на панели "Матрицы" выбираем кнопку "Вычисление определителя", а затем "Создать матрицу или вектор", указываем количество строк=2, количество столбцов=2.
14. В первой строке, в появившихся квадратах поочередно вводим: Mx и n+1.
15. В квадратах второй строки вводим Mx2 и Mx. Рядом вводим D=.
16. В следующей строчке вводим D1:=, на панели "Матрицы" выбираем кнопку "Вычисление определителя", а затем "Создать матрицу или вектор", указываем количество строк=2, количество столбцов=2.
17. В первой строке, в появившихся квадратах поочередно вводим: My и n+1.
18. В квадратах второй строки вводим Mxy и Mx. Рядом вводим D1=.
19. В следующей строчке вводим D2:=, на панели "Матрицы" выбираем кнопку "Вычисление определителя", а затем "Создать матрицу или вектор", указываем количество строк=2, количество столбцов=2.
20. В первой строке, в появившихся квадратах поочередно вводим: Mx и My.
21. В квадратах второй строки вводим Mx2 и Mxy. Рядом вводим D2=.
22. Ниже вводим a=D1, знак деления, D. Рядом вводим a=.
23. Ниже вводим b=D2, знак деления, D. Рядом вводим b=.

В итоге  получаем следующее:

Вывод: при реализации МНК в Mathcad 2000 мы находили определители, далее пользовались соотношением определителей. Показатели записали в виде матрицы. 

4. Сравнение  результатов

     При нахождении a и b в линейном уравнении y=ax+b в табличном процессоре Excel и математическом редакторе Mathcad 2000, a и b совпадают, что означает о правильности решения.

     a=-1,5,  b=28,5. Из этого следует, что y=-1,5x+28,5

 

     Очень часто и Excel и Mathcad используют вместе:

     При осуществлении линейной аппроксимации функций методом наименьших квадратов для составления уравнения регрессии сначала необходимо вычислить числа М_х, М_ху, М_у, М_х², это удобно сделать в Microsoft Excel, используя автосуммирование.

     По  полученным значениям М_х, М_ху, М_у, М_х² составляется система, после решения которой можно будет записать уравнение регрессии. Решить систему можно с помощью MathCAD 2000.

     Правильность  вычислений можно проверить в MathCAD 2000. Функции interсept(Х,У) и slope(Х,У) вычисляют  по заданным векторам экспериментальных данных Х, У значения а₀ и а₁ для записи уравнения линейной регрессии в виде j (х)=а₀+а₁х.

     Затем с помощью MathCAD 2000 можно убедиться  в том, что полученное уравнение  регрессии аппроксимирует таблично заданную функцию, построив в одной  системе координат график данной функции и полученного уравнения регрессии.