Аппроксимация функции методом наименьших квадратов

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

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

ПОСТАНОВКА ЗАДАЧИ

_{РАСЧЁТ С ПОМОЩЬЮ ТАБЛИЦ, ВЫПОЛНЕННЫХ СРЕДСТВАМИ MICROSOFT EXCEL}

Для проведения расчётов, данные целесообразно расположить в виде таблицы 1, используя средства табличного процессора Microsoft Excel.

Таблица 1

Расчеты с помощью таблицы процессора Microsoft Excel

Аппроксимируем функцию линейной функцией. Для определения коэффициентов и воспользуемся системой (4). Используя итоговые суммы таблицы 2, расположенные в ячейках A27, B27, C27 и D27, запишем систему (4) в виде

(11)

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

Систему решали методом Крамера. Суть которого состоит в следующем. Рассмотрим систему n алгебраических линейных уравнений с n неизвестными:

 (12)

Определителем системы называется определитель матрицы системы:

(13)

Обозначим - определитель, который получится из определителя системы Д заменой j-го столбца на столбец

(14)

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

Таблица 2

Решение системы (11)

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

(15)

Далее аппроксимируем функцию квадратичной функцией . Для определения коэффициентов a₁, a₂ и a₃ воспользуемся системой (5). Используя итоговые суммы таблицы 1, расположенные в ячейках A27, B27, C27 , D27, E27, F27, G27 запишем систему (5) в виде

(16)

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

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

Таблица 3.

Решение системы (16)

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

(17)

Теперь аппроксимируем функцию экспоненциальной функцией

.

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

(18)

где .

Решив систему (18), получим и .

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

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

Таблица 4

Решение системы (18)

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

(19)

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

; .

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

Таблица 5

Результаты расчета и

Для того, чтобы рассчитать коэффициент корреляции и коэффициент детерминированности данные целесообразно расположить в виде таблицы 6, которая является продолжением таблицы 1.

Таблица 6

Продолжение Таблицы 1

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

Таблица 7

Расчеты по формулам (8) и (10)

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

_{ИСПОЛЬЗОВАНИЕ ФУНКЦИИ ЛИНЕЙН ДЛЯ ТАБЛИЧНОГО ПРОЦЕССОРА MICROSOFT EXCEL}

Для построения числовых характеристик необходимо создать табличную форму, которая будет занимать 5 строк и 2 столбца. В этот интервал требуется ввести функцию ЛИНЕЙН. Для этого выполняем следующую последовательность действий:

1. Выделим область А65:В69.

2. Вызовем функцию Линейн.

3. Определим аргументы функции

· В качестве изв_знач_у укажем А3:А27.

· В качестве изв_знач_х укажем В3:В27.

· Третье поле Константа оставим пустым.

· В четвертом поле стат наберем истина.

4. Нажмем кнопку ОК.

5. Установим курсор в строку формул.

Нажмем комбинацию клавиш Ctrl+Shift+Enter, это обеспечит ввод табличной формулы.

В результате должны заполниться все ячейки интервала А65:В69 (табл.8)

Таблица 8

Расчеты по формулам (8) и (10)

В ячейках А65:В69 введена формула {=ЛИНЕЙН(А3:А27;B3:B27;ИСТИНА;ИСТИНА)}.

Пояснения к табл. 8:

А67- коэффициент детерминированности

А68- F-наблюдаемое значение.

В68- число степеней свободы.

А69- факторная сумма квадратов.

В69- остаточная сумма квадратов.

Рассмотрим назначение функции ЛИНЕЙН.

Эта функция использует метод наименьших квадратов, чтобы определить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные.

_{Получение числовых характеристик квадратичной зависимости}

1. Выделим область А71:С75.

2. Вызовем Мастер функций.

3. Выберем функцию Линейн.

4. Определим аргументы функции

· В качестве изв_знач_у укажем А3:А27.

· В качестве изв_знач_х укажем В3:В27.

· Третье поле Константа оставим пустым.

· В четвертом поле стат наберем истина.

5. Нажмем кнопку ОК.

6. Установим курсор в строку формул.

Нажмем комбинацию клавиш Ctrl+Shift+Enter. В результате должны заполниться все ячейки интервала А71:С75 (табл.9).

Таблица 9

Получение числовых характеристик квадратичной зависимости

В ячейках А71:С75 введена формула

={ЛИНЕЙН(А3:А27;B3:C27;ИСТИНА;ИСТИНА)}.

_{Получение числовых характеристик экспоненциальной зависимости}

1. Выделим область А78:В82.

2. Вызовем Мастер функций.

3. Выберем функцию ЛГРФПРИБЛ.

4. Определим аргументы функции

· В качестве изв_знач_у укажем А3:А27.

· В качестве изв_знач_х укажем В3:В27.

· Третье поле Константа оставим пустым.

· В четвертом поле стат наберем истина.

5. Нажмем кнопку ОК.

6. Установим курсор в строку формулу.

Нажмем комбинацию клавиш Ctrl+Shift+Enter. В результате должны заполниться все ячейки интервала А78:В82 (см. табл.10).

Таблица 10

Получение числовых характеристик экспоненциальной зависимости

В ячейках А78:В82 введена формула

{=ЛГРФПРИБЛ(А3:А27;B3:B27;ИСТИНА;ИСТИНА)}.

Сравнивая значения коэффициентов, полученных вручную с табличными, видим отличие в значении (он вычислен в ячейке А78). Это связано с тем, что функция ЛГРФПРИБЛ возвращает параметры для соотношения . То есть . Отсюда следует, что .

Вычисление коэффициента представлено на рис.2.:

Рис. 2. Ячейка А78 Вычисление коэффициента

В ячейке А77 введена формула =LN(A78).

_{ГРАФИЧЕСКИЙ СПОСОБ НАХОЖДЕНИЯ ИСКОМОЙ ЗАВИСИМОСТИ}

Рис. 3. График линейной аппроксимации в среде Microsoft Excel