задачи для программы эксель

Е17 = 1,435666647 - максимальное отклонение

E16 = 6,96092E-09 - минимальное отклонение

G12 = 0,310112289 - среднее отклонение

G11 = 2,791010602 - сумма отклонений

Подаем команду "выполнить" и получаем в ячейках:

Установить целевую ячейку

Теперь входим в поиск решения и устанавливаем:

Чтобы аппроксимировать заданный точками участок кривой необходимо в ячейки В12-В16 ввести любые начальные значения для их оптимизации.

В ячейки B2-B10 мы вводим значения заданных координат по оси абсцисс, т.е. вводим значения xi, а в ячейки C2-C10 вводим значения yi соответственно. Функция ф1 вводится в ячейки с координатами D2-D10, а функция ф2 в ячейки E2-E10. При этом значения констант (a и b) в обеих функция подставляем из ячеек B15, B16. В ячейках F2-F10 функции ф1 и ф2 складываются с учетом коэффициентов k1, k2, k3, как показано выше. Подсчет отклонения по каждой точке считается отдельно и заносится в ячейки G2-G10. Данная операция проводится следующим образом: от заданных координат yi отнимаем полученное значение функции F(x) для одинаковых координат xi. Необходимо помнить, что разность F(x)-yi берется по модулю |F(x)-yi|. В ячейке G11 находим сумму всех отклонений (не забывая о том, что делить необходимо на число пар), а в ячейке G12 - среднее отклонение аппроксимирующей функции от заданной точками функции. В ячейках H2-H10 записываем сумму квадратов модулей этих отклонений. Среднее квадратическое отклонение считается в ячейке Е18 по формуле:

Лист Excel оформим следующим образом:

где a и b некоторые константы, которые попытаемся оптимизировать в поиске решения.

Попытаемся найти F(x) в виде:

Предлагаемый метод отыскания функции F(x) рассмотрим на конкретном примере. Пусть задан набор пар (2;7); (3;3,2); (5;1,3); (8;-0,9); (11;0,9); (14;1,1); (17;2,2); (19;2,2); (23;3).

Мы же пойдем другим путем, а именно вместо минимизации суммы квадратов отклонений будем минимизировать сумму квадратов модулей этих отклонений. Данный подход позволяет не только изменять коэффициенты k1, k2, k3 , но и оптимизировать функции фj(x).

Задача заключается в том, чтобы подобрать такие фj(x) и найти такие kj, чтобы функция F(x) наиболее точно аппроксимировала заданный набор пар (xi;yi). Эта классическая задача регрессионного анализа решается методом наименьших квадратов, суть которого заключается в минимизации суммы квадратов отклонений вида:

где фj(x) - функции задаваемые пользователем, kj - константы.

Задача построения регрессии общего вида заключается в нахождении коэффициентов k1, k2, k3 в функции F(x), имеющей вид:

Пусть задан набор пар (xi;yi) любой природы: результаты экспериментов, измерений, наблюдений и т.д. Заданные пары - не обязательно таблично заданная функция, среди них могут быть пары с одинаковым первым элементом. Excel методом наименьших квадратов отыскивает аппроксимирующую функцию в виде полинома любой степени или других функций, смотри "Мастер диаграмм" - "Диаграмма". Мы проверяли: Excel проводит ргрессионный анализ, используя метод наименьших квадратов. Для многих наборов пар такая "грубая" аппроксимация не может устраивать, поэтому существуют иные пути поиска аппроксимирующих функций.

Регрессия общего вида.

Решение некоторых классов математических задач в программе Excel