Метод наименьших квадратов и его реализация в MicrosoftExcel

Далее аппроксимируем функцию квадратичной функцией . Для определения коэффициентов , и воспользуемся системой

 

 

Используя итоговые суммы таблицы 2, расположенные в ячейках A27, B27, C27, D27, E27, F27 и G27 запишем систему в виде

решив которую, получим , и .

Таким образом, квадратичная аппроксимация имеет вид .

Решение системы проводили, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 4.э

Таблица 4 Результаты коэффициентов квадратичной аппроксимации.

 

В таблице 4 в ячейках E38:G40 записана формула {=МОБР(E33:G35)}.

В ячейках I38:I40 записана формула {=МУМНОЖ(E38:G40;H33:H35)}.

Теперь аппроксимируем функцию экспоненциальной функцией . Для определения коэффициентов и прологарифмируем значения и используя итоговые суммы таблицы 2, расположенные в ячейках A27, C27, H27 и I27 получим систему

где .

Решив систему, найдем , .

После потенцирования получим .

Таким образом, экспоненциальная аппроксимация имеет вид

Решение системы проводили, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 5.

 

Таблица 5 Результаты коэффициентов экспоненциальной аппроксимации.

 

В таблице 5 в ячейках D45:E46 записана формула {=МОБР(D42:943)}.

В ячейках G45:G46 записана формула {=МУМНОЖ(D45:E46;F42:F43)}. В ячейке G47 записана формула =EXP(G45).

Вычислим среднее арифметическое и по формулам:

Результаты расчета и средствами Microsoft Excel представлены в таблице 6.

Таблица 6 Вычисление средних значений X и Y.

 

В ячейке F49 записана формула =A26/25. В ячейке F50 записана формула =B26/25. Для того, чтобы рассчитать коэффициент корреляции и коэффициент детерминированности данные целесообразно расположить в виде таблицы 7, которая является продолжением таблицы 2.

 

Таблица7. Вычисление остаточных сумм.

Поясним как таблица 7 составляется.

Ячейки A2:A27 и B2:B27 уже заполнены (см. табл. 2).

Далее делаем следующие шаги.

Шаг 1. В ячейку J2 вводим формулу =(A2-$F$49)*(B2-$F$50).

Шаг 2. В ячейки J3:J26 эта формула копируется.

Шаг 3. В ячейку K2 вводим формулу =(A2-$F$49)^2.

Шаг 4. В ячейки K3:K26 эта формула копируется.

Шаг 5. В ячейку L2 вводим формулу =(B2-$F$50)^2.

Шаг 6. В ячейки L3:L26 эта формула копируется.

Шаг 7. В ячейку M2 вводим формулу =($D$37+$D$38*A2-B2)^2.

Шаг 8. В ячейки M3:M26 эта формула копируется.

Шаг 9. В ячейку N2 вводим формулу

=($I$38+$I$39*A2+$I$40*A2^2-B2)^2.

Шаг 10. В ячейки N3:N26 эта формула копируется.

Шаг 11. В ячейку O2 вводим формулу

=($G$47*EXP($G$46*A2)-B2)^2.

Шаг 12. В ячейки O3:O26 эта формула копируется.

Последующие шаги делаем с помощью автосуммирования .

Шаг 13. В ячейку J27 вводим формулу =СУММ(J2:J26).

Шаг 14. В ячейку K27 вводим формулу =СУММ(K2:K26).

Шаг 15. В ячейку L27 вводим формулу =СУММ(L2:L26).

Шаг 16. В ячейку M27 вводим формулу =СУММ(M2:M26).

Шаг 17. В ячейку N27 вводим формулу =СУММ(N2:N26).

Шаг 18. В ячейку O27 вводим формулу =СУММ(O2:O26).

Теперь проведем расчеты коэффициента корреляции по формуле

 

(только  для линейной аппроксимации)

и коэффициента детерминированности по формуле . Результаты расчетов средствами Microsoft Excel представлены в таблице 8.

 

Таблица 8 Результаты расчета.

 

В таблице 8 в ячейке D53 записана формула =J27/(K27*L27)^(1/2).

В ячейке D54 записана формула =1- M27/L27.

В ячейке D55 записана формула =1- N27/L27.

В ячейке D56 записана формула =1- O27/L27.

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

 

 

4. Построение  графиков в Excel и использование функции ЛИНЕЙН

 

Рассмотрим результаты эксперимента, приведенные в исследованном выше примере.

Исследуем характер зависимости в три этапа:

• Построим график зависимости.
• Построим линию тренда ( , , ).
• Получим числовые характеристики коэффициентов этого уравнения.

 

Рис.4.1. График зависимости y от x

 

Рис.4.2. График линейной аппроксимации

 

Рис.4.3. График квадратичной аппроксимации.

 

Рис.4.4. График экспоненциальной аппроксимации.

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

 

 

Таблица 9

 

 

Заключение

 

Сделаем заключение по результатам полученных данных:

1. Анализ  результатов расчетов показывает, что квадратичная аппроксимация  наилучшим образом описывает  экспериментальные данные т.к. согласно  таблице 8 коэффициент корреляции - 0,9620; Коэффициенты детерминированности  линейной аппроксимации - 0,9253; квадратической аппроксимации – 0,994; экспоненциальной аппроксимация – 0,0269.

2. Сравнивая  результаты, полученные при помощи  функции ЛИНЕЙН видим что они полностью совпадают с вычислениями, проведенными выше. Это указывает на то, что вычисления верны.

3. Полученное  при построении линии тренда  значение коэффициента детерминированности  для экспоненциальной зависимости  не совпадает с истинным значением поскольку при вычислении коэффициента детерминированности используются не истинные значения y, а преобразованные значения ln(y) с дальнейшей линеаризацией.

4. Результаты полученные с помощью программы на языке PASCAL полностью совпадают со значениями приведенными выше. Это говорит о верности вычислений.

 

 

 

 

 

Список литературы

 

1. Ахметов К.С. Windows 95 для всех. - М.:ТОО "КомпьютерПресс", 1995.
2. Вычислительная техника и программирование. Под ред. А.В. Петрова. М.: Высшая школа, 1991.
3. Гончаров A., Excel 97 в примерах. — СПб: Питер, 1997.
4. Левин А., Самоучитель работы на компьютере. - М.: Международное агентство А.Д.Т., 1996.

5.Информатика: Методические указания к курсовой работе. Санкт-Петербургский горный институт. Сост. Д.Е. Гусев, Г.Н. Журов. СПб, 1999